MySQL 性能监控与诊断 | 清晨的一缕阳光

核心概念

性能监控是数据库运维的基础，通过监控可以及时发现问题并优化。

监控指标体系

MySQL 监控指标：
├── 资源指标
│   ├── CPU 使用率
│   ├── 内存使用率
│   ├── 磁盘 IO
│   └── 网络 IO
├── 性能指标
│   ├── QPS/TPS
│   ├── 查询延迟
│   ├── 锁等待
│   └── 主从延迟
└── 容量指标
    ├── 数据量
    ├── 连接数
    └── 表空间

监控工具对比

工具	特点	适用场景
performance_schema	官方，详细	深度诊断
slow query log	简单，直接	慢查询分析
Prometheus + Grafana	可视化，告警	生产监控
pt-tools	命令行工具	临时诊断
Cloud 监控	云厂商提供	云上数据库

performance_schema

开启配置

[mysqld]
# 开启 performance_schema
performance_schema = ON

# 监控消费者
performance-schema-instrument = '%=ON'

# 历史记录保留时间
performance-schema-max-digest-length = 2048
performance-schema-max-sql-text-length = 4096

监控查询

-- 1. 查看等待事件
SELECT 
    event_name,
    count_star,
    sum_timer_wait,
    avg_timer_wait
FROM performance_schema.events_waits_summary_by_event_name
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- 2. 查看 SQL 执行统计
SELECT 
    digest,
    digest_text,
    count_star,
    sum_timer_wait,
    sum_rows_sent,
    sum_rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- 3. 查看表锁等待
SELECT 
    object_schema,
    object_name,
    index_name,
    lock_type,
    lock_duration,
    lock_status
FROM performance_schema.data_locks;

-- 4. 查看锁等待
SELECT 
    requesting_trx_id,
    requested_lock_id,
    blocking_trx_id,
    blocking_lock_id
FROM performance_schema.data_lock_waits;

-- 5. 查看线程状态
SELECT 
    thread_id,
    type,
    name,
    current_event_id
FROM performance_schema.threads
WHERE type = 'FOREGROUND';

性能分析

-- 查看最耗时的 SQL
SELECT 
    digest_text,
    count_star,
    sum_timer_wait / 1000000000000 AS total_time_sec,
    avg_timer_wait / 1000000000000 AS avg_time_sec,
    sum_rows_sent,
    sum_rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- 查看全表扫描的 SQL
SELECT 
    digest_text,
    sum_no_index_used,
    sum_no_good_index_used
FROM performance_schema.events_statements_summary_by_digest
WHERE sum_no_index_used > 0
ORDER BY sum_no_index_used DESC;

-- 查看临时表使用情况
SELECT 
    digest_text,
    sum_created_tmp_tables,
    sum_created_tmp_disk_tables
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_created_tmp_disk_tables DESC;

慢查询日志

配置慢查询

[mysqld]
# 开启慢查询日志
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/slow.log

# 慢查询阈值（秒）
long_query_time = 1

# 记录未使用索引的查询
log_queries_not_using_indexes = ON

# 记录管理语句
log_slow_admin_statements = ON

分析慢查询

# 查看慢查询数量
wc -l /var/lib/mysql/slow.log

# 使用 mysqldumpslow 分析
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
# -s t: 按时间排序
# -t 10: 显示前 10 条

# 使用 pt-query-digest 分析（推荐）
pt-query-digest /var/lib/mysql/slow.log

# 输出示例：
# Rank Query ID           Response time  Calls  R/Call  Item
# ==== ================= ============== ====== ======= ====
#    1 0x1234567890abcdef  100.0000 0.0100  10000 0.0100  SELECT * FROM orders

实时慢查询

-- 查看正在执行的慢查询
SHOW FULL PROCESSLIST;

-- 查看慢查询统计
SELECT * FROM mysql.slow_log;

-- 实时查询性能
SELECT 
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    info
FROM information_schema.PROCESSLIST
WHERE time > 1
ORDER BY time DESC;

监控工具

Prometheus + Grafana

# Prometheus 配置
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']
    metrics_path: /metrics
    
# mysqld_exporter 配置
# 采集指标：
# - 连接数
# - QPS/TPS
# - 慢查询
# - 锁等待
# - 主从延迟
# - Buffer Pool
# - 表空间

Grafana 面板：

1. 概览面板
   - QPS/TPS
   - 连接数
   - 慢查询数

2. 性能面板
   - 查询延迟
   - 锁等待
   - 临时表

3. 资源面板
   - CPU 使用率
   - 内存使用率
   - 磁盘 IO

4. 复制面板
   - 主从延迟
   - 复制状态

pt-tools 工具集

# pt-query-digest - 慢查询分析
pt-query-digest /var/lib/mysql/slow.log

# pt-index-usage - 索引使用分析
pt-index-usage /var/lib/mysql/slow.log

# pt-table-checksum - 数据一致性检查
pt-table-checksum --host=master --replicate=test.checksum

# pt-table-sync - 数据同步
pt-table-sync --print --sync-to-master h=slave

# pt-deadlock-logger - 死锁监控
pt-deadlock-logger --host=localhost --dest=test.deadlocks

# pt-duplicate-key-checker - 重复索引检查
pt-duplicate-key-checker --host=localhost

# pt-online-schema-change - 在线改表
pt-online-schema-change --alter "ADD INDEX idx_name (name)" D=database,t=table

自定义监控脚本

#!/bin/bash
# MySQL 监控脚本

MYSQL_HOST="localhost"
MYSQL_USER="monitor"
MYSQL_PASS="password"

# 检查连接数
conn=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW STATUS LIKE 'Threads_connected'" -N)
if [ $conn -gt 400 ]; then
    echo "WARNING: 连接数过高：$conn"
fi

# 检查慢查询
slow=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW GLOBAL STATUS LIKE 'Slow_queries'" -N)
echo "慢查询数：$slow"

# 检查主从延迟
delay=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind_Master | awk '{print $2}')
if [ "$delay" -gt 60 ]; then
    echo "WARNING: 主从延迟：${delay}s"
fi

# 检查死锁
deadlock=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks'" -N)
echo "死锁数：$deadlock"

故障诊断

诊断流程

1. 收集信息
   - SHOW STATUS
   - SHOW PROCESSLIST
   - 慢查询日志

2. 定位问题
   - CPU 高？→ 查看活跃查询
   - IO 高？→ 查看慢查询
   - 内存高？→ 查看 Buffer Pool
   - 延迟高？→ 查看锁等待

3. 分析问题
   - EXPLAIN 分析 SQL
   - 查看执行计划
   - 查看索引使用

4. 解决问题
   - 优化 SQL
   - 添加索引
   - 调整配置
   - 扩容硬件

常见问题诊断

问题 1：CPU 使用率高

-- 查看活跃查询
SHOW FULL PROCESSLIST;

-- 查看最耗时的 SQL
SELECT * FROM performance_schema.events_statements_summary_by_digest 
ORDER BY sum_timer_wait DESC LIMIT 10;

-- 解决：优化 SQL，添加索引

问题 2：查询慢

-- 查看慢查询
SELECT * FROM mysql.slow_log LIMIT 10;

-- 分析执行计划
EXPLAIN SELECT ...;

-- 解决：优化 SQL，添加索引，调整配置

问题 3：锁等待

-- 查看锁等待
SELECT * FROM performance_schema.data_lock_waits;

-- 查看长事务
SELECT * FROM information_schema.INNODB_TRX 
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

-- 解决：优化事务，缩短持有时间

问题 4：主从延迟

-- 查看复制状态
SHOW SLAVE STATUS\G

-- 查看延迟原因
-- Seconds_Behind_Master: 60

-- 解决：开启并行复制，优化大事务

最佳实践

监控配置

1. 开启慢查询日志
   long_query_time = 1

2. 开启 performance_schema
   performance_schema = ON

3. 配置 Prometheus 监控
   采集间隔：15s
   保留时间：15d

4. 配置告警
   - CPU > 90%
   - 连接数 > 80%
   - 主从延迟 > 60s
   - 慢查询 > 100/min

参考资料

MySQL 官方文档 - performance_schema
Percona Toolkit 官方文档
《高性能 MySQL》第 8 章：MySQL 服务器性能剖析