Galera Cluster全节点因更新查询变慢及连接过多问题排查

1. Total Order Isolation (TOI) 模式下DDL的集群级阻塞 #

你使用的TOI是Galera默认的DDL复制策略，该模式会在所有集群节点上同步执行DDL操作，执行期间全局暂停写操作，等待DDL在所有节点完成：

• 你的CREATE TABLE item_temp AS SELECT COUNT(*) FROM item WHERE item_status = 1;语句需要在3个t3.medium节点上分别扫描220万行的item表计算统计值，瞬间耗尽节点的CPU、IO和内存资源。
• 此时所有节点已处于高负载状态，后续操作的响应能力大幅下降。

2. 错误UPDATE操作的双重负面影响 #

你执行的UPDATE item SET item_status = 3 where id = (select id from item_temp);存在致命问题：

• 首先，item_temp表通过SELECT COUNT(*)创建，仅包含一行统计值，不存在id列，子查询会触发字段不存在的错误；即使是笔误（比如实际用SELECT id创建临时表），=运算符匹配多行子查询结果也会触发语法错误，导致事务异常。
• 其次，Galera会将这个异常事务同步到所有节点，每个节点都要处理错误事务的回滚或重试逻辑，进一步加剧已高负载节点的资源消耗，CPU、IO使用率持续飙升。

3. GTID冲突引发的复制阻塞与资源耗尽 #

节点C的日志明确显示GTID序列冲突：

2024-03-15 13:22:34 8 [ERROR] Slave SQL: Could not execute Write_rows_v1 event on table test.item_temp; An attempt was made to binlog GTID 0-1-117195197 which would create an out-of-order sequence number with existing GTID 0-1-117195197, and gtid strict mode is enabled...

• 高负载下节点的binlog写入出现异常，导致同一GTID被重复生成；开启gtid_strict_mode后，Galera拒绝执行冲突的GTID事件，复制进程进入无限重试循环。
• 持续的复制重试占用大量CPU和IO资源，同时未完成的事务持有连接，最终导致连接数耗尽，触发Too many connections错误，新连接无法建立，甚至MariaDB进程因资源耗尽崩溃。

4. 连锁反应的最终结果 #

• 节点A进程终止后重启失败：启动时需要恢复未完成的事务或修复损坏的binlog/relaylog，但节点资源仍处于紧张状态，无法完成启动流程。
• 重启所有节点后更新未生效：Galera的一致性协议保证未成功提交到全局集群状态的事务不会被持久化，异常的UPDATE事务未完成同步，因此不会生效。

内容的提问来源于stack exchange，提问作者samsamsamsmasma