正文

从慢查询日志定位卡死到用Percona Toolkit自动化诊断 详解mysqldumpslow及PMMA监控工具实战避坑指南

/0 浏览量
0902

说实话，第一次在生产环境看到MySQL CPU飙到100%的时候，我手心全是汗。那时候我们有个报表服务，每天早上八点准时卡顿，像极了一个睡醒后反应迟钝的中年人。开发小哥说是“卡死”了，但数据库明明还活着，连接数也没爆，就是慢，慢得让人想砸键盘。

后来我们才搞明白，那不是“卡死”，是慢查询在拖后腿。而且最坑的是，大家当时都习惯手动去翻日志，用 grep 瞎找，效率低得离谱，还经常漏掉真正的元凶。今天我就把这段“血泪史”整理出来，手把手教你从慢查询日志的初级定位，一路升级到Percona Toolkit的自动化诊断，顺便把 mysqldumpslow 和 PMMA 这两个神器摸得透透的，避开那些让人头秃的坑。

一、 别急着重启，先学会“听”MySQL的抱怨

很多新手遇到慢查询，第一反应是查 show processlist，然后盯着那些 Sleep 状态的连接发呆，最后恨不得 kill 掉几个解气。但这治标不治本。真正的诊断，得从慢查询日志（Slow Query Log）开始。

1.1 开启慢查询日志：别让它成为摆设

有些同学开启了慢查询日志，但发现里面空荡荡的，或者记录了一堆没用的信息。这通常是因为阈值设得太高，或者日志格式不对。

-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 推荐的生产环境配置
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1; -- 超过1秒的才算慢，太敏感会撑爆磁盘，太迟钝会漏掉隐患
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- 重点！没走索引的查询也要记下来

避坑指南：

  • long_query_time 别设太低：设成 0.1 秒的话，你线上每一个普通的关联查询可能都会进日志，最后日志文件几G几G地涨，磁盘I/O直接爆炸。1秒是个不错的起点，根据业务容忍度调整。
  • 别只靠 slow_query_log：它记录的是“事后诸葛亮”。如果一条SQL执行了10秒，日志里才会看到它。对于真正的“卡死”瞬间，你更需要的是 performance_schema 或者 sys 库里的实时视图。

1.2 人肉分析：mysqldumpslow 的真面目与假象

日志有了，接下来就是怎么读。mysqldumpslow 是MySQL自带的日志分析工具，很多人的使用姿势是这样的：

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

输出一堆类似这样的结果：

Reading mysql slow query log from /var/log/mysql/slow.log
Count: 15  Time=5.00s (75s)  Lock=0.00s (0s)  Rows=1000.0 (22500), user[user]@host
  SELECT * FROM orders WHERE status = ? AND create_time > ?

看着挺像那么回事，对吧？但这里有个巨大的坑：

mysqldumpslow 会模糊化处理参数。它把 12345 变成了 N，把 '2023-01-01' 变成了 '...'。这意味着，它无法告诉你到底是哪个具体的订单ID导致了慢查询。

如果你的SQL里有范围查询，比如 WHERE id > 10000 和 WHERE id > 1000000，mysqldumpslow 会把它们合并成一条。你以为你优化了一条SQL，其实还有另一条更差的SQL躲在后面笑。

实战技巧：

  1. 先用 mysqldumpslow 抓大放小：它适合快速找出“哪类SQL”最慢，比如全是 SELECT * 或者全是某个表的关联查询。
  2. 再用 pt-query-digest 深入挖掘：当你发现某类SQL有问题后，别停在 mysqldumpslow，换工具！
# mysqldumpslow 的常用参数详解
-s t      # 按总耗时排序（Time）
-s c      # 按查询次数排序（Count）
-s r      # 按返回行数排序（Rows）
-t 10     # 只显示前10条
-g        # 正则过滤，比如只看不走索引的
mysqldumpslow -s t -t 10 -g "SELECT.*FROM orders" /var/log/mysql/slow.log

小朋友也能听懂的比喻：

mysqldumpslow 就像是你妈妈问“今天班里谁最调皮”，她只知道“小明很调皮”，但不知道你具体干了什么坏事（是扔纸团还是打架）。而 pt-query-digest 就像是有监控摄像头，不仅知道小明调皮，还知道他是几点几分、在哪个角落、扔了哪个纸团。

二、 从“人肉排查”到“自动化诊断”：Percona Toolkit 的降维打击

如果说 mysqldumpslow 是算盘，那 Percona Toolkit (PTK) 就是自动化流水线。它的核心神器叫 pt-query-digest。

2.1 为什么 pt-query-digest 比 mysqldumpslow 强百倍？

  1. 保留原始参数：它能还原出真实的SQL，包括具体的ID、时间戳，让你能精准复现问题。
  2. 多维度聚类：它不仅按耗时聚类，还能按查询指纹（Query Digest）聚类，自动识别哪些SQL是同一个“变种”。
  3. 生成HTML报告：直接输出一个漂亮的网页报告，老板看了都懂。

2.2 实战：一键生成诊断报告

假设你有一台线上MySQL，慢查询日志已经积累了GB级别。别直接用 pt-query-digest 跑整个日志，那样会卡死你的机器。正确姿势是结合 pt-query-digest 和 slowlog 的截取。

# 步骤1：先用 pt-query-digest 分析慢日志，生成报告
pt-query-digest --report --report-type=flat --no-report-summary /var/log/mysql/slow.log > slow_report.html

# 步骤2：如果想看最近1小时的TOP慢查询
pt-query-digest --filter '$event->{ts} ge "2023-10-27 10:00:00" and $event->{ts} le "2023-10-27 11:00:00"' /var/log/mysql/slow.log

报告里的关键指标怎么看？

  • Query id：查询指纹，相同的SQL指纹会被归为一类。
  • Count：出现次数。如果一个SQL只出现1次但耗时10秒，和出现100次每次耗时0.5秒，优先级不同。前者可能是偶发抖动，后者是常态化瓶颈。
  • Time：总耗时、平均耗时、最大耗时。关注最大耗时，因为那往往是“卡死”的元凶。
  • Rows：扫描行数 vs 返回行数。如果扫描100万行只返回10行，必崩无疑。
  • Query trace：详细执行计划。

真实案例： 我们曾经发现一个SQL，pt-query-digest 显示它的 Rows_examined 是 500万，但 Rows_sent 只有 50。执行计划显示它全表扫了 user_orders 表。加上索引后，平均耗时从 8秒 降到 0.02秒。这就是 pt-query-digest 的价值：一眼看清数据膨胀的本质。

2.3 避坑：pt-query-digest 的内存陷阱

pt-query-digest 在处理超大日志（比如几十GB）时，可能会因为聚类过程占用大量内存，导致分析机本身OOM。

解决方案：

  1. 轮转日志：不要分析堆积了几年的日志。每天或每周生成一个快照。
  2. 使用 --process-time 限制：只分析特定时间段的数据。
  3. 远程分析：如果可能，把慢日志拷贝到一台高性能机器上分析，不要在数据库主机上直接跑。
# 示例：只分析最近7天的日志
pt-query-digest --since "7 days ago" /var/log/mysql/slow.log

三、 实时监控不落地：PMMA 工具的正确打开方式

如果说 PTK 是“死后验尸”，那 PMMA (Percona Monitoring and Management Agents，或者泛指基于 Prometheus/Grafana 的监控方案) 就是“实时体检”。

这里我要澄清一个概念：市面上常说的 PMMA 有时指 Percona 的监控代理，有时也指一些基于 MySQL Enterprise Monitor 的第三方方案。在实际操作中，我更推荐结合 Prometheus + mysqld_exporter + Grafana 的组合，因为 PMMA 原生的商业版本费用较高，而开源方案同样强大。

但既然你问到了 PMMA，我就讲讲它的核心思路，以及如何用它来发现“卡死”的前兆。

3.1 PMMA 能帮你看到什么？

  1. InnoDB 行锁等待：这是“卡死”最常见的原因。一个事务持有锁，另一个事务在等待。PMMA 的 Lock Waits 面板能直接显示当前有多少会话在等待锁。
  2. Temp Table 到磁盘：如果临时表从内存写到磁盘，说明查询复杂度过高，内存配置不足。
  3. Network I/O 瓶颈：有时候SQL不慢，是网络传输慢。

3.2 实战：从 PMMA 告警到 PTK 定位

场景重现： 某天下午，PMMA 面板上 InnoDB Row Lock Waits 曲线突然飙升到 50+。这意味着有50个请求在排队等锁。

错误做法： 直接 show processlist，然后看到一堆 Waiting for table metadata lock，一脸懵逼，然后重启MySQL。

正确做法：

  1. 在 PMMA 中定位时间窗口：看到锁等待是从 14:30 开始的。
  2. 去慢查询日志找 14:30 前后的异常SQL：使用 pt-query-digest 过滤该时间段。
  3. 发现元凶：原来是一个批量更新脚本 UPDATE orders SET status = 2 WHERE create_time < '2020-01-01'，没有加 LIMIT，锁住了整张表。
  4. 解决：通知开发改成小批量更新，或者加索引优化。

代码化运维思维： 不要依赖肉眼盯监控。写脚本自动化：

#!/bin/bash
# 每小时自动检查锁等待超过10秒的查询并告警

LOCK_WAIT_THRESHOLD=10
REPORT=$(mysql -e "SELECT * FROM performance_schema.data_lock_waits" 2>/dev/null)

if echo "$REPORT" | wc -l | grep -qE '^[2-9]'; then
    echo "ALERT: Lock waits detected! Details: $REPORT" | mail -s "MySQL Lock Alert" dba@example.com
fi

3.3 PMMA 的坑：误报与漏报

  • 误报：PMMA 的阈值如果是默认的，可能在业务高峰期频繁告警。建议根据业务峰谷动态调整阈值。
  • 漏报：如果使用了分库分表，单节点的 PMMA 代理可能看不到全局锁等待。这时需要部署全局监控视图，或者使用 Percona 的分布式监控方案。

四、 总结：构建你的MySQL诊断“三板斧”

从慢查询日志到自动化诊断，这不是一个工具的替换，而是一套方法论的建立。我总结为“三板斧”：

  1. 第一斧：日志为王，但别迷信

    • 开启慢查询日志，但 long_query_time 要合理。
    • 用 mysqldumpslow 快速筛查，但要知道它的局限性（参数模糊化）。

  2. 第二斧：PTK 深挖，精准打击

    • 用 pt-query-digest 还原真实SQL，分析执行计划。
    • 关注 Rows_examined vs Rows_sent 的比率，这是发现全表扫描的金标准。
    • 注意大日志分析时的内存压力，分段处理。

  3. 第三斧：实时监控，防患未然

    • 部署 PMMA 或 Prometheus 监控，重点关注锁等待、临时表、网络IO。
    • 建立告警机制，从“救火”转向“防火”。

最后的一点心里话： 数据库诊断这事儿，没有银弹。工具再强大，也得有人去理解背后的原理。当你看到 pt-query-digest 报告里那个 Rows_examined: 5000000 时，你应该能想象到那500万行数据在磁盘上翻滚的样子，然后决定给它加一个索引，让它安静下来。

记住，好的DBA不是会写多少条SQL，而是能在混乱的日志和监控曲线中，听到MySQL的“呻吟”，并找到治愈它的方法。

希望这篇指南能帮你从“卡死”的恐慌中解脱出来，建立起一套自信、系统的诊断流程。如果还有具体的日志或监控问题，随时扔过来，我们一起拆解。

-- 展开阅读全文 --