sysbench测试重复插入数据

DBA天团 2021-02-05

2859

群里o9reo同学用sysbench测试分片cetus，对sbtest1表用id字段分库，oltp_read_write压测正常。分片键改成pad字段后，大概率会报Duplicate entry ‘xxxx’ for key ‘PRIMARY’的错。

什么情况？换了分片键就不行了？

在分析原因前，首先介绍一下sysbench。sysbench是一个模块化的、跨平台、多线程基准测试工具，主要用于评估测试各种不同系统参数下的数据库负载情况。
sysbench的oltp_read_write测试，涉及以下的sql：

SELECT c FROM sbtest1 WHERE id=658479;
UPDATE sbtest1 SET k=k+1 WHERE id=517964;
UPDATE sbtest1 SET c='59540519602-09785693357-74259059391-58789463511-85697065049-90277650583-49255380950-45305374248-89805169656-84550149942' WHERE id=539276;
DELETE FROM sbtest1 WHERE id=557597;
INSERT INTO sbtest1 (id, k, c, pad) VALUES (557597, 614210, '60290376853-10526350214-07315525783-15201647890-65522891160-07107427190-80132168599-92634255413-41141293746-83975076323', '34999484873-95390617828-93764481506-11030186365-14051039214');

sbtest1表结构：

CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`pad` char(60) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

sysbench每次执行若干select，update，delete和insert。有几个要点：

1）sysbench会开启事务。
2）select、update和delete语句的过滤条件都是id字段。
3）delete和insert语句成对出现，执行delete语句，后面必然执行insert语句，重新insert记录后的id字段值不变，但是k,c,pad字段值会改变。
4）update语句会改变k和c字段的值，所以k和c不能作为分片字段，只能按照id字段或者pad字段进行分片。

测试现场：sbtest1表有100万数据；测试并发数为150。测试开始1-5分钟内，会出现经典的1062报错：

FATAL: mysql_drv_query() returned error 1062 (Duplicate entry '20314' for key 'PRIMARY')
for query 'INSERT INTO sbtest1 (id, k, c, pad)
VALUES (20314, 1, '76917325204-93912308321-38517235363-57067364622-58802215109-00501545820-66534286136-44833509064-14872756776-15004350226', '50174188717-59895265567-11895190607-21795876427-84430251428')'
FATAL: `thread_run'' function failed: usr/share/sysbench/oltp_common.lua:482: SQL error, errno = 1062, state = '23000': Duplicate entry '20314' for key 'PRIMARY'

看报错，sysebench重复插入了id为’20314’的记录。既然在事务内先delete再insert，那么不会出现重复插入数据的问题才对。想不通！

随后增加表的记录，或者减少并发数，出错的几率变小了，但还会出错。查看中间件的log日志，很多Lock wait timeout exceeded; try restarting transaction。遇到行锁超时，cetus会回滚整个事务，这种情况也不会出现重复插入的问题。最后通过抓包分析，才终于找到了真正的原因。
流程图如下：

session 1 和 session 2 分别执行delete和insert语句。

Delele语句： delete from sbtest1 where id=20314;
Insert 语句： insert into sbetest1 (id,k,c,pad) values (20314,150,’cccc’,’xxxx’)
测试的cetus后端有两个分库节点：groupA和groupB。
执行delete前，id=100的记录pad字段值是’yyyy’，记录落在groupB节点。
执行insert后，id=100的记录pad字段值是’xxxx’，记录落在groupA节点。

PS：删除前和插入后id=100的记录，落在后端的分库不一样。

流程图每一行代表一个时间点，每个session左边列代表groupA节点操作，右边列代表groupB节点操作。null代表无操作。

T1：session1(后面简写为S1) 和 session2(后面简写为S2) 在groupA和groupB开启分布式事务。
T2：S1 和 S2 在groupA和groupB开始执行delete。
T3：S1在groupA和groupB执行的delete完成，在groupA影响行数是0（因为groupA没有id=100的记录），在groupB影响行数是1；S2在groupA执行的delete完成，影响行数是0，在groupB执行delete的时候，因为S1持有id=100的记录的行锁，此时groupB执行的delete需要等S1释放锁资源。
T4：S1在groupA和groupB都做完delete，开始执行insert。新的pad值路由到groupA节点，groupB节点没有执行任何操作。与此同时，S2因为groupB的delete被阻塞，没有执行新的操作。
T5：S1在groupA的insert完成
T6：S1完成所有节点操作，xa提交。
T7：S1释放占有的行锁后，S2在groupB的delete得以成功完成，但是删除了0条记录，因为在groupB的记录已经在S1被删除了。id=100的记录由于pad变化，记录被插入groupA节点，S2删除groupA记录的操作，在记录插入前已经完成。也就是说，S2根本没删除id=100的记录。这就是产生重复插入记录的原因。如果T3到T7的时间差比groupB节点的innodb_lock_wait_timeout值要大，S2将会报错：Lock wait timeout exceeded; try restarting，否则S2继续执行insert。
T8：S2的delete都执行完，开始执行insert。
T9：S2执行的insert语句，根据pad的值，记录被hash路由到groupA节点，这样将会报主键重复插入的错。如果pad的值路由到了groupB节点，大家想想会发生什么事情？

我们再看看按照id字段分库，id=100的记录只能插入groupB，那么在T7步骤将删除id=100的记录，就不会有重复插入主键的问题了。出现主键重复插入的根本原因，是同一个主键id的记录，因为分片键值的不同而在多个分库节点漂移，第二个会话根本没删除任何数据。所以说分片键的选择很重要。
我们再回顾一下分库的几个原则：
1、同一个分库业务规则下的表，放到同一个vdb下。sbtest1表放到第一个vdb。
2、选取的分片键，能把表的数据打散，尽量均匀分布到后端各个分库。id字段的值是连续的，pad字段的值是随机的，相对来说，按照id字段，数据能更均匀的分到各个分库。
3、尽量减少分布式事务和跨节点事务。oltp_read_write测试执行sql前都开启分布式事务。再看select、update和delete的过滤条件都跟id有关，如果用id字段做分片键，能把id=xx的语句路由到唯一的后端节点执行，避免跨节点操作。
4、分片键的值最好不要改变。如果要改变分片键的值，需要先删除记录，再进行插入新记录。这里只能选择id字段，k、c和pad字段都是不合适的。
综上所述，sysbench测试用id字段做分片键是最合适的，没有之一。线上业务参考以上分库原则，一般都能找到最佳的分库规则。