Online DDL、pt-osc与gh-ost性能对比

除了MySQL原生的Online DDL,目前业界使用最多的改表工具主要有两款:Percona的pt-osc和GitHub的gh-ost。

1、pt-osc

工作原理:

1、如果存在外键,根据alter-foreign-keys-method参数的值,检测外键相关的表,做相应设置的处理。没有使用 –alter-foreign-keys-method=rebuild_constraints 指定特定的值,该工具不予执行。

2、创建一个和源表表结构一样的临时表(_tablename_new),执行alter修改临时表表结构。

3、在原表上创建3个于inser delete update对应的触发器。(用于copy 数据的过程中,在原表的更新操作 更新到新表)

4、从原表拷贝数据到临时表,拷贝过程中在原表进行的写操作都会更新到新建的临时表。

5、修改外键相关的子表,根据修改后的数据,修改外键关联的子表。

6、rename源数据表为old表,把新表rename为源表名,并将old表删除。

7、删除触发器。

使用限制:

  1. 表必须有主键或唯一索引
  2. 表上必须没有触发器
  3. innodb_autoinc_lock_mode必须为2,否则会严重降低MySQL性能。

2、gh-ost

gh-ost采用的是binlog+回放线程来替换掉触发器。

工作原理

如下图:

1、先连接到主库上,创建临时的ghost表,根据alter语句修改新表。

2、作为一个“备库”连接到主库(或者某一个备库上),一边在主库上拷贝已有的数据到新表,一边从主库(或者某一个备库)上拉取增量数据的binlog。

3、然后不断的把 binlog 应用回主库。

4、cut-over是最后一步,锁住主库的源表,等待binlog 应用完毕,然后替换gh-ost表为源表。

3、性能测试

使用sysbench表进行测试:

CREATE TABLE sbtest1 (

  id int(11) NOT NULL AUTO_INCREMENT,

  k int(11) NOT NULL DEFAULT ‘0’,

  c char(120) NOT NULL DEFAULT ”,

  pad char(60) NOT NULL DEFAULT ”,

  c1 varchar(100) DEFAULT NULL,

  PRIMARY KEY (id),

  KEY k_1 (k)

) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4

测试表原始数据:1000000行。

测试对比业务发生crash时的sql语句:1、重组:alter table sbtest1 engine=innodb,algorithm=inplace,lock=none;2、加字段:alter table sbtest1 add column c1 varchar(100),algorithm=inplace,lock=none;

分别测试无负载、低负载、高负载时改表性能:

  • 重组:
  • 加字段:

重组与加字段表现基本一致:

执行时间:online ddl < pt-osc < gh-ost

对业务性能影响:gh-ost < online ddl < pt-osc

4、测试小结

pt-osc

存在1节中的使用限制。由于会在原表上创建3个触发器,触发器有可能导致数据库产生死锁,存在较大风险。且三者中对业务性能影响最大。

online ddl

在高负载时会有超过10s的时间qps为0,影响业务使用:

gh-ost

由于采用单线程回放binlog,会导致高负载时一直无法追上数据更新,无法结束改表操作,降低或停止复制后即可正常结束。

测试过程中会在日志中打印一些错误,但报错不影响程序功能:

3.2、gh-ost能支持的最大qps

write qps 执行时间
2400 73
2700 128
3000 无法结束

当前场景下,测试gh-ost最大支持写qps 约2700。

发表评论

邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据