Difference between revisions of "Enable MySQL’s slow query log without a restart"

Latest revision as of 08:42, 18 May 2016

Log on to mysql as root
Set the log-file location for the Slow Query log. Make sure mysql has write permission.

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

Determine what makes a query "slow", by setting the limit (in seconds) after which a query is logged to the slow query log. The example below logs every query that exceeds 10 seconds in duration.

mysql> SET GLOBAL long_query_time = 10;

Now enable the Slow Query log.

mysql> SET GLOBAL slow_query_log = 'ON';
mysql> FLUSH LOGS;

As a bonus, you can also make all queries that do not use indexes be logged to the same Slow Query log.

mysql> SET GLOBAL log_queries_not_using_indexes = 'YES'; 

If you want to make these changes persistent, modify the my.cnf and add these lines to the [mysqld] part of the config.

[mysqld]
...
slow_query_log = /path/to/slow_query.log
long_query_time = 10
log_queries_not_using_indexes = YES

To verify if your settings are working, request the running parameters from MySQL.

mysql> SHOW GLOBAL VARIABLES LIKE 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+

mysql> SHOW GLOBAL VARIABLES LIKE 'slow\_%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /path/to/slow_query.log       |
+---------------------+-------------------------------+

mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

And don't forget to either disable it again after your debug-session, or enable logrotate to prevent the files from growing too large. To disable the logging, but leave the rest of the requested configs intact, use the following at the CLI.

mysql> SET GLOBAL slow_query_log = 'OFF';
mysql> FLUSH LOGS;

To disable it permanently in the my.cnf file, set the slow_query_log to 0.

[mysqld]
...
slow_query_log = 0
long_query_time = 10
log_queries_not_using_indexes = YES