Pick our brains...

MySQL Slow Query Log Changes Its Game (& Syntax)

The MySQL slow query log can be very insightful for tracking down slow-running queries that are bogging down your webserver. But more often than not, it’s not enabled by default. So let’s learn how to enable and configure it, so you can start examining your slow running queries yourself!

Ok, so we’re going to make some adjustments to the my.cnf configuration file for MySQL. You’re going to need root privileges to do this.

Once upon a time, you used to be able to use the following syntax in your my.cnf:

[mysqld]
##
# enable slow query log [turned on by LD on 4/13/2013]
#
log-slow-queries = /var/lib/mysql/mysql_slow.log
long_query_time = 3

But on newer MySQL distributions (starting about v.5.6), you’re going to get an error when restarting the MySQL daemon, similar to:

[root@host /var/log]# /etc/init.d/mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL... ERROR! The server quit without updating PID file (/var/lib/mysql/host.peacefulmedia.com.pid).

Upon inspection of the MySQL error log (in this case found in /var/lib/mysql/host.peacefulmedia.com.err), we find that it’s choking on the log-slow-queries variable:

[ERROR] /usr/sbin/mysqld: unknown variable 'log-slow-queries=/var/log/mysql_slow.log'

The newer syntax for setting up the MySQL slow query log in the my.cnf file looks like this:

[mysqld]
##
# enable slow query log [turned on by LD on 6/8/2015]
#
slow_query_log = 1
slow_query_log_file = /var/log/mysql_slow.log
long_query_time = 1
log_queries_not_using_indexes = 0

Make sure that the log file in question is writeable by the MySQL process, which in this case is the user named “mysql”. If you need to modify the ownership of the file, you can use:

chown owner:group filename

For the changes in your my.cnf to take effect, we’ll need to restart the MySQL daemon, so here we go!

/etc/init.d/mysql restart

Happy server tuning.
To your success!