dede wap网站模板下载,wordpress5回复后查看,相机拍照的图片怎么做网站呀,网络整合营销公司1 引言
大家好#xff0c;接着上次和大家一起学习了《MySQL DDL执行方式-Online DDL介绍》#xff0c;那么今天接着和大家一起学习另一种MySQL DDL执行方式之pt-soc。
在MySQL使用过程中#xff0c;根据业务的需求对表结构进行变更是个普遍的运维操作#xff0c;这些称为…1 引言
大家好接着上次和大家一起学习了《MySQL DDL执行方式-Online DDL介绍》那么今天接着和大家一起学习另一种MySQL DDL执行方式之pt-soc。
在MySQL使用过程中根据业务的需求对表结构进行变更是个普遍的运维操作这些称为DDL操作。常见的DDL操作有在表上增加新列或给某个列添加索引。
DDL定义
Data Definition Language即数据定义语言那相关的定义操作就是DDL包括新建、修改、删除等相关的命令有CREATEALTERDROPTRUNCATE截断表内容开发期还是挺常用的COMMENT 为数据字典添加备注。
注意DDL操作是隐性提交的不能rollback一定要谨慎哦
下图是执行方式的性能对比及说明 图1 易维平台说明图
下面本文将对DDL的执行工具之pt-osc进行简要介绍及分析。如有错误还请各位大佬们批评指正。
2 介绍
pt-online-schema-change - ALTER tables without locking them.
pt-online-schema-change alters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully.
pt-online-schema-change是Percona公司开发的一个非常好用的DDL工具称为 pt-online-schema-change是Percona-Toolkit工具集中的一个组件很多DBA在使用Percona-Toolkit时第一个使用的工具就是它同时也是使用最频繁的一个工具。它可以做到在修改表结构的同时即进行DDL操作不阻塞数据库表DML的进行这样降低了对生产环境数据库的影响。在MySQL5.6之前是不支持Online DDL特性的即使在添加二级索引的时候有FIC特性但是在修改表字段的时候还是会有锁表并阻止表的DML操作这样对于DBA来说是非常痛苦的好在有pt-online-schema-change工具在没有Online DDL时解决了这一问题。
Percona 公司是成立于2006年总部在美国北卡罗来纳的Raleigh。由 Peter Zaitsev 和 Vadim Tkachenko创立 这家公司声称他们提供的软件都是免费的他们的收入主要来与开源社区企业的支持以及使用他们软件的公司的支付他们提供support的费用。 而实际上这家公司垄断了业内最流行数据库支持类的软件并且还开发了一些其他的与数据库相关的东西。
Percona-Toolkit工具集是Percona支持数据库人员用来执行各种MySQL、MongoDB和系统任务的高级命令行工具的集合这些任务太难或太复杂而无法手动执行。这些工具是私有或“一次性”脚本的理想替代品因为它们是经过专业开发、正式测试和完整记录的。它们也是完全独立的因此安装快速简便无需安装任何库。
Percona Toolkit 源自 Maatkit 和 Aspersa这两个最著名的 MySQL 服务器管理工具包。它由 Percona 开发和支持。
3 工作流程
pt-osc 用于修改表时不锁表简单地说这个工具创建一个与原始表一样的新的空表并根据需要更改表结构然后将原始表中的数据以小块形式复制到新表中然后删除原始表然后将新表重命名为原始名称。在复制过程中对原始表的所有新的更改insertdeleteupdate都将应用于新表因为在原始表上创建了一个触发器以确保所有新的更改都将应用于新表。有关 pt-online-schema-change 工具的更多信息请查阅手册文档 。
pt-osc大致的工作过程如下
1.创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)
2.在新表执行alter table 语句速度应该很快
3.在原表中创建触发器3个触发器分别对应insert,update,delete操作如果表中已经定义了触发器这个工具就不能工作了
4.以一定块大小从原表拷贝数据到临时表拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表保证数据不会丢失会限制每次拷贝数据的行数以保证拷贝不会过多消耗服务器资源采用 LOCK IN SHARE MODE 来获取要拷贝数据段的最新数据并对数据加共享锁阻止其他会话修改数据不过每次加S锁的行数不多很快就会被释放
5.将原表Rename为old表再把新表Rename为原表整个过程只在rename表的时间会锁一下表其他时候不锁表
6.如果有参考该表的外键根据alter-foreign-keys-method参数的值检测外键相关的表做相应设置的处理根据修改后的数据修改外键关联的子表如果被修改表存在外键定义但没有使用 –alter-foreign-keys-method 指定特定的值该工具不予执行
7.默认最后将旧原表删除、触发器删除。 图2 pt-osc工作过程示意图
4 用法
Percona Toolkit 是成熟的但是官方还是建议在使用前做到以下几点
•阅读该工具的详细文档
•查看该工具的已知“错误”
•在非生产服务器上测试该工具
•备份您的生产数据并验证备份
下载安装
从官方网站下载percona-toolkit然后执行下面的命令进行安装示例
# 安装依赖包
yum install perl-TermReadKey.x86_64
yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL# 安装percona-toolkit
rpm -ivh percona-toolkit-3.1.0-2.el7.x86_64.rpm
执行类似下面的命令修改表结构
pt-online-schema-change --alteradd column c1 int; --execute Dtest,ttable,uuser,ppassword
alter参数指定修改表结构的语句execute表示立即执行D、t、u、p分别指定库名、表名、用户名和密码执行期间不阻塞其它并行的DML语句。pt-online-schema-change还有许多选项具体用法可以使用pt-online-schema-change --help查看联机帮助。
5 限制
pt-online-schema-change也存在一些局限性
1.在使用此工具之前应为表定义PRIMARY KEY或唯一索引因为它是DELETE触发器所必需的
2.如果表已经定义了触发器则不支持 pt-osc 注不是不能有任何触发器只是不能有针对insert、update、delete的触发器存在因为一个表上不能有两个相同类型的触发器
3.如果表具有外键约束需要使用选项 –alter-foreign-keys-method如果被修改表存在外键定义但没有使用 --alter-foreign-keys-method 指定特定的值该工具不予执行
4.还是因为外键对象名称可能会改变indexes names 等
5.在Galera集群环境中不支持更改MyISAM表系统变量 wsrep_OSU_method 必须设置为总序隔离Total Order IsolationTOI
6.此工具仅适用于 MySQL 5.0.2 及更新版本因为早期版本不支持触发器
7.需要给执行的账户在 MySQL上授权才能正确运行。应在服务器上授予PROCESS、SUPER、REPLICATION SLAVE全局权限以及 SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、ALTER 和 TRIGGER 表权限。Slave 只需要 REPLICATION SLAVE 和 REPLICATION CLIENT 权限。
6 对比OnLine DDL
下面的表格是国外技术牛人进行的测试数据是Online DDL和pt-osc对一个包含1,078,880行的表应用一些alter操作的对比结果仅供参考
online ddlpt-osc更改操作受影响的行是否锁表时间秒受影响的行是否锁表时间秒添加索引0否3.76所有行否38.12下降指数0否0.34所有行否36.04添加列0否27.61所有行否37.21重命名列0否0.06所有行否34.16重命名列更改其数据类型所有行是30.21所有行否34.23删除列0否22.41所有行否31.57更改表引擎所有行是25.3所有行否35.54
那么现在的问题是我们应该使用哪种方法来执行alter语句呢
虽然pt-osc允许对正在更改的表进行读写操作但它仍然会在后台将表数据复制到临时表这会增加MySQL服务器的开销。所以基本上如果Online DDL不能有效工作我们应该使用 pt-sc。换句话说如果Online DDL需要将数据复制到临时表algorithmcopy并且该表将被长时间阻塞lockexclusive或者在复制环境中更改大表时我们应该使用 pt-osc工具。
pt-osc官方文档 https://docs.percona.com/percona-toolkit/pt-online-schema-change.html
7 总结
本次和大家一起学习了解pt-online-schema-change工具介绍了其产生的背景、基本工作流程、用法及相应的一些限制。还介绍了其与Online DDL执行方式的一些对比如果错误还请指正。
目前可用的DDL操作工具包括pt-oscgithub的gh-ost以及MySQL提供的在线修改表结构命令Online DDL。pt-osc和gh-ost均采用拷表方式实现即创建个空的新表通过selectinsert将旧表中的记录逐次读取并插入到新表中不同之处在于处理DDL期间业务对表的DML操作。
到了MySQL 8.0 官方也对 DDL 的实现重新进行了设计其中一个最大的改进是 DDL 操作支持了原子特性。另外Online DDL 的 ALGORITHM 参数增加了一个新的选项INSTANT只需修改数据字典中的元数据无需拷贝数据也无需重建表同样也无需加排他 MDL 锁原表数据也不受影响。整个 DDL 过程几乎是瞬间完成的也不会阻塞 DML不过目前8.0的INSTANT使用范围较小后续再对8.0的INSTANT做详细介绍吧。
下一期文章将和大家一起学习、了解github的gh-ost敬请期待哦 作者京东物流 刘邓忠 来源京东云开发者社区
