Using pt-query-digest to analyze MySQL slow query

1. Usage and parameter description

pt-query-Digest [OPTIONS] [FILES] [DSN]--create-review-table # #当使用--when the review parameter outputs the analysis results to a table, it is automatically created without a table. --create-history-table # #当使用--when the history parameter outputs the analysis results to a table, it is created automatically if no tables are found. --Filter # #对输入的慢查询按指定的字符串进行匹配过滤后再进行分析--limit # #限制输出结果百分比或数量, the default value is 20, will be the slowest 20 statements output, if 50% is the total response time from large to small sort, output to the sum of 50%location Cutoff. --Host # #MySQL地址--User # #MySQL用户名--Password # #MySQL密码--history # #将分析结果保存到表中, the analysis results are more detailed, then use--history, if the same statement exists, and the query is in a different time period and history table, it is recorded in the data table, you can query the same checksum to compare the historical changes of a type of query. --review # #将分析结果保存到表中, this analysis only parameters the query condition, when the next use--Review, if there is the same statement analysis, it is not recorded in the data table. --output # #分析结果输出类型, values can be report, Slowlog, JSON, json-Anon, the report is generally used for readability. --since # #从什么时间开始分析, the value is a string, which can be specified as "yyyy-mm-DD[Hh:mm:ss] ", s (seconds), H (Hours), M (minutes), D (days), such as 12h means starting from 12 hours ago statistics. --until# #截止时间, with-since you can analyze slow queries over a period of time.

Common commands

# #分析慢查询文件:p T-query-digest Slow.log >slow_report.log# #分析最近12小时pt-query-digest--since=12h slow.log >slow_report2.log# #分析指定时间范围pt-query-digest Slow.log--since'2017-01-07 09:30:00'--until '2017-01-07 10:00:00'> >slow_report3.log# #分析只含有select语句的慢查询pt-query-digest--filter'$event->{fingerprint} =~ m/^select/i'Slow.log>slow_report4.log# #针对某个用户的慢查询pt-query-digest--filter'($event->{user} | | "") =~ m/^root/i'Slow.log>slow_report5.log# #查询所有所有的全表扫描或full Join slow Query pt-query-digest--filter'(($event->{full_scan} | | "") eq "yes") | | (($event->{full_join} | | "") eq "yes")'Slow.log>slow_report6.log# #把查询保存到query_review表pt-query-digest--user=root–password=abc123--review h=localhost,d=test,t=query_review--create-review-table slow.log# #把查询保存到query_history表pt-query-digest--user=root–password=abc123--review h=localhost,d=test,t=query_history--create-review-Table slow.log_0001pt-query-digest--user=root–password=abc123--review h=localhost,d=test,t=query_history--create-review-table slow.log_0002# #通过tcpdump抓取mysql的tcp协议数据, and then analyze Tcpdump-S65535-x-nn-q-tttt-i any-c +Port3306>mysql.tcp.txtpt-query-digest--type tcpdump mysql.tcp.txt>slow_report9.log# #分析binlogmysqlbinlog MySQL-bin.000093> mysql-Bin000093.sqlpt-query-digest--type=binlog mysql-bin000093.sql >slow_report10.log# #分析general logpt-query-digest--type=genlog localhost.log > Slow_report11.log

2. Interpretation of the report

Overall statistical results
Overall: How many queries are there in total?
Time range: The timeframe for query execution
Unique: The number of unique queries, that is, after the query criteria are parameterized, the total number of different queries
Total: min: min Max: Max avg: Average
95%: Put all the values from small to large, the position is located in the number 95%, this number is generally the most valuable reference
Median: Median, all values from small to large, position in the middle of the number

# Overall:2Total2Unique0.00QPS,0. 00x Concurrency ________________# time range:2018- ,-13T12: -: $To2018- ,-14T13: Geneva: to# Attribute Total min Max avg the%StdDev median#============     ======= ======= ======= ======= ======= ======= =======# Exec Time6s 10ms 6s 3s 6s 5s 3s# Lock Time209us 82us 127us 104us 127us 31us 104us# Rows sent -       0      -       -      -   70.71       -# Rows Examine411     111      -  205.50      -  133.64  205.50# Query Size310       the     234     155     234  111.72     155

Query Grouping Statistics results

Rank: The ranking of all statements, by default in descending order by query time, specified by--order-by
Query ID: The ID of the statement (remove extra space and text characters, calculate hash value)
Response: Total response time
Time: The total percentage of the query in this analysis
Calls: number of executions, that is, the total number of this analysis of this type of query statements
R/call: Average response time per execution
V/m: Ratio of response time Variance-to-mean
Item: Query Object

 # profile# Rank Query ID Response  time  Calls r/call v/m #  = ================================= = = = ========= ===== ====== ===== #  1  0xf0c5ae75a52e847d737f39f04b19  ...     6.4700  99.8 % 1  6.4700   0.00  SELECT Sbtest? # MISC 0xMISC      0.0100  0.2 % 1  0.0100   0.0  <1  items> 

SQL statistics

Detailed statistical results from the following query, the top table lists the number of executions, maximum, minimum, average, 95% and other purposes of statistics.
ID: The ID number of the query, and the reference ID corresponding to the
Databases: Database name
Users: Number of times each user executes (%)
Query_time Distribution: The query time distribution, the length reflects the interval ratio, in this case 1s-10s between the number of queries is more than 10s twice times.
Tables: Tables involved in the query
Explain:sql statements

# Query1:0QPS, 0x concurrency, ID0xf0c5ae75a52e847d737f39f04b198ef6Atbyte 0# This item is includedinchThe report because it matches--limit.# scores:v/M =0.00# time Range:all events occurred at2018- ,-13T12: -: $# Attribute pct Total min max avg the%StdDev median#============ === ======= ======= ======= ======= ======= ======= =======# Count -       1# Exec Time      About6s 6s 6s 6s 6s06s# Lock Time      the82us 82us 82us 82us 82us082us# Rows sent -      -      -      -      -      -       0      -# Rows Examine the      -      -      -      -      -       0      -# Query Size -       the       the       the       the       the       0       the# string:# Databases yong# Hosts192.168.10.16# Users yong# query_time distribution# 1us# 10us# 100us# 1ms# 10ms# 100ms# 1s ###################### ########################################### 10s+# tables# SHOW TABLE STATUS from ' Yong ' like'Sbtest8'\g# SHOW CREATE TABLE ' Yong '. ' Sbtest8 ' \g# EXPLAIN/*!50100 Partitions*/SELECT DISTINCT c from Sbtest8 WHEREIDBetween519478and519577ORDER by C\g

Using pt-query-digest to analyze MySQL slow query