注册 登录
  • 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

使用pt-online-schema-change在线修改MySQL表结构

MySQL Tools 彭东稳 8685次浏览 已收录 0个评论

一、背景

MySQL大字段的DDL操作:加减字段、索引、修改字段属性等,在MySQL 5.1之前都是非常耗时耗力的,特别是会对MySQL服务产生影响。在MySQL 5.1之后随着Plugin Innodb的出现在线加索引的提高了很多,但是还会影响(时间缩短了),主要是在更改期间,会生成一个互斥锁,阻塞对整个表的所有操作。不过MySQL 5.6版本开始已经支持部分DDL在线操作了,但也是问题很大的。那如何在不锁表的情况下安全快速地更新表结构?现在来介绍下percona-toolkit的pt-online-schema-change(简称:OSC)的使用说明,可以很好的解决上述的问题,由于是依靠触发器来工作的,也是有一定的风险,比如死锁及数据丢失情况。如果你对OSC足够了解,其实也是可以避免上述问题的。

在我们的以前做法中,为了不影响线上业务,我们一般采用:先在线下从库更改表结构,然后替换线上从库,这样一台台的修改,最后做一下主库切换,这个过程会耗费很长时间,并且在做主库切换时,风险也非常的大,我们怎样才能让时间更短,且能不阻塞读写情况下在线修改呢?早在2008年Shlomi Noach就利用触发器的原理,开发了python版本“oak-online-alter-table”在线更改表结构脚本。最近,Percona公司在自己的percona-toolkit工具箱中也发布了在线更改表结构的Perl版本脚本“pt-online-schema-change”,另外Facebook公司也开发自己的在线更改表结构PHP版本脚本“OnlineSchemaChange.php”,而它们最底层的实现原理都为触发器。因为oak-online-alter-table不确定是否在被开发与支持,而OnlineSchemaChange.php的使用比较复杂,且有很多功能不支持如:表结构中如果有外键的情况,故在此我就Percona公司的“pt-online-schema-change”脚本做详细的剖析。

另外也极力推荐尝试另一款在线更改表结构工具“Gh-ost”,Gh-ost是Github的DBA在2016年8月开源的一款使用Go语言开发的MySQL在线改表工具,解决了目前采用pt-online-schema-change遇到的一些问题,思路也很新颖,没有像前面几款工具使用触发器来解决在线该表问题,而是采用了binlog方式,具体可以参考“gh-ost解析”。作者Shlomi Noach很厉害,同时也是openark kit工具集的作者(主要是用Python写的一套工具集,其中就包括我们上面说的oak-online-alter-table工具)。

二、OSC实现原理

pt-online-schema-change在线更改表结构的实现核心有如下几个过程:(注:在跟改过程中涉及到三个表:原表、tmp_table即作为原表导数据的临时表,old_table在最后rename原表的结果表)

第一步:新建tmp_table,表结构同原表。

第二步:在tmp_table上更改表结构为需要的表结构。

第三步:在原表上创建三个触发器,以便后续原表上的操作同步到新表。

我们可以看到这三个触发器分别对应于INSERT、UPDATE、DELETE三种操作,下面详细解释三个触发器的作用:

  • pt_osc_test_online_table_del

DELETE操作,我们注意到DELETE IGNORE关键字,也就是说只有当新表有新数据时,我们才进行删除操作,反之亦然。为什么需要这样呢?当在后续旧表数据同步新表过程中,如果要删除的这个数据还未被导入到新表,那么我们可以不在新表执行该删除操作,因为在以后的导入过程中,原表中的该行数据已经被删除,已经没有数据,那么它也就不会导入到新表中。

  • pt_osc_test_online_table_ins

INSERT操作,所有的INSERT INTO全部转换为REPLACE INTO,为了确保数据的一致性。当有新数据插入到原表时,如果在触发器还未把这条数据同步到新表时,这条数据已经被导入到新表了,那么我们就可以利用INSERT INTO进行覆盖,这样数据也是一致的。

  • pt_osc_test_online_table_upd

UPDATE操作,所有的UPDATE也转换为REPLACE INTO。因为当新的数据的行还未同步到新表时,新表是不存在这条记录的,那么我们就只能插入该条数据,如果已经同步到新表了,那么也可以进行覆盖插入,所有数据与原表也是一致的。(这里还有一个问题要解决,新表没有数据时,触发器插入数据。然后后续同步数据也会把这条数据同步过来,此时新表已经有了,然后会报错。解决这个问题需要INSERT IGNORE参数,下面介绍。)

我们也能看出上述的精髓也就这这几条REPLACE INTO操作,正是因为这几条REPLACE INTO才能保证数据的一致性。

第四步:拷贝原表数据到新表,数据量大时会根据主键进行分段chunk插入。

数据的拷贝是基于小块(根据chunk-time参数指定)进行的,而且根据主键或者索引进行选择,所以对整体服务器性能影响较小。它是通过一些查询(基本为主键、唯一键值)分批把数据导入到新的表中,在导入前,我们能通过参数 --chunk-size对每次导入行数进行控制,已减少对原表的锁定时间,并且在导入时,我们能通过 --sleep参数控制,在每个chunk导入后与下一次chunk导入开始前sleep一会,sleep时间越长,对于磁盘IO的冲击就越小。

另外可以看到INSERT使用了两个参数LOW_PRIORITY和IGNORE。其中INSERT IGNORE会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据,达到在间隙中插入数据的目的。这个参数也是为了UPDATE触发器使用的,解决数据重复问题。使用LOW_PRIORITY关键词,则INSERT的执行被延迟,直到没有其它客户端从表中读取为止。当原有客户端正在读取时,有些客户端刚开始读取。这些客户端也被包括在内。此时,INSERT LOW_PRIORITY语句等候。因此,在读取量很大的情况下,发出INSERT LOW_PRIORITY语句的客户端有可能需要等待很长一段时间(甚至是永远等待下去)。LOW_PRIORITY关键字在这里不知道有什么作用,暂时没搞明白。

最后pt-osc工具通过LOCK IN SHARE MODE来实现读取当前读取之后还需要保证其他并发事务不能修改当前读取的记录,确保数据的新老数据的100%一致,故要对读取记录加S锁。例如,原表一个UPDATE操作更新1000条数据,在数据插入新表途中如果新表也同样执行了UPDATE,有可能会只更新900条。所以需要这个读锁在对原表数据查询时不允许其他事务做更改操作,同样也不允许读其他正在更改数据的事务。

第五步:交换表,其通过一个RENAME TABLE同时处理两个表,实现原子操作。

其通过一个RENAME TABLE同时处理两个表,实现原子操作。在rename过程,其实我们还是会导致写入读取堵塞的,所以从严格意思上说,我们的OSC也不是对线上环境没有一点影响,但由于rename操作只是一个修改名字的过程,也只会修改一些表的信息,基本是瞬间结束,故对线上影响不太大。

实际上变更的表如果有超长事务,依然存在DML锁问题导致失败,因为在最后的新表旧表交替阶段,会有一个短暂的加锁操作,而长事务会阻塞这个加锁操作,所以还得在使用OSC前确认无长事务。

第六步:清理以上过程中的old表,触发器。

总结

以上即为整个Percona OSC的过程,我们看到精华部分就触发器那一块,不过还有很多细节未介绍,如:外键、记录binlog等等,由于环境的复杂性,此工具还是有很多风险,如以下几个方面问题或者需要规避的一些问题:

  • 此工具不是原子操作,如果某一点失败,不仅仅会留下很多中间过程的垃圾文件,而这些文件很难完全清理,并且如果有这些文件存在,那么就不能在次执行OSC操作。
  • 在执行时,尽量避免有这个表的批量更新、锁表、优化表的操作。我们能想象的到,如果有锁表、优化表,那么OSC是否还能正常执行?
  • 如果存在主从结构,那么尽量在从库先执行。因为如果在主库执行完毕后再到从库执行,我们能想象,主库字段多,会不会有问题呢?除非你确定在从库整个OSC未完成之前不会有新增列写入。
  • 表必须有单一列的主键或者单一唯一键。否则,REPLACE语句就等同于INSERT语句,因为没有用于确定新行跟旧行是否重复的凭证。
  • 不要有外键,尽管脚本经过严格测试,但是是否还有bug,也未知,表的外键是不是会带来更多的问题呢?
  • 修改索引、外键、列名时,优先采用MySQL原生Online DDL,并指定ALGORITHM=INPLACE,表示不加锁修改,如果报错则意味此语句必须得加排它锁。
  • 在执行之前,我们是不是要对磁盘容量进行评估呢?使用OSC会使增加一倍的空间,包括索引,而且在Row Based Replication下,还会写一份binlog。不要想当然使用–set-vars去设置sql_log_bin=0,因为在这个session级别,alter语句也要在从库上执行,除非你对从库另有打算。

以上列到的,只是部分问题,我想如果需要在线进行实施,还需要经过严格的测试。但是它的实现为我们提供了一个很好的在线更改表结构方法,我相信只要我们能很好的规避他的弊端,它会给我们带来很大的帮助,对于MySQL 5.6之前的数据库版本而言。另外,在MySQL 5.6版本开始已经支持部分Online DDL操作了,比如添加索引,添加字段等操作。

三、OSC语法介绍

具体安装可以参考:Percona Toolkit工具集介绍

常用选项说明:

--user-u

#连接的用户名。

--password-p

# 连接的密码。

--database-D

# 连接的数据库。

--port-P

# 连接数据库的端口。

--host-h

# 连接的主机地址。

--socket-S

# 连接的套接字文件。

--ask-pass

# 隐式输入连接MySQL的密码。

--charset

# 指定修改的字符集。

--defaults-file-F

# 读取配置文件。

--alter

# 结构变更语句,不需要alter table关键字,可以指定多个更改,用逗号分隔。如下场景,需要注意:

  • 绝大部分情况下表上需要有主键或唯一索引,因为工具在运行当中为了保证新表也是最新的,需要旧表上创建 DELETE和UPDATE 触发器,同步到新表的时候有主键会更快。个别情况是,当alter操作就是在c1列上建立主键时,DELETE触发器将基于c1列。
  • 子句不支持 RENAME 去给表重命名。
  • alter命令原表就不支持给索引重命名,需要先drop再add,在pt-osc也一样。(MySQL 5.7 支持 RENAME INDEX old_index_name TO new_index_name)。但给字段重命名,千万不要drop-add,整列数据会丢失,使用change col1 col1_new type constraint(保持类型和约束一致,否则相当于修改 column type,不能online)
  • 如果加入的列非空而且没有默认值,则工具会失败,其不会为你设置一个默认值,必须显示指定。
  • 删除外键(drop foreign key constrain_name)时,需要指定名称_constraint_name,而不是原始的constraint_name。如:CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`),需要指定:–alter “DROP FOREIGN KEY _fk_foo”。

--alter-foreign-keys-method

# 如何把外键引用到新表?需要特殊处理带有外键约束的表,以保证它们可以应用到新表。当重命名表的时候,外键关系会带到重命名后的表上。该工具有两种方法,可以自动找到子表,并修改约束关系。

auto:在rebuild_constraints和drop_swap两种处理方式中选择一个。

rebuild_constraints:使用ALTER TABLE语句先删除外键约束,然后再添加。如果子表很大的话,会导致长时间的阻塞。

drop_swap: 执行FOREIGN_KEY_CHECKS=0,禁止外键约束,删除原表,再重命名新表。这种方式很快,也不会产生阻塞,但是有风险:

1. 在删除原表和重命名新表的短时间内,表是不存在的,程序会返回错误。

2. 如果重命名表出现错误,也不能回滚了,因为原表已经被删除。

none: 类似”drop_swap”的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面。

--[no]check-alter

# 默认yes,语法解析。配合 --dry-run 和 --print 一起运行,来检查是否有问题(change column,drop primary key)。

--max-lag

# 默认1s,每个chunk拷贝完成后,会查看所有复制Slave的延迟情况。要是延迟大于该值,则暂停复制数据,直到所有从的滞后小于这个值,使用Seconds_Behind_Master。如果有任何从滞后超过此选项的值,则该工具将睡眠 --check-interval指定的时间,再检查。如果从库被停止,将会永远等待,直到从库开始同步,并且延迟小于该值。如果指定 --check-slave-lag,该工具只检查该服务器的延迟,而不是所有服务器。

--check-slave-lag

# 指定一个从库的DSN连接地址,如果从库超过 --max-lag参数设置的值,就会暂停操作。

--recursion-method = none

# 默认是show processlist,发现从的方法,也可以是host,但需要在从上指定report_host,通过show slave hosts来找到,可以指定none从不检查Slave的主从延迟。

--check-interval

# 默认是1,配置 --max-lag参数检查的睡眠时间。

--[no]check-plan

# 默认yes,检查查询执行计划的安全性。

--[no]check-replication-filters

# 默认yes,如果工具检测到服务器选项中有任何复制相关的筛选,如指定binlog_ignore_db和replicate_do_db此类。发现有这样的筛选,工具会报错且退出。因为如果更新的表Master上存在,而Slave上不存在,会导致复制的失败。使用 --no-check-replication-filters选项来禁用该检查。

--[no]swap-tables

# 默认yes,交换原始表和新表,除非你禁止 --[no]drop-old-table 。

--[no]drop-triggers

# 默认yes,删除原表上的触发器。 --no-drop-triggers会强制开启 --no-drop-old-table ,即不删除触发器就会强制不删除原表。

--new-table-name

# 复制创建新表的名称,默认%T_new。

--[no]drop-new-table

# 默认yes。删除新表,如果复制组织表失败。

--[no]drop-old-table

# 默认yes。复制数据完成重命名之后,删除原表。如果有错误则会保留原表。

--max-load

# 默认为Threads_running=25。每个chunk拷贝完后,会检查SHOW GLOBAL STATUS的内容,检查指标是否超过了指定的阈值。如果超过,则先暂停。这里可以用逗号分隔,指定多个条件,每个条件格式:status指标=MAX_VALUE或者status指标:MAX_VALUE。如果不指定MAX_VALUE,那么工具会这只其为当前值的120%。

--critical-load

# 默认为Threads_running=50。用法基本与 --max-load类似,如果不指定MAX_VALUE,那么工具会这只其为当前值的200%。如果超过指定值,则工具直接退出,而不是暂停。

--default-engine

# 默认情况下,新的表与原始表是相同的存储引擎,所以如果原来的表使用InnoDB的,那么新表将使用InnoDB的。在涉及复制某些情况下,很可能主从的存储引擎不一样。使用该选项会默认使用默认的存储引擎。

--set-vars

# 使用pt-osc进行ddl要开一个session去操作,set-vars可以在执行alter之前设定这些变量,比如默认会设置–set-vars “wait_timeout=10000,innodb_lock_wait_timeout=1,lock_wait_timeout=60″。因为使用pt-osc之后ddl的速度会变慢,所以预计2.5h还不能改完,记得加大wait_timeout。

--chunk-size-limit

# 当需要复制的块远大于设置的chunk-size大小,就不复制,默认值是4.0,一个没有主键或唯一索引的表,块大小就是不确定的。

--chunk-time

# 默认0.5s,即拷贝数据行的时候,为了尽量保证0.5s内拷完一个chunk,动态调整chunk-size的大小,以适应服务器性能的变化。也可以通过另外一个选项 --chunk-size禁止动态调整,即每次固定拷贝1k行,如果指定则默认1000行,且比chunk-time优先生效。

--chunk-size

# 指定块的大小,默认是 1k 行,可以添加k,M,G后缀。这个块的大小要尽量与 --chunk-time匹配,如果明确指定这个选项,那么每个块就会指定行数的大小。

--[no]check-plan

# 默认yes。为了安全,查查询的执行计划,默认情况下,这个工具在执行查询之前会先EXPLAIN,以获取一次少量的数据。如果是不好的EXPLAIN,那么会获取一次大量的数据,这个工具会多次执行EXPALIN,如果EXPLAIN不同的结果,那么就会认为这个查询是不安全的。

--statistics

# 打印出内部事件的数目,可以看到复制数据插入的数目。

--dry-run

# 创建和修改新表,但不会创建触发器、复制数据、和替换原表。并不真正执行,可以看到生成的执行语句,了解其执行步骤与细节。 --dry-run 与 --execute 必须指定一个,二者相互排斥。和–print配合最佳。

--execute

# 确定修改表,则指定该参数会真正开始执行。 --dry-run--execute必须指定一个,二者相互排斥。

--print

# 打印SQL语句到标准输出。指定此选项可以让你看到该工具所执行的语句,和 --dry-run配合最佳。

--progress

# 复制数据的时候打印进度报告,二部分组成:第一部分是百分比,第二部分是时间。

--quiet-q

# 不把信息标准输出。

四、OSC使用实战

测试一:简单使用

第一步:创建一个测试表,表引擎为myisam

PS:使用OSC时,原表需要一个主键或唯一索引,因为删除的触发器需要,否则数据不会被复制。上面已经说明了该工具的实现方式,下面来执行看是否有效。

第二步:更改存储引擎查看OSC实现方式和原理

执行过程如下:

如果表没有主键或唯一键,会出现如下这一句:

原表需要一个主键或唯一索引,因为删除的触发器需要,否则数据不会被复制。

第三步:使用–execute正式修改表的存储引擎(DDL操作)

执行结果如下:

上面指定了 --execute ,输出的信息里面也表示原表已经被修改成功并且记录了很详细的操作信息,查看表的引擎是否已经被修改:

测试二:主从环境

在主从环境下,修改表结构时尽量在从库先执行,因为如果先在主库执行完毕后再到从库执行,我们能想象,主库字段多,数据同步到从库,会不会有问题呢,要考虑?除非你明确在从库未完全同步完之前,主库这个字段是不会有数据写入的。另外触发器是不会在从库执行的,虽然从库会同步这个触发器。

在主从环境下,如果添加一个字段需要加上 --no-check-replication-filters选项,语句如下。

如果不加 --no-check-replication-filters选项的话,那么就会报错的,因为默认会检查主从。除了add column,也可以modify column,drop column,对于change column则需要指定: --no-check-alter参数。

测试三:主外键表的基本操作

执行错误退出,提示需要指定: --alter-foreign-keys-method 参数来操作有外键的表。要是没有外键而加了参数的话会出现:

No foreign keys reference `aaa`.`xx`; ignoring –alter-foreign-keys-method。

对可靠性要求不高可以用auto模式更新,要是可靠性要求高则需要用rebuild_constraints模式。即:

总结

1)上面的测试都是把原表删除了,要是不删除原表则,则使用 --no-drop-old-table选项,这样会让原表(_test_binlog_old)保留。

2)要是在线上环境上添加字段,但又不想影响到服务,可以用 --max-load选项去执行该工具,默认是Threads_running=25,即当前有这么多线程在运行的时候就暂停数据的复制,等少于该值则继续复制数据到新表。

3)当业务量较大时,修改操作会等待没有数据修改后,执行最后的rename操作。因此,在修改表结构时,应该尽量选择在业务相对空闲时,至少修改表上的数据操作较低时,执行较为妥当。

4)如果对外键表操作时,四种外键操作类型需要根据表的数据量和可靠程度,进行选择。处于可靠性的原因,尽量使用rebuild_constraints类型,如果没有可靠性要求,可以使用auto类型。

5)由于可能存在一定的风险,在操作之前,建议对数据表进行备份,可以使得操作更安全、可靠。

使用该工具的前提是处理的表需要有主键或则唯一索引。当处理有外键的表时,需要加 --alter-foreign-keys-method参数,值可以根据情况设置。当是主从环境,不在乎从的延迟,则需要加 --recursion-method=none 参数。当需要尽可能的对服务产生小的影响,则需要加上 --max-load参数。

<延伸>

pt-online-schema-change你今天滥用了吗?

pt-online-schema-change死锁分析

pt-online-schema-change使用说明、限制与比较

库表过多导致OSC Online DDL效率变低,罪魁祸首原来是它!

GitHub为MySQL社区贡献了新的在线更改表定义工具gh-ost


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (5)or分享 (0)
关于作者:

您必须 登录 才能发表评论!