MySQL Online DDL(8) [Table Operations]

Table 관련 DDL 작업 중 Online DDL이 적용되는 것은 다음과 같다.

Table 상태를 변경하는 작업이므로 대부분 Rebuild가 이루어져야 한다.

이 전과 다른 점이라면 Table명 변경이 보다 쉽게 이루어지고 Instant 알고리즘을 통해

Metadata만 변경하여 처리가 가능하기 때문에 해당 부분에는 확실한 발전이 있다고 보겠다.

 

ROW_FORMAT 변경

mysql 5.7 버전이 되면서 ROW_FORMAT의 기본값이 Dynamic으로 변경되었다.

해당 값은 information_schema 데이터베이스의 테이블 정보 조회로 확인이 가능하다.

SELECT * FROM information_schema.TABLES

WHERE TABLE_SCHEMA='test' AND TABLE_NAME='alter_test_tbl2' \G;

 

*************************** 1. row ***************************

  TABLE_CATALOG: def

   TABLE_SCHEMA: test

     TABLE_NAME: alter_test_tbl2

     TABLE_TYPE: BASE TABLE

         ENGINE: InnoDB

        VERSION: 10

     ROW_FORMAT: Dynamic

     TABLE_ROWS: 9937205

 AVG_ROW_LENGTH: 121

    DATA_LENGTH: 1205846016

MAX_DATA_LENGTH: 0

   INDEX_LENGTH: 0

      DATA_FREE: 3145728

 AUTO_INCREMENT: NULL

    CREATE_TIME: 2021-10-27 09:02:28

    UPDATE_TIME: NULL

     CHECK_TIME: NULL

TABLE_COLLATION: utf8mb4_0900_ai_ci

       CHECKSUM: NULL

 CREATE_OPTIONS:

  TABLE_COMMENT:

 

InPlace 알고리즘 사용을 제공하지만 결국 테이블 내에서 재편성이 이루어지기 때문에 고비용을 발생 시킨다.

ALTER TABLE alter_test_tbl2 ROW_FORMAT=COMPRESSED,

ALGORITHM=INPLACE, LOCK=NONE;

 

SELECT * FROM information_schema.TABLES

WHERE TABLE_SCHEMA='test' AND TABLE_NAME='alter_test_tbl2' \G;

 

*************************** 1. row ***************************

  TABLE_CATALOG: def

   TABLE_SCHEMA: test

     TABLE_NAME: alter_test_tbl2

     TABLE_TYPE: BASE TABLE

         ENGINE: InnoDB

        VERSION: 10

     ROW_FORMAT: Compressed

     TABLE_ROWS: 9937205

 AVG_ROW_LENGTH: 121

    DATA_LENGTH: 1205846016

MAX_DATA_LENGTH: 0

   INDEX_LENGTH: 0

      DATA_FREE: 3145728

 AUTO_INCREMENT: NULL

    CREATE_TIME: 2021-11-09 01:09:40

    UPDATE_TIME: NULL

     CHECK_TIME: NULL

TABLE_COLLATION: utf8mb4_0900_ai_ci

       CHECKSUM: NULL

 CREATE_OPTIONS: row_format=COMPRESSED

  TABLE_COMMENT:

 

KEY_BLOCK_SIZE 변경

ROW_FORMAT이 COMPRESSED 일 때, 압축해서 저장할 값의 BLOCK 사이즈이다.

해당 값 변경 역시 InPlace 알고리즘을 제공하나 테이블의 재구성이 발생되고 DML의 작업이 가능할 뿐 고비용이 발생된다.

ALTER TABLE alter_test_tbl2 KEY_BLOCK_SIZE = 16,

ALGORITHM=INPLACE, LOCK=NONE;

 

SELECT * FROM information_schema.TABLES

WHERE TABLE_SCHEMA='test' AND TABLE_NAME='alter_test_tbl2' \G;

 

*************************** 1. row ***************************

  TABLE_CATALOG: def

   TABLE_SCHEMA: test

     TABLE_NAME: alter_test_tbl2

     TABLE_TYPE: BASE TABLE

         ENGINE: InnoDB

        VERSION: 10

     ROW_FORMAT: Compressed

     TABLE_ROWS: 9937205

 AVG_ROW_LENGTH: 121

    DATA_LENGTH: 1205846016

MAX_DATA_LENGTH: 0

   INDEX_LENGTH: 0

      DATA_FREE: 3145728

 AUTO_INCREMENT: NULL

    CREATE_TIME: 2021-11-09 02:29:40

    UPDATE_TIME: NULL

     CHECK_TIME: NULL

TABLE_COLLATION: utf8mb4_0900_ai_ci

       CHECKSUM: NULL

 CREATE_OPTIONS: row_format=COMPRESSED KEY_BLOCK_SIZE=16

  TABLE_COMMENT:

 

TABLE 통계 정보 설정

통계 정보를 영구적으로 저장할 것인지 Sample Page의 크기 등등 테이블 통계 관련 옵션

변경 시 InPlace 알고리즘을 사용하여 변경가능하고

Metadata만 수정하기 때문에 잠시간 Mete Lock 정도 처리가 가능하다.

ALTER TABLE alter_test_tbl2 STATS_PERSISTENT=0,

STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1,

ALGORITHM=INPLACE, LOCK=NONE;

 

Character Set 설정

이미 입력된 데이터들의 인코딩 형태를 변경되지 않는 설정 시 에는

InPlace 알고리즘을 이용하여 바로 적용이 되지만

다른 인코딩으로 변경 시에는 테이블 재구성이 일어날 수 있다.

ALTER TABLE alter_test_tbl2 CHARACTER SET=utf8mb4,

ALGORITHM=INPLACE, LOCK=NONE;

 

Character Set 변경

Character Set 변경 시에는 COPY 알고리즘만 가능하며 역시 SHARED Lock 으로만 작업이 가능하다.

ALTER TABLE alter_test_tbl2 CONVERT TO CHARACTER SET euckr,

ALGORITHM=COPY, LOCK=SHARED;

 

Table Optimizing

조각화된 Index를 재구성하여 속도를 높이는 작업을 진행하는 Optimizing 에서는

Page들에 조각화된 Index 자체를 재구성하는 것이기 때문에 테이블 재구성이 일어난다.

테이블에 FULLTEXT Index가 존재한다면 Copy 알고리즘만이 사용 가능하다.

InPlace 알고리즘을 차용하지만 알고리즘과 Lock을 명시적으로 선언할 수 없다.

OPTIMIZE TABLE alter_test_tbl2;

 

FORCE 사용한 Rebuilding

테이블 재구성 시 force 옵션을 이용하여 흩어진 데이터를 모으는 작업을 진행한다.

mysql 5.6.17 이 후 버전에서는 InPlace 알고리즘을 차용했지만

테이블에 FULLTEXT Index가 존재한다면 Copy 알고리즘만이 사용 가능하다.

ALTER TABLE alter_test_tbl2 FORCE, ALGORITHM=INPLACE, LOCK=NONE;

 

CREATE TABLE opening_lines (

 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

 opening_line TEXT(500),

 author VARCHAR(200),

 title VARCHAR(200)

) ENGINE=InnoDB;

 

CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);

 

ALTER TABLE opening_lines FORCE, ALGORITHM=INPLACE, LOCK=NONE;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.

Reason: InnoDB presently supports one FULLTEXT index creation at a time.

Try ALGORITHM=COPY.

 

ALTER TABLE opening_lines FORCE, ALGORITHM=COPY, LOCK=SHARED;

 

NULL Rebuilding

변경 사항 없이 테이블의 재구성하여 퍼포먼스를 올리는 작업을 진행할 때도 마찬가지로

mysql 5.6.17 이 후 버전에서는 InPlace 알고리즘을 차용했지만

테이블에 FULLTEXT Index가 존재한다면 Copy 알고리즘만이 사용 가능하다.

ALTER TABLE alter_test_tbl2 ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

 

ALTER TABLE opening_lines ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.

Reason: InnoDB presently supports one FULLTEXT index creation at a time.

Try ALGORITHM=COPY.

 

ALTER TABLE opening_lines ENGINE=InnoDB, ALGORITHM=COPY, LOCK=SHARED;

 

테이블명 변경

Table Operation 중 유일하게 Instant 알고리즘을 제공함으로써 Lock 없이 작업이 완료된다.

ALTER TABLE alter_test_tbl2 RENAME TO alter_test_tbl2, ALGORITHM=INSTANT;

 

참조

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-primary-key-operations