Have you ever encountered a performance bottleneck in the operation of a MySQL database, or have slow logs become an important reference metric when looking for optimization solutions?Today, I'm going to take you to an in-depth look at the mysteries of MySQL slow logs, from the opening of each version to the query, so that you can easily master this powerful performance analysis tool.
1. What is a slow log?
A slow log is a type of log used in MySQL to record SQL statements that take a long time to execute. It helps us identify and optimize performance issues and locate SQL statements that may be causing slow database responses.
Part 2: How Do I Enable Slow Logging?
In MySQL, you can enable the slow logging feature by modifying the configuration file. There may be some differences between different versions of MySQL, which are described below.
1️⃣mysql 5.Version 6 and below: In myCNF (or MY.)ini) configuration file, find the [mysqld] section and add the following line:
2️⃣mysql 5.Version 7 and above: Also in myCNF (or MY.)ini) configuration file, find the [mysqld] section and add the following line:
Restart the MySQL service for the configuration to take effect. The long query time parameter indicates the maximum time threshold for slow queries, in seconds. It can be adjusted according to actual needs.
3. Whether the slow log is enabled and the location of the log file.
Run the following command to check whether slow logging is enabled and the location of the log file:
4. How do I query slow logs?
After you enable slow logging, you can query and analyze log files in the following ways:
1 Use the mysqladmin command-line tool to run the following command to view the content of the slow log file, and enter the password to view and analyze the content of the slow log.
2 Use MySQL's built-in tools: MySQL comes with a tool called MysqlDumpSlow, which can be used to analyze slow log files. Execute the following command:
This command will sort the order by execution time and output the slow query statement and its related information. You can use other parameters to filter and sort as needed.
I'm a tech creator