分类: MySQL

2018-06-13 16:42:56

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
阅读(122) | 评论(0) | 转发(0) |
0

上一篇:pstack命令

下一篇:pt-stalk监控MySQL运行

给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册