pt-query-digest

pt-query-digest는 percona 에서 만든 유용한 Toolkit 으로
slow query 가 많을 경우, 쿼리를 parsing하고 결과를 summary 하여 보여줍니다.
쿼리의 유형, 빈도, 비중 등을 확인할 수 있습니다.

 

SlowQuery 보기

슬로우쿼리를 요약해서 보여줍니다.

$ pt-query-digest slow.log

 

Binlog 보기

type 을 binlog 라고 따로 지정해줘야 합니다. (슬로우쿼리는 따로 지정하지 않아도 됩니다.)
binlog 에 남은 쿼리를 요약해서 보여줍니다.

$ mysqlbinlog bin-log.000002 > bin002.tmp
$ ./pt-query-digest --type binlog bin002.tmp

# 1.4s user time, 40ms system time, 22.74M rss, 173.82M vsz
# Current date: Tue Feb 21 13:16:08 2017
# Hostname: mytestdb
# Files: bin002.tmp
# Overall: 7.39k total, 26 unique, 7.39k QPS, 0x concurrency _____________
# Time range: 2016-11-17 08:43:34 to 08:43:35
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time              0       0       0       0       0       0       0
# Query size       788.45k       5  14.59k  109.27  511.45  490.66    5.75
# @@session.au           1       1       1       1       1       0       1
# @@session.au           1       1       1       1       1       0       1
# @@session.au           1       1       1       1       1       0       1
# @@session.ch          33      33      33      33      33       0      33
# @@session.co          33      33      33      33      33       0      33
# @@session.co          33      33      33      33      33       0      33
# @@session.fo           1       1       1       1       1       0       1
# @@session.lc           0       0       0       0       0       0       0
# @@session.ps           1       1       1       1       1       0       1
# @@session.sq           0       0       0       0       0       0       0
# @@session.sq       1.00G   1.00G   1.00G   1.00G   1.00G       0   1.00G
# @@session.un           1       1       1       1       1       0       1
# error code             0       0       0       0       0       0       0

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0x9669C3CA8D682CF3  0.0000  0.0%   569 0.0000  0.00 INSERT SELECT help_topic
#    2 0x0CBEE2D590974E43  0.0000  0.0%     3 0.0000  0.00 INSERT SELECT help_topic
#    3 0x3111CFAC56B7F868  0.0000  0.0%     1 0.0000  0.00 INSERT UPDATE INSERT UPDATE INSERT UPDATE help_topic
#    4 0xBA1D332B93598A57  0.0000  0.0%     1 0.0000  0.00 INSERT help_topic
#    5 0x32AB55431077349C  0.0000  0.0%     1 0.0000  0.00 INSERT SELECT UPDATE INSERT DELETE SELECT UPDATE REPLACE INSERT UPDATE INSERT SELECT DELETE SELECT help_topic
#    6 0x3D8FE5F7A4F35B39  0.0000  0.0%     1 0.0000  0.00 TRUNCATE TABLE help_topic
#    7 0xBB7C6C3AEDD369AB  0.0000  0.0%     1 0.0000  0.00 INSERT UPDATE SELECT UPDATE INSERT SELECT INSERT SELECT INSERT SELECT INSERT help_topic
#    8 0x7DF5027649B2002A  0.0000  0.0%     1 0.0000  0.00 INSERT SELECT help_topic
#    9 0xBCBC01709CA2A938  0.0000  0.0%     1 0.0000  0.00 INSERT SELECT help_topic
#   10 0x753EC8E016B3A117  0.0000  0.0%     1 0.0000  0.00 INSERT REPLACE SELECT INSERT REPLACE INSERT REPLACE INSERT DELETE REPLACE INSERT REPLACE help_topic
#   11 0x757E6F2238CC152D  0.0000  0.0%     1 0.0000  0.00 INSERT SELECT help_topic
#   12 0xDAB48334253B1919  0.0000  0.0%     1 0.0000  0.00 INSERT UNION help_topic
#   13 0x5352E4133E954777  0.0000  0.0%     1 0.0000  0.00 INSERT SELECT help_topic
#   14 0x1EFC2DE6CC335EDE  0.0000  0.0%     1 0.0000  0.00 INSERT UNION SELECT help_topic
#   15 0xFFDCE34F4BC1CA0F  0.0000  0.0%    40 0.0000  0.00 INSERT help_category
#   16 0x813031B8BBC3B329  0.0000  0.0%  2461 0.0000  0.00 COMMIT
#   17 0x06EBCB4968733E9A  0.0000  0.0%  1223 0.0000  0.00 INSERT help_relation
#   18 0x85FFF5AA78E5FF6A  0.0000  0.0%  2461 0.0000  0.00 BEGIN
#   19 0xC2BA4B26B17449FF  0.0000  0.0%     1 0.0000  0.00 INSERT SELECT help_topic
#   20 0x2CF24E4EF7DA9A35  0.0000  0.0%     1 0.0000  0.00 TRUNCATE TABLE help_relation
# MISC 0xMISC              0.0000  0.0%   616 0.0000   0.0 <4 ITEMS>

# Query 1: 0 QPS, 0x concurrency, ID 0x9669C3CA8D682CF3 at byte 968549 ___
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2016-11-17 08:43:34
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          7     569
# Exec time      0       0       0       0       0       0       0       0
# Query size    76 606.70k     208  14.59k   1.07k   3.19k   1.40k  621.67
# error code     0       0       0       0       0       0       0       0
# String:
# Databases    mysql
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `mysql` LIKE 'help_topic'\G
#    SHOW CREATE TABLE `mysql`.`help_topic`\G
INSERT INTO help_topic (help_topic_id,help_category_id,name,description,example,url) VALUES (584,17,'VERSION','Synt....

 

tcpdump 결과 보기

tcpdump 결과를 summary 해서 보여줍니다.

$ tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt

$ pt-query-digest --type tcpdump mysql.tcp.txt