If you run or administer a production MySQL server it’s quite likely that it sometimes runs a little slow, and can be a very time consuming effort to figure out why. To be able to pinpoint the query that slows the DB down can be hard to track down. MySQL has an option to help you out. It comes with an option to log slow queries. This way when you find your database server running slower than usual you can open this log file in a text editor and see what queries are running slow. Let’s look at how to set this up.

First open your MySQL server’s configuration file, my.cnf. On Linux this should be in the /etc directory. Look to see if the parameter log-slow-queries has already been set. If not, then set it to something like this:

log-slow-queries = /var/log/mysql/mysql-slow.log

You also need to set the length of time before a query is fit to be considered a slow query. On my server I have set it to 30 seconds, like this:

long_query_time = 30

Restart your MySQL server, and then watch the mysql-slow.log file. Whenever a query takes longer to complete than 30 seconds, or whatever you set in my.cnf, the query will be logged in mysql-slow.log. Now debugging MySQL queries should be a lot simpler and quicker for you. Do remember to check this file every once in a while. What might help is to setup a cron job to mail you this file once every few days.