osc mysql_mysql 案例~关于pt-osc工具的用途

简介:今天咱们聊聊PT-OSC的东东

一 简单介绍

1 标准组合

pt-online-schema-change --user= --password= --host= D=,t= --sock=/tmp/mysql.sock --alter ''   --max-load='Threads_running=35,Threads_connected=10000'  --critical-load='Threads_running=200,Threads_connected=10000' --charset=utf8 --alter-foreign-keys-method=auto --print --execute

--charset 字符集设置 不支持utf8mb4

--alter-foreign-keys-method auto 自身采用哪种方法处理外键 强烈建议开启,因为你不可能在执行之前去查看每张表是否有外键

--print 打印进度

--execute 执行

注意:因为pt-osc工具默认有一些控制参数搭配,这些参数会导致pt工具暂缓进行(比如主从延迟>1s这种) 所以以上组合推荐在没有任何压力的小表上做

2 不考虑从库延迟和主从复制设置的过滤表

pt-online-schema-change --user= --password= --host= D=,t= --sock=/tmp/mysql.sock --alter '' --charset=utf8 --alter-foreign-keys-method=auto --max-load='Threads_running=35,Threads_connected=10000'  --critical-load='Threads_running=200,Threads_connected=10000' --no-check-replication-filters -recursion-method=none --print --    execute

--no-check-replication-filters 不考虑复制过滤规则

-recursion-method=none 不设置默认方法来指定发现从库,必须显示指定

--max-load 默认thread_running=25 一旦大于25会暂缓pt任务的执行,可以看情况是否调大,参数格式(Threads_running=35,Threads_connected=10000)

注意:如果不是非常高强度一致性或者接受的情况下,建议采取此策略.配合--max-load根据服务器负载进行条件

3 考虑从库延迟

pt-online-schema-change --user= --password= --host= D=,t= --sock=/tmp/mysql.sock --alter '' --charset=utf8 --alter-foreign-keys-method=auto  --max-load='Threads_running=35,Threads_connected=10000'  --critical-load='Threads_running=200,Threads_connected=10000'--no-check-replication-filters -recursion-method=processlist --   max-lag=10000 -recursion-method=none --print --execute

-recursion-method=processlist/host 发现从库的方式

--max-lag 默认1s 也就是说一旦从库延迟超过1s 就会暂缓执行

二 注意事项

1 pt-osc 无法作用于没有主键的表,请特别注意(包括PT的一系列的工具其实都需要主键或者唯一性索引)

2 pt-osc 可根据主库负载和从库延迟这两方面自由调节

3 pt-osc 操作上的表不能有触发器

4 pt-osc 的速度不如原生Online ddl的速度 5 百万级别的表建议都采用pt-osc

三 一些问题

1 我曾经遇到过pt-osc任务卡在创建触发器的问题,不得不中断。此时有高密度的DML操作,所以如果此表有很高的DML操作,请暂缓执行任务,业务低谷再做

2 关于pt-osc修改主键的问题

3 pt-osc在处理存在外键约束的表，有可能出现异常，pt-osc它爸Baron也提过，所以先检查是否有外键(有外键的，尽量不要使用)。

四 关于联合主键的修改问题

场景 关于将联合主键如何修改为自增ID为主键

DDL执行顺序

add unique key()

drop primary key

add id int auto_increment primary key

采用PT执行

加上 --no-check-alter 才可以执行

在这里要注意一点:再执行主键变更的时候要确保没有太多的DML和select的操作否则会严重影响性能(本人经历过一次)

对主键的变更操作要格外慎重

五 pt-osc的执行过程

1 创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)

2 在新表执行alter table 语句(速度应该很快)

3 在原表中创建触发器3个触发器分别对应insert,update,delete操作

4 以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表

5 Rename 原表到old表中，在把临时表Rename为原表

6 如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理

7 默认最后将旧原表删除

六 pt-osc强制终止清理

必须按照以下步骤来

1 kill pt-osc相关进程

2 drop triggers

如果出现MDL锁要进行MDL锁定位(一般为慢查询导致)

3 确认 triggers删除后再执行drop _table_new操作