Enabling the Slow Query Log in MySQL without service restart - Database Optimization Basics

This is the first article in a series on MySQL Database Optimization Basics. Subscribe to be notified when new articles are added!

Today we're going to cover how to log slow queries in mysql so that you can identify problems and refactor the correct queries in order to improve the performance of your website or application. Slow queries can cause errors when people use your website or application and having them will make your code difficult to scale. The first step toward fixing them is seeing which queries are slow in your code, so let's start by learning how to enable slow query logging without restarting the mysql service:

How to enable slow query log in MySQL without restarting mysqld

In MySQL 5.1.12 and later, this is fairly straightforward and can be done without restarting mysql as follows:

*Note: this requires command line access over SSH as root, so it is only applicable to VPS and Dedicated Servers. I am working on a Centos 5 VPS with cPanel, but this will work on most Linux and UNIX versions (paths may be different).

root in [~]# touch /var/log/slow
root in [~]# chown mysql:mysql /var/log/slow
root in [~]# mysql -e 'SET GLOBAL slow_query_log=1;'
root in [~]# mysql -e 'SET GLOBAL slow_query_log_file="/var/log/slow";'
root in [~]# mysql -e 'SET GLOBAL long_query_time=2;'

If you want to leave slow query logging on (recommended), you will need to add 3 lines to my.cnf under the [mysqld] section:

slow_query_log=1
slow_query_log_file=/var/log/slow
long_query_time=2

To test if it is working correctly (you should test after changing things, I promise it's a good idea) - simply run a SELECT SLEEP(x) query where x is longer than the long_query_time you have set - the query should be logged to the file you specified for slow_query_log_file (as long as it exists and the mysql user can access it).

# mysql -e 'SELECT SLEEP(5);'

Note: you don't have to have any databases or tables to use SLEEP().

Here is what it looks like if everything worked correctly. If not, make sure that the log file you specified exists and mysql user has access, and also that your GLOBALs are set correctly.

Here is a summary of all of the different ways to turn on slow query logging in various versions of MySQL:

• Before 5.1.6, start mysqld with the --log-slow-queries[=file_name] option.
• In versions after MySQL 5.1.6, you can log a file or a table, or both. Start mysqld with the --log-slow-queries[=file_name] option and optionally use --log-output to specify the log destination.
• After MySQL 5.1.12, use --slow_query_log[={0|1}] - 1 is on and the default slow query log file name is used.
• After MySQL 5.1.29, use --slow_query_log[={0|1}] - the --log-slow-queries option is deprecated.
• When using slow_query_log=1 (either in my.cnf or starting with mysqld --slow_query_log=1) use slow_query_log_file=/your/log/file or just leave it as the default ($hostname-slow.log in the mysql data folder).

More info about slow query logging at the official MySQL Reference.

If you want to enable slow query logging without restarting mysql on MySQL 5.0, you can - use this patch

Need help with MySQL Performance Tuning or Query Optimization? Call A Developer.

Peter Stolmar is a Linux Systems Administrator and technology optimization specialist. He can help you fix websites and apps, or make then run faster and scale better. Need help with some tech stuff? Find Peter Stolmar on Google+.