MySQL slow query log can be to used to determine queries that take a longer time to execute in order to optimize them. The slow query log consists of SQL statements that took more than `long_query_time` seconds to execute, required at least `min_examined_row_limit` rows to be examined and fulfilled other criteria specified by the slow query log settings.

Before enabling the MySQL slow query log, we must decide criteria for SQL statements that will be logged and also select the location where they will be logged.

Slow Query Log Output Destinations

MySQL allows you to control the destination of output for the slow query log. By default the slow query log is written to FILE.

You can change it dynamically by setting the GLOBAL variable ‘log_output’ as shown below:

mysql> SET GLOBAL log_output = 'TABLE,FILE';

The allowed values for the variable ‘log_output’ are: FILE, TABLE, NONE. You can enter multiple values separated by comma. In case a value of ‘NONE’ is specified the log will not be written even if the slow query log has been turned on. If the log output destination is table then the logs will be written to the table ‘mysql.slow_log’.

By default the slow query log file name is host_name-slow.log. The server creates the file in the data directory unless an absolute path name is given to specify a different directory.

The log file path can be changed, say we have to change it to ‘/var/log/mysql/slow_query.log’. We run the following command:

mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/slow_query.log';

Make sure the MySQL user has write permissions to the ‘slow_query.log’ file.

You can create a blank file with MySQL user as its owner as shown below:

$ mkdir /var/log/mysql

$ touch /var/log/mysql/slow_query.log

$ chown mysql.mysql -R /var/log/mysql

Controlling what is logged

MySQL allows you to define the benchmark for considering a query as slow. By default, the value of system variable ‘long_query_time’ is 10. It means that any query which takes more than 10 seconds to execute will be considered as slow.

Lets change the benchmark to 20 seconds as shown below:

mysql > SET GLOBAL long_query_time = 20;

By default, queries that do not use indexes are not logged. In order to log them too run the following command:

mysql> SET GLOBAL log_queries_not_using_indexes = 'YES';

Similarly, administrative statements are also not logged by default. Administrative statements include ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE. To enable logging for them, run the following command:

mysql> SET GLOBAL log_slow_admin_statements = 'YES';

Enabling the MySQL slow query logging

After you have decided and configured what will be logged and where it will be logged, you can now turn on the slow query logging using the following command:

mysql > SET GLOBAL slow_query_log = 'ON';

Instead of the value ‘ON’, you can even set the value as 1.

Disabling the MySQL slow query logging

The log file may grow big over time so you can enable log rotation or disable the slow query logging using the following command:

mysql> SET GLOBAL slow_query_log = 'OFF';

Monitoring

You can monitor the number of queries logged as a slow query due to taking longer than long_query_time seconds using the ‘Slow_queries’ status variable as shown below:

mysql> SHOW GLOBAL STATUS LIKE 'Slow_queries';

Analyzing the slow query log

It will be difficult to interpret the MySQL slow query log without any tool, fortunately MySql provides “mysqldumpslow” . “mysqldumpslow” parses MySQL slow query log files and prints a summary of their contents.

Normally, mysqldumpslow groups queries that are similar except for the particular values of number and string data values. It “abstracts” these values to N and ‘S’ when displaying summary output. The -a and -n options can be used to modify value abstracting behavior.

mysql> mysqldumpslow /var/log/mysql/slow_query.log

To find the top 5 queries which returned the maximum rows, you can run the following command:

mysqldumpslow -a -s r -t 5 /var/log/mysql/mysql-slow.log

This can help you find the queries where you missed LIMIT clause.

To sort output by count (the number of times the query is found in the slow log), run the following command:

mysqldumpslow -a -s c -t 5 /var/log/mysql/mysql-slow.log

You can refer to the MySQL official documentation to see all the options available with “mysqldumpslow”.