How to enable the MySQL/MariaDB slow query log and analyze it on a Plesk for Linux server

Question

How to enable the MySQL/MariaDB slow query log and analyze it?

Answer

1. Connect to a Plesk server via SSH.

2. Enable the MySQL/MariaDB slow query log in the MySQL/MariaDB configuration file my.cnf:

   2.1. Open the my.cnf file in a text editor. In this example, we are using the vi editor:

   • on CentOS/RHEL-based distributions

     # vi /etc/my.cnf

   • on Debian/Ubuntu-based distributions

     # vi /etc/mysql/my.cnf

   2.2. Add the records below under the [mysqld] section:

   Note: For MySQL 5.6/MariaDB 5.5 and older versions, use log-slow-queries instead of slow_query_log_file.

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

   • where long_query_time - time taken by an SQL query to be executed in seconds. If a query takes longer than the value specified, this query will be recorded in the slow query log file.

   2.3. Save the changes and close the file.

3. Create the slow query logfile /var/log/mysql-slow.log and adjust ownership on it:

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

4. Restart the MySQL/MariaDB service:

   # service mysql restart

   # service mariadb restart

5. Start monitoring the slow query logfile. Use the command mysqldumpslow to analyze it and print summary of the slow query logfile.
   For example, to print all slow queries that have already been recorded, run the command:

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

   The full list of mysqldumpslow options is available here.

Additional Information

To learn more about the MySQL slow query log, visit MySQL 5.7 Reference Manual: The Slow Query Log.