PolarDB:Perform DDL operations on cold data

Prerequisites

• Your cluster runs MySQL 8.0.2 whose revision version is 8.0.2.2.23 or later.

• The cold data archiving feature is enabled for the cluster. For more information, see Enable cold data archiving.

• A connection to the cluster is established. For more information, see Connect to a cluster.

• The cold data is in the comma-separated values (CSV) or Optimized Row Columnar (ORC) format. The Object Storage Service (OSS) table data and cold data mentioned in the following section refer to cold data in the CSV or ORC format.

DDL execution algorithms

PolarDB for MySQL supports the following DDL execution algorithms for cold data:

• INSTANT algorithm: If you use the INSTANT algorithm in a DDL operation, the operation modifies only metadata in the data dictionary. Existing data is not modified, copied, or rebuilt. The DDL operation can be completed in seconds regardless of the table size.

• COPY algorithm: If you use the COPY algorithm in a DDL operation, the operation copies all data in the table to a new table. During the copy operation, the original table is locked by the SHARED_NO_WRITE (SNW) lock. Only read operations are allowed. Write operations are prohibited, which negatively impacts businesses that depend on frequent updates or writes to the table.

When you execute a DDL statement, you can use the ALGORITHM clause to specify the algorithm that you want to use. The options are DEFAULT, INSTANT, and COPY. If you specify an algorithm that the DDL operation does not support, an error is returned.

INSTANT algorithm instructions

In 8.0.2.2.23 or later, you can enable OSS META for an OSS cold data table for enhanced data management. 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:

1. 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)

2. 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)
   

3. 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.

4. 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

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

1. 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.