Percona Toolkit : pt-query-digest option

Recently I've been trying the option 'pt-query-digest ' of Percona toolkit for mysql and to be honest, I really think it a handy option.

I used to verify the connection status of client application using only the show processlist command but why on earth can't I obtain useful information for example :

• Response time as total and as a percentage of the total overall in milliseconds,
• The number of times the query is executed,
• The average response time, etc.

Following you'll find the command :
 
tcpdump -s 65535 -x -nn -q -tttt -i any -c 99999 port 3306 | pt-query-digest --type tcpdump --run-time 60s > appo_pt_digest.txt

Essentially this command is made from the union of the 'tcpdump' plus 'pt-query-digest '. The first catches the query SQL from the network, while the second does the job of executing it and collection the statistics too.

Here are the description options:

• Option -c : tcpdump will, if not run with the -c flag, continue capturing packets until it is interrupted by a SIGINT signal,
• Option ttttt: print a delta (micro-second resolution) between current and first line on each dump line,
• Option -x :when parsing and printing, in addition to printing the headers of each packet, print the data of each packet (minus its link level header) in hex,
• Option -i any :  listen on interface. If unspecified, tcpdump searches the system interface list for the lowest numbered,configured up interface (excluding loopback), which may turn out to be, for example, ``eth0'',
• Option -s 65535 : snarf snaplen bytes of data from each packet rather than the default of 65535 bytes,
•  Option -n : dont convert addresses (i.e., host addresses, port numbers, etc.) to names.

Main Section

# 18.4s user time, 970ms system time, 269.30M rss, 447.86M vsz
# Current date: Mon Feb 10 15:09:01 2014
# Hostname: Mysql01
# Files: STDIN
# Overall: 23 total, 14 unique, 1.70 QPS, 0.00x concurrency ______________
# Time range: 2014-02-10 15:07:33.786104 to 15:07:47.299483
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           36ms    50us    19ms     2ms     4ms     4ms   125us
# Rows affecte           0       0       0       0       0       0       0
# Query size           588      12      55   25.57   49.17   12.60   21.45
# Warning coun           0       0       0       0       0       0       0
# Boolean:
# No index use   8% yes,  91% no

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ==============
#    1 0x5D51E5F01B88B79E  0.0194 53.9%     1 0.0194  0.00 ADMIN CONNECT
#    2 0xC0BB2384D10871D1  0.0085 23.7%     1 0.0085  0.00 SELECT payment
#    3 0xEBD964314E5CCD07  0.0042 11.7%     1 0.0042  0.00 SELECT payment
#    4 0x4699F54E6381E911  0.0015  4.1%     4 0.0004  0.00
#    5 0x9439C8E2DE90E5FF  0.0008  2.2%     1 0.0008  0.00 SHOW VARIABLES
# MISC 0xMISC              0.0016  4.4%    15 0.0001   0.0 <9 ITEMS>

Here is the select in details :

 # Query 2: 0 QPS, 0x concurrency, ID 0xC0BB2384D10871D1 at byte 39701 ____
# Scores: V/M = 0.00
# Time range: all events occurred at 2014-02-10 15:07:47.299483
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4       1
# Exec time     23     9ms     9ms     9ms     9ms     9ms       0     9ms
# Rows affecte   0       0       0       0       0       0       0       0
# Query size     8      50      50      50      50      50       0      50
# Warning coun   0       0       0       0       0       0       0       0
# String:
# Databases
# Hosts      

# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `1` LIKE 'payment'\G
#    SHOW CREATE TABLE `1`.`payment`\G
# EXPLAIN /*!50100 PARTITIONS*/
select *from payment a, payment b limit 0,10000000\G

Everything is well reported and easy to understand.

Why don't you try ?

See you in my next Blog !

Byeeeee