When its comes to optimizing and tuning Mysql the most important aspect is to identify the inefficient/slow queries.
So the question arises how we can find the queries which are taking long time to execute so we can optimize/improve them to improve the overall performance.
Mysql helps us with its built in support for logging slow queries.

Activating the slow query logging :

We need check if slow query loggin is already enabled or not , it can be checked as below :

mysqladmin var |grep log_slow_queries
| log_slow_queries | OFF

If its already set to ON then you are set, if its set to OFF like above then you will need to enable slow query logging.

The mysql variable long_query_time (default 1) defines what is considered as a slow query. In the default case, any query that takes more than 1 second will be considered a slow query.

Now to enable the slow query logging we will need following entries in the /etc/my.cnf mysql configuration file.

[mysqld]
long_query_time = 1
log-slow-queries = /var/log/mysql/mysql-slow.log

You can define the path for logging according to your requirements. Also the log query time which is by default 1 sec can be adjusted according to your needs.

Once you have done the configuration, restart mysql service to load the new configurations.

Once slow query logging is enabled we can check the log file for each slow query that was executed by the server.
Different details are logged to help you understand how was the query executed:

Time:  the time it took to execute the query
Lock:  how long was a lock required
Rows: how many rows were investigated by the query
Host: this is the actual host that launched/initiated the query
Query: The actual mysql query.

This information will help us to see what queries need to be optimized.

Tags: , , ,

2 Comments on How To : Enable slow query log in mysql

  1. […] lower loads on your servers. I wrote about enabling slow query logging in one of my earlier post here , you can check it to see the steps for enabling slow query logging. Thirdly the use of some tuning […]

  2. Raja says:

    This is a nice article / solution for the problem which i’m facing currently. thanks again

Leave a Reply