How to enable slow query log in mysql

In daily use of MySQL database, in order to improve query speed and efficiency, we usually use indexes and optimizers. But sometimes, we also need to know the specific execution of the query statement in order to better find the problem and optimize the database more accurately. At this time, you need to use MySQL's Slow Query Log function.

The slow query log can record all SQL statements whose execution time exceeds a certain threshold so that we can troubleshoot and optimize. This article will introduce how to enable the slow query log in the MySQL database, and explain in detail the relevant parameters and optimization methods of the slow query log.

1. Enable slow query log

1. Modify the configuration file

Add the following configuration to the MySQL configuration file my.cnf or my.ini:

slow_query_log = 1 #开启慢查询日志
slow_query_log_file = /var/log/mysql/mysql-slow.log #日志文件路径
long_query_time = 3 #超过3s即为慢查询
log_queries_not_using_indexes = 1 #记录未使用索引的查询

Among them, slow_query_log = 1 means turning on the slow query log, slow_query_log_file specifies the path and name of the slow query log file, long_query_time means how many seconds the query execution time exceeds before it is considered a slow query, log_queries_not_using_indexes means whether the record is not used Index query.

2. Use the SET command to dynamically modify the configuration

In addition to modifying the configuration file, we can also dynamically modify parameters through the SET command. For example, we can turn on the slow query log in the MySQL command line through the following statement:

mysql> SET global slow_query_log = 1;
mysql> SET global slow_query_log_file = '/var/log/mysql/mysql-slow.log';
mysql> SET global long_query_time = 3;
mysql> SET global log_queries_not_using_indexes = 1;

In the above code, SET global means that it will take effect globally, that is, all connections will use these parameters.

Of course, if you only want to set the parameters of a local connection, you can use the SET SESSION command. For example:

mysql> SET SESSION slow_query_log = 1;
mysql> SET SESSION slow_query_log_file = '/var/log/mysql/mysql-slow.log';
mysql> SET SESSION long_query_time = 3;
mysql> SET SESSION log_queries_not_using_indexes = 1;

2. Optimize the slow query log configuration parameters

1. slow_query_log_file

Before turning on the slow query log, we need to ensure that the specified log file The directory already exists and has write permission. If the directory does not exist, you need to create it first.

We can use the following command to view the current working directory of the MySQL process:

mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

As you can see from the above example, the working directory of the MySQL process is /var/lib/mysql/, and this There is a slow.log file in the logs directory under the directory. Therefore, if we want to write the slow query log to this file, we only need to add the following configuration to my.cnf:

slow_query_log_file = /var/lib/mysql/logs/slow.log

2. long_query_time

default long_query_time The value is 10 seconds, which means that only query statements that take more than 10 seconds to execute will be recorded in the slow query log. Under normal circumstances, this time may be a bit long. We can set it to a shorter time based on actual conditions to discover slow queries faster.

The reason for long execution of query statements is often that the statement itself is inefficient and usually needs to be optimized. In actual use, we can gradually reduce the long_query_time time to discover less efficient statements, optimize them, and improve query efficiency.

3. log_queries_not_using_indexes

If the log_queries_not_using_indexes parameter is set to 1, then all queries that are executed without using indexes will be logged. This can help us identify potential problems and optimize them in a timely manner. However, this may leave some false positives since queries without indexes are normal in some cases.

You need to be careful when using the log_queries_not_using_indexes parameter. It is recommended not to set it as a global variable. You can choose to set it dynamically when needed. Because if this parameter is always turned on, it may have a negative impact on the performance of MySQL, resulting in a decrease in the overall operating efficiency of the database.

3. View and analyze the slow query log

When we turn on the slow query log, MySQL begins to record all query statements whose execution time exceeds the threshold. We can use the following command to view the slow query log:

mysql> SHOW VARIABLES LIKE 'slow_query_log_file';
+---------------------+------------------------+
| Variable_name       | Value                  |
+---------------------+------------------------+
| slow_query_log_file | /var/lib/mysql/slow.log |
+---------------------+------------------------+
1 row in set (0.00 sec)

As can be seen from the above output, the current slow query log file path is /var/lib/mysql/slow.log.

If you want to view the specific content in the slow query log, you can use the following command:

mysql> mysqldumpslow -s t /var/lib/mysql/slow.log

Among them, -s means sorting by time, and -t means only displaying the first 10 records. If you want to view all query records, you can remove the -t parameter.

4. Optimize slow query log

Enabling slow query log is a very good way to help us discover and optimize performance problems. However, you also need to pay attention to the following points in actual use:

1. The size of the slow query log file will continue to increase, so it needs to be cleaned regularly. It is recommended to set max_slowlog_size to an appropriate value in my.cnf to avoid running out of disk space.
2. Logging slow query logs will have a negative impact on MySQL performance, so it is recommended to only enable necessary logging in the production environment. Normally, set the log level and only record key information.
3. You can use tools to analyze slow query logs, such as pt-query-digest, etc. These tools can help us better discover the causes of slow queries and optimize execution plans.

In short, it is a good habit to turn on the MySQL slow query log. In a production environment, we need to carefully configure parameters and use related tools to ensure database performance and stability.

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