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
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:
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