Friday, September 18, 2009

To enable slow query logging of mysql

SkyHi @ Friday, September 18, 2009
MySQL has built-in functionality that allows you to log SQL queries to a file , You can enable the full SQL queries logs to a file or only slow running queries log. It is easy for us to troubleshoot/ debug the sql statement if SQL queries log enable , The slow query log is used to find queries that take a long time to execute and are therefore candidates for optimization.

To enable you just need to add some lines to your my.cnf file, and restart. Add the following:

To enable slow Query Log only
log-slow-queries = /var/log/mysql/mysql-slow.log
touch /var/log/mysql/mysql-slow.log
chown mysql:mysql /var/log/mysql/mysql-slow.log
long_query_time = 1

service mysqld restart

After enabling slow query, mysqld writes a statement to the slow query log file and it consists of all SQL statements that took more than long_query_time seconds to execute. The time to acquire the initial table locks is not counted as execution time. mysqld only log after SQL statements has been executed and after all locks have been released, so log order might be different from execution order. The minimum and default values of long_query_time are 1 and 10, respectively.

To enable full Log Query

  touch /var/log/mysqlquery.log 
 chown mysql:mysql  /var/log/mysqlquery.log 
 chmod 640 /var/log/mysqlquery.log 
 service mysqld restart

The above will log all queries to the log file.