Logging slow queries with MySQL

MySQL has a slow query log which can be enabled if you need to track down slow queries which are causing issues for a website or application.

Enable the slow query log

MySQL prior to 5.1.0 requires a change to the MySQL my.cnf file and a restart in order to log slow queries; from MySQL 5.1.0 you can change this dynamically without having to restart.

To make the change permanent whenever the MySQL server is started, and for MySQL prior 5.1.0, edit your my.cnf file (on Linux boxes this is usually somewhere like /etc/my.cnf or /etc/mysql/my.cnf) and uncomment the "log_slow_queries" line or add it if it's not present.

On a Debian box, for example, the line to uncomment looks like so:

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

You can change the log file name to something else or leave it blank so it uses the default. The default is to log the queries into a file in the MySQL data directory. On my Debian test box this was "mysqld-slow.log".

To enable or disable the setting dynamically in MySQL 5.1.0 run the following query to enable it:

set global log_slow_queries = ON;

and to disable it:

set global log_slow_queries = OFF;

Setting the long query time

You can also specify how long a quey needs to run for before it is logged with the "long_query_time" setting. By default this is 10 seconds.

In the my.cnf file, to change it to e.g. 5 seconds add the following:

long_query_time = 5

This can be changed dynamically in MySQL 5.0.0+ (and possibly earlier versions) by running the following query:

set global long_query_time = 5;

This will only work for new connections; any connections which have already been established will continue to use the old setting. Once the user disconnects and reconnects their new connection will use the new setting.

Errors when changing settings dynamically

If you get the following error message when attempting to change the log_slow_queries setting dynamically then you are using a version of MySQL that does not support changing the setting dynamically:

ERROR 1193 (HY000): Unknown system variable 'log_slow_queries'

The long_query_time value must be a whole number; if it's not (e.g. you attempt set global long_query_time = 2.5;) then you'll get this error:

#1232 - Incorrect argument type to variable 'long_query_time'

Note also that if you set the long_query_time to 0 it will not fail, but the actual setting applied will be 1 and not 0.

If you get the following error then you have omitted the "global" keyword when setting it:

ERROR 1229 (HY000): Variable 'log_slow_queries' is a GLOBAL variable and should be set with SET GLOBAL

 

Related posts:

• Location of MySQL's my.cnf file