DBMS 서버의 모든 오브젝트를 생성하거나 변경하는 쿼리를 DDL(Data Definition Language)라고 한다.

• 스토어드 프로시저나 함수, DB나 테이블 등을 생성하거나 변경하는 대부분의 명령이 DDL에 해당한다.
• MySQL 서버가 업그레이드 되면서 많은 DDL이 온라인 모드로 처리될 수 있게 개선됐지만, 스키마를 변경하는 작업은 오랜 시간이 걸리고 서버에 많은 부하를 발생시키는 작업들이 있으므로 주의해야 한다.

MySQL 5.5 이전 버전까지는 서버에서 테이블의 구조를 변경하는 동안 다른 커넥션에서 DML을 실행할 수 없었기 때문에 Percona에서 개발한 pt-online-schema-change라는 도구를 사용해야했다.(MySQL 5.5 버전에서도 성능상 이유로 사용했다.)

MySQL 8.0 버전으로 업그레이드되면서 대부분의 스키마 변경 작업은 서버에 내장된 온라인 DDL 기능으로 처리가 가능하기 때문에 위와 같은 도구는 거의 사용되지 않는다.

온라인 DDL 알고리즘

온라인 DDL은 스키마를 변경하는 작업 도중에도 다른 커넥션에서 해당 테이블의 데이터를 변경하거나 조회하는 작업을 가능하게 해준다.

• 온라인 DDL 기능은 테이블의 구조를 변경하거나 인덱스 추가와 같은 대부분의 작업에 대해 작동한다.
• ALGORITHM, LOCK 옵션을 통해 어떤 모드로 스키마 변경을 실행할지 결정할 수 있다.

MySQL 서버는 old_alter_table 시스템 변수를 이용해 ALTER TABLE 명령이 온라인 DDL 또는 예전 방식(테이블의 읽고 쓰기를 막고 스키마를 변경하는 방식)으로 처리할지를 결정할 수 있다.

• 해당 시스템 변수의 기본값은 OFF로 설정되어 있어 기본적으로 온라인 DDL이 활성화된다.

ALTER TABLE 명령이 실행되면 다음과 같은 순서로 스키마 변경에 적합한 알고리즘을 찾는다.

1. ALGORITHM=INSTANT로 스키마 변경이 가능한지 확인 후, 가능하면 선택
2. ALGORITHM=INPLACE로 스키마 변경이 가능한지 확인 후, 가능하면 선택
3. ALGORITHM=COPY 알고리즘 선택

스키마 변경 알고리즘의 우선순위가 낮을수록 MySQL 서버는 스키마 변경을 위해 더 큰 잠금과 많은 작업을 필요로 하고 서버의 부하도 많이 발생시킨다.

INSTANT

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

• 테이블이 가진 레코드 건수와 무관하게 작업 시간이 매우 짧다.
• 스키마 변경 도중 테이블의 읽고 쓰기는 대기하게 되지만 스키마 변경 시간이 매우 짧기 때문에 다른 커넥션의 쿼리 처리에는 크게 영향을 미치지 않는다.

INPLACE

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

• 내부적으로는 테이블의 리빌드를 실행할 수 있다.
• 레코드의 복사 작업은 없지만 테이블의 모든 레코드를 리빌드해야 하기 때문에 테이블의 크기에 따라 많은 시간이 소요될 수 있다.
• 스키마 변경 중에도 테이블의 읽기와 쓰기 모두 가능하다.
• 최초 시작 시점과 마지막 종료 시점에는 테이블의 읽고 쓰기가 불가능하다.
  • 하지만 시간이 매우 짧기 때문에 다른 커넥션의 쿼리 처리에 대한 영향도는 높지 않다.
• 대부분 잠금은 NONE으로 설정 가능하지만,

COPY

변경된 스키마를 적용한 임시 테이블을 생성하고 테이블을 모두 입시 테이블로 복사한 후 최종적으로 임시 테이블을 RENAME하여 스키마 변경을 완료한다.

• 테이블 읽기만 가능하고 DML은 실행할 수 없다.

잠금 수준

온라인 DDL 명령은 알고리즘과 함께 잠금 수준도 함께 명시할 수 있다.

1
2

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

ALGORITHM, LOCK 옵션이 명시되지 않으면 서버가 적절한 수준의 알고리즘과 잠금 수준을 선택한다.

INSTANT 알고리즘은 메타데이터만 변경하기 때문에 매우 짧은 시간 동안의 메타데이터 잠금을 필요로 하기 때문에 LOCK 옵션을 지원하지 않는다.

• NONE: 아무런 잠금을 걸지 않음
• SHARED: 읽기 잠금을 걸고 스키마 변경을 실행한다.
  • 스키마 변경 중 읽기는 가능하지만 쓰기는 불가
  • INPLACE 알고리즘이 사용되는 경우 대부분 NONE으로 설정 가능하지만 SHARED 수준까지 설정해야 할 수 있다.
• EXCLUSIVE: 쓰기 잠금을 걸고 스키마 변경을 실행한다.
  • 테이블의 읽고 쓰기 불가
  • 전통적인 ALTER TABLE과 동일하므로 명시할 필요는 없다.

온라인 스키마 변경 작업이 INPLACE 알고리즘을 사용하더라도 내부적으로는 테이블의 리빌드가 필요할 수도 있다.

• 테이블의 프라이머리 키를 추가하는 작업은 데이터 파일에서 레코드의 저장 위치가 바뀌어야 하기 때문에 테이블 리빌드가 필요하다.
• 단순히 컬럼의 이름만 변경하는 경우 INPLACE 알고리즘을 사용해야 하지만 테이블 레코드의 리빌드는 필요하지 않다.

프라이머리 키를 추가하는 경우와 같이 테이블 레코드의 리빌드가 필요한 경우를 Data Reorganizing(데이터 재구성) 또는 Table Rebuild(테이블 리빌드) 라고 부른다.

결론적으로 INPLACE 알고리즘을 사용하는 경우는 데이터 재구성(테이블 리빌드) 여부에 따라 다음과 같이 구분할 수 있다.

• 필요한 경우: 잠금을 필요로 하지 않기 때문에 읽고 쓰기는 가능하지면 테이블의 레코드 건수에 따라 상당히 많은 시간이 소요될 수도 있다.
• 필요치 않은 경우: INPLACE 알고리즘을 사용하지만 INSTANT 알고리즘처럼 매우 빨리 작업이 완료될 수 있다.

MySQL 서버의 온라인 DDL 기능은 버전별로 많은 차이가 있으므로 사용 중인 버전이 8.0이 아니라면 메뉴얼을 살펴보고 테이블 리빌드가 필요한지 확인이 필요하다.
또한 스키마 변경 작업을 실행하기 전 먼저 메뉴얼과 테스트를 진행해본다면 안전하게 처리할 수 있다.

온라인 처리 가능한 스키마 변경

MySQL 서버의 모든 스키마 변경 작업이 온라인으로 가능한 것이 아니기 때문에 필요한 스키마 변경 작업의 형태가 온라인으로 처리될 수 있는지, 일고 쓰기가 대기(Waiting)하게 되는지 확인한 후 실행하는 것이 좋다.

MySQL 서버에서 사용할 수 있는 스키마 변경 작업은 매우 다양하기 때문에 모든 명령이 온라인 DDL을 지원하는지는 기억하기 어려우므로 가이드나 책을 참고한다.

• ALTER TABLE 문장에 LOCK과 ALGORITHM절을 명시해서 온라인 스키마 변경의 처리 알고리즘을 강제할 수 있으나, 무조건 명시한대로 처리하지는 않는다.
• 명시된 알고리즘으로 온라인 DDL이 처리되지 못한다면 에러를 발생시키고 작업은 수행되지 않기 때문에 의도하지 않은 잠금과 대기는 발생하지 않는다.

온라인 DDL이라 하더라도 서버에 부하를 유발할 수 있으며, 그로 인해 다른 커넥션의 쿼리들이 느려질 수도 있다. 따라서 스키마 변경 작업이 직접 다른 커넥션의 DML을 대기하게 만들지는 않더라도 주의해서 사용한다.

INPLACE 알고리즘

INPLACE 알고리즘은 임시 테이블로 레코드를 복사하지는 않더라도 내부적으로 테이블의 모든 레코드를 리빌드해야 하는 경우가 많다.

이러한 경우 다음과 같은 과정을 거친다.

1. 스키마 변경이 지원되는 스토리지 엔진의 테이블인지 확인
2. 스키마 변경 준비
   • 스키마 변경에 대한 정보를 준비해서 온라인 DDL 작업 동안 변경되는 데이터를 추적할 준비
3. 테이블 스키마 변경 및 새로운 DML 로깅
   • 실제 스키마 변경을 수행하는 과정으로, 작업이 수행되는 동안 다른 커넥션의 DML 작업이 대기하지 않는다.
   • 스키마를 온라인으로 변경함과 동시에 다른 스레드에서는 사용자에 의해서 발생한 DML들에 대해서 별도의 로그로 기록
4. 로그 적용
   • 온라인 DDL 작업 동안 수집된 DML들에 대해 별도 로그로 기록
5. 스키마 변경 완료(COMMIT)

• INPLACE 알고리즘으로 스키마가 변경된다고 하더라도 2, 4번 단계에서는 짧은 배다적 잡금이 필요하며, 이 시점에는 다른 커넥션의 DML들이 잠깐 대기한다.
• 실제 변경 작업이 실행되며 많은 시간이 필요한 3번 단계는 DML 작업이 대기 없이 즉시 처리된다.
• INPLACE 알고리즘으로 온라인 스키마 변경이 진행되는 동안 새로 유입된 DML 쿼리들에 의해 변경되는 데이터를 온라인 변경 로그라는 메모리 공간에 쌓아 두었다가 완료되면 로그의 내용을 실제 테이블로 일괄 적용한다.
• 온라인 변경 로그는 메모리에만 생성되며, 메모리 공간의 크기는 innodb_online_alter_log_max_size시스템 설정 변수에 의해 결정된다.
  • 온라인 스키마 변경이 오랜 시간 걸리거나, 스키마 변경 중에 유입되는 DML 쿼리가 많다면 메모리 공간을 크게 설정하는 것이 좋다.
  • 해당 시스템 변수는 세션 단위의 동적 변수이므로 언제든 변경할 수 있다.

온라인 DDL의 실패 케이스

온라인 DDL이 INSTANT 알고리즘을 사용하는 경우 시작과 동시에 작업이 완료되기 때문에 작업 도중 실패할 가능성은 거의 없지만, INPLACE 알고리즘으로 실행되는 경우 내부적으로 테이블 리빌드 과정이 필요할 수 있고, 최종 로그 적용 과정이 필요해서 중간에 실패할 가능성이 높은 편이다.

• ALTER TABLE 명령이 장시간 실행되고 동시에 다른 커넥션에서 DML이 많이 실행되는 경우
• 온라인 변경 로그의 공간이 부족한 경우
• ALTER TABLE 명령이 실행되는 동안 이전 버전의 테입르 구조에서는 아무런 문제가 안되지만 이후 테이블 구조에는 적합하지 않은 레코드가 INSERT, UPDATE 됐다면 스키마 변경 작업은 마지막 과정에서 실패
• 스키마 변경을 위해서 필요한 잠금 수준보다 낮은 잠금 옵션이 사용된 경우
• 잠금 없이 실행되는데 변경 작업의 처음과 마지막 과정에서 잠금이 이 수행되는 과정에서 잠금을 획득하지 못하는 경우
• 인덱스를 생성하는 작업의 경우 정렬을 위해 디스크의 임시 디렉터리를 사용하는데, 이 공간이 부족한 경우

온라인 DDL 진행 상황 모니터링

온라인 DDL을 포함한 모든 ALTER TABLE 명령은 MySQL 서버의 performance_schema를 통해 진행 상황을 모니터링 할 수 있다.

performance_schema를 이용해 ALTER TABLE의 진행 상황을 모니터링 하려면 옵션(Instrument, Consumer)이 활성화돼야 한다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

/* 시스템 변수 활성화 - 서버 재시작 필요 */
SET GLOBAL performance_schema = ON;

UPDATE performance_schema.setup_instruments
  SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'stage/innodb/alter%'
;

UPDATE performance_schema.setup_consumers
  SET ENABLED = 'YES'
WHERE NAME LIKE '%stages%'
;

스키마 변경 작업의 진행 상황은 performance_schema.events_stages_current 테이블을 통해 확인할 수 있는데 실행중인 스키마 변경 종류에 따라 기록되는 내용이 조금씩 달라진다.