How to enable MySQL slow query log

Slow query refers to a SQL statement whose execution time exceeds the scheduled time compared with the scheduled time. If slow queries are not deleted in time, the database running performance on the server may be greatly affected. Therefore, it is very important to enable the MySQL slow query log in a production environment.

MySQL Slow Query Log helps you publish detailed information about queries that are performing poorly. Will be very helpful when diagnosing and resolving performance issues in production environments. In this article, we will introduce in detail how to enable MySQL slow query log.

Step 1: Check the default path of the MySQL slow query log

We need to check the default path of the MySQL slow query log. On Linux systems, the MySQL slow query log is stored in the /var/lib/mysql/hostname-slow.log file.

Step 2: Edit the MySQL configuration file my.cnf

To enable the MySQL slow query log, we need to edit the MySQL configuration file. My my.cnf file is located in the /etc/mysql/ directory.

Use vim editor to open the my.cnf file.

$ sudo vim /etc/mysql/my.cnf

Step 3: Append the following content to the my.cnf file

Append the following content to the end of the file:

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/ hostname-slow.log
long_query_time = 2

In the above configuration,

• slow_query_log is a switch variable used to enable or disable the MySQL slow query log. Setting this to 1 enables MySQL slow query logging.
• slow_query_log_file is used to specify the location and name of the MySQL slow query log file.
• long_query_time is an integer that specifies the time required for a query to be considered a slow query. In this example, we set it to 2 seconds. In other words, if the execution time exceeds 2 seconds, it will be recorded as a slow query.

After editing the my.cnf file, save and close the file.

Step 4: Restart the MySQL service

Restart your MySQL service for the changes in the my.cnf file to take effect.

$sudo service mysql restart

Step five: Check the MySQL slow query log

To check the MySQL slow query log, enter the following command:

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

If the MySQL slow query log is enabled, then output The results are as follows:

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+

We can check the slow query log file using the following command:

$ sudo cat /var/lib/mysql/ hostname-slow.log

If the query exceeds long_query_time, it is logged in the file.

Conclusion

Turning on the MySQL slow query log can provide detailed information about query execution time, which is very helpful for diagnosing and solving performance problems in internal production systems. This article introduces how to enable MySQL slow query log on Linux systems. Before enabling the MySQL slow query log, be sure to test in a test environment and do enough testing to ensure that the feature will not negatively impact your production database.

The above is the detailed content of How to enable MySQL slow query log. For more information, please follow other related articles on the PHP Chinese website!