Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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 (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
OFF

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 [mysqld] my settings seem to be saving. Now the result of the above variable_value query is :

1.0000000
/var/logs/my.slow.log
ON

However I am still not seeing the file my.slow.log being written to. I don't think it is a permissions issue as the file is owned by mysql and I've added all permissions for all users on the file. Could anyone think of a reason why this would not be working?

Edit: Solved! The path to the slow query log was incorrect, it should have been /var/log/my.slow.log instead of /var/log*s*/my.slow.log . Thanks to all for the help, I've learned allot!

Please run SELECT variable_value FROM information_schema.global_variables WHERE variable_name IN ('slow_query_log','slow_query_log_file','long_query_time'); and post its output.
–
RolandoMySQLDBAMar 5 '13 at 18:42

If anyone still have issues after reading this, check the log_output variable. SET GLOBAL log_output=FILE fixed it for me.
–
molholmJul 2 at 7:08