MySQL追梦旅途之慢查询分析工具mysqldumpslow和pt-query-digest

mysqldumpslow (暂时没测试过)

mysqldumpslow 是 MySQL 自带的一个命令行工具，用于解析和汇总慢查询日志文件。它可以帮助数据库管理员快速了解哪些查询是导致性能瓶颈的主要原因，并为优化提供方向。

基本用法

mysqldumpslow [options] [log_file]

log_file：指定要解析的慢查询日志文件路径。如果不指定，默认会读取所有名为 host_name-slow.log 的文件

常用选项

-a：显示所有字段，包括那些默认被省略的字段（如数据库名）。

-g pattern：只显示匹配给定模式的查询。模式可以包含通配符（例如，%SELECT%）。

-r：反转排序顺序，从最不常用的查询开始显示。

-s：按特定字段排序。常见排序选项包括：

t 或 at：按查询次数排序（总次数）

l 或 al：按锁定时间排序（平均锁定时间）

c 或 ac：按返回行数排序（平均返回行数）

r 或 ar：按实际执行时间排序（平均执行时间）

-t N：仅显示前 N 条记录。

-v：详细模式，显示更多信息。

-d：调试模式，输出额外的调试信息。

示例

1. 查看最常见的10个慢查询

mysqldumpslow -s c -t 10 /path/to/slow-query.log

2. 查看执行时间最长的5个查询

mysqldumpslow -s t -t 5 /path/to/slow-query.log

3. 查找包含“UPDATE”的慢查询

mysqldumpslow -g "UPDATE" /path/to/slow-query.log

4. 按平均执行时间排序并显示前10条

mysqldumpslow -s ar -t 10 /path/to/slow-query.log

5. 显示所有字段的信息

mysqldumpslow -a /path/to/slow-query.log

6. 显示最常出现的10个查询及其平均锁定时间

 mysqldumpslow -s al -t 10 /path/to/slow-query.log

pt-query-digest

由 Percona 提供的强大工具，它可以生成详细的慢查询报告，并提供优化建议。

wget https://downloads.percona.com/downloads/percona-toolkit/3.5.0/binary/tarball/percona-toolkit-3.5.0_x86_64.tar.gz


tar -zxvf ./percona-toolkit-3.5.0_x86_64.tar.gz


//进入目录
perl Makefile.PL PREFIX=/usr/local/percona-toolkit
make && make install




vi /etc/profile

export PATH=$PATH:/usr/bin # 假设 pt-query-digest 在 /usr/bin 目录下

source /etc/profile

常用方法

分析慢查询日志文件：

最简单的方式是直接提供慢查询日志文件作为输入：

pt-query-digest /path/to/slow-query.log

这将输出一个包含查询模式、频率、响应时间等信息的报告。

使用标准输入：

如果你的日志文件位于远程服务器上，可以结合 SSH 和管道来实时分析：

ssh user@remote.server 'cat /path/to/slow-query.log' | pt-query-digest

分析特定时间段内的查询：

你可以指定只分析某个时间段内的查询，例如过去4小时的数据：

pt-query-digest --since='4h' /path/to/slow-query.log

时间单位可以是s（秒）、m（分钟）、h（小时）、d（天）等。例如，10m表示10分钟，2d表示2天




//绝对时间
pt-query-digest --until='2024-12-22 12:00:00' /path/to/slow-query.log
//只分析截至当前时间前1小时的查询（即过去1小时内记录的查询不会被分析）
pt-query-digest --until='1h' /path/to/slow-query.log


//分析从 2024-12-22 10:00:00 到 2024-12-22 12:00:00 之间的查询
pt-query-digest --since='2024-12-22 10:00:00' --until='2024-12-22 12:00:00' /path/to/slow-query.log

限制输出结果：

通过 –limit 参数限制输出的结果数量或百分比：

pt-query-digest --limit=10% /path/to/slow-query.log

--limit=10%：
这个选项指定了输出结果的比例。设置为百分比（如10%）时，它表示只显示累计执行时间占所有查询总执行时间前10%的查询。

排序查询：

根据不同的指标对查询进行排序，比如平均查询时间 ( query_time:avg ) 或总查询时间 ( query_time:sum )：

pt-query-digest --order-by Rows_sent:sum --limit=50% /path/to/slow-query.log > top_rows_sent_queries_report.txt

//列出了返回行数累计总和占所有查询前50%的查询

过滤查询：

使用 –filter 参数过滤特定类型的查询，例如只显示插入操作：

pt-query-digest --filter='$event->{arg} =~ m/^INSERT/i' /path/to/slow-query.log

将分析结果存储到数据库中：

为了长期保存和进一步分析，你可以将结果存储在数据库表中：

pt-query-digest --review h=review_host,D=percona,t=global_query_review \
                --history h=review_host,D=percona,t=global_query_review_history \
                /path/to/slow-query.log

这里 h=, D=, t= 分别代表主机名、数据库名和表名


--review 参数
    h=review_host：指定了目标MySQL服务器的主机名或IP地址。
    D=percona：表示要使用的数据库名为percona。
    t=global_query_review：指定了用于存储查询审查数据的目标表为global_query_review。

这个参数告诉pt-query-digest将分析后的查询摘要信息插入到global_query_review表中。这张表通常用于记录每个唯一查询的执行情况，包括首次出现的时间、最近一次执行时间、执行次数等信息。这使得你可以对查询进行长期监控，并识别出哪些查询是新出现的或者执行频率增加的。

--history 参数
此参数的作用是将每次运行pt-query-digest时生成的查询统计信息追加到global_query_review_history表中。这样可以保存每次分析的结果，形成一个历史记录，方便你比较不同时间段内的查询性能变化，以及评估优化措施的效果。

CREATE TABLE `global_query_review` (
  `checksum` varchar(200) NOT NULL,
  `fingerprint` text NOT NULL,
  `sample` longtext,
  `first_seen` datetime DEFAULT NULL,
  `last_seen` datetime DEFAULT NULL,
  `reviewed_by` varchar(20) DEFAULT NULL,
  `reviewed_on` datetime DEFAULT NULL,
  `comments` text,
  `reviewed_status` varchar(24) DEFAULT NULL,
  PRIMARY KEY (`checksum`)
) ENGINE=InnoDB

CREATE TABLE `global_query_review_history` (
  `hostname_max` varchar(64) NOT NULL,
  `db_max` varchar(64) DEFAULT NULL,
  `checksum` varchar(200) NOT NULL,
  `sample` longtext,
  `ts_min` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `ts_max` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `ts_cnt` float DEFAULT NULL,
  `query_time_avg` float DEFAULT NULL,
  UNIQUE KEY `hostname_max` (`hostname_max`,`checksum`,`ts_min`,`ts_max`),
  KEY `ts_min` (`ts_min`),
  KEY `checksum` (`checksum`)
) ENGINE=InnoDB

实时查询分析：(暂时没测试过)

如果你想分析正在运行的 MySQL 服务器上的实时查询，可以使用如下命令：

pt-query-digest --processlist h=localhost,u=root,p=your_password \
                --interval=1s --run-time=60s

pt-query-digest --processlist h=47.120.66.18,u=root,p=Aa23456 --interval=1s --run-time=60s

查看帮助：

要查看所有可用选项及其解释，可以运行：

pt-query-digest --help

结果字段解释

基本信息

● User Time: 170ms 用户时间，即应用程序在用户空间执行的时间。

● System Time: 20ms 系统时间，即应用程序在内核空间执行的时间。

● RSS (Resident Set Size): 35.09M 实际使用的内存大小。

● VSZ (Virtual Size): 102.66M 虚拟内存大小。

时间戳

● Current Date: Sat Dec 21 15:10:29 2024 当前日期和时间。

● Hostname: iZf8zhlfifa37v6msjd8qykZ 主机名。

文件路径

● Files: /mydata/mysql/data/4e5ac4a22869-slow.log 慢查询日志文件的路径。

总体统计

Overall:

● Total: 21 总共记录了21个慢查询。

● Unique: 13 其中13个是唯一的查询。

● QPS (Queries Per Second): 0.00 每秒查询数为0.00。

● Concurrency: 0.00x 并发度为0.00倍。

时间范围

● Time Range: 从2024-12-17T06:34:25到2024-12-19T12:20:26 查询的时间范围。

统计指标

Attribute:

总执行时间：表示所有慢查询的总执行时间。

最小、最大、平均执行时间：分别表示最短、最长和平均执行时间。

95%执行时间：表示95%的查询执行时间不超过这个值。

标准差：表示执行时间的波动程度。

中位数：表示中间值，即一半查询执行时间低于此值，一半高于此值。

Exec Time (Execution Time)：执行时间

● Total: 10s 总执行时间为10秒。

● Min: 1ms 最小执行时间为1毫秒。

● Max: 10s 最大执行时间为10秒。

● Avg: 488ms 平均执行时间为488毫秒。

● 95%: 128ms 95%的执行时间小于或等于128毫秒。

● Stddev (Standard Deviation): 2s 标准差为2秒。

● Median: 4ms 中位数为4毫秒。

Lock Time (Locking Time)：锁定时间

● Total: 1ms 总锁定时间为1毫秒。

● Min: 0 最小锁定时间为0毫秒。

● Max: 940us 最大锁定时间为940微秒。

● Avg: 62us 平均锁定时间为62微秒。

● 95%: 20us 95%的锁定时间小于或等于20微秒。

● Stddev: 200us 标准差为200微秒。

● Median: 6us 中位数为6微秒。

Rows Sent (Rows Sent to Client)：发送给客户端的行

● Total: 196.35k 总共发送给客户端的行数为196,350行。

● Min: 0 最小发送行数为0行。

● Max: 97.66k 最大发送行数为97,660行。

● Avg: 9.35k 平均发送行数为9,350行。

● 95%: 329.68 95%的发送行数小于或等于329.68行。

● Stddev: 28.47k 标准差为28,470行。

● Median: 2.90 中位数为2.90行。

Rows Examined (Rows Examined by Query)：按查询检查的行

● Total: 197.21k 总共被查询检查的行数为197,210行。

● Min: 0 最小检查行数为0行。

● Max: 97.66k 最大检查行数为97,660行。

● Avg: 9.39k 平均检查行数为9,390行。

● 95%: 621.67 95%的检查行数小于或等于621.67行。

● Stddev: 28.46k 标准差为28,460行。

● Median: 13.83 中位数为13.83行。

Query Size (Size of Queries)：查询大小

● Total: 1.60k 总查询大小为1,600字节。

● Min: 11 最小查询大小为11字节。

● Max: 896 最大查询大小为896字节。

● Avg: 77.86 平均查询大小为77.86字节。

● 95%: 112.70 95%的查询大小小于或等于112.70字节。

● Stddev: 181.53 标准差为181.53字节。

● Median: 27.38 中位数为27.38字节。

主要用于监控和优化数据库性能

基本信息

Profile: 表示这是一个性能分析报告。

● Rank: 查询的排名，按照响应时间排序。

● Query ID: 查询的唯一标识符。

● Response time: 查询的响应时间（单位：秒）。

● Calls: 该查询被调用的次数。

● R/Call: 每次调用的平均响应时间（单位：秒）。

● V/M: 虚拟机操作数（通常用于内存管理）。

Query ID: 0x19A1F14EFC0F221D30AFCB1E1344BEBD
    Response time: 9.5735 秒
    Calls: 93.5% 的总调用次数
    R/Call: 4.7868 秒
    V/M: 9.37
    SQL Statement: SELECT Users

具体哪一条语句的指标参数

基本信息

● Query ID: 0x19A1F14EFC0F221D30AFCB1E1344BEBD 查询的唯一标识符。

● Time range: 从 2024-12-17T06:34:25 到 2024-12-17T11:23:30 查询的时间范围。

统计指标

Attribute:

Count: 总共记录了9次查询。

Exec time (Execution Time)：

● pct: 93% 的查询执行时间占比。

● total: 总执行时间为10秒。

● min: 最小执行时间为52毫秒。

● max: 最大执行时间为10秒。

● avg: 平均执行时间为5秒。

● 95%: 95%的查询执行时间小于或等于10秒。

● stddev: 标准差为7秒。

● median: 中位数为5秒。

Lock time (Locking Time)：

● pct: 0% 的查询锁定时间占比。

● total: 总锁定时间为5微秒。

● min: 最小锁定时间为2微秒。

● max: 最大锁定时间为3微秒。

● avg: 平均锁定时间为2微秒。

● 95%: 95%的查询锁定时间小于或等于3微秒。

● stddev: 标准差为0。

● median: 中位数为2微秒。

Rows sent (Rows Sent to Client)：发送给客户端的行

● pct: 99% 的查询发送行数占比。

● total: 总共发送给客户端的行数为195,310行。

● min: 最小发送行数为97,660行。

● max: 最大发送行数为97,660行。

● avg: 平均发送行数为97,660行。

● 95%: 95%的查询发送行数小于或等于97,660行。

● stddev: 标准差为0。

● median: 中位数为97,660行。

Rows examine (Rows Examined by Query)：按查询检查的行

● pct: 99% 的查询检查行数占比。

● ** **total: 总共被查询检查的行数为195,310行。

● min: 最小检查行数为97,660行。

● max: 最大检查行数为97,660行。

● avg: 平均检查行数为97,660行。

● 95%: 95%的查询检查行数小于或等于97,660行。

● stddev: 标准差为0。

● median: 中位数为97,660行。

Query size (Size of Queries)：查询大小

● pct: 2% 的查询大小占比。

● total: 总查询大小为38字节。

● min: 最小查询大小为19字节。

● max: 最大查询大小为19字节。

● avg: 平均查询大小为19字节。

● 95%: 95%的查询大小小于或等于19字节。

● stddev: 标准差为0。

● median: 中位数为19字节。

String: 查询字符串。

Hosts: 连接主机的IP地址及其权重。

117.61.100.132 (1/50%)：表示该主机占总连接数的50%。

localhost (1/50%)：表示本地主机占总连接数的50%。

Users：用户名。

root：表示使用root用户进行查询。

查询时间分布

Query_time distribution: 查询时间分布情况。

1us: 1微秒。

10us: 10微秒。

100us: 100微秒。

1ms: 1毫秒。

10ms: 10毫秒。

100ms: 100毫秒。

1s: 1秒。

10s+: 大于10秒。

查询时间分布图

使用#符号表示不同时间范围内的查询次数。

10ms: 大约有n次查询在10毫秒范围内。

1s: 大约有n次查询在1秒范围内。

10s+: 大约有n次查询在10秒以上。

查询语句

Tables: 查询涉及的表。

SHOW TABLE STATUS LIKE ‘Users’\G: 显示名为Users的表的状态。

SHOW CREATE TABLE Users\G: 显示Users表的创建语句。

EXPLAIN /!50100 PARTITIONS/ SELECT * from Users\G: 解释查询SELECT * FROM Users的执行计划，并启用分区优化。