How to enable the MySQL slow query log and analyze it on Linux

Follow

Comments

6 comments

  • 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.

  • 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 

  • 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.

  • 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.

  • 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`

  • 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"

Please sign in to leave a comment.

Have more questions? Submit a request