MySql Slow Queries

MySQL has a slow query log which can be enabled if you want to identify the queries that are causing problems for a website or application. Checking slow queries is one of the most important steps in optimizing and tuning mysql.

This article shows how to identify those 'slow queries' that need special attention and proper optimization.

First let’s check on the server if slow query logging is enabled on MySql.

On Windows
cd c:\xampp\mysql\bin
bin>mysqladmin var | find "log_slow_queries"

On linux 
bin>mysqladmin var |grep log_slow_queries

Enable the slow query log

MySQL prior to 5.1.0 requires a change to the MySQL my.cnf file and a restart in order to log slow queries. From MySQL 5.1.0 you can apparantly change this dynamically without having to restart.

Too make the change permanent, you could add the following lines under the [mysqld] section of your my.ini or my.cnf configuration file:

 >vi /etc/my.cnf
 [mysqld]
 # Activate the Slow Query Log
 slow-query-log = 1
  
 # Log all queries taking more than 3 seconds
 long_query_time=5 # minimum: 1, default: 10

 # Write log to a custom file name
 slow-query-log-file=/var/log/mysql/log-slow-queries.log  #default: /data_dir/host_name-slow.log

if you do not specify the slow-query-log-file then the default value is host_name-slow.log.

If you specify the slow-query-log-file, then you must create the file manually and change owners:

 > mkdir /var/log/mysql
 > touch /var/log/mysql/log-slow-queries.log
 > chown mysql.mysql -R /var/log/mysql

Slow queries are written into the slow_log files and you can check it in a classic MySQL table of default mysql database.

Process the slow query log using the mysqldumpslow command to summarize the queries.

 

#To get top ten slow query in file /tmp/top_ten_slow_query.txt
bin>mysqldumpslow -t 10 /var/log/mysql/log-slow-queries.log > /tmp/top_ten_slow_query.txt