15

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!

2
  • 1
    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. Mar 5, 2013 at 18:42
  • 1
    If anyone still have issues after reading this, check the log_output variable. SET GLOBAL log_output=FILE fixed it for me.
    – molholm
    Jul 2, 2015 at 7:08

1 Answer 1

22

I think I got the answer:

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=1

and 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 start

then run SELECT SLEEP(10); and see if it lands in /var/logs/my.slow.log

4
  • Thanks!What you suggested helped, it seems the settings are saving properly however I'm still not getting things written. I've edited my question to reflect this.
    – TheMethod
    Mar 5, 2013 at 20:24
  • Hey, just curious, I'm running a Debian system under a VM. Is the path correct in your reply? /var/logs/ instead of /var/log Nov 29, 2014 at 18:01
  • Just Info. "touch & chown" are necessary at CentOS 6.6 mysql 5.1.73 environment also. Jul 14, 2015 at 2:41
  • I just ran across a system where they attempted to put these in [mysqld_safe], which did not work. Putting them under [mysql] solved the problem. Sep 24, 2018 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.