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)

2 comments:

Darren Cassar said...

don't forget to "set global long_query_time=10" by default .... you should set it to whatever you need.
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_long_query_time

James said...

Yep cannot believe I forgot that ;) I'll do another revision of this article with a couple of tricks I recently learned.