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 themselves to ensure that they are constructed as efficiently as possible.
For more information about the MySQL slow query log, read the MySQL 5.7 Reference Manual: The Slow Query Log documentation.
Procedure
- Log in to your server as the
root
user via SSH. - Open the
my.cnf
file with a text editor and add the following block of code under themysqld
section:
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.
- Create the
/var/log/mysql-slow.log
file and set its user as themysql
user. To do this, run the following commands:
touch /var/log/mysql-slow.log
chown mysql:mysql /var/log/mysql-slow.log
- Restart MySQL or MariaDB. To do this, run the following command:
/usr/local/cpanel/scripts/restartsrv_mysql
- 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
For a complete list of options to use with the mysqldumpslow
command, read MySQL’s mysqldumpslow article.