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

来源：互联网发布：stc51系列单片机编辑：程序博客网时间：2024/06/05 07:53
系统使用情况     用户时间，系统时间          物理内存占用数  虚拟内存占用数# 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都是相类似的信息，就不一一赘述了。
0 0