Menu:
innodb-online-ddl-limitations:: Online DDL Limitations
The online DDL feature provides support for in-place table alterations and concurrent DML. Benefits of this feature include:
Improved responsiveness and availability in busy production environments, where making a table unavailable for minutes or hours is not practical.
The ability to adjust the balance between performance and concurrency during DDL operations using the 'LOCK' clause. See *note innodb-online-ddl-locking-options::.
Less disk space usage and I/O overhead than the table-copy method.
Typically, you do not need to do anything special to enable online DDL. By default, MySQL performs the operation in place, as permitted, with as little locking as possible.
You can control aspects of a DDL operation using the 'ALGORITHM' and 'LOCK' clauses of the *note 'ALTER TABLE': alter-table. statement. These clauses are placed at the end of the statement, separated from the table and column specifications by commas. For example:
ALTER TABLE TBL_NAME ADD PRIMARY KEY (COLUMN), ALGORITHM=INPLACE, LOCK=NONE;The 'LOCK' clause is useful for fine-tuning the degree of concurrent access to the table. The 'ALGORITHM' clause is primarily intended for performance comparisons and as a fallback to the older table-copying behavior in case you encounter any issues. For example:
To avoid accidentally making the table unavailable for reads, writes, or both, specify a clause on the *note 'ALTER TABLE': alter-table. statement such as 'LOCK=NONE' (permit reads and writes) or 'LOCK=SHARED' (permit reads). The operation halts immediately if the requested level of concurrency is not available.
To compare performance between algorithms, run a statement with 'ALGORITHM=INPLACE' and 'ALGORITHM=COPY'. Alternatively, run a statement with the 'old_alter_table' configuration option disabled and enabled.
To avoid tying up the server with an *note 'ALTER TABLE': alter-table. operation that copies the table, include 'ALGORITHM=INPLACE'. The statement halts immediately if it cannot use the in-place mechanism.
File: manual.info.tmp, Node: innodb-online-ddl-operations, Next: innodb-online-ddl-performance, Prev: innodb-online-ddl, Up: innodb-online-ddl
Online support details, syntax examples, and usage notes for DDL operations are provided under the following topics in this section.
*note online-ddl-index-operations::
*note online-ddl-primary-key-operations::
*note online-ddl-column-operations::
*note online-ddl-generated-column-operations::
*note online-ddl-foreign-key-operations::
*note online-ddl-table-operations::
*note online-ddl-tablespace-operations::
*note online-ddl-partitioning::
Index Operations
The following table provides an overview of online DDL support for index operations. An asterisk indicates additional information, an exception, or a dependency. For details, see *note online-ddl-index-syntax-notes::.
Online DDL Support for Index Operations
Operation In Place Rebuilds Permits Only Table Concurrent Modifies DML 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
Adding a 'FULLTEXT' index
Yes* No* No No
Adding a 'SPATIAL' index
Yes No No No
Changing the index type
Yes No Yes Yes
Syntax and Usage Notes
Creating or adding a secondary index
CREATE INDEX NAME ON TABLE (COL_LIST);
ALTER TABLE TBL_NAME ADD INDEX NAME (COL_LIST);The table remains available for read and write operations while the index is being created. The *note 'CREATE INDEX': create-index. statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table.
Online DDL support for adding secondary indexes means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without secondary indexes, then adding secondary indexes after the data is loaded.
A newly created secondary index contains only the committed data in the table at the time the note 'CREATE INDEX': create-index. or note 'ALTER TABLE': alter-table. statement finishes executing. It does not contain any uncommitted values, old versions of values, or values marked for deletion but not yet removed from the old index.
If the server exits while creating a secondary index, upon recovery, MySQL drops any partially created indexes. You must re-run the note 'ALTER TABLE': alter-table. or note 'CREATE INDEX': create-index. statement.
Some factors affect the performance, space usage, and semantics of this operation. For details, see *note innodb-online-ddl-limitations::.
Dropping an index
DROP INDEX NAME ON TABLE;
ALTER TABLE TBL_NAME DROP INDEX NAME;The table remains available for read and write operations while the index is being dropped. The *note 'DROP INDEX': drop-index. statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table.
Renaming an index
ALTER TABLE TBL_NAME RENAME INDEX OLD_INDEX_NAME TO NEW_INDEX_NAME, ALGORITHM=INPLACE, LOCK=NONE;Adding a 'FULLTEXT' index
CREATE FULLTEXT INDEX NAME ON table(COLUMN);Adding the first 'FULLTEXT' index rebuilds the table if there is no user-defined 'FTS_DOC_ID' column. Additional 'FULLTEXT' indexes may be added without rebuilding the table.
Adding a 'SPATIAL' index
CREATE TABLE geom (g GEOMETRY NOT NULL);
ALTER TABLE geom ADD SPATIAL INDEX(g), ALGORITHM=INPLACE, LOCK=SHARED;Changing the index type ('USING {BTREE | HASH}')
ALTER TABLE TBL_NAME DROP INDEX i1, ADD INDEX i1(KEY_PART,...) USING BTREE, ALGORITHM=INPLACE;Primary Key Operations
The following table provides an overview of online DDL support for primary key operations. An asterisk indicates additional information, an exception, or a dependency. See *note online-ddl-primary-key-syntax-notes::.
Online DDL Support for Primary Key Operations
Operation In Place Rebuilds Permits Only Table Concurrent Modifies DML 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
Syntax and Usage Notes
Adding a primary key
ALTER TABLE TBL_NAME ADD PRIMARY KEY (COLUMN), ALGORITHM=INPLACE, LOCK=NONE;Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation. 'ALGORITHM=INPLACE' is not permitted under certain conditions if columns have to be converted to 'NOT NULL'.
Restructuring the clustered index always requires copying of table data. Thus, it is best to define the primary key when you create a table, rather than issuing 'ALTER TABLE ... ADD PRIMARY KEY' later.
When you create a 'UNIQUE' or 'PRIMARY KEY' index, MySQL must do some extra work. For 'UNIQUE' indexes, MySQL checks that the table contains no duplicate values for the key. For a 'PRIMARY KEY' index, MySQL also checks that none of the 'PRIMARY KEY' columns contains a 'NULL'.
When you add a primary key using the 'ALGORITHM=COPY' clause, MySQL converts 'NULL' values in the associated columns to default values: 0 for numbers, an empty string for character-based columns and BLOBs, and 0000-00-00 00:00:00 for 'DATETIME'. This is a non-standard behavior that Oracle recommends you not rely on. Adding a primary key using 'ALGORITHM=INPLACE' is only permitted when the 'SQL_MODE' setting includes the 'strict_trans_tables' or 'strict_all_tables' flags; when the 'SQL_MODE' setting is strict, 'ALGORITHM=INPLACE' is permitted, but the statement can still fail if the requested primary key columns contain 'NULL' values. The 'ALGORITHM=INPLACE' behavior is more standard-compliant.
If you create a table without a primary key, 'InnoDB' chooses one for you, which can be the first 'UNIQUE' key defined on 'NOT NULL' columns, or a system-generated key. To avoid uncertainty and the potential space requirement for an extra hidden column, specify the 'PRIMARY KEY' clause as part of the *note 'CREATE TABLE': create-table. statement.
MySQL creates a new clustered index by copying the existing data from the original table to a temporary table that has the desired index structure. Once the data is completely copied to the temporary table, the original table is renamed with a different temporary table name. The temporary table comprising the new clustered index is renamed with the name of the original table, and the original table is dropped from the database.
The online performance enhancements that apply to operations on secondary indexes do not apply to the primary key index. The rows of an InnoDB table are stored in a clustered index organized based on the primary key, forming what some database systems call an 'index-organized table'. Because the table structure is closely tied to the primary key, redefining the primary key still requires copying the data.
When an operation on the primary key uses 'ALGORITHM=INPLACE', even though the data is still copied, it is more efficient than using 'ALGORITHM=COPY' because:
* No undo logging or associated redo logging is required for
'ALGORITHM=INPLACE'. These operations add overhead to DDL
statements that use 'ALGORITHM=COPY'.
* The secondary index entries are pre-sorted, and so can be
loaded in order.
* The change buffer is not used, because there are no
random-access inserts into the secondary indexes.If the server exits while creating a new clustered index, no data is lost, but you must complete the recovery process using the temporary tables that exist during the process. Since it is rare to re-create a clustered index or re-define primary keys on large tables, or to encounter a system crash during this operation, this manual does not provide information on recovering from this scenario.
Dropping a primary key
ALTER TABLE TBL_NAME DROP PRIMARY KEY, ALGORITHM=COPY;Only 'ALGORITHM=COPY' supports dropping a primary key without adding a new one in the same 'ALTER TABLE' statement.
Dropping a primary key and adding another
ALTER TABLE TBL_NAME DROP PRIMARY KEY, ADD PRIMARY KEY (COLUMN), ALGORITHM=INPLACE, LOCK=NONE;Data is reorganized substantially, making it an expensive operation.
Column Operations
The following table provides an overview of online DDL support for column operations. An asterisk indicates additional information, an exception, or a dependency. For details, see *note online-ddl-column-syntax-notes::.
Online DDL Support for Column Operations
Operation In Place Rebuilds Permits Only Table Concurrent Modifies DML 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 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
Syntax and Usage Notes
Adding a column
ALTER TABLE TBL_NAME ADD COLUMN COLUMN_NAME COLUMN_DEFINITION, ALGORITHM=INPLACE, LOCK=NONE;Concurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation. At a minimum, 'ALGORITHM=INPLACE, LOCK=SHARED' is required.
Dropping a column
ALTER TABLE TBL_NAME DROP COLUMN COLUMN_NAME, ALGORITHM=INPLACE, LOCK=NONE;Data is reorganized substantially, making it an expensive operation.
Renaming a column
ALTER TABLE TBL CHANGE OLD_COL_NAME NEW_COL_NAME DATA_TYPE, ALGORITHM=INPLACE, LOCK=NONE;To permit concurrent DML, keep the same data type and only change the column name.
When you keep the same data type and '[NOT] NULL' attribute, only changing the column name, the operation can always be performed online.
You can also rename a column that is part of a foreign key constraint. The foreign key definition is automatically updated to use the new column name. Renaming a column participating in a foreign key only works with 'ALGORITHM=INPLACE'. If you use the 'ALGORITHM=COPY' clause, or some other condition causes the operation to use 'ALGORITHM=COPY', the 'ALTER TABLE' statement fails.
'ALGORITHM=INPLACE' is not supported for renaming a generated column.
Reordering columns
To reorder columns, use 'FIRST' or 'AFTER' in 'CHANGE' or 'MODIFY' operations.
ALTER TABLE TBL_NAME MODIFY COLUMN COL_NAME COLUMN_DEFINITION FIRST, ALGORITHM=INPLACE, LOCK=NONE;Data is reorganized substantially, making it an expensive operation.
Changing the column data type
ALTER TABLE TBL_NAME CHANGE c1 c1 BIGINT, ALGORITHM=COPY;Changing the column data type is only supported with 'ALGORITHM=COPY'.
Extending 'VARCHAR' column size
ALTER TABLE TBL_NAME CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;The number of length bytes required by a note 'VARCHAR': char. column must remain the same. For note 'VARCHAR': char. columns of 0 to 255 bytes in size, one length byte is required to encode the value. For note 'VARCHAR': char. columns of 256 bytes in size or more, two length bytes are required. As a result, in-place note 'ALTER TABLE': alter-table. only supports increasing note 'VARCHAR': char. column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place note 'ALTER TABLE': alter-table. does not support increasing the size of a note 'VARCHAR': char. column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy ('ALGORITHM=COPY'). For example, attempting to change note 'VARCHAR': char. column size for a single byte character set from VARCHAR(255) to VARCHAR(256) using in-place *note 'ALTER TABLE': alter-table. returns this error:
ALTER TABLE TBL_NAME ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change
column type INPLACE. Try ALGORITHM=COPY.Note:
The byte length of a 'VARCHAR' column is dependant on the byte length of the character set.
Decreasing note 'VARCHAR': char. size using in-place note 'ALTER TABLE': alter-table. is not supported. Decreasing *note 'VARCHAR': char. size requires a table copy ('ALGORITHM=COPY').
Setting a column default value
ALTER TABLE TBL_NAME ALTER COLUMN COL SET DEFAULT LITERAL, ALGORITHM=INPLACE, LOCK=NONE;Only modifies table metadata. Default column values are stored in the .frm file for the table, not the 'InnoDB' data dictionary.
Dropping a column default value
ALTER TABLE TBL ALTER COLUMN COL DROP DEFAULT, ALGORITHM=INPLACE, LOCK=NONE;Changing the auto-increment value
ALTER TABLE TABLE AUTO_INCREMENT=NEXT_VALUE, ALGORITHM=INPLACE, LOCK=NONE;Modifies a value stored in memory, not the data file.
In a distributed system using replication or sharding, you sometimes reset the auto-increment counter for a table to a specific value. The next row inserted into the table uses the specified value for its auto-increment column. You might also use this technique in a data warehousing environment where you periodically empty all the tables and reload them, and restart the auto-increment sequence from 1.
Making a column 'NULL'
ALTER TABLE tbl_name MODIFY COLUMN COLUMN_NAME DATA_TYPE NULL, ALGORITHM=INPLACE, LOCK=NONE;Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.
Making a column 'NOT NULL'
ALTER TABLE TBL_NAME MODIFY COLUMN COLUMN_NAME DATA_TYPE NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;Rebuilds the table in place. 'STRICT_ALL_TABLES' or 'STRICT_TRANS_TABLES' 'SQL_MODE' is required for the operation to succeed. The operation fails if the column contains NULL values. The server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. See *note alter-table::. Data is reorganized substantially, making it an expensive operation.
Modifying the definition of an 'ENUM' or 'SET' column
CREATE TABLE t1 (c1 ENUM('a', 'b', 'c'));
ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INPLACE, LOCK=NONE;Modifying the definition of an note 'ENUM': enum. or note 'SET': set. column by adding new enumeration or set members to the end of the list of valid member values may be performed in place, as long as the storage size of the data type does not change. For example, adding a member to a *note 'SET': set. column that has 8 members changes the required storage per value from 1 byte to 2 bytes; this requires a table copy. Adding members in the middle of the list causes renumbering of existing members, which requires a table copy.
Generated Column Operations
The following table provides an overview of online DDL support for generated column operations. For details, see *note online-ddl-generated-column-syntax-notes::.
Online DDL Support for Generated Column Operations
Operation In Place Rebuilds Permits Only Table Concurrent Modifies DML Metadata
Adding a 'STORED' column
No Yes No No
Modifying 'STORED' column order
No Yes No No
Dropping a 'STORED' column
Yes Yes Yes No
Adding a 'VIRTUAL' column
Yes No Yes Yes
Modifying 'VIRTUAL' column order
No Yes No No
Dropping a 'VIRTUAL' column
Yes No Yes Yes
Syntax and Usage Notes
Adding a 'STORED' column
ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) STORED), ALGORITHM=COPY;'ADD COLUMN' is not an in-place operation for stored columns (done without using a temporary table) because the expression must be evaluated by the server.
Modifying 'STORED' column order
ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED FIRST, ALGORITHM=COPY;Rebuilds the table in place.
Dropping a 'STORED' column
ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;Rebuilds the table in place.
Adding a 'VIRTUAL' column
ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL), ALGORITHM=INPLACE, LOCK=NONE;Adding a virtual column is an in-place operation for non-partitioned tables. However, adding a virtual column cannot be combined with other *note 'ALTER TABLE': alter-table. actions.
Adding a 'VIRTUAL' is not an in-place operation for partitioned tables.
Modifying 'VIRTUAL' column order
ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL FIRST, ALGORITHM=COPY;Dropping a 'VIRTUAL' column
ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;Dropping a 'VIRTUAL' column is an in-place operation for non-partitioned tables. However, dropping a virtual column cannot be combined with other *note 'ALTER TABLE': alter-table. actions.
Dropping a 'VIRTUAL' is not an in-place operation for partitioned tables.
Foreign Key Operations
The following table provides an overview of online DDL support for foreign key operations. An asterisk indicates additional information, an exception, or a dependency. For details, see *note online-ddl-foreign-key-syntax-notes::.
Online DDL Support for Foreign Key Operations
Operation In Place Rebuilds Permits Only Table Concurrent Modifies DML Metadata
Adding a foreign key constraint
Yes* No Yes Yes
Dropping a foreign key constraint
Yes No Yes Yes
Syntax and Usage Notes
Adding a foreign key constraint
The 'INPLACE' algorithm is supported when 'foreign_key_checks' is disabled. Otherwise, only the 'COPY' algorithm is supported.
ALTER TABLE TBL1 ADD CONSTRAINT FK_NAME FOREIGN KEY INDEX (COL1)
REFERENCES TBL2(COL2) REFERENTIAL_ACTIONS;Dropping a foreign key constraint
ALTER TABLE TBL DROP FOREIGN KEY FK_NAME;Dropping a foreign key can be performed online with the 'foreign_key_checks' option enabled or disabled.
If you do not know the names of the foreign key constraints on a particular table, issue the following statement and find the constraint name in the 'CONSTRAINT' clause for each foreign key:
SHOW CREATE TABLE TABLE\GOr, query the Information Schema *note 'TABLE_CONSTRAINTS': information-schema-table-constraints-table. table and use the 'CONSTRAINT_NAME' and 'CONSTRAINT_TYPE' columns to identify the foreign key names.
You can also drop a foreign key and its associated index in a single statement:
ALTER TABLE TABLE DROP FOREIGN KEY CONSTRAINT, DROP INDEX INDEX;Note:
If foreign keys are already present in the table being altered (that is, it is a child table containing a 'FOREIGN KEY ... REFERENCE' clause), additional restrictions apply to online DDL operations, even those not directly involving the foreign key columns:
An *note 'ALTER TABLE': alter-table. on the child table could wait for another transaction to commit, if a change to the parent table causes associated changes in the child table through an 'ON UPDATE' or 'ON DELETE' clause using the 'CASCADE' or 'SET NULL' parameters.
In the same way, if a table is the parent table in a foreign key relationship, even though it does not contain any 'FOREIGN KEY' clauses, it could wait for the note 'ALTER TABLE': alter-table. to complete if an note 'INSERT': insert, note 'UPDATE': update, or note 'DELETE': delete. statement causes an 'ON UPDATE' or 'ON DELETE' action in the child table.
Table Operations
The following table provides an overview of online DDL support for table operations. An asterisk indicates additional information, an exception, or a dependency. For details, see *note online-ddl-table-syntax-notes::.
Online DDL Support for Table Operations
Operation In Place Rebuilds Permits Only Table Concurrent Modifies DML 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* Yes 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
Syntax and Usage Notes
Changing the 'ROW_FORMAT'
ALTER TABLE TBL_NAME ROW_FORMAT = ROW_FORMAT, ALGORITHM=INPLACE, LOCK=NONE;Data is reorganized substantially, making it an expensive operation.
For additional information about the 'ROW_FORMAT' option, see *note create-table-options::.
Changing the 'KEY_BLOCK_SIZE'
ALTER TABLE TBL_NAME KEY_BLOCK_SIZE = VALUE, ALGORITHM=INPLACE, LOCK=NONE;Data is reorganized substantially, making it an expensive operation.
For additional information about the 'KEY_BLOCK_SIZE' option, see *note create-table-options::.
Setting persistent table statistics options
ALTER TABLE TBL_NAME STATS_PERSISTENT=0, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;Only modifies table metadata.
Persistent statistics include 'STATS_PERSISTENT', 'STATS_AUTO_RECALC', and 'STATS_SAMPLE_PAGES'. For more information, see *note innodb-persistent-stats::.
Specifying a character set
ALTER TABLE TBL_NAME CHARACTER SET = CHARSET_NAME, ALGORITHM=INPLACE, LOCK=NONE;Rebuilds the table if the new character encoding is different.
Converting a character set
ALTER TABLE TBL_NAME CONVERT TO CHARACTER SET CHARSET_NAME, ALGORITHM=COPY;Rebuilds the table if the new character encoding is different.
Optimizing a table
OPTIMIZE TABLE TBL_NAME;In-place operation is not supported for tables with 'FULLTEXT' indexes. The operation uses the 'INPLACE' algorithm, but 'ALGORITHM' and 'LOCK' syntax is not permitted.
Rebuilding a table with the 'FORCE' option
ALTER TABLE TBL_NAME FORCE, ALGORITHM=INPLACE, LOCK=NONE;Uses 'ALGORITHM=INPLACE' as of MySQL 5.6.17''. 'ALGORITHM=INPLACE' is not supported for tables with 'FULLTEXT' indexes.
Performing a "null" rebuild
ALTER TABLE TBL_NAME ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;Uses 'ALGORITHM=INPLACE' as of MySQL 5.6.17. 'ALGORITHM=INPLACE' is not supported for tables with 'FULLTEXT' indexes.
Renaming a table
ALTER TABLE OLD_TBL_NAME RENAME TO NEW_TBL_NAME, ALGORITHM=INPLACE, LOCK=NONE;MySQL renames files that correspond to the table TBL_NAME without making a copy. (You can also use the note 'RENAME TABLE': rename-table. statement to rename tables. See note rename-table::.) Privileges granted specifically for the renamed table are not migrated to the new name. They must be changed manually.
Tablespace Operations
The following table provides an overview of online DDL support for tablespace operations. For details, see *note online-ddl-tablespace-syntax-notes::.
Online DDL Support for Tablespace Operations
Operation In Place Rebuilds Permits Only Table Concurrent Modifies DML Metadata
Enabling or disabling file-per-table tablespace encryption
No Yes No No
Syntax and Usage Notes
Enabling or disabling file-per-table tablespace encryption
ALTER TABLE TBL_NAME ENCRYPTION='Y', ALGORITHM=COPY;Encryption is only supported for file-per-table tablespaces. For related information, see *note innodb-data-encryption::.
Partitioning Operations
With the exception of most *note 'ALTER TABLE': alter-table. partitioning clauses, online DDL operations for partitioned 'InnoDB' tables follow the same rules that apply to regular 'InnoDB' tables.
Most note 'ALTER TABLE': alter-table. partitioning clauses do not go through the same internal online DDL API as regular non-partitioned 'InnoDB' tables. As a result, online support for note 'ALTER TABLE': alter-table. partitioning clauses varies.
The following table shows the online status for each 'ALTER TABLE' partitioning statement. Regardless of the online DDL API that is used, MySQL attempts to minimize data copying and locking where possible.
note 'ALTER TABLE': alter-table. partitioning options that use 'ALGORITHM=COPY' or that only permit ''ALGORITHM=DEFAULT, LOCK=DEFAULT'', repartition the table using the 'COPY' algorithm. In other words, a new partitioned table is created with the new partitioning scheme. The newly created table includes any changes applied by the note 'ALTER TABLE': alter-table. statement, and table data is copied into the new table structure.
Online DDL Support for Partitioning Operations
Partitioning In Place Permits Notes Clause DML
*note 'PARTITION BY': alter-table.
No No Permits 'ALGORITHM=COPY', 'LOCK={DEFAULT|SHARED|EXCLUSIVE}'
*note 'ADD PARTITION': alter-table.
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.
*note 'DROP PARTITION': alter-table.
No No Only permits 'ALGORITHM=DEFAULT', 'LOCK=DEFAULT'. Does not copy existing data for tables partitioned by 'RANGE' or 'LIST'.
*note 'DISCARD PARTITION': alter-table.
No No Only permits 'ALGORITHM=DEFAULT', 'LOCK=DEFAULT'
*note 'IMPORT PARTITION': alter-table.
No No Only permits 'ALGORITHM=DEFAULT', 'LOCK=DEFAULT'
*note 'TRUNCATE PARTITION': alter-table.
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.
*note 'COALESCE PARTITION': alter-table.
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.
*note 'REORGANIZE PARTITION': alter-table.
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.
*note 'EXCHANGE PARTITION': alter-table.
Yes Yes
*note 'ANALYZE PARTITION': alter-table.
Yes Yes
*note 'CHECK PARTITION': alter-table.
Yes Yes
*note 'OPTIMIZE PARTITION': alter-table.
No No 'ALGORITHM' and 'LOCK' clauses are ignored. Rebuilds the entire table. See *note partitioning-maintenance::.
*note 'REBUILD PARTITION': alter-table.
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.
*note 'REPAIR PARTITION': alter-table.
Yes Yes
*note 'REMOVE PARTITIONING': alter-table.
No No Permits 'ALGORITHM=COPY', 'LOCK={DEFAULT|SHARED|EXCLUSIVE}'
Non-partitioning online note 'ALTER TABLE': alter-table. operations on partitioned tables follow the same rules that apply to regular tables. However, note 'ALTER TABLE': alter-table. performs online operations on each table partition, which causes increased demand on system resources due to operations being performed on multiple partitions.
For additional information about note 'ALTER TABLE': alter-table. partitioning clauses, see note alter-table-partition-options::, and note alter-table-partition-operations::. For information about partitioning in general, see note partitioning::.
File: manual.info.tmp, Node: innodb-online-ddl-performance, Next: innodb-online-ddl-space-requirements, Prev: innodb-online-ddl-operations, Up: innodb-online-ddl
Online DDL improves several aspects of MySQL operation:
Applications that access the table are more responsive because queries and DML operations on the table can proceed while the DDL operation is in progress. Reduced locking and waiting for MySQL server resources leads to greater scalability, even for operations that are not involved in the DDL operation.
In-place operations avoid the disk I/O and CPU cycles associated with the table-copy method, which minimizes overall load on the database. Minimizing load helps maintain good performance and high throughput during the DDL operation.
In-place operations read less data into the buffer pool than the table-copy operations, which reduces purging of frequently accessed data from memory. Purging of frequently accessed data can cause a temporary performance dip after a DDL operation.
The LOCK clause
By default, MySQL uses as little locking as possible during a DDL operation. The 'LOCK' clause can be specified to enforce more restrictive locking, if required. If the 'LOCK' clause specifies a less restrictive level of locking than is permitted for a particular DDL operation, the statement fails with an error. 'LOCK' clauses are described below, in order of least to most restrictive:
'LOCK=NONE':
Permits concurrent queries and DML.
For example, use this clause for tables involving customer signups or purchases, to avoid making the tables unavailable during lengthy DDL operations.
'LOCK=SHARED':
Permits concurrent queries but blocks DML.
For example, use this clause on data warehouse tables, where you can delay data load operations until the DDL operation is finished, but queries cannot be delayed for long periods.
'LOCK=DEFAULT':
Permits as much concurrency as possible (concurrent queries, DML, or both). Omitting the 'LOCK' clause is the same as specifying 'LOCK=DEFAULT'.
Use this clause when you know that the default locking level of the DDL statement does not cause availability problems for the table.
'LOCK=EXCLUSIVE':
Blocks concurrent queries and DML.
Use this clause if the primary concern is finishing the DDL operation in the shortest amount of time possible, and concurrent query and DML access is not necessary. You might also use this clause if the server is supposed to be idle, to avoid unexpected table accesses.
Online DDL and Metadata Locks
Online DDL operations can be viewed as having three phases:
Phase 1: Initialization
In the initialization phase, the server determines how much concurrency is permitted during the operation, taking into account storage engine capabilities, operations specified in the statement, and user-specified 'ALGORITHM' and 'LOCK' options. During this phase, a shared upgradeable metadata lock is taken to protect the current table definition.
Phase 2: Execution
In this phase, the statement is prepared and executed. Whether the metadata lock is upgraded to exclusive depends on the factors assessed in the initialization phase. If an exclusive metadata lock is required, it is only taken briefly during statement preparation.
Phase 3: Commit Table Definition
In the commit table definition phase, the metadata lock is upgraded to exclusive to evict the old table definition and commit the new one. Once granted, the duration of the exclusive metadata lock is brief.
Due to the exclusive metadata lock requirements outlined above, an online DDL operation may have to wait for concurrent transactions that hold metadata locks on the table to commit or rollback. Transactions started before or during the DDL operation can hold metadata locks on the table being altered. In the case of a long running or inactive transaction, an online DDL operation can time out waiting for an exclusive metadata lock. Additionally, a pending exclusive metadata lock requested by an online DDL operation blocks subsequent transactions on the table.
The following example demonstrates an online DDL operation waiting for an exclusive metadata lock, and how a pending metadata lock blocks subsequent transactions on the table.
Session 1:
mysql> CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
mysql> START TRANSACTION;
mysql> SELECT * FROM t1;The session 1 *note 'SELECT': select. statement takes a shared metadata lock on table t1.
Session 2:
mysql> ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE;The online DDL operation in session 2, which requires an exclusive metadata lock on table t1 to commit table definition changes, must wait for the session 1 transaction to commit or roll back.
Session 3:
mysql> SELECT * FROM t1;The note 'SELECT': select. statement issued in session 3 is blocked waiting for the exclusive metadata lock requested by the note 'ALTER TABLE': alter-table. operation in session 2 to be granted.
You can use *note 'SHOW FULL PROCESSLIST': show-processlist. to determine if transactions are waiting for a metadata lock.
mysql> SHOW FULL PROCESSLIST\G
...
*************************** 2. row ***************************
Id: 5
User: root
Host: localhost
db: test
Command: Query
Time: 44
State: Waiting for table metadata lock
Info: ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE
...
*************************** 4. row ***************************
Id: 7
User: root
Host: localhost
db: test
Command: Query
Time: 5
State: Waiting for table metadata lock
Info: SELECT * FROM t1
4 rows in set (0.00 sec)Metadata lock information is also exposed through the Performance Schema note 'metadata_locks': performance-schema-metadata-locks-table. table, which provides information about metadata lock dependencies between sessions, the metadata lock a session is waiting for, and the session that currently holds the metadata lock. For more information, see note performance-schema-metadata-locks-table::.
Online DDL Performance
The performance of a DDL operation is largely determined by whether the operation is performed in place and whether it rebuilds the table.
To assess the relative performance of a DDL operation, you can compare results using 'ALGORITHM=INPLACE' with results using 'ALGORITHM=COPY'. Alternatively, you can compare results with 'old_alter_table' disabled and enabled.
For DDL operations that modify table data, you can determine whether a DDL operation performs changes in place or performs a table copy by looking at the 'rows affected' value displayed after the command finishes. For example:
Changing the default value of a column (fast, does not affect the table data):
Query OK, 0 rows affected (0.07 sec)Adding an index (takes time, but '0 rows affected' shows that the table is not copied):
Query OK, 0 rows affected (21.42 sec)Changing the data type of a column (takes substantial time and requires rebuilding all the rows of the table):
Query OK, 1671168 rows affected (1 min 35.54 sec)Before running a DDL operation on a large table, check whether the operation is fast or slow as follows:
Clone the table structure.
Populate the cloned table with a small amount of data.
Run the DDL operation on the cloned table.
Check whether the 'rows affected' value is zero or not. A nonzero value means the operation copies table data, which might require special planning. For example, you might do the DDL operation during a period of scheduled downtime, or on each replica server one at a time.
Note:
For a greater understanding of the MySQL processing associated with a DDL operation, examine Performance Schema and 'INFORMATION_SCHEMA' tables related to 'InnoDB' before and after DDL operations to see the number of physical reads, writes, memory allocations, and so on.
Performance Schema stage events can be used to monitor note 'ALTER TABLE': alter-table. progress. See note monitor-alter-table-performance-schema::.
Because there is some processing work involved with recording the changes made by concurrent DML operations, then applying those changes at the end, an online DDL operation could take longer overall than the table-copy mechanism that blocks table access from other sessions. The reduction in raw performance is balanced against better responsiveness for applications that use the table. When evaluating the techniques for changing table structure, consider end-user perception of performance, based on factors such as load times for web pages.
File: manual.info.tmp, Node: innodb-online-ddl-space-requirements, Next: innodb-online-ddl-single-multi, Prev: innodb-online-ddl-performance, Up: innodb-online-ddl
Online DDL operations have the following space requirements:
Temporary log files:
A temporary log file records concurrent DML when an online DDL operation creates an index or alters a table. The temporary log file is extended as required by the value of 'innodb_sort_buffer_size' up to a maximum specified by 'innodb_online_alter_log_max_size'. If the operation takes a long time and concurrent DML modifies the table so much that the size of the temporary log file exceeds the value of 'innodb_online_alter_log_max_size', the online DDL operation fails with a 'DB_ONLINE_LOG_TOO_BIG' error and uncommitted concurrent DML operations are rolled back. A large 'innodb_online_alter_log_max_size' setting permits more DML during an online DDL operation, but it also extends the period of time at the end of the DDL operation when the table is locked to apply logged DML.
The 'innodb_sort_buffer_size' variable also defines the size of the temporary log file read buffer and write buffer.
Temporary sort files:
Online DDL operations that rebuild the table write temporary sort files to the MySQL temporary directory ('$TMPDIR' on Unix, '%TEMP%' on Windows, or the directory specified by '--tmpdir') during index creation. Temporary sort files are not created in the directory that contains the original table. Each temporary sort file is large enough to hold one column of data, and each sort file is removed when its data is merged into the final table or index. Operations involving temporary sort files may require temporary space equal to the amount of data in the table plus indexes. An error is reported if online DDL operation uses all of the available disk space on the file system where the data directory resides.
If the MySQL temporary directory is not large enough to hold the sort files, set 'tmpdir' to a different directory. Alternatively, define a separate temporary directory for online DDL operations using 'innodb_tmpdir'. This option was introduced in MySQL 5.7.11 to help avoid temporary directory overflows that could occur as a result of large temporary sort files.
Intermediate table files:
Some online DDL operations that rebuild the table create a temporary intermediate table file in the same directory as the original table. An intermediate table file may require space equal to the size of the original table. Intermediate table file names begin with '#sql-ib' prefix and only appear briefly during the online DDL operation.
The 'innodb_tmpdir' option is not applicable to intermediate table files.
File: manual.info.tmp, Node: innodb-online-ddl-single-multi, Next: innodb-online-ddl-failure-conditions, Prev: innodb-online-ddl-space-requirements, Up: innodb-online-ddl
Before the introduction of online DDL, it was common practice to combine many DDL operations into a single note 'ALTER TABLE': alter-table. statement. Because each note 'ALTER TABLE': alter-table. statement involved copying and rebuilding the table, it was more efficient to make several changes to the same table at once, since those changes could all be done with a single rebuild operation for the table. The downside was that SQL code involving DDL operations was harder to maintain and to reuse in different scripts. If the specific changes were different each time, you might have to construct a new complex *note 'ALTER TABLE': alter-table. for each slightly different scenario.
For DDL operations that can be done in place, you can separate them into individual *note 'ALTER TABLE': alter-table. statements for easier scripting and maintenance, without sacrificing efficiency. For example, you might take a complicated statement such as:
ALTER TABLE t1 ADD INDEX i1(c1), ADD UNIQUE INDEX i2(c2),
CHANGE c4_old_name c4_new_name INTEGER UNSIGNED;and break it down into simpler parts that can be tested and performed independently, such as:
ALTER TABLE t1 ADD INDEX i1(c1);
ALTER TABLE t1 ADD UNIQUE INDEX i2(c2);
ALTER TABLE t1 CHANGE c4_old_name c4_new_name INTEGER UNSIGNED NOT NULL;You might still use multi-part *note 'ALTER TABLE': alter-table. statements for:
Operations that must be performed in a specific sequence, such as creating an index followed by a foreign key constraint that uses that index.
Operations all using the same specific 'LOCK' clause, that you want to either succeed or fail as a group.
Operations that cannot be performed in place, that is, that still use the table-copy method.
Operations for which you specify 'ALGORITHM=COPY' or 'old_alter_table=1', to force the table-copying behavior if needed for precise backward-compatibility in specialized scenarios.
File: manual.info.tmp, Node: innodb-online-ddl-failure-conditions, Next: innodb-online-ddl-limitations, Prev: innodb-online-ddl-single-multi, Up: innodb-online-ddl
The failure of an online DDL operation is typically due to one of the following conditions:
An 'ALGORITHM' clause specifies an algorithm that is not compatible with the particular type of DDL operation or storage engine.
A 'LOCK' clause specifies a low degree of locking ('SHARED' or 'NONE') that is not compatible with the particular type of DDL operation.
A timeout occurs while waiting for an exclusive lock on the table, which may be needed briefly during the initial and final phases of the DDL operation.
The 'tmpdir' or 'innodb_tmpdir' file system runs out of disk space, while MySQL writes temporary sort files on disk during index creation. For more information, see *note innodb-online-ddl-space-requirements::.
The operation takes a long time and concurrent DML modifies the table so much that the size of the temporary online log exceeds the value of the 'innodb_online_alter_log_max_size' configuration option. This condition causes a 'DB_ONLINE_LOG_TOO_BIG' error.
Concurrent DML makes changes to the table that are allowed with the original table definition, but not with the new one. The operation only fails at the very end, when MySQL tries to apply all the changes from concurrent DML statements. For example, you might insert duplicate values into a column while a unique index is being created, or you might insert 'NULL' values into a column while creating a primary key index on that column. The changes made by the concurrent DML take precedence, and the *note 'ALTER TABLE': alter-table. operation is effectively rolled back.
File: manual.info.tmp, Node: innodb-online-ddl-limitations, Prev: innodb-online-ddl-failure-conditions, Up: innodb-online-ddl
The following limitations apply to online DDL operations:
The table is copied when creating an index on a 'TEMPORARY TABLE'.
The *note 'ALTER TABLE': alter-table. clause 'LOCK=NONE' is not permitted if there are 'ON...CASCADE' or 'ON...SET NULL' constraints on the table.
Before an online DDL operation can finish, it must wait for transactions that hold metadata locks on the table to commit or roll back. An online DDL operation may briefly require an exclusive metadata lock on the table during its execution phase, and always requires one in the final phase of the operation when updating the table definition. Consequently, transactions holding metadata locks on the table can cause an online DDL operation to block. The transactions that hold metadata locks on the table may have been started before or during the online DDL operation. A long running or inactive transaction that holds a metadata lock on the table can cause an online DDL operation to timeout.
An online DDL operation on a table in a foreign key relationship does not wait for a transaction executing on the other table in the foreign key relationship to commit or rollback. The transaction holds an exclusive metadata lock on the table it is updating and shared metadata lock on the foreign-key-related table (required for foreign key checking). The shared metadata lock permits the online DDL operation to proceed but blocks the operation in its final phase, when an exclusive metadata lock is required to update the table definition. This scenario can result in deadlocks as other transactions wait for the online DDL operation to finish.
When running an online DDL operation, the thread that runs the *note 'ALTER TABLE': alter-table. statement applies an online log of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error ('ERROR 1062 (23000): Duplicate entry'), even if the duplicate entry is only temporary and would be reverted by a later entry in the online log. This is similar to the idea of a foreign key constraint check in 'InnoDB' in which constraints must hold during a transaction.
note 'OPTIMIZE TABLE': optimize-table. for an 'InnoDB' table is mapped to an note 'ALTER TABLE': alter-table. operation to rebuild the table and update index statistics and free unused space in the clustered index. Secondary indexes are not created as efficiently because keys are inserted in the order they appeared in the primary key. *note 'OPTIMIZE TABLE': optimize-table. is supported with the addition of online DDL support for rebuilding regular and partitioned 'InnoDB' tables.
Tables created before MySQL 5.6 that include temporal columns (note 'DATE': datetime, note 'DATETIME': datetime. or note 'TIMESTAMP': datetime.) and have not been rebuilt using ' ALGORITHM=COPY' do not support 'ALGORITHM=INPLACE'. In this case, an note 'ALTER TABLE ... ALGORITHM=INPLACE': alter-table. operation returns the following error:
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.The following limitations are generally applicable to online DDL operations on large tables that involve rebuilding the table:
* There is no mechanism to pause an online DDL operation or to
throttle I/O or CPU usage for an online DDL operation.
* Rollback of an online DDL operation can be expensive should
the operation fail.
* Long running online DDL operations can cause replication lag.
An online DDL operation must finish running on the source
before it is run on the replica. Also, DML that was processed
concurrently on the source is only processed on the replica
after the DDL operation on the replica is completed.For additional information related to running online DDL operations on large tables, see *note innodb-online-ddl-performance::.
File: manual.info.tmp, Node: innodb-data-encryption, Next: innodb-parameters, Prev: innodb-online-ddl, Up: innodb-storage-engine