Online DDL
With the online DDL
- Prevent a table from becoming unavailable
- Adjust the balance between performance and concurrency by LOCK clause
- Less disk space usage and I/O overhead than table-copy
MySQL act as below as possible.
Choose the left option if possible rather than the right one.
- ALGORITHM = INPLACE | COPY
- LOCK = NONE | SHARED | EXCLUSIVE, DEFAULT
| TYPE | QUERY | DML |
| NONE | PERMIT | PERMIT |
| SHARED | PERMIT | BLOCK |
| EXCLUSIVE | BLOCK | BLOCK |
Do ALTER TABLE ... ALGORITHM = INPLACE, The statement halt immediately if it cannot use the in-place mechanism.
Online DDL and Metadata Locks
- Initialization - Take a shared upgradable metadata lock
- Execution - The statement is prepared and executed. Metadata lock is upgraded if necessary.
- Commit Table Definition - The metadata lock is upgraded to exclusive to evict the old table definition.
Online DDL planning
Clone the target table and populate it.
Run DDL and check wheter the "row affected" value is zero or not.
- Query OK, 0 rows affected (0.07 sec)
- Query OK, 0 rows affected (21.42 sec).
- Query OK, 1671168 rows affected (1 min 35.54 sec) <--- Copy-table occurred
Online DDL Space Requirements
Space for temporary log files
A temporary log file is used for recording concurrent DML.
innodb_sort_buffer_size ~ innodb_online_alter_log_max_size
If the temporary log file size exceed innodb_online_alter_log_max_size, DDL fails.
Bigger innodb_online_alter_log_maxsize extends the period of time when the table is locked to apply the temporary logged DML.
Space for temporary sort files
Rebuilds the table write temporary sort files to tmpdir. (in my.cnf)
( It may takes table + indexes size )
Space for an intermediate table file
May require space equal to the size of the original table.
Online DDL Operations
In Place, Rebuilds Table, and Permit Concurrent DML are yes mean that are expensive operations.
"In Place = No" mean that ALGORITHM = "COPY". And this operation blocks Concurrent DML.
Index Operations
| Operation | In Place | Rebuilds Table | Permit Concurrent DML | Only Modifies Metadata |
| Creating or adding a secondary index | Yes | No | Yes | No |
| Dropping an index | Yes | No | Yes | Yes |
| Renaming an index | Yes | No | Yes | Yes |
Primary Key Operations
| Operation | In Place | Rebuilds Table | Permits Concurrent DML | Only Modifies Metadata |
| 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
| Operation | In Place | Rebuilds Table | Permit Concurrent DML | Only Modifies Metadata |
| 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 ( Check doc ) | No | Yes | Yes |
| 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
| Operation | In Place | Rebuilds Table | Permit Concurrent DML | Only Modifies Metadata |
| Adding a foreign key constraint | Yes | No | Yes | Yes |
| Dropping a foreign key constraint | Yes | No | Yes | Yes |
Table Operations
| Operation | In Place | Rebuilds Table | Permit Concurrent DML | Only Modifies Metadata |
| 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 |
Partitioning Operations
| Partitioning Clause | In Place | Permits DML |
| PARTITION BY | No | No |
| ADD PARTITION | No | No |
| DROP PARTITION | No | No |
| DISCARD PARTITION | No | No |
| IMPORT PARTITION | No | No |
| TRUNCATE PARTITION | Yes | Yes |
| COALESCE PARTITION | No | No |
| REORGANIZE PARTITION | No | No |
| EXCHANGE PARTITION | Yes | Yes |
| ANALYZE PARTITION | Yes | Yes |
| CHECK PARTITION | Yes | Yes |
| OPTIMIZE PARTITION | No | No |
| REBUILD PARTITION | No | No |
| REPAIR PARTITION | Yes | Yes |
| REMOVE PARTITIONING | No | No |
