MySQL online DDL

1. online DDL algorithms

1) COPY : 템프 테이블에 변경 작업을 하고 replace

  Algorithm COPY alters the schema of the existing table by creating a new temporary table with the altered schema

   - blocks concurrent DML’s (inserts,updates,deletes) operations

     but it allows concurrent read queries(SELECT’S) when LOCK=SHARED

   - no mechanism to pause ddl or i/o or cpu usage

   - expensive process when it happen rollback of operation.

   - causes replication lag

 

2) INPLACE : 원본 테이블에 변경 작업이 발생

  INPLACE algorithm performs operations in-place to the original table and avoids the table copy and rebuild, whenever possible.

- long-running online DDL operations and replication lag in slaves 

- use larger innodb_online_alter_log_max_size size (변경량이 많아지고 해당값을 넘어가면 에러발생)

- high IO usage for multi concurrency servers with large table.

 

 

3) INSTANT ( from 8.0 versions) : 메타 정보만 수정하여 변경 사항 반영

INSTANT algorithm performs only metadata changes in the data dictionary. It doesn’t acquire any metadata lock during schema changes and as it doesn’t touch the data file of the table

 

1) list of the operations which is possible to instant algorithm

  - adding a column

  - setting a column with default value

  - dropping a column default value

  - modifying the enum column

  - adding or dropping a virtual column

  - renaming a table

  * I_S.innodb_table and I_S.innodb_columns : view table which it can query the info of instant table.

 

2) disadvanage

  - A column can only be added as the last column of the table ( 마지막에만 컬럼 추가 가능)

  - Columns cannot be added to tables that use  ROW_FORMAT=COMPRESSED

  - Columns cannot be added to tables that include a FULLTEXT index

  - Columns cannot be added to temporary tables

  - Columns cannot be added to tables that reside in the data dictionary tablespace

 

 

2. MySQL 5.7 ( https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html )

 

1) index

  ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name, ALGORITHM=INPLACE, LOCK=NONE;

  ALTER TABLE tbl_name DROP INDEX i1, ADD INDEX i1(key_part,...) USING BTREE, ALGORITHM=INPLACE;

 

2) Primary key

  ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

 

3) Table Column

 ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INPLACE, LOCK=NONE;

 ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

 (  when it change the column size like the following,  0 <= column size <= 255 ,  column size > 255 )

 

4) Table

 ALTER TABLE tbl_name STATS_PERSISTENT=0, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;

 

 

 

3. MySQL 8.0 ( https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html )

 

 ALTER TABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INSTANT;

 

For table operations, it only use the instant algorithm regarding to rename table.

instant는 metadata 변경만 가능한 작업에만 가능하고, 그 이외에는 table rebuild하는 작업이 거의 발생함

 

 

 

4. mariadb 

1) COPY : MariaDB 10.0 and later

2) INPLACE : MariaDB 10.0 and later

3) NOCOPY : MariaDB 10.3 and later

  it can be performed without rebuilding the clustered index.

4) INSTANT : MariaDB 10.3 and later

 

When you use the session level for algorithm, 

 

SET SESSION alter_algorithm='INPLACE';