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

Introduction

Enabling the Slow Query Log for MySQL or MariaDB can be a useful tool to diagnose performance and efficiency issues affecting your server. By identifying queries that are particularly slow in their execution, you can address them by restructuring the application that triggers your queries. You can also rebuild the queries to ensure they are constructed as efficiently as possible.

Procedure

1. Access the server's command line as the 'root' user via SSH or "Terminal" in WHM.
2. Open the my.cnf file with a text editor and add the following block of code under the mysqld section:

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

   Please note that MySQL 5.6 and older use the log-slow-queries variable instead of the slow-query_log_file variable.
   Please note that on Ubuntu servers, the mysql user cannot write to the /var/log folder. The log file needs to be placed at /var/log/mysql/mysql-slow.log instead.
3. Create the mysql-slow.log file.
   • Non-Ubuntu servers:

     touch /var/log/mysql-slow.log

   • Ubuntu servers:

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

4. Set the ownership of the mysql-slow.log file to the mysql user.
   • Non-Ubuntu servers:

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

   • Ubuntu servers:

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

5. Restart MySQL or MariaDB.

   /usr/local/cpanel/scripts/restartsrv_mysql

MySQL users will run the command mysqldumpslow to analyze and print the file's summary. While MariaDB users will use the mariadb-dumpslow command. For example, run the following command to print all slow queries that the system previously recorded.

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

mariadb-dumpslow -a /var/log/mysql-slow.log

Additional resources

MySQL 5.7 Reference Manual: The Slow Query Log

mysqldumpslow — Summarize Slow Query Log Files

MariaDB Slow Query Log Overview

MariaDB mariadb-dumpslow