Debugging mysql communication with tcpdump and pt-query-digest

To test the network communication between a mysql client and the associated mysql database is usually a simple tcpdump on the database server.

root@debian-iptables1 ~ # tcpdump -i any port 3306
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on any, link-type LINUX_SLL (Linux cooked), capture size 262144 bytes
14:27:49.392769 IP 192.168.1.119.mysql > 192.168.1.112.40316: Flags [S.], seq 4182841961, ack 4246718087, win 28960, options [mss 1460,sackOK,TS val 17505918 ecr 17728855,nop,wscale 7], length 0
14:27:49.392803 IP 192.168.1.112.40316 > 192.168.1.119.mysql: Flags [.], ack 1, win 229, options [nop,nop,TS val 17728855 ecr 17505918], length 0
14:27:49.393235 IP 192.168.1.119.mysql > 192.168.1.112.40316: Flags [P.], seq 1:88, ack 1, win 227, options [nop,nop,TS val 17505918 ecr 17728855], length 87

In the search for further analysis possibilities I am on pt-query-digest pushed.

Download

the percona toolkit from https://www.percona.com/downloads/percona-toolkit/LATEST/

Use dpkg to install paket:

root@debian-iptables1 ~ # dpkg -i percona-toolkit_3.0.2-1.jessie_amd64.deb

Usage

Use tcpdump for capture traffic to file:

root@debian-iptables1 ~ # tcpdump -i any port 3306 -s 65535 -x -nn -q -tttt> tcpdump.out

Use pt-query-digest

root@debian-iptables1 ~ # pt-query-digest --report-format query_report --type tcpdump tcpdump.out

# Query 1: 54.14 QPS, 0.08x concurrency, ID 0x5D51E5F01B88B79E at byte 1550
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2017-03-27 14:21:53.009370 to 14:21:53.046309
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 40 2
# Exec time 81 3ms 1ms 2ms 2ms 2ms 429us 2ms
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 40 60 30 30 30 30 0 30
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Databases ulogdb
# Hosts 192.168.1.112
# Users ulog
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ################################################################
# 10ms
# 100ms
# 1s
# 10s+
administrator command: Connect\G

# Query 2: 0 QPS, 0x concurrency, ID 0x29E0EC7C34DD5943 at byte 2602 _____
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2017-03-27 14:21:53.010131
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 20 1
# Exec time 18 708us 708us 708us 708us 708us 0 708us
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 22 33 33 33 33 33 0 33
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Databases ulogdb
# Hosts 192.168.1.112
# Users ulog
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
administrator command: Field_list\G
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s