Overview
When servicing, we always have requirements about SEQUENCE. MySQL has auto_increment for each table by default, but it is difficult to satisfy all requirements in production.
In this situation, as in the blog content posted by Peter Zaitsev at the bottom, a sequence function is usually created and used. Of course, specifying InnoDB ensures stability in replication situations, not MyISAM.
https://www.percona.com/blog/2008/04/02/stored-function-to-generate-sequences/
Based on this content, I would like to create a resettable sequence that resets at a specific point in time.
Preparations
Before we get started, let’s take a look at how we will process data.
First, let’s create a table to save the current sequence as shown below.
CREATE TABLE `t_sequence` (
`name` varchar(100) NOT NULL,
`seq_num` bigint(20) NOT NULL DEFAULT '0',
`mtime` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY (`name`)
) ENGINE=InnoDB
Unlike Peter’s blog, I will issue a sequence by UPSERT processing with the syntax “insert into .. on duplicate key update ..“.
insert into t_sequence (name, seq_num, mtime) values ('abc', last_insert_id(1), now(6))
on duplicate key update
seq_num = last_insert_id(seq_num+1),
mtime = now(6);
return last_insert_id();
Here, let’s put the logic to reset to 1 at a specific time in “on duplicate update". The bottom is an example of resetting in minutes.
insert into t_sequence (name, seq_num, mtime) values ('abc', last_insert_id(1), now(6))
on duplicate key update
seq_num = last_insert_id(if(mtime < date_format(now(6), '%Y-%m-%d %H:%i:00'), 1, seq_num+1)),
mtime = now(6);
return last_insert_id();
By adding the logic to check the time in “on duplicate key update", it is reset the sequence number to 1 under certain conditions.
mysql> insert into t_sequence (name, seq_num, mtime) values ('abc', last_insert_id(1), now(6))
-> on duplicate key update
-> seq_num = last_insert_id(if(mtime < date_format(now(6), '%Y-%m-%d %H:%i:00'), 1, seq_num+1)),
-> mtime = now(6);
Query OK, 2 rows affected (0.00 sec)
mysql> select now(), last_insert_id();
+---------------------+------------------+
| now() | last_insert_id() |
+---------------------+------------------+
| 2021-06-21 12:31:58 | 6 |
+---------------------+------------------+
1 row in set (0.00 sec)
mysql> insert into t_sequence (name, seq_num, mtime) values ('abc', last_insert_id(1), now(6))
-> on duplicate key update
-> seq_num = last_insert_id(if(mtime < date_format(now(6), '%Y-%m-%d %H:%i:00'), 1, seq_num+1)),
-> mtime = now(6);
Query OK, 2 rows affected (0.00 sec)
mysql> select now(), last_insert_id();
+---------------------+------------------+
| now() | last_insert_id() |
+---------------------+------------------+
| 2021-06-21 12:32:01 | 1 |
+---------------------+------------------+
1 row in set (0.00 sec)
Sequence function
Based on this content, let’s create the Function as below.
delimiter //
drop function nextval//
create function nextval(in_name varchar(100), in_type char(1)) returns bigint
begin
declare date_format varchar(20);
SET date_format = (
case
when in_type = 'M' then '%Y-%m-01 00:00:00'
when in_type = 'D' then '%Y-%m-%d 00:00:00'
when in_type = 'H' then '%Y-%m-%d %H:00:00'
when in_type = 'I' then '%Y-%m-%d %H:%i:00'
when in_type = 'S' then '%Y-%m-%d %H:%i:%S'
else '%Y-%m-%d 00:00:00'
end
);
insert into t_sequence (name, seq_num, mtime) values (in_name, last_insert_id(1), now(6))
on duplicate key update
seq_num = last_insert_id(if(mtime < date_format(now(6), date_format), 1, seq_num+1)),
mtime = now(6);
return last_insert_id();
end
//
delimiter ;
As shown in the Function function, M is reset every month, D is daily reset, H is reset every hour, etc.
Below is an example of resetting in minutes. (same example above)
mysql> select nextval('abc', 'I') seq, now();
+------+---------------------+
| seq | now() |
+------+---------------------+
| 1 | 2021-06-21 12:40:42 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> select nextval('abc', 'I') seq, now();
+------+---------------------+
| seq | now() |
+------+---------------------+
| 2 | 2021-06-21 12:40:52 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> select nextval('abc', 'I') seq, now();
+------+---------------------+
| seq | now() |
+------+---------------------+
| 3 | 2021-06-21 12:40:56 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> select nextval('abc', 'I') seq, now();
+------+---------------------+
| seq | now() |
+------+---------------------+
| 1 | 2021-06-21 12:41:00 |
+------+---------------------+
1 row in set (0.00 sec)
If necessary, you can put additional requirements in the “insert into .. on duplicate update..“ statement of the Function. 🙂
Performance
Environments
## Physical machine
Intel(R) Core(TM) i3-8100 CPU @ 3.60GHz(4core), 32G Memory
## MySQL variables
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 0 |
| sync_binlog | 0 |
+--------------------------------+-------+
Due to the structure of the function defined above, a lot of locks for a specific row occur. This means that network latency has a huge impact on query execution time, and it impacts directly on the duration of the lock time.
So, I simply tested it as follows LOCAL vs REMOTE, and results from local testing should probably be considered the best performance.
TEST1. Localhost
Using mysqlslap, let’s throw random traffic.
$ time mysqlslap -utest \
--password=test123 \
--create-schema=test \
--iterations=1 \
--number-of-queries=100000 \
--query="select test.nextval('abc', 'H');"
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 5.979 seconds
Minimum number of seconds to run all queries: 5.979 seconds
Maximum number of seconds to run all queries: 5.979 seconds
Number of clients running queries: 10
Average number of queries per client: 10000
real 0m5.996s
user 0m0.915s
sys 0m1.709s
It was executed in 5.979 seconds, and 16,666 sequences were issued per second!! Not bad.
TEST2. Remote host
This time, let’s run it in an environment with some network latency. In my environment the latency is around 0.4ms.
$ ping 10.5.5.11
PING 10.5.5.11 (10.5.5.11): 56 data bytes
64 bytes from 10.5.5.11: icmp_seq=0 ttl=64 time=0.404 ms
I tried generating the same test traffic as above in this environment.
$ time mysqlslap -utest \
--password=test123 \
--host=10.5.5.11 \
--concurrency=10 \
--create-schema=test \
--iterations=1 \
--number-of-queries=100000 \
--query="select test.nextval('abc', 'H');"
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 7.191 seconds
Minimum number of seconds to run all queries: 7.191 seconds
Maximum number of seconds to run all queries: 7.191 seconds
Number of clients running queries: 10
Average number of queries per client: 10000
was executed in 7.191 seconds, and 13,906 sequences were issued per second!! Not bad also.
But, if the network latency is higher than it is now, it will definitely show a worse figure. In any case, the sequence issuance performance itself seems to come out with an appropriate performance.
If a sequence is allocated individually, it seems that it can be used without worrying about performance or lock. (Test in which 10 users issue a sequence)
$ time mysqlslap -utest \
--password=test123 \
--host=10.5.5.11 \
--concurrency=10 \
--create-schema=test \
--iterations=1 \
--number-of-queries=100000 \
--query="select test.nextval(concat('ab',floor(rand()*10)), 'H');"
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 5.702 seconds
Minimum number of seconds to run all queries: 5.702 seconds
Maximum number of seconds to run all queries: 5.702 seconds
Number of clients running queries: 10
Average number of queries per client: 10000
Conslusion
I started with the idea of making a sequence that doesn’t exist in MySQL to suit our service requirements in fun way.
Whether it’s a specific service or a personalized service… I’ve occasionally heard of a sequence requirement that starts from 1 at 0 o’clock.
In this case, in the existing case, it is necessary to lock and decide whether to process the reset with the current sequence value. Here, this was solved with a simple "INSERT INTO ON DUPLICATE UPDATE.."statement.
In a similar way, various functions can be created for your own service..
Have a good day 🙂
gywndi's database 