MySQL 高负载 I/O 故障全链路分析与优化案例-智慧文博士

📚 MySQL 高负载 I/O 故障全链路分析与优化指南

场景背景：
数据库：MySQL 5.7.39（InnoDB）
存储：LVM 逻辑卷（dm-0为根分区/）
问题现象：
临时表报错The table '/tmp/...' is full
InnoDB 日志频繁出现page_cleaner: 1000ms intended loop took XXXXms
半同步复制超时并自动关闭
从库 mysqld 进程神秘重启

一、问题根源定位：I/O 瓶颈在`dm-0`（根分区）

✅`iostat`是测什么的？

主要用途：监控磁盘（块设备）I/O 性能
附带信息：CPU 使用率（含%iowait）
关键指标：
- %util = 100%→ 设备饱和，I/O 请求排队
- await > 8ms→ 响应延迟高
- %iowait ≈ 10%→ CPU 被磁盘拖累

🔍 结论：瓶颈在磁盘，不在 CPU 计算能力

✅`dm-0`是什么？

是Linux device-mapper设备，通常由LVM（逻辑卷管理器）创建

通过以下命令确认其身份：

ls-l /dev/mapper/# 查看符号链接df-h /# 确认是否挂载为根分区

你的环境：dm-0= 根分区/，意味着：
- 操作系统、MySQL 数据、日志、/tmp全部共用同一 I/O 资源池

💥 后果：任何高 I/O 操作（如临时表、刷脏页）都会导致全系统卡顿

二、MySQL 层面的症状与调优

🔥 症状 1：临时表写满`/tmp`

原因：/tmp在根分区，空间不足 + I/O 拥塞

解决方案：

[mysqld] tmpdir = /data/tmp # 迁移到独立大容量分区 tmp_table_size = 512M max_heap_table_size = 512M # 尽量用内存临时表

🔥 症状 2：InnoDB page_cleaner 延迟

原因：脏页集中刷写，I/O 能力不足

优化配置：

innodb_io_capacity = 500 # SSD 建议 200~2000 innodb_io_capacity_max = 2000 innodb_max_dirty_pages_pct = 60 # 提前开始刷写 innodb_adaptive_flushing = ON # 启用自适应刷写 innodb_log_file_size = 2G # 减少 checkpoint 频率 innodb_flush_neighbors = 0 # SSD 关闭邻近页刷新

三、复制问题：半同步超时 ≠ 重启原因

❓ 半同步复制关闭会导致 MySQL 重启吗？

答案：不会！
真实机制：
- 主库等待从库 ACK 超时（默认 10ms）
- 自动降级为异步复制（Semi-sync switched OFF）
- 这是保护机制，避免主库阻塞

⚠️ 但为什么从库真的重启了？

根本原因：操作系统强制杀死 mysqld 进程
- 最常见：OOM Killer（内存耗尽）
- 次常见：外部 HA 工具干预、硬件故障

四、如何定位“神秘重启”？—— 超越 error.log

仅看mysql_error.log不够！必须检查系统级日志：

✅ 1. 检查 OOM Killer

dmesg-T|grep-i"killed process"grep-i"oom"/var/log/messages

→ 若有Kill process ... (mysqld)，即为内存溢出。

✅ 2. 检查 systemd 状态

systemctl status mysqld journalctl -u mysqld --since"2026-01-08 16:00"

→ 若显示code=killed, status=9/KILL，确认被 SIGKILL 终止。

✅ 3. 分析重启前负载

检查慢查询日志：是否有大事务？
检查SHOW SLAVE STATUS：SQL 线程是否卡在某条语句？

五、终极解决方案：架构 + 配置双管齐下

🏗️ 架构层面（推荐）

组件	建议位置
操作系统（/）	原 LVM（`dm-0`）
MySQL 数据	独立 SSD + 新 LV（如`/dev/vgdata/lv_mysql`）
binlog / redo	高速盘（可与数据同盘）
tmpdir	大容量分区（如`/data/tmp`）

⚙️ 配置层面

[mysqld] # I/O 能力匹配 innodb_io_capacity = 1000 innodb_io_capacity_max = 2000 # 平滑刷写 innodb_max_dirty_pages_pct = 60 innodb_adaptive_flushing = ON # 临时表优化 tmpdir = /data/tmp tmp_table_size = 512M max_heap_table_size = 512M # 半同步容错 rpl_semi_sync_master_timeout = 5000000 # 5秒