티스토리 뷰

개요

  • 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 여부' AFTER id, ALGORITHM=INSTANT;
ALTER TABLE foobar ADD COLUMN is_foobar TINYINT(1) NULL DEFAULT 0 COMMENT 'foobar 여부' AFTER id, ALGORITHM=INSTANT, 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=INSTANTMySQL 8.0(MariaDB 10.3.7)부터 지원하는 최신 모드이다. (텐센트가 오픈 소스를 기여했다.)

LOCK=NONE 제약사항

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

Online DDL 제약사항

  • 어떤 LOCK 모드를 사용해도 테이블 변경 작업의 처음과 마지막 시점에 테이블 락이 발생한다.

갈레라 클러스터에서의 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';

참고 글

댓글
댓글쓰기 폼