{"id":13391,"date":"2023-12-26T02:33:26","date_gmt":"2023-12-26T09:33:26","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=13391"},"modified":"2023-12-26T02:38:21","modified_gmt":"2023-12-26T09:38:21","slug":"mysql-slow-query-logs","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-slow-query-logs\/","title":{"rendered":"MySQL Slow Query Logs"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about MySQL slow query logs, configurations, and the mysqldumpslow<\/code> utility to examine slow queries.<\/p>\n\n\n\n

Introduction to MySQL slow query logs<\/h2>\n\n\n\n

When you execute queries that take longer than a specified threshold, MySQL treats them as slow queries. <\/p>\n\n\n\n

Subsequently, MySQL records these slow queries in logs called slow query logs<\/strong>. <\/p>\n\n\n\n

These slow query logs are a crucial tool to help you analyze and optimize your database performance. <\/p>\n\n\n\n

MySQL slow query log configurations<\/h2>\n\n\n\n

1) Checking the current configuration<\/h3>\n\n\n\n

To check the current slow query log configuration, you follow these steps:<\/p>\n\n\n\n

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the MySQL server:<\/p>\n\n\n

mysql -u root -p<\/code><\/span><\/pre>\n\n\n
Second, check the slow query log variables:<\/p>\n\n\n
SHOW VARIABLES LIKE '%slow_query%'<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
\r+---------------------+----------------------------------+\r\n| Variable_name       | Value                            |\r\n+---------------------+----------------------------------+\r\n| slow_query_log      | OFF                              |\r\n| slow_query_log_file | \/var\/<\/span>lib\/mysql\/mysql-slow.log    |\r\n+---------------------+----------------------------------+\r\n2<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The value of slow_query_log<\/code> indicates whether the slow query log is enabled. If set to ON<\/code>, it means the slow query log is enabled; If set to OFF<\/code>, it means the slow query log is disabled.<\/p>\n\n\n\n
MySQL disables the slow query logs by default. Therefore, the value of the slow_query_log<\/code> option is OFF<\/code>.<\/p>\n\n\n\n
The slow_query_log_file<\/code> option specifies the log file where MySQL records the slow queries.<\/p>\n\n\n\n
2) Setting up a slow query log file<\/h3>\n\n\n\n
First, make a new directory called mysql<\/code> in the \/var\/log\/<\/code> directory:<\/p>\n\n\n
mkdir -p \/var<\/span>\/log\/mysql\/<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create a new file called mysql-slow.log<\/code> inside the \/var\/log\/mysql\/<\/code> directory:<\/p>\n\n\n
touch \/var<\/span>\/log\/mysql\/mysql-slow.log<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, change permission for the mysql<\/code> user to the directory:<\/p>\n\n\n
chown -R mysql:mysql \/var<\/span>\/log\/mysql\/<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
3) Enabling slow query logs<\/h3>\n\n\n\n
To enable slow query logs, modify the MySQL configuration file by adding the following lines:<\/p>\n\n\n
[mysqld]\nslow_query_log = 1<\/span>\r\nslow_query_log_file = \/var\/<\/span>log\/mysql\/mysql-slow.log\r\nlong_query_time = 1<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
In this file:<\/p>\n\n\n\n