MGR并行复制从节点复制线程死锁

1.故障现象

20191113-22:32 datax全量同步t_shop_info表到 eorder所在的实例，t_shop_info表有两个唯一约束。总数据量不超过1w行，同步完成后MGR从库复制线程死锁。导致同步停止，影响业务。

从库processlist 截图

2.故障复现

2.1 集群环境

MGR集群ip	端口
10.204.55.205	3320
10.204.55.206	3320
10.204.55.207	3320

2.2 复现方法1 ,使用datax全量同步。

集群主要参数配置

SET GLOBAL transaction_isolation = 'READ-COMMITTED'
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 16;
SET GLOBAL slave_preserve_commit_order = ON;

用datax启动 replace into方式全量同步t_shop_info表到MGR测试集群。

结果：任务启动第二次从库复制线程必定hangs。

用datax启动 DUPLICATE KEY UPDATE 方式全量同步t_shop_info表到MGR测试集群

结果：任务启动5次都没有出现从库复制线程hangs。

2.3 复现方法2,使用 jmeter 并发40线程并行执行sql。

集群主要参数

SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 16;
SET GLOBAL slave_preserve_commit_order = ON;

表结构

表结构

CREATE TABLE `replaceinto_deadlocktest` (
`fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
`fname` VARCHAR(10) DEFAULT NULL,
`fdesc` VARCHAR(10) DEFAULT NULL,
PRIMARY KEY (`fid`),
UNIQUE KEY `fname` (`fname`)
) ENGINE=INNODB AUTO_INCREMENT=10754 DEFAULT CHARSET=utf8mb4

测试sql类型

#随机数replace into
REPLACE INTO replaceinto_deadlocktest (fid,fname,fdesc) VALUES (@fid:='${__Random(1,200000,)}', @fname:=CONCAT('b',@fid),'a2');
#递增id replace into
REPLACE INTO replaceinto_deadlocktest (fid,fname,fdesc) VALUES ('${__intSum(${__counter(TRUE,)},1)}','b${__intSum(${__counter(TRUE,)},1)}','a2')
#随机id DUPLICATE update
INSERT INTO replaceinto_deadlocktest (fid,fname,fdesc) VALUES (@fid:='${__Random(1,200000,)}',@fname:=CONCAT('b',@fid),'a2')
ON DUPLICATE KEY UPDATE fid=@fid,fname=@fname,fdesc='a2';
#递增id DUPLICATE update
INSERT INTO replaceinto_deadlocktest (fid,fname,fdesc) VALUES ('${__intSum(${__counter(TRUE,)},1)}','b${__intSum(${__counter(TRUE,)},1)}','a2')
ON DUPLICATE KEY UPDATE fid='${__intSum(${__counter(TRUE,)},1)}',fname='b${__intSum(${__counter(TRUE,)},1)}',fdesc='a2';

结果：

随机数replace into 与递增id replace into 都会出现从库复制线程hangs。并且jmeter会有一定的比例报线程死锁。

随机数DUPLICATE update 与递增id DUPLICATE update 都没出现从库复制线程hangs。

jmeter死锁日志
Response code:40001 1213
Response message:com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
 
mysql死锁日志
TRANSACTION 10244303573, ACTIVE 0 sec inserting
mysql TABLES IN USE 1, locked 1
LOCK WAIT 5 LOCK struct(s), HEAP size 1136, 4 ROW LOCK(s), UNDO LOG entries 2
MySQL thread id 39582, OS thread handle 140372828473088, QUERY id 11662118 10.201.3.1 WORK UPDATE
REPLACE INTO replaceinto_deadlocktest (fid,fname,fdesc) VALUES (@fid:='49', @fname:=CONCAT('b',@fid),'a2')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 734 page NO 4 n bits 120 INDEX fname of TABLE `dayutest`.`replaceinto_deadlocktest` trx id 10244303573 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 10244303574, ACTIVE 0 sec inserting
mysql TABLES IN USE 1, locked 1
4 LOCK struct(s), HEAP size 1136, 3 ROW LOCK(s), UNDO LOG entries 2
MySQL thread id 39560, OS thread handle 140349632673536, QUERY id 11662119 10.201.3.1 WORK UPDATE
REPLACE INTO replaceinto_deadlocktest (fid,fname,fdesc) VALUES (@fid:='5', @fname:=CONCAT('b',@fid),'a2')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS SPACE id 734 page NO 4 n bits 120 INDEX fname of TABLE `dayutest`.`replaceinto_deadlocktest` trx id 10244303574 lock_mode X LOCKS rec but NOT gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 734 page NO 4 n bits 120 INDEX fname of TABLE `dayutest`.`replaceinto_deadlocktest` trx id 10244303574 lock_mode X waiting
*** WE ROLL BACK TRANSACTION (2)

2.4 复现方法3，jmeter+（pt-osc，gh-ost）

jmeter并发写入带唯一索引的表，并且使用pt-osc 和gh-ost 分别修改表结构

结果：jmeter并发写入时，同时pt-osc出现从库复制线程hangs。

jmeter并发写入时，同时gh-ost没有出现从库复制线程hangs。

pt-online-schema-change --user=work --password=111111  --host=10.204.55.205 --port 3320 
--alter="add index idx_fdesc(fdesc);" 
D=dayutest,t=replaceinto_deadlocktest 
--chunk-size=20 
--nodrop-old-table 
--chunk-time=0.5 
--max-lag=2 
--check-interval=2 
--charset=utf8 
--critical-load="Threads_running=64" 
--max-load="Threads_running=32" 
--print 
--execute


 gh-ost  --host='10.204.55.205' --port 3320 
--user='work' 
--password='111111' 
--database='dayutest' 
--table='replaceinto_deadlocktest' 
--alter="add index idx_fdesc(fdesc);"  
--allow-on-master  
--verbose  
--initially-drop-ghost-table  
--throttle-flag-file /tmp/1.log   
--allow-master-master 
--ok-to-drop-table  
--approve-renamed-columns  
--max-lag-millis=2000 
--chunk-size=2000 
--max-load=Threads_running=20  
--switch-to-rbr 
--execute

3.测试结论

MGR开启并行16线程复制，并且对带唯一约束表做replace into操作很高的几率导致MGR从库hangs。

replace into 场景包括（pt-osc，datax全量同步）。

后续改进措施，使用gh-ost替代pt-osc，datax全量同步调整为ON DUPLICATE KEY UPDATE 插入。