正文

MySQL数据库性能监控工具实战：从慢查询日志分析到Prometheus+Grafana可视化大屏搭建解决高并发卡顿与连接数爆满问题

/0 浏览量
0726

嘿，朋友。我是Agnes。

想象一下这个场景：周五晚上八点，流量高峰刚过，你正享受着下班后的宁静，突然手机震动——报警群炸了。你的核心业务系统响应时间从200ms飙升到5秒，甚至直接超时。用户投诉如潮水般涌来：“怎么这么卡？”“是不是服务器挂了？”你连滚带爬地打开服务器，发现MySQL的连接数已经飙到了上限，CPU占用率100%，整个数据库像是一台过载的拖拉机，发出痛苦的轰鸣声。

这种时刻，每一个后端工程师和DBA都经历过。很多时候，我们不是输在代码逻辑上，而是输在对数据库“身体状况”的无知上。直到今天之前，你可能还在靠show processlist和肉眼观察日志来排查问题，这就像是用听诊器去诊断CT片子的问题——既慢又不准。

今天，我不跟你讲那些枯燥的理论定义，我们要干点实际的。我要带你搭建一套全链路、可视化、自动化的MySQL监控体系。从最基础的慢查询日志（Slow Query Log）深度剖析，到引入Prometheus抓取实时指标，最后用Grafana画出让你老板和同事目瞪口呆的大屏。我们的目标只有一个：在高并发来临前，提前嗅到危机的味道；在连接数爆满时，精准定位是哪条SQL在搞鬼。

准备好了吗？让我们把那个“黑盒”般的数据库变成透明的玻璃房。

第一阶段：知己知彼——慢查询日志的深度解剖学

在引入任何高级工具之前，我们必须回归本源。MySQL自带的慢查询日志是性能优化的第一道防线。但大多数人只看个大概，这远远不够。我们需要像法医一样审视每一行日志。

1.1 开启并配置慢查询

默认情况下，慢查询可能并没有开启，或者阈值设得太高（比如10秒），导致很多轻微的性能瓶颈被忽略。在生产环境中，我建议将阈值设为 1秒 甚至 0.5秒。

在你的 my.cnf 或 mysqld.cnf 中添加以下配置：

[mysqld]
# 开启慢查询日志
slow_query_log = 1
# 日志文件路径
slow_query_log_file = /var/log/mysql/slow.log
# 超过1秒的查询记录为慢查询
long_query_time = 1
# 记录未使用索引的查询（即使它很快）
log_queries_not_using_indexes = 1
# 对于临时表，如果超过一定大小也记录下来，防止内存溢出
tmp_table_size = 64M
max_heap_table_size = 64M

注意：修改配置后需要重启MySQL服务。在生产环境重启前，务必确认你的业务允许短暂停机，或者使用在线变更工具（如pt-online-schema-change等辅助手段，虽然改配置通常只需重启daemon）。

1.2 使用 mysqldumpslow 进行初步统计

拿到日志后，不要直接用文本编辑器打开几GB的日志文件，那会让你电脑死机。使用MySQL自带的工具 mysqldumpslow 可以快速聚合相似查询。

例如，你想找出执行次数最多且平均耗时最长的10条SQL：

# -s t: 按总耗时排序
# -r: 降序排列
# -t 10: 只取前10条
# -g "select": 只包含SELECT语句
mysqldumpslow -s t -r -t 10 -g "select" /var/log/mysql/slow.log

输出结果可能长这样：

Count: 500  Time=2.00s (1000s)  Lock=0.00s (0s)  Rows=100.0 (50000), user[user]@hostname
  SELECT * FROM orders WHERE status = 'pending' AND create_time > ?

这里的关键信息是 Count（执行频率）、Time（单次耗时）和 Rows（返回行数）。如果某条SQL执行频率极高，即使单次耗时只有0.1秒，累积起来的数据库负载也是惊人的。

1.3 终极武器：pt-query-digest

如果说 mysqldumpslow 是瑞士军刀，那么 Percona Toolkit 中的 pt-query-digest 就是重型挖掘机。它能生成详细的HTML报告，包含Top SQL、碎片化分析、索引建议等。

安装 Percona Toolkit：

# Ubuntu/Debian
sudo apt-get install percona-toolkit
# CentOS/RHEL
sudo yum install percona-toolkit

运行分析：

pt-query-digest /var/log/mysql/slow.log > report.html

打开生成的 report.html，你会看到一个结构清晰的报告。重点关注以下几个部分：

  1. Overall: 整体概况，查看慢查询总数、时间跨度。
  2. Top 10 queries by avg time: 平均耗时最高的前10条SQL。
  3. Queries with full table scan: 全表扫描的查询。这是性能杀手，必须优化！
  4. Queries with temporary tables: 使用了临时表的查询。如果频繁出现，可能需要调整 sort_buffer_size 或优化SQL避免文件排序。

实战案例： 假设报告中显示有一条SQL SELECT * FROM users JOIN orders ON users.id = orders.user_id WHERE users.city = 'Beijing' 占据了80%的慢查询时间。

通过 EXPLAIN 分析这条SQL，你可能会发现 orders 表没有针对 user_id 的索引，或者 users 表的 city 字段区分度太低。这时候，你就知道该加什么索引了。

给小白的提示：加索引就像给图书馆的书贴标签。如果没有标签（索引），管理员（MySQL）就得跑遍整个图书馆（全表扫描）去找书。标签贴得越多，找得越快，但贴标签本身也需要时间（写入性能下降）。所以，索引要适量，且要精准。

第二阶段：实时监控——搭建 Prometheus + MySQL Exporter 数据管道

慢查询日志是“事后诸葛亮”，它告诉你过去发生了什么。但在高并发场景下，你需要的是“实时预警”。当连接数即将爆满时，你必须在它崩溃前的毫秒级做出反应。这就是 Prometheus 的用武之地。

2.1 为什么选择 Prometheus？

Prometheus 是目前云原生时代的事实标准监控解决方案。它的优势在于：

  • 多维数据模型：时间序列数据，带有标签（Labels），查询能力极强。
  • Pull 模式：主动拉取数据，解耦了监控系统和被监控对象。
  • 丰富的生态：拥有大量的 Exporter，包括专门针对 MySQL 的 mysqld_exporter。

2.2 部署 mysqld_exporter

mysqld_exporter 是一个轻量级的代理程序，它连接到 MySQL，收集各种指标（QPS, TPS, 连接数, InnoDB状态等），并将其暴露给 Prometheus 通过 HTTP 接口获取。

步骤 1：创建监控专用用户

为了安全起见，不要使用 root 账号让 exporter 登录。创建一个权限受限的用户：

CREATE USER 'exporter'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';
FLUSH PRIVILEGES;

步骤 2：下载并启动 exporter

以 Docker 方式为例（推荐，隔离性好）：

docker run -d \
  --name mysqld-exporter \
  -p 9104:9104 \
  -e DATA_SOURCE_NAME="exporter:StrongPassword123!@(localhost:3306)/" \
  prom/mysqld-exporter:v0.14.0

现在，访问 http://your-server-ip:9104/metrics，你应该能看到一大串类似这样的数据：

# HELP mysql_global_status_connections_current Current number of open connections.
# TYPE mysql_global_status_connections_current gauge
mysql_global_status_connections_current 150
# HELP mysql_global_variables_max_connections Maximum permitted number of connections.
# TYPE mysql_global_variables_max_connections gauge
mysql_global_variables_max_connections 151

2.3 配置 Prometheus

编辑 prometheus.yml 文件，添加 scrape 配置：

global:
  scrape_interval: 15s # 每15秒抓取一次

scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']
        labels:
          instance: 'mysql-primary'

重启 Prometheus：

docker restart prometheus

现在，Prometheus 已经开始每15秒从 MySQL 抓取一次指标了。你可以去 Prometheus 的 UI (http://localhost:9090)，在 Graph 页面输入 mysql_global_status_connections_current，看看是否有数据跳动。

第三阶段：可视化艺术——Grafana 大屏搭建

数据有了，但 Prometheus 自带的 UI 并不适合展示给团队或老板看。我们需要 Grafana。

3.1 安装与连接

Grafana 同样推荐使用 Docker 安装：

docker run -d \
  --name grafana \
  -p 3000:3000 \
  grafana/grafana

打开 http://localhost:3000，默认账号密码是 admin/admin。登录后，进入 Configuration -> Data Sources，添加 Prometheus 数据源，URL 填 http://prometheus:9090（如果在同一网络下）或 http://localhost:9090。

3.2 导入社区仪表盘

不要从零开始画图表！那是浪费生命。Grafana 有一个庞大的社区仪表盘市场。

  1. 点击左侧菜单 Dashboards -> Browse。
  2. 搜索 “MySQL” 或 “Percona MySQL”。
  3. 选择一个评分高、更新频繁的 Dashboard（例如 ID: 7362 或 10000）。
  4. 点击 Import，选择刚才添加的 Prometheus 数据源。

瞬间，你会得到一个包含数十个图表的专业大屏：

  • General Overview: 总体概览，包括 QPS, TPS, 连接数趋势。
  • Network I/O: 网络流量监控。
  • InnoDB Buffer Pool: 缓冲池命中率，这是MySQL性能的命脉。
  • Replication Lag: 主从延迟（如果有集群）。

3.3 定制你的“作战指挥室”

虽然社区模板很好，但我们需要针对“高并发卡顿”和“连接数爆满”这两个痛点进行定制。

关键图表 1：连接数监控与预警

我们要关注两个指标：

  • mysql_global_status_threads_connected: 当前连接数。
  • mysql_global_variables_max_connections: 最大允许连接数。

创建面板：

  1. 新建 Panel，选择 Graph。
  2. 添加 Query A: mysql_global_status_threads_connected。
  3. 添加 Query B: mysql_global_variables_max_connections。
  4. 在 Query B 的设置中，勾选 Add threshold，设置为红色，数值为 max_connections * 0.9（即90%容量）。
  5. 当曲线接近红线时，意味着连接池即将耗尽，新请求将被拒绝。

告警规则配置（Alerting）： 在 Grafana 中，我们可以设置 Alert Rule。当 threads_connected > max_connections * 0.8 持续 1 分钟时，发送通知到钉钉/企业微信/Slack。

关键图表 2：活跃查询与锁等待

高并发卡顿往往源于锁等待。我们需要监控 Innodb_row_lock_waits 和 Innodb_row_lock_time_avg。

创建面板：

  1. 选择 Table 或 Time series。
  2. 查询：rate(mysql_global_status_innodb_row_lock_waits[5m])。
  3. 如果这个值突然飙升，说明有大量事务在争抢行锁。

结合之前的慢查询日志，你可以快速定位是哪张表、哪个操作导致了锁竞争。

关键图表 3：QPS/TPS 趋势与 CPU 关联

将 MySQL 的 QPS（每秒查询数）与服务器 CPU 使用率放在同一个面板中对比。

  • Query 1: rate(mysql_global_status_queries[5m])
  • Query 2: node_cpu_seconds_total{mode="idle"} * -1 (假设你同时监控了 Node Exporter)

如果 QPS 平稳上升，但 CPU 也线性上升，说明系统正在正常处理负载。但如果 QPS 持平甚至下降，而 CPU 却达到 100%，这说明存在严重的上下文切换或锁竞争，导致 CPU 空转。这是一个非常典型的“假性高负载”信号。

第四阶段：实战演练——解决“幽灵”卡顿与连接数爆满

理论讲完了，我们来模拟一个真实的故障场景，看看这套系统如何帮你破案。

场景描述

周一上午 10:00，客服反馈系统响应缓慢。用户点击“下单”按钮后，需要等待 10 秒以上才能看到结果。

第一步：查看 Grafana 大屏

你迅速打开 Grafana，切换到 MySQL Overview 面板。

  1. 观察连接数：发现 threads_connected 曲线在 10:00 左右垂直拉升，迅速逼近 max_connections 的红线。当前值为 1400/1500。
  2. 观察 QPS：QPS 并没有显著增加，反而略有下降。
  3. 观察 CPU：CPU 使用率在 40%-50% 之间波动，并未满载。

初步判断：这不是因为流量暴增导致的 CPU 瓶颈，而是连接数耗尽。为什么连接数会爆满？因为每个连接都在等待资源释放，无法及时归还给连接池。

第二步：深入排查——寻找“僵尸”连接

你切换到 Connections 面板，查看 mysql_global_status_threads_running（活跃线程数）。发现活跃线程数只有 5 个，但总连接数是 1400。这意味着有 1395 个连接处于空闲状态，或者正在等待某种资源。

这很可能是长事务或未关闭的连接导致的。

你打开 Process List 面板（如果配置了相关 Exporter），或者直接 SSH 到数据库服务器执行：

SHOW FULL PROCESSLIST;

你发现大量连接的状态是 Sleep，但它们的 Time 列显示已经休眠了超过 300 秒。更糟糕的是，有几个连接的状态是 Waiting for table metadata lock。

线索出现：有人执行了一条 DDL 操作（比如 ALTER TABLE），锁住了元数据，导致后续所有对该表的写入请求都在等待。同时，应用程序端的连接池配置不当，没有设置合理的 maxLifetime，导致大量陈旧连接堆积。

第三步：结合慢查询日志验证

你回到之前生成的 pt-query-digest 报告，或者实时查看慢查询日志。

tail -f /var/log/mysql/slow.log

果然，在 10:00 左右，出现了一条耗时极长的 SQL：

ALTER TABLE orders ADD INDEX idx_new_status (status);

这条 DDL 操作在 MySQL 5.7 及以下版本中是阻塞性的，它会持有元数据锁，直到执行完毕。而在 MySQL 8.0 中，虽然支持在线 DDL，但如果表非常大，依然会消耗大量 IO 和 CPU，并可能导致连接排队。

第四步：紧急止血与长期优化

紧急措施：

  1. 终止长事务/阻塞会话：找到持有元数据锁的进程 ID（假设是 PID 12345），执行 KILL 12345;。
  2. 清理空闲连接：在应用层重启服务，强制断开所有旧连接，重建连接池。
  3. 临时放宽限制：如果业务允许，临时调大 max_connections，但这只是治标不治本。

长期优化方案：

  1. 应用层优化：

    • 连接池调优：检查 Java/Go/Python 应用的连接池配置（如 HikariCP, SQLAlchemy）。确保设置了 maxLifetime（建议小于数据库的 wait_timeout），idleTimeout，以及合理的 maximumPoolSize。不要让连接池成为无底洞。
    • 优雅关闭：确保应用关闭时，正确关闭数据库连接，而不是直接杀死进程。

  2. 数据库层优化：

    • DDL 窗口期：严禁在业务高峰期执行 DDL 操作。安排在凌晨低峰期，并使用 pt-online-schema-change 或 gh-ost 等工具进行在线表结构变更，避免锁表。
    • 审计工具：引入 Percona Audit Log Plugin 或阿里云 RDS 审计功能，记录所有 DDL 操作，方便追溯。

  3. 监控告警完善：

    • 在 Grafana 中添加一个告警：当 threads_connected > 1000 时，立即发送 P1 级告警。
    • 添加一个告警：当 Innodb_row_lock_time_avg > 1000ms 时，发送 P2 级告警。

第五部分：给小朋友也能听懂的比喻

为了让你更好地向非技术同事解释这套系统的重要性，我们可以打个比方。

想象你的 MySQL 数据库是一家超级繁忙的餐厅：

  1. 慢查询日志 就像是顾客投诉信。如果一位顾客说“我的牛排等了30分钟还没好”，你就知道厨房出问题了。通过阅读投诉信，你能找出是哪道菜做得慢，是哪个厨师动作太慢。
  2. Prometheus 就像是餐厅里的监控摄像头和传感器。它实时看着大厅里有多少人在排队（连接数），厨房的火开得有多大（CPU），冰箱里的肉够不够（磁盘IO）。它不等你投诉，就在人排到门口时就拉响警报。
  3. Grafana 就像是餐厅经理的控制台大屏。上面有各种颜色的图表：绿色表示正常，红色表示危险。经理一眼就能看出“哦，今天人太多了，得赶紧叫几个临时工（扩容）或者让厨房加快速度（优化SQL）”。
  4. 连接数爆满 就像是餐厅座位满了，但客人都不走，只是在那儿发呆（Sleep状态）。新来的客人进不来，只能站在门口生气。这时候，经理需要赶紧把那些发呆的客人请出去，或者增加桌子（扩容），或者让服务员催单（优化SQL执行速度）。

有了这套系统，你不再是那个在火灾发生后拿着水桶乱泼的消防员，而是那个手持热成像仪、能在火苗刚起时就精准扑灭隐患的消防专家。

结语：监控不是目的，稳定才是

搭建 Prometheus + Grafana 监控体系，并不是为了展示你有多懂技术，而是为了在深夜被叫醒时，能从容地打开笔记本，看一眼大屏，然后淡定地说：“我知道问题在哪，给我五分钟。”

这套组合拳，从底层的慢查询日志挖掘，到中层的指标采集，再到上层的可视化呈现，构成了一个完整的闭环。它不仅能解决眼前的高并发卡顿，更能帮助你建立对数据库性能的直觉。

记住，最好的监控是预防。当你能通过趋势图预测出下周的连接数峰值，并提前申请资源或优化代码时，你就真正成为了这个系统的掌控者。

现在，去检查一下你的数据库吧。也许，那个“幽灵”卡顿的根源，就藏在某个未被注意的慢查询里。

-- 展开阅读全文 --