How to Enable the Slow Query Log in MySQL/MariaDB

Enabling the Slow Query Log for MySQL or MariaDB can be a useful tool to diagnose performance and efficiency issues affecting your server such as MySQL high CPU load. 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 themselves to ensure that they are constructed as efficiently as possible.

Enable the Slow Query Log

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 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

   1 2 3

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

   Note:

   In MySQL 5.6 and older, use the log-slow-queries variable instead of the slow-query_log_file variable.

    

3. Create the /var/log/mysql-slow.log file and set its user as the mysql user. To do this, run the following commands:

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

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

    

4. Restart MySQL or MariaDB. To do this, run the following command:

   /usr/local/cpanel/scripts/restartsrv_mysql

   1	/usr/local/cpanel/scripts/restartsrv_mysql

    

5. Start monitoring the slow query logfile. To analyze and print the file’s summary, run the mysqldumpslow command. For example, to print all slow queries that the system previously recorded, run the following command:

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

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

    

For a complete list of options to use with the mysqldumpslow command, read MySQL’s mysqldumpslow article.