Articles in this section

See more

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

Avatar
Kuzma Ivanov
Updated
Follow
Plesk for Linux kb: how-to ABT: Group B

Applicable to:

  • Plesk for Linux

Question

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

Answer

  1. Connect to a Plesk server via SSH.

  2. Enable the MySQL slow query log in the MySQL 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.

    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.

  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.

 

 

Comments

8 comments

Sort by Date Votes
  • Avatar
    Marcos Martinez

    Hi, 

    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, 

    Regards.

    0
    Comment actions Permalink
  • Avatar
    Alexandr Redikultsev

    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*
    /etc/my.cnf:log-error=/var/log/mariadb/mariadb.log

    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 

    0
    Comment actions Permalink
  • Avatar
    Ralph Otowo (Edited )

    The following worked for me on Plesk Onyx 17.8.11 Update

    Update /etc/my.cnf

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

    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.

    1
    Comment actions Permalink
  • Avatar
    Maxim Krasikov

    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.

    0
    Comment actions Permalink
  • Avatar
    Sharul Hafiz

    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`

    0
    Comment actions Permalink
  • Avatar
    Alisa Kasyanova

    @Sharul Hafiz
    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"

    0
    Comment actions Permalink
  • Avatar
    Karl May

    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?

    0
    Comment actions Permalink
  • Avatar
    Francisco Roman Garcia Rodriguez

    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 :)

    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles