正文

从慢查询日志到实时指标全掌握MySQL性能监控工具实战避坑指南

/0 浏览量
0910

记得几年前，我接手过一个“半夜报警叫起来查库”的项目。业务跑得欢，数据库却像老了十岁的老人，喘着粗气。那时候我最头疼的不是怎么写SQL，而是怎么知道哪里卡了。是慢查询？是连接数爆满？还是锁竞争？

今天这篇指南，我不跟你讲枯燥的理论，咱们直接从实战出发，把MySQL性能监控的工具链拆碎了揉烂了，顺便把那些深坑给你填平。

一、 先别急着装Agent，先搞懂MySQL自己给你的“黑匣子”

很多新手一上来就装Prometheus、Grafana、Percona Monitoring and Management (PMM)。停！如果连MySQL自带的日志都看不懂，上面的工具来了你也只会看个热闹。

1. 慢查询日志 (Slow Query Log)：最原始也最可靠的朋友

这是你排查问题的第一站。它记录了所有执行时间超过阈值的SQL。

避坑指南：

  • 阈值别设太低：默认是10秒。很多团队把它改成0.1秒甚至更低，结果日志量爆炸，磁盘IO直接被日志写满，反而拖慢系统。建议：生产环境保持在1秒左右，或者根据业务SLA设定。
  • log_queries_not_using_indexes：这个开关一定要开！它记录那些没走索引的查询，哪怕很快。这是发现烂SQL的宝贝。
  • 使用 pt-query-digest：别直接看原始日志，用Percona Toolkit里的这个神器。它能聚合相似的SQL，找出TOP N的耗时查询。
# 简单示例：分析慢查询日志
pt-query-digest /var/log/mysql/slow.log

它会直接告诉你：“这个SELECT语句占了总查询时间的40%，平均耗时2秒，建议加索引。”

2. Performance Schema：MySQL的“内脏透视”

从MySQL 5.5开始引入的，专门用来诊断性能问题。它比SHOW STATUS更细致，能告诉你谁在等什么。

核心表：

  • events_waits_current：当前正在等待的事件（比如等锁、等IO）。
  • events_statements_summary_by_digest：按SQL摘要统计的执行次数、耗时。

实战技巧： 想看最近10秒内哪条SQL最慢？

SELECT 
    DIGEST_TEXT AS 'SQL模式',
    COUNT_STAR AS '执行次数',
    ROUND(AVG_TIMER_WAIT/1000000000000, 2) AS '平均耗时(秒)',
    ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS '总耗时(秒)'
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

避坑：Performance Schema本身有开销，生产环境不要开太细的采样率，默认足够。

3. Sys Schema：Performance Schema的“人话版”

直接查Performance Schema的表？那是在折磨自己。MySQL 5.7+内置了sys库，里面全是视图，结果直观多了。

-- 查看最耗时的SQL
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile
LIMIT 10;

-- 查看有Full Table Scan的SQL
SELECT * FROM sys.statements_with_full_table_scans;

专家建议：日常运维，先查sys库，查不出问题再深入performance_schema。

二、 实时指标监控：让数据“活”起来

慢查询日志是事后诸葛亮，你需要的是实时监控。当CPU飙升时，你能立刻知道发生了什么。

1. 核心监控指标（必须盯住的）

别盯着CPU使用率发呆，要看具体为什么CPU高。

指标 正常范围 异常含义
Threads_connected < 最大连接数的70% 连接池耗尽，新请求排队
Threads_running < CPU核心数 * 2 大量并发，可能锁竞争或复杂计算
Innodb_buffer_pool_usage > 95% 缓冲池命中率低，磁盘IO激增
Binlog_cache_disk_use 0 或极低 大事务导致Binlog缓存撑不住，刷磁盘
Handler_read_rnd_next 低 可能在做全表扫描或排序

避坑：Threads_running高不等于慢。如果是短查询，高并发是好事。要看QPS/TPS和平均响应时间的组合。

2. 工具选型：Prometheus + Grafana 还是 PMM？

方案A：Prometheus + Grafana（灵活，生态好）

适合有运维能力的团队，可以自定义监控，集成到现有的K8s体系。

  • Exporter：mysqld_exporter（官方或Percona出品）
  • 步骤：
    1. 部署mysqld_exporter，配置DATA_SOURCE_NAME连接MySQL。
    2. Prometheus抓取指标。
    3. Grafana导入模板（如ID 7362）。
# prometheus.yml 示例配置
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['mysql-exporter:9104']

方案B：Percona Monitoring and Management (PMM)（开箱即用）

如果不想折腾，PMM是最省心的选择。它自带QAN（Query Analytics），能关联慢查询和实时指标。

PMM的杀手锏：

  • QAN Free Text Search：你可以搜索慢查询日志中的SQL，然后直接看到那条SQL执行时的CPU、IO、锁等待情况。
  • 拓扑图：自动发现MySQL主从、Proxy、应用服务器关系。

避坑：PMM Server本身资源占用不低，建议单独部署在轻量级虚拟机或容器里，别和业务数据库在一起。

3. 监控告警：别做狼来了

告警太多会被忽略。设定合理的阈值和静默期。

  • CPU > 80% 持续5分钟：告警。
  • 慢查询数每分钟 > 10：告警。
  • 主从延迟 > 10秒：告警。
  • 连接数使用率 > 85%：预警，> 95%：紧急告警。

三、 深层诊断：当监控发现异常，下一步做什么？

监控告诉你“出事了”，诊断工具告诉你“为什么”。

1. 锁等待诊断：谁锁了谁？

高并发下，锁是性能杀手。

-- 查看当前正在等待锁的会话
SELECT * FROM information_schema.INNODB_TRX;

-- 查看锁等待链（MySQL 8.0+）
SELECT * FROM performance_schema.data_lock_waits;

实战技巧：发现死锁，立刻查SHOW ENGINE INNODB STATUS\G，里面会打印最新的死锁详情，包括两个事务的SQL和持有的锁。

2. IO瓶颈定位

MySQL是IO密集型应用。iostat -x 1 1看磁盘，iotop -o看哪个进程在写盘。

关键指标：

  • await：平均每次IO操作等待时间，超过20ms就要警惕。
  • %util：磁盘利用率，长期100%说明IO饱和。

避坑：云数据库（如RDS、Cassandra）通常有IO配额限制，await突然升高可能是触发了云厂商的IO限速，而非SQL问题。

3. 连接池问题：应用侧 vs DB侧

很多性能问题不在MySQL，而在应用。

  • 应用侧：检查HikariCP、Druid等连接池配置。maximum-pool-size是否合理？max-lifetime是否避免长连接？
  • DB侧：SHOW PROCESSLIST看每个连接的执行状态。如果看到大量Sending data或Sorting result，说明SQL有问题。

代码示例：HikariCP配置建议

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("user");
config.setPassword("pass");
// 连接池大小：CPU核心数 * 2 + 磁盘数（简单经验公式）
config.setMaximumPoolSize(Runtime.getRuntime().availableProcessors() * 2);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);
config.setConnectionTimeout(30000);

四、 实战避坑：那些年我踩过的雷

坑1：监控自己成了性能瓶颈

部署了PMM或Prometheus后，发现数据库变慢了。 原因：information_schema和performance_schema在某些版本中查询开销大，如果监控工具频繁查询这些表，会拖慢业务。 解决：

  • 降低采集频率（如从5秒改为30秒）。
  • 使用sys库视图，而非直接查底层表。
  • 考虑使用mysql_exporter的只读权限，限制其查询复杂度。

坑2：只看平均值，忽略长尾

QPS正常，平均响应时间10ms，但用户投诉慢。 原因：10ms是平均值，可能有90%的请求1ms，但10%的请求1秒。 解决：监控P99、P95延迟，而非平均值。Grafana中用histogram_quantile函数计算分位数。

坑3：索引监控的误区

看到Handler_read_next高就加索引？ 原因：read_next高可能只是正常的主键顺序扫描，比如报表全表统计。 解决：结合Rows_examined和Rows_sent判断。如果Rows_examined远大于Rows_sent，才需要优化。

坑4：忽视Binlog和Redo Log

磁盘IO高，查业务SQL没毛病，但CPU不高。 原因：可能是刷Binlog或Redo Log太频繁。 解决：监控Innodb_os_log_written和Innodb_data_fsyncs。如果刷新频繁，考虑调整innodb_flush_log_at_trx_commit（从1改为2，有少量数据丢失风险但性能提升显著）。

五、 一套可落地的监控体系建议

如果你从零开始搭建，按这个顺序来：

  1. 基础层：部署mysqld_exporter，接入Prometheus，监控基础指标（QPS、TPS、连接数、缓冲池命中率）。
  2. 日志层：开启慢查询日志，配置pt-query-digest定期分析，生成报告。
  3. 可视化层：Grafana导入MySQL模板，自定义Dashboard，重点关注P99延迟和慢查询趋势。
  4. 告警层：配置Alertmanager，阈值告警+严重告警分级推送（钉钉/企业微信/邮件）。
  5. 进阶层：引入PMM或SkyWalking，进行Query Analytics和分布式链路追踪。

结语

MySQL性能监控不是一次性的工作，而是一个持续优化的过程。工具只是眼睛，经验才是大脑。

记住，最好的监控是让数据库“透明化”——你不需要盯着屏幕，异常时自动找你。但当异常发生时，你能在3分钟内定位到是慢查询、锁竞争、还是硬件瓶颈，这才是真正的掌控。

希望这份指南能帮你少走弯路。如果你的数据库现在正疼着，先从慢查询日志和sys库查起，那里往往藏着答案。

-- 展开阅读全文 --