Log Slow MySQL Queries

MySQL can often use excessive CPU cores or time out unexpectedly. The slow-query-log can assist in finding queries, making it much easier to debug your application or website.

Please note: this applies to MySQL 5.5 and above. Older versions use a different method to activate the slow-query-log. Enable slow-query-log by uncommenting or adding the following lines in /etc/mysql/my.cnf:

slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes

The slow query log consists of SQL statements that took more than long_query_time seconds to execute. The minimum and default values of long_query_time are 1 and 10, respectively.

The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimisation. However, examining a long slow query log can become a difficult task. To make this easier, you can process a slow query log file using the mysqldumpslow command to summarise the queries that appear in the log:

mysqldumpslow /var/log/mysql/mysql-slow.log

The following command will display the top five queries which returned maximum rows. It can also find queries where the LIMIT clause has been left out, which is a common cause of poor performance:

mysqldumpslow -a -s r -t 5 /var/log/mysql/mysql-slow.log

  • 2 Users Found This Useful
Was this answer helpful?

Related Articles

cPanel Server Time

Setting the server timezone in cPanel is very easy. Having the correct server time helps when...

Dovecot Is Not Running on cPanel

Quite a common error with Dovecot running on Xen hypervisors, especially for clients who use...

mySQL Errcode: 24

The mySQL error: Errcode: 24 is an indication that the number of files that mySQL is allowed to...

CentOS Change Hostname

One of the most common changes to CentOS servers is modifying the hostname. Hostnames are...

Swap Disk Size

What is swap? Swap space is the area on a hard disk which is part of the Virtual Memory of your...