Wednesday, January 8, 2014

Enable Slow Query Log in MySQL

How to enable slow query log in MySQL without restarting mysqld

In MySQL 5.1.12 and later, this can be done without restarting mysql as follows:

root@anneke:~# touch /var/log/mysql/mysql-slow.log
root@anneke:~# chown mysql:mysql /var/log/mysql/mysql-slow.log
root@anneke:~# mysql -e 'SET GLOBAL slow_query_log=1;'
root@anneke:~# mysql -e 'SET GLOBAL slow_query_log_file="/var/log/mysql/mysql-slow.log";'
root@anneke:~# mysql -e 'SET GLOBAL long_query_time=2;'


If you want to leave slow query logging on, you will need to add 3 lines to /etc/mysql/my.cnf under the [mysqld] section:
slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=2


To test if it is working correctly, simply run a SELECT SLEEP(x) query where "x" is longer than the long_query_time you have set - the query should be logged to the file you specified for slow_query_log_file (as long as it exists and the mysql user can access it).

root@anneke:~# mysql -e 'SELECT SLEEP(5);'

Then you can look at the logfile and check id your "slow" query is there:
less /var/log/mysql/mysql-slow.log
or
tail -f /var/log/mysql/mysql-slow.log


Here is a summary of all of the different ways to turn on slow query logging in various versions of MySQL:
  • Before 5.1.6, start mysqld with the --log-slow-queries[=file_name] option.
  • In versions after MySQL 5.1.6, you can log a file or a table, or both. Start mysqld with the --log-slow-queries[=file_name] option and optionally use --log-output to specify the log destination.
  • After MySQL 5.1.12, use --slow_query_log[={0|1}] - 1 is on and the default slow query log file name is used.
  • After MySQL 5.1.29, use --slow_query_log[={0|1}] - the --log-slow-queries option is deprecated.
  • When using slow_query_log=1 (either in my.cnf or starting with mysqld --slow_query_log=1) use slow_query_log_file=/your/log/file or just leave it as the default ($hostname-slow.log in the mysql data folder).

You can find more information about slow query logging at the official MySQL Reference: http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html