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:
slow-query-log=1
slow-query-log-file=/var/logs/my.slow.log
long_query_time=1
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!
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.SET GLOBAL log_output=FILE
fixed it for me.