Enable the slow query log in MySQL

Rob

Administrator
Staff member
Joined
Oct 27, 2011
Messages
1,207
Reaction score
2,239
Credits
3,467
If you notice some slowness on your application (ie: website) and are looking to speed things up you can enable slow query logging on your server and inspect some of the slow queries. Slow queries are database queries that take longer than the amount of time you specify in the file. Most people start at 1 or 2 seconds and let that cook a day or two.

Once you start getting some data in your log file, you can use mysqldumpslow to parse it into more useful data if you have a lot of slow queries.

The syntax to enable the slow query log in MySQL changes by version. Let's find out which version you're running.

Run the following command to find the version:
Code:
mysql --version

It should output something like:
Code:
Server version: 5.5.54-cll MySQL Community Server (GPL)

Let's enable it now..

Edit your MySQL config file (/etc/my.cnf) and add the following lines, being careful to use the one specific to your version. Note that 'long_query_time' can be set to however many seconds you'd like to use.

MySQL 5.6 and after:
Code:
long_query_time=1
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow-query.log

MySQL 5.1.6 up to 5.5.x:
Code:
log_slow_queries = 1
long_query_time=1
slow_query_log_file =/var/log/mysql/slow-query.log

MySQL previous to 5.1.6:
Code:
long_query_time = 1
log_slow_queries=/var/log/mysql/slow-query.log

Next, create the directory and chown it to the correct owner:
Code:
mkdir /var/log/mysql
chown mysql.mysql /var/log/mysql

Then, reload MySQL to read in the changes:
Code:
service mysql reload

You should see something like:
Code:
Success! MySQL running (22344)
 


Top