情况介绍:
数据版本为5.7.29 . 数据表为500万左右的大表,线上又频繁插入操作,表有自增主键。在线用PT做给表增加一列的时候,发生死锁。
修改方式:
一. innodb_autoinc_lock_mode=2 加入到my.cnf里
先提及insert的类型:
1、simple insert 如insert into t(name) values('test')
2、bulk insert 如load data | insert into ... select .... from ....
3、mixed insert 如insert into t(id,name) values(1,'a'),(null,'b'),(5,'c');
innodb_autoinc_lock_mode: 好处和缺点:
与上面三种插入对应的3个不同值
0 这个表示tradition 获得表级锁 auto_inc
1 这个表示consecutive (mysql默认值)8.0版本之后默认是2. 有部分优化,提前预见算出要多少个sequence,一并操作。
2 这个表示interleaved. 没有auto_inc锁,但数据不连续。binlog格式一定要为row,不然可能会出现主从数据不一致的情况。
二 . 把 --chunk-size参数设置的更小
pt工作步骤:
1.新建一个更改后的表结构,
2.再在原表上创建三个触发器,分别对应update,delete,insert操作,以保证新的DML操作能同步到新表,再按chunk拆分copy原表的数据到新表。
3.最后最一次rename操作,因此整个过程都是几乎不堵塞的
以下为chunk的一些解释:
有了chunk size,pt-osc采用的是 select id from xxx where id >= ? order by id limit (chunk_size -1),2 获取到每次操作chunk的最大id范围,注意这里的id可以是主键,也可以是唯一键。这里得到两个值,第一个值是此次chunk操作的最大值,第二个值则是下一个chunk的最小值,对应下次的where id >=?
得到一个具体的id值,然后采用INSERT LOW_PRIORITY IGNORE xxx FORCE INDEX(PRIMARY
) WHERE ((id
>= ?)) AND ((id
<= ?)) LOCK IN SHARE MODE 来copy数据,
where条件最该该chunk的最大值和最小值,最大值是前面select获取到的,而最小值是每次chunk操作完设置的,也是上面select查询得到的。按照这样迭代,根据主键或者唯一键升顺一个一个chunk的来操作。
另外不只是pt-osc chunk的拆分是这样方式,实际上pt-table-checksum也是按照这种方式来拆分的。
pt-online-schema-change --host=127.0.0.1 --port=3306 --user=root --password=xxxxx --nodrop-old-table --charset=utf8 --lock-wait-timeout=51 --max-load Threads_running=100 --alter="add cityid bigint(20) DEFAULT NULL COMMENT '菜单' " D=dbname,t=tbname --execute