MySQL Online DDL

Last Updated on 1월 20, 2024 by Jade(정현호)

안녕하세요
이번 포스팅에서는 MySQL Index 의 online DDL 에 대한 내용과 버전 별 내용을 확인해보도록 하겠습니다.

Contents

Fast Index Creation
MySQL Online DDL
Online - Algorithm
- INPLACE
- COPY
COPY 알고리즘의 단점
Lock Clause
Online DDL 및 Metadata Lock
Online DDL - Support Detail 5.6
Partitioning Operations
Online DDL - Support Detail 5.7
Online DDL - Support Detail 8.0
Reference

Fast Index Creation

예전 버전의(5.1~5.5) 에서는 Secondary Index에 대한 Fast Index Creation 외에는 테이블 변경 작업 시(DDL) Online 되지 않아 다른 세션의 read , write 작업 모두가 blocking 되었습니다.

이 버전(예전 버전)에서는 테이블 스키마 구조를 변경하게 되면

임시 빈 테이블 생성 -> 새 테이블에 하나씩 one-by-one 복사 -> 행이 삽입될 때 인덱스를 업데이트 수행

그리고 원본 테이블의 모든 행이 새로운 테이블로 복사 -> 원본 테이블이 삭제되고 새로운 복사본 테이블의 이름이 원본 테이블 이름으로 rename 되는 형식으로 처리되었습니다.

MySQL 5.5 이상 또는 InnoDB 플러그인이 있는 MySQL 5.1을 사용하면 InnoDB 테이블에 대한 Secondary Index 생성 및 삭제가 이전보다 훨씬 빠르게 수행할 수 있게 기능이 개선되었습니다

이전 포스팅 참조: Fast Index Creation In InnoDB 기능

Mysql Clustered Index 와 Fast Index Creation In InnoDB

InnoDB - PK - Clustered Index Clustered Index(클러스터형 인덱스)기본 키(PK) 인덱스에 대한 InnoDB 용어입니다 InnoDB의 테이블은 Primary Key 값을 기반으로 구성되며 Cluster 라는 용어 의미대로 PK 값이 비슷한 레코드끼리

* MySQL 5.6 버전의 내용은 5.7버전은 아래에 이어지게 됩니다.

Fast Index Creation 기능이 추가된 5.5 버전의 DDL 처리에 대한 부분이 개선되어 MySQL 5.6 부터 DDL 작업에 대해서 Online DDL 이 추가되었습니다.

Online DDL 기능은 테이블 복사 피하기 위해 CREATE INDEX 및 DROP INDEX를 최적화한 MySQL 5.5에서 사용할 수 있는 Fast Index Creation In InnoDB 기능을 기반으로 합니다

Online DDL 기능은 In-Place 방식의 테이블 변경 과 concurrent DML을 지원하며 이 기능의 장점은 다음과 같습니다.

- 비지니스 적인 다운타임이 불가능한 중요 업무의 운영 환경에서 응답성과 가용성이 향상
- LOCK 절을 사용하여 DDL 작업 중에 성능과 동시성 간의 균형을 조정하는 기능을 제공(Lock 방식 선택)
- 테이블 복사 방법보다 디스크 공간 사용량과 I/O 오버 헤드가 적으며 Undo 를 생성하지 않아 더 효율적인 처리

* 5.6, 5.7 ,8.0 버전 마다 Online DDL의 지원범위가 조금씩은 상이 함으로 버전별로 확인은 필요 합니다.

https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html

명령어 문법)
SQL> alter table 테이블명 add index 인덱스명(컬럼명,컬럼명),
ALGORITHM=[방식], LOCK=[방식];

Online - Algorithm

우선 Online DDL 수행 시 ALTER TABLE 작업은 다음 알고리즘 중 하나를 선택하여 처리됩니다.
(MySQL 8.0 버전은 아래에 내용이 별도로 있습니다)

MySQL 5.6, 5.7 ALGORITHM 구문은 다음과 같이 지정할 수 있습니다.
- INPLACE, COPY

INPLACE

INPLACE(인프레이스) 방식은 원본 테이블에 직접 변경 작업이 발생합니다.

작업의 준비(during preparation) 및 실행 단계에서(execution phases of the operation) 테이블에 대한 배타적 메타 데이터 잠금(exclusive metadata lock)이 잠깐 동안 수행될 수 있습니다.

일반적으로 Concurrent DML이 지원됩니다.

InnoDB 테이블의 Online DDL 작업 중에 발생되는 concurrent DML에 대한 임시 로그 파일 크기로 innodb_online_alter_log_max_size 지정된 사이즈만큼 저장이 가능하고 크기를 초과하면 온라인 DDL 작업이 실패하고 Concurrent DML 작업이 롤백 됩니다.

innodb_online_alter_log_max_size 파라미터의 default 값은 128MB 입니다.

이 임시 파일은 DDL 작업 중에 테이블에 Concurrent DML 데이터를 저장합니다.
임시 파일은 innodb_sort_buffer_size 값 단위로 사용되며 최대 innodb_online_alter_log_max_size에 값까지 사용 가능 합니다.

따라서 이 옵션의 값이 크면 Online DDL 작업 중에 더 많은 DML 에 대해서 처리가 가능하지만, 다시 임시 로그의 데이터를 적용하기 위해서 DDL 작업이 끝나는 때의 기간도 연장됩니다.

INPLACE 에 가능 여부 확인

INPLACE 에 대해서 명시적으로 지원여부를 확인하려면 ALGORITHM=INPLACE 구문을 사용할 수 있습니다.

만약 지원하지 않는 DDL 구문이라면 아래와 같이 에러가 발생됩니다.
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

COPY

COPY ALGORITHM 는 DDL 수행 시 원본 테이블의 복사본 테이블을(복제하는 방식) 생성하여 row 단위로 새로운 테이블에 one-by-one 복사를 하는 처리 방식을 사용합니다.

복사가 완료된 후 복사본 테이블이 원본 테이블명으로 rename 처리되는 방식입니다.
Concurrent DML은 허용되지 않습니다(select 는 가능, LOCK=SHARED 가능)

COPY 알고리즘의 단점

ALGORITHM=COPY 사용시

- DDL 작업을 일시 중지하거나 작업 중에 I/O 또는 CPU 사용을 제한하는 메커니즘은 없음
- 작업 중 문제 발생시 롤백이 발생될 수 있으며 롤백은 많은 비용이 소모되는 작업임
- ALTER 테이블 동안 Concurrent DML은 허용되지 않습니다.
- 상황에 따라서 복제 지연이 발생 소지가 있음

ALGORITHM 구문 생략 또는 DEFAULT 지정시

ALGORITHM=INPLACE 이 사용 가능한 구문에 대해서는 우선 사용하며 그렇지 않을 경우 ALGORITHM=COPY 로 사용되게 됩니다.

즉 ALGORITHM 구문 미사용시 INPLACE -> COPY 순으로 처리됩니다.

Lock Clause

LOCK 절은 테이블에 대한 동시 액세스 수준을 미세 조정하는 데 유용합니다. ALGORITHM 절은 수행되는 방식에 따라서 주로 성능적인 차이를 보이는데 반해 LOCK 절은 동시 Access 에 대한 차이를 보입니다.

기본적으로 MySQL은 DDL 작업 중에 가능한 한 작은 Level의 Lock을 사용합니다. 필요한 경우 더 제한적인 잠금을 적용하기 위해 LOCK 절을 지정할 수 있습니다.

LOCK 절이 특정 DDL 조작에 허용되는 것보다 더 적은 제한적인 Lock 레벨을 지정하면 SQL Statement 는 오류와 함께 실패합니다.
LOCK 절은 가장 제한적인 순서로 아래에 설명되어 있습니다.

LOCK=NONE
Concurrent query 와 Concurrent DML 을 허용 합니다.

LOCK=SHARED
Concurrent query 허용되지만 Concurrent DML 불가합니다.

LOCK=DEFAULT
수행 가능한 동시성 작업에 대해서 허용합니다(concurrent queries, concurrent DML, or both)
LOCK clause 를 생략할 경우 LOCK=DEFAULT 과 동일 합니다.

LOCK=EXCLUSIVE
Concurrent query 와 Concurrent DML 둘 다 불가 합니다.
빠른 시간 내에 DDL이 완료되거나, 동시 쿼리나 DML의 액세스가 필요하지 않을 경우 사용합니다.

Online DDL 및 Metadata Lock

Online DDL 작업은 세 단계로(3 phases) 구분됩니다.

Phase 1: Initialization

초기화 단계에서 Storage Engine 기능, 명령문으로 지정된 작업, 사용자 지정의 algorithm 및 LOCK 옵션을 고려하여 작업에 허용가능한 옵션을 결정합니다.
이 단계에서 테이블의 정의를 보호하기 위해 가능한 shared upgradeable metadata lock 을 수행합니다.

Phase 2: Execution

이 단계에서는 명령문이 준비되고 실행됩니다.
Metadata lock 의 exclusive 여부는 Phase 1 평가된 내역에 따라 다르며, exclusive Metadata lock 이 필요한 경우 alter 문을 준비하는 동안 잠깐 수행됩니다

Phase 3: Commit Table Definition

테이블의 구조가 완료되는 phase 입니다.
metadata lock은 테이블 정의를 커밋하기 위해 exclusive로 업그레이드됩니다 배타적 메타 데이터 lock은 기간은 매우 짧습니다.

위에 설명된 exclusive metadata lock은 요구 사항으로 인해 온라인 DDL 작업은 커밋 또는 롤백을 위해 테이블에 metadata lock을 보유하는 동시 트랜잭션을 기다려야 할 수 있습니다.

Online DDL 작업에서 exclusive metadata lock은 테이블의 후속 변경 트랜잭션을 차단합니다.

Online DDL - Support Detail 5.6

Category	Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Index Operations	Creating or adding a secondary index	Yes	No	Yes	No
	Dropping an index	Yes	No	Yes	Yes
	Adding a FULLTEXT index	Yes*	No*	No	No
	Changing the index type	Yes	No	Yes	Yes

Primary Key Operations	dding a primary key	Yes*	Yes*	Yes	No
	Dropping a primary key	No	Yes	No	No
	Dropping a primary key and adding another	Yes	Yes	Yes	No

Column Operations	Adding a column	Yes	Yes	Yes*	No
	Dropping a column	Yes	Yes	Yes	No
	Renaming a column	Yes	No	Yes*	Yes
	Reordering columns	Yes	Yes	Yes	No
	Setting a column default value	Yes	No	Yes	Yes
	Changing the column data type	No	Yes	No	No
	Dropping the column default value	Yes	No	Yes	Yes
	Changing the auto-increment value	Yes	No	Yes	No*
	Making a column NULL	Yes	Yes*	Yes	No
	Making a column NOT NULL	Yes*	Yes*	Yes	No
	Modifying the definition of an ENUM or SET column	Yes	No	Yes	Yes

Foreign Key Operations	ng a foreign key constraint	Yes*	No	Yes	Yes
	Dropping a foreign key constraint	Yes	No	Yes	Yes

Table Operations	Changing the ROW_FORMAT	Yes	Yes	Yes	No
	Changing the KEY_BLOCK_SIZE	Yes	Yes	Yes	No
	Setting persistent table statistics	Yes	No	Yes	Yes
	Specifying a character set	Yes	Yes*	No	No
	Converting a character set	No	Yes	No	No
	Optimizing a table	Yes*	Yes	Yes	No
	Rebuilding with the FORCEoption	Yes*	Yes	Yes	No
	Performing a null rebuild	Yes*	Yes	Yes	No
	Renaming a table	Yes	No	Yes	Yes

Partitioning Operations

ALTER TABLE Partitioning 절을 제외하고 partitioned InnoDB 테이블에 대한 Online DDL 작업은 일반 InnoDB 테이블에 적용되는 동일한 규칙을 따라서 수행됩니다.

ALTER TABLE partitioning 절은 일반 InnoDB테이블과 달리 Online DDL API를 거치지 않으며 대부분은 ALGORITHM 및 LOCK 절을 지원하지 않습니다

ALTER TABLE 구문에서 Partitioning 절을 사용한다면, Partitioned table은 ALTER TABLE COPY algorithm 을 이용하여 repartitioned 되게 됩니다.

즉, 새로운 파티션 테이블 사용하여 새로운 파티션을 생성하게 됩니다.

새로 생성된 테이블에는 모든 변경 사항이 포함되며 테이블 데이터는 새 테이블 구조로 복사됩니다

Online DDL - Partitioning

MySQL 5.7 에서는 테이블로 더 상세한 내용이 기술되어 있습니다.

ALTER TABLE Partitioning 절이 일반 InnoDB테이블 의 Alter 와 다르게 Internal Online DDL API를 거치지 않더라도 MySQL은 가능한 경우 데이터 복사 및 잠금을 최소화하려고 합니다.

- partitioned by RANGE or LIST 에 대해서 ADD PARTITION and DROP PARTITION 기존 데이터를 copy 하지 않습니다.

- TRUNCATE PARTITION 는 기존 데이터를 copy 하지 않습니다.

- tables partitioned by HASH or LIST 에 대해서 ADD PARTITION and COALESCE PARTITION 작업 진행 간 Concurrent queries는 허용 됩니다
MySQL은 copies the data 하는 동안 LOCK을 shared mode 로 획득하게 됩니다.

- partitioned by LINEAR HASH or LIST 로 구성된 테이블에 대해서 REORGANIZE PARTITION, REBUILD PARTITION, or ADD PARTITION or COALESCE PARTITION 수행은 concurrent queries 수행이 허용됩니다.
MySQL은 copies the data 하는 동안 LOCK을 shared mode 로 획득하게 됩니다.

[참조] partition by range 에 대해서 REORGANIZE PARTITION 수행 시 5.7 버전에서는 ALGORITHM=INPLACE 이 불가능하지만 8.0 에서는 ALGORITHM=INPLACE(Online) 이 가능합니다.

- PARTITION BY 구문절과 REMOVE PARTITIONING 에서 concurrent queries 는 제한됩니다.

- ALTER TABLE의 PARTITION BY 변경 관련된 구문에서는 ALGORITHM=DEFAULT, ALGORITHM=INPLACE, and ALGORITHM=COPY 구문은 허용되지 않습니다

PARTITION BY [LINEAR] KEY에서 사용되는 ALGORITHM 은 ALGORITHM=1 or 2 를 지정할 수 있으며 ALGORITHM=1 은 5.1 과 동일한 Key-hasing function 을 사용하게 되고 ALGORITHM=2 는 MySQL 5.5 and later 에서 사용하는 Key-hasing function 을 사용하게 됩니다.

Default 는 ALGORITHM=2 이며 ALGORITHM=2로 생성된 Key 파티션테이블은 5.1 버전에서 사용할 수 없습니다.

버전 간 다운그레이드 할 때 사용하거나 MySQL 5.5 이후 버전에서 MySQL 5.1의 Key-Hasing Function을 사용한 Key or Linear Key 파티션을 생성하기 위해 사용됩니다.
또한 Lock 절도 지원되지 않습니다.

ex) ALTER TABLE p PARTITION BY LINEAR KEY ALGORITHM=2 (id) PARTITIONS 32;

Online DDL - Support Detail 5.7

MySQL 5.7 에서는 Online DDL이 몇 가지가 추가되었습니다.
아래 표에서 5.7 New 컬럼에 체크되어 있는 항목입니다.

Category	Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata	5.7 New
Index Operations	Creating or adding a secondary index	Yes	No	Yes	No
	Dropping an index	Yes	No	Yes	Yes
	Renaming an index	Yes	No	Yes	Yes	V
	Adding a FULLTEXT index	Yes*	No*	No	No
	Adding a SPATIAL index	Yes	No	No	No	V
	Changing the index type	Yes	No	Yes	Yes

Primary Key Operations	Adding a primary key	Yes*	Yes*	Yes	No
	Dropping a primary key	No	Yes	No	No
	Dropping a primary key and adding another	Yes	Yes	Yes	No

Column Operations	Adding a column	Yes	Yes	Yes*	No
	Dropping a column	Yes	Yes	Yes	No
	Renaming a column	Yes	No	Yes*	Yes
	Reordering columns	Yes	Yes	Yes	No
	Setting a column default value	Yes	No	Yes	Yes
	Changing the column data type	No	Yes	No	No
	Extending VARCHAR column size	Yes	No	Yes	Yes	V
	Dropping the column default value	Yes	No	Yes	Yes
	Changing the auto-increment value	Yes	No	Yes	No*
	Making a column NULL	Yes	Yes*	Yes	No
	Making a column NOT NULL	Yes*	Yes*	Yes	No
	Modifying the definition of an ENUM or SET column	Yes	No	Yes	Yes

Generated Column Operations	Adding a STORED column	No	Yes	No	No	V
	Modifying STORED column order	No	Yes	No	No	V
	Dropping a STORED column	Yes	Yes	Yes	No	V
	Adding a VIRTUAL column	Yes	No	Yes	Yes	V
	Modifying VIRTUAL column order	No	Yes	No	No	V
	Dropping a VIRTUAL column	Yes	No	Yes	Yes	V

Foreign Key Operations	Adding a foreign key constraint	Yes*	No	Yes	Yes
	Dropping a foreign key constraint	Yes	No	Yes	Yes

Table Operations	Changing the ROW_FORMAT	Yes	Yes	Yes	No
	Changing the KEY_BLOCK_SIZE	Yes	Yes	Yes	No
	Setting persistent table statistics	Yes	No	Yes	Yes
	Specifying a character set	Yes	Yes*	No	No
	Converting a character set	No	Yes*	No	No
	Optimizing a table	Yes*	Yes	Yes	No
	Rebuilding with the FORCE option	Yes*	Yes	Yes	No
	Performing a null rebuild	Yes*	Yes	Yes	No
	Renaming a table	Yes	No	Yes	Yes

Tablespace Operations	Enabling or disabling file-per-table tablespace encryption	No	Yes	No	No	V

Partitioning Clause	In Place	Permits DML	Notes
PARTITION BY	No	No	Permits ALGORITHM=COPY, LOCK={DEFAULT\|SHARED\|EXCLUSIVE}
ADD PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Does not copy existing data for tables partitioned by RANGE or LIST. Concurrent queries are permitted for tables partitioned by HASH or LIST. MySQL copies the data while holding a shared lock.
DROP PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Does not copy existing data for tables partitioned by RANGE or LIST.
DISCARD PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT
IMPORT PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT
TRUNCATE PARTITION	Yes	Yes	Does not copy existing data. It merely deletes rows; it does not alter the definition of the table itself, or of any of its partitions.
COALESCE PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Concurrent queries are permitted for tables partitioned by HASH or LIST, as MySQL copies the data while holding a shared lock.
REORGANIZE PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Concurrent queries are permitted for tables partitioned by LINEAR HASH or LIST. MySQL copies data from affected partitions while holding a shared metadata lock.
EXCHANGE PARTITION	Yes	Yes
ANALYZE PARTITION	Yes	Yes
CHECK PARTITION	Yes	Yes
OPTIMIZE PARTITION	No	No	ALGORITHM and LOCK clauses are ignored. Rebuilds the entire table. SeeSection 22.3.4, “Maintenance of Partitions”.
REBUILD PARTITION	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Concurrent queries are permitted for tables partitioned by LINEAR HASH or LIST. MySQL copies data from affected partitions while holding a shared metadata lock.
REPAIR PARTITION	Yes	Yes
REMOVE PARTITIONING	No	No	Permits ALGORITHM=COPY, LOCK={DEFAULT\|SHARED\|EXCLUSIVE}

Extending VARCHAR column size

5.7 버전에서 새로 추가된 Extending VARCHAR column size 부분에 대해서 살펴보겠습니다.

먼저 varchar의 바이트 크기는 사용되는 character set 에 의존하여 계산됩니다.
utf8 은 3바이트이며, utf8mb4 는 최대 4바이트를 지원합니다.

utf8 인 varchar(20) 은 20*3 = 60byte 이고 utf8mb4 인 varchar(20) 은 20*4 = 80 byte 입니다.

varchar 의 크기(byte)가 0~ 255 byte 일때 inplace 가능하며, 256byte ~ 이상으로 일 때 inplace 가능합니다.
즉 위의 2가지 경우에서 inlpace 로 변경할 수 있습니다.

그러므로 255byte -> 256byte 이상은 COPY 방식이 사용 됩니다.

크기가 0 ~ 255 바이트 인 VARCHAR 열의 경우 값을 인코딩하는 데 1 바이트 길이가 필요하고 256 바이트 이상의 VARCHAR 컬럼의 경우 2 바이트 길이가 필요합니다.

값의 길이정보를 담는 바이트에 의해서 0~255 / 256~ 이상 2부분으로 나뉘게 됩니다.

정리하면 Online DDL - Inplace 방식은 VARCHAR 컬럼 크기를 0에서 255 바이트 내에서 또는 256 바이트에서 ~ 더 큰 크기로만 지원합니다.

그래서 utf8 은 varchar(1) 당 3bytes 라서 varchar(85)=> varchar(86) 로 변경할 때 Inplace 방식 대신 Copy 방식이 사용되고,
utf8mb4는 varchar(63)=>varchar(64) 로 변경할 때 Inplace 방식 대신 Copy 방식이 사용됩니다.

그 외 5.7 버전에서 추가된 Online DDL은 Index 에서는 Renaming Index와 Adding Spatial Index이고 STORED/VIRTUAL Column 에 관한 부분이 추가되었습니다.

또한 Tablespace Operation 이 5.7 버전에서 추가되었습니다

Online DDL - Support Detail 8.0

MySQL 8.0.12 버전부터 ADD COLUMN 및 그 외 DDL 작업 시 ALGORITHM=INSTANT 이 지원되기 시작하였습니다.

ALGORITHM=INSTANT support is available for ADD COLUMN and other operations in MySQL 8.0.12.

ALGORITHM=INSTANT 는 Data Dictionary 의 메타 데이터만 수정을 하고, 테이블에 대한 메타 데이터 잠금이 수행되지 않으며 테이블 데이터가 영향을 받지 않으므로 작업이 즉시 수행됩니다

또한 동시 DML 수행도 영향받지 않습니다

ALGORITHM 절은 선택 사항이며 ALGORITHM 절이 생략되면 MySQL은 ALGORITHM = INSTANT를 사용됩니다.

사용이 불가능할 경우 ALGORITHM = INPLACE가 사용됩니다.
ALGORITHM = INPLACE가 지원되지 않으면 ALGORITHM = COPY가 사용됩니다

즉 ALGORITHM 절 생략시 INSTANT -> INPLACE -> COPY 순으로 적용됩니다.

사용 예시:)

SQL> alter table 테이블명 add foorbar varchar(100), ALGORITHM = INSTANT;

INSTANT 에 가능 여부 확인

INSTANT 가 지원되지 않은 구분에서 사용하면 이전의 INPLACE 의 경우처럼 에러가 발생하게 됩니다.

INSTANT 에 대해서 사용 가능여부를 명시적으로 확인하려면 ALGORITHM=INSTANT 구문을 사용할 수 있습니다.

만약 지원하지 않는 DDL 구문이라면 아래와 같이 에러가 발생됩니다.

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

참고) 특정 컬럼 뒤에 추가하는 after 구문을 사용하면 INSTANT 방식으로 추가가 되지 않으며, INPLACE 방식을 사용해야 합니다.

8.0 버전에서 지원되는 목록은 아래와 같고 INSTANT 지원은 별도의 색으로 표기하였습니다.

Category	Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Index Operations	Creating or adding a secondary index	No	Yes	No	Yes	No
	Dropping an index	No	Yes	No	Yes	Yes
	Renaming an index	No	Yes	No	Yes	Yes
	Adding a FULLTEXT index	No	Yes*	No*	No	No
	Adding a SPATIAL index	No	Yes	No	No	No
	Changing the index type	Yes	Yes	No	Yes	Yes

Primary Key Operations	Adding a primary key	No	Yes*	Yes*	Yes	No
	Dropping a primary key	No	No	Yes	No	No
	Dropping a primary key and adding another	No	Yes	Yes	Yes	No

Column Operations	Adding a column	Yes*	Yes	No*	Yes*	No
	Dropping a column	No	Yes	Yes	Yes	No
	Renaming a column	No	Yes	No	Yes*	Yes
	Reordering columns	No	Yes	Yes	Yes	No
	Setting a column default value	Yes	Yes	No	Yes	Yes
	Changing the column data type	No	No	Yes	No	No
	Extending VARCHARcolumn size	No	Yes	No	Yes	Yes
	Dropping the column default value	Yes	Yes	No	Yes	Yes
	Changing the auto-increment value	No	Yes	No	Yes	No*
	Making a column NULL	No	Yes	Yes*	Yes	No
	Making a column NOT NULL	No	Yes*	Yes*	Yes	No
	Modifying the definition of an ENUM or SET column	Yes	Yes	No	Yes	Yes

Generated Column Operations	Adding a STORED column	No	No	Yes	No	No
	Modifying STORED column order	No	No	Yes	No	No
	Dropping a STOREDcolumn	No	Yes	Yes	Yes	No
	Adding a VIRTUAL column	Yes	Yes	No	Yes	Yes
	Modifying VIRTUALcolumn order	No	No	Yes	No	No
	Dropping a VIRTUALcolumn	Yes	Yes	No	Yes	Yes

Foreign Key Operations	Adding a foreign key constraint	No	Yes*	No	Yes	Yes
	Dropping a foreign key constraint	No	Yes	No	Yes	Yes

Table Operations	Changing the ROW_FORMAT	No	Yes	Yes	Yes	No
	Changing the KEY_BLOCK_SIZE	No	Yes	Yes	Yes	No
	Setting persistent table statistics	No	Yes	No	Yes	Yes
	Specifying a character set	No	Yes	Yes*	No	No
	Converting a character set	No	No	Yes*	No	No
	Optimizing a table	No	Yes*	Yes	Yes	No
	Rebuilding with the FORCE option	No	Yes*	Yes	Yes	No
	Performing a null rebuild	No	Yes*	Yes	Yes	No
	Renaming a table	Yes	Yes	No	Yes	Yes

Tablespace Operations	Renaming a general tablespace	No	Yes	No	Yes	Yes
	Enabling or disabling general tablespace encryption	No	Yes	No	Yes	No
	Enabling or disabling file-per-table tablespace encryption	No	No	Yes	No	No

파티션 작업 관련

파티션 작업에서는 INSTANT 지원하지 않습니다.

Partitioning Clause	Instant	In Place	Permits DML	Notes
PARTITION BY	No	No	No	Permits ALGORITHM=COPY, LOCK={DEFAULT\|SHARED\|EXCLUSIVE}
ADD PARTITION	No	Yes	Yes	ALGORITHM=INPLACE, LOCK={DEFAULT\|NONE\|SHARED\|EXCLUSISVE} is supported for RANGE and LIST partitions, ALGORITHM=INPLACE, LOCK={DEFAULT\|SHARED\|EXCLUSISVE} for HASH and KEY partitions, and ALGORITHM=COPY, LOCK={SHARED\|EXCLUSIVE} for all partition types. Does not copy existing data for tables partitioned by RANGE or LIST. Concurrent queries are permitted with ALGORITHM=COPY for tables partitioned by HASH or LIST, as MySQL copies the data while holding a shared lock.
DROP PARTITION	NO	Yes	Yes	ALGORITHM=INPLACE, LOCK={DEFAULT\|NONE\|SHARED\|EXCLUSIVE} is supported. Does not copy data for tables partitioned by RANGE or LIST. DROP PARTITION with ALGORITHM=INPLACE deletes data stored in the partition and drops the partition. However, DROP PARTITION with ALGORITHM=COPY or old_alter_table=ON rebuilds the partitioned table and attempts to move data from the dropped partition to another partition with a compatible PARTITION ... VALUES definition. Data that cannot be moved to another partition is deleted.
DISCARD PARTITION	No	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT
IMPORT PARTITION	No	No	No	Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT
TRUNCATE PARTITION	No	Yes	Yes	Does not copy existing data. It merely deletes rows; it does not alter the definition of the table itself, or of any of its partitions.
COALESCE PARTITION	No	Yes	No	ALGORITHM=INPLACE, LOCK={DEFAULT\|SHARED\|EXCLUSIVE} is supported.
REORGANIZE PARTITION	No	Yes	No	ALGORITHM=INPLACE, LOCK={DEFAULT\|SHARED\|EXCLUSIVE} is supported.
EXCHANGE PARTITION	No	Yes	Yes
ANALYZE PARTITION	No	Yes	Yes
CHECK PARTITION	No	Yes	Yes
OPTIMIZE PARTITION	No	No	No	ALGORITHM and LOCK clauses are ignored. Rebuilds the entire table. SeeSection 22.3.4, “Maintenance of Partitions”.
REBUILD PARTITION	No	Yes	No	ALGORITHM=INPLACE, LOCK={DEFAULT\|SHARED\|EXCLUSIVE} is supported.
REPAIR PARTITION	No	Yes	Yes
REMOVE PARTITIONING	No	No	No	Permits ALGORITHM=COPY, LOCK={DEFAULT\|SHARED\|EXCLUSIVE}

Reference

Reference Link
https://mydbops.wordpress.com/2020/03/04/an-overview-of-ddl-algorithms-in-mysql-covers-mysql-8
https://dev.mysql.com/doc/refman/5.6/en/alter-table-partition-operations.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html
https://dev.mysql.com/doc/refman/5.6/en/alter-table.html
https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

관련된 다른 글

Mysql Clustered Index 와 Fast Index Creation In InnoDB

InnoDB - PK - Clustered Index Clustered Index(클러스터형 인덱스)기본 키(PK) 인덱스에 대한 InnoDB 용어입니다 InnoDB의 테이블은 Primary Key 값을 기반으로 구성되며 Cluster 라는 용어 의미대로 PK 값이 비슷한 레코드끼리 묶어서 저장되어 사용하고 그것을 Clustered Index(PK) 라고 합니다. PK 기반의 테이블 구조이기 때문에 최상의 성능을 위해서는 성능에 가장 중요한 쿼리를 기반으로 신중하게 기본 키 컬럼이 선택되어야 합니다.

MySQL 파티션 추가 삭제 - Error Code: 1493 - 파티션 테이블 - REORGANIZE PARTITION

안녕하세요. 이번 포스팅에서는 월별로 되어있는 range 파티션에 파티션 추가 관련하여 maxvalue 유무, 앞에 추가 또는 뒤에 추가에 따른 명령어 사용 등을 확인 해보도록 하겠습니다.

MySQL Partition 파티션(1) - 정의와 기능 설명 파티션 제약사항

Partition(파티션) 이란 파티션이란 MySQL 서버 입장에서는 데이터를 별도의 테이블로 분리해서 저장하지만 사용자 입장에서는 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 기능을 의미 합니다.

MySQL 8.0.29 INSTANT 컬럼 추가 삭제시 인스턴스 장애 발생 - Crash When INSTANT ADD/DROP columns

안녕하세요 이번 포스팅에서는 MySQL 8.0.29 버전으로 업그레이드 이후 INSTANT 방식으로 컬럼 추가/삭제 작업시 인스턴스 비정상 종료(장애) 에 대한 내용을 확인 해보려고 합니다. 이슈 상황 요약 MySQL 8.0.29 로 업그레이드 한 다음에 Database 를 사용하는 과정에서 INSTANT 방식으로 ADD Column or DROP Column 수행 후에 인스턴스 비정상 종료(Instance Crash) 가 발생되는 현상 입니다.

MySQL - Percona Toolkit - PT-ONLINE-SCHEMA-CHANGE

Percona Toolkit Percona Toolkit 는 Percona 사에서 개발한 오픈소스 기반의 MySQL/MariaDB 의 유틸리티로 기술지원 시 사용하던 유용한 기능의 유틸리티 도구를 하나로 묶은 패키지 입니다. 이러한 Toolkit은 일일이 수동으로 작업하기 다소 복잡하거나 어려운 작업들을 단순하고 수월하게 할수 있게 도와주는 유용한 도구 입니다.

Jade(정현호)

Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io

2 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

이윤형

1 year ago

좋은 글 감사합니다. 실무에서 대용량 Table에 Inplace알고리즘으로 작업을 할경우 innodb_online_alter_log_max_size의 변경이 필요할 텐데 에때 해당 Table에 평상시 발생하는 Transaction의 크기나 이런것을 확인하는 방법이 있을까요??
Online DDL를 할때 얼만큼의 시간이 발생할지 예상하는 방법이 있을까요? -> Performnace_schema를 이용해서 보는 경우 실행을 시켜야 어느정도 예상치의 값을 볼 수 있다고 알고있습니다.

Author

Reply to 이윤형

안녕하세요

해당 테이블에 대해서 트랜잭션 사이즈보다는 변경량(건수)는 확인 하실 수 있습니다.
sys.schema_table_statistics

수치는 누적치임으로 주기적으로 조회를 해봐야하며, 현재값에서 이전값을 -(마이너스) 해서 보셔야 합니다.

DDL 수행 예상 시간은 실행하는 서버의 스펙 또는 RDS Class와 그때 당시의 CPU, IO 현황, 테이블의 사이즈와 테이블의 사용 빈도수 등
다양한 변수가 있기 때문에 운영 환경과 유사한 STG 나 복제 환경, 또는 개발용 Read Replica 인스턴스에서 테스트 스키마 등에서 직접 테스트를 해보셔야할 것 같습니다.

Online DDL 이라도 Aurora MySQL 이 아닌 경우(온프레미스 MySQL 또는 RDS for MySQL) DDL 수행 시간이 길어질 수록 수행 완료 후에 Replication Delay가 발생할 수도 있기 때문에
가급적 시스템의 CPU 와 IO가 여유롭고 바쁘지 않은 업무시간대에 작업하시길 권유 드립니다.

감사합니다.

bug.mysql.com

Statistics

MySQL Online DDL - InnoDB Online DDL - Alter Online

Fast Index Creation

MySQL Online DDL

Online - Algorithm

INPLACE

COPY

COPY 알고리즘의 단점

Lock Clause

Online DDL 및 Metadata Lock

Online DDL - Support Detail 5.6

Partitioning Operations

Online DDL - Support Detail 5.7

Online DDL - Support Detail 8.0

Reference

You may also like...

Fast Index Creation

MySQL Online DDL

Online - Algorithm

INPLACE

COPY

COPY 알고리즘의 단점

Lock Clause

Online DDL 및 Metadata Lock

Online DDL - Support Detail 5.6

Partitioning Operations

Online DDL - Support Detail 5.7

Online DDL - Support Detail 8.0

Reference

You may also like...

CentOS 8 APM(Apache + PHP + MariaDB/MySQL) 설치 및 연동 구성

MySQL 8 - Generated Invisible Primary Keys - GIPK

MySQL 8 MHA and ProxySQL on RockyLinux 8 - 5번째 글