Jim Youmans
asked on
MySQL Log Question
I have a database running on CentOS 7, MySQL 5.1.
My question is that the general log file is like 10 - 20 GB a day and is full of simple select statements. Is this normal? That seems to be a huge daily file to create. I am pretty new to MySQL on CentOS so was hoping someone could tell me if that seems to be a bit much or if it is pretty normal?
Thanks!
Jim
My question is that the general log file is like 10 - 20 GB a day and is full of simple select statements. Is this normal? That seems to be a huge daily file to create. I am pretty new to MySQL on CentOS so was hoping someone could tell me if that seems to be a bit much or if it is pretty normal?
Thanks!
Jim
I'm far from a MySQL Expert. I've only dabbled but I wonder if you have general_log set?
https://docs.oracle.com/cd/E17952_01/mysql-5.1-en/query-log.html
https://docs.oracle.com/cd/E17952_01/mysql-5.1-en/query-log.html
ASKER
No it is the general log. And looking through it, there are not errors noted, only simple queries. Here is an example of what is in that file.
21354208 Query SELECT Conversion FROM Units WHERE ItemCode = '9719' AND UOM = 'BX'
21354208 Query SELECT ItemCount FROM ItemCount WHERE ItemCode = '381' AND Unit = 'EA' AND (ProgramID = 0 || ProgramID = '')
21354208 Query SELECT Conversion FROM Units WHERE ItemCode = '381' AND UOM = 'EA'
21354208 Query SELECT ItemCount FROM ItemCount WHERE ItemCode = '382' AND Unit = 'EA' AND (ProgramID = 0 || ProgramID = '')
21354208 Query SELECT Conversion FROM Units WHERE ItemCode = '382' AND UOM = 'EA'
21354208 Query SELECT ItemCount FROM ItemCount WHERE ItemCode = '383' AND Unit = 'EA' AND (ProgramID = 0 || ProgramID = '')
Just line after line after line like this. Some queries are larger but all the contents seem to be queries.
21354208 Query SELECT Conversion FROM Units WHERE ItemCode = '9719' AND UOM = 'BX'
21354208 Query SELECT ItemCount FROM ItemCount WHERE ItemCode = '381' AND Unit = 'EA' AND (ProgramID = 0 || ProgramID = '')
21354208 Query SELECT Conversion FROM Units WHERE ItemCode = '381' AND UOM = 'EA'
21354208 Query SELECT ItemCount FROM ItemCount WHERE ItemCode = '382' AND Unit = 'EA' AND (ProgramID = 0 || ProgramID = '')
21354208 Query SELECT Conversion FROM Units WHERE ItemCode = '382' AND UOM = 'EA'
21354208 Query SELECT ItemCount FROM ItemCount WHERE ItemCode = '383' AND Unit = 'EA' AND (ProgramID = 0 || ProgramID = '')
Just line after line after line like this. Some queries are larger but all the contents seem to be queries.
ASKER
Yes it is the general log.
general_log_file /var/log/mysqld/mysqld.log
general_log_file /var/log/mysqld/mysqld.log
>>No it is the general log.
Check the link I provided. If you don't want/need all that, just disable general logging.
Check the link I provided. If you don't want/need all that, just disable general logging.
Usually, binlog if enabled has a prediifined size limit and auto rotate.
One option is to list show variables and then find the reference to this file to determine why it is being maintained.
One option is to list show variables and then find the reference to this file to determine why it is being maintained.
ASKER
Show Variables give me this.
general_log_file /var/log/mysqld/mysqld.log
So from what I am reading I don't need this log to be on?
general_log_file /var/log/mysqld/mysqld.log
So from what I am reading I don't need this log to be on?
You can disable it without the need to stop/start MySQL by disabling the general log
Not sure the correct variablename for general log to use
Set global <variablename>=0
To male this setting permanent, make sure to change the /etc/my.cnf
Not sure the correct variablename for general log to use
Set global <variablename>=0
To male this setting permanent, make sure to change the /etc/my.cnf
Quick check on what is in the log:
grep -v SELECT mysql.log
or
grep -v SELECT mysql.log grep -v INSERT | grep -v DELETE ...
to exclude some other verbs.
then you should see if there are some other stuff inside.
grep -v SELECT mysql.log
or
grep -v SELECT mysql.log grep -v INSERT | grep -v DELETE ...
to exclude some other verbs.
then you should see if there are some other stuff inside.
See
https://dev.mysql.com/doc/refman/5.7/en/query-log.html
set global general_log=off
Within my.cnf make sure to either comment out general_log=on or change on to off to make the change permanent.
If not changed, when service restarts general_logging will resume.
https://dev.mysql.com/doc/refman/5.7/en/query-log.html
set global general_log=off
Within my.cnf make sure to either comment out general_log=on or change on to off to make the change permanent.
If not changed, when service restarts general_logging will resume.
>>Not sure the correct variablename for general log to use
I already posted the link with the correct command to enable and disable it.
>>So from what I am reading I don't need this log to be on?
We cannot say. There might be some corporate policy that requires it.
not sure about 5.1 but I believe OFF is the default in later versions. If it is ON, someone set it for some reason.
I already posted the link with the correct command to enable and disable it.
>>So from what I am reading I don't need this log to be on?
We cannot say. There might be some corporate policy that requires it.
not sure about 5.1 but I believe OFF is the default in later versions. If it is ON, someone set it for some reason.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Are you refering to the slow query log?...
If so then the timeout to declare a query slow is too short...
If it is not the slow query log then you need to fix troubles:
you may have to look into the file and pick some errors and fix them...