MySQL AUTO_INCREMENT counter의 진실

현재 서비스에서 MySQL 5.7버전으로 데이터를 운용하고 있고, Storage Engine은 InnoDB를 사용하고 있다. 그리고 테이블들의 Primary Key(PK)는 모두 AUTO_INCREMENT로 설정되어 있다. 여기까지는 특별한 점이라곤 전혀 찾아볼 수 없는 아주 진부한 구성이다.

하지만 여기서 이 끝없이 올라갈 것이라고 믿었던 이 PK가 예전에 생성되었던 값부터 다시 반복되는(?) 충격적인 모습을 최근 관측하게 되었다.

그리고 DBA 분에 의해 최근 MySQL 서버 재기동이 있었음을 듣게 되었고, 그 시점이 이 현상이 발생하기 시작한 시점과 동일하다는 것을 알게되었다.

음.. 그런데 MySQL 서버 재기동이 왜?

사실 이 때만해도 재기동이 AUTO_INCREMENT에 영향을 줄 것이라 전혀 생각하지 못했었다. 당연히 MySQL에서는 재기동 뒤에도 이전 AUTO_INCREMENT된 값 이후로 잘 증가시켜줄 것이라는 막연한 믿음이 있었다.

DBA 분의 친절한 설명을 통해 AUTO_INCREMENT counter의 동작이 MySQL 8.0 이전과 이후 버전에 따라 다르다는 것을 알게 되었다.

(개인적으로 MySQL 5.7 바로 다음 공식 버전이 8.0이라는 것이 의아해서 좀 찾아봤는데 MySQL 공식문서 FAQ 에 아래와 같이 이유가 정리되어 있었다.)

Due to the many new and important features we were introducing in this MySQL version, we decided to start a fresh new series. As the series numbers 6 and 7 had actually been used before by MySQL, we went to 8.0.

실습

macOS Big Sur, M1 환경에서 실습하였고 Docker 20.10.7 버전을 사용하였습니다.

편리한 실습을 위해 각 버전별 MySQL Docker 컨테이너 를 띄워 아래 과정을 테스트해보았다.

1. 테이블 생성
2. Insert item1(pk: 1), item2(pk: 2), item3(pk: 3)
3. Delete item3(pk: 3)
4. MySQL 재기동
5. AUTO_INCREMENT 확인

MySQL 5.7

우선 재기동 이후에도 MySQL 데이터가 남아있어야하므로 docker volume을 생성하였다.

$ docker volume create mysql-5.7-volume

그리고 이 volume을 mount한 MySQL 5.7 버전 컨테이너를 띄운 후 mysql에 접속하였다.

$ docker pull --platform linux/amd64 mysql:5.7
$ docker run \
--platform linux/amd64 \
—-name mysql-5.7-server \
-e MYSQL_ROOT_PASSWORD=1234 \
-v mysql-5.7-volume:/var/lib/mysql \
-d -p 3306:3306 mysql:5.7

컨테이너의 mysql에 접속하여 1 ~ 3까지의 과정을 SQL로 실행하였다.

$ docker exec -it mysql-5.7-server bash
root@4c9bcddfbb64:/# mysql -u root -p  
mysql>  

mysql> -- 1. 테이블 생성  
mysql> USE mysql;  
mysql> CREATE TABLE jupiny_table (  
    -> id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(10) NOT NULL
    -> )
    -> ENGINE=InnoDB;
mysql> DESC jupiny_table;  
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
mysql> -- 2. Insert item1(pk: 1), item2(pk: 2), item3(pk: 3)  
mysql> INSERT INTO jupiny_table(name) VALUES('item1');  
mysql> INSERT INTO jupiny_table(name) VALUES('item2');  
mysql> INSERT INTO jupiny_table(name) VALUES('item3');  
mysql> SELECT * FROM jupiny_table;  
+----+-------+
| id | name  |
+----+-------+
|  1 | item1 |
|  2 | item2 |
|  3 | item3 |
+----+-------+
mysql> -- 3. Delete item3(pk: 3)  
mysql> DELETE FROM jupiny_table WHERE id=3;  
mysql> SELECT * FROM jupiny_table;  
+----+-------+
| id | name  |
+----+-------+
|  1 | item1 |
|  2 | item2 |
+----+-------+

아래와 같은 쿼리 다음 생성될 AUTO_INCREMENT 값을 미리 확인해볼 수 있다.

mysql> SELECT AUTO_INCREMENT  
    -> FROM information_schema.TABLES
    -> WHERE TABLE_SCHEMA="mysql"
    -> AND TABLE_NAME="jupiny_table";
+----------------+
| AUTO_INCREMENT |
+----------------+
|              4 |
+----------------+

컨테이너를 빠져나온 후, docker restart 로 mysql 서버를 재시작하였다.

$ docker restart mysql-5.7-server

다시 mysql에 접속하여 다음 생성될 AUTO_INCREMENT 값을 동일하게 확인해보았다.

mysql> SELECT AUTO_INCREMENT  
    -> FROM information_schema.TABLES
    -> WHERE TABLE_SCHEMA="mysql"
    -> AND TABLE_NAME="jupiny_table";
+----------------+
| AUTO_INCREMENT |
+----------------+
|              3 |
+----------------+

4가 아닌 3으로 변경되어 있음을 확인할 수 있다. 여기서 데이터를 추가로 insert해보면,

mysql> INSERT INTO jupiny_table(name) VALUES('item4');  
mysql> SELECT * FROM jupiny_table;  
+----+-------+
| id | name  |
+----+-------+
|  1 | item1 |
|  2 | item2 |
|  3 | item4 |
+----+-------+

3 부터 다시 AUTO_INCREMENT 값이 증가함을 확인할 수 있다.

MySQL 8.0

이번엔 MySQL 8.0 버전 컨테이너를 띄워보았다.

$ docker volume create mysql-8.0-volume
$ docker run \
--platform linux/amd64 \
--name mysql-8.0-server \
-e MYSQL_ROOT_PASSWORD=1234 \
-v mysql-8.0-volume:/var/lib/mysql \
-d -p 3306:3306 mysql:8.0

컨테이너의 mysql에 접속하여 1 ~ 3까지의 과정을 동일하게 실행해보았다.

item3을 delete한 후, 다음 생성될 AUTO_INCREMENT 값을 확인해보았다.

mysql> SELECT AUTO_INCREMENT  
    -> FROM information_schema.TABLES
    -> WHERE TABLE_SCHEMA="mysql"
    -> AND TABLE_NAME="jupiny_table";
+----------------+
| AUTO_INCREMENT |
+----------------+
|              4 |
+----------------+

그 후 8.0 컨테이너를 재시작한 후에

$ docker restart mysql-8.0-server

한번더 확인해보았지만, 재시작 전과 동일하게 그대로 4임을 확인할 수 있다.

mysql> SELECT AUTO_INCREMENT  
    -> FROM information_schema.TABLES
    -> WHERE TABLE_SCHEMA="mysql"
    -> AND TABLE_NAME="jupiny_table";
+----------------+
| AUTO_INCREMENT |
+----------------+
|              4 |
+----------------+

새로 insert한 데이터 역시 AUTO_INCREMENT 값이 4부터 계속 증가함을 확인할 수 있다.

mysql> INSERT INTO jupiny_table(name) VALUES('item4');  
mysql> SELECT * FROM jupiny_table;  
+----+-------+
| id | name  |
+----+-------+
|  1 | item1 |
|  2 | item2 |
|  4 | item4 |
+----+-------+

원인

위 현상의 원인은 두 버전에서의 AUTO_INCREMENT counter값을 저장하는 방식의 차이에 있다.

5.7 이하 버전에서 MySQL은 AUTO_INCREMENT counter값을 메모리에 저장하였다. 따라서 재기동 후에는 이 값이 소멸되기 때문에, 어쩔 수 없이 현재 테이블의 데이터 상태를 기반으로 새로 AUTO_INCREMENT counter 값을 다시 계산해야만 했다.

SELECT MAX(ai_col) FROM table_name FOR UPDATE;  

이렇게 현재 존재하는 AUTO_INCREMENT 컬럼의 최대값을 기준으로 counter가 초기화되기 때문에, 이후 삭제된 AUTO_INCREMENT 값으로 다시 증가했던 것이다.

8.0 이후부터는 AUTO_INCREMENT counter 값을 디스크에 저장하는 방식으로 변경되었다. (정확히는 redo log 에 쓰인다.) 그래서 재기동이 되더라도 counter값이 남아있기 때문에 한번 AUTO_INCREMENT된 값은 다시 생성될 수 없고 영원히 계속 증가하게 된다.

해결책

당연한 소리지만, 우선 이러한 문제가 해결된 최신버전인 MySQL 8.0 이후 버전을 사용하는 것이 가장 좋을것 같다. (기승전업그레이드 아니던가.)

하지만 어떠한 이유로 만약 아직 MySQL을 8.0 이상 버전으로 업그레이드를 못하고 이전 버전을 사용하고 있는 상태라면, 가급적이면 DELETE 쿼리를 사용해서 실제 record를 날려버리지 말고, 일종의 삭제 상태로 UPDATE하는 방식이 안전할 것 같다.

DB 서버 재기동이 흔한 일은 아니지만, 언제든 서버는 모종의 이슈(장애, Migration, 설정 변경 등)로 재기동이 발생할 수 있다는 것을 가정해야한다.

이때 만약 이전에 삭제되었던 PK들이 다시 등장하고 또 운나쁘게 이전에 이 PK들을 참조하고 있었던 데이터들이 있다면.. 디버깅하기도 힘든 생각만해도 아찔한 카오스가 실제 서비스에서 펼쳐질 수 있을 것이다.

출처

• InnoDB AUTO_INCREMENT Counter Initialization
• How to get the next auto-increment id in MySQL?