patternsqlMajor
Slow Queries Not Logging
Viewed 0 times
loggingqueriesnotslow
Problem
I am attempting to enable slow query logging on our server in order to identify any queries that could use optimization. Sounds simple enough, however my file is not being written to. I get no errors or anything of the like, it just doesn't seem to be logging the slow queries. I have remembered to restart mysql after my config changes.
I am using MySQL Ver 5.1.61 . Here is what I have in my.cnf:
the file /var/logs/my.slow.log has mysql as the owner, also in the interest of debugging I gave read/write to all on the log file.
I have the long_query_time set to 1 in the above as I just want to see if it is working. I have tried setting it lower(e.g 0.3) but I'm still not getting anything logged. I know that the queries my app are running take longer than 1 second and I've also ran intentionally log queries (
I've looked through the docs, from what I can see this should be working. Anyone have any suggestions as to what I am doing wrong? Any advice would be appreciated, thanks much!
Edit: As asked in comments I ran the query:
The result:
Obviously my configuration changes are not being taken into account as I believe these are defaults. I am quite certain the my.cnf file I am altering is being parsed as if I put in an invalid value mysql will error on restart. What could be going on here?
Another Edit:
After taking @RolandoMySQLDBA 's advice and moving my slow query config lines to under
However I am still not seeing the file my.slow.log being written
I am using MySQL Ver 5.1.61 . Here is what I have in my.cnf:
slow-query-log=1
slow-query-log-file=/var/logs/my.slow.log
long_query_time=1the file /var/logs/my.slow.log has mysql as the owner, also in the interest of debugging I gave read/write to all on the log file.
I have the long_query_time set to 1 in the above as I just want to see if it is working. I have tried setting it lower(e.g 0.3) but I'm still not getting anything logged. I know that the queries my app are running take longer than 1 second and I've also ran intentionally log queries (
SELECT sleep(10);) in the terminal for testing and the log is still empty. I've looked through the docs, from what I can see this should be working. Anyone have any suggestions as to what I am doing wrong? Any advice would be appreciated, thanks much!
Edit: As asked in comments I ran the query:
`SELECT variable_value FROM information_schema.global_variables WHERE variable_name IN ('slow_query_log','slow_query_log_file','long_query_time');`The result:
10.0000000
/var/run/mysqld/mysqld-slow.log
OFFObviously my configuration changes are not being taken into account as I believe these are defaults. I am quite certain the my.cnf file I am altering is being parsed as if I put in an invalid value mysql will error on restart. What could be going on here?
Another Edit:
After taking @RolandoMySQLDBA 's advice and moving my slow query config lines to under
[mysqld] my settings seem to be saving. Now the result of the above variable_value query is :1.0000000
/var/logs/my.slow.log
ONHowever I am still not seeing the file my.slow.log being written
Solution
I think I got the answer:
You need to put those options under the
and restart mysql
UPDATE 2013-03-05 16:36 EST
I don't know why this is still happening, but please try this:
then run
You need to put those options under the
[mysqld] section[mysqld]
slow-query-log=1
slow-query-log-file=/var/logs/my.slow.log
long_query_time=1and restart mysql
UPDATE 2013-03-05 16:36 EST
I don't know why this is still happening, but please try this:
service mysql stop
rm -f /var/logs/my.slow.log
touch /var/logs/my.slow.log
chown mysql:mysql /var/logs/my.slow.log
service mysql startthen run
SELECT SLEEP(10); and see if it lands in /var/logs/my.slow.logCode Snippets
[mysqld]
slow-query-log=1
slow-query-log-file=/var/logs/my.slow.log
long_query_time=1service mysql stop
rm -f /var/logs/my.slow.log
touch /var/logs/my.slow.log
chown mysql:mysql /var/logs/my.slow.log
service mysql startContext
StackExchange Database Administrators Q#36003, answer score: 22
Revisions (0)
No revisions yet.