PolarDB for MySQL Cold Data Archiving Supports DDL Changes Within Seconds

The article introduces PolarDB for MySQL's new ability to support quick DDL changes for cold data archiving, facilitating more efficient management and separation of cold and hot data.

By Daoke

Why is it necessary to support metadata changes after cold data (CSV/ORC) archiving? To better support the separation of cold and hot data to reduce costs, there are many complex scenarios during use. For example, some customers want to use hybrid partitions that separate cold and hot data to achieve a unified schema, and applications can automatically access the corresponding data through partition pruning. However, since cold data does not support DDL changes, hybrid partitions cannot be used in production. Some users want to automatically age to the corresponding historical OSS table through the scenario of partition aging to the cold storage table. However, after automatic archiving is configured, metadata changes lead to incompatibility of the newly aged data with the original data and the failure of automatic aging. Although some customers have used the table partitioning mechanism to continuously age to the corresponding historical OSS tables, if these historical data are accessed and analyzed in a unified manner, there will be a situation where data cannot be merged. In addition, the DDL of the COPY algorithm is too costly and thus unnecessary for cold storage tables. Therefore, PolarDB for MySQL 8.0.2.2.23 uses the mechanism of cold data metadata autonomy to support OSS (CSV/ORC) DDL changes within seconds.

INSTANT Algorithm Instructions for Code Data Tables

A table supports DDL operations that use the INSTANT algorithm (INSTANT DDL) only if OSS META is enabled for the table. To use INSTANT DDL on a table, perform the following operations:

Check whether OSS META is enabled for the table

Execute the show create table statement to check whether OSS META is enabled for the table. If "OSS META=1" is displayed in the output, OSS META is enabled for the table.

show create table t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` varchar(1000) DEFAULT NULL
) /*!99990 800020213 STORAGE OSS */ ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ /*!99990 800020223 OSS META=1 */
1 row in set (0.00 sec)

Enable OSS META for newly created tables

You can use the use_oss_meta parameter to enable or disable OSS META on new OSS foreign tables that are created in scenarios in which InnoDB non-partitioned tables are archived to OSS foreign tables, InnoDB partitioned tables are archived to OSS foreign tables, or InnoDB tables are archived to OSS partitions. You can use the following statement to check whether the use_oss_meta parameter is set to ON.

show variables like "use_oss_meta";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| use_oss_meta  | ON    |
+---------------+-------+
1 row in set (0.03 sec)

If the use_oss_meta parameter is set to ON, new OSS tables to which data is archived include the OSS META marker.

alter table t engine = csv storage oss;
Query OK, 3 rows affected (2.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

  
show create table t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` varchar(1000) DEFAULT NULL
) /*!50100 */ /*!99990 800020213 STORAGE OSS */ ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ /*!99990 800020223 OSS META=1 */
1 row in set (0.00 sec)
alter table t1 change partition p0 engine = orc;
Query OK, 0 rows affected (1.95 sec)
Records: 0  Duplicates: 0  Warnings: 0


show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020223 OSS META=1 */ CONNECTION='default_oss_server'
/*!99990 800020205 PARTITION BY RANGE  COLUMNS(id)
(PARTITION p0 VALUES LESS THAN (10) ENGINE = ORC,
 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (40) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (50) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (60) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (70) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (80) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (90) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (110) ENGINE = InnoDB) */
1 row in set (0.00 sec)

Enable OSS META for existing tables

You can use the repair statement to enable OSS META for an existing table.

repair table t;
+--------+--------+----------+----------+
| Table  | Op     | Msg_type | Msg_text |
+--------+--------+----------+----------+
| test.t | repair | status   | OK       |
+--------+--------+----------+----------+
1 row in set (0.84 sec)

show create table t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` varchar(1000) DEFAULT NULL
) /*!50100 */ /*!99990 800020213 STORAGE OSS */ ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ /*!99990 800020223 OSS META=1 */
1 row in set (0.00 sec)

Note

When you execute the REPAIR statement, the table is locked by the X lock. You cannot query or modify the table. The execution time of the REPAIR statement varies based on the size of the table。

Disable OSS META for the table

You can use the disable statement to disable OSS META for the table.

alter table t disable oss meta;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

show create table t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` varchar(1000) DEFAULT NULL
) /*!50100 */ /*!99990 800020213 STORAGE OSS */ ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */
1 row in set (0.00 sec)

After you disable OSS META for the table, the table no longer has the OSS META marker.

Note:

The disable statement immediately takes effect without the need to restart the database service.

DDL Operation Characteristics

Operations on columns

Operation	Rebuild the table	Only modify metadata
Add a column	No 1	Yes 1
Remove a column	Yes	No
Rename a column	No	Yes
Sort columns	Yes	No
Specify the default value for a column	No	Yes
Modify the comment of a column	No	Yes
Change the type of a column	Yes	No
Extend the length of a VARCHAR column	No	Yes
Change the UTF8mb3 character set of a column to the UTF8mb4 character set	No 2	Yes 2
Delete the default value of a column	No	Yes
Change the auto-increment value of a column	No	Yes
Change the values of a column to NULL	Yes	No
Change the values of a column to non-NULL values	Yes	No
Change the definition of an ENUM or SET column	No	Yes 3

1. The Instant ADD COLUMN feature adds columns only to the end of a table for which OSS META is enabled. If no primary key is specified for the table, you must set the value of the implicit_primary_key parameter to OFF to prevent conflicts with an automatically-generated implicit primary key column when you add a new column to the end of the table. If the cluster does not support the Instant ADD COLUMN feature, use the COPY algorithm to add columns. In this case, a table rebuild is required. During the rebuilding process, concurrent read operations on the table are allowed.

2. If the following conditions are met, you can change the character set of a column from UTF8mb3 to UTF8mb4 by modifying only the metadata. If the following conditions are not met, you must use the COPY algorithm to rebuild the table. The table is locked and only read operations are allowed during the rebuilding operation.

• The column type is CHAR, VARCHAR, ENUM, or TEXT.

• No indexes are created on the column.

• The maximum storage length of the column before and after the character set is converted remains shorter than 256 bytes or remains longer than 255 bytes.

You can forcibly use the INSTANT algorithm by specifying ALGORITHM=INSTANT in the DDL statement. If the operation cannot be performed by using the INSTANT algorithm, an error is returned. Example:

ALTER TABLE test modify column b char(1) CHARACTER SET utf8mb4 default null,algorithm = INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

3. You can modify only the metadata without the need to rebuild the table if you add new elements to the end of an ENUM or SET column and the storage size of the data type remains unchanged. Otherwise, the COPY algorithm is used, which involves a complete table rebuild.

Operations on tables

Operation	Rebuild the table	Only modify metadata
Enable META	Yes	No
Disable META	No	Yes
Declare a character set	No	Yes
Convert a character set	Yes	No
Rename a table	No	Yes 1
Modify the comment of a table	No	Yes

4. When you rename a table whose data is stored in OSS, the table data is not rewritten. Instead, the corresponding OSS data files are renamed. The speed of the operation is proportional to the size of the table and is slightly slower than other INSTANT operations that modify only metadata.

What to Do Next

PolarDB for MySQL 8.0.2 will continue to promote the automatic aging solution to support application scenarios in various industries. It will also launch cold data query acceleration and the column-storage combination to accelerate ORC data query. Please stay tuned.

>> For more information, please refer to the official documentation.

Community

PolarDB for MySQL Cold Data Archiving Supports DDL Changes Within Seconds

INSTANT Algorithm Instructions for Code Data Tables

Check whether OSS META is enabled for the table

Enable OSS META for newly created tables

Enable OSS META for existing tables

Disable OSS META for the table

DDL Operation Characteristics

Operations on columns

Operations on tables

What to Do Next

Read previous post:

Read next post:

ApsaraDB

You may also like

Comments

ApsaraDB

Related Products

PolarDB for MySQL

PolarDB for PostgreSQL

PolarDB for Xscale

AnalyticDB for MySQL