1、将捕获到的SQL存入文本文件
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > /mysql.tcp.txt
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on any, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes
1000 packets captured
1016 packets received by filter
0 packets dropped by kernel
2、利用percona的pt-query-digest工具对抓取到的信息进行分析
[root@master /]# pt-query-digest mysql.tcp.txt
# 460ms user time, 10ms system time, 34.22M rss, 185.79M vsz
# Current date: Wed Jun 13 16:32:58 2018
# Hostname: master
# Files: mysql.tcp.txt
# Overall: 0 total, 1 unique, 0 QPS, 0x concurrency ______________________
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Query size 1.32M 1.32M 1.32M 1.32M 1.32M 0 1.32M
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# =========== =========== =========== =========== =========== ===== ======
[root@master /]#
[root@master /]#
[root@master /]# pt-query-digest --type tcpdump mysql.tcp.txt
# 480ms user time, 10ms system time, 22.31M rss, 173.76M vsz
# Current date: Wed Jun 13 16:33:22 2018
# Hostname: master
# Files: mysql.tcp.txt
# Overall: 293 total, 12 unique, 5.02k QPS, 4.09x concurrency ____________
# Time range: 2018-06-13 16:31:02.409116 to 16:31:02.467511
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 239ms 0 11ms 815us 5ms 2ms 125us
# Rows affecte 0 0 0 0 0 0 0
# Query size 34.71k 14 873 121.31 833.10 243.41 28.75
# Warning coun 0 0 0 0 0 0 0
# Boolean:
# No index use 16% yes, 83% no
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== ===============
# 1 0x0F45051F493F587F 0.0468 19.6% 8 0.0058 0.00 SELECT weixin_bind
# 2 0xC69B6ED2C47380A4 0.0462 19.3% 30 0.0015 0.00 SHOW VARIABLES
# 3 0x5D51E5F01B88B79E 0.0452 18.9% 30 0.0015 0.00 ADMIN CONNECT
# 4 0xA2750AF24EA2AEE6 0.0304 12.7% 30 0.0010 0.00 SHOW COLLATION
# 5 0x3607184B9D9C3A96 0.0146 6.1% 30 0.0005 0.00 SELECT
# 6 0xCC47B42511EA22DD 0.0143 6.0% 30 0.0005 0.01 SET
# 7 0xE4CF7146873CCC28 0.0135 5.6% 29 0.0005 0.01 SET
# 8 0x18EC0094FCA82DC0 0.0125 5.2% 29 0.0004 0.00 SELECT
# 9 0x3AEAAD0E15D725B5 0.0103 4.3% 29 0.0004 0.00 SET
# MISC 0xMISC 0.0051 2.1% 48 0.0001 0.0 <3 ITEMS>
# Query 1: 318.60 QPS, 1.86x concurrency, ID 0x0F45051F493F587F at byte 632688
# Scores: V/M = 0.00
# Time range: 2018-06-13 16:31:02.439892 to 16:31:02.465002
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 2 8
# Exec time 19 47ms 5ms 7ms 6ms 7ms 564us 5ms
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 1 384 48 48 48 48 0 48
# Warning coun 0 0 0 0 0 0 0 0
# Boolean:
# No index use 100% yes, 0% no
# String:
# Databases jxweixin
# Hosts 10.160.2.151
# Users jxapp
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ################################################################
# 10ms
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `jxweixin` LIKE 'weixin_bind'\G
# SHOW CREATE TABLE `jxweixin`.`weixin_bind`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from weixin_bind where u_id = '2388641'\G
# Query 2: 550.44 QPS, 0.85x concurrency, ID 0xC69B6ED2C47380A4 at byte 925549
# Scores: V/M = 0.00
# Time range: 2018-06-13 16:31:02.411113 to 16:31:02.465615
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 10 30
# Exec time 19 46ms 786us 6ms 2ms 2ms 1ms 1ms
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 73 25.58k 873 873 873 873 0 873
# Warning coun 0 0 0 0 0 0 0 0
# Boolean:
# No index use 100% yes, 0% no
# String:
# Databases jxweixin
# Hosts 10.160.2.151
# Users jxapp
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms ################################################################
# 10ms
# 100ms
# 1s
# 10s+
/* mysql-connector-java-5.1.26 ( Revision: ${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'\G
# Query 3: 540.74 QPS, 0.82x concurrency, ID 0x5D51E5F01B88B79E at byte 923525
# Scores: V/M = 0.00
# Time range: 2018-06-13 16:31:02.409116 to 16:31:02.464595
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 10 30
# Exec time 18 45ms 560us 8ms 2ms 3ms 1ms 1ms
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 2 900 30 30 30 30 0 30
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Databases jxweixin
# Hosts 10.160.2.151
# Users jxapp
# Query_time distribution
# 1us
# 10us
# 100us ################################################
# 1ms ################################################################
# 10ms
# 100ms
# 1s
# 10s+
administrator command: Connect\G
# Query 4: 550.97 QPS, 0.56x concurrency, ID 0xA2750AF24EA2AEE6 at byte 727110
# Scores: V/M = 0.00
# Time range: 2018-06-13 16:31:02.412140 to 16:31:02.466589
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 10 30
# Exec time 12 30ms 384us 7ms 1ms 2ms 1ms 445us
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 1 420 14 14 14 14 0 14
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Databases jxweixin
# Hosts 10.160.2.151
# Users jxapp
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms ############
# 10ms
# 100ms
# 1s
# 10s+
SHOW COLLATION\G
# Query 5: 550.61 QPS, 0.27x concurrency, ID 0x3607184B9D9C3A96 at byte 1162249
# Scores: V/M = 0.00
# Time range: 2018-06-13 16:31:02.411476 to 16:31:02.465961
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 10 30
# Exec time 6 15ms 48us 5ms 486us 925us 1ms 95us
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 9 3.13k 107 107 107 107 0 107
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Databases jxweixin
# Hosts 10.160.2.151
# Users jxapp
# Query_time distribution
# 1us
# 10us ################################################################
# 100us ################################################
# 1ms ########
# 10ms
# 100ms
# 1s
# 10s+
# EXPLAIN /*!50100 PARTITIONS*/
/* mysql-connector-java-5.1.26 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment\G
# Query 6: 548.30 QPS, 0.26x concurrency, ID 0xCC47B42511EA22DD at byte 408589
# Scores: V/M = 0.01
# Time range: 2018-06-13 16:31:02.412796 to 16:31:02.467511
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 10 30
# Exec time 5 14ms 34us 11ms 475us 596us 2ms 52us
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 1 420 14 14 14 14 0 14
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Databases jxweixin
# Hosts 10.160.2.151
# Users jxapp
# Query_time distribution
# 1us
# 10us ################################################################
# 100us ####################
# 1ms
# 10ms ##
# 100ms
# 1s
# 10s+
SET NAMES utf8\G
# Query 7: 541.88 QPS, 0.25x concurrency, ID 0xE4CF7146873CCC28 at byte 758719
# Scores: V/M = 0.01
# Time range: 2018-06-13 16:31:02.413009 to 16:31:02.466526
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 9 29
# Exec time 5 13ms 35us 11ms 464us 596us 2ms 44us
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 2 928 32 32 32 32 0 32
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Databases jxweixin
# Hosts 10.160.2.151
# Users jxapp
# Query_time distribution
# 1us
# 10us ################################################################
# 100us #######
# 1ms
# 10ms ##
# 100ms
# 1s
# 10s+
SET character_set_results = NULL\G
# Query 8: 546.36 QPS, 0.23x concurrency, ID 0x18EC0094FCA82DC0 at byte 966121
# Scores: V/M = 0.00
# Time range: 2018-06-13 16:31:02.413953 to 16:31:02.467032
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 9 29
# Exec time 5 12ms 43us 4ms 429us 839us 943us 63us
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 2 841 29 29 29 29 0 29
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Databases jxweixin
# Hosts 10.160.2.151
# Users jxapp
# Query_time distribution
# 1us
# 10us ################################################################
# 100us ##################
# 1ms ######
# 10ms
# 100ms
# 1s
# 10s+
# EXPLAIN /*!50100 PARTITIONS*/
select @@session.tx_read_only\G
# Query 9: 541.84 QPS, 0.19x concurrency, ID 0x3AEAAD0E15D725B5 at byte 1020116
# Scores: V/M = 0.00
# Time range: 2018-06-13 16:31:02.413218 to 16:31:02.466739
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 9 29
# Exec time 4 10ms 33us 7ms 354us 467us 1ms 44us
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 1 464 16 16 16 16 0 16
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Databases jxweixin
# Hosts 10.160.2.151
# Users jxapp
# Query_time distribution
# 1us
# 10us ################################################################
# 100us ##########
# 1ms ##
# 10ms
# 100ms
# 1s
# 10s+
SET autocommit=1\G