MySQL 8.0高流量后DDL触发'committing alter table'等待超时求助

问题背景 #

在RDS MySQL 8.0.27版本中，对高流量插入（每秒600条，持续数小时）后的分区表执行DROP PARTITION或REORGANIZE PARTITION操作时，committing alter table to storage engine等待事件耗时显著增加（例如空分区删除耗时18秒），期间所有会话阻塞在Waiting for table metadata lock导致服务中断。但首次DDL后立即执行同类操作仅耗时毫秒级，MySQL 5.7无此问题。

验证测试细节 #

1. 创建分区表big_table：

CREATE TABLE `big_table` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(1000) DEFAULT NULL,
  `c2` varchar(1000) DEFAULT NULL,
  `c3` text,
  `c4` text,
  `c5` text,
  `c6` text,
  `c7` text,
  `c8` text,
  `c9` text,
  `created_day` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`id`,`created_day`),
  KEY `c1_index` (`c1`,`created_day`),
  KEY `c2_index` (`c2`,`created_day`)
) ENGINE=InnoDB AUTO_INCREMENT=124732836 DEFAULT CHARSET=utf8mb3
/*!50500 PARTITION BY RANGE  COLUMNS(created_day)
(PARTITION p20220906 VALUES LESS THAN ('2022-09-06') ENGINE = InnoDB,
 PARTITION p20220907 VALUES LESS THAN ('2022-09-07') ENGINE = InnoDB,
 PARTITION p20220908 VALUES LESS THAN ('2022-09-08') ENGINE = InnoDB,
 PARTITION p20220909 VALUES LESS THAN ('2022-09-09') ENGINE = InnoDB,
 PARTITION p20220910 VALUES LESS THAN ('2022-09-10') ENGINE = InnoDB,
 PARTITION p20220911 VALUES LESS THAN ('2022-09-11') ENGINE = InnoDB,
 PARTITION p20220912 VALUES LESS THAN ('2022-09-12') ENGINE = InnoDB,
 PARTITION p20220913 VALUES LESS THAN ('2022-09-13') ENGINE = InnoDB,
 PARTITION p20220914 VALUES LESS THAN ('2022-09-14') ENGINE = InnoDB,
 PARTITION p20220915 VALUES LESS THAN ('2022-09-15') ENGINE = InnoDB,
 PARTITION p20220916 VALUES LESS THAN ('2022-09-16') ENGINE = InnoDB,
 PARTITION pMax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

2. 通过200线程插入10小时，累计约1000万条记录，停止插入后执行空分区删除：

mysql> alter table big_table drop partition p20220904; 
Query OK, 0 rows affected (18.16 sec) Records: 0 Duplicates: 0  Warnings: 0

3. 执行期间可见大量I/O及fsync操作，再次执行同类DDL仅耗时0.21秒。

原因分析 #

MySQL 8.0对分区DDL的实现逻辑做了变更：执行DROP/EXCHANGE PARTITION时，会触发buf_flush_or_remove_pages()流程，需要扫描并刷盘该表在缓冲池中的所有脏页，同时检查flush_list，期间会持有元数据锁阻塞所有读写操作。高流量插入后缓冲池中累积了大量该表的脏页，首次DDL需要完成全量刷盘，耗时极长；刷盘完成后缓冲池中脏页减少，后续DDL耗时恢复正常。

解决方案 #

1. 提前主动刷脏页，降低DDL时的刷盘压力 #

在执行分区维护DDL前，主动触发该表的脏页刷盘，避免DDL期间的全量阻塞刷盘：

• 使用ALTER TABLE ... FORCE（该命令会触发表的脏页刷盘，锁粒度较低）：

ALTER TABLE big_table FORCE;

• 临时调整InnoDB刷盘参数，加快后台脏页清理：
  • 若使用SSD，将innodb_flush_neighbors设为0（减少不必要的邻页刷盘）：

  SET GLOBAL innodb_flush_neighbors = 0;
  

  • 调大innodb_max_dirty_pages_pct_lwm（例如从10%调到20%），让后台更早开始刷脏页：

  SET GLOBAL innodb_max_dirty_pages_pct_lwm = 20;
  

  注意：参数调整需结合业务场景，避免影响写入性能。

2. 升级MySQL版本 #

该问题在MySQL 8.0.30及以后版本中已得到优化，官方调整了分区DDL时的脏页处理逻辑，不再强制刷盘全量脏页，仅处理与目标分区相关的页。如果RDS支持升级，优先考虑升级到8.0.30+版本。

3. 调整分区维护策略 #

• 选择业务低峰期执行分区DDL，减少阻塞影响；
• 拆分分区维护操作，避免一次性处理大量分区；
• 采用EXCHANGE PARTITION替代直接删除分区：先将目标分区交换到临时表，再删除临时表，锁粒度更低，刷盘压力更小：

-- 创建与原表结构一致的临时表
CREATE TABLE big_table_temp LIKE big_table;
-- 交换分区
ALTER TABLE big_table EXCHANGE PARTITION p20220904 WITH TABLE big_table_temp;
-- 删除临时表
DROP TABLE big_table_temp;

4. 优化缓冲池配置 #

• 适当调大innodb_buffer_pool_size，降低脏页占比；
• 若业务对数据一致性要求稍低，可调整innodb_flush_log_at_trx_commit为2，降低日志刷盘压力（此操作需谨慎评估数据丢失风险）。

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