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

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

阅读量：5316 次

发布时间：2019-06-14

本文共 26431 字，大约阅读时间需要 88 分钟。

这个工具同样来自percona-toolkit

该工具集合的其他工具

MySQL Slave异常关机的处理 (pt-slave-restart)

验证MySQL主从一致性(pt-table-checksum&pt-table-sync)

它可以剖析各种日志,从而分析性能问题

包括

1.慢日志(默认)

2.tcpdump

3.general log

4.binlog

5.show processlist

实验通过tpcc模拟一定的压力,并开启binlog,general log,slow log和tcpdump收集日志。

关于tpcc参考:

1.slow log方式

将slow_query_log设置为on

long_query_time设置为0

重新连接服务器(一般意味着中间件服务器重启)

这样慢日志将记录所有的操作。

通过pt-query-digest分析慢日志文件,生成剖析报告

首先是概要信息,包括一共有54.17k个操作,46个语句,平均每秒1.55k查询,剖析报告覆盖的时间等。

概要还包括类似Top SQL的分析

可以从响应时间查看对服务器性能影响最大的SQL,其中R/Call指的是平均每次执行的响应时间 ResponseTime/Calls

然后就是每条SQL的详细剖析报告,

pct指的是该项占概要报告中的百分比

分析pt-query-digest输出结果。

1. 工具简介

pt-query-digest是用于分析my慢查询的一个工具，它可以分析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

转载于:https://www.cnblogs.com/paul8339/p/6605906.html

你可能感兴趣的文章

Windows Phone 7你不知道的8件事

查看>>

实用拜占庭容错算法PBFT

查看>>

java的二叉树树一层层输出,Java构造二叉树、树形结构先序遍历、中序遍历、后序遍历...

查看>>

php仿阿里巴巴,php实现的仿阿里巴巴实现同类产品翻页

poj-1410 Intersection

SQL Server中利用正则表达式替换字符串

查看>>

POJ 1015 Jury Compromise（双塔dp）

查看>>

论三星输入法的好坏

查看>>