likes
comments
collection
share

记录一次 Online DDL 操作

作者站长头像
站长
· 阅读数 60

记录一次 Online DDL 操作

为支持用户账号删除功能,需要在 user 表上加一个字段 deleted。

一、环境

数据库:Mysql5.6

被操作表 user:数量级为100w,外键200多个

操作:alter table user add deleted boolean NOT NULL default false comment '用户注销标识' , algorithm=inplace, lock=none;

二、执行过程分析

在Mysql5.6之后,mysql支持 Online DDL 操作。

Online DDL Support for Column Operations

OperationIn PlaceRebuilds TablePermits Concurrent DMLOnly Modifies Metadata
Adding a columnYesYesYes*No
Dropping a columnYesYesYesNo
Renaming a columnYesNoYes*Yes
Reordering columnsYesYesYesNo
Setting a column default valueYesNoYesYes
Changing the column data typeNoYesNoNo
Dropping the column default valueYesNoYesYes
Changing the auto-increment valueYesNoYesNo*
Making a column NULLYesYes*YesNo
Making a column NOT NULLYes*Yes*YesNo
Modifying the definition of an ENUM or SET columnYesNoYesYes

如图所示,所执行的添加列操作整个过程为:

  • 初始化:为创建临时表的表结构,获取MDL的排他锁
  • 执行:根据参数 algorithm=inplace, lock=none ,MDL锁降级为共享锁进行数据拷贝
  • 提交:由于涉及到增量备份和临时表的重命名,MDL锁需要升级为排他锁

分析后认为,整个过程只有在初始化和提交的极短过程内需要用到MDL排他锁(影响线上),故而就直接在线上进行操作尝试。

三、遇到的问题

​ 在初始化和提交阶段需要用到MDL的排他锁,而如果DDL操作一直没获取MDL锁(默认获取MDL锁超时时间为一年),那么就会造成 Waiting for table metadata lock 状态,也会阻塞后面所有对 user 表的操作(包括select)。后面会看到连接占满,服务502:

记录一次 Online DDL 操作

在遇到这样的问题后,为不影响线上,于是后面进行了工具的尝试。

四、工具尝试

​ 工具涉及到 pt-online-schema-change、gh-ost和阿里云无锁DDL。三个工具大同小异,均为使用临时表,将原表数据拷贝到临时表,最后将临时表重命名替换掉原表。区别是在增量同步方面,一个用的触发器、一个用的binlog日志。但是在处理外键方面,pt-online-schema-change用的删除、重建外键,gh-ost和阿里云无锁DDL则是不支持主表外键的变更。eg:阿里云无锁DDL的失败尝试

记录一次 Online DDL 操作

故使用工具进行 DDL 操作也不适合。

五、Online DDL 尝试

在本地测试30w数据新增列只需440ms后,尝试选择了 online ddl 的操作:

  1. 设置 session 级别获取 MDL 锁等待时间时间,避免长时间阻塞其他线程

    $ set lock_wait_timeout=10;
    
    # 在10s内获取不到MDL锁,直接退出 alter 操作
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  2. kill 掉对应的线程以及事务

    $ select group_concat(stat separator ' ') from (select concat('kill query ',id,';') as stat from information_schema.processlist  where command != 'Sleep' and Time > 5 order by Time desc) as stats;
    
    +-------------------------------------+
    | group_concat(stat separator ' ')    |
    +-------------------------------------+
    | kill query 42510; kill query 42514; |
    +-------------------------------------+
    1 row in set (0.00 sec)
    
    
    $ select group_concat(stat separator ' ') from (select concat('kill ',trx_mysql_thread_id,';')   as stat from information_schema.innodb_trx order by trx_started desc) as stats;
    
    +-------------------------------------------------------------------------+
    | group_concat(stat separator ' ')                                        |
    +-------------------------------------------------------------------------+
    | kill 42436; kill 42435; kill 42521; kill 42511; kill 42510; kill 42483; |
    +-------------------------------------------------------------------------+
    1 row in set (0.01 sec)
  3. 执行 DDL 操作:

    $ alter table user add deleted boolean NOT NULL default false comment '用户注销标识' , algorithm=inplace, lock=none;
  4. 结果:

    记录一次 Online DDL 操作

以上是在停服后操作的结果,其中 2 操作在停服后,没有需要 kill 的 ID。

六、参考文献

InnoDB and Online DDL

转载自:https://segmentfault.com/a/1190000042107671
评论
请登录