0%

percona-toolkit : pt-query-digest

pt-query-digest是Percona Toolkit工具集中常用的一个工具,其能够分析MySQL的slowlog,general log,binlog文件,同时也可以使用show processlist或者tcpdump抓取的MySQL协议数据进行分析。

安装Toolkit

下载最新工具包

Download Percona Toolkit

安装依赖

1
$ yum install perl-DBI perl-DBD-MySQL perl-Digest-MD5 perl-IO-Socket-SSL perl-TermReadKey

安装Toolkit

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

pt-query-digest常用参数

参数 说明
–host mysql主机
–port mysql端口
–user mysql用户
–password MySQL用户密码
–filter 对慢查询进行条件过滤
–limit 限制输出结果百分比或数量,默认是最慢的20条记录
–history 将分析结果保存到表中,分析结果比较详细,下次再使用–history时,如果存在相同的语句,且查询所在的时间区间和历史表中的不同,则会记录到数据表中,可以通过查询同一CHECKSUM来比较某类型查询的历史变化
–review 将分析结果保存到表中,这个分析只是对查询条件进行参数化,一个类型的查询一条记录,比较简单。当下次使用–review时,如果存在相同的语句分析,就不会记录到数据表中。
–create-review-table 当使用–review参数把分析结果输出到表中,如果没有表就自动创建
–create-history-table 当使用–history参数把分析结果输出到表中,如果没有表就自动创建
–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形成一个范围查询

示例

分析整个slowlog

1
$ pt-query-digest  slow.log

分析近12个小时的slowlog

1
$ pt-query-digest  --since=12h  slow.log

分析指定范围的slowlog

1
$ pt-query-digest slow.log --since '2020-01-19 09:30:00' --until '2020-01-19 10:30:00'

分析带有select的慢查询

1
$ pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log

分析指定用户的慢查询

1
$ pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log

分析全表扫和full join的慢查询

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

把分析结果保存到review表

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

把分析结果保存到history表

1
pt-query-digest  --user=root –password=Abc123# --review  h=localhost,D=test,t=query_history --create-review-table  slow.log

通过tcpdump抓取mysql协议数据并分析

1
2
$ tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 33006 > mysql.tcpdump
$ pt-query-digest --type tcpdump mysql.tcpdump > slow_report.log

分析指定binlog

1
2
$ mysqlbinlog mysql-bin.000093 > mysql-bin000100.sql
$ pt-query-digest --type=binlog mysql-bin000100.sql > binlog_report.log

分析gerneral log

1
$ pt-query-digest  --type=genlog  gerneral.log

结果分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
# 6.2s user time, 40ms system time, 26.66M rss, 189.31M vsz
# Current date: Sun Jan 19 11:07:53 2020
# Hostname: t-luhx03-v-szzb
# Files: mysqlslow.log
# Overall: 48.43k total, 53 unique, 0.01 QPS, 0.00x concurrency __________
# Time range: 2019-12-04T08:50:53 to 2020-01-19T02:04:55
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 57s 65us 122ms 1ms 332us 5ms 125us
# Lock time 31s 16us 106ms 647us 125us 3ms 49us
# Rows sent 253.03k 0 310 5.35 46.83 14.48 0
# Rows examine 1013.54k 0 620 21.43 97.36 61.08 0
# Query size 9.48M 15 1.55k 205.22 1.53k 471.91 26.08

# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============================= ============= ===== ====== ===== ====
# 1 0x8A8CA17B1A950869272872BF... 49.6826 87.1% 1690 0.0294 0.00 SELECT information_schema.TABLES
# 2 0xC14C189A6D160CDFCFE206BC... 4.1641 7.3% 29690 0.0001 0.00 SELECT connections
# 3 0x6BA5AF356A52DF5A399033C7... 1.5805 2.8% 5070 0.0003 0.00 SELECT global_alert_settings group_alert_settings connection_alert_settings
# MISC 0xMISC 1.6264 2.9% 11981 0.0001 0.0 <50 ITEMS>

# Query 1: 0.02 QPS, 0.00x concurrency, ID 0x8A8CA17B1A950869272872BF60773D55 at byte 7784854
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-12-19T02:32:09 to 2019-12-20T06:41:09
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 1690
# Exec time 87 50s 22ms 122ms 29ms 40ms 6ms 27ms
# Lock time 91 29s 11ms 106ms 17ms 26ms 5ms 16ms
# Rows sent 0 1.65k 1 1 1 1 0 1
# Rows examine 50 512.97k 310 320 310.82 313.99 4.11 299.03
# Query size 5 552.88k 335 335 335 335 0 335
# String:
# Databases test
# Hosts t-luhx03-v-szzb
# Users dba
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms ################################################################
# 100ms #
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `information_schema` LIKE 'TABLES'\G
# SHOW CREATE TABLE `information_schema`.`TABLES`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT
count(*) table_num,
ifnull(sum(table_rows), 0) table_rows_num,
ifnull(sum(data_length), 0) db_data_size,
ifnull(sum(index_length), 0) db_idx_size,
ifnull(sum(data_length+index_length), 0) total_db_size,
ifnull(round(sum(index_length)/sum(data_length), 2), 0) db_idxfrac_size
FROM
information_schema.TABLES\G

分析报告分为三个部分:

  • 第一部分为总体统计结果,overall表示查询数量;time range为时间范围;unique为唯一查询数量,95%表示把所有值从小到大排列,位置位于95%的那个数;median表示中位数
  • 第二部分为分组统计结果,rank为所有语句的排名,默认按查询时间降序,通过–order-by指定;reponse为总的响应时间;time为分析时间占比;calls执行次数;R/CALL平均响应时间
  • 第三部分为每条SQL的详细分析结果

参考链接

  1. pt-query-digest
  2. pt-query-digest详解慢查询日志