Cofigure Slow MySQL Queries and full MySQL Queries LOG

We will need to optimize and tune MySQL to identify the queries that are causing the problems. How can we find out what queries are taking a long time to complete? How can we see what queries are slowing down the mysql server.

If we take the most expensive 10 queries and we optimize them properly (maybe running them more efficiently, or maybe they are just missing a simple index to perform properly), then we will immediately see the result on the overall mysql performance. Then we can iterate this process and optimize the new top 10 queries.

Activate the logging of mysql slow queries :

The first step is to make sure that the mysql server will log slow queries and to properly configure what we are considering as a slow query.

Firstly, check on the mysql server if we have slow query logging enabled ;

———————————————————————
# mysqladmin var |grep log_slow_queries

| log_slow_queries | OFF |
———————————————————————

If log_slow_queries is ON then we already have it enabled. This setting is disabled by default – meaning that if you don’t have log_slow_queries defined in the mysql server config this will be disabled.

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.

In order to do to do this in your mysql server config file(/etc/my.cnf in case of RHEL/Centos)in the mysqld section we need to add:

———————————————————————
[mysqld]
long_query_time = 1

log-slow-queries = /var/log/mysql/mysql-slow.log

log=/var/log/mysqldquery.log
———————————————————————

This configuration will log all queries that take more than 1 sec in the file /var/log/mysql/mysql-slow.log.

Once you have done the proper configurations to enable mysql to log slow queries, you will have to reload the mysql service in order to activate the changes.

Advertisements
  1. 🙂

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: