Home » Enable Slow Query Log Mysql

Enable Slow Query Log Mysql

  • by

Enable Slow Query Log Mysql in Version 5.1.6 and above:

1. Enter the MySQL shell and run the following command:

set global slow_query_log = 'ON';

2. Enable any other desired options. Here are some common examples:

Log details for queries expected to retrieve all rows instead of using an index:

set global log_queries_not_using_indexes = 'ON'
Set the path to the slow query log:

set global slow_query_log_file ='/var/log/mysql/slow-query.log';
Set the amount of time a query needs to run before being logged:

set global long_query_time = 20;
(default is 10 seconds)
3. Confirm the changes are active by entering the MySQL shell and running the following command:

show variables like '%slow%';
Versions below 5.1.6:

Edit the /etc/my.cnf file with your favorite text editor vi /etc/my.cnf

Add the following line under the “[mysqld]” section. Feel free to update the path to the log file to whatever you want:

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

3. Enable additional options as needed. Here are the same commonly used examples from above:

Set the amount of time a query needs to run before being logged:

`long_query_time=20
(default is 10 seconds)`
Log details for queries expected to retrieve all rows instead of using an index:

`log-queries-not-using-indexes`
4. Restart the MySQL service:

service mysqld restart
5. Confirm the change is active by entering the MySQL shell and running the following:

show variables like '%slow%';

For moe information refer the links below:

http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html

http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

Leave a Reply

Your email address will not be published.