Enabling the slow query log in MySQL

This article describes how to enable the MySQL slow query log. You can use the slow query log to help determine which database queries take a long time to run.

🚧

Important

You must have root access to the server to follow these procedures. If you have a Managed VPS or Managed Dedicated Server (and do not have root access) and want to enable the slow query log, please open a support ticket at https://my.hosting.com.

📘

Note

Information in this article about unmanaged dedicated servers is only for customers who purchased those plans before May 27, 2024. As of that date, unmanaged dedicated servers are no longer available. Our new managed dedicated server plans feature more powerful hardware to meet your hosting needs. To view available dedicated hosting options, please visit https://hosting.com/hosting/dedicated-hosting/.

Enabling the slow query log

Slow queries can affect database performance and overall server performance. The slow query log feature in MySQL enables you to log queries that exceed a predefined time limit. This greatly simplifies the task of finding inefficient or time-consuming queries.

To enable the slow query log in MySQL, follow these steps:

1. Log in to your server using SSH.

2. At the command line, type the following command:

   Shell

   mysql -u root -p
   

3. Type the MySQL root password.

4. To enable the slow query log, type the following command at the mysql> prompt:

   SQL

   SET GLOBAL slow_query_log = 'ON';
   

5. There are additional options that you can set for the slow query log:

   • By default, when the slow query log is enabled, it logs any query that takes longer than 10 seconds to run. To change this interval, type the following command, replacing X with the time in seconds:

     SQL

     SET GLOBAL long_query_time = X;
     

   • By default, the slow query log file is located at /var/lib/mysql/hostname-slow.log. To change the log path or filename, type the following command, replacing path with the path to the file, and filename with the name of the log filename:

     SQL

     SET GLOBAL slow_query_log_file = '/path/filename';
     

6. To verify that the slow query log is working correctly, log out of the mysql program, and then log back in. (This reloads the session variables for the mysql program.) Type the following command, replacing X with a value that is greater than the long_query_time setting:

   SQL

   SELECT SLEEP(X);
   

   The slow query log file should contain information about the query.

7. Continue to monitor the slow query log file to see which queries take a long time to run.

8. When you are done troubleshooting, disable the slow query log. To do this, run the mysql program again, and then type the following command:

   SQL

   SET GLOBAL slow_query_log = 'OFF';
   

   🚧

   Important

   You should only enable the slow query log for as long as it is necessary to troubleshoot performance issues.

More Information

For more information about the MySQL slow query log, please visit https://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html.

Related Articles

• Connecting to MySQL from the command line

• MySQL runs out of memory or does not start

• Repairing MySQL databases and tables

• Managing MySQL databases, users, and tables from the command line