How to Enable the Slow Query Log in MySQL® or MariaDB

Overview

Enabling the Slow Query Log for MySQL or MariaDB is an effective method to diagnose and resolve performance and efficiency issues on your server. By identifying queries that exhibit prolonged execution times, you can optimize them either by restructuring the application that issues these queries or by refining the queries themselves for maximum efficiency.

For more detailed information regarding the MySQL slow query log, please refer to the MySQL 5.7 Reference Manual: The Slow Query Log documentation.

Enable the Slow Query Log

Follow these steps to enable the Slow Query Log for MySQL or MariaDB:

1. Log in to your server as the root user via SSH.
2. Open the my.cnf file using a text editor and insert the following configuration block under the [mysqld] section:

   slow_query_log = 1
   slow-query_log_file = /var/log/mysql-slow.log
   long_query_time = 2

   • For MySQL 5.6 and earlier versions, use the log-slow-queries variable instead of slow-query_log_file.
   • In MariaDB 10.11 and subsequent versions, the slow_query_log variable has been renamed to log_slow_query.
   • Similarly, in MariaDB 10.11 and later, the long_query_time variable has been renamed to log_slow_query_time.
3. Create the /var/log/mysql-slow.log file and assign its ownership to the mysql user by executing the following commands:

   touch /var/log/mysql-slow.log
   chown mysql:mysql /var/log/mysql-slow.log

4. Restart MySQL or MariaDB using the following command:

   /usr/local/cpanel/scripts/restartsrv_mysql

5. Begin monitoring the slow query logfile. To analyze and generate a summary of the file's contents, use the mysqldumpslow command. For instance, to display all slow queries previously recorded by the system, run this command:

   mysqldumpslow -a /var/log/mysql-slow.log

For a comprehensive list of options available with the mysqldumpslow command, please consult MySQL's mysqldumpslow documentation.