percona-toolkit 之【pt-query-digest】介绍

背景：

做为一个MySQL DBA，分析慢查询是日常主要的工作之一，之前一直使用mysqlsla作为分析慢查询的，因为简单并且也能满足自己对慢查询分析的要求，对于另一个工具pt-query-digest并不熟悉，对于pt系列的工具之前也介绍过一些，Percona Toolkit确实厉害，整个全家桶：包含了备份、监控、各种分析工具，以及定制化过的MySQL Server。现在本文来介绍下如何使用pt-query-digest，也方便自己查阅。

很多人都把pt-query-digest作为分析mysql工具的首选，因为它可以从logs、processlist、和tcpdump 来分析MySQL的状况，logs包括slow log、general log、binlog。也可以把分析结果输出到文件中，或则把文件写到表中。分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。

使用方法：

下载：

进入到下载页，下载Percona Toolkit

依赖包：

Centos：

yum -y install perl-TermReadKey perl-Time-HiRes perl-IO-Socket-SSL.noarch

使用方法：

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

说明：

pt-query-digest分析slow log、general log和binary log，还可以分析来自SHOW PROCESSLIST的查询和来自tcpdump的MySQL协议数据。默认情况下，查询按fingerprint分组，并按查询时间的降序排列（最慢的查询排在第一位）。如果未提供文件，则该工具读取STDIN。可选的DSN用于某些选项，例如--since和--until。

示例：

1）从slow.log报告最慢的查询

pt-query-digest slow.log

2）从host1上的processlist里报告最慢的查询

pt-query-digest --processlist h=host1

3）使用tcppdump捕获MySQL协议数据，然后报告最慢的查询

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

pt-query-digest --type tcpdump mysql.tcp.txt

4）将查询数据从slow.log保存到host2，以供以后查看和趋势分析

pt-query-digest --review h=host2 --no-report slow.log

描述

pt-query-digest是一种复杂但易于使用的工具，用于分析MySQL查询。它可以分析来自MySQL慢速日志，常规日志和二进制日志的查询（二进制日志必须首先转换为文本，请参阅--type）。默认情况下，该工具报告哪些查询最慢。可以使用--group-by、--filter和--embedded-attributes之类的选项来创建更复杂和定制的报告。

pt-query-digest具有两个功能：查询查看（--review）和查询历史记录（--history）。使用--review选项时，所有查询都将保存到数据库中。当再次使用--review运行该工具时，数据库中标记为已审阅的查询不会打印在报告中，只显示需要审查的新查询。使用--history选项时，每个查询的指标（查询时间，锁时间等）都保存到数据库中。每次使用--history运行该工具时，都会保存更多的历史数据，这些数据可用于随时间推移趋势和分析查询性能。

属性

如果使用--filter，-ignore-attributes和其他与属性相关的选项时，必须熟悉这些属性，如果不需要自定义可以跳过属性说明。

pt-query-digest处理event，这些event是称为属性的键值对的集合，能识别出大多数属性：Query_time，Lock_time等。如果编写--filter，请务必在使用每个event之前检查其属性是否已定义，否则过滤器代码可能会因“使用未初始化的值”错误而使工具崩溃。有关常见和--type特定属性的列表：为任何输入转储event属性：

$ pt-query-digest                  \
    slow.log                       \
    --filter 'print Dumper $event' \
    --no-report                    \
    --sample 1

产生很多带有“ attribute => value”对的输出：

$VAR1 = {
  Query_time => '0.033384',
  Rows_examined => '0',
  Rows_sent => '0',
  Thread_id => '10',
  Tmp_table => 'No',
  Tmp_table_on_disk => 'No',
  arg => 'SELECT col FROM tbl WHERE id=5',
  bytes => 103,
  cmd => 'Query',
  db => 'db1',
  fingerprint => 'select col from tbl where id=?',
  host => '',
  pos_in_log => 1334,
  ts => '071218 11:48:27',
  user => '[SQL_SLAVE]'
};

通过编写--filter，可以创建从现有属性派生的新属性。例如，要创建一个名为Row_ratio的属性以检查Rows_sent与Rows_examined的比率，请指定一个过滤器：

--filter '($event->{Row_ratio} = $event->{Rows_sent} / ($event->{Rows_examined})) && 1'

需要使用&& 1技巧来创建始终为true的有效单行语法，新属性将自动出现在输出中：

# Row ratio        1.00    0.00      1    0.50      1    0.71    0.50

输出值

默认的--output是查询分析报告。 --no-report选项控制是否打印此报告。使用--review或--history时，解析所有查询但不显示报告。

报告里的列说明：

Column        Meaning
============  ==========================================================
Rank          查询在分析的整个查询集中的排名
Query ID      查询的指纹（fingerprint）
Response time 总响应时间，占总响应时间的百分比
Calls         执行此查询的次数
R/Call        每次执行的平均响应时间
V/M           响应时间的方差均值比，变异数对平均数比，可说明样本的分散程度. 这个值大,往往是值得考虑优化的对象.
Item          查询的简单显示，包含了查询涉及的表对象。

示例：
# Rank Query ID                           Response time Calls R/Call V/M  
# ==== ================================== ============= ===== ====== =====
#    1 0x95BE8AA82ED172A4ABAFC4ED3B792467 6.0100 100.0%     2 3.0050  0.00 SELECT x

拆分多部分说明报告信息：

# Query 2: 0.01 QPS, 0.02x conc, ID 0xFDEA8D2993C9CAF30x6F7A87D11DDD9CC608CCACD1427CD832 at byte 160665

说明：查询的顺序号、每秒查询量、查询的近似并发度（根据时间隔和总Query_time计算）、查询ID。如果使用--review，则此ID是数据库中查询校验和的十六进制。可以使用SELECT ...之类的查询从数据库中选择已审核查询的详细信息：WHERE checksum = 0xFDEA8D2993C9CAF3。160665表示在文本中的偏移量，可以利用偏移量到慢查询日志里定位具体的sql语句，定位方法如下：

tail -c +160665 /path/to/slow.log. | head

如果想打印出特定查询的每个样本：去掉查询ID的0x

pt-query-digest test2-slow.log --no-report --output slowlog --filter '$event->{fingerprint} && make_checksum($event->{fingerprint}) eq "6F7A87D11DDD9CC608CCACD1427CD832"'
# Time: 2020-02-28T10:49:34
# User@Host: root[root] @ test2 []
# Thread_id: 19
# Query_time: 3.003152  Lock_time: 0.002218  Rows_sent: 1  Rows_examined: 2
use orchestrator;
select count(*),sleep(3) from cluster_domain_name;

接下来是有关此类查询的指标表：

# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         20       1
# Exec time     13      3s      3s      3s      3s      3s       0      3s
# Lock time     33     2ms     2ms     2ms     2ms     2ms       0     2ms
# Rows sent      5       1       1       1       1       1       0       1
# Rows examine  11       2       2       2       2       2       0       2
# Query size    25      49      49      49      49      49       0      49
# Row ratio     11    0.50    0.50    0.50    0.50    0.50       0    0.50

第一行是表的列标题：属性、百分比（总计的百分比），总计、最大、最小、平均、95％的值小于或等于此值、标准偏差和中位数都是从第95个百分位数计算得出的。

接下来，将查询有关用户，数据库和时间范围的统计信息：

# Users       1   user1
# Databases   2     db1(1), db2(1)
# Time range 2008-11-26 04:55:18 to 2008-11-27 00:15:15

用户和数据库显示为不同值的计数。如果只有一个，则会单独显示；如果有很多，将显示每个最常出现的频率，然后显示它出现的次数。

然后看执行时间分布：

# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  #####
# 100ms  ####################
#    1s  ##########
#  10s+

执行时间显示时间聚类的分布图。每个查询都进入“存储桶”之一，并被计算在内。第一个存储桶的所有值都在“单个微秒范围”内-小于10us。第二个是“数十微秒”，从10us到（但不包括）100us；等等。可以通过指定--report-histogram更改图表属性，但仅限于基于时间的属性。

然后看表信息：

# Tables
#    SHOW TABLE STATUS LIKE 'table1'\G
#    SHOW CREATE TABLE `table1`\G
# EXPLAIN
SELECT * FROM table1\G

通过给出的信息可以得到表的基本信息，以及其执行计划，对于无法执行的非SELECT查询，该工具会尝试将该查询转换为大致等效的SELECT查询，并将其添加到下面。

QUERY REVIEW

--review是存储所有已分析的查询，可以将元数据添加到查询类别中，例如将其标记为后续跟踪，为查询添加注释或为问题跟踪系统使用问题ID标记它们。可以存储历史数据，例如行数，查询时间以及通常可以在报告中看到的任何内容。

要使用此功能，请使用--review选项运行pt-query-digest。它将指纹和其他信息存储到指定的表中。下次使用相同的选项运行它时，它将执行以下操作：

不会显示已审核的查询。如果为review_by列设置了值，则认为该查询已被审核。（如果想查看已经查看过的查询，请使用--report-all选项。）
已查看但未出现在输出中的查询将导致每个段落第一行中查询编号序列的空白。--limit指定的值仍然会被接受。因此，如果看了前10名中的所有查询并要求输入前10名，则输出中将看不到任何内容。
如果想查看已经查看过的查询，可以指定--report-all。然后，将看到正常的分析输出，但还将看到执行时间图下方的审阅表中的信息。例如：
```
# Review information
#      comments: really bad IN() subquery, fix soon!
#    first_seen: 2008-12-01 11:48:57
#   jira_ticket: 1933
#     last_seen: 2008-12-18 11:49:07
#      priority: high
#   reviewed_by: xaprb
#   reviewed_on: 2008-12-18 15:03:11
```
此元数据很有用，因为当分析查询时，会将注释直接集成到报告中。

FINGERPRINTS

简而言之就是将将相似的查询分组在一起，如：

SELECT name, password FROM user WHERE id='12823';
select name,   password from user
   where id=5;

可以合并当成：

select name, password from user where id=?

参数说明： pt-query-digest --help

  --ask-pass                   FALSE        #连接到MySQL时提示输入密码
  --attribute-aliases          db|Schema    #属性列表|别名等（默认db | Schema）
  --attribute-value-limit      0            #属性值的限制（默认为0）
  --charset                    (No value)   #默认字符集
                                            #逗号分隔的配置文件列表；如果指定，则必须是命令行上的第一个选项
  --config                     /etc/percona-toolkit/percona-toolkit.conf,/etc/percona-toolkit/pt-query-digest.conf,/root/.percona-toolkit.conf,/root/.pt-query-digest.conf
  --continue-on-error          TRUE         #即使有错误也继续解析（默认为是）
  --create-history-table       TRUE         #创建--history表（如果不存在）（默认为yes）
  --create-review-table        TRUE         #创建--review表（如果不存在）（默认为yes）
  --daemonize                  FALSE        #Fork到后台并从shell分离
  --database                   (No value)   #连接到该数据库
  --defaults-file              (No value)   #仅从给定文件读取mysql选项
  --embedded-attributes        (No value)   #两个Perl正则表达式模式，用于捕获查询中嵌入的伪属性
  --expected-range             5,10         #当数量多于或少于预期时解释项目（默认值为5,10）
  --explain                    (No value)   #使用此DSN对示例查询运行EXPLAIN并打印结果
  --filter                     (No value)   #丢弃此Perl代码未返回true的事件
  --group-by                   fingerprint  #要对事件的哪个属性进行分组（默认fingerprint）
  --help                       TRUE         #显示帮助并退出
  --history                    (No value)   #在给定表中保存每个查询类的指标。 pt-query-digest将查询指标（查询时间，锁定时间等）保存到此表中，以便您查看查询类如何随时间变化
  --host                       (No value)   #连接到主机
  --ignore-attributes          arg,cmd,insert_id,ip,port,Thread_id,timestamp,exptime,flags,key,res,val,server_id,offset,end_log_pos,Xid  #不要聚合这些属性
  --inherit-attributes         db,ts        #如果丢失，则从具有属性的最后一个事件继承这些属性（默认db，ts）
  --interval                   .1           #轮询show processlist的频率，以秒为单位（默认为.1）
  --iterations                 1            #在收集和报告周期中迭代多少次（默认为1）
  --limit                      95%:20       #将输出限制为给定的百分比或计数（默认为95％：20）
  --log                        (No value)   #守护进程时将所有输出打印到此文件
  --max-hostname-length        10           #将报告中的主机名修剪到此长度。 0 =不修剪主机名（默认为10）
  --max-line-length            74           #将行修剪到此长度。 0 =不修剪线条（默认74）
  --order-by                   Query_time:sum   #按此属性:聚合函数对事件进行排序（默认Query_time：sum）
  --outliers                   Query_time:1:10  #按属性:百分比:计数报告异常值（默认查询时间：1：10）
  --output                     report           #如何格式化和打印查询分析结果（默认report）
  --password                   (No value)       #连接时使用的密码
  --pid                        (No value)       #创建给定的PID文件
  --port                       (No value)       #用于连接的端口号
  --preserve-embedded-numbers  FALSE            #查询时保留数据库/表名中的数字
  --processlist                (No value)       #使用--interval sleep轮询此DSN的进程列表以进行查询
  --progress                   time,30          #将进度报告打印到STDERR（默认时间30）
  --read-timeout               0                #等待的超时时间，等待来自输入的事件； 0表示永远等待（默认值为0）。 可选后缀s =秒，m =分钟，h =小时，d =天； 如果没有后缀，则使用s。
  --report                     TRUE             #打印每个--group-by属性的查询分析报告（默认为yes）
  --report-all                 FALSE            #报告所有查询，甚至包括已审核的查询
  --report-format              rusage,date,hostname,files,header,profile,query_report,prepared  #打印查询分析报告的这些部分
  --report-histogram           Query_time       #绘制此属性值的分布图（默认Query_time）
  --resume                     (No value)       #如果指定，该工具会将最后一个文件偏移（如果有的话）写入给定的文件名
  --review                     (No value)       #保存查询以供以后查看，并且不报告已查看的
  --run-time                   (No value)       #每个迭代要运行多长时间。可选后缀s =秒，m =分钟，h =小时，d =天；如果没有后缀，则使用s。
  --run-time-mode              clock            #设置--run-time的值所用的值（默认clock）
  --sample                     (No value)       #过滤掉每个查询中除前N个事件外的所有事件
  --set-vars                                    #在此以逗号分隔的（变量=值对）列表中设置MySQL变量
  --show-all                                    #显示这些属性的所有值
  --since                      (No value)       #解析仅查询比该值新的查询（自此日期以来解析查询）
  --slave-password             (No value)       #设置用于连接到从的密码
  --slave-user                 (No value)       #设置用于连接到从的用户
  --socket                     (No value)       #用于连接的套接字文件
  --timeline                   FALSE            #显示事件的时间表
  --type                       slowlog          #要解析的输入的类型（默认慢日志）
  --until                      (No value)       #仅解析早于此值的查询（直到此日期为止解析查询）
  --user                       (No value)       #用于登录的用户（如果不是当前用户）
  --variations                                  #报告这些属性值的变化数量
  --version                    FALSE            #显示版本并退出
  --version-check              TRUE             #检查最新版本的Percona Toolkit，MySQL和其他程序（默认为是）
  --vertical-format            TRUE             #在报告的SQL查询中输出尾随的“ \ G”（默认为是）
  --watch-server               (No value)       #此选项告诉pt-query-digest在解析tcpdump时要监视哪个服务器IP地址和端口（例如“ 10.0.0.1:3306”）（对于--type tcpdump）；其他所有服务器均被忽略

View Code

1，--ask-pass：连接到MySQL时提示输入密码。

2，--attribute-aliases：默认值：db | Schema。如果event不具有主要属性，则将查找别名属性。如果找到别名，则会使用别名属性的值创建主要属性，并删除别名属性。

3，--attribute-value-limit：默认值：0，属性的限制值。处理慢查询中的错误，这些错误会导致属性值过大。如果值大于此值，则使用该查询类别的最后一次看到的值。

4，--charset：连接数据库的默认字符集。

5，--config：逗号分隔的配置文件列表；如果指定，则必须是命令行上的第一个选项。

6，--[no]continue-on-error：即使有错误，也继续解析。该工具不会永远持续下去：一旦任何过程导致100个错误，它将停止运行，默认yes。

7，--[no]create-history-table：创建--history表（如果不存在），默认yes。

8，--[no]create-review-table：如果--review表不存在，则创建它，默认yes。

9，--daemonize：守护进程执行，fork到后台并从shell分离。

10，--database：连接到该数据库，缩写-D。

11，--defaults-file：从给定文件中读取mysql选项，必须提供绝对路径名，缩写-F。

12，--embedded-attributes：两个Perl正则表达式模式，用于捕获查询中嵌入的伪属性。

13，--expected-range：当数量多于或少于预期时，请说明，默认5,10。

14，--explain：使用此DSN对示例查询运行EXPLAIN并打印结果。

--explain='h=192.168.163.132,u=root,p=root'

15，--filter：过滤器规则，舍弃此Perl代码未返回true的事件。属性的名称可以看文章前面的 “ attribute => value”。

只返回指定类型的查询，如返回select的查询：

--filter '$event->{arg} =~ m/^select/i'
--filter '$event->{fingerprint} =~ m/^select/i'

只返回指定用户的查询，如返回zhou用户的查询：

--filter '$event->{user} =~ m/^zhou/i'
--filter '($event->{user} || "") =~ m/^zhou/i'

只返回指定IP的查询，如返回192.168.163.111的查询：

--filter '($event->{host} || $event->{ip} || "") =~ m/^192.168.163.111/i'

只返回指定DB的查询，如返回zhoujinyi DB的查询：
```
--filter '($event->{db} || "") =~ m/^zhoujinyi/i'
```

返回指定DB和类型的查询，如返回zhoujinyi DB中的select查询：

--filter '(($event->{db} || "") =~ m/^mysql/i) && (($event->{fingerprint} || "") =~ m/^select/i)'

16，--group-by：对event的哪个属性进行分组（默认fingerprint）。可以根据查询的任何属性（如user或db）将查询分组为类，默认情况下，这些属性将显示哪些用户和哪些数据库获得的Query_time最多。每个值在--order-by中的相同位置必须具有对应的值。为方便起见，将值添加到--group-by将自动将值添加到--order-by。如根据db和user分组：

--group-by=db,user

# Report grouped by db
...
# Report grouped by user
...

可选值还有：

fingerprint：指纹，将查询抽象为规范形式，然后用于将event分组到一个类中。
tables：按表的形式返回报告信息。
distill：超级指纹，将查询分解为对它们执行操作的建议。

17，--history：DSN选项，在给定表中保存每个查询类的指标（查询时间，锁定时间等）。默认表是percona_schema.query_history，指定数据库（D）和表（t）的DSN选项以覆盖默认值。除非指定了--no-create-history-table，否则将自动创建数据库和表。

--history='h=192.168.163.132,u=root,p=root'

从Percona Toolkit 3.0.11开始，校验和功能已更新为在MD5总和中使用32个字符。这将导致历史记录表中的校验和字段的值与该工具的早期版本中的值不同。

只针对slowlog？

18，--host：连接到主机，缩写-h。

19，--ignore-attributes：不要聚合这些属性，默认值：arg, cmd, insert_id, ip, port, Thread_id, timestamp, exptime, flags, key, res, val, server_id, offset, end_log_pos, Xid

20，--inherit-attributes：继承属性，默认值：db，ts。如果缺少属性，从它们的最后一个event继承这些属性。如一个event的db属性等于“ foo”，而下一个事件没有db属性，则它继承其db属性的“ foo”。

21，--interval：轮询processlist列表的频率，默认为：.1，以秒为单位。

22，--iterations：收集和报告周期中迭代次数，默认为1。如果为0，则迭代到无穷大。每次迭代都运行--run-time的时间。使用--run-time-mode interval时，由--run-time指定的间隔时间决定。

23，--limit：限制显示，默认值：95%:20。0%表示输出所有，不做限制。将输出限制为给定的百分比或行数。如果参数是整数，则仅报告前N个最差查询。如果参数是％，报告最差查询的百分比。如果百分比后跟冒号和另一个整数，报告最高百分比或该整数指定的数字，以先到者为准。

24，--log：如果以守护程序方式允许，则将所有输出打印到此文件。

25，--max-hostname-length：将报告中的主机名修剪到此长度，默认10，0为不修剪主机名。

26，--max-line-length：将线修剪到此长度。默认74，0为不修剪线。

27，--order-by：一个用逗号分隔的order-by表达式列表，通过此属性和聚合函数对事件进行排序，默认值：Query_time:sum，也用于--group-by属性。当解析常规日志（--type genlog）时，默认的变为Query_time:cnt。常规日志不会报告查询时间，因此只有cnt聚合才有意义，因为所有查询时间均为零。

#按总的执行时间排序
--order-by=Query_time:sum
#按总的执行次数排序
--order-by=Query_time:cnt

28，--outliers：按照定义的值报告异常值，默认值：Query_time:1:10，语法是用冒号分隔的字符串的逗号分隔列表。第一个字段是定义异常值的属性。第二个是与该属性的第95个百分点进行比较的数字。第三个是可选的，并且与属性的cnt集合进行比较。不管--limit中指定了什么限制，通过此规范的查询都会添加到报告中。即返回95%栏位的Query_time>=1秒，且执行次数>=10次。最终结果是取limit和outliers的并集，这也就是为什么显示指定limit参数，返回的结果并不全满足limit参数，还会返回匹配outliers的结果（比如--limit=1，按照正常会只取1条，但是异常值的也有1条，则就返回2个query）。

--outliers=Query_time:30:1

29，--output：格式化和打印查询分析结果，默认值：report。可选值：

report：标准查询分析报告
slowlog：MySQL慢查询模式
json：json模式
json-anon：json模式，无查询示例
secure-slowlog：MySQL慢查询模式，无查询示例

可以通过指定--no-report禁用整个报告输出，并且可以通过指定--report-format禁用或重新排列其部分。

30，--password：缩写-p，连接时使用的密码。如果密码包含逗号，则必须使用反斜杠将其转义

31，--pid：创建给定的PID文件。如果PID文件已经存在并且包含的PID与当前的PID不同，则该工具将无法启动。但是，如果存在PID文件，并且其中包含的PID不再运行，则该工具将使用当前PID覆盖PID文件。工具退出后，PID文件将自动删除。

32，--port：缩写-P，用于连接的端口号。

33，--preserve-embedded-numbers：在报告中的样本中，保存数据库/表名称中保留数字。使得SELECT * FROM db1.table2不会变成SELECT * FROM db？.table？。

34，--processlist：轮询此DSN的进程列表以进行分析，并使用--interval sleep间隔：

#间隔1s执行show full processlist 拉取processlist中订阅到的慢查询转存到指定的文件。
pt-query-digest --processlist h=192.168.163.132,u=root,p=root --interval=1  --output=slowlog > /tmp/process.log

配合--run-time使用，可以限制执行时间，到时间（--iterations=1）之后退出并且打印分析。

#间隔1s执行show full processlist，永远运行，每30秒生成一次报告

pt-query-digest --processlist h=192.168.163.132,u=root,p=root --interval=1  --run-time-mode=clock --run-time=30s --iterations=0

35，--progress：打印进度，默认值：time:30。第一部分可以比例、时间或迭代周期；第二部分指定多长时间更新应打印,百分比,秒,或迭代次数。将进度报告打印到STDERR。

--progress=time,30   按照时间
--progress=percentage,10   按照百分比

36，--read-timeout：读取超时，默认0，不超时。此选项设置等待输入event的最长时间(Reading from STDIN)。适用于--processlist以外的所有类型的输入。如果在指定时间后未收到event，脚本将停止读取输入并打印其报告。如果--iterations为0或大于1，则下一个迭代将开始，否则脚本将退出。

37，--report：打印每个--group-by属性的查询分析报告（默认为yes）。如果不需要报告（例如，使用--review或--history时），则指定--no-report。

38，--report-all：打印所有查询。

39，--report-format：打印查询分析报告格式，默认：rusage,date,hostname,files,header,profile,query_report,prepared。

SECTION      PRINTS
============ ======================================================
rusage       CPU时间和内存使用情况
date         当前本地时间
hostname     运行pt-query-digest的计算机的主机名
files        输入文件读取/解析
header       整个分析运行的摘要
profile      报告概述
query_report 每个唯一查询的详细信息
prepared     准备好的陈述

这些部分按指定的顺序打印，如果一起指定，则rusage、date、files、和header会分组在一起，其他部分由空行分隔。

40，--report-histogram：报告的直方图，默认值：Query_time。绘制该属性值的分布图。基于时间的属性直方图：

# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ###########################
# 100ms  ########################################################
#    1s  ########
#  10s+

详细说明可以看上面输出值部分。

41，--resume：将最后一个文件偏移（如果有的话）写入给定的文件名。当再次使用该选项的相同值运行时，该工具将从文件中读取最后一个文件偏移，在日志中查找到该位置，然后从该点开始继续解析事件。

42，--review：类型为DSN，保存查询供以后查看，并且不要报告已看的查询。默认表是percona_schema.query_review。指定数据库（D）和表（t）的DSN选项以覆盖默认值。除非指定了--no-create-review-table，否则将自动创建数据库和表。如果该表是手动创建的，则它至少必须包含以下几列。可以出于自己的特殊目的添加更多列，但pt-query-digest不会使用它们。

表结构：

CREATE TABLE IF NOT EXISTS query_review (
   checksum     CHAR(32) NOT NULL PRIMARY KEY,
   fingerprint  TEXT NOT NULL,
   sample       TEXT NOT NULL,
   first_seen   DATETIME,
   last_seen    DATETIME,
   reviewed_by  VARCHAR(20),
   reviewed_on  DATETIME,
   comments     TEXT
)

列说明：

COLUMN       MEANING
===========  ====================================================
checksum     查询指纹的64位校验和
fingerprint  查询的抽象版本；它的主键
sample       查询样本的文本
first_seen   此类查询的最早时间戳
last_seen    此类查询的最近时间戳
reviewed_by  如果设置，此后将跳过查询
reviewed_on  没有赋予任何特殊含义
comments     没有赋予任何特殊含义

注意，fingerprint列是一类查询的主键。校验和只是该值的加密哈希，它提供了一个较短的值，该值很可能也是唯一的。解析和汇总事件后，表应为每个fingerprint包含一行。此选项取决于--group-by（默认fingerprint），否则它将无法正常工作。

43，--run-time：每个--iterations要运行多长时间。默认永远运行（CTRL-C中断）。因为--iterations默认为1，如果指定--run-time，会运行该时间，然后退出。一起指定了这两个选项以执行收集和报告周期。如使用连续输入（例如STDIN或--processlist）指定--iterations 2 --run-time 1m将导致pt-query-digest运行1分钟（1分钟x2），报告2次，每1分钟间隔一次。

pt-query-digest --processlist='h=192.168.163.132,u=root,p=root' --iterations=2 --run-time=1m

44，--run-time-mode：设置--run-time的模式，默认值：clock。可选值有：

clock：指定--run-time的实际时间，期间为每个--iterations运行该值。
event：指定日志时间，日志时间由日志中的时间戳确定。会记住所看到的第一个时间戳，并将此后的每个时间戳与第一个时间戳进行比较，以确定经过了多少日志时间。如看到的第一个时间戳是12:00:00，下一个是12:01:30，则这是1分钟30秒的日志时间。该工具将读取事件，直到日志时间大于或等于指定的--run-time值为止。
interval：指定将event划分为多个日志时间并生成报告的日志时间间隔边界。

45，--sample：过滤掉每个查询中除前N个事件之外的所有事件。查询是根据--group-by中的第一个值进行过滤的，因此默认情况下，它将按查询指纹进行过滤。

pt-query-digest --sample 2 --no-report --output slowlog slow.log

--sample 2将允许对每个指纹进行两次样本查询，与--output slowlog一起使用可用于打印查询。

46，--slave-user：设置用于连接到从的用户。此参数允许拥有其他用户，该用户在从上具有较少的特权，但必须存在于所有从站上。

47，--slave-password：设置用于连接到从的密码。可以与–slave-user一起使用，并且该用户的密码在所有从上都必须相同。

48，--set-vars：设置MySQL变量，以逗号分隔的变量=值对列表。

--set-vars wait_timeout=500

49，--show-all：显示属性的所有值，默认情况下只显示一行中包含的尽可能多的属性值。此选项允许指定将显示所有值的属性（忽略线宽）。这仅适用于具有字符串值的属性，例如用户，主机，数据库等。可以指定多个属性，以逗号分隔。

50，--since：解析比该值更新的查询。该值可以是几种类型：

1. 具有可选后缀的简单时间值N：N [shmd]，s =秒，h =小时，m =分钟，d =天（如果没有后缀，则默认为s）这就像在说“因为N [shmd]之前”

2.带可选小时，分钟，秒的完整日期：YYYY-MM-DD [HH：MM：SS]

3.简短的MySQL风格日期：YYMMDD [HH：MM：SS]

4. MySQL评估的任何时间表达式：CURRENT_DATE-间隔7天

如果提供MySQL时间表达式，需要制定DSN来连接MySQL进行计算。需要指定--until参数来限制时间。

51，--until：解析早于此值的查询，--since的类型一样。

52，--socket：指定连接套接字文件。

53，--timeline：为events显示时间线，此选项使pt-query-digest打印另一种报告：event的时间轴。每个查询仍根据--group-by进行分组并聚合，随后将按时间顺序打印。时间线报告将打印出每个类的时间戳，时间间隔，计数和值。如果只需要时间轴报告，则指定--no-report以取消默认查询分析报告。否则，时间轴报告将在响应时间配置文件的末尾打印。如：

#pt-query-digest test2-slow.log --type=slowlog --group-by distill --timeline

# ########################################################################
# distill report
# ########################################################################
# 2020-02-28T13:43:52   00:19   3 SELECT x
# 2020-02-28T13:45:22   00:48   2 SELECT mysql.user
# 2020-02-28T13:46:10   00:31   3 SELECT x
# 2020-02-28T13:47:00    0:00   1 SELECT mysql.user
# 2020-02-28T13:47:06    0:00   1 SELECT x
# 2020-02-28T13:47:10    0:00   1 SELECT mysql.user
# 2020-02-29T08:25:28 08:37:44  82 SELECT user

如果只需要时间轴报告，则指定--no-report以取消默认查询分析报告。否则，时间轴报告将在响应时间配置文件的末尾打印

54，--type：要解析的输入的日志类型。允许的类型是：

binlog：二进制日志，解析一个二进制日志文件，该文件首先使用mysqlbinlog转换为文本。
```
mysqlbinlog mysql-bin.000441> mysql-bin.000001.txt
pt-query-digest --type binlog mysql-bin.000001.txt
```
genlog：解析MySQL general log 文件。 general log 缺少很多“属性”，如Query_time，所以默认把 --order-by 更改为Query_time:cnt。
slowlog：解析MySQL格式的慢查询文件。
tcpdump：检查网络数据包并解码MySQL客户端协议，从中分析查询和响应。它不会监视网络，只解析tcpdump的输出。使用以下参数来格式化输入：-x -n -q -tttt。如：
```
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 \
  > mysql.tcp.txt

pt-query-digest --type tcpdump mysql.tcp.txt
```
自行选择其他tcpdump参数（例如-s，-c和-i）。结果如：
```
2009-04-12 09:50:16.804849 IP 127.0.0.1.42167
       > 127.0.0.1.3306: tcp 37
    0x0000:  4508 0059 6eb2 4000 4006 cde2 7f00 0001
    0x0010:  ....
```
注意：tcpdump的-c选项可以在捕获一定数量的数据包后停止！这对于测试tcpdump命令非常有用。请注意，tcpdump无法捕获Unix套接字上的流量。
```
tcpdump -i any -s 65535 -x -n -q -tttt \
   'port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2'
```
在tcpdump输出中会自动检测在端口3306上运行的所有MySQL服务器。因此，如果tcpdump out包含来自端口3306上多个服务器的数据包（例如10.0.0.1:3306、10.0.0.2:3306等），则来自所有这些服务器的所有数据包/查询将被一起分析，就像它们是一台服务器。如果要分析未在端口3306上运行的MySQL服务器的流量，请参阅--watch-server。

另请注意，在解析tcpdump输出时，pt-query-digest可能无法报告数据库中的查询。仅在新客户端的初始连接事件中或执行<USE db>时发现数据库。如果tcpdump输出中都不包含这些内容，则pt-query-digest无法发现数据库。
rawlog：不是MySQL日志，而是简单的文本文件，每行只有一条SQL语句，例如：
```
SELECT c FROM t WHERE id=1
/* Hello, world! */ SELECT * FROM t2 LIMIT 1
INSERT INTO t (a, b) VALUES ('foo', 'bar')
INSERT INTO t SELECT * FROM monkeys
```
由于rawlog没有任何指标，因此pt-query-digest的许多功能都无法使用。当唯一可用的信息是查询列表时，按计数对查询进行排名，如通过轮询SHOW PROCESSLIST进行。

55，--user：缩写-u，连接用户。

56，--variations：报告这些属性值的变化数量。显示一个类在一个属性中有多少个不同的值。此选项的通常值为arg，它显示该类中有多少个不同的查询。这对于确定查询的可缓存性很有用。

57，--watch-server：针对--type tcpdump使用，在解析tcpdump时要监视哪个服务器IP地址和端口（如“ 10.0.0.1:3306”），所有其他服务器都将被忽略。如果未指定，则通过使用端口3306或“mysql”查找任何IP地址来监视所有服务器。如果要监视混合服务器，其中一些服务器在标准端口3306上运行，而另一些服务器在非标准端口上运行，则需要为非标准端口服务器创建单独的tcpdump输出，然后为每个服务器指定此选项。

58，--version：显示版本并退出。

59，--[no]version-check：是否检查最新版本，默认检查。

60，--[no]vertical-format：是否垂直格式，报告的SQL查询中输出尾随的“ G”。

DSN选项

用于创建DSN， DSN选项以逗号分隔，每个选项都类似于option = value。区分大小写，不能有空格，并且如果值包含空格，则必须将其引起来。各个选项说明如下：

A：默认字符集
D：连接到MySQL时要使用的默认数据库。
F：从给定文件中读取默认选项。
h：连接到主机的地址。
p：连接使用的密码，如果密码包含逗号，则必须使用反斜杠将其转义。
P：用于连接的端口号。
S：连接的套接字文件。
t：指定--review或--history表名。
u：连接的用户。

总结

本文介绍了pt-query-digest的使用、各个模式、参数的说明，更多具体的信息可以见官网说明。关于一些日常的使用方法还没说明，由于篇幅的问题，将放到下一篇文章里说明。

posted @ 2020-03-01 22:37 jyzhou 阅读(5986) 评论(0) 编辑收藏举报

刷新页面返回顶部

DBA's Record

percona-toolkit 之【pt-query-digest】介绍

背景：

使用方法：

下载：

使用方法：

说明：

示例：

描述

属性

输出值

QUERY REVIEW

FINGERPRINTS

参数说明： pt-query-digest --help

DSN选项

总结

公告

DBA's Record

percona-toolkit 之 【pt-query-digest】介绍

背景：

使用方法：

下载：

使用方法：

说明：

示例：

描述

属性

输出值

QUERY REVIEW

FINGERPRINTS

参数说明： pt-query-digest --help

DSN选项

总结

公告

percona-toolkit 之【pt-query-digest】介绍