MySQL 컬럼 추가 전 확인할 것: INSTANT 적용 조건과 오류 1845 해결 실험
같은 테이블, 같은 날, 연달아 실행한 ALTER TABLE 두 개가 있습니다. 첫 번째는 31ms 만에 끝났고, 두 번째는 실행 즉시 오류 1845로 거부되었습니다. 데이터는 그대로 10만 행이었고, 두 문장 모두 컬럼 하나를 추가하는 작업이었습니다.
차이는 두 번째 문장에 인덱스 생성이 한 줄 더 들어 있었다는 것뿐입니다. 이 글은 MySQL 8.4.11에서 컬럼 추가 작업을 실제 작업 순서대로 진행한 로그입니다. 사전 점검부터 실패 원인 확인, 재실행, 운영 반영 때 겪은 메타데이터 잠금 대기까지 단계별로 정리했습니다.
| 단계 | 작업 | 결과 |
|---|---|---|
| STEP 0 | 사전 점검 | 10만 행, Dynamic, 행 구조 버전 0 |
| STEP 1 | order_status 추가 (INSTANT) | 성공 |
| STEP 2 | channel_code + 인덱스 동시 추가 (INSTANT) | 오류 1845 |
| STEP 3 | 실패 직후 상태 확인 | 아무것도 반영되지 않음 |
| STEP 4 | 컬럼(INSTANT)과 인덱스(INPLACE) 분리 실행 | 모두 성공 |
| STEP 5 | 운영 반영 | 메타데이터 잠금 대기 발생 후 재작업 |
STEP 0. ALTER 전에 테이블 상태부터 기록합니다
INSTANT 컬럼 추가가 가능한지는 MySQL 버전뿐 아니라 테이블 속성에 따라서도 달라집니다. 그래서 작업 전에 행 형식, 기존 인덱스, 그리고 TOTAL_ROW_VERSIONS를 한 번에 조회해 기록했습니다.
SELECT
VERSION() AS mysql_version,
@@session.autocommit AS autocommit_mode,
(SELECT COUNT(*) FROM lab47_instant.orders_ddl) AS actual_rows,
t.ENGINE AS engine,
t.ROW_FORMAT AS row_format,
i.TOTAL_ROW_VERSIONS AS row_versions,
(
SELECT GROUP_CONCAT(
CONCAT(s.INDEX_NAME, ':', s.COLUMN_NAME)
ORDER BY s.INDEX_NAME, s.SEQ_IN_INDEX
SEPARATOR ', '
)
FROM information_schema.STATISTICS AS s
WHERE s.TABLE_SCHEMA = 'lab47_instant'
AND s.TABLE_NAME = 'orders_ddl'
) AS index_definition
FROM information_schema.TABLES AS t
JOIN information_schema.INNODB_TABLES AS i
ON i.NAME = CONCAT(t.TABLE_SCHEMA, '/', t.TABLE_NAME)
WHERE t.TABLE_SCHEMA = 'lab47_instant'
AND t.TABLE_NAME = 'orders_ddl';
| 항목 | 확인값 |
|---|---|
| MySQL 버전 | 8.4.11 |
| 스키마·테이블 | lab47_instant.orders_ddl |
| 실제 행 수 | 100,000 (COUNT(*) 기준) |
| 엔진 / 행 형식 | InnoDB / Dynamic |
| 기존 인덱스 | PRIMARY(id), idx_customer_id(customer_id) |
| autocommit | true |
TOTAL_ROW_VERSIONS | 0 |
이 중 운영에서 가장 놓치기 쉬운 값이 TOTAL_ROW_VERSIONS입니다. INSTANT 방식으로 컬럼을 추가하거나 삭제할 때마다 1씩 늘어나는 행 구조 버전이며, 데이터 건수나 트랜잭션 수와는 관계가 없습니다. MySQL 8.4에서는 이 값이 64에 도달하면 더 이상 INSTANT로 컬럼을 추가하거나 삭제할 수 없습니다. 지금은 0이므로 여유가 충분합니다.
실험은 별도 스키마에서 동시 부하 없이 순차 실행했습니다. 시간은 SQL 클라이언트가 문장별로 표시한 단일 실행값입니다.
STEP 1. 중간 위치에 컬럼 추가: 성공
첫 작업은 주문 상태 컬럼을 customer_id 바로 뒤에 추가하는 것입니다. 알고리즘은 INSTANT로 명시했습니다.
ALTER TABLE lab47_instant.orders_ddl
ADD COLUMN order_status TINYINT UNSIGNED
NOT NULL DEFAULT 0 AFTER customer_id,
ALGORITHM = INSTANT;실행 로그 기준 31.771ms에 완료되었습니다. 적용 결과는 데이터와 메타데이터를 함께 조회해 확인했습니다.
10만 행 모두에서 order_status = 0이 조회되고, 컬럼 위치는 세 번째, 행 구조 버전은 0에서 1이 되었습니다. MySQL 8.0.29 이후로는 INSTANT로도 중간 위치에 컬럼을 넣을 수 있으므로, AFTER 지정은 실패 원인이 되지 않습니다.
여기서 “31ms 만에 10만 행에 기본값을 채웠다”고 해석하면 안 됩니다. INSTANT는 기존 행을 고치지 않고 데이터 사전에 새 컬럼 정의와 기본값만 기록합니다. 기존 행을 읽을 때는 그 행이 만들어진 행 구조 버전을 보고, 당시 없던 컬럼은 기록된 기본값으로 채워서 돌려줍니다. 행 구조 버전이 1 올라간 것이 바로 그 흔적입니다.
STEP 2. 컬럼과 인덱스를 한 문장에: 오류 1845
두 번째 작업은 판매 채널 컬럼과 그 컬럼의 인덱스를 함께 추가하는 것이었습니다. 하나의 ALTER로 처리하고, 앞과 똑같이 INSTANT를 명시했습니다.
ALTER TABLE lab47_instant.orders_ddl
ADD COLUMN channel_code TINYINT UNSIGNED
NOT NULL DEFAULT 1,
ADD INDEX idx_channel_code (channel_code),
ALGORITHM = INSTANT;
-- 결과
MySQL Error (1845): ALGORITHM=INSTANT is not supported for this operation.
Try ALGORITHM=COPY/INPLACE.직전 작업이 같은 테이블에서 성공했으니 테이블 크기나 행 형식 문제는 아닙니다. 달라진 것은 문장 안에 든 작업의 종류입니다.
컬럼 추가는 데이터 사전만 바꾸면 되지만, 보조 인덱스 생성은 10만 행을 실제로 읽어 정렬하고 새 B-Tree를 만들어야 합니다. 메타데이터만으로 끝낼 수 없는 작업이므로 INSTANT 대상이 아닙니다. MySQL은 ALTER 한 문장을 하나의 작업으로 보고 그 안의 모든 작업이 지원하는 알고리즘으로 실행하는데, 인덱스 생성 때문에 문장 전체가 INSTANT 조건을 잃은 것입니다.
오류 메시지는 COPY나 INPLACE를 써 보라고 안내합니다. 하지만 그 안내를 따라 알고리즘만 바꾸면, 컬럼 추가까지 테이블 재구성 방식으로 처리될 수 있습니다. 메시지가 알려 주는 것은 “이 조합은 INSTANT가 안 된다”는 사실이지, 어떤 방식으로 바꾸는 것이 맞는지는 아닙니다.
STEP 3. 실패한 ALTER가 무엇을 남겼는지 확인합니다
재실행 전에 반드시 확인할 것이 있습니다. 컬럼은 추가되고 인덱스만 실패한 상태인지, 아무것도 반영되지 않았는지입니다. 컬럼이 이미 생겼는데 같은 스크립트를 다시 돌리면 이번에는 중복 컬럼 오류가 납니다.
channel_column_exists와 channel_index_exists가 모두 0이고, 행 구조 버전도 1에 그대로 있습니다. 앞에서 성공한 order_status는 유지되었습니다. 오류 1845는 MySQL이 DDL을 시작하기 전에 지원 여부를 검사하는 단계에서 거부한 것이라, 문장 안의 어떤 작업도 반영되지 않았습니다.
이 확인은 실험에서만 필요한 절차가 아닙니다. 운영 배포 스크립트가 중간에 멈췄을 때 가장 먼저 해야 할 일이 바로 이 상태 조회입니다. 확인 쿼리는 INFORMATION_SCHEMA.COLUMNS, STATISTICS, INNODB_TABLES를 각각 조회해 존재 여부를 0과 1로 돌려주는 단순한 형태라 스크립트에 그대로 넣어 두기 좋습니다.
STEP 4. 컬럼은 INSTANT, 인덱스는 INPLACE로 나눠 실행
원인이 작업 조합이라면 해법은 문장을 나누는 것입니다. 컬럼 추가는 INSTANT를 유지하고, 인덱스 생성은 INPLACE와 LOCK=NONE을 지정해 동시 읽기·쓰기를 허용하도록 했습니다.
-- 1. 컬럼 추가는 INSTANT
ALTER TABLE lab47_instant.orders_ddl
ADD COLUMN channel_code TINYINT UNSIGNED
NOT NULL DEFAULT 1,
ALGORITHM = INSTANT;
-- 2. 보조 인덱스 생성은 INPLACE
ALTER TABLE lab47_instant.orders_ddl
ADD INDEX idx_channel_code (channel_code),
ALGORITHM = INPLACE,
LOCK = NONE;
두 문장 모두 성공했습니다. 컬럼 추가는 35.614ms, 인덱스 생성은 383.062ms가 표시되었습니다. 화면 하단의 583ms는 확인 쿼리까지 포함한 스크립트 전체 시간이라 문장별 시간과는 다른 값입니다.
인덱스 생성이 열 배 넘게 오래 걸렸다고 해서 INPLACE가 INSTANT보다 열 배 느리다는 뜻은 아닙니다. 하나는 정의만 기록하는 작업이고, 다른 하나는 10만 행을 읽어 인덱스를 만드는 작업입니다. 하는 일이 다르므로 시간을 성능 배수로 비교할 수 없습니다.
작업 성공 여부는 실행 시간이 아니라 결과 상태로 판단했습니다. 전체 흐름을 한 표로 정리하면 다음과 같습니다.
| 시점 | 실행 작업 | 알고리즘 | 결과 | channel 컬럼 / 인덱스 | 행 구조 버전 |
|---|---|---|---|---|---|
| STEP 0 | 사전 점검 | – | – | – / – | 0 |
| STEP 1 | order_status 추가 | INSTANT | 성공, 31.771ms | – / – | 1 |
| STEP 2·3 | channel_code + 인덱스 | INSTANT | 오류 1845 | 0 / 0 | 1 |
| STEP 4 | channel_code 추가 | INSTANT | 성공, 35.614ms | 1 / 0 | 2 |
| STEP 4 | idx_channel_code 생성 | INPLACE, LOCK=NONE | 성공, 383.062ms | 1 / 1 | 2 |
표에서 컬럼 추가 직후 “1 / 0” 상태가 보입니다. 분리 실행에는 이렇게 컬럼만 있고 인덱스는 없는 중간 상태가 반드시 생깁니다. 두 번째 문장이 실패하면 이 상태로 남으므로, 배포 스크립트는 단계마다 결과를 확인하고 다음 단계로 넘어가야 합니다.
행 구조 버전은 두 번째 INSTANT 추가로 2가 되었고, 인덱스 생성 후에도 2로 유지되었습니다. INPLACE 인덱스 생성은 테이블 전체를 재구성하지 않기 때문입니다. 반대로 테이블을 재구성하는 작업을 수행하면 이 값은 0으로 초기화됩니다.
STEP 5. 운영 반영: INSTANT도 잠금을 기다립니다
여기까지는 실험 환경의 결과입니다. 실제 운영에서는 같은 원칙으로 작업하면서도 실험에서는 볼 수 없던 문제를 한 번 겪었습니다.
INSTANT를 명시하고, 개발 환경에서 먼저 검증했습니다
운영에서 컬럼을 추가할 때는 항상 ALGORITHM=INSTANT를 명시했습니다. INSTANT로 처리할 수 없는 변경이라면 MySQL이 다른 방식으로 바꿔 진행하는 대신 즉시 오류를 돌려주고 작업이 취소되어야 했기 때문입니다. 알고리즘을 생략하면 MySQL은 가능한 방식 중 하나를 스스로 고르는데, 그 결과 운영 테이블 전체가 재구성될 수도 있습니다. STEP 2의 오류 1845가 바로 이 안전장치가 작동한 모습입니다.
또한 운영 스크립트는 개발 환경에서 먼저 테스트로 실행해 본 뒤 확정했습니다. 이 글의 STEP 0~4가 사실상 그 과정입니다. 작업 조합 때문에 INSTANT가 거부되는 경우를 개발 단계에서 발견하면 운영 작업 시간에 당황할 일이 없습니다.
커밋하지 않은 조회 하나가 DDL을 붙잡았습니다
운영 반영 시점에 ALTER가 끝나지 않고 메타데이터 잠금(MDL)을 기다리는 상황이 발생했습니다. 원인을 추적해 보니 한 개발자가 해당 테이블에 간단한 조회 쿼리를 실행한 뒤 커밋하지 않은 채 세션을 열어 두고 있었습니다.
autocommit이 꺼진 클라이언트에서는 SELECT 한 번만 실행해도 트랜잭션이 시작되고, 그 트랜잭션이 끝날 때까지 테이블에 대한 공유 메타데이터 잠금이 유지됩니다. INSTANT ADD COLUMN은 데이터를 건드리지 않지만, 테이블 정의를 바꾸는 순간에는 배타적 메타데이터 잠금이 필요합니다. 그래서 1초도 안 걸릴 작업이 그 조회 세션이 끝나기만을 기다리게 됩니다.
더 위험한 것은 그 다음입니다. 배타적 잠금을 기다리는 DDL이 대기열 앞에 서 있으면, 뒤이어 들어오는 일반 서비스 쿼리도 그 테이블의 잠금을 얻지 못하고 DDL 뒤에 줄을 섭니다. 커밋하지 않은 조회 하나 때문에 서비스 쿼리 전체가 멈출 수 있는 구조입니다.
그래서 당시에는 DDL 작업을 즉시 중단했습니다. 서비스 쿼리가 DDL 뒤에 쌓이는 것을 먼저 풀어야 했기 때문입니다. 이어서 잠금을 점유하고 있던 세션을 KILL한 뒤 ALTER를 다시 실행해 작업을 마쳤습니다.
이런 상황에서 잠금을 붙잡고 있는 세션은 sys 스키마의 뷰로 바로 찾을 수 있습니다.
SELECT
object_schema,
object_name,
waiting_pid,
waiting_query,
blocking_pid,
sql_kill_blocking_connection
FROM sys.schema_table_lock_waits;blocking_pid가 잠금을 점유한 세션이고, sql_kill_blocking_connection 컬럼에는 해당 세션을 종료하는 KILL 문이 만들어져 나옵니다. 다만 KILL하면 그 세션의 미커밋 작업은 롤백되므로, 단순 조회 세션인지 먼저 확인한 뒤 실행해야 합니다.
같은 일을 줄이려면 DDL 세션의 잠금 대기 시간을 짧게 잡아 두는 방법도 있습니다. lock_wait_timeout의 기본값은 31,536,000초, 즉 1년이라서 그대로 두면 DDL이 사실상 무한정 기다립니다.
-- DDL 실행 세션에서만 적용
SET SESSION lock_wait_timeout = 5;
ALTER TABLE lab47_instant.orders_ddl
ADD COLUMN order_memo VARCHAR(100) NULL,
ALGORITHM = INSTANT;이렇게 하면 5초 안에 잠금을 얻지 못한 DDL은 스스로 실패하고 물러나므로, 서비스 쿼리가 뒤에 오래 쌓이지 않습니다. 실패하면 점유 세션을 확인하고 정리한 뒤 다시 실행하면 됩니다. 이 설정은 이 글의 실험에서 측정한 것이 아니라, 위 경험을 바탕으로 덧붙이는 권장 사항입니다.
작업 전에 함께 확인할 INSTANT 제한 조건
이번 실험에서 재현한 것은 “작업 조합에 의한 거부”뿐입니다. MySQL 8.4 문서에는 다음과 같은 제한도 있으므로, STEP 0의 점검 항목에 함께 넣어 두는 것이 좋습니다.
- 행 구조 버전 64 상한: 컬럼 추가·삭제를 INSTANT로 반복한 테이블은
TOTAL_ROW_VERSIONS를 확인합니다. 상한에 가까우면 테이블 재구성을 포함한 작업 계획을 세웁니다. - 행 형식과 테이블 종류: COMPRESSED 행 형식, FULLTEXT 인덱스가 있는 테이블, 임시 테이블 등은 INSTANT 컬럼 추가가 지원되지 않습니다.
- 행 크기 제한: 새 컬럼을 더했을 때 최대 행 크기를 넘으면 INSTANT로 추가할 수 없습니다.
- 함께 묶은 작업: 이번 실험처럼 인덱스 생성, 컬럼 타입 변경 등 INSTANT를 지원하지 않는 작업이 같은 문장에 있으면 전체가 거부됩니다.
또한 이번 idx_channel_code는 10만 행이 모두 기본값 1인 컬럼에 만든 인덱스입니다. 생성 절차를 검증하기 위한 구성일 뿐, 이 인덱스가 조회에 유용하다는 의미는 아닙니다. 실제 인덱스는 조회 조건과 데이터 분포를 근거로 따로 판단해야 합니다.
자주 받는 질문
Q. ALGORITHM을 생략하면 오류 없이 처리되지 않나요?
처리는 됩니다. 다만 MySQL이 문장 전체가 지원하는 방식을 스스로 고르므로, STEP 2의 문장이라면 INSTANT가 아닌 방식으로 실행됩니다. 컬럼 추가를 INPLACE로 처리하면 테이블 재구성이 동반되므로, 컬럼 하나 넣으려다 대형 테이블 전체를 다시 쓰게 될 수 있습니다. 운영에서는 의도하지 않은 방식으로 실행되는 것보다 오류로 멈추는 편이 안전합니다.
Q. 오류 메시지대로 COPY로 바꾸면 되지 않나요?
COPY는 새 테이블을 만들어 데이터를 복사하는 방식이며, 작업 중 해당 테이블에 대한 쓰기(DML)를 허용하지 않습니다. 작은 테이블이라면 문제가 없을 수 있지만, 운영 테이블에서는 서비스 쓰기가 막히는 시간이 생깁니다. 먼저 어떤 작업이 INSTANT를 막는지 찾고, 그 작업만 분리하는 쪽을 검토합니다.
Q. 모든 ALTER를 항상 쪼개는 것이 좋은가요?
그렇지는 않습니다. 테이블 재구성이 필요한 변경이 여러 개라면 한 문장에 묶어 재구성을 한 번만 하는 편이 비용이 적습니다. 이번처럼 INSTANT가 가능한 작업과 불가능한 작업이 섞인 경우에 분리가 효과적입니다. 분리할 때는 STEP 4에서 본 중간 상태를 스크립트에서 확인하도록 만들어야 합니다.
Q. LOCK=NONE이면 잠금이 전혀 없나요?
아닙니다. LOCK=NONE은 작업 중 동시 읽기·쓰기를 허용하라는 요구이고, 그것이 불가능한 작업이면 오류로 거부됩니다. 작업 시작과 끝의 짧은 배타적 메타데이터 잠금은 여전히 필요하므로, STEP 5에서 겪은 것처럼 커밋되지 않은 세션이 있으면 똑같이 대기할 수 있습니다. 이번 실험은 동시 부하 없이 진행했으므로 운영 중 무중단 적용까지 검증한 결과는 아닙니다.
댓글 0
첫 댓글을 남겨보세요.