MySQL MyISAM and Innodb table generation sequence

Catalog

background

The application side needs to generate the sequential increasing sequence to make the pipeline sequence number. The schemes are as follows: 1. redis /MySQL SEQUENCE engine generates the sequence; 2. Mysam table replace into mode in MySQL; 3. innodb table insert... On duplicate key mode in MySQL

Analysis

  • The redis /MySQL SEQUENCE engine generates sequences, but multiple MySQL clusters have the need to generate sequences. If there is a problem, the scope of influence will be large. Generating sequences in redis /MySQL SEQUENCE also increases the cost of R & D and code modification, which can be used in new projects
  • In MySQL, the replacement into table of myisam is the way we currently use to generate sequence (although it is a table lock, the sequence generated per second can also meet the requirements). The way to use it is
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)

But there are problems:
The myisam table is a non transactional storage engine, and the backup is inconsistent (there is a risk of inconsistency in restoring the data);
myisam is not crash safe either;
In gtid mode, myisam table and innodb table cannot be operated in the same transaction

Why not use the innodb table replace into mode?
When the concurrency of this method is large, there is a risk of deadlock

  • The transactional innodb table insert... On duplicate key in MySQL is crash safe. It seems that there are no problems in generating sequences from myisam! What's the situation?
    Usage:
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)

test

General mechanical disk machine
MySQL5.7.16
RR isolation level
sysbench custom sql statement test tps (how many sequences are generated per second)

  • myisam replace into mode
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 mode
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 concurrent threads 124 tps 122 tps
10 concurrent threads 123 tps 121 tps
20 concurrent threads 125 tps 104 tps
30 concurrent threads 127 tps 67 tps
40 concurrent threads 127 tps 33 tps
  • It can be seen that with the increase of the number of concurrent threads in myisam, the replacement into TPS remains unchanged, because myisam is a table lock. At the same time, the table can only be written or read
  • With the increase of concurrency in innodb table, insert..on duplicate tps does not increase but decreases. The contention before row lock becomes larger, which results in lock waiting
  • The machine configuration of this test is poor, the results are somewhat referential, and the online machine configuration is better

Note that when mysqlslap tests 40 concurrent threads in innodb table, deadlock may occur (as well as RC isolation level). See the last section for details of deadlock
Why does sysbench40 concurrent thread test have no deadlock? Isn't sysbench concurrent thread issued at the same time? A kind of

/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

conclusion

  • No matter the number of concurrent threads, the speed of generating sequence is stable, but there are defects in myisam table
  • The inert on duplicate generation sequence in innodb table is suitable for the situation that the number of concurrent threads is small, and the speed of deadlock generation sequence will decrease when the number of concurrent threads is large
  • If the speed of generating sequence is required to be fast, redis /MySQL SEQUENCE can be used

Deadlock log

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)

Customizing sysbench scripts
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 statement
#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

Tags: MySQL Redis SQL less

Posted on Wed, 01 Apr 2020 09:08:18 -0700 by anybody99