Use pt-query-digest to find the SQL statement that is not suitable, pt-query-digestsql

Use pt-query-digest to find the SQL statement that is not suitable, pt-query-digestsql

 

Overview of pt-query-digest1.

Indexes can be executed more quickly, but there must be unreasonable indexes. If you want to find those indexes that are not very suitable and optimize them before they become problems, you can use the pt-query-digest query review function to analyze the EXPLAIN execution plan.

Pt-query-digest is a tool used to analyze mysql slow queries. It can analyze binlog, General log, and slowlog, you can also use SHOWPROCESSLIST or MySQL protocol data captured by tcpdump for analysis. You can output the analysis results to a file. The analysis process is to first parameterize the query statement conditions, and then perform grouping statistics on the query after parameterization, calculate the execution time, number of times, and proportion of each query. You can use the analysis results to find out the problem and optimize it.

2. Tool Installation

Pt-query-digest is a perl script that can be executed only by downloading and granting permissions.

[Root @ test1] # wget percona.com/get/pt-query-digest

[Root @ test1] # chmod u + x pt-query-digest

3. Execute the tool 3.1 to directly analyze the slow Query file

Pt-query-digest slow. log> slow_report.log

3.2 analyze queries in the last 12 hours

Pt-query-digest -- since = 12 h slow. log> slow_report2.log

3.3 analyze queries within a specified time range

Pt-query-digest slow. log -- since '2017-04-17 09:30:00 '-- until '2017-04-17 10:00:00'> slow_report3.log

3.4 analysis refers to slow queries containing select statements

Pt-query-digest -- filter' $ event-> {fingerprint} = ~ M/^ select/I 'slow. log> slow_report4.log

3.5 slow query for a user

Pt-query-digest -- filter' ($ event-> {user} | "") = ~ M/^ root/I 'slow. log> slow_report5.log

3.6 query all full table scans or full join slow queries

Pt-query-digest -- filter' ($ event-> {Full_scan} | "") eq "yes ") | ($ event-> {Full_join} | "") eq "yes") 'slow. log> slow_report6.log

3.7 Save the query to the query_review table.

Pt-query-digest -- user = root-password = abc123 -- review h = localhost, D = test, t = query_review -- create-review-table slow. log

3.8 Save the query to the query_history table.

Pt-query-digest -- user = root-password = abc123 -- review h = localhost, D = test, t = query _ history -- create-review-table slow. log_20140401

Pt-query-digest -- user = root-password = abc123 -- review h = localhost, D = test, t = query_history -- create-review-table slow. log_20140402

3.9 capture mysql tcp protocol data through tcpdump, and then analyze

Tcpdump-s 65535-x-nn-q-tttt-I any-c 1000 port 3306> mysql.tcp.txt

Pt-query-digest -- type tcpdump mysql.tcp.txt> slow_report9.log

3.10 binlog Analysis

Mysqlbinlog mysql-bin.000093> mysql-bin000093. SQL

Pt-query-digest -- type = binlog mysql-bin000093. SQL> slow_report10.log

3.11 analyze general log

Pt-query-digest -- type = genlog localhost. log> slow_report11.log

4. Report Analysis 4.1 Overall statistical results

 

Overall: Total number of queries. The preceding example shows a total of 109 queries.

Unique: number of unique queries, that is, the total number of Different queries after the query conditions are parameterized. In this example, 8 is used. (Deduplicated Statement)

Time range: the Time range for query execution.

Total: total; min: minimum; max: Maximum; avg: average; 95%: Sort all values from small to large, where the position is 95%, this number is generally the most useful reference; median: median, which sorts all values from small to large and positions them in the middle.

4.2 query group statistics

 

In this part, the query is parameterized and grouped, and the execution of various types of queries is analyzed. The results are sorted in descending order based on the total execution duration.

Response: total Response time.

Time: Total time proportion of the query in this analysis.

CILS: number of executions, that is, the total number of queries of this type in this analysis.

R/Call: average response time of each execution.

Item: query object

4.3 detailed statistics of each query

 

Detailed statistical results of the query. The top table lists the execution count, maximum, minimum, average, and 95% statistics.

Databases: Database Name

Users: number of executions by each user (proportion)

Query_time distribution: Query time distribution, which indicates the ratio of the interval. In this example, the number of queries over 10 s is twice that of 1-10 s.

Tables: Tables involved in the query

Explain: Example

5. Syntax and important options

Pt-query-digest [OPTIONS] [FILES] [DSN]

-- Create-review-table When the -- review parameter is used to output the analysis result to the table, it is automatically created if no table exists.

-- Create-history-table When the -- history parameter is used to output the analysis result to the table, it is automatically created if no table exists.

-- Filter Filter input slow queries by matching the specified string before analysis

-- LimitLimit the percentage or quantity of output results. The default value is 20, that is, the output of the slowest 20 statements. If the value is 50%, the total response time is sorted from large to small, output to the end of the total reaches 50%.

-- Host Mysql server address

-- User Mysql user name

-- Password Mysql user password

-- HistorySave the analysis results to the table. The analysis results are more detailed. If the same statement exists next time -- history is used, and the time range of the query is different from that in the history table, it is recorded in the data table. You can query the same CHECKSUM to compare the historical changes of a certain type of query.

-- ReviewSave the analysis results to the table. This analysis only parameterizes the query conditions and queries a record of a type, which is relatively simple. When -- review is used next time, if the same statement analysis exists, it is not recorded in the data table.

-- OutputAnalysis result output type. The values can be report (Standard Analysis report), slowlog (Mysql slow log), json, and json-anon. Generally, report is used for ease of reading.

-- SinceThe time when the analysis starts. The value is a string, which can be a specified time point in the format of "yyyy-mm-dd [hh: mm: ss, it can also be a simple Time Value: s (seconds), h (hours), m (minutes), d (days). For example, 12 hours indicates that statistics are started from 12 hours ago.

--End Time, with -- since, you can analyze slow queries within a period of time.

6. Remarks

Enable mysql slow query, open the configuration file (vim/etc/my. cnf in this path), add the following configuration, and restart the mysql service after modification.

// Slow Query

Log-output = FILE

Slow_query_log = 1

Slow_query_log_file =/data/dbdata/slow_query.log

Long-query-time = 1

Log-queries-not-using-indexes

7. References

Http://blog.csdn.net/seteor/article/details/24017913