Tcpdump pt-query-digest common operations,

Tcpdump pt-query-digest common operations,

Tcpdump pt-query-digest Common Operations

Tcpdump pt-query-digest

Objective:

Use the tcpdump command to capture data packets according to certain rules, and then dump them to intermediate files. Use pt-query-digest to analyze intermediate files according to certain rules.

Tcpdump

Tcpdump is a powerful command. Here we only discuss some typical tcp output methods, because MySQL uses tcp (reliable) to transmit data.

[root@archpredb214  ~]#tcpdump -s 65535 -x -nn -q -tttt -i any -c 100000 port 3306 >dmp.txt

-S 65535 or-s 0 both indicate capturing a complete data packet. In the IP data packet, = total length = header + Data Section =. Therefore, as shown in, the total length is 16 ~ 31, 16bit, 2 ^ 16-1 = 65535, that is, capture the entire data packet.

-X indicates the content of each packet header and packet. To analyze the data, this parameter is required. The following is the difference between adding the-x parameter and not adding the-x parameter.

[Root @ archpredb214 ~] # Tcpdump-s 65535-x-nn-q-tttt-I any-c 100000 port 3306 # option-xtcpdump: verbose output suppressed, use-v or-vv for full protocol decodelistening on any, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes2018-01-24 09:49:54. 443184 IP Route> 10.27.139.176.3306: tcp 0 0x0000: 4500 0034 430d 4000 4006 cc1e 0a1b 8bb2 0x0010: 0a1b 8bb0 e254 0cea e057 2c39 e536 d965 0 0x0020 : 8011 007b 7867 0000 0101 080a 1676 5325 0x0030: 1606 9833 # hexadecimal packet content... [root @ archpredb214 ~] # Tcpdump-s 65535-nn-q-tttt-I any-c 100000 port 3306 # No option-xtcpdump: verbose output suppressed, use-v or-vv for full protocol decodelistening on any, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes2018-01-24 09:50:16. 107281 IP 127.0.0.1.43489> 127.0.0.1.3306: tcp 02018-01-24 09:50:16. 107293 IP 127.0.0.1.3306> 127.0.0.1.43489: tcp 0... # note that there is no packet content here

-Nn does not resolve the ip address to the host name or port number to the service name, but directly displays the ip address or port number.

[Root @ archpredb214 ~] # Tcpdump-s 65535-x-nn-q-tttt-I any-c 100000 port 3306 # option-nntcpdump: verbose output suppressed, use-v or-vv for full protocol decodelistening on any, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes2018-01-24 09:49:54. 443184 IP Route> 10.27.139.176.3306: tcp 0 # note the host name and port part 0x0000: 4500 0034 430d 4000 4006 cc1e 0a1b 8bb2 0x0010: 0a1b 8bb0 e254 0cea e057 2c39 e53 6 d965 0x0020: 8011 007b 7867 0000 0101 080a 1676 0x5325: 0030 1606 ...... [root @ archpredb214 ~] # Tcpdump-s 65535-x-q-tttt-I any-c 100000 port 3306 # No option-nntcpdump: verbose output suppressed, use-v or-vv for full protocol decodelistening on any, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes2018-01-24 10:01:31. 397868 IP localhost.43489> localhost. mysql: tcp 19 # note the host name and port part 0x0000: 4508 0047 e96b 4000 4006 533b 7f00 0001 0x0010: 7f00 0001 a9e1 0cea ae56 4f04 fecc 12a2 0x0020: 8018 0101 fe3b 0000 0101 080a 1680 f5a0 0x0030: 1676 a7c7 0f00 0000 0373 686f 7720 6461 0x0040: 7461 6261 7365 73

-Q quiet output, rarely print information about the protocol, so this option should also be used. The following is a display without the-q Parameter. A long part is meaningless for statistical analysis.

[Root @ archpredb214 ~] # Tcpdump-s 65535-x-nn-tttt-I any-c 100000 port 3306 tcpdump: verbose output suppressed, use-v or-vv for full protocol decodelistening on any, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes2018-01-24 10:09:41. 913854 IP 127.0.0.1.43489> 127.0.0.1.3306: Flags [P.], seq 2924891927: 2924891946, ack 4274787189, win 265, options [nop, nop, TS val 378040756 ecr 377550240], length 19 #0x0000 for the extra output without-q: 4508 0047 e96d 4000 4006 5339 7f00 0001 0x0010: 7f00 0001 a9e1 0cea ae56 4f17 fecc 1375 0x0020: 8018 0109 fe3b 0000 0101 080a 1688 71b4 0x0030: 1680 f5a0 0f00 0000 0373 686f 7720 6461 0x0040: 7461 6261 7365 73

-Tttt prints the date before each line is printed, which is necessary as time statistics.

-I any: capture the packets of all the Network Ports (excluding lo). In fact, you can capture eth0 here. The following is the-I option output of man tcpdump.

       -i     Listen on interface.  If unspecified, tcpdump searches the system interface list for the lowest numbered, configured up interface (excluding              loopback).  Ties are broken by choosing the earliest match.              On  Linux systems with 2.2 or later kernels, an interface argument of ‘‘any’’ can be used to capture packets from all interfaces.  Note that              captures on the ‘‘any’’ device will not be done in promiscuous mode.              If the -D flag is supported, an interface number as printed by that flag can be used as the interface argument.

-C 100000: capture 10 million data packets port 3306 port number

Pt-query-digest

This is actually a script tool of percona. Of course, dependencies need to be resolved.

[root@archpredb214 ~]#yum -y install perl perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes...[root@archpredb214 ~]#wget percona.com/get/percona-toolkit.rpm...rpm -ivh percona-toolkit-2.1.7-1.noarch.rpm...[root@archpredb214 ~]# find  / -name 'pt-query-digest'/home/mysql/mysql_tools/pt-query-digest[root@archpredb214 ~]# 

Usage: pt-query-digest [OPTIONS] [FILES] [DSN]This command supports stdin

Typical analysis usage

Analyze slow logs
[Root @ archpredb214 ~] #/Home/mysql/mysql_tools/pt-query-digest slow. log

Analyze the data packet dump file mysql.tcp.txt captured by the tcpdump Tool

[root@archpredb214 ~]#tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt[root@archpredb214 ~]#/home/mysql/mysql_tools/pt-query-digest --type tcpdump mysql.tcp.txt

-Type [binlog | genlog | slowlog | tcpdump]

Specifies the file type to be analyzed by pt-query-digest.

The first part is the overall statistical result (Here we use the slow query log as an example to analyze)

  [root@archpredb214 data]# /home/mysql/mysql_tools/pt-query-digest --type=slowlog             ./archpredb214-slow.log  # 170ms user time, 10ms system time, 23.39M rss, 180.74M vsz # Current date: Wed Jan 24 14:23:02 2018 # Hostname: archpredb214 # Files: ./archpredb214-slow.log # Overall: 2 total, 2 unique, 0 QPS, 0x concurrency ______________________ # Time range: all events occurred at 2017-10-13 15:36:25 # Attribute          total     min     max     avg     95%  stddev  median # ============     ======= ======= ======= ======= ======= ======= ======= # Exec time             2s   517ms      1s   759ms      1s   342ms   759ms # Lock time          176us    63us   113us    88us   113us    35us    88us # Rows sent              0       0       0       0       0       0       0 # Rows examine     208.00k       0 208.00k 104.00k 208.00k 147.08k 104.00k # Rows affecte     104.00k       0 104.00k  52.00k 104.00k  73.54k  52.00k # Rows read              0       0       0       0       0       0       0 # Bytes sent            72      11      61      36      61   35.36      36 # Tmp tables             0       0       0       0       0       0       0 # Tmp disk tbl           0       0       0       0       0       0       0 # Tmp tbl size           0       0       0       0       0       0       0 # Query size           271     121     150  135.50     150   20.51  135.50...

The output above indicates that the subsequent data is omitted.

Overall (row 6th): 2 total indicates the total number of queries. Here there are two rows in total; 2 unique indicates the number of unique queries. Here there are two in total; assume that the SQL statement is the same, the value should be 1. Time range: indicates the Time range. In the preceding table, all slow queries occur at 15:36:25.

Attribute Attribute	Total Total	Min Minimum	Max Max	Avg Average	95% 95% confidence interval	Stddev Standard Deviation	Median Median
Exec time Execution time
Lock time Lock time
Rows sent Query the number of returned rows
Rows examine Query the number of checked rows

....

95%: normal distribution, 95%, is to remove the extreme situation

The second part is to query the group statistical result (or the command in the first part of the preceding Section as an example)

 # Profile # Rank Query ID           Response time Calls R/Call V/M   Item # ==== ================== ============= ===== ====== ===== =============== #    1 0x106F26B3D217B2C9  1.0003 65.9%     1 1.0003  0.00  #    2 0x01C3BF9ABB566E2C  0.5170 34.1%     1 0.5170  0.00 INSERT SELECT    test.group_message group_message

Column	Meaning
Rank	The query's rank within the entire set of queries analyzed
Query ID	The query's fingerprint
Response time	The total response time, and percentage of overall total
CILS	The number of times this query was executed
R/Call	The mean response time per execution
V/M	The Variance-to-mean ratio of response time
Item	The distilled query

The third part is the detailed information of each statistic query.

 # Query 1: 0 QPS, 0x concurrency, ID 0x106F26B3D217B2C9 at byte 0 ________ # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: all events occurred at 2017-10-13 15:36:25 # Attribute    pct   total     min     max     avg     95%  stddev  median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count         50       1 # Exec time     65      1s      1s      1s      1s      1s       0      1s # Lock time     35    63us    63us    63us    63us    63us       0    63us # Rows sent      0       0       0       0       0       0       0       0 # Rows examine   0       0       0       0       0       0       0       0 # Rows affecte   0       0       0       0       0       0       0       0 # Rows read      0       0       0       0       0       0       0       0 # Bytes sent    15      11      11      11      11      11       0      11 # Tmp tables     0       0       0       0       0       0       0       0 # Tmp disk tbl   0       0       0       0       0       0       0       0 # Tmp tbl size   0       0       0       0       0       0       0       0 # Query size    44     121     121     121     121     121       0     121 # String: # Hosts # Last errno   0 # Users        root # Query_time distribution #   1us #  10us # 100us #   1ms #  10ms # 100ms #    1s  ################################################################ #  10s+GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.27.139.%' IDENTIFIED BY PASSWORD '*5C2A4F610EF47B9C3C868FF5E88A69965D887ED4'\G

Common Operations

Analyze queries in the last 12 hours

[root@archpredb214 lianxi]# /home/mysql/mysql_tools/pt-query-digest  --since=12h /mysql/data/archpredb214-slow.log  >anal1.txt

Analyze queries within a specified time range

[root@archpredb214 lianxi]# /home/mysql/mysql_tools/pt-query-digest  'unixtimestamp1' –until  'unixtimestamp2' >anal2.txt

-The filter parameter is a powerful option, and you can even create some output by yourself. Here, we only list some common outputs.

Analyze slow queries that only contain select statements

[root@archpredb214 lianxi]# /home/mysql/mysql_tools/pt-query-digest  --filter '$event->{fingerprint} =~ m/^select/i' /mysql/data/archpredb214-slow.log  >anal3.txt

Slow query for a user

root@archpredb214 lianxi]# /home/mysql/mysql_tools/pt-query-digest  --filter  '($event->{user} || "") =~ m/^root/i' /mysql/data/archpredb214-slow.log  >anal4.txt

All full table scans or full join slow queries

[root@archpredb214 lianxi]# /home/mysql/mysql_tools/pt-query-digest  --filter  '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")'  /mysql/data/archpredb214-slow.log  >anal5.txt

For more detailed examples, see percona official documentation.

Usage of super detailed Tcpdump

Analysis of mysql load performance problems using tcpdump & pt-query-digest