MySQL에서 리셋되는 시퀀스 만들어보기

https://www.percona.com/blog/stored-function-to-generate-sequences/

delimiter //
create function seq(seq_name char (20)) returns int
begin
 update seq set val=last_insert_id(val+1) where name=seq_name;
 return last_insert_id();
end
//
delimiter ;
 
CREATE TABLE `seq` (
  `name` varchar(20) NOT NULL,
  `val` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
insert into seq values('one',100);
insert into seq values('two',1000);

 

mysql> select seq('one'),seq('two'),seq('one'),seq('one');
+------------+------------+------------+------------+
| seq('one') | seq('two') | seq('one') | seq('one') |
+------------+------------+------------+------------+
|        102 |       1002 |        103 |        104 |
+------------+------------+------------+------------+
1 row in set (0.00 sec)

 

첫번째로는 현재 시퀀스를 담을 테이블 그릇(?)을 아래와 같이 생성을 해보도록 하겠습니다.

1 2 3 4 5 6

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

여기에서 seq_num이 매번 +1되면서 데이터를 전달해주게 되겠죠.
Peter의 블로그와는 다르게, 저는 아래와 같이 insert into .. on duplicate key update .. 구문으로 Upsert 처리하여 시퀀스를 발급하도록 하겠습니다.

1 2	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);

그런데, 이 구문은 단순히 시퀀스값을 매번 1씩 증가하는 것으로. 우리에게 필요한 것은 매일 0시 혹은 매시 시퀀스 값이 1부터 다시 초기화되는 로직이 쿼리안에 필요한 것입니다. 그래서, 위 쿼리를 아래와 같이 변경을 해봅니다. (매분 1로 초기화)

1 2	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);

on duplicate key update 안에 시간을 체크하는 로직을 추가하여, 결과적으로 0분때마다 다시 1부터 다시 시작하는 값이 추출되는 것이죠.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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)

자 이제, 이 쿼리들을 조합해서, 아래와 같은 Function을 만들어보겠습니다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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 ;

Function 함수에 나와있듯이, M인경우는 매월 리셋, D는 매일 리셋, H는 매시 리셋.. 등등 파라메터로 리셋할 시점을 정해서 만들어볼 수 있겠습니다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

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)

필요하다면, Function의 insert into .. on duplicate update.. 구문 안에 더 다양한 요구 사항을 넣어볼 수 있을 듯 합니다. 

Performance

함수로 만들어지기 때문에.. 느릴 수도 있다고 선입견을 가지신 분들을 위해서.. 간단하게 아래와 같이 테스트를 해보았습니다.

Environments

1	Intel(R) Core(TM) i3-8100 CPU @ 3.60GHz(4core), 32G Memory

MySQL parameter

1 2 3 4 5 6 7 8

mysql> show variables where Variable_name in ('innodb_flush_log_at_trx_commit', 'sync_binlog'); +--------------------------------+-------+ | Variable_name                  | Value | +--------------------------------+-------+ | innodb_flush_log_at_trx_commit | 0     | | sync_binlog                    | 0     | +--------------------------------+-------+ 2 rows in set (0.00 sec)

1. Local test

시퀀스 특성 상 특정 row에 대한 Lock이 매번 발생할 수밖에 없습니다. 이 얘기는, 네트워크 레이턴시가 관여할 수록 더욱 낮은 퍼포먼스를 보인다는 이야기인데요. 우선 서버에 접속해서 mysqlslap으로 아래와 같이 시퀀스 발급 트래픽을 무작위로 줘봅니다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

$ time mysqlslap -utest      \   --password=test123         \   --create-schema=test       \   --iterations=1             \   --number-of-queries=100000 \   --query="select test.nextval('abc', 'H');" 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 mysqlslap -uroot --concurrency=10 --create-schema=test --iterations=1    0.18s user 0.32s system 15% cpu 3.285 total

5.996초 수행되었고, 초당 16,666 시퀀스 발급이 이루어졌네요!!

2. Remote test

거실에 있는 블로그 서버로의 네트워크 레이턴시는 대략 아래와 같습니다. 04~0.5ms 사이를 왔다갔다 하는듯..

1 2 3

$ 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

이 환경에서 위와 동일한 테스트 트래픽을 발생시켜보았습니다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

$ 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 mysqlslap -utest --password=test123 --host=10.5.5.11 --concurrency=10      0.43s user 0.44s system 11% cpu 7.238 total

7.191초 수행하였고, 초당 13,906건 정도 시퀀스 발급이 이루어졌습니다.

개인적인 생각으로는.. 단일 시퀀스 성능으로는 이정도도 나쁘지 않다고 생각합니다만.. ^^ 만약 시퀀스 자체가 이렇게 공유하는 개념이 아닌 개인별로 할당되는 구조로 관리된다면..? row lock으로 인한 불필요한 대기를 어느정도 줄여줄 수 있을 것으로 생각되네요.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

$ 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 mysqlslap -utest --password=test123 --host=10.5.5.11 --concurrency=10      0.40s user 0.45s system 14% cpu 5.767 total

앞서 7.2초 걸리던 결과를 5.7초 정도로 처리하였는데. 만약 네트워크 레이턴시가 많이 안좋은 환경에서는 Lock으로 인한 대기를 크게 경감시킴으로써 훨씬 더 좋은 효과를 보여줄 것이라 생각합니다.

Concluion

MySQL에 없는 시퀀스를 서비스 요구사항에 맞게 좀더 재미나게 만들어보자라는 생각으로 시작하였습니다.
특정 서비스건, 개인화 서비스건.. 0시 기준으로 새롭게 1부터 시작해야하는 시퀀스 요구사항을 가끔 듣기는 했습니다. 이럴때 기존이라면, 락을 걸고, 현재 시퀀스 값을 가지고 리셋 처리 여부를 결정해야할 것인데.. 여기서는 이것을 간단하게 단건의 INSERT 구문으로 해결을 하였습니다.

필요에 따라.. 특정 이벤트의 개인화 테이블에.. 최근 1시간동안 10회 이상이면 다시 1부터 시작하는 이상스러운 시퀀스도 재미나게 만들어볼 수 있을 듯 하네요.