1 事故总结

时间: 15-Aug-2023 9PM
操作：DBOps 执行了两个增加 partition 的 DDL操作，其中一个执行成功，另一个执行失败。
影响: RDS 的 master replica CB open 30s

2 背景

• 我们的数据库有两个主要的订单表orders1和orders2，这两个表在创建时，均使用了以创建时间作为了分区：

CREATE TABLE `orders1` (
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `order_id` varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `order_type` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `order_source` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `order_state` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `payment_method` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `fare` int(11) NOT NULL,
  `exponent` int(11) NOT NULL,
  `city_id` int(11) NOT NULL,
  `sender_id` int(11) NOT NULL,
  `driver_id` int(11) NOT NULL,
  `metadata` blob NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
 
  PRIMARY KEY (`id`, `created_at`),
 
  KEY `index_order_id` (`order_id`),
  KEY `index_state_created_at` (`order_state`,`created_at`),
  KEY `index_payment_method` (`payment_method`),
  KEY `index_created_at` (`created_at`),
  KEY `index_updated_at` (`updated_at`),
  KEY `index_sender_id_created_at` (`sender_id`,`created_at`),
  KEY `index_driver_id_created_at` (`driver_id`,`created_at`)
 
) ENGINE=InnoDB AUTO_INCREMENT=87805939 DEFAULT CHARSET=utf8
PARTITION BY RANGE (unix_timestamp(`created_at`)) ( // 每个月作为一个分区，
        PARTITION p_05_2020 VALUES LESS THAN (unix_timestamp( 2020-06-01 )),
        PARTITION p_06_2020 VALUES LESS THAN (unix_timestamp( 2020-07-01 )),
        PARTITION p_07_2020 VALUES LESS THAN (unix_timestamp( 2020-08-01 )),
       ...
        PARTITION p_08_2023 VALUES LESS THAN (unix_timestamp( 2023-09-01 )),
        PARTITION p_09_2023 VALUES LESS THAN (unix_timestamp( 2023-10-01 )),
        PARTITION p_10_2023 VALUES LESS THAN (unix_timestamp( 2023-11-01 )),
        PARTITION p_max VALUES LESS THAN MAXVALUE
        );

• 在order 表创建时，我们的分区创建到了p_10_2023，根据现有的逻辑，当时间来到2023 年 11 月后，新的订单都会储存到 p_max 分区
• 八月1 日后，由于空分区数 <= 3 （只有p_09_2023, p_10_2023和p_max了），我们收到了 DBOps的 alert，所以需要手动添加新分区

3 方案

• 对于orders1和orders2表，我们需要提交 Alter table DDL (Data Definition Languages), 对现有的 p_max 进行repartition

ALTER TABLE orders1
REORGANIZE PARTITION p_max INTO (
PARTITION p_01_2025 VALUES LESS THAN (unix_timestamp( 2025-02-01 )),
PARTITION p_02_2025 VALUES LESS THAN (unix_timestamp( 2025-03-01 )),
PARTITION p_03_2025 VALUES LESS THAN (unix_timestamp( 2025-04-01 )),
PARTITION p_04_2025 VALUES LESS THAN (unix_timestamp( 2025-05-01 )),
PARTITION p_05_2025 VALUES LESS THAN (unix_timestamp( 2025-06-01 )),
PARTITION p_06_2025 VALUES LESS THAN (unix_timestamp( 2025-07-01 )),
PARTITION p_07_2025 VALUES LESS THAN (unix_timestamp( 2025-08-01 )),
PARTITION p_08_2025 VALUES LESS THAN (unix_timestamp( 2025-09-01 )),
PARTITION p_09_2025 VALUES LESS THAN (unix_timestamp( 2025-10-01 )),
PARTITION p_10_2025 VALUES LESS THAN (unix_timestamp( 2025-11-01 )),
PARTITION p_11_2025 VALUES LESS THAN (unix_timestamp( 2025-12-01 )),
PARTITION p_12_2025 VALUES LESS THAN (unix_timestamp( 2026-01-01 )),
PARTITION p_max VALUES LESS THAN (MAXVALUE));

• 由于我司提供的 DDL 工具有table size限制，orders1表大小超过了 500G，所以只能使用 direct DML， 而 orders2表默认使用 pt-osc工具。

4 DDL 执行效果及分析

4.1 使用 direct DML 方式为 orders1 表增加分区

Direct DML 可以理解为直接修改 master replica 的表结构，在 MySQL 8.0版本中，我司默认使用 inplace的方法，inplace 算法的操作阶段主要分为三个：

  1. Prepare阶段：
      - 创建新的临时 frm 文件(与 InnoDB 无关)。
      - 持有 EXCLUSIVE-MDL 锁，禁止读写。
      - 根据 alter 类型，确定执行方式（copy，online-rebuild，online-not-rebuild）
  2. Execute阶段：
      - 降级EXCLUSIVE-MDL锁，允许读写。
      - 扫描old_table聚集索引（主键）中的每一条记录 rec。
      - 遍历new_table的聚集索引和二级索引，逐一处理。
      - 根据 rec 构造对应的索引项。
      - 将构造索引项插入 sort_buffer 块排序。
      - 将 sort_buffer 块更新到 new_table 的索引上。
      - 记录 online-ddl 执行过程中产生的增量（仅 rebuild 类型需要）。
      - 重放 row_log 中的操作到 new_table 的索引上（not-rebuild 数据是在原表上更新）。
      - 重放 row_log 中的DML操作到 new_table 的数据行上。
  3. Commit阶段：
      - 当前 Block 为 row_log 最后一个时，禁止读写，升级到 EXCLUSIVE-MDL 锁。
      - 重做 row_log 中最后一部分增量。
      - 更新 innodb 的数据字典表。
      - 提交事务（刷事务的 redo 日志）。
      - 修改统计信息。
      - rename 临时 ibd 文件，frm文件。
      - 变更完成，释放 EXCLUSIVE-MDL 锁。

执行效果

• 这个DDL执行过程中一共花了30s，主要是prepare阶段时间较长，而剩下的两个阶段一共花了不到1s。
• 在DDL执行过程中，短暂的造成了我们的orders1表CB open，导致线上业务与这个表有关的读写操作均不可用

Root Cause Analysis:

• 对于inplace算法, 在prepare和 commit 阶段持有 MDL_EXCLUSIVE 锁，禁止读写
• 由于在运行 DDL 的时候，该表存在长事务，所以导致该 DDL 被 block 在 prepare 阶段
• 由于后续的读写操作，会持有 MDL_SHARED 锁，与 MDL_EXCLUSIVE 锁 互斥，所以导致后续的读写操作均timeout
• 由于主从同步，从库也会收到影响, 所以总体上的 DB CB open 的时间(downtime)会大于 30s

关于MDL Lock:

• 全称是 Metadata Data Lock,元数据锁
• MDL出现的初衷就是为了保护一个处于事务中的表的结构不被修改
• MDL是事务级别的，只有在事务结束后才会释放。
• MDL 锁的类型 (为了提高并发度)

- MDL_INTENTION_EXCLUSIVE
- MDL_SHARED
- MDL_SHARED_HIGH_PRIO
- MDL_SHARED_READ
- MDL_SHARED_WRITE
- MDL_SHARED_WRITE_LOW_PRIO
- MDL_SHARED_UPGRADABLE
- MDL_SHARED_READ_ONLY
- MDL_SHARED_NO_WRITE
- MDL_SHARED_NO_READ_WRITE
- MDL_EXCLUSIVE （独占锁，在其持有期间是不允许其它类型的MDL被授予，自然也包括SELECT和DML操作）

4.2 使用 pt-osc 工具为 orders2 表增加分区

由于 orders2 表大小不到 500G，我们采用了默认的 pt-osc (pt online schema change) 工具执行 DDL

pt-osc

• 创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)
• 在新表执行alter table 语句（速度应该很快）
• 在原表中创建触发器3个触发器分别对应insert,update,delete操作
• 以必定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表（会限制每次拷贝数据的行数以保证拷贝不会过多消耗服务器资源，采用 LOCK IN SHARE MODE 来获取要拷贝数据段的最新数据并对数据加共享锁阻止其他会话修改数据，不过每次加S锁的行数不多，很快就会被释放）
• Rename 原表到old表中，在把临时表Rename为原表（整个过程只在rename表的时间会锁一下表，其他时候不锁表）
• 如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理
  默认最后将旧原表删除

执行效果

• 在copy 原数据到临时表的阶段，由于 thread_running > threshold,

2023-08-15T13:03:08 Error copying rows from `XXX`.`orders2` to `XXX`.`orders2`: Threads_running=231 exceeds its critical threshold 200

关于 thread running

• 每秒查询次数（Queries per second，QPS）可以衡量数据库的吞吐量，但不能反映MySQL的工作强度
• Thread running 是判断当前数据库负载的绝佳指标

5 Lesson Learnt

• DDL 的执行，是可能对当前 DB造成影响的（本次事故中，我们的 DDL 执行造成了数据库 30s的downtime）
• 所有的 DDL 执行，都应该放到 DB qps 较低的时间（列如凌晨）