Monday, July 02, 2012

MySQL Enable Slow Query Logging

In MySQL, slow queries can be logged in the 'slow query log'. To enable this feature you need to set the following variable:

log_slow_queries = 1

If you are running an older version of MySQL (prior to 5.1) you need to bounce the database. Additionally you can also set the filename as well:

slow_query_log_file = '<filename>'

Note that in MySQL 5.1 and later versions, this parameter can be set while the instance is running:

mysql> set global log_slow_queries = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%slow%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| log_slow_queries    | ON                               |
| slow_launch_time    | 2                                |
| slow_query_log      | ON                               |
| slow_query_log_file | /home/james/mysql/ebony-slow.log |
+---------------------+----------------------------------+
4 rows in set (0.00 sec)
Post a Comment