- 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
my.cnffile 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
Note: For MySQL 5.6/MariaDB 5.5 and older versions, use
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.logand 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.
To learn more about the MySQL slow query log, visit MySQL 5.7 Reference Manual: The Slow Query Log.
after doing the configuration, I get the following error from Mariadb.log "Could not use /var/log/mysql-slow.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it. plesk".
I think this error is about permission, but how can I grant permission to a mysql user over the "mysql-slow.log" file?
Thanks in advance,
Hello, @Marcos Martinez.
Basically doing the following chown should be enough to provide mysql user access to mysql-slow.log file:
# chown mysql:mysql /var/log/mysql-slow.log
However, I can imagine the situation when there are some limitations applied by the operation system.
I suggest the following action plan to troubleshoot the issue:
1. Try creating the file in the same directory where the initial error log of MySQL is located on the server. You can find it with the following command:
# grep -R 'log-error' /etc/my*
In my example, I would create a file in /var/log/mariadb/mysql-slow.log.
2. Adjust the steps in the article to use /var/log/mariadb/mysql-slow.log (or the other path that you will be using).
3. Verify that log-slow-queries as well as all the other related directives in my.cnf are indeed under [mysqld] section and not under [mysqld_safe].
4. Do not forget to execute chown on the file:
# chown mysql:mysql /var/log/mariadb/mysql-slow.log
The following worked for me on Plesk Onyx 17.8.11 Update
By default, the /var/log/mariadb directory is owned by mysql:mysql so simply restart MariaDB with service mariadb restart and it will create the file at the defined path and begin logging slow queries there. My issue was creating the file first with incorrect permissions so I simply let MariaDB create it according to its requirements.
Hello @Ralph Otowo,
Thank you for sharing your experience.
In general, it is recommended to create mysql-slow.log file and set permissions manually since older MySQL versions may not create the file automatically.
How do I get the database name from the list? Here is what I get which groups all the query in one line. The table name is the same for all WordPress.
Count: 8601 Time=15.97s (137355s) Lock=0.91s (7832s) Rows_sent=8.7 (75177), Rows_examined=8.7 (75177), 25users@localhost
SHOW FULL COLUMNS FROM `wp_wfBlocks7`
You may open the log (for example, with "less /var/log/mysql-slow.log") and find the needed query. Usually, the database name is specified before the query like "use database_name"
Enable mysql-slow.log is only one part. This article should include/link for logrotate this file. Is here already a article for logrotate mysql-slow.log?
Hi Karl May,
In my opinion, if you have to enable slow log, it's for troubleshooting purposes and will be temporary, not for permanent usage.
Anyway, this is something not managed by Plesk, as it's custom configuration.
Here you can find a way to rotate slow query log using Logrotate from the system: https://minervadb.com/index.php/2018/04/19/rotating-mysql-slow-query-log/
Warning: Do it on your own will and risk, as it's not supported :)
Please sign in to leave a comment.