mysql_slow_query []

This is an old revision of the document!

Slow Query Logs

You can check to see if slow query logging is currently enabled. The following command assumes you have configured .my.cnf file:

show full variables like "%slow%";

Or you can run the following from the command line:

mysql -e 'show variables;' | grep slow_query_log

Output should show something similar to:

+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| log_slow_queries    | OFF                             |
| slow_launch_time    | 2                               |
| slow_query_log      | OFF                             |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
+---------------------+---------------------------------+

Or:

slow_query_log  OFF
slow_query_log_file     /var/run/mysqld/mysqld-slow.log

How many slow queries?

You can also use the following command to view if you currently have any slow queries (if logging has been enabled):

show status like '%slow%';

Or from the command line:

mysql -e 'show status;' | grep -i slow

This should produce a similar output:

Slow_launch_threads     0
Slow_queries    0

Configuring Slow Query logs

There are now 2 options for configuring slow query logging. You can add the settings in the .my.cnf file or you can set the variables through the mysql command line.

Option 1

If run the following command it will set slow query logging without needing to restart mysql

mysql -e 'set global log_slow_queries = 1;'

You can now run the following command and it should produce an output saying that slow_query logging is enabled:

mysql -e 'show variables;' | grep slow

Option 2 - .my.cnf

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 10
log_queries_not_using_indexes = 1

Change permissions so that mysqld can write to the specified log file. Giving write permissions to the 'other' group should suffice.