문제상황

MySQL에서 90만 건이 넘는 데이터가 적재된 테이블에 칼럼을 추가하려고 했다. 보통 데이터가 많지 않으면 1초 이내로 완료된다. 그런데, 데이터 양이 많아서 DDL 반영까지 10분 가량 시간이 소요됐다. 문제는 그 동안, 테이블에 락이 걸려, 테이블에 대한 쓰기 작업이 일시적으로 중단되는 현상이 발생됐다.

락이 걸리는 이유

락이 걸리는 이유는 뭘까? 데이터 일관성을 보장하기 위함이다. 테이블의 스키마를 변경하는 작업은 데이터베이스의 구조를 수정하는 것으로, 이 과정에서 테이블에 대한 읽기 및 쓰기 작업을 일시적으로 중지하여 데이터 일관성을 유지한다.

스키마 변경 작업이 완료될 때까지 다른 작업들이 해당 테이블에 접근하지 못하도록 하는 것이다. 이는 동시성 문제를 방지하고 데이터베이스의 일관성을 보장하기 위한 조치이다. 특히 복잡한 스키마 변경 작업이나 데이터 양이 많은 테이블의 경우, 락이 발생할 가능성이 더 높다.

해결방법

스키마 변경 작업의 락이 발생하지 않거나 최소화 하는 방법은 여러 가지가 있다.

Online DDL 사용
- MySQL에서는 데이터베이스에 락을 걸지 않고도 스키마 변경을 수행할 수 있다.
- 예를 들면, 아래와 같이 사용한다. ALTER문 뒤에 ALGORITHM=INPLACE, LOCK=NONE을 추가하면 된다. 자세한 내용은 아래에서 더 알아보자.
```
ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
```
Replication 사용
- 스키마 변경 작업을 수행하기 전에 데이터베이스 복제를 설정하여 마스터와 슬레이브 사이의 데이터를 복제한다. 그런 다음, 슬레이브에서 스키마 변경 작업을 수행하여 마스터에서는 락이 발생하지 않도록 한다.
작은 트랜잭션 사용
- 스키마 변경 작업을 여러 개의 작은 트랜잭션으로 분할하여 수행하는 것이 락을 최소화하는 데 도움이 된다. 이렇게 하면 한 번에 하나의 작은 트랜잭션만이 락을 소유하게 되므로 다른 작업들이 중단되는 시간을 최소화할 수 있다.
테이블 복제
- 스키마 변경 작업을 수행하기 전에 데이터를 복사하여 새로운 테이블에 적용한 다음, 기존 테이블과 새로운 테이블을 교체하는 방식을 사용할 수 있다. 이를 통해 스키마 변경 작업이 발생하는 시간을 최소화할 수 있다.

이 중에서도 가장 간단하고 효과적으로 적용해볼 수 있는 Online DDL에 대해 알아보자.

Online DDL

장점
- 스키마 변경을 실행하는 중에도 INSERT나 UPDATE와 같은 DML이 실행될 수 있다.
- 락을 최소화할 수 있다. Online DDL을 사용 하더라도, 실행 단계 동안 테이블에 대한 배타적 메타데이터 잠금이 잠시 필요할 수 있으며, 테이블 정의를 업데이트할 때 작업의 마지막 단계에서는 항상 잠금이 필요하다.
단점
- 온라인 DDL 작업을 일시 중지하거나, 온라인 DDL 작업에 대한 I/O 또는 CPU 사용량을 조절하는 메커니즘이 없다.
- 작업이 실패할 경우 온라인 DDL 작업의 롤백 비용이 많이 들 수 있다.
- 장기간 실행되는 온라인 DDL 작업으로 인해 복제 지연이 발생할 수 있습니다. 온라인 DDL 작업은 복제본에서 실행되기 전에 소스에서 실행을 완료해야 합니다. 또한 소스에서 동시에 처리된 DML은 복제본에 대한 DDL 작업이 완료된 후에만 복제본에서 처리될 수 있다.

방법

INSTANT
- 테이블의 데이터는 전혀 변경하지 않고, 메타데이터만 변경하고 작업을 완료함. 테이블이 가진 레코드 건수와 무관하게 작업 시간은 매우 짧음. 스키마 변경 도중 테이블의 읽고 쓰기는 대기하게 되지만 스키마 변경 시간이 매우 짧기 때문에 다른 커넥션의 쿼리 처리에는 크게 영향을 미치지 않음.
INPLACE
- 임시 테이블로 데이터를 복사하지 않고 스키마 변경을 실행. 하지만 내부적으로는 테이블의 리빌드를 실행할 수도 있음. 레코드의 복사 작업은 없지만 테이블의 모든 레코드를 리빌드해야 하기 때문에 테이블의 크기에 따라 많은 시간이 소요될 수 있음. 하지만 스키마 변경 중에도 테이블의 읽기와 쓰기 모두 가능. INPLACE 알고리즘으로 스키마가 변경되는 경우에도 최초 시작 시점과 마지막 종료 시점에는 테이블의 읽고 쓰기가 불가함. 하지만 이 시간은 매우 짧기 때문에 다른 커넥션의 쿼리 처리에 대한 영향도는 높지 않음.
COPY
- 변경된 스키마를 적용한 임시 테이블을 생성하고, 테이블의 레코드를 모두 임시 테이블로 복사한 후 최종적으로 임시 테이블을 RENAME해서 스키마 변경을 완료. 이 방법은 테이블 읽기만 가능하고 DML은 실행할 수 없음.

서비스 영향을 최소화하면서 가능한 알고리즘을 확인해 보는 방법

ALGORITHM=INSTANT 옵션으로 스키마 변경을 시도
실패하면 ALGORITHM=INPLACE, LOCK=NONE 옵션으로 스키마 변경을 시도
실패하면 ALGORITHM=INPLACE, LOCK=SHARED 옵션으로 스키마 변경을 시도
실패하면 ALGORITHM=COPY, LOCK=SHARED 옵션으로 스키마 변경을 시도
실패하면 ALGORITHM=COPY, LOCK=EXCLUSIVE 옵션으로 스키마 변경을 시도
- 1, 2번으로 되지 않는다면 DML을 멈춘 다음 스키마 변경을 해야 하는 작업임

레퍼런스

https://medium.com/daangn/mysql-online-ddl-faf47439084c
https://blog.bespinglobal.com/post/mysql-online-ddl-%EB%B3%84-%EC%A0%81%EC%9A%A9-%EA%B0%80%EB%8A%A5%ED%95%9C-%EC%95%8C%EA%B3%A0%EB%A6%AC%EC%A6%98-8-0-%EC%9D%B4%EC%83%81/
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

저작자표시 비영리 변경금지