1. 개요

MySQL은 2000년대부터 웹서비스를 많이 사용하는 개발자들에 의해 많이 사용되었습니다. 오픈 소스로서 사용이 쉬웠을 뿐 아니라 개발자들이 다루기 쉽고 이해하기 쉬운 관계형 데이터베이스이기 때문에 빠르고 가볍게 개발할 수 있는 웹서비스에 최적의 데이터베이스였습니다.

하지만, MySQL은 서비스가 잘되고 사용자가 늘어날수록 운영하기 쉽지 않았습니다. 여러 가지 문제가 있었지만, 그 중 하나가 서비스가 운영 중인 상황에서 Online DDL을 수행하기 어렵다는 점이었습니다.

이 문제를 해결하기 위해, MySQL은 Ver 5.6부터 기존 COPY 알고리즘을 개선한 In-Place 알고리즘을 추가했습니다. In-Place 알고리즘을 통해 테이블 가용성을 높일 수 있었지만 사용이 편한 것은 아니었습니다. COPY 알고리즘 보다 개선되기는 했지만 DDL 작업의 시작과 종료 시점에 메타 락을 획득해야하고, 작업 종류에 따라서는 Copy 알고리즘과 마찬가지로 임시 테이블 생성 및 데이터 복제 과정도 필요하기 때문입니다. 이 때문에 MySQL Ver. 8.0 부터는 Instant 알고리즘을 추가해 테이블 사이즈와 상관없이 간단한 DDL 작업을 수행할 수 있도록 개선했습니다.

MySQL은 현재 이렇게 세 가지 ALTER DDL 알고리즘을 제공합니다. 그래서, 이 문서에서는 MySQL의 ALTER DDL에 관한 주요 함수와 알고리즘, 알고리즘간 Meta Lock 동작을 비교해보며 내부 동작 과정을 자세히 살펴보고자 합니다. 이를 통해 MySQL을 사용하는 서비스 운영에 도움이 되기를 바랍니다.

2. ALTER DDL 문의 동작 흐름

먼저, MySQL이 ALTER DDL을 크게 어떤 단계로 수행하는지 알아보겠습니다.

MySQL은 DDL 수행 알고리즘과 무관하게 크게 3단계로 작업을 진행합니다. 단, 후술할 상세 로직 및 함수들은 In-Place/Instant 알고리즘 위주로 작성되어 있습니다.

2.1 Initialization 단계

해당 ALTER DDL 문 실행에 사용할 알고리즘과 Lock 유형을 선택하는 단계입니다.

작업 순서는 다음과 같습니다.

사용자가 명시한 알고리즘으로 DDL문을 수행할 수 있는지 확인합니다.
⇒ 컬럼 이름의 중복이나 존재하지 않는 컬럼을 수정하려는 등 테이블 메타 데이터 조회로 바로 확인이 가능한 문제들을 체크합니다.
handler::check_if_supported_inplace_alter() 함수로 ALTER TABLE의 실행에 필요한 Lock 모드를 확인합니다.
⇒ 함수가 반환한 Lock 모드와 사용자가 지정한 Lock 모드를 비교합니다. 둘 사이에 충돌이 발생한다면 오류를 발생하고 작업을 중지합니다.
ex) HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE 인데 LOCK=none 설정을 한 경우에 해당합니다.

2.2 Execution 단계

실제 DDL 작업을 수행하는 단계입니다. 앞단계에서 선택한 알고리즘과 Lock 모드를 실제 수행합니다.

[참고]

MySQL에서 사용하는 Metadata Lock은 여러가지가 있습니다. 이 문서에서 나오는 Metadata Lock에 대해서만 간단히 정리하면 다음과 같습니다.

❍ MDL_SHARED_UPGRADABLE : 승격이 가능한 Shared Metadata Lock으로 다른 세션의 읽기/쓰기를 허용하는 Lock

❍ MDL_SHARED_READ : 테이블을 읽을 때 사용하는 Shared Metadata Lock 으로 다른 세션에 영향을 주지 않고, 이 Lock 을 획득한 세션은 테이블의 메타데이터와 데이터를 읽을 수 있다.

❍ MDL_SHARED_NO_WRITE : 승격이 가능한 Shared Metadata Lock으로 다른 세션의 읽기 작업은 막지 않지만, 쓰기 작업은 차단하는 Lock, 이 Lock을 획득한 세션은 Metadata를 읽고 데이터도 읽을 수 있다.

❍ MDL_EXCLUSIVE : Metadata Lock 중 가장 차단 단계가 높은 Lock으로 다른 세션의 모든 접근을 차단하는 Lock

상세 작업 순서는 다음과 같습니다.

앞서 선택한 Lock 모드에 필요한 Metadata Lock을 획득합니다. 이 단계에서, MDL_SHARED_UPGRADABLE Metadata Lock을 획득합니다.
In-Place 알고리즘을 선택했다면, Metadata Lock을 MDL_EXCLUSIVE로 승격하여 다른 세션에서 Metadata Lock을 획득하는 것을 차단합니다.
handler:ha_prepare_inplace_alter_table()을 호출해 제약사항을 체크하고, 인덱스와 외래키의 Metadata를 업데이트합니다. Instant 알고리즘의 경우 이 단계를 수행하지 않습니다.
In-Place 알고리즘을 선택했다면, 2번 단계에서 승격했던 Lock 모드를 다시 강등합니다.
1. DDL 수행 도중 DML이 차단되어야 한다면 Metadata Lock을 MDL_SHARED_NO_WRITE로 강등합니다.
2. 그렇지 않다면, MDL_SHARED_UPGRADABLE로 강등합니다.
ALTER TABLE에 의해 요청된 변경사항을 실행하기 위해 handler::ha_inplace_alter_table()을 호출합니다. Instant 알고리즘의 경우 이 단계를 수행하지 않습니다.
작업을 수행하는 테이블에 대한 Metadata Lock을 MDL_EXCLUSIVE로 승격합니다.
⇒ 승격 후, handler::ha_commit_inplace_alter_table()을 호출하여 테이블의 Metadata를 변경합니다.

2.3 Final 단계

ALTER 작업이 모두 마무리 된 후 정리하는 단계입니다. 지금까지의 변경 내역을 기반으로 Data Dictionary를 업데이트하고 커밋합니다. 스토리지 엔진의 atomic DDL지원 여부에 따라 다음과 같이 마무리 작업을 진행합니다.

Atomic DDL을 지원하는 스토리지 엔진의 경우:
⇒ Old_Table과 New_Table간의 테이블 이름을 변경하는 작업을 먼저 수행하고 Data Dictionary를 업데이트, 커밋합니다.
Atomic DDL을 지원하지 않는 스토리지 엔진의 경우:
⇒ Data Dictionary를 업데이트 하고, Old_Table과 New_Table의 테이블 이름을 변경하는 작업을 수행합니다.

마지막으로 지금까지 사용한 객체와 메모리를 정리하고, MDL_SHARED_READ Lock을 획득해 변경된 테이블의 Metadata를 최종 확인하며 마무리합니다.

3. ALTER DDL 주요 함수 설명

ALTER DDL문이 수행될 때 즉, Execution 단계에서 사용되는 주요 함수는 다음과 같습니다.

ha_prepare_inplace_alter_table
ha_inplace_alter_table
ha_commit_inplace_alter_table

위 함수들에 대해 간단히 알아보도록 하겠습니다.

3.1 ha_prepare_inplace_alter_table

ALTER DDL과 현재 테이블 명세를 비교합니다. 테이블의 제약사항을 체크하고, 인덱스나 외래키에 대한 Metadata를 업데이트하는 역할을 합니다.

여기에서 확인하는 제약사항은 다음과 같습니다.

인덱스 이름 유효성
컬럼 이름에 대한 제약

이 단계는 모든 알고리즘에서 수행되는 단계는 아닙니다. Instant 알고리즘을 사용하겠다고 한 ALTER DDL문이라면 이 단계에서 아무 작업도 수행하지 않고 바로 빠져나옵니다.

소스 코드 상에서 다음과 같이 확인이 가능합니다.

/* prepare_inplace_alter_table_impl */
  if (is_instant(ha_alter_info)) {
    Instant_Type type = innobase_support_instant(ha_alter_info, indexed_table,
                                                 this->table, altered_table);

    if (type == Instant_Type::INSTANT_ADD_DROP_COLUMN) {
      ut_a(is_valid_row_version(indexed_table->current_row_version + 1));
    }

    return false;
  }

3.2 ha_inplace_alter_table

이 함수안에서 실제 ALTER 작업이 수행됩니다. 다음과 같은 작업들이 이 함수 안에서 수행됩니다.

테이블 리빌드 작업
DML 로그 적용
인덱스 구성

Instant 알고리즘을 사용하거나, 또는 In-Place 알고리즘을 사용하지만 인덱스 구성 작업이 아니면서 테이블 리빌드 작업도 필요없다면 이 함수 안에서 작업을 수행하지 않습니다.

소스 코드 상에서 다음과 같이 확인이 가능합니다.

/* INNOBASE_ALTER_DATA : 인덱스 추가이거나, 테이블 리빌드가 필요한 경우 */
 if (!(ha_alter_info->handler_flags & INNOBASE_ALTER_DATA) || 
     is_instant(ha_alter_info)) {
   return all_ok();
 }

3.3 ha_commit_inplace_alter_table

이 함수는 Execution 마지막 단계에서 호출되어 ALTER 문으로 변경된 테이블 내역에 따라 Data Dictionary를 수정하고 작업 내용을 최종 커밋합니다. 단, 스토리지 엔진이 Atomic DDL을 지원하는 경우 실제 커밋은 Final 단계에서 이루어집니다.

이 함수에서는 다음과 같은 작업을 진행합니다.

테이블 이름 변경 시 임시로 사용할 이름 생성
통계 정보를 수집하는 스레드가 DDL 대상 테이블에 접근하지 못하게 설정
원본 테이블과 새롭게 변경된 테이블간의 Metadata 동기화

Instant 알고리즘을 사용하는 경우 dd_add_instant_columns 함수를 실행하여 다음의 작업을 수행합니다.

테이블의 Metadata 내의 row_version 값 변경
Default Value 설정

dd_add_instant_column 함수 코드를 보면 다음과 같이 확인이 가능합니다.

bool dd_add_instant_columns(const dd::Table *old_dd_table, 
                            dd::Table *new_dd_table, 
                            dict_table_t *new_dict_table, 
                            const Columns &cols_to_add) { 
    .... 
    auto set_col_default = [&](Field *field, dd::Properties &se_private) {
    ....
    DD_instant_col_val_coder coder; 
    size_t length = 0; 
    const char *value = coder.encode(reinterpret_cast(dfield.data), 
                                   dfield.len, &length); 
   
    dd::String_type default_value; 
    default_value.assign(dd::String_type(value, length)); 
    se_private.set(dd_column_key_strings[DD_INSTANT_COLUMN_DEFAULT], 
                 default_value); 
    }
 
    for (const auto new_column : cols_to_add) { 
      Field *field = new_column; 
       
      .....
       
      /* ROW_VERSION 변경 후 추가 */ 
      se_private.set(dd_column_key_strings[DD_INSTANT_VERSION_ADDED], 
                     new_dict_table->current_row_version + 1); 
       
      /* Instant 알고리즘으로 추가한 row의 위치 (8.0.29 이후 사용안됨) */ 
      se_private.set(dd_column_key_strings[DD_INSTANT_PHYSICAL_POS], 
                     next_phy_pos + cols_added++); 
       
      /* 컬럼의 DEFAULT 값을 null로 설정 */ 
      if (field->is_real_null()) { 
        se_private.set(dd_column_key_strings[DD_INSTANT_COLUMN_DEFAULT_NULL], 
                       true); 
        continue; 
      }
       /* 컬럼의 Default 값 설정 */ 
      set_col_default(field, se_private); 
    }
    ...
}

4. ALTER DDL 알고리즘 동작 방식

MySQL은 ALTER DDL문을 수행할 때 3가지 알고리즘 중 하나를 선택해 수행합니다.

Copy
In-Place
Instant

4.1 Copy 알고리즘

MySQL에서 가장 먼저 사용한 알고리즘으로, 가장 단순한 방식으로 동작합니다. Copy 알고리즘으로 ALTER DDL이 수행되면 해당 테이블에 대한 읽기 작업은 가능하지만 쓰기 작업은 불가합니다.

MySQL Ver. 8.0 기준으로 다음 작업들은 Copy 방식으로만 진행됩니다.

Primary Key 에 대한 Drop
Column Type 변경
Character Set Convert
스토리지 엔진이 Copy만 지원하는 경우

동작 방식은 다음과 같습니다.

수행하고자 하는 DDL 문이 적용된 새로운 테이블을 생성
현재 사용 중인 테이블의 데이터를 새로운 테이블로 복사
복사 완료 후 테이블의 이름을 변경하고 기존 테이블 제거

로직을 통해서도 확인할 수 있듯이, Copy 알고리즘은 많은 단점이 있습니다.

작업이 완료될 때까지 다른 세션의 모든 쓰기 작업이 차단됩니다.
전체 테이블이 복사되는 알고리즘이라 많은 디스크 공간이 확보되어야 수행이 가능합니다.
전체 테이블의 데이터를 복사해야 하기 때문에 Disk I/O를 많이 사용합니다.
테이블 사이즈에 따라 작업 시간이 영향을 받습니다.
Replication 구성이 되어있다면 Source/Replica 간에 Gap이 많이 발생하여 문제가 될 수 있습니다.
롤백을 진행한다면 많은 시간이 필요할 수 있습니다.

4.2 In-Place 알고리즘

Copy 알고리즘 방식을 개선하기 위해 MySQL Ver. 5.6에 추가된 알고리즘입니다. 일부 케이스를 제외하면 ALTER DDL이 수행될 때 다른 세션의 읽기/쓰기 작업이 모두 가능하고, 필요에 따라 테이블을 리빌딩할 수도 있습니다.

In-Place 알고리즘 사용 시, 작업 전에 테이블 리빌딩이 필요한 작업인지 아닌지 확인하고 ALTER 작업을 위한 작업 공간을 미리 확보해 두어야 합니다. 다른 세션의 읽기/쓰기 작업이 가능하기 때문에 서비스 중 상황에서 작업이 가능하지만, 특정 케이스에서는 쓰기 작업이 불가한 경우도 있기에 유의가 필요하며, 테이블 사이즈가 크면 시간이 오래 걸리고, 트랜잭션에도 어느 정도 영향을 줄 수 있기 때문에 주의가 필요합니다.

In-Place 알고리즘은 Copy 알고리즘으로만 가능한 작업을 제외한 대부분의 ALTER DDL 작업이 가능합니다.

동작 방식은 다음과 같습니다. (테이블 리빌드가 필요한 작업을 기준으로 설명합니다.)

테이블 재구축에 필요한 임시 테이블 생성
현재 사용 중인 테이블의 데이터를 임시 테이블로 복사
⇒ 필요한 경우 데이터 타입 변경과 같은 변환 작업이 적용됨
레코드 Copy 중 유입되는 쓰기 작업은 별도의 버퍼에 DML Log 적재
Copy 작업이 완료되면, DML Log에 적재된 내용을 새로운 테이블에 적용
테이블의 이름을 변경하고 기존 테이블 및 DML Log 제거

[참고]

내부 테스트 결과 In-Place 알고리즘에서 사용하는 임시 테이블 작업은 Copy 알고리즘에서 사용하는 전체 데이터 복사와 다른 작업인 것으로 추정됩니다. 물론 작업에 따라 원본 테이블 크기에 버금가는 상당한 공간이 필요하지만, Copy 방식보다는 In-Place가 더 빠르게 동작합니다.

In-Place 알고리즘은 다음과 같은 단점이 있습니다.

모든 작업이 재구축없이 진행되는 것은 아닙니다. 일부 작업은 시간이 많이 소요될 수 있습니다.
역시나 테이블 사이즈에 작업 시간이 영향을 받습니다.
여전히 다른 세션의 쓰기 작업을 차단하는 경우가 있습니다.
처리량이 많은 MySQL서버에서는 높은 I/O 사용량을 유발하여 서비스에 영향을 줄 수 있습니다.
장시간 수행될 경우 Replication Gap을 발생시킬 수 있습니다.
2번의 Exclusive Metadata Lock이 수행되어 서비스에 영향을 줄 수 있습니다.
역시나 작업 시 필요한 공간 확보가 필요할 수 있습니다.
innodb_online_alter_log_max_size 시스템 변수 설정을 잘 해야 작업이 성공할 수 있습니다.

4.3 Instant 알고리즘

MySQL Ver. 8.0에서 추가된 새로운 ALTER DDL 알고리즘입니다. Metadata만 변경하여 수행되는 알고리즘으로, 최소한의 Metadata Lock만 획득하여 ALTER DDL 작업을 수행합니다. 현재 제공되는 ALTER 수행 알고리즘 중 가장 빠른 알고리즘으로 서비스에 거의 영향을 주지 않습니다.

Instant 알고리즘은 다음의 ALTER DDL 작업들을 지원하며 점차 지원 범위가 확대되고 있습니다.

컬럼 추가/삭제
컬럼의 Default Value 설정
ENUM 타입 컬럼의 명세 변경

동작 방식은 다음과 같습니다.

대상 테이블의 Metadata 변경
Row Version 값 수정

Instant 알고리즘은 가장 단순하게 작업을 수행해 많은 장점이 있어 보이지만, 아직 다음과 같은 단점이 있습니다.

사용할 수 있는 작업이 적습니다.
64번까지 작업 후에는 테이블 리빌딩 작업을 한 후에 다시 수행할 수 있습니다.
컬럼의 추가/삭제는 FULLTEXT 인덱스가 있거나 ROW_FORMAT=COMPRESSED인 테이블에서는 수행이 불가합니다.
1번의 Exclusive Metadata Lock이 수행되어 잠깐이지만 서비스에 영향이 있을 수 있습니다.

4.4 알고리즘 비교 분석

ALTER DDL 수행 시 사용되는 3가지 알고리즘을 비교하면 다음과 같이 표로 정리해 볼 수 있습니다.

기능	Copy 알고리즘	In-Place 알고리즘	Instant 알고리즘
테이블 복사 필요 여부	Yes	부분적/개념적 (내부 중간 구조 사용)	No
테이블 데이터 재구축	Yes	작업에 따라 다름	No
메타데이터만 수정	No	작업에 따라 다름	Yes
동시 DML 지원	No	작업에 따라 다름	Yes
필요한 Lock 모드	작업 완료까지 Exclusive DML Lock 수행	작업에 따라 다름	짧은 Metadata Lock 필요
디스크 공간 오버헤드	높음 - 전체 테이블 복사	중간 - 임시 파일, 로그	매우 낮음
성능 영향 (시간)	가장 느림	작업 종류와 테이블 사이즈에 따라 다름	가장 빠름
자원 사용량 (CPU/IO)	높음	중간	낮음
주요 제한 사항	다른 세션의 쓰기를 차단 자원 집약적	innodb_online_alter_log_max_size 설정 필요 종류에 따라 다른 세션의 쓰기를 차단	제한된 작업 종류 64회의 제한 (해결 가능)
사용 가능한 MySQL Version	모든 버전	MySQL Ver 5.6 이상	MySQL Ver 8.0.12 이상 컬럼 추가 외 기능은 MySQL 8.0.29 부터 지원

5. In-Place / Instant 알고리즘 Metadata Lock 비교 분석

In-Place 알고리즘과 Instant 알고리즘 모두 최소한의 Metadata Lock을 사용하여 서비스 중에 사용할 수 있게 설계되었지만, 내부 동작방식에 따라 수행되는 Lock 종류도 다르고, 필요한 시기도 다릅니다. 두 알고리즘이 어떤 단계에서 어떤 Metadata Lock을 사용하고 어느 부분에서 Exclusive Metadata Lock이 필요한지 살펴보겠습니다.

두 알고리즘 로직의 Metadata Lock 사용에 대한 내용만 정리하면 다음과 같이 간단히 정리해 볼 수 있습니다.

5.1 전체 로직에 대한 비교 설명

이제 위 그림에서 표현한 Metadata Lock 사용 방식을 주요 단계별로 간단히 설명합니다.

5.1.1 prepare_inplace_alter 함수 실행 전 단계

두 알고리즘 모두 ALTER DDL 작업 수행 시 MDL_SHARED_UPGRADABLE Metadata Lock을 획득합니다. MDL_SHARED_UPGRADABLE Metadata Lock은 다른 세션의 읽기/쓰기를 모두 허용하기 때문에 이 단계까지 다른 세션들의 작업은 영향을 받지 않습니다.

하지만, In-Place 알고리즘은 MDL_SHARED_UPGRADABLE Meta Lock 획득 후 prepare_inplace_alter 함수 진입 전에 해당 Lock을 MDL_EXCLUSIVE로 승격하여 다른 세션이 해당 테이블에 접근하지 못하게 막습니다. 그래서, 이 단계부터 In-Place 알고리즘을 사용하는 세션으로 인해 다른 세션들은 Meta-Lock 대기 상태에 빠질 수 있습니다.

5.1.2 prepare_inplace_alter 함수 작업 완료 후 단계

prepare_inplace_alter 함수 작업 완료 후 In-Place 알고리즘을 수행하는 세션은 MDL_EXCLUSIVE로 변경했던 Meta Lock을 MDL_SHARED_UPGRADABLE로 강등합니다. 즉, In-Place 알고리즘은 prepare_inplace_alter 함수 수행 시 MDL_EXCLUSIVE Metadata Lock이 필요하고, 함수 실행 시에는 다른 세션이 해당 테이블에 접근할 수 없음을 의미합니다.

5.1.3 inplace_alter_table 함수 작업 시

두 알고리즘 모두 MDL_SHARED_UPGRADABLE Metadata Lock을 획득한 상태로 이 함수를 실행합니다. Instant 알고리즘을 사용하는 경우나, In-Place 알고리즘 중 인덱스 구성 작업이 아니면서 테이블을 리빌딩하지 않는 경우엔 이 함수 안에서 특별히 하는 것은 없습니다.

5.1.4 commit_inplace_alter_table 함수 실행 전 단계

inplace_alter_table 함수 완료 후 commit_inplace_alter_table 함수 진입 전에는 두 알고리즘 모두 Metadata Lock 승격이 진행되어 MDL_EXCLUSIVE Metadata Lock을 획득하게 됩니다. 이 단계부터 다시 다른 세션에서 해당 테이블에 접근하는 것은 차단됩니다.

두 알고리즘 모두 MDL_EXCLUSIVE Metadata Lock을 획득하고, commit_inplace_alter_table 함수 내 작업을 수행합니다. 해당 함수 내 작업이 모두 완료될 때까지 다른 세션들은 해당 테이블에 접근하는 것이 차단되기 때문에, 이 단계에서도 Meta-Lock 대기 상태에 빠지는 세션들이 있을 수 있습니다.

5.1.5 commit_inplace_alter_table 함수 완료 후

마지막으로 ALTER DDL 작업의 마지막 단계인 Final 단계에서 최종적으로 Metadata 확인을 위해 MDL_SHARED_READ Metadata Lock을 획득합니다.

[참고]

In-Place 알고리즘에서 Metadata Lock 사용은 대부분 동일하지만, 예외 케이스가 있습니다. Auto_increment 속성을 가진 INT 타입의 컬럼을 추가하는 경우가 그 예외에 해당합니다. 이 경우에는 실제 ALTER 작업 수행을 위해 MDL_SHARED_UPGRADABLE로 강등하지 않고, MDL_SHARED_NO_WRITE로 강등합니다.

5.2 Metadata 수정만 진행되는 작업에 대한 비교

In-Place 알고리즘을 사용하는 ALTER DDL 작업 중에도 Metadata 수정만으로 진행되는 작업이 있습니다. 큰 틀에서 Instant 알고리즘과 다르지 않는 것으로 보이는데요, 실제 작업 시에도 그러한지 테스트를 통해 확인해 보았습니다.

디버깅을 통해 확인해 본 결과, Metadata만 수정하는 작업을 In-Place 알고리즘으로 수행하는 경우, 그렇지 않은 작업과 동일하게 prepare_inplace_alter 함수 수행 시 MDL_EXCLUSIVE Metadata Lock으로 승격하여 작업하는 것을 확인하였습니다.

/* mysql_inplace_alter_table() */

/* prepare_inplace_alter 과정 진입 전, 메타데이터 락을 MDL_EXCLUSIVE로 업그레이드합니다. */
 else if (inplace_supported == HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE ||  
           inplace_supported == HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE) {  

  /*  여기서 메타데이터 락을  MDL_EXCLUSIVE로 업그레이드 합니다. */  
  if (thd->mdl_context.upgrade_shared_lock(table->mdl_ticket, MDL_EXCLUSIVE,  
                                           thd->variables.lock_wait_timeout))  
    goto cleanup;  
 
...

소스 내용을 통해 확인할 수 있듯이 알고리즘만 구분하여 Metadata Lock을 승격하기 때문에 Metadata만 수정하더라도 In-Place 알고리즘내에서의 Metadata Lock은 차이가 없다는 것을 확인할 수 있습니다.

5.3 요약

두 알고리즘의 비교 결과를 정리하면 다음과 같이 정리해 볼 수 있습니다.

6. 마무리

지금까지 ALTER DDL의 실행 순서와 알고리즘별 상세 동작, 알고리즘별 특징과 사용하는 Metadata Lock Type에 대해 알아보았습니다.

그러면, 실제 ALTER 작업을 어떻게 하는 것이 가장 좋을까요? 작업에 따라 사용 가능한 알고리즘이 다르고, Instant가 아닌 In-Place나 Copy 알고리즘이 필요한 경우가 많아 선택에 제약이 있습니다.

하지만 어떤 알고리즘을 사용하든 가장 좋은 방법은 ALTER 수행 시 ALGORITHM 구문을 명시하는 것입니다. 그래야 예기치 않게 느린 알고리즘으로 대체되는 것을 막을 수 있기 때문입니다. 이는 DBA가 예상치 못한 서비스 장애를 막을 수 있는 방어적 수행 방식입니다.

또한, 어떤 DDL이든 Exclusive Metadata Lock이 짧게라도 수행되기 때문에 ALTER 작업 시 장시간 수행되는 트랜잭션이 없어야 합니다. 미리 세션에서 동작하는 작업들을 확인하고 ALTER 작업을 수행해야 합니다.

즉, 다음과 같은 습관이 필요합니다.

ALTER 명령문 작성 시 ALGORITHM 구문 작성
수행 전 장시간 동작하는 트랜잭션 및 쿼리 확인

위 내용이 이 글을 읽는 분들에게 많은 도움이 되었으면 합니다.

참고 자료

MySQL Server 8.0.32 - DDL Phase,
https://github.com/percona/percona-server/blob/8.0/sql/handler.h#L6180-L6298
MySQL Server 8.0.32 - Lock Mode,
https://github.com/mysql/mysql-server/blob/trunk/storage/innobase/handler/handler0alter.cc#L949-L963
MySQL Server 8.0.32 - Metadata Lock,
https://github.com/mysql/mysql-server/blob/trunk/sql/mdl.h#L195-L350
MySQL 8.0 Reference Manual - 17.12.1 Online DDL Operations
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-table-operations
MySQL 8.0 Reference Manual - 17.12.8 Online DDL Limitations
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-limitations.html
MySQL 8.0 Reference Manual - 17.12.2 Online DDL Performance and Concurrency
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-performance.html
MySQL 8.0 Reference Manual - 17.14 InnoDB Startup Options and System Variables
https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html

MySQL ALTER DDL 수행 방식에 대한 이해