MySQL slow query logs not working

Question

I need some help diagnosing a fault

I have a development server and a production server. They are (in the large part) identical in setup. Both are Ubuntu Server 10.04 LTS installs.

I turned on the MySQL slow query logs on both servers, and it works on the development server, but not the production server. On development, the slow query log fills up fairly quicly, but on production, the log file remains empty.

And idea how I can diagnose why it's not working on production? Are there any error logs that might contain the information?

There's nothing obviously relevant in /var/log/mysql/error.log, and /var/log/mysql/mysql-slow.log actually exists - it's just empty.

my.cnf:

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2

Thanks for any direction.

Answer 1

I'd check the MySQL server versions for the correct parameters to use, as there are differences between them.

E.g. http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

Which states that log_slow_queries is depracated as of version 5.1.29, and you need to use slow_query_log = 1 to enable it and slow_query_log_file = /path/to/file.name

Prior to 5.1.29 you used log_slow_queries = /path/to/file.name to specify where to log the queries to.

Between 5.1.12 and 5.1.29 you could use either variant.

It could be that your production box is 5.1.29 or higher, and your dev box is pre-5.1.29 and can therefore understand the options you are using, and the production box can't...

Answer 2

Can you be more specific on what is 'not working' Is it a system crash? A select not returning the right information? Tables not updating?

In general tools such as the:

explain statement (http://dev.mysql.com/doc/refman/5.0/en/explain.html)

show process list (http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html)

general query log (http://dev.mysql.com/doc/refman/5.0/en/query-log.html)

are other avenues of investigation.