应用端需要生成依次递增的序列来做流水序号等,方案有1、redis /mysql sequence引擎生成序列;2、mysql中myisam表 replace into方式;3、mysql中innodb表insert ... on duplicate key方式
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方式了?
该方式并发大时,存在发生死锁的风险
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(每秒生成多少序列)
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
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 |
注意 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
死锁日志
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
如对本文有疑问, 点击进行留言回复!!
网友评论