Praji’s Weblog

A query is considered as slow if it takes more than “long_query_time” seconds to execute. By default, the value of this variable is set to “1″.

1). Identifying MySQL slow queries
************************************

First let’s check on the mysql server if we have slow query logging enabled:

mysqladmin var |grep log_slow_queries | log_slow_queries | OFF

2) . Activate the logging of mysql slow querie
************************************************
MySQL allows  to log slow running queries to a file. To enable slow query logging, just add the following lines to /etc/my.cnf, and restart mysql.

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

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

Another alternative for enabling this feature is to restart mysql with the –log-slow-queries[=file_name] option.

2. Investigate the mysql slow queries log
*******************************************

MySQL gives us a small tool that does exactly this: mysqldumpslow

For example using:

mysqldumpslow -s c -t 10

We get the top 10 queries (-t 10) sorted by the number of occurrences in the log (-s c).