How do I use the online DDL feature for an ApsaraDB RDS instances that run MySQL 5.6? - ApsaraDB RDS

This topic describes how to use the online DDL feature for an ApsaraDB RDS instance that runs MySQL 5.6

ApsaraDB RDS instances that run MySQL 5.6 support the online DDL feature.

This feature allows you to perform DDL operations such as index creation on tables without blocking DML operations and SELECT queries that run concurrently with the DDL operations.

Note

After you upgrade your database engine version from an earlier version such as MySQL 5.5 to MySQL 5.6, you may not be able to perform DDL operations on a table for the first time because the table format is an earlier version. In this case, run the following command to convert the table format:

ALTER TABLE <The name of the table whose format you want to convert> engine=innodb;

For more information about the features that are supported by ApsaraDB RDS for MySQL, see Release notes for AliSQL.

Important

We recommend that you perform all DDL operations during off-peak hours to avoid impacts on your business.

Limits

DDL operation	In-place supported	Table-copy required	DML concurrency allowed	Query concurrency allowed	Remarks
Create a common index on a table	Yes	No	Yes	Yes	None.
Create a full-text index	Yes	No	No	Yes	You must create the first full-text index by using the table-copy method. Then, you can create other full-text indexes on the table by using the in-place method.
Delete an index	Yes	No	Yes	Yes	This DDL operation only modifies the metadata of the table.
Optimize tables	Yes	Yes	Yes	Yes	If a full-text index is created on the table, you cannot set the ALGORITHM option to INPLACE.
Set the default value in a column of a table	Yes	No	Yes	Yes	This DDL operation only modifies the metadata of the table.
Set the default value in an auto-increment column of a table.	Yes	No	Yes	Yes	This DDL operation only modifies the metadata of the table.
Add a foreign key constraint to a table	Yes	No	Yes	Yes	You can run the `set foreign_key_checks=0;` command to disable the foreign_key_checks option. This way, you do not need to copy the table.
Delete a foreign key constraint from a table	Yes	No	Yes	Yes	You can enable or disable the foreign_key_checks option.
Rename a column of a table	Yes	No	Yes	Yes	If this DDL operation only changes the column name without changing the data type, concurrent DML operations are allowed.
Add a column to a table	Yes	Yes	Yes	Yes	If the column that you want to add is an auto-increment column, you cannot perform concurrent DML operations. You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.
Delete a column from a table	Yes	Yes	Yes	Yes	You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.
Change the sequence of columns in a table	Yes	Yes	Yes	Yes	You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.
Modify the Row_Format attribute of a table	Yes	Yes	Yes	Yes	You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.
Modify the Key_Block_Size attribute of a table	Yes	Yes	Yes	Yes	You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.
Set the values in a column of a table to NULL	Yes	Yes	Yes	Yes	You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.
Set the values in a column of a table to NOT NULL	Yes	Yes	Yes	Yes	This DDL operation succeeds only when the SQL_MODE option is set to STRICT_ALL_TABLES or STRICT_TRANS_TABLES and the column does not contain NULL values. You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.
Modify the data type of a column in a table	No	Yes	No	Yes	None.
Add a primary key to a table	Yes	Yes	Yes	Yes	You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead. In addition, if you set the values in the column to NOT NULL, you cannot set the ALGORITHM option to INPLACE.
Delete the primary key of a table and add a new one	Yes	Yes	Yes	Yes	You can set the ALGORITHM option to INPLACE only when you execute one ALTER TABLE statement to delete the existing primary key and add a new one. You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.
Delete the primary key of a table	No	Yes	No	Yes	None.
Convert character set	No	Yes	No	Yes	If the new character set uses a different encoding format, you must rebuild the table.
Specify character set	No	Yes	No	Yes	If the new character set uses a different encoding format, you must rebuild the table.
Rebuild a table with the force option	Yes	Yes	Yes	Yes	If a full-text index is created on the table, you cannot set the ALGORITHM option to INPLACE.
Rebuild a table alter table ... engine=innodb	Yes	Yes	Yes	Yes	If a full-text index is created on the table, you cannot set the ALGORITHM option to INPLACE.
Set the persistent statistics attribute of a table	Yes	No	Yes	Yes	This DDL operation only modifies the metadata of the table.
Modify the comment of a table	Yes	No	Yes	Yes	None.

In-place supported: controlled by the ALGORITHM option in the DDL operation. This method consumes less disk space and I/O resources than the table-copy method.
Table-copy required: controlled by the ALGORITHM option in the DDL operation. This method consumes more disk space and I/O resources than the in-place method.
DML concurrency allowed: controlled by the LOCK option in the DDL operation.
Query concurrency allowed: In most cases, queries concurrent with the DDL operation are allowed.
For more information, see Online DDL Operations.
The DDL operation on a table modifies the table metadata. Therefore, you may wait for the metadata lock on that table. For more information about how to handle metadata locks, see Use DMS to release metadata locks.
The in-place method works in opposite to the table-copy method. However, even if the in-place method is specified, a DDL operation may still involve table copying, for example, the DDL operation used to add a column to a table.

Configuration suggestions

ALGORITHM: When you perform a DDL operation, we recommend that you set this option to INPLACE to avoid performance deterioration caused by disk space usage or I/O issues. If the DDL operation does not support this setting, it returns an error.
```
ALTER TABLE area_bak algorithm=inplace, modify father text;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
```
LOCK: When you perform a DDL operation, we recommend that you set this option to NONE. This ensures the smooth execution of DML operations concurrent with the DDL operation. If the DDL operation does not support this setting, it returns an error.
```
alter table area ALGORITHM=copy, lock=none,CONVERT TO CHARACTER SET utf8mb4;

ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.
```

By default, ApsaraDB RDS for MySQL sets the ALGORITHM option to INPLACE and the LOCK option to NONE, so you do not need to configure these two options. However, if you are worried that a DDL operation may increase system loads or block DML operations on the target table, we recommend that you set the ALGORITHM option to INPLACE and the LOCK option to NONE separately for testing. Therefore, an error is returned when one of the two settings is not supported.

Example

ALTER TABLE area algorithm=inplace, lock=none, add index idx_fa (father);

If your database engine version, such as MySQL 5.5, does not support online DDL operations, you can use the pt-online-schema-change tool of Percona.

For more information about the syntax for ALTER TABLE, see ALTER TABLE Syntax.

Troubleshooting

When you perform an online DDL operation on a large table with concurrent DML operations, the following error may be returned:

ALTER TABLE rd_order_rec add index idx_cr_time_detail (cr_time,detail);

ERROR 1799(HY000): Creating index 'idx_cr_time_detail' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

Cause

The system records all concurrent DML operations that you perform during a DDL operation by creating a temporary log file when a table is modified or when an index is created. The size of the temporary log file can be extended by the value of the innodb_sort_buffer_size parameter, but cannot exceed the size that is specified by the innodb_online_alter_log_max_size parameter.

If the temporary log file exceeds the maximum size, the DDL operation returns a failure message and rolls back all not committed concurrent DML operations. Therefore, we recommend that you use the innodb_online_alter_log_max_size parameter to specify a proper file size, so more concurrent DML operations are permitted. However, a larger file size increases the time taken by the DDL operation to lock the table and apply logged data to the table.

Solution

If your RDS instance runs MySQL 5.6 or MySQL 5.7, you can reconfigure the innodb_online_alter_log_max_size parameter in the ApsaraDB RDS console. For more information, see Modify instance parameters.