이번 시간에는 금방 끝나는 ALTER가 왜 서비스 쿼리를 멈추게 하는지 직접 재현하고, ALTER가 오래 기다리지 않게 막는 설정까지 확인하며 배워가는 시간입니다.
예를 들어서 주문 테이블에 메모 컬럼 하나를 추가하는 배포가 나갔다고 해볼게요. 컬럼 추가는 데이터를 건드리지 않아 순식간에 끝난다고 알고 있었어요. 그런데 배포 직후 주문 조회 API가 전부 타임아웃을 내기 시작합니다.
데이터베이스 CPU도 디스크도 한가한데, 연결 목록에는 Waiting for table metadata lock 상태의 쿼리만 쌓여 있어요.
실습 준비
이 글의 명령은 모두 실습 코드의 mysql-online-ddl-mdl/ 디렉터리에서 실행합니다.
compose.yaml: MySQL 8.4 컨테이너setup.sql: 주문 테이블과 200만 행 생성alter_timeout.sql:lock_wait_timeout을 3초로 줄여 ALTER를 실행하고 기다린 시간 측정
먼저 Docker로 MySQL 8.4를 띄울게요. 다른 설정은 기본값 그대로 두고, 컨테이너 메모리만 2GB로 제한했어요. name으로 compose 프로젝트 이름을 myddl로 정해 두었습니다. compose.yaml은 이렇게 생겼어요.
name: myddl
services:
mysql:
image: mysql:8.4
environment:
MYSQL_ROOT_PASSWORD: root
ports:
- "7380:3306"
mem_limit: 2g
setup.sql은 주문 테이블 shop.orders를 만들고 200만 행을 넣어요. 재귀 CTE로 번호를 만들어 한 번에 넣습니다.
이제 컨테이너를 띄우고 데이터를 넣을게요. 터미널에서 쓸 셸 함수도 함께 만들어 놓습니다.
sql은 컨테이너 안의 mysql 클라이언트를 실행하는 함수예요. MYSQL_PWD 환경 변수로 비밀번호를 넘기는 방식은 실습에서만 써 주세요. 아래 블록은 파일로 만들지 않고 실습 디렉터리에서 연 터미널에 그대로 붙여 넣어 실행합니다. 이후 명령은 따로 말하지 않는 한 이 터미널에서 실행하고, 터미널을 새로 열면 다시 붙여 넣습니다.
docker compose up -d
sql() { docker compose exec -T -e MYSQL_PWD=root mysql mysql -uroot "$@"; }
sql < setup.sql
sql -t -e "ANALYZE TABLE shop.orders;" > /dev/null
sql -t -e "SELECT TABLE_ROWS, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS size_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'orders';"
sql -t -e "SELECT @@version, @@lock_wait_timeout, @@innodb_lock_wait_timeout;"
+------------+---------+
| TABLE_ROWS | size_mb |
+------------+---------+
| 1986476 | 214 |
+------------+---------+
+-----------+---------------------+----------------------------+
| @@version | @@lock_wait_timeout | @@innodb_lock_wait_timeout |
+-----------+---------------------+----------------------------+
| 8.4.11 | 31536000 | 50 |
+-----------+---------------------+----------------------------+
TABLE_ROWS는 통계로 낸 추정값이라 200만과 조금 달라요. 테이블 크기는 약 214MB입니다.
이번 실습에서는 여러 세션이 동시에 움직이는 상황을 만들어야 해요. 그래서 터미널을 여러 개 열고 각각 mysql 클라이언트로 접속해 주세요.
이 글에서는 이 연결들을 세션 A, B, C, D, E라고 부를게요. 상태 조회는 또 다른 터미널에서 sql 함수로 합니다. 함수가 필요한 터미널마다 앞의 셸 함수 블록을 다시 붙여 넣어 주시면 됩니다.
docker compose exec mysql mysql -uroot -proot
실습 1: INSTANT 컬럼 추가는 바로 끝납니다
먼저 note1 컬럼을 INSTANT로 추가했다가 지워 볼게요. 세션 A에서 실행합니다.
ALTER TABLE shop.orders ADD COLUMN note1 VARCHAR(20), ALGORITHM=INSTANT;
ALTER TABLE shop.orders DROP COLUMN note1, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
200만 행 테이블인데 추가와 삭제가 각각 0.02초, 0.01초에 끝났어요. InnoDB는 행을 다시 쓰지 않고, 각 행이 어느 버전의 구조로 저장됐는지 기억해 두거든요.
INSTANT는 테이블 크기와 상관없이 바로 끝나요.
메타데이터 락이란
메타데이터 락은 테이블 정의를 보호하는 잠금이에요. 어떤 세션이 테이블을 읽고 쓰는 동안, 다른 세션이 그 테이블의 구조를 바꾸거나 지우지 못하게 막습니다.
- SELECT는
SHARED_READ, INSERT·UPDATE·DELETE는SHARED_WRITE를 얻어요. 서로 막지 않습니다. - ALTER TABLE은 시작할 때
SHARED_UPGRADABLE을 얻어요. 다른 세션의 읽기·쓰기와 함께 쥘 수 있지만, 다른 ALTER와는 함께 쥘 수 없습니다. - 테이블 정의를 실제로 바꾸는 순간에는
EXCLUSIVE(배타 락)가 필요해요. 다른 어떤 메타데이터 락과도 함께 쥘 수 없습니다.
Online DDL은 ALTER가 실행되는 동안 다른 세션의 읽기·쓰기를 허용하는 방식이에요. 행을 다시 쓰지 않는 ALGORITHM=INSTANT와 테이블을 다시 만드는 ALGORITHM=INPLACE가 여기에 해당해요.
INSTANT든 INPLACE든 Online DDL에는 배타 락이 필요한 순간이 있어요. 배타 락을 쥐는 시간은 짧지만, 얻기까지 얼마나 기다릴지는 다른 세션에 달려 있습니다. 여기에 규칙 두 가지가 관여해요.
- 트랜잭션 안에서 얻은 메타데이터 락은 문장이 끝나도 놓지 않고, 트랜잭션이 끝날 때 놓아요.
autocommit으로 실행한 문장은 문장이 끝날 때 놓습니다. - 쓰기 쪽 잠금 요청은 읽기 쪽 잠금 요청보다 우선해요. 기다리는 배타 락 요청이 있으면, 그 뒤에 온 요청은 지금 당장 함께 쥘 수 있는 잠금이라도 기다립니다.
실습 2: 열린 트랜잭션 하나가 뒤의 쿼리를 모두 막습니다
이제 처음 이야기한 상황을 재현해 볼게요. 세션 A에서 트랜잭션을 열고 주문 한 건을 읽은 뒤, 커밋하지 않고 둡니다.
애플리케이션이 트랜잭션 안에서 주문을 읽고 외부 결제 API 응답을 기다리는 상황이라고 보시면 돼요. 먼저 각 세션의 연결 ID를 확인해 둘게요.
세션 A, B, C, D, E에서 각각 실행합니다.
SELECT CONNECTION_ID();
세션 A는 107, B는 108, C는 109, D는 110, E는 111이었어요. 번호는 그전까지 연 연결 수에 따라 달라지니, 따라 하실 때는 각자 확인한 번호로 읽어 주세요.
세션 A에서 실행합니다.
BEGIN;
SELECT * FROM shop.orders WHERE id = 1;
이 상태에서 세션 B에서 INSTANT로 컬럼을 추가해 주세요.
ALTER TABLE shop.orders ADD COLUMN note2 VARCHAR(20), ALGORITHM=INSTANT;
세션 C에서 주문을 하나 조회합니다.
SELECT id FROM shop.orders WHERE id = 2;
세션 D에서는 다른 주문을 조회합니다.
SELECT id FROM shop.orders WHERE id = 3;
세션 E에서도 다른 주문을 조회합니다.
SELECT id FROM shop.orders WHERE id = 4;
넷 모두 결과가 나오지 않고 멈춰요.
다른 터미널에서 실행 중인 쿼리를 볼게요.
sql -t -e "SELECT ID, TIME, STATE, INFO FROM performance_schema.processlist WHERE INFO LIKE '%shop.orders%' AND INFO NOT LIKE '%processlist%' ORDER BY ID;"
+-----+------+---------------------------------+-------------------------------------------------------------------------+
| ID | TIME | STATE | INFO |
+-----+------+---------------------------------+-------------------------------------------------------------------------+
| 108 | 6 | Waiting for table metadata lock | ALTER TABLE shop.orders ADD COLUMN note2 VARCHAR(20), ALGORITHM=INSTANT |
| 109 | 6 | Waiting for table metadata lock | SELECT id FROM shop.orders WHERE id = 2 |
| 110 | 6 | Waiting for table metadata lock | SELECT id FROM shop.orders WHERE id = 3 |
| 111 | 5 | Waiting for table metadata lock | SELECT id FROM shop.orders WHERE id = 4 |
+-----+------+---------------------------------+-------------------------------------------------------------------------+
ALTER와 SELECT 세 개가 모두 Waiting for table metadata lock 상태예요.
그런데 원인인 세션 A(107)는 이 목록에 없습니다. 왜 그럴까요? 지금 실행 중인 쿼리가 없어서 INFO가 비어 있기 때문이에요. 그래서 실행 중인 쿼리만 보면 원인을 찾을 수 없습니다.
이번에는 메타데이터 락을 직접 볼게요. performance_schema.metadata_locks는 지금 쥐고 있거나 기다리는 메타데이터 락을 한 줄씩 보여 줘요. 계측 항목 wait/lock/metadata/sql/mdl이 켜져 있어야 하는데, MySQL 8.4에서는 기본으로 켜져 있습니다.
sql -t -e "SELECT OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA = 'shop' AND OBJECT_NAME = 'orders' ORDER BY OWNER_THREAD_ID, LOCK_STATUS;"
+-------------+-------------------+---------------+-------------+-----------------+
| OBJECT_NAME | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | OWNER_THREAD_ID |
+-------------+-------------------+---------------+-------------+-----------------+
| orders | SHARED_READ | TRANSACTION | GRANTED | 187 |
| orders | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 188 |
| orders | EXCLUSIVE | TRANSACTION | PENDING | 188 |
| orders | SHARED_READ | TRANSACTION | PENDING | 189 |
| orders | SHARED_READ | TRANSACTION | PENDING | 190 |
| orders | SHARED_READ | TRANSACTION | PENDING | 191 |
+-------------+-------------------+---------------+-------------+-----------------+
열은 이렇게 읽으시면 됩니다.
LOCK_STATUS:GRANTED는 쥐고 있는 잠금,PENDING은 기다리는 잠금이에요.OWNER_THREAD_ID: 잠금을 가진 스레드예요. 연결 ID가 아니라 performance_schema의 스레드 ID입니다.
이제 위에서부터 차례로 읽어 볼게요.
- 187(세션 A)은
SHARED_READ를 쥐고 있어요. SELECT는 끝났지만 트랜잭션이 열려 있어서 놓지 않았습니다. - 188(세션 B)은
SHARED_UPGRADABLE을 이미 얻었고,EXCLUSIVE로 올리려고 기다려요. A의SHARED_READ와 함께 쥘 수 없기 때문입니다. - 189~191(세션 C, D, E)은
SHARED_READ를 기다려요. A가 쥔 것과 같은 종류라 A와는 충돌하지 않지만, 앞에 기다리는 B의EXCLUSIVE요청이 있어서 그 뒤에서 기다립니다.
이제 세션 A에서 커밋해 볼게요.
COMMIT;
멈춰 있던 세션들이 곧바로 끝나요.
-- 세션 B
Query OK, 0 rows affected (7.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 세션 C
+----+
| id |
+----+
| 2 |
+----+
1 row in set (6.91 sec)
-- 세션 D
+----+
| id |
+----+
| 3 |
+----+
1 row in set (6.73 sec)
-- 세션 E
+----+
| id |
+----+
| 4 |
+----+
1 row in set (6.54 sec)
출력을 보면 0.01~0.02초면 끝나는 ALTER가 7.11초 걸렸고, 기본 키로 한 행을 읽는 SELECT가 6.5~6.9초씩 걸렸어요. 각 세션이 기다린 시간은 쿼리를 보낸 때부터 A가 커밋하기까지의 시간과 같습니다.
운영 중인 서버라면 이 사이에 이 테이블로 들어온 모든 쿼리가 같이 기다려요. 그리고 기다리는 쿼리마다 애플리케이션의 연결을 하나씩 붙잡고 있게 됩니다.
다음 실습에서 같은 컬럼을 다시 추가하니, note2 컬럼을 지워 둘게요.
sql -e "ALTER TABLE shop.orders DROP COLUMN note2;"
실습 3: lock_wait_timeout으로 기다리는 시간을 제한합니다
ALTER가 오래 기다리지 않도록 미리 막아 둘 수도 있어요. 메타데이터 락을 기다리는 최대 시간은 lock_wait_timeout이 정합니다.
- 기본값: 31536000초(1년). 사실상 끝없이 기다려요.
- 적용 대상: 테이블, 뷰, 저장 프로시저 등에 대한 DML과 DDL이 얻는 메타데이터 락. InnoDB 행 잠금에는 적용되지 않아요.
InnoDB 행 잠금을 기다리다 나는 1205는 innodb_lock_wait_timeout(기본 50초)이 정해요(궁금하면 lock wait timeout을 다룬 글을 참고하세요). 메타데이터 락은 lock_wait_timeout이라는 다른 설정을 따르지만, 시간을 넘기면 같은 1205 오류가 납니다.
alter_timeout.sql은 그 세션의 lock_wait_timeout만 3초로 줄인 뒤 note2 컬럼을 INSTANT로 추가해요. 그리고 ALTER가 기다린 시간을 waited_sec로 출력합니다.
세션 A에서 트랜잭션을 열고 주문을 읽어 둔 상태에서, 다른 터미널에서 이 파일을 실행해 주세요.
BEGIN;
SELECT * FROM shop.orders WHERE id = 1;
sql -t --force < alter_timeout.sql
ALTER가 기다리기 시작하면 세션 C에서 SELECT를 실행합니다.
SELECT id FROM shop.orders WHERE id = 2;
ERROR 1205 (HY000) at line 3: Lock wait timeout exceeded; try restarting transaction
+------------+
| waited_sec |
+------------+
| 3.0035 |
+------------+
-- 세션 C
+----+
| id |
+----+
| 2 |
+----+
1 row in set (2.49 sec)
ALTER는 3초를 기다린 뒤 1205로 실패했고, 컬럼은 추가되지 않았어요. C는 ALTER가 포기한 직후 끝났습니다.
C는 ALTER가 기다리기 시작하고 조금 뒤에 실행했기 때문에, 기다린 시간이 3초보다 약간 짧아요. 세션 A의 트랜잭션은 여전히 열려 있지만, 같은 SHARED_READ끼리는 충돌하지 않으니 C는 A와 함께 읽을 수 있습니다.
이렇게 lock_wait_timeout을 짧게 두면 뒤의 쿼리가 막히는 시간도 그만큼으로 줄어들어요. 실패한 ALTER는 아무것도 바꾸지 않았으니, 원인 트랜잭션을 정리하거나 잠시 뒤에 다시 실행하면 됩니다.
정리
범위: MySQL 8.4.11(mysql:8.4) 설정 기본값, 200만 행(약 214MB)인 shop.orders 테이블.
- 열린 트랜잭션 하나가
SHARED_READ를 쥔 동안 0.01~0.02초짜리 INSTANT ALTER는 7.11초, 그 뒤에 온 기본 키 SELECT는 6.5~6.9초를 기다렸어요. 뒤의 쿼리를 막는 것은 대기 중인 ALTER의EXCLUSIVE요청이라는 점을 기억해 두세요. - 이 글에 싣지 않은 추가 측정에서는 트랜잭션 없이 10초 걸리는 SELECT가 도는 동안에도 ALTER 뒤의 SELECT가 9.32초를 기다렸습니다. 오래 걸리는 통계 쿼리나 배치 조회가 도는 중에는 ALTER를 실행하지 마세요.
- ALTER 세션의
lock_wait_timeout을 3초로 줄이자 ALTER는 3.0035초 뒤 1205로 실패했고 뒤의 SELECT는 2.49초에 끝났습니다. ALTER를 실행하는 세션에서만lock_wait_timeout을 몇 초로 줄이고, 1205가 나면 열린 트랜잭션을 확인한 뒤 횟수를 제한해 다시 실행해 주세요.