MySQL/MariaDB, 테이블 락 최소화하여 변경하기

MySQL/ADMIN

MySQL/MariaDB, 테이블 락 최소화하여 변경하기

argoLee 2023. 11. 9. 15:35

개요

MySQL/MariaDB는 전통적으로 특정 테이블에 대한 ALTER 명령 실행시, 작업이 완료될 때까지 해당 테이블 전체에 락을 걸어 읽기, 쓰기 작업이 불가능했다.
MySQL 5.6(MariaDB 10.0)부터 InnoDB 테이블에 Online DDL 기능이 추가되어 테이블 락을 최소화한 테이블 수정이 가능해졌다. 사용자는 ALTER 명령 실행시 ALGORITHM, LOCK 2개 옵션을 추가로 명시해주면 테이블 락을 최소화하여 테이블 스키마를 수정하는 작업을 수행할 수 있게 되었다.

ALTER TABLE foobar ADD COLUMN is_foobar TINYINT(1) NULL DEFAULT 0 COMMENT 'foobar 여부', ALGORITHM=INSTANT;
ALTER TABLE foobar ADD COLUMN is_foobar TINYINT(1) NULL DEFAULT 0 COMMENT 'foobar 여부' AFTER id, ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM 모드의 종류

ALGORITHM=COPY는 대상 테이블의 원본은 놔두고 변경될 테이블을 새로 생성한 후 원본으로부터 데이터를 로우 단위로 복제하는 방식이다. 모든 복제 작업과 인덱스 생성 작업이 완료되면 원본을 변경된 테이블로 바꾼다. 복제본을 만들어야 하기 때문에 시스템 자원을 많이 소모한다.
ALGORITHM=INPLACE은 대상 테이블의 복제 과정 없이 메타 데이터 변경 만으로 빠르게 변경사항을 반영하는 방식이다. 속도가 가장 빠르며, 시스템 자원도 거의 소모하지 않는다. MySQL 8.0 하위 버전에서는 가장 최선의 방법이라고 할 수 있다. 다만, ALGORITHM=INPLACE에는 주의할만한 제약사항이 있다. 이런 상황을 가정해보자. 만약, MySQL 5.6 이전 버전을 사용하면서 테이블을 생성했고, 이후 MySQL 5.6으로 업그레이드했다면 ALGORITHM=INPLACE는 당장 사용이 불가능하다. ERROR 1846 (0A000) 오류가 발생한다. 해결책은 최초 1번은 ALGORITHM=COPY를 사용하여 테이블을 리빌드하는 작업이 필요하다. 리빌드가 완료된 후에는 ALGORITHM=INPLACE의 사용이 가능해진다.
ALGORITHM=INSTANT는 MySQL 8.0(MariaDB 10.3.7)부터 지원하는 최신 모드이다. ALGORITHM=COPY, ALGORITHM=INPLACE, LOCK=NONE과 다르게 테이블 락이 전혀 발생하지 않는다. (텐센트가 오픈 소스를 기여했다.)

LOCK=NONE 제약사항

AUTO_INCREMENT 속성의 컬럼을 추가할 때는 불가능하다.
대상 테이블이 FULLTEXT 인덱스를 가지거나, FTS_DOC_ID 컬럼을 가질 경우 불가능하다.
대상 테이블이 FOREIGN KEY와 함께 ON … CASCADE 또는 ON … SET NULL 제약조건을 가질 경우 불가능하다.

# 모든 테이블의 FK 제약 조건 조회
# UPDATE_RULE, DELETE_RULE 컬럼을 통해 CASCADE|NO_ACTION 지정 여부를 확인 가능
SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS;

# 이미 설정된 CASCADE를 변경하려면 FK를 삭제 후 재생성해야함
# FK 제거 후 재생성하지 않고, 인덱스만 유지하는 것도 방법
ALTER TABLE foo DROP FOREIGN KEY bar;

ALGORITHM=INSTANT 소개

ALGORITHM=INSTANT는 Online DDL 실행시 테이블 락이 전혀 발생하지 않아 현재 최상의 알고리즘으로 권장된다. ALGORITHM=INPLACE, LOCK=NONE와 달리 해당 테이블에 대한 모든 실행 중인 쿼리가 종료될 때까지 기다려야 하는 일시적인 메타데이터 잠금이 전혀 발생하지 않는 장점이 있다. 즉, 전혀 락이 발생하지 않는다. [관련 링크]
사용법은 ALGORITHM=INPLACE와 달리 LOCK=NONE 키워드 없이 단독으로 사용하면 된다.

ALTER TABLE foo ADD COLUMN bar1 INT, ADD COLUMN bar2 INT, ALGORITHM=INSTANT;

ALGORITHM=INSTANT 쓰임새

새로운 컬럼을 추가할 때 사용 가능하다.
기존 컬럼의 기본값을 추가하거나 삭제할 때 사용 가능하다.
인덱스 타입을 변경할 때 사용 가능하다.
테이블 이름을 변경할 때 사용 가능하다.

ALGORITHM=INSTANT 제약사항

MySQL 8.0.12부터 가능하다. (Amazon Aurora는 2021-11-18 부로 3 엔진 버전을 통해 지원한다.)
ALGORITHM=INSTANT가 불가능한 구문이 포함되면 사용이 불가능하다. (컬럼 추가시 ALTER TABLE을 단독으로 사용하는 것이 좋다.)
컬럼 추가시 위치를 명시적으로 지정하면 안된다. 즉, 추가되는 컬럼의 위치는 마지막 컬럼 뒤어야 한다. (컬럼 위치 조정이 불가피하게 필요하다면 ALGORITHM=INPLACE, LOCK=NONE을 사용해야 하며, 이 경우 테이블 변경 작업의 처음과 마지막 시점에 테이블 락이 발생한다.)
대상 테이블이 ROW_FORMAT=COMPRESSED이면 안된다. (테이블 생성시 기본 옵션은 ROW_FORMAT=DYNAMIC이기 때문에 거의 해당되지 않는다.)
대상 테이블이 FULLTEXT 인덱스를 가지면 안된다.
대상 테이블이 TEMPORARY 테이블이면 안된다.

갈레라 클러스터에서의 ALTER TABLE 실행

갈레라 클러스터는 멀티 마스터 구조이기 때문에 ALTER TABLE의 실행이 전체 노드에 영향을 끼친다. 즉, 일반적인 방법으로 스키마 변경을 실행하면 전체 노드에 한꺼번에 쓰기 락이 발생한다. 이 방법은 24시간 매출이 발생하는 운영 서비스에 치명적일 수 있다. 최선의 방법은 운영 서비스에 영향을 최소화하기 위해 노드 단위로 차례로 스키마를 변경하는 것이다. 이를 위해 RSU(Rolling Schema Update) 모드를 사용해야 한다.(이와 반대되는 기본 모드가 TOI이다.) 방법은 아래와 같다.

$ mysql -u root
> USE somedb;

-- RSU 모드 전환
-- 이제부터 현재 노드에서 실행된 DDL을 다른 노드에 전파하지 않음
> SET GLOBAL wsrep_OSU_method = 'RSU';

-- 테이블 스키마 변경 실행
-- 앞서 RSU 모드 전환 덕분에 다른 노드에는 영향을 미치지 않음
-- DDL 실행 중에는 클러스터에서 노드가 일시적으로 이탈
-- DDL 완료 후 클러스터에 노드가 복귀하여 버퍼에 저장된 데이터 변경점을 반영
> ALTER TABLE foobar ...;
Stage: 1 of 2 'copy to tmp table' 100.0% OF stage done
Stage: 2 of 2 'Enabling keys' 100.0% OF stage done
Query OK, 3698216 ROWS affected (2 MIN 51.01 sec)
Records: 3698216  Duplicates: 0  Warnings: 0

-- TOI 모드 복귀
> SET GLOBAL wsrep_OSU_method = 'TOI';

다른 대안, gh-ost 마이그레이션 도구 사용

써드 파티 마이그레이션 도구를 이용한 테이블 스키마 변경도 가능하다. GitHub이 직접 개발하여 오픈 소스로 공개한 gh-ost가 대표적이다. [관련 링크]

참고 글

저작자표시 비영리