MySQL Online DDL - 8.0.29 추가 기능

MySQL 8.0에서부터 instant algorithm으로 컬럼추가 하는 기능이 도입되었고 dba 작업이 편리해졌습니다. 최근 8.0.29에서 drop column과 add column 순서지정 한것도 instant로 추가 되었습니다.

한가지 주의할 점은 8.0.29 부터는 아래와 같이 테이블의 TOTAL_ROW_VERSIONS 값이 64를 넘을 경우 inplace나 copy로 처리된다는점 입니다.

 

When a table with instantly added or dropped columns is rebuilt by table-rebuilding [ALTER TABLE] or [OPTIMIZE TABLE] operation, the TOTAL_ROW_VERSIONS value is reset to 0. The maximum number of row versions permitted is 64, as each row version requires additional space for table metadata. When the row version limit is reached, ADD COLUMN and DROP COLUMN operations using ALGORITHM=INSTANT are rejected with an error message that recommends rebuilding the table using the COPY or INPLACE algorithm.

 

테스트 내용

 - TOTAL_ROW_VERSIONS이 64일때, add column을 수행할 경우 inplace로 수행되면서 수행시간이 오래 걸림

 - TOTAL_ROW_VERSIONS값은 0으로 초기화 

[slave3] Mon Aug 15 09:24:03 2022 [root]:[etl] >alter table migration_table_list add column test_col varchar(64);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

[slave3] Mon Aug 15 09:25:45 2022 [root]:[etl] >SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES where name = 'etl/migration_table_list';
+--------------------------+--------------------+
| NAME                     | TOTAL_ROW_VERSIONS |
+--------------------------+--------------------+
| etl/migration_table_list |                 64 |
+--------------------------+--------------------+
1 row in set (0.00 sec)

[slave3] Mon Aug 15 09:25:58 2022 [root]:[etl] >alter table migration_table_list add column test_col varchar(64);
Query OK, 0 rows affected (18.97 sec)
Records: 0  Duplicates: 0  Warnings: 0

[slave3] Mon Aug 15 09:26:23 2022 [root]:[etl] >SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES where name = 'etl/migration_table_list';
+--------------------------+--------------------+
| NAME                     | TOTAL_ROW_VERSIONS |
+--------------------------+--------------------+
| etl/migration_table_list |                  0 |
+--------------------------+--------------------+

 

 

Online DDL Support for Column Operations

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a column	Yes* 8.0.29 부터 순서 상관 없이 처리됨	Yes	No*	Yes*	Yes
Dropping a column	Yes* 8.0.29에서 추가	Yes	Yes	Yes	Yes
Renaming a column	Yes*	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 VARCHAR column 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

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata