This article will guide you on how to enable the slow Query Log in MySQL or MariaDB. If you enable the slow query log for MySQL® or MariaDB then it’s a useful tool to monitor the performance and efficiency issues affecting the server.
By scanning the queries that are particularly slow in their execution, you can label them restricting the application that triggers the queries. You can reassemble the queries themselves to ensure that they are contracted as efficiently as possible.
For more information about the MySQL slow query log, MySQL 5.7 Reference Manual: The Slow Query Log documentation.
Enable the Slow Query Log
- To enable the Slow Query Log for MySQL or MariaDB:
- Open the my.cnf file with a text editor and add the following block of code under the mysql section:
slow_query_log = 1
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2
- In MySQL 5.6 and older, use the log-slow-queriesvariable instead of the slow-query_log_file
- In MariaDB 10.11 and later, they renamed the slow_query_logvariable to the log_slow_query
- In MariaDB 10.11 and later, they renamed the long_query_timevariable to the log_slow_query_time
- Create the /var/log/mysql-slow.log file and set its user as the mysql user. To implement this, type the following commands:
touch /var/log/mysql-slow.log
chown mysql:mysql /var/log/mysql-slow.log
- For rebooting the MySQL or MariaDB. Perform the following command.
/usr/local/cpanel/scripts/restartsrv_mysql
- Start checking 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
Check in the dept list of options to use with the mysqldumpslow command, and read MySQL’s mysqldumpslow article.