글을 작성하게 된 계기
회사에서 테이블에 COMMENT를 추가 하다가 결제 트랜잭션이 밀려 연쇄 장애가 발생 했습니다. 이 과정에서 알게 된 내용을 정리하기 위해 글을 작성하게 되었습니다.
1. 문제 상황
회사에서 자주 사용되는 테이블에 COMMENT 추가 명령어를 실행 했습니다. 주석을 추가해 팀원들에게 도움을 주고 싶었는데요, 그랬더니 갑자기 결제가 연쇄적으로 밀리며 장애가 발생했습니다.
1
2
ALTER TABLE table
ADD COLUMN column INT(11) COMMENT '코멘트';
PG사에서 결제가 실패한 것은 꽤 큰 장애인데, 약 1분간, 결제가 지연되다 줄줄이 타임아웃이 발생했습니다. 단순히 COMMENT를 추가하는 작업인데 왜 이런 장애가 발생했을까요?
결제 과정에서 해당 테이블을 많이 조회 하지만 데이터 자체는 그렇게 많지 않았습니다.
2. 왜 결제 트랜잭션이 밀렸을까?
MySQL에서 ALTER TABLE ... COMMENT 작업은, 테이블의 데이터를 직접 변경하지 않고 메타데이터만 변경하는 간단한 작업처럼 보이지만, 메타데이터 락(Metadata Lock, MDL) 이라는 특수한 락을 요청합니다.
MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas, stored programs, tablespaces, user locks acquired with the GET_LOCK() function, and locks acquired with the locking service described in Section 7.6.9.1, “The Locking Service”.
이는 테이블 구조를 보호 하기 위해 사용되며, 모든 스키마 변경(DDL) 작업과 데이터 변경/조회(DML) 작업 간의 충돌을 방지 합니다. MySQL MDL은 두 가지로 나뉩니다.
Shared: 일반적 SELECT와 같은 읽기 연산은 Shared MDL을 얻습니다.Exclusive: ALTER TABLE과 같은 DDL 연산은 Exclusive MDL을 요청합니다.
즉, 이 잠금은 두 가지 유형의 작업이 충돌하지 않게 하기 위해 존재합니다.
DDL(데이터 정의): ALTER TABLE, DROP TABLE, CREATE INDEX 등DML(데이터 조작): SELECT, INSERT, UPDATE, DELETE 등
이를 당시 상황과 연관지어 보면, 주석 추가를 위해 ALTER TABLE을 실행했고, 테이블의 구조 변경 이 발생하며 EXCLUSIVE MDL 을 획득했습니다. 결제 과정에서 SELECT, INSERT, UPDATE 등의 DML 작업이 수행 됐고, 테이블에 걸려있는 메타데이터 락 때문에 결제 트랜잭션이 대기 상태에 빠지며 타임아웃(Timeout) 이 발생습니다. 이로 인해 결제 실패가 발생한 것이죠. 하필 해당 테이블에 row 수도 많아서 작업 시간도 오래 걸렸고요.
정리하면 ALTER TABLE이 사용량이 많은 테이블에서 수행되며, 기존 결제 트랜잭션과 MDL 충돌이 발생했고, 이로 인해 전체 서비스의 트랜잭션이 연쇄적으로 블로킹 된 것입니다. 팀원들에게 도움을 주기 위해 작업한 내용이 장애를 발생시킨 것이죠.
부서장님께서 다음부터 조심하라는 말 외에는 일절 하지 않으셨는데, 그래서 더 죄송했습니다.
참고로 performance_schema 를 조회하면 락의 상태를 확인할 수 있습니다.
1
2
3
4
5
6
7
8
9
mysql> SELECT object_schema, object_name, lock_type, lock_status,
owner_thread_id FROM performance_schema.metadata_locks WHERE object_name = 'test_mdl';
+---------------+-------------+-------------------+-------------+-----------------+
| object_schema | object_name | lock_type | lock_status | owner_thread_id |
+---------------+-------------+-------------------+-------------+-----------------+
| test | test_mdl | SHARED_READ | GRANTED | 5584 |
| test | test_mdl | SHARED_UPGRADABLE | GRANTED | 5583 |
| test | test_mdl | EXCLUSIVE | PENDING | 5583 |
+---------------+-------------+-------------------+-------------+-----------------+
이를 활용하면 EXCLUSIVE MDL이 PENDING 상태일 때, 뒤에 오는 다른 트랜잭션이 대기 상태가 되는 것을 확인할 수 있습니다.
1
2
3
4
5
6
7
8
9
10
mysql> SELECT object_schema, object_name, lock_type, lock_status,
owner_thread_id FROM performance_schema.metadata_locks WHERE object_name = 'test_mdl';
+---------------+-------------+-------------------+-------------+-----------------+
| object_schema | object_name | lock_type | lock_status | owner_thread_id |
+---------------+-------------+-------------------+-------------+-----------------+
| test | test_mdl | SHARED_READ | GRANTED | 5584 |
| test | test_mdl | SHARED_UPGRADABLE | GRANTED | 5583 |
| test | test_mdl | EXCLUSIVE | PENDING | 5583 |
| test | test_mdl | SHARED_READ | PENDING | 5596 |
+---------------+-------------+-------------------+-------------+-----------------+
3. 문제 해결
회사에 DBA가 존재하기 때문에 제가 직접 문제를 해결하진 않지만, 해결책을 찾아보았는데, 이도 함께 살펴보겠습니다.
- COPY
- INPLACE
- INSTANT
3-1. COPY
COPY 알고리즘은 ALTER TABLE 작업을 수행할 때 원본 테이블을 그대로 두고 새로운 임시 테이블을 만든 다음, 원본 테이블의 데이터를 한 행씩 전부 복사하여 옮긴 후에, 원본 테이블을 삭제하고 새로 만든 테이블로 교체하는 방식입니다.
Operations are performed on a copy of the original table, and table data is copied from the original table to the new table row by row. Concurrent DML is not permitted.
이는 모든 데이터를 복사하는 특성 때문에 테이블 크기가 크면 작업 시간이 매우 오래 걸릴 수 있습니다. 또한 복사 작업이 진행되는 동안 다른 데이터 변경 작업(DML:INSERT, UPDATE, DELETE)은 허용되지 않기 때문에, 서비스에 큰 영향을 줄 수 있습니다. 주로 테이블 구조 변경이 복잡하거나, 스토리지 엔진 변경과 같은 중대한 작업 시 사용됩니다.
1
ALTER TABLE test_mdl ADD COLUMN age INT, ALGORITHM=COPY;
3-2. INPLACE
INPLACE 알고리즘은 데이터를 복사하는 과정을 최소화하면서 테이블을 재구성하는 방식입니다. 이 알고리즘은 원본 테이블을 그대로 둔 상태에서 필요한 부분만 변경하거나 재배치하여, 데이터를 전체적으로 복사하지는 않지만 내부에서 데이터를 재정리하는 과정이 있을 수 있습니다.
Operations avoid copying table data but may rebuild the table in place. An exclusive metadata lock on the table may be taken briefly during preparation and execution phases of the operation. Typically, concurrent DML is supported.
작업을 수행하는 동안 아주 짧은 시간 동안 독점적인 메타데이터 락(Exclusive MDL)이 발생할 수 있지만, 일반적으로 데이터 변경 작업(DML)이 동시에 가능하기 때문에 서비스에 미치는 영향이 COPY 알고리즘보다 적습니다. 대부분의 인덱스 추가나 삭제, 컬럼의 데이터 타입 변경 같은 작업에서 사용됩니다.
1
ALTER TABLE test_mdl ADD INDEX idx_name (name), ALGORITHM=INPLACE;
3-3. INSTANT
INSTANT 알고리즘은 테이블의 데이터를 전혀 변경하지 않고 MySQL 내부의 메타데이터만 즉시 변경하는 방식입니다.
Operations only modify metadata in the data dictionary. An exclusive metadata lock on the table may be taken briefly during the execution phase of the operation. Table data is unaffected, making operations instantaneous. Concurrent DML is permitted.
예를 들어, 새로운 컬럼을 추가하거나 테이블의 코멘트를 변경하는 등 데이터 변경이 필요 없는 간단한 작업에서 주로 사용됩니다. 이 알고리즘은 작업 시간이 즉시 끝나며, 데이터가 전혀 복사되지 않기 때문에 성능상 가장 빠릅니다. 다만, 짧은 순간이지만 Exclusive MDL이 걸릴 수 있습니다. 그러나 이 시간이 매우 짧아서 다른 트랜잭션과의 충돌이 최소화되어 실제 운영 환경에서 가장 권장되는 알고리즘입니다.
1
ALTER TABLE test_mdl COMMENT = 'updated with INSTANT' ALGORITHM=INSTANT;
4. 정말 Lock이 발생할까?
이론적으로는 ALTER TABLE 시, Lock이 발생한다고 하지만, 실제로 이를 눈으로 확인해보겠습니다. 이를 위해 테이블을 하나 생성한 후, 데이터를 삽입합니다.
1
2
3
4
CREATE TABLE test_mdl (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10)
) ENGINE=InnoDB;
1
INSERT INTO test_mdl(name) VALUES ('a'), ('b'), ('c');
이후 BEGIN TRANSACTION으로 트랜잭션을 시작하고(1), ALTER TABLE을 실행한 후(2), performance_schema를 통해 MDL Lock을 확인합니다(3).
그러면 EXCLUSIVE MDL Lock이 발생하는 것을 확인할 수 있습니다.
1
2
3
4
5
6
7
8
9
mysql> SELECT object_schema, object_name, lock_type, lock_status,
owner_thread_id FROM performance_schema.metadata_locks WHERE object_name = 'test_mdl';
+---------------+-------------+-------------------+-------------+-----------------+
| object_schema | object_name | lock_type | lock_status | owner_thread_id |
+---------------+-------------+-------------------+-------------+-----------------+
| test | test_mdl | SHARED_READ | GRANTED | 5584 |
| test | test_mdl | SHARED_UPGRADABLE | GRANTED | 5583 |
| test | test_mdl | EXCLUSIVE | PENDING | 5583 |
+---------------+-------------+-------------------+-------------+-----------------+
다른 shell 창을 킨 후, 추가 작업을 하더라도 락이 걸려 지연되는 것을 볼 수 있습니다.
참고로 MySQL의 MDL은 여러 종류가 있습니다.
SHARED_READ: SELECT와 같은 일반 읽기 작업이 획득합니다.SHARED_WRITE: INSERT, UPDATE, DELETE와 같은 쓰기 작업이 획득합니다.EXCLUSIVE: ALTER TABLE 같은 DDL 작업이 최종적으로 필요로 하는 배타적인 락입니다. DDL 작업이 EXCLUSIVE 락을 얻기 위해 대기하는 동안 잠시 얻어놓는 임시적인 락이며, 이를 획득한 이후에 곧바로 EXCLUSIVE 락을 얻으려고 시도합니다.SHARED_UPGRADABLE: DDL이 초기에 획득하는 중간 단계의 락입니다.
참고로 위에 배운 알고리즘을 사용해도 여전히 락은 걸리는데요, MySQL에서 ALGORITHM=INSTANT를 명시했음에도 불구하고 ALTER TABLE이 멈춰 있는 이유는 INSTANT 알고리즘이 사용되더라도 MDL은 필요하기 때문입니다.
즉, 알고리즘을 사용해도 MDL은 여전히 걸리는 것이죠.
5. 정리
회사에서 꽤 큰 장애를 냈는데요, 테이블에 주석을 추가하는 작업이 결제 트랜잭션을 밀어내며 연쇄적인 장애를 발생시켰습니다. 이는 MySQL의 MDL 락 때문인데, ALTER TABLE과 같은 DDL 작업은 EXCLUSIVE MDL을 요청하며, 이로 인해 다른 DML 작업이 대기 상태에 빠지게 됩니다. 이로 인해 결제 트랜잭션이 밀리며 장애가 발생한 것입니다.
장애가 발생해도 별 말 안하고 지나가는 부서장님. 죄송하고 감사합니다.
이전에 동균님이 한 번 설명했을 때, 그런가보다 하고 지나쳤는데요, 딱 제가 장애를 발생시켰네요. 앞으로는 이런 작업을 할 때, DBA와 함께 검토하고 진행해야겠습니다.