MySQL Slow Query Log File

by lifeLinux on May 8, 2011

The slow query log consists of all SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. The time to acquire the initial table locks is not counted as execution time. mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might be different from execution order. The default value of long_query_time is 10. The minimum value is 0, and a resolution of microseconds is supported when logging to a file. However, the microseconds part is ignored and only integer values are written when logging to tables.
Source: http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html

To make the change permanent whenever the MySQL server is started, and for MySQL prior 5.1.x, edit your my.cnf file (on Linux boxes this is usually somewhere like /etc/my.cnf or /etc/mysql/my.cnf) and uncomment the “log_slow_queries” line or add it if it’s not present.
On a CentOS, type the following command

[root@server2 ~]# vi /etc/my.cnf

For example, the line to uncomment looks like so:

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

You can change the log file name to something else or leave it blank so it uses the default. The default is to log the queries into a file in the MySQL data directory.

Setting the long_query_time

You can also specify how long a quey needs to run for before it is logged with the “long_query_time” setting. By default this is 10 seconds. In the my.cnf file, to change it to e.g. 1 second add the following:

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

After that, restart mysql service. Enter the following command:

[root@server2 ~]# /etc/init.d/mysqld restart

Related Posts:

Previous post:

Next post: