PolarDB:Archive in IBD Format

Applicable versions

• MySQL 8.0.1 with a revision of 8.0.1.1.51.2 or later.

• To use IBD format archiving, go to Quota Center, find the quota by Quota ID polardb_innodb_oss_enable, and click Request in the Actions column to enable the feature.

Important notes

• Read/write and transaction limits (DML & DDL)

  • DML and transaction support: Archived tables support normal DML operations such as INSERT, UPDATE, and DELETE, as well as transactions.

  • DDL limits: DDL operations are not supported. Once a table is archived to OSS, you cannot modify the table structure.

• Availability during archiving

  • Data access interruption: By default, data in a table cannot be accessed while the table is being archived.

  • Temporary workaround: To keep a table readable during archiving, set the polar_oss_ddl_shared parameter to ON in advance.

• Backup and data security

  • Not included in standard backups: Automatic and manual backups do not include data stored in OSS.

  • Restore limits: You cannot perform database or table restoration or point-in-time recovery on data in OSS.

• Performance impact and O&M recommendations

  • Resource consumption during archiving: Archiving cold data consumes network and storage I/O resources. We recommend that you perform this operation during off-peak hours or on inactive clusters.

  • High latency risk: OSS I/O latency is more than 100 times higher than block storage. Assess the impact on long-running SQL queries.

  • Heavy write operations: Before you run heavy write or delete operations on an OSS table, move the table back to block storage first. Otherwise, the following issues may occur:

    1. Cache pollution: A large amount of database buffer pool resources is consumed, causing SQL queries on other tables to slow down because they cannot obtain free pages.

    2. Checkpoint blocking: Slow persistence of a large number of OSS dirty pages blocks checkpoint advancement, which prolongs crash recovery time.

    3. Slower crash recovery: During crash recovery, log replay on OSS tables triggers OSS read and write operations, further slowing down crash recovery.

Archive table data to OSS

When the data in a table is cold data and not latency-sensitive, or when data access is concentrated and can be cached with acceptable tail latency, convert the block storage table to an OSS table by using the following syntax:

ALTER TABLE table_name STORAGE_TYPE OSS;

Move data back to block storage

ALTER TABLE table_name STORAGE_TYPE NULL;

Delete the corresponding files in OSS

After you drop an OSS table or move it back to block storage, the corresponding files in OSS are not automatically deleted. After you confirm that the data is no longer needed, use the following syntax to delete the corresponding files in OSS:

CALL dbms_oss.delete_table_file('database_name', 'table_name');

File deletion in OSS is an asynchronous operation. The system waits until all nodes in the cluster no longer depend on the OSS files before the files are fully deleted. During periods of high traffic, some latency may occur. If the command fails and returns the error message OSS files are still in use, wait for a while and try again.

Example

The following example includes operations for dropping a table, dropping a database, and deleting archived files in OSS. Run the operations in a test database.

1. Create a database.

   CREATE DATABASE oss;

2. Use the database.

   USE oss;

3. Create the toss1 table.

   CREATE TABLE `toss1`(
     `uid` int NOT NULL AUTO_INCREMENT,
     `text_data` text NOT NULL,
     `save_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     PRIMARY KEY(`uid`)
   ) ENGINE = InnoDB;

4. Create the populate stored procedure.

   DELIMITER |
   CREATE PROCEDURE populate(IN `cnt` INT)
   BEGIN
     DECLARE i INT DEFAULT 1;
     WHILE (i <= cnt) DO
       INSERT INTO `toss1` (`text_data`) VALUES (REPEAT("#", 6333));
       SET i = i + 1;
     END WHILE;
   END |
   DELIMITER ;

5. Call the stored procedure to insert data into the toss1 table.

   CALL populate(5000);

6. Archive the data to OSS.

   ALTER TABLE `toss1` STORAGE_TYPE OSS;

7. Query data in the toss1 table.

   SELECT COUNT(*) FROM `toss1`;

8. Drop the toss1 table in OSS.

   DROP TABLE `toss1`;

9. Drop the OSS database.

   DROP DATABASE oss;

10. After you confirm that the data is no longer needed, delete the corresponding files in OSS.

    CALL dbms_oss.delete_table_file('oss', 'toss1');

Time required for cold data archiving

The number of concurrent threads used for cold data archiving is controlled by the innodb_oss_copy_worker parameter, and the parameter value is bound to the cluster specifications. The following table shows the time required for cold data archiving with different thread counts and data volumes:

Applicable versions

• MySQL 8.0.2 with a revision of 8.0.2.2.37 or later. The storage type must be PSL4 or PSL5. ESSD cloud disks are not supported.

• In the console, go to Settings and Management > Parameters and enable the loose_polar_oss_enable parameter.

• The target table uses a file-per-table tablespace and the InnoDB storage engine. All partitions of a partitioned table must use the InnoDB engine. Compressed tables and general tablespaces are not supported.

• Run archiving, querying, and move-back operations on the primary node through the primary endpoint, and make sure that the current account has the required permissions on the target table.

Important notes

• Read/write and partition behavior (DML)

  • DML and transaction support: Archived tables and partitions remain writable. INSERT, UPDATE, and DELETE operations and transactions are supported.

  • Partition-specific behavior:

    • Queries: Archived partitions are automatically skipped by default.

    • Updates and deletes: UPDATE and DELETE operations do not skip archived partitions. Before you run these operations, verify the scope of impact to avoid accidental modifications or request backlogs caused by OSS latency.

• Schema change support (DDL rules)

  • DDL capabilities: Common DDL operations are supported, such as adding columns, creating indexes, and adjusting partitions (subject to InnoDB limits).

  • Archiving status inheritance: Archiving status is automatically retained or adjusted along with schema changes. However, after you reorganize partitions, we recommend that you manually verify the archiving status of each partition.

  • Syntax limits (no merging):

    • Archiving and move-back operations must be run separately. They cannot be combined with other schema changes (such as adding a column or changing a data type) in a single ALTER TABLE statement.

    • When you run archiving and move-back operations, you cannot explicitly specify ALGORITHM=COPY or ALGORITHM=INSTANT. This limit applies only to the archiving operation itself and does not affect regular COPY DDL operations on archived tables.

• Table compression and tablespace limits

  Before you perform the following operations, you must first move the data of the relevant table or partition back from OSS to block storage. The move-back operation does not change the InnoDB storage engine of the table.

  • Enable table compression: ROW_FORMAT=COMPRESSED and page compression are not supported in the archived state. Move the data back before you enable compression.

  • Move into a general tablespace: A general tablespace allows multiple tables to share the same data file. OSS archiving requires each table or partition to use an independent .ibd file. Therefore, tables in general tablespaces are not supported for archiving. Move the data back before you move an archived table into a general tablespace.

  • Import or discard a tablespace: Before you run IMPORT TABLESPACE (import a tablespace file) or DISCARD TABLESPACE (discard the tablespace used by the table), move the data back first. DISCARD TABLESPACE is not a move-back operation and cannot be used to move data back to block storage.

• Transparent Data Encryption (TDE)

  • TDE is fully supported. Archiving and move-back operations do not change the existing encryption properties of a table.

  • To modify encryption properties, use the default algorithm or explicitly specify ALGORITHM=COPY. ALGORITHM=INPLACE is not supported.

• Backup, security, and O&M recommendations

  • Archiving is not a backup: DROP and TRUNCATE operations physically delete or clear data in OSS. Do not use archiving as a backup alternative.

  • Concurrency blocking and resource consumption: Archiving and move-back operations lock the target table, which may block concurrent reads and writes and consume network and storage I/O resources. We recommend that you run these operations during off-peak hours.

  • Bulk modification recommendation: Because OSS access latency is higher than block storage, we recommend that you move archived data back to block storage before you perform bulk modifications.

Archive table data to OSS

Archiving is suitable for data that is accessed infrequently and is not latency-sensitive. Specifying CREATE TABLE with STORAGE_TYPE does not automatically archive a newly created table. Create the table first, and then run the archiving operation:

ALTER TABLE table_name STORAGE_TYPE=HYBRID_OSS;

This syntax also applies to partitioned tables. After the statement is executed, all partitions in the table are archived.

Archive specified partitions to OSS

-- Archive one partition.
ALTER TABLE table_name MODIFY PARTITION p0 STORAGE_TYPE=HYBRID_OSS;

-- Archive multiple partitions.
ALTER TABLE table_name MODIFY PARTITION p0, p1 STORAGE_TYPE=HYBRID_OSS;

Archive the entire table when all data in the table is cold data. Archive only specified partitions when only some partitions contain cold data. Do not archive the same partition more than once. If the table contains subpartitions, you can specify a subpartition name. If you specify a parent partition name, all subpartitions under that parent partition are archived.

Query archived data

Archived regular tables can be queried directly in the same way as before archiving. Partitioned tables query only non-archived partitions by default. To include archived partitions in a query, add WITH ARCHIVED to the statement, or disable archived partition filtering for the current session.

• Single query: Add WITH ARCHIVED after the table name. This applies only to the statement that includes this clause and is suitable for one-time queries of archived data:

  -- Query non-archived partitions.
  SELECT * FROM table_name;
  
  -- Query data, including archived partitions.
  SELECT * FROM table_name WITH ARCHIVED;

• Current session: After you disable archived partition filtering for the current session, regular queries include archived partitions. This method applies to the entire current session and is suitable for scenarios in which you need to continuously query archived data:

  SET SESSION prune_archived_oss_partitions=OFF;

  The default value of the prune_archived_oss_partitions parameter is ON. After you set it to OFF, queries still filter data based on WHERE conditions and normal partitioning rules. To restore the default behavior, set this parameter to ON.

Check the archiving status

SHOW CREATE TABLE table_name;

In the output, if the table or a partition contains STORAGE_TYPE=HYBRID_OSS, the object is archived.

Move data back to block storage

Move-back operations retain your business data and cancel the archiving status of the corresponding table or partition.

-- Move back a regular table. For a partitioned table, all archived partitions are moved back.
ALTER TABLE table_name STORAGE_TYPE=NULL;

-- Move back only a specified partition.
ALTER TABLE table_name MODIFY PARTITION p0 STORAGE_TYPE=NULL;

-- Move back all archived partitions.
ALTER TABLE table_name MODIFY PARTITION ALL STORAGE_TYPE=NULL;

Use the first statement when you need to restore the entire table or all archived partitions of a partitioned table. Use MODIFY PARTITION to specify a partition when you only need to restore individual partitions. The specified partition must be in the archived state. When you use ALL, all currently archived partitions are selected.

Example

The following examples demonstrate operations on a regular table and a partitioned table. Run the examples in a test database. The examples use a small amount of data to demonstrate the operations. The actual storage space saved depends on the size of the table.