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 2 options for configuring slow query logs:

- Run time - this will configure the slow query logs in the current runtime environment, when mysql is restarted the value will be lost. You will need to configure the value in the /etc/my.cnf for variables to remain

- Permanent - editing the /etc/my.cnf will allow mysql to retain the values once the service has been restarted. Changing this file will NOT change the current runtime environment.

NOTE: To enable to variables without restarting the service, use a combination of both techniques above.

Run-time Configuration

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

/etc/my.cnf - Permanent Configuration

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

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