MySQL Optimization-enable MySQL slow query log and analysis tool mysqldumpslow

MySQL Optimization-enable MySQL slow query log and analysis tool mysqldumpslow

1. It is very easy to enable MySQL slow query logs. You only need to add the following in the MySQL configuration file my. cnf:
[Mysqld]
Slow_query_log = 1-enable slow query log
Slow_query_log_file =/data01/mysqldata/3306/slow_query.log -- slow query log storage location, which can be written as needed. Generally, it is the data storage directory.
Long_query_time = 10 seconds. The default value is 10, indicating that records exceed 10 seconds.

Slow_launch_time = 2-indicates that if the thread creation takes longer than this value, the slow_launch_threads counter will increase
Log_queries_not_using_indexes = OFF -- if it is set to ON, the query records do not use the index. It is disabled by default.

Note: run show global status like '% slow %'; to view the slow_launch_threads counter.

2. Set the time:
Mysql> set long_query_time = 5;
Mysql> show variables like 'long % ';

3. Slow query log analysis tool mysqldumpslow.
If the slow query log contains many records, you can use the mysqldumpslow tool (which is included in the MySQL client installation) to classify and summarize the slow query logs. Mysqldumpslow classifies and summarizes log files and Displays summary results.

$ Mysqldumpslow-s t-t 10-g "left join"/data01/mysqldata/3306/slow_query.log
Obtain the query statements containing the left join in the first 10 results sorted by time.

The mysqldumpslow command can be used to obtain various query statements that we need very clearly. Monitoring, analyzing, and optimizing MySQL query statements is an important step for MySQL optimization. After the slow query log is enabled, the log record operation may occupy CPU resources to some extent, affecting mysql performance. However, you can enable it to locate the performance bottleneck in stages.

This article permanently updates the link address: