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