서비스중인 테이블을 안전하게 변경(ALTER)하는 방법

서비스를 운영하다보면 테이블을 변경해야 할 때가 있습니다.

호기롭게 DDL문을 실행했지만 변경하는 시간이 오래 걸리게되면 급격하게 불안해지는 저의 모습을 여러번 보았습니다..

그래서 스키마 변경 중에도 다른 커넥션에서 데이터를 정상적으로 조회 할 수 있도록 도와주는 온라인 DDL알고리즘과 해당 알고리즘을 적용해 테이블을 변경하는 방법들을 정리해보려 합니다.

(Real MySQL 8.0을 보고 공부한 내용을 다룹니다.)

 

온라인 DDL 알고리즘

스키마 변경 중에도 다른 커넥션에서 데이터를 정상적으로 조회 할 수 있도록 도와주는 MySQL의 기능입니다.

3가지의 알고리즘이 존재하며, ALTER TABLE 실행하면 MySQL은 INSTANT, INPLACE, COPY 순서대로 가능한 알고리즘을 선택하여 적용시킵니다.

INSTANT

테이블의 데이터는 변경하지 않고, 메타데이터만 변경하고 작업을 완료시킵니다.

스키마 변경 중 테이블의 읽기, 쓰기는 대기하지만 작업시간은 매우 짧아 다른 커넥션의 쿼리 처리는 거의 영향을 끼치지 않습니다.

INPLACE

임시테이블로 데이터를 복사하지 않고, 스키마 변경을 실행합니다.

내부적으로 테이블 리빌드가 실행 될 수 있습니다.

레코드의 복사작업은 없지만 테이블의 모든 데이터를 리빌드해야하기에 테이블 크기에 따라 많은 시간이 걸릴 수 있습니다.

하지만 작업중에도 테이블의 읽기 쓰기가 모두 가능합니다.

(최초 시작 시점과 종료시점에는 테이블 읽고 쓰기가 불가능 하지만 시간이 매우 짧아 다른 커넥션에서 큰 영향은 없습니다.)

COPY

변경된 스키마를 적용한 임시테이블을 생성하고, 테이블의 레코드를 모두 임시 테이블로 복사 한 후 최종적으로 임시 테이블을 RENAME해서 스키마 변경을 완료합니다.

테이블 읽기만 가능하고 DML(INSERT, UPDATE, DELETE)는 불가능합니다.

 

DDL을 적용 할 떄 위에 3개의 알고리즘과 함께 잠금수준도 명시해서 실행하면 원하는 알고리즘으로 테이블 변경을 할 수 있습니다.

잠금 수준도 동일하게 3가지 방법이 있습니다

NONE

아무런 잠금을 걸지 않습니다.

SHARED

읽기 잠금을 걸고 스키마 변경을 실행합니다. (테이블 읽기는 가능, 쓰기작업 불가)

EXCLUSIVE

쓰기 잠금을 걸고 스키마 변경을 실행합니다. (테이블 읽고 쓰기 불가)

 

예시

DDL아래 알고리즘과 LOCK을 명시해서 사용하면 됩니다.

만약 명시하지 않으면 MySQL이 적절한 알고리즘과 잠금수준을 적용합니다.

ALTER TABLE salaries CHANGE to_date, end_date DATE NOT NULL,
ALGORITHM=INPLACE, LOCK=NONE

 

INPLACE 알고리즘의 테이블 리빌드란?

INPLACE알고리즘에는 테이블 리빌딩이 일어날 수 있습니다.

예를들어 PK키를 추가하는 작업의 경우 데이터 파일에서 레코드의 저장위치가 변경되어야 하기에 리빌드가 필요합니다.

(컬럼이름 변경작업 같은 경우에는 리빌드 X)

 

하지만 INPLACE알고리즘에서도 작업 시작, 끝 두 시점에는 X-Lock이 걸리게 됩니다.

X-Lock이 걸렸던 시점에서 발생했던 DML들은 메모리의 온라인 변경 로그 (Oneline alter log)에 쌓기게 되는데

테이블의 변경이 완료되면 해당 로그들을 일괄로 처리됩니다.

 

만약 온라인 변경 로그의 값이 너무 커지게 되면 온라인 DDL작업은 실패하게 됩니다.

( innodb_online_alter_log_max_size 변수로 크기 조절 가능, 세션단위의 동적 변수임)

 

안전하게 테이블 변경 DDL 실행하는 방법

아래 두 방식의 알고리즘을 선택하면 다른 커넥션과의 잠금없이 테이블 변경이 가능합니다.

만약 두 방식으로 변경이 안된다면 사용자가 없는 시간대나 서비스 점검을 걸고 작업하는것이 좋습니다.

 

1. ALGORITHM=INSTANT 방식으로 실행

ALTER TABLE salaries CHANGE to_date, end_date DATE NOT NULL,
ALGORITHM=INSTANT

2. ALGORITHM=INPLACE, LOCK=NONE 방식으로 실행 

ALTER TABLE salaries CHANGE to_date, end_date DATE NOT NULL,
ALGORITHM=INPLACE, LOCK=NONE

 

각 작업별 온라인 DDL 알고리즘 처리 방식

 

MySQL에서 테이블 변경시 처리되는 알고리즘 처리표입니다. (8.0.21 버전 기중)