Fork me on GitHub

MySQL 慢查询分析工具-pt-query-digest

字数统计: 2k字   |   阅读时长≈ 9分

官方文档: pt-query-digest
下载地址: DOWNLOADING

pt-query-digest 简介

pt-query-digest是用于分析mysql慢查询的一个工具，它可以分析binlog、General log、slowlog，也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。

pt-query-digest 安装

  1. 下载软件包

    1
    wget percona.com/get/percona-toolkit.tar.gz

    或者使用 wget percona.com/get/TOOL 命令下载单个工具，将 TOOL 换成想下载的工具名称

    1
    wget percona.com/get/pt-query-digest

  2. 安装 perl 模块

    1
    yum install -y perl-CPAN perl-Time-HiRes

  3. 安装步骤

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    # RPM 安装
    cd /usr/local/src
    wget percona.com/get/percona-toolkit.rpm
    yum install -y percona-toolkit.rpm

    # 源码安装(推荐)
    cd /usr/local/src
    wget percona.com/get/percona-toolkit.tar.gz
    tar zxvf percona-toolkit.tar.gz
    cd percona-toolkit-3.3.1

    perl Makefile.PL PREFIX=/usr/local/percona-toolkit

    make && make install

  4. 各种工具用法文档Percona Toolkit Documentation

pt-query-digest 使用

语法

1
pt-query-digest [OPTIONS] [FILES] [DSN]

重要选项

  • --create-review-table: 当使用 --review 参数把分析结果输出到表中时，如果没有表就自动创建;
  • --create-history-table: 当使用 --history 参数把分析结果输出到表中时，如果没有表就自动创建;
  • --filter: 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析;
  • --limit: 限制输出结果百分比或数量，默认值是20，即将最慢的 20 条语句输出，如果是 50% 则按总响应时间占比从大到小排序，输出到总和达到50% 位置截止;
  • --host: mysql服务器地址;
  • --user: mysql用户名;
  • --password: mysql用户密码;
  • --history: 将分析结果保存到表中，分析结果比较详细，下次再使用 --history 时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同 一CHECKSUM 来比较某类型查询的历史变化;
  • --review: 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用 --review 时，如果存在相同的语句分析，就不会记录到数据表中;
  • --output: 分析结果输出类型，值可以是 report (标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读;
  • --since: 从什么时间开始分析，值为字符串，可以是指定的某个 yyyy-mm-dd [hh:mm:ss] 格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计;
  • --until: 截止时间，配合 -—since 可以分析一段时间内的慢查询。

示例

pt-query-digest 主要用来分析 mysql 的慢日志，与 mysqldumpshow 工具相比，py-query_digest 工具的分析结果更具体，更完善。

1
pt-query-digest /data/mysql/mysql-slow.log

常用的用法分析

  1. 直接分析慢查询文件:

    1
    pt-query-digest /data/mysql/mysql-slow.log > slow_report.log

  2. 分析最近12小时内的查询：

    1
    pt-query-digest --since=12h /data/mysql/mysql-slow.log > slow_report2.log

  3. 分析指定时间范围内的查询：

    1
    2
    3
    pt-query-digest /data/mysql/mysql-slow.log \
      --since '2021-08-01 09:30:00' \
      --until '2021-08-03 09:30:00' > slow_report3.log

  4. 分析指含有 select 语句的慢查询

    1
    pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /data/mysql/mysql-slow.log > slow_report4.log

  5. 针对某个用户的慢查询

    1
    pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /data/mysql/mysql-slow.log > slow_report5.log

  6. 查询所有的全表扫描或 full join 的慢查询

    1
    2
    3
    pt-query-digest \
      --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' \
      /data/mysql/mysql-slow.log > slow_report6.log

pt-query-digest 报告分析

pt-query-digest 的报告导出信息如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
# 30.1s user time, 2.5s system time, 28.40M rss, 218.48M vsz
# Current date: Wed Aug  4 11:34:56 2021
# Hostname: dev-dbs
# Files: /data/mysql/mysql-slow.log
# Overall: 13 total, 3 unique, 0.00 QPS, 0.00x concurrency _______________
# Time range: 2021-08-02T00:31:49 to 2021-08-04T01:16:07
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            20s      1s      2s      2s      2s   247ms      2s
# Lock time           11ms   148us     8ms   864us   445us     2ms   287us
# Rows sent          1.12M      20 142.25k  88.06k 136.54k  65.80k 136.54k
# Rows examine       3.33M 225.33k 284.50k 262.43k 283.86k  29.59k 283.86k
# Query size         3.30k     126     308  260.08  299.03   44.24  246.02

# Profile
# Rank Query ID                            Response time Calls R/Call V/M 
# ==== =================================== ============= ===== ====== ====
#    1 0xD486793322A0C370F9A6BF8F64D7F783  13.7696 67.6%     8 1.7212  0.01 SELECT tms_order
#    2 0x7DE5995980E9A7C71A98B3A831A87062   5.4931 27.0%     4 1.3733  0.01 SELECT tms_order TMS_STORE_INFO
#    3 0x87D91CB53FEE6F71A6A502E81BC3D4EE   1.0970  5.4%     1 1.0970  0.00 SELECT tms_order

# Query 1: 0.00 QPS, 0.01x concurrency, ID 0xD486793322A0C370F9A6BF8F64D7F783 at byte 194901353
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2021-08-04T00:48:02 to 2021-08-04T01:16:07
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         61       8
# Exec time     67     14s      2s      2s      2s      2s   158ms      2s
# Lock time     14     2ms   148us   352us   207us   348us    68us   180us
# Rows sent     99   1.11M 142.25k 142.25k 142.25k 142.25k       0 142.25k
# Rows examine  66   2.22M 284.50k 284.50k 284.50k 284.50k       0 284.50k
# Query size    60   1.98k     254     254     254     254       0     254
# String:
# Databases    tms_dev
# Hosts        192.168.64.165
# Users        tms
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `tms_dev` LIKE 'tms_order'\G
#    SHOW CREATE TABLE `tms_dev`.`tms_order`\G
# EXPLAIN /*!50100 PARTITIONS*/
select a.id ,a.ORDER_NO,a.order_time,a.warehouse_name , a.store_name,
                           a.STORE_ADDRESS,a.delivery_time,a.shipper_address, a.receiver_address,
                           a.total_volume,a.dispatch_dept,a.DISTRIBUTION_CYCLE
                from tms_order a
                         ORDER BY a.ORDER_TIME DESC\G

# Query 2: 0.00 QPS, 0.00x concurrency, ID 0x7DE5995980E9A7C71A98B3A831A87062 at byte 194897284
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2021-08-02T00:31:49 to 2021-08-02T06:02:45
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         30       4
# Exec time     26      5s      1s      2s      1s      2s   128ms      1s
# Lock time     81     9ms   297us     8ms     2ms     8ms     3ms     4ms
# Rows sent      0      80      20      20      20      20       0      20
# Rows examine  26 908.02k 225.33k 227.57k 227.01k 222.42k       0 222.42k
# Query size    36   1.19k     305     308  305.75  299.03       0  299.03
# String:
# Databases    tms_dev
# Hosts        192.168.64.61
# Users        tms
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `tms_dev` LIKE 'tms_order'\G
#    SHOW CREATE TABLE `tms_dev`.`tms_order`\G
#    SHOW TABLE STATUS FROM `tms_dev` LIKE 'TMS_STORE_INFO'\G
#    SHOW CREATE TABLE `tms_dev`.`TMS_STORE_INFO`\G
# EXPLAIN /*!50100 PARTITIONS*/
select a.id ,a.ORDER_NO,a.order_time,a.warehouse_name , a.store_name,
                a.STORE_ADDRESS,a.delivery_time,a.shipper_address, a.receiver_address,
                 a.total_volume,a.dispatch_dept,a.DISTRIBUTION_CYCLE
                from tms_order a left JOIN TMS_STORE_INFO b on a.STORE_NAME=b.NAME
                 ORDER BY a.ORDER_TIME DESC LIMIT 20,20\G
...

第一部分, 总体统计结果

  • Overall：总共有多少条查询
  • Time range：查询执行的时间范围
  • unique：唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询
  • total：总计
  • min：最小
  • max：最大
  • avg：平均
  • 95%：把所有值从小到大排列，位置位于95%的那个数，这个数一般最具有参考价值
  • median：中位数，把所有值从小到大排列，位置位于中间那个数

第二部分, 查询分组统计结果

  • Rank：所有语句的排名，默认按查询时间降序排列，通过 --order-by 指定
  • Query ID：语句的ID，（去掉多余空格和文本字符，计算hash值）
  • Response：总的响应时间
  • time：该查询在本次分析中总的时间占比
  • calls：执行次数，即本次分析总共有多少条这种类型的查询语句
  • R/Call：平均每次执行的响应时间
  • V/M：响应时间Variance-to-mean的比率
  • Item：查询对象

第三部分, 每一种查询的详细统计结果

  • ID：查询的ID号，和上图的Query ID对应
  • Databases：数据库名
  • Users：各个用户执行的次数（占比）
  • Query_time distribution ：查询时间分布, 长短体现区间占比。
  • Tables：查询中涉及到的表
  • Explain：SQL语句

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia-plus根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

主要涉及技术：
Linux 运维、自动化运维、
Kubernetes、Python、Jenkins、Docker

联系QQ:2350686113

很惭愧

只做了一点微小的工作
谢谢大家