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 如下为输入结果示例: 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.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)
也可以自己做一个简单的web程序,即可获取慢查询日志的结果。