MySQL how to open MySQL slow query log and analysis tool mysqldumpslow

This article is about how MySQL opens the MySQL slow query log and the analysis tool mysqldumpslow. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. The slow log with MySQL enabled is simple, just add it to 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 is generally a data storage directory because it needs to be writable.

Long_query_time=10 unit of second. The default is 10, which means that only those with more than 10 seconds can be recorded.

Slow_launch_time=2-indicates that if it takes longer than this value to set up the thread, the slow_launch_threads counter will be incremented

Log_queries_not_using_indexes=OFF-A query set to ON indicates that the record does not use index. It is not enabled by default.

Note: view slow_launch_threads counters through show global status like'% slow%';.

two。 Set time:

Mysql > set long_query_time=5

Mysql > show variables like 'long%'

3. Slow query log analysis tool mysqldumpslow.

If there are a lot of records in the slow log, you can use the mysqldumpslow tool (included with the MySQL client installation) to classify and summarize the slow log. Mysqldumpslow classifies and summarizes the log files and displays the summary results.

$mysqldumpslow-s t-t 10-g "left join" / data01/mysqldata/3306/slow_query.log

Get the query statements with left links in the first 10 items sorted by time.

Using the mysqldumpslow command, we can clearly get all kinds of query statements we need. Monitoring, analysis and optimization of MySQL query statements is a very important step of MySQL optimization. When slow log is enabled, it will consume CPU resources and affect the performance of mysql to a certain extent due to logging operations, but it can be enabled periodically to locate the performance bottleneck.

Thank you for reading! On "MySQL how to open MySQL slow query log and analysis tool mysqldumpslow" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!