HDUO Lab

标签： MySQL

  • 深入 Zabbix 队列雪崩排查：Housekeeper 锁表引发的 MySQL IO 饱和与 History Syncer 堵塞实战

    排查某次监控系统大面积告警延迟事故，Zabbix Dashboard 显示待处理队列（Queue）堆积突破 20 万，告警通知 P99 延迟达到夸张的 2 小时。最终定位：业务团队滥用自定义模板，通过 log[] 键值将大段 Java 报错栈直接写入 Zabbix，导致 history_str 和 history_text 表数据暴增；同时 Zabbix 原生 Housekeeper 清理过期数据时触发海量 DELETE 操作，彻底打爆 MySQL InnoDB 的 IOPS，引发 History Syncer 进程全部夯死。解决方案极其粗暴且有效：彻底关闭 Zabbix 原生 Housekeeper，将所有历史与趋势表改造成 MySQL 按天/按月分区表（Table Partitioning），用 DROP PARTITION 替代 DELETE。

    故障现场极具讽刺意味：监控系统本身成了最需要被监控的系统。登录 Zabbix Server 节点，系统负载（Load Average）出奇的低，但查看 zabbix_server.log，满屏的红色告警：

    Zabbix server history syncer processes more than 75% busy
Zabbix server trapper processes more than 75% busy
[Z3005] query failed: [1205] Lock wait timeout exceeded; try restarting transaction [delete from history_text where itemid=19283 and clock<1698710400]

    History Syncer 是 Zabbix 将内存缓存数据刷入数据库的核心进程，它被堵死，意味着前端 Poller 和 Trapper 收集到的数据全憋在 Server 的 Shared Memory 里，最终导致采集停滞、队列爆炸。

    切到 MySQL 数据库节点，罪魁祸首立刻浮出水面。执行 iostat -dx 2 观察磁盘，数据盘的 %util 死死钉在 100%，w/s （每秒写 IO）达到磁盘物理极限，await 延迟飙升到 800ms 以上。 进入 MySQL 终端敲下 SHOW FULL PROCESSLIST;，看到几十个处于 updating 或 Locked 状态的 SQL 线程，全部是类似这样的语句：

    DELETE FROM history_str WHERE itemid=40281 AND clock < 1698710400 LIMIT 5000;
DELETE FROM history_text WHERE itemid=40282 AND clock < 1698710400 LIMIT 5000;

    技术逻辑其实非常清晰。把关系型数据库当做时序数据库（TSDB）来用，本身就是一种架构妥协。在 InnoDB 引擎中，执行 DELETE 语句删除几百万行历史数据，简直是运维自杀。DELETE 并不是简单地抹掉磁盘空间，而是会产生海量的 Undo Log（用于回滚）和 Redo Log（用于崩溃恢复），同时需要更新 B+ 树索引，引发大量的数据页分裂与 Buffer Pool 内存淘汰（Churn）。 当这种重度 IO 操作遇上业务团队滥用 Zabbix 收集文本日志（把 Zabbix 当 ELK 用），history_text 表的一行数据可能高达几 KB。Housekeeper 一启动，瞬间的随机写 IO 洪峰直接把底层存储击穿。

    为什么说这种配置不可原谅？因为在超过万级 NVPS（每秒处理新值数）的中大型 Zabbix 架构中，依赖原生 Housekeeper 清理数据是标准的新手雷区。Zabbix 官方手册虽然提过分区表的替代方案，但默认安装依然开启 Housekeeper，这坑了无数没有经历过数据量毒打的运维。

    止血与根治方案：

    第一步，紧急止损。立刻修改 zabbix_server.conf，将 Housekeeping 的频率设置为 0，切断 IO 洪峰的源头，并重启 Zabbix Server，等待队列慢慢消化。

    # zabbix_server.conf
HousekeepingFrequency=0

    第二步，架构重构，实施 MySQL 表分区（Table Partitioning）。 原理很简单：将时间序列数据按时间（clock 字段）分散到不同的物理文件中。当需要删除过期数据时，直接 ALTER TABLE ... DROP PARTITION。在文件系统层面，这等同于直接 rm -f 一个物理文件，时间复杂度为 O(1)，瞬间释放空间，彻底零 IO 负担，不会产生任何 Undo Log。

    针对 Zabbix 历史表的改造核心 SQL 如下（以 history_uint 为例）：

    -- 确保表结构没有外键，且时钟字段是主键/唯一键的一部分
ALTER TABLE history_uint PARTITION BY RANGE (clock) (
    PARTITION p20231101 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-02 00:00:00')),
    PARTITION p20231102 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-03 00:00:00')),
    -- 预先建好未来的分区
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

    配合一个定时执行的 Shell 或 Python 脚本（业内常用 zabbix-mysql-partitioning.pl 脚本），每天零点自动检查并 DROP 掉过期的历史分区，同时 ADD 未来的新分区。

    最后，强烈建议剥离 Zabbix 的文本日志收集职能。Zabbix 核心是数值型时序监控（float/uint），日志型（str/text）数据一律丢给 Filebeat + Elasticsearch 或 Promtail + Loki 去处理。在监控架构里，强行让一个工具做所有事，最后通常是什么都做不好。

    同类问题排查清单（Zabbix 性能雪崩速查）

    1. Zabbix 内部队列指标：在 Zabbix Frontend 检查 Administration -> Queue。如果延迟集中在 10 分钟以上，且大部分是 Zabbix agent (active) 或 Trapper，大概率是 Server 性能瓶颈而非网络问题。

    2. 底层数据库 IOPS 饱和度：通过 iostat -dx 1 或 Node Exporter 的 node_disk_io_time_seconds_total 指标，排查底层数据盘 %util 是否长期处于 90%+。如果是，立即停止 Zabbix Server 进程以保护 DB。

    3. Housekeeper 配置确认：检查 Zabbix Server 配置中的 HousekeepingFrequency、MaxHousekeeperDelete。大规模场景下必须置 0 关闭，改用 DB 原生表分区或直接使用 TimescaleDB/ClickHouse 作为后端。

    4. 滥用监控项审查：在 MySQL 执行 SELECT itemid, COUNT(*) FROM history_text GROUP BY itemid ORDER BY COUNT(*) DESC LIMIT 10;，揪出产生大量文本型历史数据的 Top 10 监控项（Items），并在 Zabbix 页面中直接 Disable，切断污染源。

    5. 缓存击穿指标：查看 Dashboard 中的 Zabbix cache usage, % free。如果 History index cache 或 Value cache 经常跌破 5%，说明 CacheSize 和 HistoryCacheSize 配置过小，或者存在大量低频的长周期聚合查询在刷缓存。

  • 深入 MySQL InnoDB 高并发雪崩排查：Redo Log 刷盘阻塞与 Buffer Pool 抖动引发的间隙锁死锁惨案

    高并发写入场景下，MySQL TPS 陡降甚至雪崩，根因通常是“底层 I/O 阻塞放大 + 锁冲突”。排查发现，Redo Log 空间不足引发同步刷盘等待，Buffer Pool 脏页回收跟不上导致突发 I/O 抖动。加之业务代码在长事务中执行并发插入，触发 InnoDB 间隙锁（Gap Lock）与插入意向锁的死锁风暴，最终压垮实例。核心解法：重构插入逻辑绕过间隙锁，调优 innodb_redo_log_capacity 与 innodb_io_capacity，彻底打通内核级 I/O 瓶颈。

    故障现场：一场突如其来的写入停顿

    近期在处理某核心订单系统的高并发大促压测时，数据库发生严重雪崩。监控面板上呈现出典型的“心电图式”崩溃：

    1. TPS 与 QPS 齐降：原本稳定在 6000 的 TPS，周期性跌至 100 以下，随后又缓慢爬升。

    2. 系统负载飙升：MySQL 节点 Load Average 飙破 150，CPU 的 %iowait 持续在 40% 以上震荡。

    3. 海量慢查询与死锁报错：应用侧大量爆出 Deadlock found when trying to get lock; try restarting transaction，且 99 线延迟从 20ms 飙升至 5s。

    登机直奔 MySQL 终端，执行 SHOW ENGINE INNODB STATUS\G，输出中的关键报错日志立刻暴露了底层的挣扎：

    -- 脏页刷盘告警
InnoDB: page_cleaner: 1000ms intended loop took 6540ms. The settings might not be optimal. (flushed=25000 and evicted=0, during the time.)

-- 日志等待状态
Log sequence number 14589934251
Log flushed up to   14589801020
Pages flushed up to 14581100000
Last checkpoint at  14580010000

    计算一下 Log sequence number（当前 LSN）和 Last checkpoint at（最后检查点 LSN）的差值，已经逼近了当时配置的 Redo Log 总容量。数据库实际上处于一种“憋死”的状态。

    为什么 TPS 陡增时 Redo Log 会成为整个实例的阿喀琉斯之踵？

    要搞懂这个问题，必须从 InnoDB 的 WAL（Write-Ahead Logging）机制说起。任何修改数据的操作，都会先写 Redo Log，再修改 Buffer Pool 中的数据页（脏页）。

    但在极端高并发下，Redo Log 的产生速度远超后台 Page Cleaner 线程将脏页刷入磁盘的速度。Redo Log 是循环使用的（Ring Buffer），如果脏页还没刷盘，对应的 Redo Log 空间就不能被覆盖。

    当未 Checkpoint 的 Redo Log 数据量达到了配置容量的 75%（异步刷盘水位）甚至 90%（同步刷盘水位）时，InnoDB 会触发 Sync Flush 机制。 此时，所有的用户更新线程（DML操作）将被强制挂起，由用户线程去抢占 log_sys->mutex 锁并主动触发脏页刷盘，以推进 Checkpoint LSN 腾出 Redo Log 空间。

    这就是为什么监控上的 TPS 会出现断崖式下跌。在 MySQL 终端使用以下命令可以抓到现场：

    -- 查看 Redo Log 等待次数，如果在高频增加，说明 Redo Log 容量太小
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';

    Buffer Pool 脏页风暴与 I/O 抖动原理

    Redo Log 告急只是表象，背后的帮凶往往是 Buffer Pool 刷盘策略与底层存储硬件的不匹配。

    排查过程中检查了该实例（MySQL 8.0.32，底层采用企业级 NVMe SSD）的 I/O 配置：

    innodb_io_capacity = 200
innodb_io_capacity_max = 2000

    这是极其保守的默认值。NVMe SSD 的随机写 IOPS 随随便便就能上 50,000。 因为 innodb_io_capacity 设置过低，Page Cleaner 线程在平常认为“我只需每秒刷 200 个脏页就够了”，导致 Buffer Pool 里的脏页越积越多。当 Redo Log 空间告急触发高水位强制刷盘时，InnoDB 突然要求一瞬间刷入数万个脏页，底层 I/O 瞬间飙高，引发系统抖动。

    同时，这还会导致另一个致命问题：Free buffers 耗尽。

    -- 查看请求不到空闲页被迫等待的次数
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';

    当一个查询需要加载新页到 Buffer Pool，但找不到空闲页，且 LRU 尾部的都是脏页时，查询线程就必须先等待脏页同步刷盘，导致读请求也被阻塞。读写双杀。

    间隙锁死锁：压垮骆驼的最后一根稻草

    I/O 阻塞导致了事务执行时间被动拉长。原本 10ms 能提交的事务，现在要拖到 1s 甚至 3s。事务生命周期的拉长，成倍放大了锁冲突的概率。这直接引爆了业务代码中的暗雷：Gap Lock（间隙锁）死锁。

    查看 SHOW ENGINE INNODB STATUS 中的 LATEST DETECTED DEADLOCK，发现大量类似以下的死锁日志：

    *** (1) TRANSACTION:
TRANSACTION 987654321, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 1234, OS thread handle 1403213456, query id 456789 update
INSERT INTO order_record (user_id, status) VALUES (1001, 'INIT')

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 200 n bits 72 index idx_user of table `db`.`order_record` trx id 987654321 lock_mode X locks gap before rec insert intention waiting

*** (2) TRANSACTION:
TRANSACTION 987654322, ACTIVE 2 sec inserting
...
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 100 page no 200 n bits 72 index idx_user of table `db`.`order_record` trx id 987654322 lock_mode X locks gap before rec

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 200 n bits 72 index idx_user of table `db`.`order_record` trx id 987654322 lock_mode X locks gap before rec insert intention waiting

    底层原理剖析： 在默认的 RR（Repeatable Read）隔离级别下，业务逻辑是经典的“先查后写”：

    1. SELECT * FROM order_record WHERE user_id = 1001 FOR UPDATE; (发现记录不存在)

    2. INSERT INTO order_record (user_id, status) VALUES (1001, 'INIT');

    两个并发事务 A 和 B 同时执行步骤 1：

    • 因为记录不存在，InnoDB 为了防止幻读，会沿着索引找到第一条大于 1001 的记录，并在它前面的间隙加上 Gap Lock（间隙锁）。

    • 重点：Gap Lock 相互之间是兼容的！ 事务 A 和 B 都能成功获取这个 Gap Lock。

    接着他们同时执行步骤 2（INSERT）：

    • 插入操作需要获取 Insert Intention Lock（插入意向锁）。

    • 重点：插入意向锁被 Gap Lock 排斥！

    • 事务 A 尝试获取插入意向锁，被事务 B 拥有的 Gap Lock 阻塞。

    • 事务 B 尝试获取插入意向锁，被事务 A 拥有的 Gap Lock 阻塞。

    • 死锁形成！ 数据库只能挑选一个代价较小的事务进行 Rollback。在高并发 + I/O 阻塞拉长事务的场景下，这个死锁被无限放大，最终压垮业务。

    核心调优与防御性落地策略

    排查清楚后，我们的破局思路非常清晰：解放 I/O 瓶颈，降低锁冲突，缩短事务时间。

    1. 数据库内核参数调优 (MySQL 8.0.32 环境)

    直接修改 mysqld.cnf，对核心参数进行手术刀式调整：

    # 1. 解除 Redo Log 空间瓶颈
# MySQL 8.0.30+ 引入了 innodb_redo_log_capacity 动态替代之前的 file_size 算法
# 依据经验，高并发写入库至少配置 4G-8G，避免频繁 Sync Flush
innodb_redo_log_capacity = 8589934592

# 2. 解除 Buffer Pool 刷盘限制 (匹配 NVMe SSD 性能)
innodb_io_capacity = 10000
innodb_io_capacity_max = 25000

# 3. 优化 Page Cleaner 线程，防止单线程刷盘瓶颈
innodb_page_cleaners = 8
innodb_buffer_pool_instances = 8

# 4. 降低死锁探测开销 (高并发极速短事务场景下，死锁探测自身消耗极大CPU)
# 注意：关闭前提是业务有完善的重试机制，并依赖 innodb_lock_wait_timeout 熔断
# innodb_deadlock_detect = OFF
innodb_lock_wait_timeout = 5

    注：修改配置后，可通过 SET GLOBAL 动态生效部分参数，但 innodb_buffer_pool_instances 需重启实例。

    2. 业务侧锁机制重构

    在确认业务其实不依赖 RR 级别下的间隙锁防幻读特性后（大部分电商/金融系统依赖分布式锁或唯一索引保证幂等），我们将核心交易库的隔离级别降级为 RC（Read Committed）。

    # 禁用绝大部分的 Gap Lock，大幅降低并发死锁概率
transaction_isolation = READ-COMMITTED

    在 RC 级别下，即使 SELECT ... FOR UPDATE 查不到数据，也不会加 Gap Lock，后续的并发 INSERT 就算主键冲突，也只会退化为 Unique Key 冲突报错，而不是灾难性的死锁回滚。

    常见问题 (FAQ)

    Q: 遇到 IO 瓶颈，直接把 innodb_flush_log_at_trx_commit 改成 2 可以解决问题吗？

    改为 2 确实能极大提升 TPS，因为它把 Redo Log 刷盘的动作交给了操作系统的 Page Cache（每秒 fsync 一次）。但在金融/订单等严苛场景下，主机一旦宕机/掉电，会丢失最多 1 秒的已提交事务数据。它能掩盖问题，但不能解决脏页积压引发的突发抖动，且违背了核心系统的持久性（Durability）要求。建议优先调优 Redo 容量和 I/O Capacity。

    Q: 如何精准监控 Buffer Pool 的内存污染与命中率不足？

    不要看粗略的 Hit Rate 比例，直接看内核指标：计算 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)。如果该值在业务高峰期跌破 98%，说明发生大量物理读。此时需排查是否存在无索引的大表扫描，或者是全表扫批处理任务冲刷了 LRU 链表热端数据。

    Q: 为什么把隔离级别改成了 READ COMMITTED，还会发生间隙锁死锁？

    很多研发以为 RC 完全没有 Gap Lock，这是误区。在 RC 下，如果是进行唯一索引（Unique Key）的批量插入或冲突检测（如 INSERT ... ON DUPLICATE KEY UPDATE），为了保证主键的唯一性约束，InnoDB 底层依然会隐式使用记录锁和部分间隙锁机制。碰到此类问题，必须通过分批提交、或将并发插入逻辑前置为分布式锁排队来解决。

  • 跨AZ专线抖动引发的全局雪崩：揭穿“伪双活”架构的遮羞布

    某次生产环境突发全站504报错，核心交易链路QPS从2万直降为0，监控大屏一片通红。排查结论极度低级：所谓的“同城双活”架构，仅仅是接入层和无状态计算层的双活，底层核心数据依然强依赖AZ1（可用区1）的单点主库。AZ2到AZ1的跨机房专线仅仅出现了持续约3秒、峰值200ms的延迟抖动，就直接耗尽了AZ2业务线的DB连接池；随后，全局网关层触发“无脑重试风暴”，将原本毫无问题的AZ1主库瞬间打挂，引发全局雪崩。

    解决跨机房架构问题，不从“故障域隔离”入手，光在接入层搞几个VIP负载均衡，纯属自欺欺人的PPT架构。

    现场还原：一根光纤引发的血案

    排查过程中，最直观的现象是全局入口Nginx疯狂抛出504：

    [error] 24155#0: *13444521 upstream timed out (110: Connection timed out) while reading response header from upstream...

    登录AZ2的业务容器抓取堆栈，发现大量线程处于 WAITING 状态，全部阻塞在 HikariCP 连接池获取连接上：

    "http-nio-8080-exec-15" #45 daemon prio=5 os_prio=0 tid=0x00007f8a1c0b8800 nid=0x2b waiting on condition [0x00007f89d413a000]
   java.lang.Thread.State: TIMED_WAITING (parking)
    at sun.misc.Unsafe.park(Native Method)
    at java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:188)
    ...

    进一步查看AZ1核心MySQL主库状态，平时常态下 Threads_running 只有几十，此时已经飙升到系统极限，Load Average 直接破百：

    mysql> show global status like 'Threads_running';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_running | 2548  |
+-----------------+-------+

$ uptime
 14:22:13 up 145 days,  2:11,  1 user,  load average: 184.32, 138.11, 89.45

    致命的逻辑漏洞：为什么犯错不可原谅？

    这套被吹得天花乱坠的“同城双活”架构，存在两个极其致命的设计缺陷，这也是为什么我会说它不可原谅：

    1. 掩耳盗铃的“跨机房同步写” 在双活架构设计中，最大的大忌就是跨AZ同步RPC/DB调用。 业务侧在AZ2处理请求，却要跨越几十公里的物理专线去读写AZ1的MySQL主库。光速不可变，物理专线常态延迟在 2-3ms 左右，看似很快，但只要遇到网络设备的微小抖动（丢包重传导致延迟突增至200ms+），单个请求占用数据库连接的时间就被放大了100倍。 高并发场景下，连接池（通常配置 maximumPoolSize=50）会在几百毫秒内被彻底抽干。随之而来的就是应用层线程全量阻塞，引发AZ2假死。

    2. 盲目自信的全局重试策略 如果仅仅是AZ2挂了，还不至于全站崩溃，毕竟AZ1依然存活。真正补上致命一刀的，是网关层的重试配置：

    proxy_next_upstream error timeout http_500 http_502 http_503 http_504;
proxy_next_upstream_tries 3;

    当网关发现AZ2的节点超时，它体贴地将流量全部重试到了AZ1。同时，C端用户的焦躁疯狂刷新，导致系统的实际请求量瞬间飙升了数倍。 此时的AZ1不仅要承受原有的流量，还要接管AZ2的灾备流量，外加几倍的重试洪峰。AZ1的数据库在没有做好任何限流、降级准备的情况下，瞬间被连接数打爆，彻底陷入死锁。

    真正的多活架构，核心是故障域的严格物理隔离。如果AZ2的生存强依赖于AZ1，那么它们在逻辑上依然属于同一个单点故障域。

    破局与架构纠偏

    针对这类“伪双活”架构的改造，没有捷径可走。以下是止血和根治的几个核心落地点：

    配置层面的防御性加固（快速止血）：

    • 严控连接池超时机制： 绝不允许应用无限制地等待连接。将 HikariCP 的 connectionTimeout 严格控制在 1000ms 以内，拿不到连接直接 Fast Fail，保住Tomcat/Undertow的工作线程。

    • 砍掉无意义的网关重试： 对于非幂等或高耗时的核心写接口，一律禁止在网关层做 proxy_next_upstream 重试。重试只会让本就拥堵的链路雪上加霜。

    • 引入断路器： 在微服务侧或网关侧全面接入 Resilience4j/Sentinel，当检测到目标AZ的接口处于高延迟或高失败率时，果断熔断降级。

    架构层面的重构（彻底根治）：

    • 单元化改造（Set化）： 真正的双活必须将数据层也切分。通过路由网关（如基于 UserID 哈希），将用户固定在某个AZ。AZ内形成闭环（App -> Cache -> DB 均在本AZ），AZ之间通过 DRC（如 Canal/Otter）进行底层Binlog的异步双向同步，彻底切断跨AZ的强依赖同步调用。

    💡 排查清单：跨机房/双活架构高可用速查

    1. 链路依赖闭环检查： 梳理核心链路，确认单个可用区（AZ）内部的计算、缓存、数据库调用是否形成闭环，是否存在隐藏的跨机房同步读写。

    2. 连接池超时配置审查： 检查所有服务端的数据库连接池（HikariCP/Druid）、Redis连接池（Jedis/Lettuce）以及 HTTP Client 的连接/读取超时时间，确保没有任何一项使用默认的无限期等待配置。

    3. 网关/RPC重试策略排查： 检查 Nginx/Envoy 及 Dubbo/gRPC 的重试次数配置，评估在单机房故障时，重试机制是否会引发倍数级流量放大导致雪崩。

    4. 数据库连接堆积监控： 在监控大盘强化针对 Threads_running 和 Threads_connected 的突增告警，结合网络层的跨AZ丢包率指标（Ping Loss）进行组合分析。