- Plesk for Linux
How to enable the MySQL slow query log and analyze it?
Connect to a Plesk server via SSH.
Enable the MySQL slow query log in the MySQL configuration file
2.1. Open the file
my.cnfin any 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:
CONFIG_TEXT: slow_query_log = 1
log-slow-queries = /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.
Note: In MySQL 5.7, the variable log-slow-queries is deprecated. Use slow_query_log_file instead.
2.3. Save the changes and close the file.
Create the slow query logfile
/var/log/mysql-slow.logand set correct ownership on it:
# touch /var/log/mysql-slow.log
# chown mysql:mysql /var/log/mysql-slow.log
Restart MySQL. The command depends on operating system and installed MySQL version:
for CentOS/RHEL-based distributions:
# service mysqld restart
for Debian/Ubuntu-based distributions:
# service mysql restart
if MariaDB is installed:
# service mariadb restart
Start monitoring the slow query logfile. Use the command mysqldumpslow to analyze it and print the 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 see here.
To learn more about the MySQL slow query log, visit MySQL 5.7 Reference Manual: The Slow Query Log.