데브러너 (DevRunner)

17.12.2 Online DDL Performance and Concurrency

Online DDL은 MySQL 운영의 여러 측면을 개선합니다:

테이블에 접근하는 애플리케이션은 더 빠르게 반응합니다. 그 이유는 테이블에 대한 쿼리와 DML 연산이 DDL 연산이 진행되는 동안에도 계속 수행될 수 있기 때문입니다. 락과 MySQL 서버 리소스 대기에 대한 감소는, DDL 연산에 관련되지 않은 연산에 대해서도 더 큰 확장성을 가져옵니다.
Instant 연산은 데이터 딕셔너리의 메타데이터만 수정합니다. 연산의 실행 단계 동안 테이블에 대해 배타적 메타데이터 락이 잠시 동안 획득될 수 있습니다. 테이블 데이터는 영향을 받지 않으므로 연산은 즉시 완료됩니다. 동시 DML이 허용됩니다.
Online 연산은 테이블 복사 방식과 관련된 디스크 I/O 및 CPU 사이클을 피함으로써 데이터베이스 전체에 걸친 부하를 최소화합니다. 부하를 최소화하면 DDL 연산 동안 좋은 성능과 높은 처리량을 유지하는 데 도움이 됩니다.
Online 연산은 테이블 복사 연산보다 버퍼 풀로 읽어들이는 데이터 양이 적어, 자주 접근되는 데이터가 메모리에서 제거되는 현상을 줄입니다. 자주 접근되는 데이터가 제거되면 DDL 연산 이후에 일시적인 성능 하락이 발생할 수 있습니다.

The LOCK clause

기본적으로 MySQL은 DDL 연산 동안 가능한 한 적은 락을 사용합니다. LOCK 절은 인플레이스 연산 및 일부 복사 연산에 대해 필요한 경우 더 제한적인 락을 강제하기 위해 지정할 수 있습니다. LOCK 절이 특정 DDL 연산에서 허용되는 수준보다 덜 제한적인 락 수준을 지정하면, 그 문장은 에러와 함께 실패합니다. LOCK 절은 아래와 같이, 가장 덜 제한적인 것에서 가장 제한적인 것 순으로 설명됩니다:

LOCK=NONE:

동시 쿼리와 DML을 허용합니다.

예를 들어, 긴 DDL 연산 동안 테이블을 사용 불가능하게 만드는 것을 피하기 위해, 고객 가입 또는 구매와 관련된 테이블에 이 절을 사용할 수 있습니다.

LOCK=SHARED:

동시 쿼리는 허용하지만 DML은 블록합니다.

예를 들어, DDL 연산이 완료될 때까지 데이터 로드 연산을 지연시킬 수 있지만 쿼리를 오랜 시간 동안 지연시킬 수 없는 데이터 웨어하우스 테이블에 이 절을 사용할 수 있습니다.

LOCK=DEFAULT:

가능한 한 많은 동시성(동시 쿼리, DML, 또는 둘 다)을 허용합니다. LOCK 절을 생략하는 것은 LOCK=DEFAULT를 지정하는 것과 같습니다.

DDL 문장의 기본 락 수준이 테이블에 대한 가용성 문제를 일으키지 않을 것이라고 예상되는 경우에 이 절을 사용합니다.

LOCK=EXCLUSIVE:

동시 쿼리와 DML을 블록합니다.

주된 관심사가 가능한 한 짧은 시간 안에 DDL 연산을 끝내는 것이고, 동시 쿼리 및 DML 접근이 필요하지 않은 경우 이 절을 사용합니다. 또한 서버가 유휴 상태여야 할 때, 예상치 못한 테이블 접근을 피하기 위해 이 절을 사용할 수도 있습니다.

Online DDL and Metadata Locks

Online DDL 연산은 세 단계로 볼 수 있습니다:

Phase 1: Initialization

Initialization 단계에서, 서버는 연산 동안 허용되는 동시성 수준을 결정합니다. 이때 스토리지 엔진 기능, 문장에서 지정된 연산, 사용자가 지정한 ALGORITHM 및 LOCK 옵션이 고려됩니다. 이 단계 동안, 현재 테이블 정의를 보호하기 위해 공유 업그레이드 가능 메타데이터 락이 획득됩니다.

Phase 2: Execution

이 단계에서 문장은 준비되고 실행됩니다. 메타데이터 락이 배타적으로 승격되는지 여부는 Initialization 단계에서 평가된 요소에 따라 달라집니다. 배타적 메타데이터 락이 필요한 경우, 문장 준비 동안에만 잠시 획득됩니다.

Phase 3: Commit Table Definition

Commit table definition 단계에서는, 이전 테이블 정의를 제거하고 새 정의를 커밋하기 위해 메타데이터 락이 배타적으로 승격됩니다. 한 번 부여되면 배타적 메타데이터 락의 지속 시간은 짧습니다.

위에서 설명한 배타적 메타데이터 락 요구 사항 때문에, online DDL 연산은 테이블에 대한 메타데이터 락을 보유한 동시 트랜잭션이 커밋 또는 롤백할 때까지 기다려야 할 수 있습니다. DDL 연산 이전 또는 도중에 시작된 트랜잭션은 변경 중인 테이블에 대한 메타데이터 락을 보유할 수 있습니다. 장시간 실행되거나 비활성 상태인 트랜잭션의 경우, online DDL 연산은 배타적 메타데이터 락을 기다리는 동안 타임아웃될 수 있습니다. 또한, online DDL 연산에 의해 요청된 보류 중인 배타적 메타데이터 락은 이후의 트랜잭션이 해당 테이블에 접근하는 것을 블록합니다.

다음 예시는 배타적 메타데이터 락을 기다리는 online DDL 연산과, 보류 중인 메타데이터 락이 테이블에 대한 이후 트랜잭션을 어떻게 블록하는지 보여줍니다.

Session 1:

1mysql> CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
2mysql> START TRANSACTION;
3mysql> SELECT * FROM t1;

session 1의 SELECT 문장은 테이블 t1에 대해 공유 메타데이터 락을 획득합니다.

Session 2:

1mysql> ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE;

session 2의 online DDL 연산은 테이블 정의 변경을 커밋하기 위해 테이블 t1에 대한 배타적 메타데이터 락이 필요하므로, session 1 트랜잭션이 커밋 또는 롤백할 때까지 기다려야 합니다.

Session 3:

1mysql> SELECT * FROM t1;

session 3에서 실행된 SELECT 문장은 session 2의 ALTER TABLE 연산에 의해 요청된 배타적 메타데이터 락이 부여되기를 기다리느라 블록됩니다.

메타데이터 락을 기다리는 트랜잭션이 있는지 확인하려면 SHOW FULL PROCESSLIST를 사용할 수 있습니다.

1mysql> SHOW FULL PROCESSLIST\G
2...
3*************************** 2. row ***************************
4     Id: 5
5   User: root
6   Host: localhost
7     db: test
8Command: Query
9   Time: 44
10  State: Waiting for table metadata lock
11   Info: ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE
12...
13*************************** 4. row ***************************
14     Id: 7
15   User: root
16   Host: localhost
17     db: test
18Command: Query
19   Time: 5
20  State: Waiting for table metadata lock
21   Info: SELECT * FROM t1
224 rows in set (0.00 sec)

메타데이터 락 정보는 Performance Schema의 metadata_locks 테이블을 통해서도 노출됩니다. 이 테이블은 세션 간 메타데이터 락 의존성, 세션이 대기 중인 메타데이터 락, 그리고 현재 메타데이터 락을 보유한 세션에 대한 정보를 제공합니다. 자세한 내용은 Section 29.12.13.3, “The metadata_locks Table”를 참조하십시오.

Online DDL Performance

DDL 연산의 성능은 대체로 그 연산이 instant로 수행되는지, 인플레이스로 수행되는지, 그리고 테이블을 리빌드하는지 여부에 의해 결정됩니다.

DDL 연산의 상대적인 성능을 평가하려면, ALGORITHM=INSTANT, ALGORITHM=INPLACE, ALGORITHM=COPY를 사용해 결과를 비교할 수 있습니다. 또한 old_alter_table을 활성화해서 문장을 실행함으로써 ALGORITHM=COPY 사용을 강제할 수 있습니다.

테이블 데이터를 변경하는 DDL 연산의 경우, DDL 연산이 변경을 인플레이스로 수행하는지, 아니면 테이블 복사를 수행하는지는 커맨드가 끝난 후 표시되는 “rows affected” 값을 보면 알 수 있습니다. 예를 들면 다음과 같습니다:

컬럼의 기본값 변경(빠르며, 테이블 데이터에 영향을 주지 않음):

1Query OK, 0 rows affected (0.07 sec)

인덱스 추가(시간은 걸리지만, 0 rows affected는 테이블이 복사되지 않았음을 보여줌):

1Query OK, 0 rows affected (21.42 sec)

컬럼의 데이터 타입 변경(상당한 시간이 걸리며, 테이블의 모든 로우를 리빌드해야 함):

1Query OK, 1671168 rows affected (1 min 35.54 sec)

큰 테이블에서 DDL 연산을 실행하기 전에, 다음과 같이 연산이 빠른지 느린지 확인하십시오:

테이블 구조를 클론합니다.
클론한 테이블에 적은 양의 데이터를 채웁니다.
클론한 테이블에서 DDL 연산을 실행합니다.
“rows affected” 값이 0인지 여부를 확인합니다.
0이 아닌 값은 연산이 테이블 데이터를 복사함을 의미하며, 이 경우 특별한 계획이 필요할 수 있습니다. 예를 들어, DDL 연산을 계획된 다운타임 기간에 수행하거나, 각 레플리카 서버에서 한 번에 하나씩 수행할 수 있습니다.

참고

DDL 연산과 관련된 MySQL 프로세싱을 더 잘 이해하기 위해, DDL 연산 전후에 Performance Schema 및 INFORMATION_SCHEMA의 InnoDB 관련 테이블을 검사하여 물리적 읽기, 쓰기, 메모리 할당 횟수 등을 확인하십시오.

Performance Schema stage 이벤트는 ALTER TABLE 진행 상황을 모니터링하는 데 사용할 수 있습니다. 자세한 내용은 Section 17.16.1, “Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema”를 참조하십시오.

동시 DML 연산으로 인해 발생한 변경 사항을 기록하고 마지막에 그 변경 사항을 적용하는 데는 어느 정도의 프로세싱 작업이 수반되므로, online DDL 연산은 다른 세션의 테이블 접근을 블록하는 테이블 복사 메커니즘보다 전체적으로 더 오래 걸릴 수 있습니다. 순수 성능의 감소는 테이블을 사용하는 애플리케이션의 더 나은 반응성으로 상쇄됩니다. 테이블 구조 변경 기술을 평가할 때는, 웹 페이지 로드 시간과 같은 요소를 기반으로 한 최종 사용자 관점의 성능 인식을 고려하십시오.