[percona-toolkit]pt-query-digest用法

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

使用说明

shell> pt-query-digest [OPTIONS] [DSN]
详情可参考
shell>pt-query-digest --help

常用参数：
--create-review-table ：当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。
--create-history-table：当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
--filter ：  对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit：限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是95%则按总响应时间占比从大到小排序，输出到总和达到95%位置截止。
--log=s ：指定输出的日志文件
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表
           中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
--review：将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。
           当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。
--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
--since：从该指定日期开始分析。
--until：截止时间，配合—since可以分析一段时间内的慢查询。

使用示例

a) pt-query-digest分析慢查询日志
shell> pt-query-digest --report slow.log

b) 报告最近半个小时的慢查询：
shell> pt-query-digest --report --since 1800s slow.log

c) 报告一个时间段的慢查询：
shell> pt-query-digest --report --since '2013-02-10 21:48:59' --until '2013-02-16 02:33:50' slow.log

d) 报告只含select语句的慢查询:
shell> pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log

e) 报告针对某个用户的慢查询:
shell> pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log

f) 报告所有的全表扫描或full join的慢查询:
shell> pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' slow.log

g) 把查询保存到query_review表
shell> pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review 
                       --create-review-table  slow.log

h) 把查询保存到query_history表
shell> pt-query-digest --user=root –password=abc123 --history  h=localhost,D=test,t=query_history
                       --create-history-table  slow.log

i) 通过tcpdump抓取mysql的tcp协议数据，然后再分析
shell> tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
shell> pt-query-digest --type tcpdump mysql.tcp.txt> slow_report.log

j) 分析binlog
shell> mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
shell> pt-query-digest --type=binlog mysql-bin000093.sql > slow_report.log

k) 分析general log
shell> pt-query-digest --type=genlog localhost.log > slow_report.log

如下为输入结果示例：

pt-query-digest /var/lib/mysql/localhost-slow.log 

# 70ms user time, 100ms system time, 25.46M rss, 222.04M vsz
# Current date: Sun Sep 4 19:41:48 2016
# Hostname: localhost.localdomain
# Files: /var/lib/mysql/localhost-slow.log
# Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________
# Time range: all events occurred at 2016-09-05T02:19:47
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 12s 12s 12s 12s 12s 0 12s
# Lock time 19ms 19ms 19ms 19ms 19ms 0 19ms
# Rows sent 0 0 0 0 0 0 0
# Rows examine 785.25k 785.25k 785.25k 785.25k 785.25k 0 785.25k
# Query size 41 41 41 41 41 0 41

# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============== ===== ======= ===== =============
# 1 0xE8455DF24FC49F59 11.5505 100.0% 1 11.5505 0.00 INSERT SELECT tb_slow

# Query 1: 0 QPS, 0x concurrency, ID 0xE8455DF24FC49F59 at byte 0 ________
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2016-09-05T02:19:47
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 100 1
# Exec time 100 12s 12s 12s 12s 12s 0 12s
# Lock time 100 19ms 19ms 19ms 19ms 19ms 0 19ms
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 100 785.25k 785.25k 785.25k 785.25k 785.25k 0 785.25k
# Query size 100 41 41 41 41 41 0 41
# String:
# Databases test
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s
# 10s+ ################################################################
# Tables
# SHOW TABLE STATUS FROM `test` LIKE 'tb_slow'\G
# SHOW CREATE TABLE `test`.`tb_slow`\G
insert into tb_slow select * from tb_slow\G
[root@localhost percona-toolkit]# pt-query-digest /var/lib/mysql/localhost-slow.log --user=root --password^C
[root@localhost percona-toolkit]# clear
[root@localhost percona-toolkit]# pt-query-digest /var/lib/mysql/localhost-slow.log --user=root --password=root --review h='localhost',D=test,t=global_query_review --no-report --create-review-table
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
together with SSL_ca_file|SSL_ca_path for verification.
If you really don't want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at /usr/bin/pt-query-digest line 11879.
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
together with SSL_ca_file|SSL_ca_path for verification.
If you really don't want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at /usr/bin/pt-query-digest line 11879.

将分析结果可视化

使用pt-query-digest分析慢查询日志并将查询分析数据保存到MySQL数据库表中.然后使用应用程序来展示分析结果.
目前有基于LAMP的Query-Digest-UI、Anemometer开源项目支持。

将慢日志插入表中：
shell> pt-query-digest --user=root --password=abc224 --review h='10.10.10.134',D=test,t=global_query_review --history h='10.10.10.134',D=test,t=global_query_review_history --no-report --create-review-table --create-history-table --limit=20% slowquery.log

或者
shell> pt-query-digest --user=root --password=abc224 --review h='10.10.10.134',D=test,t=global_query_review --no-report --create-review-table slowquery.log


shell> pt-query-digest --user=root --password=abc224 --history h='10.10.10.134',D=test,t=global_query_review_history --no-report --create-history-table slowquery.log


mysql> select * from global_query_review limit 2 \G
*************************** 1. row ***************************
   checksum: 300935684267402542
fingerprint: call test.confixinverstweek
     sample: CALL test.confixinverstweek('2014-07-01','2014-07-10',0.0060)
 first_seen: 2014-07-09 14:35:29
  last_seen: 2014-07-14 08:04:11
reviewed_by: NULL
reviewed_on: NULL
   comments: NULL

mysql> select * from global_query_review_history limit 1 \G
*************************** 1. row ***************************
                    checksum: 300935684267402542
                      sample: CALL test.confixinverstweek('2014-07-01','2014-07-10',0.0060)
                      ts_min: 2014-07-09 14:35:29
                      ts_max: 2014-07-14 08:04:11
                      ts_cnt: 4
              Query_time_sum: 419674
              Query_time_min: 13882.8
              Query_time_max: 227433
           Query_time_pct_95: 216908
           Query_time_stddev: 90097.8
           Query_time_median: 189384
               Lock_time_sum: 0
               Lock_time_min: 0
               Lock_time_max: 0
            Lock_time_pct_95: 0
            Lock_time_stddev: 0
            Lock_time_median: 0
               Rows_sent_sum: 0
               Rows_sent_min: 0
               Rows_sent_max: 0
            Rows_sent_pct_95: 0
            Rows_sent_stddev: 0
            Rows_sent_median: 0
           Rows_examined_sum: 0
           Rows_examined_min: 0
           Rows_examined_max: 0
        Rows_examined_pct_95: 0
        Rows_examined_stddev: 0
        Rows_examined_median: 0
           Rows_affected_sum: NULL
           Rows_affected_min: NULL
           Rows_affected_max: NULL
        Rows_affected_pct_95: NULL
        Rows_affected_stddev: NULL
        Rows_affected_median: NULL
               Rows_read_sum: NULL
               Rows_read_min: NULL
               Rows_read_max: NULL
            Rows_read_pct_95: NULL
            Rows_read_stddev: NULL
            Rows_read_median: NULL
            Merge_passes_sum: NULL
            Merge_passes_min: NULL
            Merge_passes_max: NULL
         Merge_passes_pct_95: NULL
         Merge_passes_stddev: NULL
         Merge_passes_median: NULL
         InnoDB_IO_r_ops_min: NULL
         InnoDB_IO_r_ops_max: NULL
      InnoDB_IO_r_ops_pct_95: NULL
      InnoDB_IO_r_ops_stddev: NULL
      InnoDB_IO_r_ops_median: NULL
       InnoDB_IO_r_bytes_min: NULL
       InnoDB_IO_r_bytes_max: NULL
    InnoDB_IO_r_bytes_pct_95: NULL
    InnoDB_IO_r_bytes_stddev: NULL
    InnoDB_IO_r_bytes_median: NULL
        InnoDB_IO_r_wait_min: NULL
        InnoDB_IO_r_wait_max: NULL
     InnoDB_IO_r_wait_pct_95: NULL
     InnoDB_IO_r_wait_stddev: NULL
     InnoDB_IO_r_wait_median: NULL
    InnoDB_rec_lock_wait_min: NULL
    InnoDB_rec_lock_wait_max: NULL
 InnoDB_rec_lock_wait_pct_95: NULL
 InnoDB_rec_lock_wait_stddev: NULL
 InnoDB_rec_lock_wait_median: NULL
       InnoDB_queue_wait_min: NULL
       InnoDB_queue_wait_max: NULL
    InnoDB_queue_wait_pct_95: NULL
    InnoDB_queue_wait_stddev: NULL
    InnoDB_queue_wait_median: NULL
   InnoDB_pages_distinct_min: NULL
   InnoDB_pages_distinct_max: NULL
InnoDB_pages_distinct_pct_95: NULL
InnoDB_pages_distinct_stddev: NULL
InnoDB_pages_distinct_median: NULL
                  QC_Hit_cnt: NULL
                  QC_Hit_sum: NULL
               Full_scan_cnt: NULL
               Full_scan_sum: NULL
               Full_join_cnt: NULL
               Full_join_sum: NULL
               Tmp_table_cnt: NULL
               Tmp_table_sum: NULL
       Tmp_table_on_disk_cnt: NULL
       Tmp_table_on_disk_sum: NULL
                Filesort_cnt: NULL
                Filesort_sum: NULL
        Filesort_on_disk_cnt: NULL
        Filesort_on_disk_sum: NULL
1 row in set (0.00 sec)