MySQL ALTER DDL 알고리즘별 동작 원리와 Metadata Lock 비교 분석

MySQL은 서비스 가용성을 높이기 위해 Copy, In-Place, Instant 세 가지 ALTER DDL 알고리즘을 제공한다. 각 알고리즘은 테이블 복사 여부, 데이터 재구축 필요성, 그리고 Metadata Lock(MDL) 획득 방식에서 명확한 차이를 보인다.

알고리즘별 핵심 특징

• Copy 알고리즘: 가장 오래된 방식으로, 새 테이블을 생성해 데이터를 복사한다. 작업 완료까지 다른 세션의 쓰기 작업이 차단되며 디스크 I/O와 디스크 공간 사용량이 높다.
• In-Place 알고리즘 (MySQL 5.6+): 일부 케이스를 제외하고 읽기/쓰기를 허용한다. 테이블 리빌딩이 필요할 경우 임시 테이블을 생성하고 변경 중 유입된 DML 로그를 적용한다. innodb_online_alter_log_max_size 설정이 중요하다.
• Instant 알고리즘 (MySQL 8.0+): 메타데이터만 변경하므로 테이블 크기와 무관하게 가장 빠르다. 컬럼 추가/삭제 등을 지원하지만, 64회 작업 후 리빌딩이 필요하며 FULLTEXT 인덱스나 압축 테이블에서는 사용이 제한된다.

Metadata Lock 동작 흐름

DDL 수행은 Initialization, Execution, Final 단계로 나뉘며, Execution 단계에서 락 상태가 동적으로 변경된다.

1. 초기 단계: 모든 알고리즘은 MDL_SHARED_UPGRADABLE을 획득하여 타 세션의 읽기/쓰기를 허용한다.
2. In-Place의 특수성: ha_prepare_inplace_alter_table 함수 진입 전 MDL_EXCLUSIVE로 승격되어 타 세션 접근을 차단한다. 함수 완료 후 다시 MDL_SHARED_UPGRADABLE로 강등한다. 단, Auto_increment 컬럼 추가 시에는 MDL_SHARED_NO_WRITE로 강등한다.
3. Instant의 효율성: Instant 알고리즘은 ha_prepare_inplace_alter_table과 ha_inplace_alter_table 단계에서 실질적인 작업 없이 즉시 반환되므로 락 대기 시간이 극히 짧다.
4. 커밋 단계: 두 알고리즘 모두 ha_commit_inplace_alter_table 진입 전 MDL_EXCLUSIVE로 승격하여 메타데이터를 최종 변경한다.

운영 권장 사항

예기치 않은 성능 저하와 서비스 장애를 방지하기 위해 ALTER 명령문에 ALGORITHM 구문을 명시하는 것이 필수적이다. 또한, 모든 알고리즘에서 짧게라도 MDL_EXCLUSIVE 락이 발생하므로, DDL 수행 전 장시간 실행 중인 트랜잭션이나 쿼리를 사전에 점검해야 한다.