What is the instant modify column feature of PolarDB - PolarDB

To modify the data type of a column, MySQL rebuilds the data of the entire table by using the COPY algorithm. During this process, the table is read-only and does not allow concurrent write operations. Moreover, the COPY algorithm rebuilds the entire table on a single thread, which is slow and inefficient. This is a long-standing pain point for MySQL users who manage large datasets. To address this issue, PolarDB for MySQL provides the Instant Modify Column feature, which allows quick modification of column types regardless of the table size. Instead of rebuilding the entire table, the Instant Modify Column feature modifies only the metadata of the table. This topic describes how to use the Instant Modify Column feature.

Prerequisites

You cluster runs PolarDB for MySQL 8.0.2, with a revision version of 8.0.2.2.28 or later. Query the database engine version of the cluster before your operation to make sure that the database engine version meets the requirements.

Parameters

Parameter

Level

Description

loose_innodb_polar_instant_modify_column_enable

Global

Specifies whether to enable the Instant Modify Column feature. Valid values:

ON
OFF (default)

loose_innodb_polar_instant_modify_column_max_times

Global

The maximum number of times that the Instant Modify Column feature can modify the column types of a table.

Value range: 1 to 64. Default value: 16.

Note

The Instant Modify Column feature of PolarDB for MySQL is in canary release. To use the feature, go to Quota Center, find the quota with the ID polarm_82_instant_modify_column, and then click Apply.

Limits

You can use the Instant Modify Column feature to modify only the length of the following column types. You cannot use the Instant Modify Column feature to modify other column properties than lengths.
- Integer types: You can use the Instant Modify Column feature only to increase the length of an integer column among the TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT types.
  For example, you can change a column from an INT type to a BIGINT type but not from a BIGINT type to an INT type.
- String types:
  - You can use the Instant Modify Column feature to change a string column from a VARCHAR(N) type to a VARCHAR(N+M) type, in which N and M are positive integers greater than 0.
    For example, you can change a string column from a VARCHAR(50) type to a VARCHAR(300) type, but not from a VARCHAR(300) type to a VARCHAR(50) type.
  - You can use the Instant Modify Column feature to change a string column from a CHAR(N) type to a CHAR(N+M) type, in which N and M are positive integers greater than 0.
    For example, you can change a string column from a CHAR(30) type to a CHAR(50) type, but not from a CHAR(50) type to a CHAR(30) type.
You can use the Instant Modify Column feature only for tables that use the COMPACT or DYNAMIC row format.
You cannot use the Instant Modify Column feature to modify a column on which an index is created.
You cannot use the Instant Modify Column feature to modify the columns of a partitioned table.
You cannot use the Instant Modify Column feature to modify the columns of a table in which full-text indexes or spatial indexes are created.
You cannot include the Instant Modify Column operation and other DDL operations in the same SQL statement.

Note

Before you execute a DDL operation, you can use the EXPLAIN DDL feature to check if the operation supports the INSTANT algorithm.

Usage

Specify the INSTANT algorithm to force the use of the Instant Modify Column feature (recommended)

CREATE TABLE t(a int);

SHOW CREATE TABLE t;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- Specify the use of the INSTANT algorithm to quickly modify a column type.
ALTER TABLE t modify COLUMN a bigint DEFAULT NULL,algorithm=INSTANT;

Note

If a column cannot be added by using the Instant Modify Column feature, the following error message is returned: ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY/INPLACE. In this case, we recommend that you check that the loose_innodb_polar_instant_modify_column_enable parameter is set to ON, and determine whether the operation is restricted. For information about the limits of the Instant Modify Column feature, see the "Limits" section of this topic.

Do not specify the algorithm or specify the default algorithm for PolarDB for MySQL to choose the optimal execution method

If you do not specify an algorithm, or you specify the default algorithm, the system selects the optimal algorithm based on the algorithm priorities.

ALTER TABLE test.test_table MODIFY COLUMN test_column bigint, ALGORITHM=DEFAULT;
ALTER TABLE test.test_table MODIFY COLUMN test_column bigint;

Note

PolarDB for MySQL evaluates and selects DDL algorithms in the following order of preference:

INSTANT algorithm: modifies only the metadata in the data dictionary, without the need to modify the actual data in the table. The DDL operation can be completed in seconds, regardless of the table size.
INPLACE algorithm: allows concurrent read and write access during DDL operations to minimize disruption to business operations. Parallel DDL can be used to accelerate the operation.
COPY algorithm: rebuilds the entire table data. During this process, the table is read-only and does not allow write access, which significantly affects business operations.

View information about columns modified by using the Instant Modify Column feature

The INNODB_INSTANT_COLUMNS table in the INFORMATION_SCHEMA database records all columns that have been modified by using the Instant Modify Column feature in the current cluster. Only columns that have been modified by using the Instant Modify Column feature are included in this table. INNODB_INSTANT_COLUMNS table schema:

SHOW CREATE TABLE INFORMATION_SCHEMA.INNODB_INSTANT_COLUMNS;
***************************[ 1. row ]***************************
Table        | INNODB_INSTANT_COLUMNS
Create Table | CREATE TEMPORARY TABLE `INNODB_INSTANT_COLUMNS` (
  `TABLE_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `TABLE_NAME` varchar(193) NOT NULL DEFAULT '',
  `SCHEMA_NAME` varchar(193) NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(193) NOT NULL DEFAULT '',
  `MTYPE` int(11) NOT NULL DEFAULT '0',
  `PRTYPE` int(11) NOT NULL DEFAULT '0',
  `LEN` int(11) NOT NULL DEFAULT '0',
  `HAS_DEFAULT` int(1) NOT NULL DEFAULT '0',
  `DEFAULT_VALUE` mediumblob,
  `CURRENT_VERSION` int(11) NOT NULL DEFAULT '0',
  `OLD_VERSION_INFO` blob
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The following table describes the fields returned from the preceding statement.

Column name	Description
TABLE_ID	The table ID.
TABLE_NAME	The table name.
SCHEMA_NAME	The schema name.
COLUMN_NAME	The column name.
MTYPE	The column type.
PRTYPE	The precise column type or attributes.
LEN	The length of the column.
HAS_DEFAULT	Whether the column has a default value.
DEFAULT_VALUE	The default value of the column.
CURRENT_VERSION	The version information of the column.
OLD_VERSION_INFO	The historical type information of the column.

The INFORMATION_SCHEMA.INNODB_INSTANT_COLUMNS table tracks columns that are modified by using the Instant Modify Column feature. You can query the table by executing the following statement:

SELECT * FROM INFORMATION_SCHEMA.INNODB_INSTANT_COLUMNS;

Note

If you perform a DDL operation that requires a table rebuilding such as a DROP COLUMN operation on a column that was previously modified by using the Instant Modify Column feature, the system removes the related column information from the INNODB_INSTANT_COLUMNS table.