mysql数据库——pt-query-digest工具之二分析慢查询日志

系统使用情况
     用户时间，系统时间          物理内存占用数  虚拟内存占用数
# 1.8s user time, 30ms system time, 28.52M rss, 169.81M vsz
报告生成时的系统的当前时间
# Current date: Mon Aug  4 00:42:35 2014
主机名
# Hostname: mrs
文档路径
# Files: /home/mysql/mysql/mysql-mrs/mysql-slow.log
总的sql语句的统计信息：
总体的sql语句的统计数量 去重以后的sql数量8
# Overall: 1.22k total, 122 unique, 0.00 QPS, 0.00x concurrency __________
时间段内的总的统计信息：
# Time range: 2013-11-04 11:05:43 to 2014-08-03 08:00:53
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
执行时间
# Exec time         49239s      4s   2167s     40s     49s     82s     30s
锁的时间
# Lock time            22s       0     12s    18ms   167us   430ms    98us
传送的行指令
# Rows sent         53.32M       0  10.10M  44.79k   7.31k 579.57k       0
行查询数量
# Rows examine       9.33G       0  27.97M   7.84M   9.76M   3.82M   9.30M
# Rows affecte           0       0       0       0       0       0       0
读取的行
# Rows read          4.51G       0  27.97M   3.79M   9.76M   4.63M   2.27k
数据的传送
# Bytes sent         5.44G       0 516.11M   4.57M   2.26M  40.27M  621.67
# Tmp tables             1       0       1    0.00       0    0.03       0
# Tmp disk tbl           0       0       0       0       0       0       0
# Tmp tbl size     124.03k       0 124.03k  104.19       0   3.55k       0
查询的大小
# Query size       413.50k      20   3.93k  347.36  592.07  274.55  441.81

统计的是系统最慢查询的25条sql语句的概要：
# Profile
# Rank Query ID           Response time    Calls R/Call    Apdx V/M   Item
# ==== ================== ================ ===== ========= ==== ===== ====
#    1 0x33205A34919FAC0C 15319.3241 31.1%   484   31.6515 0.00  0.06 SELECT t_cpay_txn_list
#    2 0x370B2A81311AE599 12451.8743 25.3%   246   50.6174 0.00  0.59 SELECT t_cpay_txn_list t_cpay_txn_list_slow t_cpay_txn_list
#    3 0xB37AD9370362074F  2513.1634  5.1%    85   29.5666 0.00  0.03 SELECT t_cpay_txn_list
#    4 0xEBB72B9937FB699A  2466.3281  5.0%     4  616.5820 0.00 12... SELECT t_cpay_txn_list
#    5 0xCEE74A8F91FFED41  1717.9964  3.5%    17  101.0586 0.00 15... SELECT t_cpay_txn_list
#    6 0xBE42C5EF974B624F  1265.8132  2.6%    43   29.4375 0.00  0.04 SELECT t_cpay_txn_list
#    7 0xB41998D17A7140A5  1201.8611  2.4%    20   60.0931 0.00 12... SELECT t_cpay_txn_list
#    8 0xB6EAF3BF2FF52EC8  1141.5683  2.3%     1 1141.5683 0.00  0.00 FLUSH
#    9 0xD1A74E4EF2D679CA  1121.8732  2.3%     2  560.9366 0.00 94... SELECT bak_t_cpay_txn_list
#   10 0x723A851DEEB30D2D   949.6828  1.9%     4  237.4207 0.00  0.10 SELECT t_cpay_txn_list
#   11 0xA7D7E61E7B81DEDC   765.9181  1.6%    38   20.1557 0.00  2.45 SELECT t_cpay_txn_list
#   12 0x5FFB07E06422C516   718.2043  1.5%    24   29.9252 0.00  0.39 SELECT t_cpay_txn_list
#   13 0xEF78C8F322139565   625.9297  1.3%     8   78.2412 0.00 27... SELECT t_cpay_txn_list_hist
#   14 0x269DCA6A7DC66036   585.5395  1.2%     2  292.7698 0.00 56... SELECT t_cpay_txn_orig_list_hist
#   15 0x22BB624DC470B0BF   550.3540  1.1%     2  275.1770 0.00 31.08 SELECT t_cpay_txn_list
#   16 0xAD707881C777E5E5   548.2856  1.1%    18   30.4603 0.00  1.38 SELECT t_cpay_txn_list
#   17 0x94C7D0F578361D67   403.1796  0.8%     6   67.1966 0.00 18.34 SELECT t_cpay_txn_list
#   18 0x023659658FF773E3   303.2028  0.6%     1  303.2028 0.00  0.00 SELECT t_system_log
#   19 0x0A4C170E77C8E691   236.8648  0.5%     8   29.6081 0.00  0.01 SELECT t_cpay_txn_list
#   20 0x7FFD81718BF15247   236.6922  0.5%     4   59.1731 0.00 16.17 SELECT t_fund
#   25 0x5120B10491405DE8   148.7606  0.3%    23    6.4679 0.00  0.02 SELECT t_cpay_txn_list
# MISC 0xMISC              3966.2977  8.1%   179   22.1581   NS   0.0 <101 ITEMS>
主要信息：
Rank：所有语句的排名，默认按查询时间降序排列，通过--order-by指定
Query ID ：语句的ID，（去掉多余空格和文本字符，计算hash值）
Response time：响应时间，占所有响应时间的百分比
Calls：查询执行次数
R/Call：平均响应时间
V/M：响应时间Variance-to-mean的比率
Item：查询语句一部分

每一条慢查询sql语句的详细信息：
# Query 1: 0.00 QPS, 0.00x concurrency, ID 0x33205A34919FAC0C at byte 1013158
# This item is included in the report because it matches --limit.
# Scores: Apdex = 0.00 [1.0], V/M = 0.06
# Query_time sparkline: |       ^|
# Time range: 2013-11-04 11:05:43 to 2014-08-03 08:00:53
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======

QPS每秒查询数，查询并发数（近似），语句ID，在日志文件的偏移量（可以尝试tail -c +763400 mysql-low.log）
# Query 1: 0.00 QPS, 0.00x concurrency, ID 0x5409DE63FA6FBDE4 at byte 6341184
# Scores: V/M = 0.00

检查时间段
# Time range: 2014-07-31 10:05:07 to 2014-08-01 10:29:06

查询属性
# Attribute    pct   total     min     max     avg     95%  stddev  median
              百分比  总计    最小    最大    平均     95%   标准     中等
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         39     484
# Exec time     31  15319s     30s     48s     32s     32s      1s     30s
# Lock time      0    58ms    83us   781us   120us   167us    37us   108us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine  49   4.64G   9.55M  10.11M   9.81M   9.76M 237.47k   9.76M
# Rows affecte   0       0       0       0       0       0       0       0
# Rows read      0     445       0       1    0.92    0.99    0.27    0.99
# Bytes sent     0 303.45k     642     642     642     642       0     642
# 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    51 212.70k     450     450     450     450       0     450
这部分内容同上，不过只是统计的单条sql的信息
我们可以得出这样的信息，这条sql的最小执行时间为30s，最大的执行时间为48s

数据库的一些信息：
# String:
# Databases    mrs_db
# Hosts
# Last errno   0
# Users        mrs

查询分布图：
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################

表结结构的信息
Tables
#    SHOW TABLE STATUS FROM `mrs_db` LIKE 't_cpay_txn_list'\G
#    SHOW CREATE TABLE `mrs_db`.`t_cpay_txn_list`\G
# EXPLAIN /*!50100 PARTITIONS*/

该条sql语句的全部的文本内容
SELECT MERCHANT_ID AS merchantId, TUNNEL_DATA as tunnelData, EXT_TRACE_NO as extTraceNo, ID_TXN as idTxn, '1' as txnType, S_PAN as sPan, DATE_FORMAT(STL_DATE, '%Y%m%d%H%i%s') as stlTime, TERMINAL_ID as terminalId, CUR_STL_AMT as amt  FROM t_cpay_txn_list  WHERE STL_MERCHANT_ID in (201012080000021) AND STL_DATE = '2014-07-31'  AND SRV_CHAN_TYPE in ('L')  AND SRV_ENTRY_MODE like '02%'  AND TXN_TYPE in ('00200')  AND TXN_FLG = 'S'  ORDER BY TXN_TIME\G                         

后面2-25都是相类似的信息，就不一一赘述了。