MySQL性能剖析工具(pt-query-digest)【转】

╰半夏微凉° 2022-03-29 15:40 184阅读 0赞

这个工具同样来自percona-toolkit  
该工具集合的其他工具  
MySQL Slave异常关机的处理 (pt-slave-restart)  
  
验证MySQL主从一致性(pt-table-checksum&pt-table-sync) [http://www.linuxidc.com/Linux/2014-07/104034.htm][http_www.linuxidc.com_Linux_2014-07_104034.htm]  
  
它可以剖析各种日志,从而分析性能问题 [http://www.linuxidc.com/Linux/2014-07/104236.htm][http_www.linuxidc.com_Linux_2014-07_104236.htm]  
包括  
1.慢日志(默认)  
2.tcpdump  
3.general log  
4.binlog  
5.show processlist  
  
实验通过tpcc模拟一定的压力,并开启binlog,general log,slow log和tcpdump收集日志。  
关于tpcc参考: [http://www.linuxidc.com/Linux/2014-07/104238.htm][http_www.linuxidc.com_Linux_2014-07_104238.htm]  
  
1.slow log方式  
将slow\_query\_log设置为on  
long\_query\_time设置为0  
重新连接服务器(一般意味着中间件服务器重启)  
这样慢日志将记录所有的操作。  
  
通过pt-query-digest分析慢日志文件,生成剖析报告  
首先是概要信息,包括一共有54.17k个操作,46个语句,平均每秒1.55k查询,剖析报告覆盖的时间等。

![140716101592781.png][]  
  
概要还包括类似Top SQL的分析  
可以从响应时间查看对服务器性能影响最大的SQL,其中R/Call指的是 平均每次执行的响应时间 ResponseTime/Calls

![140716101592782.png][]  
  
然后就是每条SQL的详细剖析报告,  
pct指的是该项占概要报告中的百分比

![140716101592783.png][]

分析pt-query-digest输出结果。  
1. 工具简介  
pt-query-digest是用于分析my[SQL][]慢查询的一个工具，它可以分析binlog、General log、slowlog，也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。  
安装  
pt-query-digest  
  
yum install perl-IO-Socket-SSL  
yum install perl-DBD-MySQL  
rpm -ivh percona-toolkit-2.2.9-1.noarch.rpm  
2.语法及重要选项  
pt-query-digest \[OPTIONS\] \[FILES\] \[DSN\]  
\--create-review-table 当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。  
\--create-history-table 当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。  
\--filter 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析  
\--limit限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。  
\--host mysql服务器地址  
\--user mysql用户名  
\--password mysql用户密码  
\--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。  
\--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。  
\--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可以分析一段时间内的慢查询。  
  
  
第一部分：总体统计结果  
  
  
Overall: 总共有多少条查询，  
Time range: 查询执行的时间范围。  
unique: 唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询.  
total: 总计 min:最小 max: 最大 avg:平均  
95%: 把所有值从小到大排列，位置位于95%的那个数，这个数一般最具有参考价值。  
median: 中位数，把所有值从小到大排列，位置位于中间那个数。  
  
  
\# 59.5s user time, 90ms system time, 51.81M rss, 228.12M vsz  
\# Current date: Sun Aug 3 16:09:31 2014  
\# Hostname: db1.test.com  
\# Files: ./slowquery.log  
\# Overall: 224.01k total, 570 unique, 0.01 QPS, 0.09x concurrency \_\_\_\_\_\_\_\_  
\# Time range: 2013-10-09 17:55:04 to 2014-08-03 15:16:38  
\# Attribute total min max avg 95% stddev median  
\# ============ ======= ======= ======= ======= ======= ======= =======  
\# Exec time 2188385s 2s 7229s 10s 13s 67s 5s  
\# Lock time 100721s 0 365s 450ms 2s 5s 119us  
\# Rows sent 26.98G 0 3.30M 126.27k 328.61k 371.66k 4.96  
\# Rows examine 394.85G 0 3.32G 1.80M 3.03M 20.83M 562.03k  
\# Query size 78.65M 6 5.54k 368.18 685.39 241.61 271.23  
  
第二部分：查询分组统计结果  
  
  
Response: 总的响应时间。  
time: 该查询在本次分析中总的时间占比。  
calls: 执行次数，即本次分析总共有多少条这种类型的查询语句。  
R/Call: 平均每次执行的响应时间。  
Item : 查询对象  
  
\# Profile  
\# Rank Query ID Response time Calls R/Call V/M Item  
\# ==== ================== ================= ===== ========= ===== ========  
\# 1 0xA6FE3D6982868655 351140.1266 16.0% 622 564.5340 99... CREATE TABLE dw\_user\_cache \`dw\_user\_cache\`  
\# 2 0x281C83DE62A11A8B 310896.7675 14.2% 40841 7.6124 6.51 SELECT dw\_borrow\_tender dw\_borrow dw\_user dw\_credit dw\_credit\_rank  
\# 3 0x26BA6BEAE6C74350 279545.6534 12.8% 1305 214.2112 25... SELECT dw\_account\_log  
\# 4 0x54032826ADCE00D8 265383.3007 12.1% 27125 9.7837 0.75 SELECT dw\_borrow dw\_user  
\# 5 0xC6D55EBA716FD06A 155096.6704 7.1% 22411 6.9206 0.76 SELECT dw\_borrow\_tender dw\_borrow\_collection  
\# 6 0x891024348B7FA6E6 114279.5309 5.2% 40740 2.8051 0.74 SELECT dw\_borrow\_collection dw\_borrow\_tender dw\_borrow  
\# 7 0x1FDB4303FDF6B0E2 107444.3602 4.9% 3575 30.0544 82.45 SELECT dw\_borrow dw\_user dw\_user\_cache dw\_user dw\_credit dw\_credit\_rank dw\_userinfo  
\# 8 0x23EAAA4FF23807E9 66187.2020 3.0% 8618 7.6801 2.57 SELECT dw\_borrow\_tender dw\_borrow dw\_user  
\# 9 0x67A347A2812914DF 57999.8268 2.7% 11874 4.8846 1.30 SELECT dw\_account\_log  
\# 10 0xCB5FB4ADA3DC39A6 50472.1849 2.3% 553 91.2698 13... SELECT dw\_borrow dw\_user dw\_user\_cache dw\_user dw\_credit dw\_credit\_rank dw\_userinfo  
\# 11 0xB5D71E48F29AC3E2 27186.7335 1.2% 31 876.9914 10... SELECT dw\_user\_cache dw\_user  
\# 12 0x686CD345F7BADD7D 25119.7512 1.1% 9021 2.7846 0.11 SELECT dw\_borrow\_collection dw\_borrow\_tender dw\_borrow  
\# 13 0x1A264A69F9BA0C4E 17611.1472 0.8% 3435 5.1270 22.25 SELECT dw\_account\_log  
\# 14 0x61EAA51EF025D8EF 16301.1821 0.7% 6428 2.5360 0.32 SELECT dw\_borrow\_collection dw\_borrow\_tender  
\# 15 0xD4B6AFA2D05E8D88 15652.0490 0.7% 75 208.6940 56... CREATE TABLE dw\_article\_fields \`dw\_article\_fields\`  
\# 16 0x5904F73A43C7B7EC 14959.7711 0.7% 9 1662.1968 75... SELECT dw\_borrow dw\_user dw\_user\_cache dw\_user dw\_credit dw\_credit\_rank dw\_userinfo  
\# 17 0x9CF37BD6ACAF1BF2 14231.6849 0.7% 2 7115.8425 3.60 SELECT dw\_borrow\_collection\_copy  
\# 18 0x4612678E31E093F7 12925.8280 0.6% 1055 12.2520 2.26 SELECT dw\_borrow dw\_user dw\_user\_cache dw\_user dw\_credit dw\_credit\_rank dw\_userinfo  
\# 19 0xC12857BE61793BE1 12892.1801 0.6% 27 477.4882 27... SELECT dw\_borrow dw\_user dw\_user\_cache dw\_user dw\_credit dw\_credit\_rank dw\_userinfo  
\# 20 0x024C0F93B0BADD04 12469.0146 0.6% 4891 2.5494 0.35 SELECT dw\_borrow\_collection dw\_borrow\_tender dw\_borrow  
\# 21 0x1A049985AE4E2F6A 12448.3880 0.6% 2054 6.0606 13.18 SELECT dw\_user dw\_ads\_follow dw\_account dw\_account\_recharge dw\_user dw\_borrow\_tender  
\# 22 0xC98B23B65503594B 12289.8756 0.6% 4722 2.6027 0.31 SELECT dw\_borrow\_collection dw\_borrow\_tender dw\_borrow  
\# 23 0x13764521ADA98ABB 12098.7824 0.6% 2075 5.8307 3.76 SELECT dw\_user dw\_ads\_follow dw\_account dw\_account\_recharge dw\_user dw\_borrow\_tender  
\# 24 0x2D03525FD4212FA3 10548.0904 0.5% 794 13.2847 2.37 SELECT dw\_user dw\_ads\_follow dw\_account dw\_account\_recharge dw\_user dw\_borrow\_tender  
\# 25 0xA12C03F6EC113579 10464.2831 0.5% 22 475.6492 27... SELECT dw\_borrow dw\_user dw\_user\_cache dw\_user dw\_credit dw\_credit\_rank dw\_userinfo  
\# 26 0x9444FF6F268AC28D 9747.2537 0.4% 3199 3.0470 0.56 SELECT dw\_borrow\_tender dw\_borrow\_collection dw\_borrow  
\# 27 0x555478B2936ABEA4 9395.2045 0.4% 799 11.7587 1.75 SELECT dw\_user dw\_ads\_follow dw\_account dw\_account\_recharge dw\_user dw\_borrow\_tender  
\# 28 0x995ECC95C327A34F 7285.6256 0.3% 703 10.3636 5.23 SELECT dw\_borrow\_repayment dw\_borrow  
\# 29 0xEECF212D98D23FF4 7124.1984 0.3% 2902 2.4549 0.22 SELECT dw\_borrow\_collection dw\_borrow\_tender  
\# 31 0xEFD47B7218EAEE88 6104.9908 0.3% 829 7.3643 1.42 SELECT dw\_message  
\# 33 0xD2B792BCA787A2A7 5593.2295 0.3% 455 12.2928 10.04 UPDATE dw\_bbs\_topics  
\# 34 0x9416D3806CE5C1CF 5547.2043 0.3% 1933 2.8697 0.68 SELECT UNION dw\_borrow borrow\_user dw\_user borrow\_user  
\# 36 0xC2A701837CE5A7FE 3829.0378 0.2% 126 30.3892 53.47 SELECT dw\_account\_log dw\_user  
\# 37 0xFDD6D01D6A276759 3691.1291 0.2% 1688 2.1867 0.01 SELECT dw\_message  
\# 38 0x0D4073804271EE14 3320.3629 0.2% 1343 2.4723 0.20 SELECT dw\_borrow dw\_borrow\_tender  
\# 40 0x16CB5BD2CAE695D9 2842.7850 0.1% 80 35.5348 16.84 SELECT dw\_account\_log  
\# 41 0xCE38615B9A8B1E11 2789.2434 0.1% 1153 2.4191 0.41 SELECT dw\_integral\_log dw\_user  
\# 42 0x681A5E20DEABF1AA 2678.5683 0.1% 930 2.8802 0.13 SELECT dw\_borrow\_tender dw\_borrow\_collection dw\_borrow  
\# 45 0x65F9D35DAFC8FB11 2378.6930 0.1% 45 52.8598 50.17 SELECT dw\_user dw\_bbs\_sign  
\# 46 0x1D7CBA0BE5BAA758 2347.4957 0.1% 394 5.9581 0.28 SHOW TRIGGERS  
\# 47 0x379B8D78AD4B43AD 2344.8506 0.1% 808 2.9020 0.67 SELECT UNION dw\_borrow borrow\_user dw\_user borrow\_user  
\# 49 0x3E5A84448E22C253 2204.6833 0.1% 921 2.3938 0.05 SELECT dw\_borrow\_repayment dw\_borrow  
\# 50 0xC0C703ACF4354212 2167.3205 0.1% 135 16.0542 2.12 SELECT dw\_borrow dw\_user dw\_user\_cache dw\_user dw\_userinfo  
\# 52 0xEBF69B24CCDCC03E 1975.5275 0.1% 669 2.9530 0.27 SELECT dw\_borrow\_collection dw\_borrow\_tender dw\_borrow  
\# 53 0xEE8F8F0DB5AA81CE 1946.5688 0.1% 28 69.5203 44.61 SELECT dw\_account\_log dw\_user  
\# 54 0xC2DE9ED4D64CFBF1 1871.9631 0.1% 23 81.3897 37... SELECT dw\_user\_cache  
\# 56 0x2CFBC4719E02C96F 1790.8727 0.1% 677 2.6453 4.63 SELECT dw\_borrow\_collection dw\_borrow\_tender  
\# 58 0x6B40F72020D66B39 1776.9902 0.1% 60 29.6165 35.89 SELECT dw\_user dw\_bbs\_sign  
\# 59 0xA4BA1B440B9D081D 1750.4325 0.1% 255 6.8644 3.02 SELECT dw\_account\_cash dw\_user dw\_account dw\_borrow\_repayment dw\_borrow dw\_account\_cash  
\# 60 0x881A6F4F54C6F1B1 1741.5004 0.1% 309 5.6359 0.10 SELECT dw\_account\_log dw\_linkage  
\# 61 0x813031B8BBC3B329 1734.2587 0.1% 646 2.6846 0.31 COMMIT  
\# 62 0x51E39E49DBEDAD2C 1658.0404 0.1% 669 2.4784 0.29 SELECT dw\_borrow\_collection dw\_borrow\_tender  
\# 63 0xF56BEC75221FB39D 1600.5334 0.1% 150 10.6702 2.24 SELECT dw\_borrow\_repayment dw\_borrow  
\# 64 0x3A38FE07E3E890D8 1595.9906 0.1% 210 7.6000 10.33 SELECT dw\_account\_recharge  
\# 65 0xAAA8996E72F813FE 1552.8871 0.1% 326 4.7635 0.62 SELECT dw\_borrow  
\# 66 0x142368E967FCA86A 1540.1275 0.1% 618 2.4921 0.08 SELECT dw\_borrow\_repayment dw\_borrow  
\# 67 0x051DE37D2AE3FF84 1362.0441 0.1% 315 4.3239 0.21 SELECT dw\_account\_log dw\_user  
\# 68 0xE104C8459E6B7739 1278.6921 0.1% 18 71.0385 18.13 SELECT lidai?.dw\_account\_log  
\# 69 0xFD4D67F2AFFDE590 1243.9355 0.1% 166 7.4936 3.52 SELECT dw\_user dw\_ads\_follow dw\_account dw\_area dw\_account\_recharge dw\_user dw\_borrow\_tender  
\# 70 0x9BB3ED25A96174CD 1241.0157 0.1% 42 29.5480 54.34 SELECT dw\_account\_log  
\# 72 0x228CCC75C8DA443F 1189.0420 0.1% 188 6.3247 2.40 SELECT dw\_account\_cash dw\_user dw\_account dw\_borrow\_repayment dw\_borrow dw\_account\_cash  
\# 73 0xCA233CB059ECB901 1165.4672 0.1% 32 36.4208 53.35 SELECT dw\_account\_log dw\_user  
\# 74 0x8CCB871394927FE2 1136.1386 0.1% 181 6.2770 2.37 SELECT dw\_account\_cash dw\_user dw\_account dw\_borrow\_repayment dw\_borrow dw\_account\_cash  
\# 76 0x3600A1B49DA394EE 1038.2666 0.0% 156 6.6556 0.17 SELECT dw\_borrow\_tender dw\_borrow\_collection  
\# 77 0xD029943043D50CC0 994.4349 0.0% 26 38.2475 58.59 SELECT dw\_account\_log dw\_user  
\# 78 0x308559BB1D081EB3 993.8470 0.0% 61 16.2926 6.03 INSERT dw\_transfer\_log  
\# 79 0x51ABC278F244C387 981.0594 0.0% 10 98.1059 19... SELECT dw\_borrow\_collection dw\_borrow\_tender dw\_borrow dw\_user dw\_borrow\_tender  
\# 80 0x6FCA79342C1FB8FA 957.7486 0.0% 95 10.0816 2.31 SELECT dw\_borrow\_repayment dw\_borrow  
\# 81 0x4D8C32BBA9D1A5D5 940.0878 0.0% 50 18.8018 6.23 UPDATE dw\_message  
\# 83 0x309DC411C96D87ED 908.6600 0.0% 379 2.3975 0.07 SELECT dw\_borrow\_tender dw\_borrow\_collection  
\# 85 0x7C81069C58672068 857.6587 0.0% 97 8.8418 1.21 SELECT dw\_borrow dw\_user dw\_user\_cache dw\_user dw\_credit dw\_credit\_rank dw\_userinfo  
\# 87 0x3C82EB77CDADEA17 776.0847 0.0% 276 2.8119 0.24 SELECT dw\_account\_log dw\_user  
\# 89 0x306AA9F2019395FE 744.0710 0.0% 267 2.7868 0.13 SELECT dw\_borrow\_tender dw\_borrow\_collection dw\_borrow  
\# 90 0xEF0E7B1753BA0407 740.2857 0.0% 40 18.5071 16.39 SELECT dw\_bbs\_posts dw\_user dw\_invest\_rank  
\# 91 0xE20308187C743605 733.2368 0.0% 150 4.8882 0.69 SELECT UNION dw\_bbs\_topics dw\_bbs\_forums dw\_bbs\_topics dw\_bbs\_forums dw\_bbs\_topics dw\_bbs\_forums dw\_bbs\_topics dw\_bbs\_forums dw\_bbs\_topics dw\_bbs\_forums dw\_bbs\_topics dw\_bbs\_forums  
\# 92 0x68C8A29EB43D1186 716.1971 0.0% 156 4.5910 0.54 SELECT dw\_bbs\_posts dw\_user dw\_invest\_rank  
\# 93 0x0D41C9E5D21B88EF 712.5703 0.0% 244 2.9204 0.71 SELECT UNION dw\_borrow borrow\_user dw\_user borrow\_user  
\# 95 0x6F7E2BE278B260D4 673.7594 0.0% 49 13.7502 9.00 UPDATE dw\_borrow\_auto  
\# 96 0xF1E0B265894B262B 671.1602 0.0% 99 6.7794 0.38 SELECT dw\_bbs\_posts dw\_user dw\_invest\_rank  
\# 97 0xC225A7E494355944 669.3832 0.0% 77 8.6933 2.07 SELECT dw\_message dw\_user  
\# 98 0xB9006A83640F6AC4 668.7845 0.0% 42 15.9234 8.24 UPDATE dw\_article  
\# 99 0x5F1EE78CA3C29A6C 668.4948 0.0% 83 8.0542 3.15 SELECT dw\_account\_cash dw\_user dw\_account dw\_borrow\_repayment dw\_borrow dw\_account\_cash  
\# 100 0x17C2D8B7730B018B 642.9315 0.0% 79 8.1384 3.07 SELECT dw\_account\_cash dw\_user dw\_account dw\_borrow\_repayment dw\_borrow dw\_account\_cash  
\# 103 0x199E0A173DFA2D80 599.9491 0.0% 87 6.8960 1.02 SELECT dw\_comment dw\_user dw\_module  
\# 107 0x7D605E5950487AF5 558.2827 0.0% 60 9.3047 11.81 SELECT dw\_area  
\# 108 0x0D182DEBDC8DF32D 541.3427 0.0% 23 23.5366 17.57 SELECT dw\_borrow\_repayment dw\_borrow  
\# 109 0x1C3B9CCBE8BE6C6C 535.3157 0.0% 48 11.1524 4.17 SELECT dw\_attestation dw\_attestation\_type dw\_user  
\# 111 0xF815D99831467E87 504.3610 0.0% 212 2.3791 0.09 SELECT dw\_account\_log  
\# 113 0x2D4FD3C169BBFB29 498.1389 0.0% 59 8.4430 1.62 SELECT dw\_borrow\_collection dw\_borrow\_tender  
\# 114 0x532A0D75D5415F12 497.8568 0.0% 50 9.9571 5.48 SELECT dw\_borrow  
\# 116 0x2171E402F72B1ABD 488.1605 0.0% 11 44.3782 18... SELECT dw\_borrow dw\_user dw\_user\_cache dw\_user dw\_credit dw\_credit\_rank dw\_userinfo  
\# 117 0x8833A5FF928CE59D 430.9204 0.0% 61 7.0643 2.63 SELECT dw\_site dw\_module  
\# 118 0x15F6EC5D6E55E9EB 416.0158 0.0% 12 34.6680 0.90 CREATE TABLE dw\_account\_log dw\_account\_log\_20140304  
\# 119 0xAC2E0CFFD22AAD91 411.8911 0.0% 153 2.6921 0.15 SELECT dw\_borrow\_collection dw\_borrow\_tender dw\_borrow  
\# 120 0xBBB4FC802F4DAEBD 405.2085 0.0% 144 2.8139 0.40 SELECT UNION dw\_borrow borrow\_user dw\_user borrow\_user  
\# 121 0x44104FF78FB2CCDE 401.2097 0.0% 146 2.7480 0.31 SELECT dw\_account\_recharge  
\# 122 0x78BBE98AB1D62FC0 400.6647 0.0% 65 6.1641 0.32 SELECT dw\_message dw\_user  
\# 123 0xCB04FB69656A84E7 397.4606 0.0% 72 5.5203 0.10 SELECT dw\_account\_log  
\# 124 0x9003310D5229035D 397.0504 0.0% 81 4.9019 2.68 SELECT dw\_account\_cash dw\_user dw\_account dw\_borrow\_repayment dw\_borrow dw\_account\_cash  
\# 125 0x4A958BB35A545E98 395.9654 0.0% 157 2.5221 0.19 SELECT UNION dw\_borrow borrow\_user dw\_user borrow\_user  
\# 126 0xDB0585772DE69172 388.0071 0.0% 20 19.4004 3.48 SELECT dw\_user  
\# 127 0x66B4675D1C677106 379.6958 0.0% 93 4.0828 0.61 SELECT dw\_borrow\_auto  
\# 128 0xA5B0886841754810 377.7354 0.0% 76 4.9702 2.06 SELECT dw\_account\_cash dw\_user dw\_account dw\_borrow\_repayment dw\_borrow dw\_account\_cash  
\# 129 0x883A88F83A0530F2 362.6208 0.0% 79 4.5901 0.61 SELECT dw\_borrow  
\# 130 0xBBCCE076172F07D3 360.3024 0.0% 123 2.9293 0.25 SELECT dw\_borrow\_collection dw\_borrow\_tender dw\_borrow  
\# 131 0xB08E8DB82C9AD1C6 359.2024 0.0% 108 3.3259 1.05 SELECT dw\_borrow\_tender dw\_borrow\_collection  
\# 132 0x9BFDE7672F9A65FC 356.0283 0.0% 110 3.2366 0.19 SELECT dw\_borrow borrow\_user dw\_credit\_rank dw\_borrow\_repayment  
\# 133 0x726230A00EABE213 355.6093 0.0% 149 2.3866 0.06 SELECT dw\_borrow\_repayment dw\_borrow  
\# 134 0xCAE05A054C5751A0 334.8765 0.0% 21 15.9465 6.27 SELECT dw\_bbs\_topics dw\_bbs\_forums dw\_bbs\_posts  
\# 135 0x380E9B81C2FC6900 332.9056 0.0% 114 2.9202 0.74 SELECT UNION dw\_borrow borrow\_user dw\_user borrow\_user  
\# 137 0x2179C1DF0BE263CC 303.0903 0.0% 111 2.7305 0.15 SELECT dw\_borrow\_collection dw\_borrow\_tender dw\_borrow  
\# 139 0x4591ED3FA8763186 276.1440 0.0% 98 2.8178 0.21 SELECT dw\_borrow\_collection dw\_borrow\_tender dw\_borrow  
\# 141 0xB657A701DC4D6A00 262.7191 0.0% 42 6.2552 0.33 SELECT dw\_borrow\_collection dw\_borrow\_tender dw\_borrow dw\_user dw\_borrow\_tender  
\# 142 0xD0F123F2AC3F0660 259.0045 0.0% 16 16.1878 4.30 SELECT dw\_borrow dw\_user dw\_user\_cache dw\_user dw\_credit dw\_credit\_rank dw\_userinfo  
\# 143 0x82B988A48DEB7C90 256.4613 0.0% 57 4.4993 0.46 SELECT dw\_bbs\_topics  
\# 145 0x8634C8FABFAFF8F8 249.5191 0.0% 11 22.6836 13.25 SELECT dw\_borrow dw\_user dw\_user\_cache dw\_user dw\_credit dw\_credit\_rank dw\_userinfo  
\# 147 0x89230C39B41A9FDA 242.6061 0.0% 19 12.7687 5.18 SELECT dw\_borrow\_repayment dw\_borrow  
\# 148 0x6439A5DD6746322B 236.1913 0.0% 18 13.1217 2.89 SELECT dw\_user dw\_user\_type dw\_user\_cache dw\_account dw\_userinfo dw\_invest\_rank  
\# 149 0x82F89C1AFAA0F387 227.9793 0.0% 39 5.8456 4.42 SELECT dw\_account\_cash dw\_user dw\_account dw\_borrow\_repayment dw\_borrow dw\_account\_cash  
\# 150 0x1BB252D4DC558785 227.0967 0.0% 21 10.8141 7.80 SELECT dw\_account\_cash dw\_user dw\_account dw\_borrow\_repayment dw\_borrow dw\_account\_cash  
\# 152 0xA1FFA0272E46CD57 225.3568 0.0% 11 20.4870 34.87 SELECT lidai?.dw\_account\_recharge  
\# 153 0x652B44DF36C9FB03 224.8771 0.0% 34 6.6140 0.25 SELECT dw\_borrow\_repayment dw\_borrow  
\# 156 0x6BBC42CD855B3E30 213.9297 0.0% 13 16.4561 7.20 UPDATE dw\_message  
\# 157 0x4DE088D335ACA923 209.3560 0.0% 48 4.3616 0.54 SELECT dw\_borrow  
\# 158 0xE50C92AFDD288B33 207.4203 0.0% 20 10.3710 7.72 SELECT dw\_account\_cash dw\_user dw\_account dw\_borrow\_repayment dw\_borrow dw\_account\_cash  
\# 159 0x22B38CCBBA0E8D7A 207.2121 0.0% 17 12.1889 16.99 SELECT dw\_borrow dw\_user dw\_user\_cache dw\_user dw\_credit dw\_credit\_rank dw\_userinfo  
\# 160 0x27DEE5AB1C9DC4AE 207.1853 0.0% 13 15.9373 7.88 SELECT dw\_borrow dw\_user dw\_user\_cache dw\_user dw\_credit dw\_credit\_rank dw\_userinfo  
\# 161 0xB1E0DF5351F8D287 205.0043 0.0% 32 6.4064 0.17 SELECT dw\_borrow dw\_user  
\# 162 0x1458FA7D05A7AEE5 204.8885 0.0% 89 2.3021 0.03 SELECT dw\_borrow\_repayment dw\_borrow  
\# 164 0x74FB2ADE2F877D90 192.1189 0.0% 31 6.1974 0.45 SELECT dw\_borrow\_collection dw\_borrow\_tender dw\_borrow dw\_user dw\_borrow\_tender  
\# 166 0xE371B55C1C684DF8 183.4475 0.0% 72 2.5479 0.16 SELECT dw\_account\_log  
\# 167 0xBABAFD75E441442C 180.5094 0.0% 13 13.8853 9.21 SELECT dw\_borrow\_collection dw\_borrow\_tender  
\# 168 0x562F1E04AB78091A 175.1864 0.0% 27 6.4884 0.70 SELECT dw\_user dw\_ads\_follow dw\_account dw\_account\_recharge dw\_user dw\_borrow\_tender  
\# 169 0x67AE63E951074962 175.0707 0.0% 68 2.5746 0.19 SELECT UNION dw\_borrow borrow\_user dw\_user borrow\_user  
\# 170 0x95C7C10BBE0F71D9 174.9168 0.0% 46 3.8025 0.07 SELECT dw\_account\_log dw\_user  
\# 171 0x6CC30C23B2919D2B 173.6089 0.0% 75 2.3148 0.04 SELECT dw\_account\_recharge dw\_user  
\# 172 0x61199112D48D8F69 172.4482 0.0% 26 6.6326 0.85 SELECT dw\_bbs\_topics dw\_bbs\_forums  
\# 174 0x87E4C701E96ED84D 168.7938 0.0% 13 12.9841 6.49 UPDATE dw\_borrow\_auto  
\# 175 0xD03BDC25FCC837B0 161.5570 0.0% 48 3.3658 3.84 SELECT dw\_borrow\_collection dw\_borrow\_tender dw\_borrow  
\# 176 0x47CC030AE0B8053F 158.4509 0.0% 25 6.3380 3.74 SELECT dw\_account\_cash dw\_user dw\_account dw\_borrow\_repayment dw\_borrow dw\_account\_cash  
\# 177 0x6B65BD5B1F94EE99 157.9761 0.0% 16 9.8735 3.58 SELECT dw\_borrow\_collection dw\_borrow\_tender  
\# 178 0xB899871670B736AC 157.8303 0.0% 67 2.3557 0.03 SELECT dw\_account\_log  
\# 180 0x2BE7CF5335742C54 153.2276 0.0% 19 8.0646 0.61 UPDATE dw\_user\_amount  
\# 182 0x3DDBE1567DDEAC43 148.8949 0.0% 16 9.3059 0.70 SELECT dw\_user dw\_ads\_follow dw\_account dw\_account\_recharge dw\_user dw\_borrow\_tender  
\# 183 0x51EAAC967FA8CEB4 143.9176 0.0% 13 11.0706 1.67 SELECT dw\_message dw\_user  
\# 184 0x3ED7CD2970FE3BBA 141.9287 0.0% 24 5.9137 0.31 SELECT dw\_account\_log dw\_linkage  
\# 185 0xD89A5D6CDBE02F51 140.9453 0.0% 26 5.4210 2.53 SELECT dw\_account\_cash dw\_user dw\_account dw\_borrow\_repayment dw\_borrow dw\_account\_cash  
\# 186 0x2B5DA8084FE5F776 139.7017 0.0% 28 4.9893 1.17 SELECT borrow\_user dw\_user\_amount  
\# 187 0xF7E82E82AF4DAF24 137.3459 0.0% 17 8.0792 0.93 SELECT dw\_account\_log  
\# 188 0x5EAA5D04F6BD407F 127.3032 0.0% 17 7.4884 1.10 UPDATE dw\_bbs\_topics  
\# 189 0x35B0FFF73EB65ACF 125.8719 0.0% 54 2.3310 0.10 SELECT dw\_account\_log  
\# 190 0xDF4E67957A5EB2EF 125.1811 0.0% 24 5.2159 0.76 SELECT dw\_user  
\# 192 0x76ADAC19DE13661A 109.5036 0.0% 11 9.9549 0.12 SELECT dw\_account\_log dw\_user  
\# 193 0xB0580569308FF08F 106.7025 0.0% 26 4.1039 0.05 SELECT dw\_account\_log dw\_user  
\# 194 0xC2BA8A7F40BB2952 105.4124 0.0% 31 3.4004 0.67 SELECT dw\_account\_cash dw\_user  
\# 198 0x224ED2DF28E7FD8E 93.6616 0.0% 15 6.2441 0.04 SELECT dw\_borrow\_repayment dw\_borrow  
\# 199 0x46AACD7E5F128168 92.8745 0.0% 40 2.3219 0.03 SELECT dw\_borrow  
\# 200 0x0EC91CA245B15139 90.8049 0.0% 10 9.0805 8.32 SELECT dw\_account\_recharge dw\_user dw\_borrow  
\# 203 0x3A0521BFB8506B79 88.4296 0.0% 19 4.6542 0.52 SELECT dw\_bbs\_posts dw\_user dw\_invest\_rank  
\# 205 0x92D826773BE3DF67 86.3710 0.0% 15 5.7581 0.14 SELECT dw\_borrow\_collection dw\_borrow\_tender dw\_borrow dw\_user dw\_borrow\_tender  
\# 207 0x1185FD4B0F5D55F0 83.4627 0.0% 14 5.9616 0.54 SELECT dw\_account\_log  
\# 214 0x21C75D148ABEAC5B 74.8971 0.0% 10 7.4897 0.89 SELECT dw\_borrow  
\# 215 0xB381B06D08A2250C 74.5037 0.0% 18 4.1391 0.54 SELECT dw\_user dw\_bbs\_posts dw\_invest\_rank  
\# 219 0xC5EDE07E27590AE9 66.6947 0.0% 28 2.3820 0.04 SELECT dw\_account\_log dw\_user  
\# 220 0x48F35858BBAF7C9A 66.1080 0.0% 15 4.4072 0.42 SELECT dw\_bbs\_topics  
\# 221 0x3BF7ED83502D5AE6 65.7778 0.0% 19 3.4620 0.23 SELECT dw\_bbs\_posts dw\_user dw\_invest\_rank  
\# 222 0xC9AABC1ACEB7A115 64.9226 0.0% 27 2.4045 0.18 SELECT dw\_account\_log  
\# 223 0x54E31CD99E3515C2 64.8183 0.0% 28 2.3149 0.03 SELECT UNION dw\_borrow borrow\_user dw\_user borrow\_user  
\# 224 0x43CB242125ED0DB2 64.7368 0.0% 22 2.9426 0.60 SELECT UNION dw\_borrow borrow\_user dw\_user borrow\_user  
\# 226 0x3385E5163AD8F187 63.1666 0.0% 18 3.5093 1.26 SELECT UNION dw\_borrow borrow\_user dw\_user borrow\_user  
\# 237 0x1FF0B9E9A9D30C7A 49.9187 0.0% 23 2.1704 0.01 SELECT dw\_borrow\_collection dw\_borrow\_tender dw\_borrow  
\# 244 0x86E219E51E83B141 47.7172 0.0% 15 3.1811 0.65 SELECT UNION dw\_borrow borrow\_user dw\_user borrow\_user  
\# 247 0xAA2F5F002E2C3F46 44.6367 0.0% 18 2.4798 0.07 INSERT dw\_borrow  
\# 252 0x74FC49AFFC59D2A2 40.7941 0.0% 18 2.2663 0.01 SELECT dw\_borrow\_repayment dw\_borrow  
\# 259 0xDE6C0796782DDE08 37.2471 0.0% 16 2.3279 0.03 SELECT dw\_borrow\_repayment dw\_borrow  
\# 262 0x41F5B55BA89BD5B0 36.0707 0.0% 16 2.2544 0.04 SELECT dw\_account\_recharge dw\_user  
\# 263 0xE131AEB0262B29CB 35.7737 0.0% 13 2.7518 0.70 SELECT dw\_borrow\_repayment dw\_borrow  
\# 269 0x613A0819005D53A8 33.9971 0.0% 12 2.8331 0.21 SELECT dw\_message  
\# 271 0xA8408773F7018431 32.2226 0.0% 12 2.6852 0.15 SELECT UNION dw\_borrow borrow\_user dw\_user borrow\_user  
\# 275 0x0C21EC876C0C6FB7 31.6462 0.0% 10 3.1646 0.29 SELECT dw\_borrow\_collection dw\_borrow\_tender dw\_borrow  
\# 294 0xC5C9BF90ADF8E20E 26.2727 0.0% 11 2.3884 0.04 SELECT borrow\_user dw\_user\_amount  
\# MISC 0xMISC 52679.9592 2.4% 903 58.3388 0.0 <389 ITEMS>  
  
  
第三部分：每一种查询的详细统计结果，  
由下面查询的详细统计结果，最上面的表格列出了执行次数、最大、最小、平均、95%等各项目的统计。  
Databases: 库名  
Users: 各个用户执行的次数（占比）  
Query\_time distribution : 查询时间分布, 长短体现区间占比，本例中1s-10s之间查询数量是10s以上的两倍。  
Tables: 查询中涉及到的表  
Explain: 示例  
  
  
\# Query 1: 0.00 QPS, 0.03x concurrency, ID 0xA6FE3D6982868655 at byte 3667736  
\# This item is included in the report because it matches --limit.  
\# Scores: V/M = 996.42  
\# Time range: 2013-10-28 21:15:52 to 2014-03-25 17:00:36  
\# Attribute pct total min max avg 95% stddev median  
\# ============ === ======= ======= ======= ======= ======= ======= =======  
\# Count 0 622  
\# Exec time 16 351140s 2s 2518s 565s 2437s 750s 113s  
\# Lock time 0 105s 0 25s 169ms 0 1s 0  
\# Rows sent 0 0 0 0 0 0 0 0  
\# Rows examine 0 0 0 0 0 0 0 0  
\# Query size 0 54.67k 90 90 90 90 0 90  
\# String:  
\# Databases test2  
\# Hosts  
\# Users rx1919 (369/59%), k1818 (246/39%)... 1 more  
\# Query\_time distribution  
\# 1us  
\# 10us  
\# 100us  
\# 1ms  
\# 10ms  
\# 100ms  
\# 1s \#\#\#  
\# 10s+ \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# Tables  
\# SHOW TABLE STATUS FROM \`test2\` LIKE 'dw\_user\_cache'G  
\# SHOW CREATE TABLE \`test2\`.\`dw\_user\_cache\`G  
CREATE TABLE IF NOT EXISTS \`dw\_user\_cache\` (  
\`user\_id\` int(11) NOT NULL DEFAULT '0')G  
  
  
\# Query 172: 0.00 QPS, 0.00x concurrency, ID 0x61199112D48D8F69 at byte 56964086  
\# This item is included in the report because it matches --outliers.  
\# Scores: V/M = 0.85  
\# Time range: 2013-11-20 17:00:38 to 2014-03-23 09:00:45  
\# Attribute pct total min max avg 95% stddev median  
\# ============ === ======= ======= ======= ======= ======= ======= =======  
\# Count 0 26  
\# Exec time 0 172s 5s 17s 7s 8s 2s 6s  
\# Lock time 0 3ms 63us 180us 100us 119us 22us 93us  
\# Rows sent 0 208 8 8 8 8 0 8  
\# Rows examine 0 333.82k 6.95k 20.62k 12.84k 18.47k 4.33k 10.80k  
\# Query size 0 5.59k 220 220 220 220 0 220  
\# String:  
\# Databases test2  
\# Hosts  
\# Users rx1919 (24/92%), tn1819 (2/7%)  
\# Query\_time distribution  
\# 1us  
\# 10us  
\# 100us  
\# 1ms  
\# 10ms  
\# 100ms  
\# 1s \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# 10s+ \#\#\#\#\#  
\# Tables  
\# SHOW TABLE STATUS FROM \`test2\` LIKE 'dw\_bbs\_topics'G  
\# SHOW CREATE TABLE \`test2\`.\`dw\_bbs\_topics\`G  
\# SHOW TABLE STATUS FROM \`test2\` LIKE 'dw\_bbs\_forums'G  
\# SHOW CREATE TABLE \`test2\`.\`dw\_bbs\_forums\`G  
\# EXPLAIN /\*!50100 PARTITIONS\*/  
select p1.\*,p2.name as forum\_name from \`dw\_bbs\_topics\` as p1  
left join dw\_bbs\_forums as p2 on p2.id = p1.fid  
where isrecycle<>1 and isrecycle<>1 and p1.status = 1 and p1.isgood = 1 order by rand() limit 8G  
\# Query 68: 0.00 QPS, 0.00x concurrency, ID 0xE104C8459E6B7739 at byte 20996  
\# This item is included in the report because it matches --outliers.  
\# Scores: V/M = 18.13  
\# Time range: 2013-10-10 17:56:37 to 2013-11-09 23:11:24  
\# Attribute pct total min max avg 95% stddev median  
\# ============ === ======= ======= ======= ======= ======= ======= =======  
\# Count 0 18  
\# Exec time 0 1279s 33s 175s 71s 151s 36s 66s  
\# Lock time 0 603us 22us 66us 33us 42us 9us 33us  
\# Rows sent 0 20.63M 674.13k 1.22M 1.15M 1.20M 125.83k 1.14M  
\# Rows examine 0 20.63M 674.13k 1.22M 1.15M 1.20M 125.83k 1.14M  
\# Query size 0 702 39 39 39 39 0 39  
\# String:  
\# Databases test2  
\# Hosts (13/72%), localhost (5/27%)  
\# Users k1818 (14/77%), root (4/22%)  
\# Query\_time distribution  
\# 1us  
\# 10us  
\# 100us  
\# 1ms  
\# 10ms  
\# 100ms  
\# 1s  
\# 10s+ \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# Tables  
\# SHOW TABLE STATUS FROM \`test2\` LIKE 'dw\_account\_log'G  
\# SHOW CREATE TABLE \`test2\`.\`dw\_account\_log\`G  
\# EXPLAIN /\*!50100 PARTITIONS\*/  
SELECT \* FROM \`test2\`.\`dw\_account\_log\`G  
  
4.用法示例  
(1)直接分析慢查询文件:  
pt-query-digest slow.log > slow\_report.log  
  
(2)分析最近12小时内的查询：  
pt-query-digest --since=12h slow.log > slow\_report2.log  
  
(3)分析指定时间范围内的查询：  
pt-query-digest slow.log --since '2014-04-17 09:30:00' --until '2014-04-17 10:00:00'> > slow\_report3.log  
  
(4)分析指含有select语句的慢查询  
pt-query-digest--filter '$event->\{fingerprint\} =~ m/^select/i' slow.log> slow\_report4.log  
  
(5) 针对某个用户的慢查询  
pt-query-digest--filter '($event->\{user\} || "") =~ m/^root/i' slow.log> slow\_report5.log  
  
(6) 查询所有所有的全表扫描或full join的慢查询  
pt-query-digest--filter '(($event->\{Full\_scan\} || "") eq "yes") ||(($event->\{Full\_join\} || "") eq "yes")' slow.log> slow\_report6.log  
  
(7)把查询保存到query\_review表  
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query\_review--create-review-table slow.log  
  
(8)把查询保存到query\_history表  
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query\_ history--create-review-table slow.log\_20140401  
pt-query-digest --user=root –password=abc123--review h=localhost,D=test,t=query\_history--create-review-table slow.log\_20140402  
  
(9)通过tcpdump抓取mysql的tcp协议数据，然后再分析  
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt  
pt-query-digest --type tcpdump mysql.tcp.txt> slow\_report9.log  
  
(10)分析binlog  
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql  
pt-query-digest --type=binlog mysql-bin000093.sql > slow\_report10.log  
  
(11)分析general log  
pt-query-digest --type=genlog localhost.log > slow\_report11.log

转自：

MySQL性能剖析工具(pt-query-digest)\_数据库技术\_Linux公社-Linux系统门户网站  
http://www.linuxidc.com/Linux/2014-07/104237.htm

分析pt-query-digest输出结果 - 关系型数据库数据分析-炼数成金-Dataguru专业数据分析社区  
http://f.dataguru.cn/thread-338724-1-1.html

[http_www.linuxidc.com_Linux_2014-07_104034.htm]: http://www.linuxidc.com/Linux/2014-07/104034.htm
[http_www.linuxidc.com_Linux_2014-07_104236.htm]: http://www.linuxidc.com/Linux/2014-07/104236.htm
[http_www.linuxidc.com_Linux_2014-07_104238.htm]: http://www.linuxidc.com/Linux/2014-07/104238.htm
[140716101592781.png]: /images/20220329/9779e97206074ca991fd5924823b2958.png
[140716101592782.png]: /images/20220329/5ee75004cf5942b788424d2deb71120e.png
[140716101592783.png]: /images/20220329/b3955fc2b48b4005baa3045f4e021511.png
[SQL]: http://www.dataguru.cn/article-8711-1.html?union_site=innerlink