How to locate MySQL slow queries

Preface

I believe that everyone has had experience in SQL optimization in their daily work. Therefore, before optimization, you must find the slow SQL and analyze it. This article describes how to locate slow queries.

The slow query log is a built-in feature of MySQL that records SQL statements that take longer than a specified time to execute.

The following are the parameters related to slow query. You can take a look if you are interested:

Enable slow query

There are two ways to enable slow query

1. Modify the configuration file
2. Setting Global Variables
   

Method 1 requires modifying the configuration file my.ini and adding the following parameters to the [mysqld] section:

[mysqld]
log_output='FILE,TABLE'
slow_query_log='ON'
long_query_time=0.001

Then you need to restart MySQL for it to take effect. The command is service mysqld restart

Method 2 can take effect without restarting, but restarting will cause the settings to become invalid. The setting commands are as follows:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL log_output = 'FILE,TABLE';
SET GLOBAL long_query_time = 0.001;

This way, the slow query log can be recorded in both the file and the mysql.slow_log table.

Use the second method to enable the slow query log, and then use the full table query statement SELECT * FROM user

Then query the slow query log: SELECT * FROM mysql.slow_log , and you can find the following record:

slow_log

Among them, start_time is the execution time, user_host is the user's host name, query_time is the time spent on the query, lock_time is the time the query uses the lock, rows_sent is the amount of data returned to the client by this query, rows_examined indicates how many rows this statement scanned, db is the database, sql_text is this SQL, and thread_id is the thread id that executes this query.

In this way, we can analyze the data in the slow_log table and then tune the SQL.

The above is an analysis through Table. The following is an analysis of slow queries through files.

If you don't know where the file is saved, you can use SHOW VARIABLES LIKE '%slow_query_log_file%' to view the file save location. Open the slow query log file, you can see that every five lines represent a slow SQL. This is more troublesome to view, so you can use some tools to view it.

Slow query log file

mysqldumpslow

MySQL has a built-in tool called mysqldumpslow to help us analyze slow query log files. To use this tool in a Windows environment, you need to install the Perl environment.

You can view its command parameters through -help:

mysqldumpslow help

For example, we can use mysqldumpslow -st 10 LAPTOP-8817LKVE-slow.log command to get 10 SQL statements sorted by query time.

mysqldumpslow results

pt-query-digest

In addition, there is pt-query-digest, which is one of the tools in Percona Toolkit. The download address is: https://www.percona.com/downloads/percona-toolkit/LATEST/. If it is a Windows system, you can download the script in the environment where Perl is installed: https://raw.githubusercontent.com/percona/percona-toolkit/3.x/bin/pt-query-digest

The following is a brief introduction to pt-query-digest:

pt-query-digest is a third-party tool for analyzing MySQL slow queries. It can analyze binlog, general log and slowlog. It can also analyze MySQL protocol data captured by showprocesslist or tcpdump. The analysis results can be output to a file. The analysis process is to first parameterize the query statement conditions, and then group and count the parameterized queries. The execution time, number of times, proportion, etc. of each query are counted. The analysis results can be used to find problems and optimize them.

If you are interested, you can download it and play with it yourself. The pt-query-digest tool will be introduced in detail in subsequent articles.

show processlist

There is also a situation where the slow query is still being executed, but the slow SQL cannot be found in the slow query log. In this case, you can use the show processlist command to find the slow query. This command can display the running threads. The execution result is shown in the figure below. You can determine whether it is a slow query based on the size of the time.

show processlist

Summarize

This article mainly explains how to locate slow queries and briefly introduces the mysqldumpslow and pt-query-digest tools. Common methods such as explain, show profile, and trace will also be explained later.

What methods do you use when locating slow queries or optimizing SQL?

This is the end of this article on how to locate MySQL slow queries. For more information on locating MySQL slow queries, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

Reference Documentation

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html