Applicable to:
- Plesk for Linux
Question
How to enable the MySQL/MariaDB slow query log and analyze it?
Answer
-
Connect to a Plesk server via SSH.
-
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 ofslow_query_log_file
.CONFIG_TEXT: 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.
-
-
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 -
Restart the MySQL/MariaDB service:
# service mysql restart
# service mariadb restart
-
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.
Comments
0 comments
Please sign in to leave a comment.