[Mysql] MySQL Online DDL

개요

이 글에서는 Mysql Online DDL에 대해서 알아봅니다.

MySQL Online DDL

MySQL의 Online DDL은 테이블 구조를 변경하면서도 서비스 중단을 최소화하기 위해 설계되었습니다. 등장 전에는 스키마 변경 시, 무조건 테이블 전체 락이 걸리기 때문에 운영 환경에서 사용하기 어려움이 있었습니다.

데이터가 많을 수록 중단 시간은 더 오래걸리기 때문에 다른 전략이 필요헀고, DBA들은 복잡한 우회 방법을 개발해야 했습니다:

1. 수동으로 새 테이블 생성 후 데이터 일괄 복사
2. 트리거를 사용한 변경 내용 동기화
3. 테이블 스왑 및 애플리케이션 재시작

MySQL 5.6 버전부터 공식적으로 Online DDL 기능을 도입되었고. 이 기능을 통해 많은 ALTER TABLE 작업을 테이블에 대한 전체 락(lock)을 걸지 않고 수행할 수 있게 되었습니다.

-- 인덱스 추가 (완전 온라인 방식)
ALTER TABLE customers 
ADD INDEX idx_last_name (last_name),
ALGORITHM=INPLACE, 
LOCK=NONE;

-- 컬럼 추가 (읽기만 허용)
ALTER TABLE orders
ADD COLUMN status VARCHAR(20) DEFAULT 'pending',
ALGORITHM=INPLACE,
LOCK=SHARED;

MySQL Online DDL 동작

MySQL Online DDL 알고리즘 동작

INPLACE 알고리즘 동작 과정 (주로 사용되는 방식)

1. 준비 단계
   • 메타데이터 락을 짧게 획득하여 다른 DDL 작업과 충돌 방지
   • 테이블 구조를 분석하고 변경 가능성 검토
   • 변경사항 적용을 위한 내부 준비 작업 수행
   • 메타데이터 락 해제 (이 시점부터 다른 쿼리 실행 가능)
2. 실행 단계
   • 테이블 데이터를 물리적으로 재구성하거나 인덱스 생성
   • 이 단계에서 테이블 읽기/쓰기가 가능 (LOCK=NONE 옵션 사용 시)
   • 새로운 DML 작업은 로그에 기록되어 변경 중에도 적용됨
3. 커밋 단계
   • 메타데이터 락을 다시 획득 (매우 짧은 시간)
   • 새 테이블 구조로 전환 완료
   • 메타데이터 락 해제
   • 변경 완료

COPY 알고리즘 동작 과정 (기존 방식)

1. 원본 테이블과 동일한 구조의 임시 테이블 생성
2. 임시 테이블에 요청된 구조 변경 적용
3. 원본 테이블의 데이터를 임시 테이블로 복사
4. 인덱스 생성 등 추가 작업 수행
5. 원본 테이블과 임시 테이블 교체

락(LOCK) 옵션

MySQL Online DDL은 세 가지 락 옵션을 제공합니다:

• NONE: 변경 중에도 읽기/쓰기 모두 허용 (가장 온라인에 가까움)
• SHARED: 읽기는 허용하지만 쓰기는 차단
• EXCLUSIVE: 모든 데이터베이스 액세스 차단 (전통적인 방식)

예시 코드

-- 인덱스 추가 (완전 온라인 방식)
ALTER TABLE customers 
ADD INDEX idx_last_name (last_name),
ALGORITHM=INPLACE, 
LOCK=NONE;

-- 컬럼 추가 (읽기만 허용)
ALTER TABLE orders
ADD COLUMN status VARCHAR(20) DEFAULT 'pending',
ALGORITHM=INPLACE,
LOCK=SHARED;

지원되는 작업 유형

INPLACE + LOCK=NONE으로 완전 온라인 수행 가능한 작업:

• 인덱스 추가/삭제
• 외래 키 추가/삭제
• 컬럼 이름 변경
• 기본값 변경

일부 제한이 있는 작업:

• 컬럼 추가 (LOCK=SHARED 필요할 수 있음)
• 일부 데이터 타입 변경

COPY 알고리즘이 필요한 작업:

• 테이블 엔진 변경
• 특정 데이터 타입 간 변환

MySQL 버전이 높아질수록 더 많은 작업이 INPLACE 알고리즘과 LOCK=NONE 옵션으로 지원됩니다.

MySQL Online DDL 의 한계

MySQL의 Online DDL은 많은 장점을 제공하지만, 여러 제한 사항도 존재합니다. 이러한 제한 사항은 MySQL 버전에 따라 다르며, 최신 버전에서는 일부 제한이 완화되었습니다. 

1. 지원되지 않는 작업 유형

완전히 온라인으로 수행 불가능한 작업

• 테이블 엔진 변경: InnoDB에서 MyISAM으로 변경하는 등의 스토리지 엔진 전환은 항상 COPY 알고리즘이 필요합니다.
• 테이블 문자셋/콜레이션 변경: 데이터 재인코딩이 필요하므로 온라인으로 수행하기 어렵습니다.
• PRIMARY KEY 변경: 기본 키를 변경하는 작업은 테이블 전체 구조에 영향을 미치므로 COPY 알고리즘으로 처리됩니다.
• 특정 데이터 타입 변경: 특히 데이터 크기가 변경되는 경우(예: VARCHAR(50)에서 VARCHAR(200)으로)

2. 성능 영향

서버 리소스 부담

• 높은 CPU 사용률: 특히 인덱스 생성 시 서버 CPU 리소스를 많이 소모합니다.
• I/O 부하 증가: 데이터 페이지 재구성 시 디스크 I/O가 증가합니다.
• 메모리 사용량 증가: 온라인 스키마 변경 중 추가 메모리가 필요합니다.

성능 저하

• 쿼리 응답 시간 증가: DDL 작업 중 일반 쿼리의 응답 시간이 늘어날 수 있습니다.
• 경합 발생: 특히 INSERT가 많은 테이블에서 인덱스 생성 시 경합이 발생할 수 있습니다.

3. 기술적 제약

트랜잭션 로그 증가

• 로그 크기 폭증: 대용량 테이블 변경 시 InnoDB 트랜잭션 로그(redo log)가 크게 증가할 수 있습니다.
• 디스크 공간 문제: 변경 작업 중 임시로 추가 공간이 필요합니다.

복제 관련 문제

• 복제 지연: 마스터에서 수행된 온라인 DDL이 슬레이브에서는 논리적 SQL문으로 실행되어 복제 지연을 유발할 수 있습니다.
• 이진 로그 포맷 의존성: row 포맷의 이진 로그에서 특정 제약이 있습니다.

4. 버전 및 설정 의존성

MySQL 버전별 차이

• 버전 제한: MySQL 5.6부터 도입되었고, 버전이 올라갈수록 지원하는 작업이 증가했습니다.
• 자동 선택 문제: MySQL이 INPLACE 알고리즘을 사용할 수 없다고 판단하면 자동으로 COPY로 전환합니다.

설정 제약

• 버퍼 풀 크기 영향: innodb_buffer_pool_size 설정이 작으면 성능이 크게 저하될 수 있습니다.
• tmp_table_size 제한: 임시 테이블 관련 설정이 작업에 영향을 미칩니다.

5. 특정 상황에서의 문제

락 호환성 문제

• 외래 키 제약조건: 외래 키가 있는 테이블에서 일부 작업은 LOCK=NONE으로 수행이 어렵습니다.
• 트리거와 충돌: 테이블에 복잡한 트리거가 있을 경우 호환성 문제가 발생할 수 있습니다.

대용량 테이블 관련 문제

• 타임아웃 위험: 매우 큰 테이블에서는 작업이 너무 오래 걸려 타임아웃이 발생할 수 있습니다.
• 롤백 비용: 문제 발생 시 롤백에 많은 시간이 소요됩니다.

실무적 고려사항

1. 사전에 철저한 테스트를 수행
2. 낮은 트래픽 시간대에 작업 진행
3. 필요에 따라 MySQL Online DDL과 Percona Toolkit의 pt-online-schema-change를 상황에 맞게 선택

결론

간단한 수정같은 경우 Mysql Online DDL을 이용해도 좋겠으나, pt-online-schema-change 를 고려해볼 수 있겠습니다. 

 

당연히 prod 에 바로 실행하는 것이 아니라 개발 환경에서 충분히 테스트 후 수행해야합니다.