pt-query-digest 简介

pt-query-digest是用于分析mysql慢查询的一个工具，它可以分析binlog、General log、slowlog，也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。

安装pt-query-digest

在官网下载pt安装包，然后直接rpm安装

rpm -ivh percona-toolkit-3.1.0-2.el7.x86_64.rpm

pt-query-digest语法及重要选项

使用pt-query-digest –help查看全部选项，下面选取一些重要选项

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

–create-review-table 当使用–review参数把分析结果输出到表中时，如果没有表就自动创建。
–create-history-table 当使用–history参数把分析结果输出到表中时，如果没有表就自动创建。
–filter 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
–limit 限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
–host mysql服务器地址
–user mysql用户名
–password mysql用户密码
–history 将分析结果保存到表中，分析结果比较详细，下次再使用–history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
–review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用–review时，如果存在相同的语句分析，就不会记录到数据表中。
–output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
–since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
–until 截止时间，配合—since可以分析一段时间内的慢查询。

分析pt-query-digest输出结果

第一部分：总体统计结果

Overall：总共有多少条查询
Time range：查询执行的时间范围
unique：唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询
total：总计 min：最小 max：最大 avg：平均
95%：把所有值从小到大排列，位置位于95%的那个数，这个数一般最具有参考价值
median：中位数，把所有值从小到大排列，位置位于中间那个数

# 该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小 # 570ms user time, 40ms system time, 32.85M rss, 309.68M vsz # 工具执行时间 # Current date: Mon May 25 00:00:05 2020 # 运行分析工具的主机名 # Hostname: localhost.localdomain # 被分析的文件名 # Files: /mysql/log/mysql-slow.log # 语句总数量，唯一的语句数量，QPS，并发数 # Overall: 73 total, 2 unique, 0.00 QPS, 0.00x concurrency _______________ # 日志记录的时间范围 # Time range: 2020-05-18 00:31:22 to 2020-05-21 18:00:14 # 属性               总计      最小    最大    平均    95%  标准    中等 # Attribute          total     min     max     avg     95%  stddev  median # ============     ======= ======= ======= ======= ======= ======= ======= # 语句执行时间 # Exec time          1516s     11s     62s     21s     24s      6s     20s # 锁占用时间 # Lock time            5ms    52us   124us    66us    93us    14us    60us # 发送到客户端的行数 # Rows sent              2       0       1    0.03       0    0.16       0 # select语句扫描行数 # Rows examine      29.73M  99.08k  13.13M 416.98k 174.27k   1.66M 143.37k # 查询的字符数 # Query size        17.16k     231     241  240.73  234.30    1.82  234.30 

第二部分：查询分组统计结果

Rank：所有语句的排名，默认按查询时间降序排列，通过–order-by指定
Query ID：语句的ID，（去掉多余空格和文本字符，计算hash值）
Response：总的响应时间
time：该查询在本次分析中总的时间占比
calls：执行次数，即本次分析总共有多少条这种类型的查询语句
R/Call：平均每次执行的响应时间
V/M：响应时间Variance-to-mean的比率
Item：查询对象

# Profile # Rank Query ID                           Response time   Calls R/Call  V/ # ==== ================================== =============== ===== ======= == #    1 0x13AA2790C74007F7D46C64DC823C9123 1433.0437 94.5%    71 20.1837  0.64 DELETE SELECT events problem #    2 0x5AC1605D997951F8B5F6CD8D47517C90   82.9604  5.5%     2 41.4802 19.94 SELECT events problem 

第三部分：每一种查询的详细统计结果

由下面查询的详细统计结果，最上面的表格列出了执行次数、最大、最小、平均、95%等各项目的统计。
ID：查询的ID号，和上图的Query ID对应
Databases：数据库名
Users：各个用户执行的次数（占比）
Query_time distribution ：查询时间分布, 长短体现区间占比，本例中1s-10s之间查询数量是10s以上的两倍。
Tables：查询中涉及到的表
Explain：SQL语句

# Query 1: 0 QPS, 0x concurrency, ID 0xF9A57DD5A41825CA at byte 802 ______ # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: all events occurred at 2016-11-22 06:11:40 # Attribute    pct   total     min     max     avg     95%  stddev  median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count         50       1 # Exec time     76      2s      2s      2s      2s      2s       0      2s # Lock time      0       0       0       0       0       0       0       0 # Rows sent     20       1       1       1       1       1       0       1 # Rows examine   0       0       0       0       0       0       0       0 # Query size     3      15      15      15      15      15       0      15 # String: # Databases    test # Hosts        192.168.2.1 # Users        mysql # Query_time distribution #   1us #  10us # 100us #   1ms #  10ms # 100ms #    1s  ################################################################ #  10s+ # EXPLAIN /*!50100 PARTITIONS*/ select sleep(2)G 

自定义创建query_review表和query_history表

备注：默认会生成query_review表和query_history表，因为一些字段不存在需要自己添加，一些字段你们可以删掉不使用。

CREATE TABLE `mysql_slow_query_review` (   `checksum` char(32) NOT NULL,   `fingerprint` text NOT NULL,   `sample` text NOT NULL,   `first_seen` datetime DEFAULT NULL,   `last_seen` datetime DEFAULT NULL,   `reviewed_by` varchar(20) DEFAULT NULL,   `reviewed_on` datetime DEFAULT NULL,   `comments` text,   `reviewed_status` varchar(24) DEFAULT NULL,   PRIMARY KEY (`checksum`),   KEY `idx_last_seen` (`last_seen`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;   CREATE TABLE `mysql_slow_query_review_history` (   `host_max` varchar(64) DEFAULT NULL,   `user_max` varchar(64) DEFAULT NULL,   `db_max` varchar(64) DEFAULT NULL,   `checksum` char(32) NOT NULL,   `sample` text NOT NULL,   `ts_min` datetime NOT NULL,   `ts_max` datetime NOT NULL,   `ts_cnt` float DEFAULT NULL,   `Query_time_sum` float DEFAULT NULL,   `Query_time_min` float DEFAULT NULL,   `Query_time_max` float DEFAULT NULL,   `Query_time_pct_95` float DEFAULT NULL,   `Query_time_stddev` float DEFAULT NULL,   `Query_time_median` float DEFAULT NULL,   `Lock_time_sum` float DEFAULT NULL,   `Lock_time_min` float DEFAULT NULL,   `Lock_time_max` float DEFAULT NULL,   `Lock_time_pct_95` float DEFAULT NULL,   `Lock_time_stddev` float DEFAULT NULL,   `Lock_time_median` float DEFAULT NULL,   `Rows_sent_sum` float DEFAULT NULL,   `Rows_sent_min` float DEFAULT NULL,   `Rows_sent_max` float DEFAULT NULL,   `Rows_sent_pct_95` float DEFAULT NULL,   `Rows_sent_stddev` float DEFAULT NULL,   `Rows_sent_median` float DEFAULT NULL,   `Rows_examined_sum` float DEFAULT NULL,   `Rows_examined_min` float DEFAULT NULL,   `Rows_examined_max` float DEFAULT NULL,   `Rows_examined_pct_95` float DEFAULT NULL,   `Rows_examined_stddev` float DEFAULT NULL,   `Rows_examined_median` float DEFAULT NULL,   `Rows_affected_sum` float DEFAULT NULL,   `Rows_affected_min` float DEFAULT NULL,   `Rows_affected_max` float DEFAULT NULL,   `Rows_affected_pct_95` float DEFAULT NULL,   `Rows_affected_stddev` float DEFAULT NULL,   `Rows_affected_median` float DEFAULT NULL,   `Rows_read_sum` float DEFAULT NULL,   `Rows_read_min` float DEFAULT NULL,   `Rows_read_max` float DEFAULT NULL,   `Rows_read_pct_95` float DEFAULT NULL,   `Rows_read_stddev` float DEFAULT NULL,   `Rows_read_median` float DEFAULT NULL,   `Merge_passes_sum` float DEFAULT NULL,   `Merge_passes_min` float DEFAULT NULL,   `Merge_passes_max` float DEFAULT NULL,   `Merge_passes_pct_95` float DEFAULT NULL,   `Merge_passes_stddev` float DEFAULT NULL,   `Merge_passes_median` float DEFAULT NULL,   `InnoDB_IO_r_ops_min` float DEFAULT NULL,   `InnoDB_IO_r_ops_max` float DEFAULT NULL,   `InnoDB_IO_r_ops_pct_95` float DEFAULT NULL,   `InnoDB_IO_r_ops_stddev` float DEFAULT NULL,   `InnoDB_IO_r_ops_median` float DEFAULT NULL,   `InnoDB_IO_r_bytes_min` float DEFAULT NULL,   `InnoDB_IO_r_bytes_max` float DEFAULT NULL,   `InnoDB_IO_r_bytes_pct_95` float DEFAULT NULL,   `InnoDB_IO_r_bytes_stddev` float DEFAULT NULL,   `InnoDB_IO_r_bytes_median` float DEFAULT NULL,   `InnoDB_IO_r_wait_min` float DEFAULT NULL,   `InnoDB_IO_r_wait_max` float DEFAULT NULL,   `InnoDB_IO_r_wait_pct_95` float DEFAULT NULL,   `InnoDB_IO_r_wait_stddev` float DEFAULT NULL,   `InnoDB_IO_r_wait_median` float DEFAULT NULL,   `InnoDB_rec_lock_wait_min` float DEFAULT NULL,   `InnoDB_rec_lock_wait_max` float DEFAULT NULL,   `InnoDB_rec_lock_wait_pct_95` float DEFAULT NULL,   `InnoDB_rec_lock_wait_stddev` float DEFAULT NULL,   `InnoDB_rec_lock_wait_median` float DEFAULT NULL,   `InnoDB_queue_wait_min` float DEFAULT NULL,   `InnoDB_queue_wait_max` float DEFAULT NULL,   `InnoDB_queue_wait_pct_95` float DEFAULT NULL,   `InnoDB_queue_wait_stddev` float DEFAULT NULL,   `InnoDB_queue_wait_median` float DEFAULT NULL,   `InnoDB_pages_distinct_min` float DEFAULT NULL,   `InnoDB_pages_distinct_max` float DEFAULT NULL,   `InnoDB_pages_distinct_pct_95` float DEFAULT NULL,   `InnoDB_pages_distinct_stddev` float DEFAULT NULL,   `InnoDB_pages_distinct_median` float DEFAULT NULL,   `QC_Hit_cnt` float DEFAULT NULL,   `QC_Hit_sum` float DEFAULT NULL,   `Full_scan_cnt` float DEFAULT NULL,   `Full_scan_sum` float DEFAULT NULL,   `Full_join_cnt` float DEFAULT NULL,   `Full_join_sum` float DEFAULT NULL,   `Tmp_table_cnt` float DEFAULT NULL,   `Tmp_table_sum` float DEFAULT NULL,   `Tmp_table_on_disk_cnt` float DEFAULT NULL,   `Tmp_table_on_disk_sum` float DEFAULT NULL,   `Filesort_cnt` float DEFAULT NULL,   `Filesort_sum` float DEFAULT NULL,   `Filesort_on_disk_cnt` float DEFAULT NULL,   `Filesort_on_disk_sum` float DEFAULT NULL,   `Bytes_sum` float DEFAULT NULL,   `Bytes_min` float DEFAULT NULL,   `Bytes_max` float DEFAULT NULL,   `Bytes_pct_95` float DEFAULT NULL,   `Bytes_stddev` float DEFAULT NULL,   `Bytes_median` float DEFAULT NULL,   PRIMARY KEY (checksum, ts_min, ts_max),   KEY `idx_hostname_max_ts_min` (`host_max`,`ts_min`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;  

生成一周前100条慢日志SQL数据

pt-query-digest /mysql/logs/mysql-slow.log --user=root --password=12345678  --socket=/tmp/mysql.sock --since=7d --review t=mysql_slow_query_review --history t=mysql_slow_query_review_history --max-hostname-length=0 --limit=100  --charset=utf8 

部署脚本定时生成

#!/bin/bash DIR="$( cd "$( dirname "$0"  )" && pwd  )" cd $DIR  #配置数据库的连接地址 monitor_db_host=127.0.0.1"" monitor_db_port=3306 monitor_db_user="root" monitor_db_password="12345678" monitor_db_socket="/tmp/mysql3306.sock" dbname="percona_schema" table_name1="mysql_slow_query_review" table_name2="mysql_slow_query_review_history"  #实例慢日志位置 slowquery_file="/mysql/logs/mysql-slow.log" pt_query_digest="/usr/bin/pt-query-digest"  #删除历史表数据 truncate_sql1="truncate table ${table_name1}" truncate_sql2="truncate table ${table_name2}" mysql -h${monitor_db_host} -P${monitor_db_port} -u${monitor_db_user} -p${monitor_db_password} --socket=${monitor_db_socket} ${dbname} -e "${truncate_sql1}" mysql -h${monitor_db_host} -P${monitor_db_port} -u${monitor_db_user} -p${monitor_db_password} --socket=${monitor_db_socket} ${dbname} -e "${truncate_sql2}"  #收集日志 $pt_query_digest  --user=$monitor_db_user --password=$monitor_db_password --port=$monitor_db_port --socket=$monitor_db_socket  --review h=$monitor_db_host,t=mysql_slow_query_review   --history h=$monitor_db_host,t=mysql_slow_query_review_history   --limit=100 --since=7d --max-hostname-length=0 --charset=utf8  $slowquery_file > /tmp/analysis_slow_query.log 

例举用法示例

1.直接分析慢查询文件:

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

2.分析最近12小时内的查询：

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

3.分析指定时间范围内的查询：

pt-query-digest slow.log –since ‘2020-05-10 09:30:00’ –until ‘2020-05-10 10:00:00’> > slow_report3.log

4.分析指含有select语句的慢查询

pt-query-digest –filter ‘$event->{fingerprint} =~ m/^select/i’ slow.log> slow_report4.log

5.针对某个用户的慢查询

pt-query-digest –filter ‘($event->{user} || “”) =~ m/^root/i’ slow.log> slow_report5.log

6.查询所有所有的全表扫描或full join的慢查询

pt-query-digest –filter ‘((
$event->{Full_scan} || “”) eq “yes”) ||(($

event−>Fulls​can∣∣““)eq“yes“)∣∣((event->{Full_join} || “”) eq “yes”)’ slow.log> slow_report6.log

7.把查询保存到query_review表

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

8.把查询保存到query_history表

pt-query-digest –user=root –password=12345678 –review h=localhost,D=test,t=query_history –create-review-table slow.log_0001

9.通过tcpdump抓取mysql的tcp协议数据，然后再分析

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

10.分析binlog

mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest –type=binlog mysql-bin000093.sql > slow_report10.log

11.分析general log

pt-query-digest –type=genlog localhost.log > slow_report11.log