CODEWITHSUNDEEP
mysqlmysql-slow-query-log
Ethan Arnold
Ethan Arnold

Reputation: 154

Why is my mysql slow_query_log empty?

I have the following setup on a production machine:
Mysql server 5.5.19
the active (checked the ps line) my.cnf contains:

slow_query_log
slow_query_log_file =/opt/app/mysql/log/mysqld-slow.log
long_query_time     = 1
general_log         = OFF
general_log_file    =/opt/app/mysql/log/mysqld.log
log-queries-not-using-indexes

mysql> SHOW VARIABLES LIKE "log%"; --> shows "log_queries_not_using_indexes=ON" and "log_slow_queries=ON"

The file /opt/app/mysql/log/mysqld-slow.log exists, belongs to mysql and has zero length.

The issue is, on a test machine we had th eslow query log filling up rapidly because of the log-queries-not-using-indexes option. We disabled it because it wanst needed anyway. Now we just went live with our production db and wanted to make sur ethe same thing wouldnt happen to us, and found the same setup, but the log is empty. We can't just restart mysqld and play around because we're live now. I just want to understand why the log is empty although the config seems to indicate it should be logging lotsa queries. The general application running is identical on the production and test servers, so it's very unlikely there are actually no un-indexed queries happening.

So the question is: What other configuration can I check to see why nothing is being logged? I dont want it to be logged, I just want to be sure it won't suddenly start logging like crazy if the db server is restarted for some reason. I know I could achieve that by simply removing the option from the my.cnf but I'd also like to understand why it isnt logging right now.

Thanks, Ethan

Upvotes: 2

Views: 3648

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562791

It's possible you simply have no queries longer than 1 second running.

You can run a query like SELECT SLEEP(10), so it'll be sure to be longer than long_query_time and will be logged.

It's also possible queries are being written to the file, but you have rm'd the old log file. So the server is still writing to a disassociated file descriptor. You can confirm this with lsof.

You can force the server to close and reopen the log file, without requiring a restart of mysqld. This will abandon the disassociated file. You can do this in either of two ways: FLUSH LOGS or else set the global variable slow_query_log=0 then slow_query_log=1.

Upvotes: 4

domi27
domi27

Reputation: 6923

MySQL version 5.0 saw some major changes to logging capacities.

Logging is controlled by the log-output variable. Be aware NONE means no logging overall. You can check the value of this variable like so:

mysql> SHOW VARIABLES LIKE "log_output";

In your case, you want to have some slow-logging to a file, so might do something like:

mysql> SET GLOBAL log_output=FILE;
mysql> SET GLOBAL slow_query_log=ON;

Upvotes: 4

Related Questions