InnoDB Online DDL Overview
MariaDB starting with 10.0
InnoDB tables support online DDL, which permits concurrent DML and uses optimizations to avoid unnecessary table copying.
Contents
In MariaDB 10.0 and later, the ALTER TABLE
statement supports two clauses that are used to implement online DDL:
ALGORITHM
- This clause controls how the DDL operation is performed.LOCK
- This clause controls how much concurrency is allowed while the DDL operation is being performed.
ALGORITHM
MariaDB starting with 10.0
In MariaDB 10.0 and later, the ALTER TABLE
statement supports the ALGORITHM
clause.
In MariaDB 10.0 and later, InnoDB supports multiple algorithms for performing DDL operations. This offers a significant performance improvement over previous versions. The supported algorithms are:
DEFAULT
- This implies the default behavior for the specific statement, such as if noALGORITHM
clause is specified.COPY
INPLACE
NOCOPY
- This was added in MariaDB 10.3.7.INSTANT
- This was added in MariaDB 10.3.7.
In MariaDB 10.0 and later, the ALGORITHM
clause for the ALTER TABLE
statement can also be used to pick the least efficient algorithm that the user is willing to accept.
In MariaDB 10.3 and later, the alter_algorithm
system variable can also be used to pick the least efficient algorithm that the user is willing to accept.
In MariaDB 10.2 and before, the old_alter_table
system variable can be used to pick the COPY
algorithm.
The algorithms are described in more details below.
DEFAULT
Algorithm
The default behavior, which occurs if ALGORITHM=DEFAULT
is specified, or if ALGORITHM
is not specified at all, usually only makes a copy if the operation doesn't support being done in-place at all. In this case, the most efficient available algorithm will usually be used.
This means that, if an operation supports the INSTANT
algorithm, then it will use that algorithm by default. If an operation does not support the INSTANT
algorithm, but it does support the NOCOPY
algorithm, then it will use that algorithm by default. If an operation does not support the NOCOPY
algorithm, but it does support the INPLACE
algorithm, then it will use that algorithm by default.
COPY
Algorithm
MariaDB starting with 10.0
In MariaDB 10.0 and later, the COPY
algorithm refers to the original ALTER TABLE
algorithm.
When the COPY
algorithm is used, MariaDB essentially does the following operations:
-- Create a temporary table with the new definition CREATE TEMPORARY TABLE tmp_tab ( ... ); -- Copy the data from the original table INSERT INTO tmp_tab SELECT * FROM original_tab; -- Drop the original table DROP TABLE original_tab; -- Rename the temporary table, so that it replaces the original one RENAME TABLE tmp_tab TO original_tab;
This algorithm is very inefficient, but it is generic, so it works for all storage engines.
If the COPY
algorithm is specified with the ALGORITHM
clause or with the alter_algorithm
system variable, then the COPY
algorithm will be used even if it is not necessary. This can result in a lengthy table copy. If multiple ALTER TABLE
operations are required that each require the table to be rebuilt, then it is best to specify all operations in a single ALTER TABLE
statement, so that the table is only rebuilt once.
Using the COPY
Algorithm with InnoDB
If the COPY
algorithm is used with an InnoDB table, then the following statements apply:
- The table will be rebuilt using the current values of the
innodb_file_per_table
,innodb_file_format
, andinnodb_default_row_format
system variables.
- The operation will have to create a temporary table to perform the the table copy. This temporary table will be in the same directory as the original table, and it's file name will be in the format
, where#
sql${PID}_${THREAD_ID}_${TMP_TABLE_COUNT}${PID}
is the process ID ofmysqld
,${THREAD_ID}
is the connection ID, and${TMP_TABLE_COUNT}
is the number of temporary tables that the connection has open. Therefore, thedatadir
may contain files with file names like
.#
sql1234_12_1.ibd
- The operation inserts one record at a time into each index, which is very inefficient.
- InnoDB does not use a sort buffer.
- In MariaDB 10.2.13, MariaDB 10.3.5 and later, the table copy operation creates a lot fewer InnoDB undo log writes. See MDEV-11415 for more information.
- The table copy operation creates a lot of InnoDB redo log writes.
INPLACE
Algorithm
MariaDB starting with 10.0
In MariaDB 10.0 and later, the INPLACE
algorithm is supported.
The COPY
algorithm can be incredibly slow, because the whole table has to be copied and rebuilt. The INPLACE
algorithm was introduced as a way to avoid this by performing operations in-place and avoiding the table copy and rebuild, when possible.
When the INPLACE
algorithm is used, the underlying storage engine uses optimizations to perform the operation while avoiding the table copy and rebuild. However, INPLACE
is a bit of a misnomer, since some operations may still require the table to be rebuilt for some storage engines. Regardless, several operations can be performed without a full copy of the table for some storage engines.
A more accurate name for the algorithm would have been the ENGINE
algorithm, since the storage engine decides how to implement the algorithm.
If an ALTER TABLE
operation supports the INPLACE
algorithm, then it can be performed using optimizations by the underlying storage engine, but it may rebuilt.
If the INPLACE
algorithm is specified with the ALGORITHM
clause or with the alter_algorithm
system variable and if the ALTER TABLE
operation does not support the INPLACE
algorithm, then an error will be raised. For example:
SET SESSION alter_algorithm='INPLACE'; ALTER TABLE tab MODIFY COLUMN c int; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY
In this case, raising an error is preferable, if the alternative is for the operation to make a copy of the table, and perform unexpectedly slowly.
Using the INPLACE
Algorithm with InnoDB
If the INPLACE
algorithm is used with an InnoDB table, then the following statements apply:
- The operation might have to write sort files in the directory defined by the
innodb_tmpdir
system variable.
- The operation might also have to write a temporary log file to track data changes by DML queries executed during the operation. The maximum size for this log file is configured by the
innodb_online_alter_log_max_size
system variable.
- Some operations require the table to be rebuilt, even though the algorithm is inaccurately called "in-place". This includes operations such as adding or dropping columns, adding a primary key, changing a column to
NULL
, etc.
- If the operation requires the table to be rebuilt, then the operation might have to create temporary tables.
- It may have to create a temporary intermediate table for the actual table rebuild operation.
- In MariaDB 10.2.19 and later, this temporary table will be in the same directory as the original table, and it's file name will be in the format
, where#
sql${PID}_${THREAD_ID}_${TMP_TABLE_COUNT}${PID}
is the process ID ofmysqld
,${THREAD_ID}
is the connection ID, and${TMP_TABLE_COUNT}
is the number of temporary tables that the connection has open. Therefore, thedatadir
may contain files with file names like
.#
sql1234_12_1.ibd - In MariaDB 10.2.18 and before, this temporary table will be in the same directory as the original table, and it's file name will be in the format
, where#
sql-ib${TABLESPACE_ID}-${RAND}${TABLESPACE_ID}
is the table's tablespace ID within InnoDB and${RAND}
is a randomly initialized number. Therefore, thedatadir
may contain files with file names like
.#
sql-ib230291-1363966925.ibd
- In MariaDB 10.2.19 and later, this temporary table will be in the same directory as the original table, and it's file name will be in the format
- When it replaces the original table with the rebuilt table, it may also have to rename the original table using a temporary table name.
- If the server is MariaDB 10.3 or later or if it is running MariaDB 10.2 and the
innodb_safe_truncate
system variable is set toOFF
, then the format will actually be
, where#
sql-ib${TABLESPACE_ID}-${RAND}${TABLESPACE_ID}
is the table's tablespace ID within InnoDB and${RAND}
is a randomly initialized number. Therefore, thedatadir
may contain files with file names like
.#
sql-ib230291-1363966925.ibd - If the server is running MariaDB 10.1 or before or if it is running MariaDB 10.2 and the
innodb_safe_truncate
system variable is set toON
, then the renamed table will have a temporary table name in the format
, where#
sql-ib${TABLESPACE_ID}${TABLESPACE_ID}
is the table's tablespace ID within InnoDB. Therefore, thedatadir
may contain files with file names like
.#
sql-ib230291.ibd
- If the server is MariaDB 10.3 or later or if it is running MariaDB 10.2 and the
- It may have to create a temporary intermediate table for the actual table rebuild operation.
- The storage needed for the above items can add up to the size of the original table, or more in some cases.
- Some operations are instantaneous, if they only require the table's metadata to be changed. This includes operations such as renaming a column, changing a column's
DEFAULT
value, etc.
Operations Supported by InnoDB with the INPLACE
Algorithm
With respect to the allowed operations, the INPLACE
algorithm supports a subset of the operations supported by the COPY
algorithm, and it supports a superset of the operations supported by the NOCOPY
algorithm.
See InnoDB Online DDL Operations with ALGORITHM=INPLACE for more information.
NOCOPY
Algorithm
MariaDB starting with 10.3
In MariaDB 10.3 and later, the NOCOPY
algorithm is supported.
The INPLACE
algorithm can sometimes be surprisingly slow in instances where it has to rebuild the clustered index, because when the clustered index has to be rebuilt, the whole table has to be rebuilt. The NOCOPY
algorithm was introduced as a way to avoid this.
If an ALTER TABLE
operation supports the NOCOPY
algorithm, then it can be performed without rebuilding the clustered index.
If the NOCOPY
algorithm is specified with the ALGORITHM
clause or with the alter_algorithm
system variable and if the ALTER TABLE
operation does not support the NOCOPY
algorithm, then an error will be raised. For example:
SET SESSION alter_algorithm='NOCOPY'; ALTER TABLE tab MODIFY COLUMN c int; ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY
In this case, raising an error is preferable, if the alternative is for the operation to rebuild the clustered index, and perform unexpectedly slowly.
Operations Supported by InnoDB with the NOCOPY
Algorithm
With respect to the allowed operations, the NOCOPY
algorithm supports a subset of the operations supported by the INPLACE
algorithm, and it supports a superset of the operations supported by the INSTANT
algorithm.
See InnoDB Online DDL Operations with ALGORITHM=NOCOPY for more information.
INSTANT
Algorithm
MariaDB starting with 10.3
In MariaDB 10.3 and later, the INSTANT
algorithm is supported.
The INPLACE
algorithm can sometimes be surprisingly slow in instances where it has to modify data files. The INSTANT
algorithm was introduced as a way to avoid this.
If an ALTER TABLE
operation supports the INSTANT
algorithm, then it can be performed without modifying any data files.
If the INSTANT
algorithm is specified with the ALGORITHM
clause or with the alter_algorithm
system variable and if the ALTER TABLE
operation does not support the INSTANT
algorithm, then an error will be raised. For example:
SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab MODIFY COLUMN c int; ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY
In this case, raising an error is preferable, if the alternative is for the operation to modify data files, and perform unexpectedly slowly.
Operations Supported by InnoDB with the INSTANT
Algorithm
With respect to the allowed operations, the INSTANT
algorithm supports a subset of the operations supported by the NOCOPY
algorithm.
See InnoDB Online DDL Operations with ALGORITHM=INSTANT for more information.
LOCK
MariaDB starting with 10.0
In MariaDB 10.0 and later, the ALTER TABLE
statement supports the LOCK
clause.
In MariaDB 10.0 and later, the ALTER TABLE
statement supports the LOCK
clause. This clause is one of the clauses that is used to implement online DDL. ALTER TABLE
supports several different locking strategies. A locking strategy can be explicitly chosen for an ALTER TABLE
operation by setting the LOCK
clause. The supported values are:
DEFAULT
: Acquire the least restrictive lock on the table that is supported for the specific operation. Permit the maximum amount of concurrency that is supported for the specific operation.NONE
: Acquire no lock on the table. Permit all concurrent DML. If this locking strategy is not permitted for an operation, then an error is raised.SHARED
: Acquire a read lock on the table. Permit read-only concurrent DML. If this locking strategy is not permitted for an operation, then an error is raised.EXCLUSIVE
: Acquire a write lock on the table. Do not permit concurrent DML.
If the LOCK
clause is not explicitly set, then the operation uses LOCK=DEFAULT
.
ALTER ONLINE TABLE
is equivalent to LOCK=NONE
. Therefore, the ALTER ONLINE TABLE
statement can be used to ensure that your ALTER TABLE
operation allows all concurrent DML.
To see which locking strategies InnoDB supports for each operation, see the pages that describe which operations are supported for each algorithm: