Enabling the slow query log in MySQL

Enabling the Slow Query Log in MySQL

This guide explains how to activate the MySQL slow query log, which helps you identify queries that take unusually long to run and may be affecting your database or server performance.

Important
Root access is required to complete these steps. If you are on a Managed VPS or Managed Dedicated Server without root access and need the slow query log enabled, open a support ticket at https://my.ruachost.com.

Note
Details about unmanaged dedicated servers apply only to customers who purchased those plans before May 27, 2024, as these plans are no longer offered.

Why Use the Slow Query Log
The slow query log records any database query that exceeds a specified execution time. This makes it easier to detect inefficient queries that may slow down your system.

How to Enable the Slow Query Log

1. Log in to your server through SSH.

2. At the command prompt, enter:

   mysql -u root -p

3. Enter the MySQL root password.

4. At the mysql> prompt, turn on the slow query log:

   SET GLOBAL slow_query_log = 'ON';

Adjusting Slow Query Log Settings

• Change the time threshold:
  By default, MySQL logs queries running longer than 10 seconds. To set a different threshold (replace X with seconds):

  SET GLOBAL long_query_time = X;

• Change the log file location:
  The default log file is /var/lib/mysql/hostname-slow.log. To set a custom path and filename:

  SET GLOBAL slow_query_log_file = '/path/filename';

Testing the Slow Query Log

1. Exit MySQL and log in again to refresh session variables.

2. Run a test query that waits longer than the configured long_query_time (replace X with a value above your threshold):

   SELECT SLEEP(X);

3. Check the slow query log file to confirm that the query was recorded.

After Troubleshooting

Disable the slow query log when you no longer need it:

SET GLOBAL slow_query_log = 'OFF';

Important
The slow query log should only remain active while diagnosing performance issues.

Additional Resources
For more details about the MySQL slow query log, visit the official MySQL documentation: https://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html