pt-query-digest用法

来源：互联网发布：自己做斗图的软件编辑：程序博客网时间：2024/06/11 09:41

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.logb) 报告最近半个小时的慢查询：shell> pt-query-digest --report --since 1800s slow.logc) 报告一个时间段的慢查询：shell> pt-query-digest --report --since '2013-02-10 21:48:59' --until '2013-02-16 02:33:50' slow.logd) 报告只含select语句的慢查询:shell> pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.loge) 报告针对某个用户的慢查询:shell> pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.logf) 报告所有的全表扫描或full join的慢查询:shell> pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' slow.logg) 把查询保存到query_review表shell> pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review                        --create-review-table  slow.logh) 把查询保存到query_history表shell> pt-query-digest --user=root –password=abc123 --history  h=localhost,D=test,t=query_history                       --create-history-table  slow.logi) 通过tcpdump抓取mysql的tcp协议数据，然后再分析shell> tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txtshell> pt-query-digest --type tcpdump mysql.tcp.txt> slow_report.logj) 分析binlogshell> mysqlbinlog mysql-bin.000093 > mysql-bin000093.sqlshell> pt-query-digest --type=binlog mysql-bin000093.sql > slow_report.logk) 分析general logshell> pt-query-digest --type=genlog localhost.log > slow_report.log如下为输入结果示例：shell> pt-query-digest --report --since '2014-03-16 00:00:00' --until '2014-03-18 15:00:00' localhost-slow.log # 1.4s user time, 560ms system time, 14.55M rss, 20.66M vsz# Current date: Wed Mar 18 15:58:13 2015# Hostname: rac2# Files: localhost-slow.log# Overall: 146 total, 26 unique, 0.00 QPS, 0.01x concurrency _____________# Time range: 2014-03-16 00:15:21 to 2014-03-18 12:54:55# Attribute          total     min     max     avg     95%  stddev  median# ============     ======= ======= ======= ======= ======= ======= =======# Exec time          3052s      3s    329s     21s     73s     46s      6s# Lock time           75ms       0    52ms   513us   384us     4ms       0# Rows sent        153.24M       0   9.70M   1.05M   6.29M   1.98M  76.03k# Rows examine     294.40M       0  27.08M   2.02M   8.86M   3.50M 380.41k# Query size        30.12k      21   2.66k  211.25  793.42  304.51   56.92# Profile# Rank Query ID           Response time   Calls R/Call   V/M   Item# ==== ================== =============== ===== ======== ===== ===========#    1 0x924A00E4A9B5281D 1279.0169 41.9%    12 106.5847 13... SELECT ...#    2 0x67A347A2812914DF 1130.2242 37.0%    75  15.0697 29.24 SELECT ...#    3 0x0EF758B84C68B150  285.1478  9.3%     6  47.5246 10.37 SELECT ...#    4 0x9819CE7C2E230790   64.1319  2.1%    10   6.4132  1.36 SELECT ...#    5 0x68669DF0A107825A   43.8553  1.4%     6   7.3092  2.30 SELECT ...#    6 0x7E5DC47A12C9AA5B   39.9418  1.3%     8   4.9927  0.21 SELECT ...#    7 0xBF7F26947159B76E   27.5826  0.9%     2  13.7913  0.74 SELECT ...#    8 0x038D7A7043C978D4   27.5193  0.9%     3   9.1731  0.00 SELECT ...#    9 0x84C28126923E551C   22.6308  0.7%     1  22.6308  0.00 SELECT ...# MISC 0xMISC              131.8447  4.3%    23   5.7324   0.0 <17 ITEMS># Query 9: 0 QPS, 0x concurrency, ID 0x84C28126923E551C at byte 158279 ___# Scores: V/M = 0.00# Time range: all events occurred at 2014-03-16 14:06:14# Attribute    pct   total     min     max     avg     95%  stddev  median# ============ === ======= ======= ======= ======= ======= ======= =======# Count          0       1# Exec time      0     23s     23s     23s     23s     23s       0     23s# Lock time      0   179us   179us   179us   179us   179us       0   179us# Rows sent      0     286     286     286     286     286       0     286# Rows examine   0   2.63k   2.63k   2.63k   2.63k   2.63k       0   2.63k# Query size     0     128     128     128     128     128       0     128# String:# Databases    yilucaifu# Hosts# Users        root# Query_time distribution#   1us#  10us# 100us#   1ms#  10ms# 100ms#    1s#  10s+  ################################################################# Tables#    SHOW TABLE STATUS FROM `yilucaifu` LIKE 'pingan_tranaccount'\G#    SHOW CREATE TABLE `yilucaifu`.`pingan_tranaccount`\G#    SHOW TABLE STATUS FROM `yilucaifu` LIKE 'activity_38_code'\G#    SHOW CREATE TABLE `yilucaifu`.`activity_38_code`\G# EXPLAIN /*!50100 PARTITIONS*/select user_id     from yilucaifu.pingan_tranaccount  where user_id not in(select user_id from  activity_38_code where status =1 )\G

将分析结果可视化

使用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.logshell> 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.logmysql> select * from global_query_review limit 2 \G*************************** 1. row ***************************   checksum: 300935684267402542fingerprint: 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:11reviewed_by: NULLreviewed_on: NULL   comments: NULLmysql> 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: NULLInnoDB_pages_distinct_pct_95: NULLInnoDB_pages_distinct_stddev: NULLInnoDB_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: NULL1 row in set (0.00 sec)

也可以自己做一个简单的web程序，即可获取慢查询日志的结果。

0 0