目录
背景
分析
测试
结论
背景
应用端需要生成依次递增的序列来做流水序号等, 方案有 1,Redis /MySQL SEQUENCE 引擎生成序列; 2,MySQL 中 myisam 表 replace into 方式; 3,MySQL 中 innodb 表 INSERT ... ON DUPLICATE KEY 方式
分析
Redis /MySQL SEQUENCE 引擎生成序列, 但多个 MySQL 集群都有生成序列的需求, 若出问题, 影响范围大; Redis /MySQL SEQUENCE 中生成序列也增加了研发修改代码的成本, 新项目可以使用这种方式
MySQL 中 myisam 表 replace into 是我们目前使用生成序列的方式 (虽然是表锁, 每秒生成的序列也满足得了需求), 使用方式为
- CREATE TABLE `test_sequence` (
- `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `val` tinyint(1) DEFAULT '0',
- PRIMARY KEY (`id`),
- UNIQUE KEY `val` (`val`)
- ) ENGINE=MyISAM;
- >replace into test_sequence(val) values(99);
- Query OK, 1 row affected (0.00 sec)
- >select last_insert_id();
- +------------------+
- | last_insert_id() |
- +------------------+
- | 1 |
- +------------------+
- 1 row in set (0.00 sec)
- >replace into test_sequence(val) values(99);
- Query OK, 2 rows affected (0.00 sec)
- >select last_insert_id();
- +------------------+
- | last_insert_id() |
- +------------------+
- | 2 |
- +------------------+
- 1 row in set (0.00 sec)
但存在问题:
myisam 表非事务存储引擎, 备份存在不一致 (恢复还原数据有不一致风险);
myisam 也不是 crash-safe 的;
gtid 模式下, 同一个事务中不能操作 myisam 表和 innodb 表
为什么不用 innodb 表 replace into 方式了?
该方式并发大时, 存在发生死锁的风险
MySQL 中事务性 innodb 表 INSERT ... ON DUPLICATE KEY, 是 crash-safe , 看起来 myisam 生成序列的存在的问题它都没有! 实际情况了?
使用方式:
- CREATE TABLE `test_sequence2` (
- `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `val` tinyint(1) DEFAULT '0',
- PRIMARY KEY (`id`),
- UNIQUE KEY `val` (`val`)
- ) ENGINE=InnoDB;
- 00>insert into test_sequence2(val) values(99) on duplicate key update id=id+1;
- Query OK, 1 row affected (0.00 sec)
- 39>select id from test_sequence2;
- +---------+
- | id |
- +---------+
- | 1 |
- +---------+
- 1 row in set (0.00 sec)
- 22>insert into test_sequence2(val) values(99) on duplicate key update id=id+1;
- Query OK, 2 rows affected (0.00 sec)
- 25>select id from test_sequence2;
- +---------+
- | id |
- +---------+
- | 2 |
- +---------+
- 1 row in set (0.00 sec)
测试
普通机械磁盘机器
MySQL5.7.16
RR 隔离级别
sysbench 自定义 sql 语句测试 tps(每秒生成多少序列)
myisam replace into 方式
- cd /usr/share/sysbench/tests
- sysbench ./test_myisam.lua --MySQL-host=127.0.0.1 --MySQL-port=3701 --MySQL-db=test --MySQL-user=sysbench --MySQL-password=sysbench --tables=1 --threads=10 --time=30 --report-interval=5 run
innodb INSERT ... ON DUPLICATE KEY UPDATE 方式
- cd /usr/share/sysbench/tests
- sysbench ./test_innodb.lua --MySQL-host=127.0.0.1 --MySQL-port=3701 --MySQL-db=test --MySQL-user=sysbench --MySQL-password=sysbench --tables=1 --threads=10 --time=30 --report-interval=5 run
myisam replace into | innodb insert..on duplicate | |
---|---|---|
1 并发线程 | 124 tps | 122 tps |
10 并发线程 | 123 tps | 121 tps |
20 并发线程 | 125 tps | 104 tps |
30 并发线程 | 127 tps | 67 tps |
40 并发线程 | 127 tps | 33 tps |
可见 myisam 随着并发线程数的增加, replace into tps 保持不变, 原因是 myisam 是表锁, 同一时刻, 该表只能写或者只能读
innodb 表随着并发数的上升, insert..on duplicate tps 不升反降, 行锁之前的争用变大了 造成锁等待
本次测试机器配置差, 结果有些参考性, 线上机器配置更好
注意 mysqlslap 压测 innodb 表 40 个并发线程时可能会出现死锁 (RC 隔离级别也是), 死锁详细见最后
为什么 sysbench40 并发线程测试没有出现过死锁? 难道 sysbench 并发线程不是同一时刻发出的?_
- /usr/local/MySQL/bin/mysqlslap -usysbench -h127.0.0.1 -P3701 -p --concurrency=40 --iterations=1 --create-schema=test --query='insert into test_sequence2(val) values(99) on duplicate key update id=id+1;select id from test_sequence2;'
- /usr/local/MySQL/bin/mysqlslap: Cannot run query insert into test_sequence2(val) values(99) on duplicate key update id=id+1;select id from test_sequence2; ERROR : Deadlock found when trying to get lock; try restarting transaction
结论
myisam 表 replace into 生成序列是稳定的方法, 不管并发线程数多少, 生成序列速度是稳定的, 但 myisam 表存在缺陷问题
innodb 表 inert on duplicate 生成序列适合并发线程数少情况, 并发线程数多会出现死锁 生成序列速度下降情况
若要求生成序列的速度快, 可用 Redis /MySQL SEQUENCE 方式
死锁日志
- LATEST DETECTED DEADLOCK
- ------------------------
- 2020-02-11 11:03:11 0x7f6a0c643700
- *** (1) TRANSACTION:
- TRANSACTION 39260727, ACTIVE 1 sec inserting
- MySQL tables in use 1, locked 1
- LOCK WAIT 28 lock struct(s), heap size 3520, 26 row lock(s), undo log entries 1
- MySQL thread id 460828, OS thread handle 140093451958016, query id 21296424 127.0.0.1 root update
- insert into test_sequence2(val) values(99) on duplicate key update id=id+1
- *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 48 page no 4 n bits 72 index val of table `test`.`test_sequence2` trx id 39260727 lock_mode X waiting
- Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
- 0: len 1; hex e3; asc ;;
- 1: len 8; hex 000000000000001a; asc ;;
- *** (2) TRANSACTION:
- TRANSACTION 39260729, ACTIVE 1 sec updating or deleting, thread declared inside InnoDB 5000
- MySQL tables in use 1, locked 1
- 29 lock struct(s), heap size 3520, 27 row lock(s), undo log entries 1
- MySQL thread id 460835, OS thread handle 140093451155200, query id 21296425 127.0.0.1 root update
- insert into test_sequence2(val) values(99) on duplicate key update id=id+1
- *** (2) HOLDS THE LOCK(S):
- RECORD LOCKS space id 48 page no 4 n bits 72 index val of table `test`.`test_sequence2` trx id 39260729 lock_mode X
- Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
- 0: len 1; hex e3; asc ;;
- 1: len 8; hex 000000000000001a; asc ;;
- *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 48 page no 3 n bits 168 index PRIMARY of table `test`.`test_sequence2` trx id 39260729 lock_mode X waiting
- Record lock, heap no 37 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
- 0: len 8; hex 000000000000001b; asc ;;
- 1: len 6; hex 000002571237; asc W 7;;
- 2: len 7; hex b6000001680110; asc h ;;
- 3: len 1; hex e3; asc ;;
- *** WE ROLL BACK TRANSACTION (1)
自定义 sysbench 脚本
- Less test_myisam/innodb.lua
- require("oltp_common")
- function thread_init(thread_id)
- drv=sysbench.sql.driver()
- con=drv:connect()
- end
- function event(thread_id)
- local vid1
- local dbprefix
- con:query('replace into test_sequence(val) values(99)')
- con:query('select last_insert_id()')
- ##innodb insert..on duplicate 语句
- #con:query('insert into test_sequence2(val) values(99) on duplicate key update id=id+1')
- #con:query('select id from test_sequence2;')
- end
- function thread_done()
- con:disconnect()
- end
来源: https://www.cnblogs.com/YangJiaXin/p/12609240.html