Support

MySQL Slow Queries

Optimizing your database is an important part of improving an application's performance and reliability. Identifying queries that are taking an abnormally long time to execute (or longer than expected) is an import part of optimization. Fortunately, MySQL does offer the ability to identify and log queries that are slow.

Slow Query Log

Firstly, the log file must be created and assigned the proper permissions to allow mysql to writte.

# touch /var/log/mysql_slow_query.log
# chown mysql.mysql /var/log/mysql_slow_query.log

The slow query log can then be enable by simply adding the following line into MySQL's my.cnf file (/etc/my.cnf):



log_slow_queries = /var/log/mysql_slow_query.log
        

This will log all queries taking longer than 10 seconds to the set         file. If queries of a longer or shorter duration need to be logged, the         setting long_query_time can be adjusted. For example, the following         will set queries taking longer than 20 seconds to be logged:
 long_query_time = 20 
Be sure to restart mysqld to apply changes to my.cnf.
 # service mysqld restart 
Analyzing The Slow Query Log
The slow query data being logged can be quite cumbersome to read, especially when a lot of queries are logged. This is where the         mysqldumpslow command comes handy.
 # mysqldumpslow /var/log/mysql_slow_query.log 
mysqldumpslow parses the log file, and displays a summary of the         logged data.
Slow query log documentation