HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Slow Queries Not Logging

Submitted by: @import:stackexchange-dba··
0
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:

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

Solution

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

Code Snippets

[mysqld]
slow-query-log=1
slow-query-log-file=/var/logs/my.slow.log
long_query_time=1
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

Context

StackExchange Database Administrators Q#36003, answer score: 22

Revisions (0)

No revisions yet.