mysql 慢日志分析工具pt-query-digest

野性酷女 2022-08-04 00:38 170阅读 0赞

之前[windows下mysql5.6.20使用mysqldumpslow.pl分析慢日志][windows_mysql5.6.20_mysqldumpslow.pl]，今天又看到一个工具pt-query-digest，原本打算在windows上安装使用，结果在windows上执行C:\\Program Files\\MySQL\\MySQL Server 5.6\\bin>pt-query-digest.pl  F:\\mysql\\im-slow.log > F:\\mysql\\slow\_report.log，等了半小时都出不来结果。想必是不怎么支持windows吧，那就一心一意用linux吧。

**   1. 先安装perl, 登录 https://www.perl.org/get.html ,我选择的是最新版。**

解压后来到perl的安装目录执行

\[root@VM-10 perl-5.22.0\]\# ./Configure -des -Dprefix=/usr/perl-5.22.0 -Dusethreads -Uversiononly

验证是否安装成功

\[root@VM-10 perl-5.22.0\]\# perl -version  
This is perl, v5.10.1 (\*) built for x86\_64-linux-thread-multi  
Copyright 1987-2009, Larry Wall  
Perl may be copied only under the terms of either the Artistic License or the  
GNU General Public License, which may be found in the Perl 5 source kit.  
Complete documentation for Perl, including FAQ lists, should be found on  
this system using "man perl" or "perldoc perl".  If you have access to the  
Internet, point your browser at http://www.perl.org/, the Perl Home Page.

**  2.下载pt-query-digest，https://www.percona.com/downloads/percona-toolkit/，下载percona-toolkit\_2.2.14.tar.gz 。**

tar zxvf 解压后，来到安装目录下/data/percona-toolkit-2.2.14执行  
   \[root@VM-10 perl-5.22.0\]\#   perl Makefile.PL  
   \[root@VM-10 perl-5.22.0\]\#   make  
   \[root@VM-10 perl-5.22.0\]\#   make install

如果是yum安装的话，yum install perl perl-TermReadKey  perl-DBI perl-DBD-MySQL  perl-Time-HiRes  perl-IO-Socket-SSL

**  3.pt-query-digest  /data/im-slow.log > /data/slow\_report.log ，相比windows半天出不来，这个结果几乎秒出。**

Overall: 总共有多少条查询，上例为总共266个查询。
    Time range: 查询执行的时间范围。
    unique: 唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询，该例为55。

\# 34.3s user time, 310ms system time, 44.95M rss, 196.52M vsz  
\# Current date: Thu Aug 13 10:51:55 2015  
\# Hostname: VM-10.10.X.X  
\# Files: /data/im-slow.log  
\# Overall: 64.10k total, 179 unique, 0.00 QPS, 0.10x concurrency \_\_\_\_\_\_\_\_\_  
\# Time range: 2015-02-08 06:52:36 to 2015-07-29 09:02:02  
\# Attribute          total     min     max     avg     95%  stddev  median  
\# ============     ======= ======= ======= ======= ======= ======= =======  
\# Exec time        1405895s      5s   4899s     22s     32s    111s     10s  
\# Lock time         62995s       0   2476s   983ms     1ms     48s   144us  
\# Rows sent          5.69G       0  44.43M  93.04k    5.75   1.34M    0.99  
\# Rows examine      13.96G       0 693.12M 228.36k  44.45k   3.49M    0.99  
\# Query size        37.30M       6 335.57k  610.11  918.49   6.98k  537.02  
  
  
\# Profile  
\# Rank Query ID           Response time     Calls R/Call    V/M   Item  
\# ==== ================== ================= ===== ========= ===== ========  
\#    1 0x0F2E6B958872280E 274452.3245 19.5% 26566   10.3310  2.39 SELECT gg\_token gg\_tenant  
\#    2 0x816F5D9DB1BD38C7 246396.5092 17.5% 15295   16.1096 31... INSERT gg\_login\_history  
\#    3 0xA3267D6CEFA1D5E3 203446.8729 14.5%   100 2034.4687 13... SELECT gg\_token gg\_login\_history  
\#    4 0x67A347A2812914DF 170491.0463 12.1%   972  175.4023 44... SELECT app\_chat\_message  
\#    5 0x8BACA5842B7A7490  95999.5908  6.8%  5737   16.7334 37... INSERT app\_offline\_message  
\#    6 0x2B874BB5CB81DC1F  68234.7809  4.9%   105  649.8551 87.61 SELECT gg\_token gg\_login\_history  
\#    7 0x848218FE4D9F7651  51218.6838  3.6%   210  243.8985 12.52 SELECT gg\_token  
\#    8 0xBF9265005D4F8D80  40299.7591  2.9%    78  516.6636 28... SELECT gg\_login\_history gg\_user gg\_user\_org gg\_org  
\#    9 0xDCDBC481602798A4  39449.2606  2.8%  3193   12.3549  4.68 SELECT gg\_token  
  
Exec time：语句执行时间  
Lock time：锁占有时间  
Rows sent：发送到客户端的行数  
Row examine：扫描的行数(SELECT语句)  
Row affecte：发送改变的行数(UPDATE, DELETE, INSERT语句)  
Bytes sent：发送多少bytes的查询结果集  
Query size：查询语句的字符数  
Databases: 库名  
Users: 各个用户执行的次数（占比）  
Query\_time distribution : 查询时间分布, 长短体现区间占比  
Tables: 查询中涉及到的表  
Explain: 示例

pct占数据库总时间的百分比  
  
\# Query 1: 0.03 QPS, 0.30x concurrency, ID 0x0F2E6B958872280E at byte 28156661  
\# This item is included in the report because it matches --limit.  
\# Scores: V/M = 2.39  
\# Time range: 2015-06-16 00:05:14 to 2015-06-26 11:33:23  
\# Attribute    pct   total     min     max     avg     95%  stddev  median  
\# ============ === ======= ======= ======= ======= ======= ======= =======  
\# Count         41   26566  
\# Exec time     19 274452s      5s     54s     10s     12s      5s     10s  
\# Lock time      0     24s    64us    40ms   885us     1ms   738us   839us  
\# Rows sent      0  80.00k       0      13    3.08    4.96    1.29    2.90  
\# Rows examine   8   1.15G  44.73k  45.59k  45.43k  44.45k       0  44.45k  
\# Query size    36  13.78M     542     544  543.73  537.02       0  537.02  
\# String:  
\# Databases    im  
\# Hosts        10.100.252.38 (13301/50%)... 1 more  
\# Users        im  
\# Query\_time distribution  
\#   1us  
\#  10us  
\# 100us  
\#   1ms  
\#  10ms  
\# 100ms  
\#    1s  \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#  10s+  \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# Tables  
\#    SHOW TABLE STATUS FROM \`im\` LIKE 'gg\_token'\\G  
\#    SHOW CREATE TABLE \`im\`.\`gg\_token\`\\G  
\#    SHOW TABLE STATUS FROM \`im\` LIKE 'gg\_tenant'\\G  
\#    SHOW CREATE TABLE \`im\`.\`gg\_tenant\`\\G  
\# EXPLAIN /\*!50100 PARTITIONS\*/  
select token0\_.token\_id as token\_id1\_119\_, token0\_.tenant\_id as tenant\_10\_119\_, token0\_.account as account2\_119\_, token0\_.client\_id as client\_i3\_119\_, token0\_.create\_date as create\_d4\_119\_, token0\_.host as host5\_119\_, token0\_.last\_auth\_date as last\_aut6\_119\_, token0\_.session\_id as session\_7\_119\_, token0\_.token as token8\_119\_, token0\_.user\_id as user\_id9\_119\_ from gg\_token token0\_ left outer join gg\_tenant tenant1\_ on token0\_.tenant\_id=tenant1\_.tenant\_id where token0\_.user\_id='00014806a5a95f25ab' and tenant1\_.tenant\_id='0001456d92804b00'\\G  
  
  
\# Query 2: 0.00 QPS, 0.02x concurrency, ID 0x816F5D9DB1BD38C7 at byte 17658778  
\# This item is included in the report because it matches --limit.  
\# Scores: V/M = 314.49  
\# Time range: 2015-03-21 13:31:25 to 2015-07-18 15:11:32  
\# Attribute    pct   total     min     max     avg     95%  stddev  median  
\# ============ === ======= ======= ======= ======= ======= ======= =======  
\# Count         23   15295  
\# Exec time     17 246397s      5s   2498s     16s     32s     71s     11s  
\# Lock time     51  32142s       0   2476s      2s   125us     71s    89us  
\# Rows sent      0       0       0       0       0       0       0       0  
\# Rows examine   0       0       0       0       0       0       0       0  
\# Query size     9   3.55M     221     258  243.36  246.02    7.37  234.30  
\# String:  
\# Databases    im  
\# Hosts        10.101.252.35 (7709/50%)... 3 more  
\# Users        im  
\# Query\_time distribution  
\#   1us  
\#  10us  
\# 100us  
\#   1ms  
\#  10ms  
\# 100ms  
\#    1s  \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#  10s+  \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\# Tables  
\#    SHOW TABLE STATUS FROM \`im\` LIKE 'gg\_login\_history'\\G  
\#    SHOW CREATE TABLE \`im\`.\`gg\_login\_history\`\\G  
INSERT INTO gg\_login\_history(login\_history\_id,username,resource,clientId,login\_time,logout\_time) VALUES('930e0b3e-d26b-4cc3-a02f-be0b1009744c','000148b62c6a6e290','Android','354273054207050','2015-04-13 23:04:42','2015-04-13 23:07:46')\\G

**最常用的使用方法：**

(1)直接分析慢查询文件:  
pt-query-digest  slow.log > slow\_report.log  
(2)分析最近1小时内的查询：  
pt-query-digest  --since=1h  slow.log > slow\_report.log  
(3)分析指定时间范围内的查询：  
pt-query-digest slow.log --since '2015-04-17 10:30:00' --until '2015-04-17 18:00:00'>slow\_report.log  
(4)分析指含有select语句的慢查询  
pt-query-digest--filter '$event->\{fingerprint\} =~ m/^select/i' slow.log> slow\_report.log  
(5) 查询所有所有的全表扫描或full join的慢查询  
pt-query-digest--filter '(($event->\{Full\_scan\} || "") eq "yes") ||(($event->\{Full\_join\} || "") eq "yes")' slow.log> slow\_report.log

**详细的使用说明：**

https://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html

[windows_mysql5.6.20_mysqldumpslow.pl]: http://blog.csdn.net/stevendbaguo/article/details/47128531