MySQL Online-DDL

당근 마켓의 서비스는 쉬지 않고 발전하고 있어요. 하지만 이런 소프트웨어의 개선 작업은 단순히 버튼 한번 클릭으로 완성되는 것은 아니에요. 때로는 마치 전쟁을 치루는 것 같은 과정을 거치는 경우도 많아요.

누군가 “소프트웨어 엔지니어는 달리는 기차의 바퀴를 바꿔 끼우는 사람들”이라는 이야기를 했던 기억이 나는데요. 그 중에서도 DBMS의 구조 변경은 단연코 기차의 바퀴를 빼고 바꾸는 핵심 작업이라고 할 수 있으며, 특히 대용량 테이블의 구조 변경 작업은 “서비스 영향도 최소화”와 “최단 시간 완료” 사이에서 최적의 지점(Sweet spot)을 찾아야 해요. 오늘은 MySQL 서버가 서비스중인 상태에서, 테이블의 구조를 변경하는 작업에서 주의해야 할 이야기를 하나 하려고 해요.

문장의 간결함을 위해서, 이제부터는 높임말은 생략할게요.

대부분의 MySQL 서버 사용자가 알고 있듯이, MySQL 서버는 2011년부터 Online DDL이라는 기능을 제공하고 있다. Online DDL은 테이블의 구조를 변경(스키마 변경)을 실행하는 도중에도, INSERT나 UPDATE와 같은 DML들을 실행할 수 있도록 해주는 기능이다. MySQL 서버에서 Online DDL이 지원되지 않았던 시절에는 pt-online-schema-change와 같은 3-rd party 도구들을 이용해야 했지만, (이런 도구들은 불필요한 작업이 너무 많이 수반되어 서비스 영향도가 높은 편이어서) 이제는 대부분의 경우 MySQL 서버의 Online DDL을 이용한다.

MySQL 서버의 Online DDL은 필수적인 기능이지만, MySQL 서버가 Online DDL을 지원하기 시작한지 10년 넘어가는 지금에도 치명적인 문제점을 가지고 있다. 그 문제점이 왜 발생하는지, 어떤 경우에 Online DDL 보다는 pt-online-schema-change 도구를 선택해야 하는지를 살펴보도록 하자.

MySQL 서버의 Online DDL 메뉴얼을 살펴보면, 다음과 같이 알쏭 달쏭한 제한 사항이 숨어 있다.

When running an online DDL operation, the thread that runs the ALTER TABLE statement applies an online log of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the online log. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.

한글 번역은 쉽게 되지만, 메뉴얼의 “only temporary”는 어떤 케이스인지 그리고 왜 그렇게 작동하는지에 대한 설명과 예시가 없어서, 어떤 경우에 주의해야 하는지 예측할 수가 없다. 그래서 메뉴얼의 “duplicate entry in only temporary”가 어떤 케이스를 의미하는지를 예제로 살펴보도록 하겠다.

아래와 같은 구조를 가진 테이블에서, PRIMARY KEY와 UNIQUE INDEX를 서로 맞바꾸는 경우를 한번 생각해보자.

-- // 테이블의 구조
CREATE TABLE test.dup_error(
    id INT AUTO_INCREMENT NOT NULL, 
    uid INT NOT NULL, 
    v DATETIME, 
    PRIMARY KEY (id),
    UNIQUE KEY ux_uid (uid)
);
-- // 인덱스 교체를 위한 Online DDL
ALTER TABLE test.dup_error 
    DROP PRIMARY KEY, 
    DROP INDEX ux_uid, 
    ADD PRIMARY KEY(uid), 
    ADD UNIQUE INDEX ux_id(id), 
    ALGORITHM=inplace, LOCK=none;

아마도 다른 컨넥션에서 INSERT나 UPDATE와 같은 DML이 실행되지 않는다면, 특별히 문제없이 스키마 변경은 완료될 것이다. 물론 테이블의 레코드가 매우 많다면 시간이 상당히 걸릴수도 있다. 그렇다 하더라도 언젠가는 Online DDL은 완료될 것이고, PRIMARY KEY와 UNIQUE INDEX는 교체되어 있을 것이다.

Online DDL을 사용하는 이유는 스키마 변경을 실행하는 중에도 INSERT나 UPDATE와 같은 DML이 실행될 수 있도록 하기 위함이다. 그래서 Online DDL이 실행되는 중에도 DML은 계속 유입될 것이다. 물론 DML이 너무 빈번하게 실행된다면, 변경 내용을 임시로 저장하는 로깅용 버퍼(innodb_online_alter_log_max_size 시스템 변수로 크기 조절)의 공간이 부족해서 실패할 수도 있다. 하지만 이는 우리에게 너무 익숙한 제약이므로, 여기에서는 설명을 생략하도록 하겠다.

그런데 Online DDL의 마지막 단계 작업(임시 버퍼에 로깅된 변경 내역을 테이블로 적용하는 단계)중 아래와 같은 에러가 발생하면서 실패하는 경우를 가끔 보았을 것이다.

ERROR 1062 (23000) at line 1: 
  Duplicate entry '1' for key 'dup_error.PRIMARY'

문제는 이 에러는 Online DDL 작업의 제일 마지막 마무리 단계에서만 발생하기 때문에, 불필요한 시간 낭비를 초래할 수도 있다. 예를 들어서, Online DDL 작업이 3~4 시간이 걸리는 작업인데 문제를 유발하는 DML이 Online DDL이 시작되자 마자 발생했다면, 어차피 결국 실패할 운명의 Online DDL을 3~4 시간동안 가슴 졸이며 기다리게 되는 것이다.

또한 한번 이 에러가 발생하는 DBMS에서는 두번 세번 Online DDL을 다시 실행해도 에러가 발생할 가능성이 매우 높다. 정확한 원인을 알지 못하면, 시간 낭비 뿐만 아니라 DBMS 서버의 자원까지 낭비하게 되는 것이다. 하지만 이 에러의 원인에 대해서 MySQL 서버 메뉴얼은 쉽게 이해할 수 있는 설명이 없어서, 많은 사용자들이 대략 이런 경우 “Duplicate entry”가 발생한다는 감만 가지고 있는 것이다.

MySQL 서버의 Online DDL은 SQL 수준이 아니라 스토리지 엔진 수준에서 구현되었다. 그래서 Online DDL은 사용자가 실행한 DML 문장이 어떤 형태인지 알지 못하며, 중복 에러가 발생해서 실패했는지에 대한 정보를 갖지 못한다. 그래서 Online DDL은 스토리지 엔진 수준으로 들어오는 데이터 변경 정보를 임시 버퍼 공간에 쌓았다가, 최종 시점에 버퍼의 내용을 순서대로 적용(Apply)하는 방식으로 처리된다. 이런 처리 과정 때문에 Online DDL이 실행되는 동안 다른 컨넥션에서 (스키마가 변경되는 테이블에) 중복된 레코드를 INSERT하는 경우, 성공 또는 실패 여부와 관계없이 Online DDL은 최종적으로 실패하게 된다.

이 과정을 확인하기 위한 간단한 테스트 시나리오를 살펴보자.

## 테스트 테이블 생성
$ echo "DROP TABLE IF EXISTS test.dup_error; CREATE TABLE test.dup_error(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, uid INT NOT NULL, v DATETIME, UNIQUE KEY ux_uid(uid));" | mysql -u tester
 
## 테이블 용량 뻥튀기를 위해서 컬럼 추가
$ seq -f "ALTER TABLE test.dup_error ADD COLUMN c%02.0f CHAR(255) DEFAULT '';" 1 30 | mysql -u tester
## 테스트 데이터 적재 (빠른 적재를 위해서 옵션 조정 후 INSERT 실행)
$ for n in {1..200000}; do printf "INSERT INTO test.dup_error (id, uid) VALUES (%d, %d);\n" $n $n; done | mysql -u tester --init-command="SET sql_log_bin=off; SET GLOBAL innodb_flush_log_at_trx_commit=0;"

테스트 케이스 (1)

## 2개 터미널에서 동시에 실행

# 세션-1 (UNIQUE KEY중복으로 UPDATE되도록 INSERT .. ON DUPLICATE KEY 실행)
#       Online DDL 실행 후 1초후 실행되도록 "sleep 1" 먼저 실행)
session-1> SELECT SLEEP(1);
session-1> INSERT INTO test.dup_error (id, uid) VALUES (NULL, 1) 
             ON DUPLICATE KEY UPDATE v=NOW();
==> NO-ERROR
# 세션-2 (PK와 UNIQUE INDEX를 swap하는 DDL 실행)
session-2> ALTER TABLE test.dup_error 
             DROP PRIMARY KEY, 
             DROP INDEX ux_uid, 
             ADD UNIQUE INDEX ux_id(id), 
             ADD PRIMARY KEY(uid), 
             ALGORITHM=inplace, LOCK=none;
==> ERROR 1062 (23000) at line 1: 
      Duplicate entry '1' for key 'dup_error.PRIMARY'

테스트 케이스 (2)

## 2개 터미널에서 동시에 실행

# 세션-1 (UNIQUE KEY 중복으로 실패하는 INSERT 실행)
#       Online DDL 실행 후 1초후 실행되도록 "sleep 1" 먼저 실행)
session-1> SELECT SLEEP(1);
session-1> INSERT INTO test.dup_error (id, uid) VALUES (NULL, 1);
==> ERROR 1062 (23000) at line 1: 
      Duplicate entry '1' for key 'dup_error.ux_uid'
# 세션-2 (PK와 UNIQUE INDEX를 swap하는 DDL 실행)
session-2> ALTER TABLE test.dup_error 
             DROP PRIMARY KEY, 
             DROP INDEX ux_uid, 
             ADD UNIQUE INDEX ux_id(id), 
             ADD PRIMARY KEY(uid), 
             ALGORITHM=inplace, LOCK=none;
==> ERROR 1062 (23000) at line 1: 
      Duplicate entry '1' for key 'dup_error.PRIMARY'

테스트 케이스 (3)

## 2개 터미널에서 동시에 실행

# 세션-1 (UNIQUE KEY중복으로 UPDATE되도록 INSERT .. ON DUPLICATE KEY 실행)
#       Online DDL 실행 후 1초후 실행되도록 "sleep 1" 먼저 실행)
#       INSERT .. ON DUPLICATE KEY UPDATE 후, 롤백 실행
session-1> SELECT SLEEP(1);
session-1> BEGIN;
session-1> INSERT INTO test.dup_error (id, uid) VALUES (NULL, 1) 
             ON DUPLICATE KEY UPDATE v=NOW();
session-1> ROLLBACK;
==> NO-ERROR
# 세션-2 (PK와 UNIQUE INDEX를 swap하는 DDL 실행)
session-2> ALTER TABLE test.dup_error 
             DROP PRIMARY KEY, 
             DROP INDEX ux_uid, 
             ADD UNIQUE INDEX ux_id(id), 
             ADD PRIMARY KEY(uid), 
             ALGORITHM=inplace, LOCK=none;
==> ERROR 1062 (23000) at line 1: 
      Duplicate entry '1' for key 'dup_error.PRIMARY'

세번째 테스트 케이스에서는 에러없이 실행된 INSERT .. ON DUPLICATE KEY UPDATE 문장을 롤백해도, Online DDL은 에러가 발생하는 것을 확인할 수 있다.

3개의 테스트 케이스에서 살펴본 것처럼, UNIQUE KEY 인덱스에 중복되는 값이 한번이라도 저장만 되면 (설령 Duplicate key error로 실패하더라도), Online DDL 문장은 최종 단계에서 실패하게 된다. 즉, 중복 에러 발생으로 인해서 INSERT가 UPDATE 방식으로 처리되거나 또는 실패로 완료된 DML이라 하더라도, Online DDL에서는 INSERT + DELETE 방식으로 처리된다. 이 과정에서 INSERT가 실행되면 Duplicate Key Error가 발생하게 되는 것이다. 물론 바로 이어서 DELETE가 실행될 것이므로 Duplicate Key Error는 해소되겠지만, MySQL 서버에서 이런 상태가 허용될 수가 없기 때문에 Online DDL은 최종적으로 에러를 발생하게 되는 것이다. 이렇게 일시적인 중복 키 에러 상태를 MySQL 서버 메뉴얼에서는 “duplicate entry in only temporary”라고 표현한 것이다.

추가로 메뉴얼에서는 “it is possible to encounter a duplicate key entry error”로 “발생할 수 있음”이라고 명시하고 있지만, 위에서 살펴본 3개 테스트 케이스에 해당하면 Online DDL은 100% 실패한다. 물론 예외 케이스가 간혹 있긴 하지만, 메뉴얼의 “possible”은 실패 빈도를 너무 낮게 표현한 것처럼 느껴진다.

Online DDL이 실행되는 중 INSERT .. ON DUPLICATE KEY UPDATE 문장이 UPDATE로 처리되어도, 문제없이 완료되는 경우도 있다. INSERT .. ON DUPLICATE KEY UPDATE 문장에서 Auto-Increment PK 컬럼에 NULL을 사용하는 경우(INSERT 항목에 PK 컬럼을 명시하지 않고 묵시적으로 NULL이 저장되는 케이스 포함)에만 에러가 발생했으며, (아래 예제에서와 같이) 명시적으로 값을 저장하는 경우에는 Online DDL의 에러가 발생하지 않는다.

## 2개 터미널에서 동시에 실행

# 세션-1 (UNIQUE KEY중복으로 UPDATE되도록 INSERT .. ON DUPLICATE KEY 실행)
#       Online DDL 실행 후 1초후 실행되도록 "sleep 1" 먼저 실행)
#       PK 컬럼에 "1"을 명시적으로 저장
session-1> SELECT SLEEP(1);
session-1> INSERT INTO test.dup_error (id, uid) VALUES (1, 1) 
             ON DUPLICATE KEY UPDATE v=NOW();
==> NO-ERROR
# 세션-2 (PK와 UNIQUE INDEX를 swap하는 DDL 실행)
session-2> ALTER TABLE test.dup_error 
             DROP PRIMARY KEY, 
             DROP INDEX ux_uid, 
             ADD UNIQUE INDEX ux_id(id), 
             ADD PRIMARY KEY(uid), 
             ALGORITHM=inplace, LOCK=none;
==> NO-ERROR

INSERT .. ON DUPLICATE KEY UPDATE .. 문장이 사용되고 있다면, 먼저 SELECT 해서 INSERT를 할지 UPDATE를 할지 구분해서 실행하도록 쿼리를 바꿔서 사용하면 Online DDL의 실패를 막을 수 있을 듯 보인다. 하지만 이보다는 pt-online-schema-change를 이용해서 스키마 변경하는 방법이 더 나을 것으로 보인다. 테이블에 UNIQUE 인덱스를 생성하는 경우는 대부분 이런 케이스의 가능성을 내포하고 있다.

아마도 “pt-online-schema-change 사용하면 이런 고민 할 필요 없지 않을까?”라고 생각이 들 수도 있겠지만, pt-online-schema-change는 그 나름대로의 자원 낭비와 문제점을 가지고 있기 때문에 상황에 맞는 최적의 방법을 선택하는 것이 중요해 보인다.

이제 MySQL 서버의 메뉴얼에서 이야기하는 “duplicate entry in only temporary”가 이해될 것으로 보인다. 테스트 케이스를 보면 쉽게 이해되지만, 테스트 케이스를 보기 전에는 이해하기 어려운 경우라고 생각한다. 특히 세번째 테스트 케이스처럼, 롤백된 경우에도 3~4시간 뒤에 Online DDL은 Duplicate Key Error만 덩그러니 출력하고 실패해 버리는 것은 (왜 이렇게 밖에 할 수 없는지 이유를 알게 된 지금에도) 이해하기 어렵다. 빠른 시간내에 MySQL 서버의 Online DDL이 더 나은 형태로 개선되기를 기대해 본다.

간단한 이야기를 너무 장황하게 기술했다는 생각이 들 수도 있겠지만 이렇게 상세한 이야기를 기술한 것은, 실패 케이스를 회피하면서 Online DDL을 최적으로 활용하기 위해서는 문제의 원인을 정확하게 이해하는 것이 중요하기 때문이다.

이번 이야기도 재미있게 읽으셨다면, 그리고 더 많은 것들을 경험하고 싶으시다면, Real MySQL 오픈 챗 참여 또는 당근 마켓 개발자와 DBA로 지원해주세요.