Archive cold data

Before you archive cold data, confirm your desired archive format and ensure that your cluster meets the requirements. For more information, see Archive format comparison and Prerequisites.

Applicable scope

Archiving to CSV format
- The cluster edition is Cluster Edition:
  - Archive a standard table:
    - MySQL 8.0.1 with a revision of 8.0.1.1.47 or later.
    - MySQL 8.0.2 with a revision of 8.0.2.2.10 or later.
  - Archive a partitioned table:
    - MySQL 8.0.2 with a revision of 8.0.2.2.34.1 or later.
- The cluster edition is Multi-master Cluster (Limitless) Edition:
  - MySQL 8.0.1 with a revision of 8.0.1.0.13 or later.
Archiving to ORC format
- The cluster edition is Cluster Edition:
  - Archive a standard table: MySQL 8.0.2 with a revision of 8.0.2.2.30 or later.
  - Archive a partitioned table: MySQL 8.0.2 with a revision of 8.0.2.2.34.1 or later.
- The cluster edition is Multi-master Cluster (Limitless) Edition: MySQL 8.0.2 with a revision of 8.0.2.2.30 or later.
Archiving to X-Engine format
- Archive a standard table:
  - MySQL 8.0.1 with a revision of 8.0.1.1.31 or later.
  - MySQL 8.0.2 with a revision of 8.0.2.2.12 or later.
- Archive a partitioned table: MySQL 8.0.2 with a revision of 8.0.2.2.12 or later.
- Archive to an X-Engine column-store table: MySQL 8.0.2 with a revision of 8.0.2.2.34.1 or later.

Parameters

When you use the cold data archiving feature, you can configure the following parameters on the Parameters page of your cluster based on your business requirements.

Parameter	Level	Description
`loose_polar_dlm_storage_mode`	Global	The switch for the automatic cold data archiving (DLM) feature. Valid values: OFF (default): DLM storage is disabled. The scheduler does not start and no archiving operations are performed. RO (not recommended): DLM storage is read-only. Archived cold data partitions can be queried, but no new archiving write operations are performed. This value is rarely used in production. RW (recommended): DLM storage is read-write. The scheduler starts and supports automatic archiving based on DLM policies, and archived data can also be queried. Note This parameter applies only to MySQL 8.0.2 clusters with revision version 8.0.2.2.34.1 or later. After you enable cold data archiving, the system automatically sets this parameter to `RW`. No manual modification is required. If you only use the X-Engine high-compression engine to archive partitioned tables (without OSS cold data archiving), you must manually set this parameter to `RW` to enable DLM scheduling.
`loose_allow_create_hybrid_partition`	Global	Controls whether to allow creating or archiving partitioned tables that contain non-InnoDB engine partitions. Valid values: OFF (default): Creating partitioned tables that contain non-InnoDB engine partitions is prohibited. All partitions must use the InnoDB engine. ON (recommended): Creating partitioned tables that contain non-InnoDB engine partitions is allowed. You can specify OSS (CSV/ORC) or the X-Engine engine at the partition level. Note This parameter applies only to MySQL 8.0.2 clusters with revision version 8.0.2.2.34.1 or later. After you enable cold data archiving, the system may automatically set this parameter to `ON` depending on the version.
`loose_use_oss_meta`	Global / Session	Controls whether OSS metadata (meta) management is enabled for OSS tables. The `loose_csv_oss_file_filter` parameter depends on this parameter. Valid values: ON: OSS metadata management is enabled. Meta information is written when OSS tables are created or archived. The file list is then retrieved by reading the metadata, which supports the `loose_csv_oss_file_filter` filtering feature. OFF: OSS metadata management is disabled. No meta information is written. Metadata is obtained by directly listing OSS files, and `loose_csv_oss_file_filter` is not supported.
`loose_csv_oss_file_filter`	Global / Session	Controls whether file-level filtering (File Filter) is enabled when querying OSS tables. This feature uses min/max statistics and Bloom filters to skip irrelevant data files and reduce the amount of OSS data scanned. This feature requires the `loose_use_oss_meta` parameter to be enabled. Valid values: OFF: File filtering is disabled. All data files in the OSS table are scanned during queries. ON (recommended): File filtering is enabled. During queries, `loose_csv_oss_file_filter` is used with WHERE conditions to improve query performance.
`dlm_prune_archiving_oss_partitions`	Global / Session	The switch for automatic pruning of archived OSS partitions by the Data Lifecycle Management (DLM) feature. When DML operations (UPDATE/DELETE) are executed, the system automatically prunes partitions that are being archived to OSS (ORC/CSV) and partitions that have already been archived to OSS. This prevents DML operations from being blocked by archiving and resolves conflicts between DML and DDL. Valid values: ON (default, recommended): DML operations (UPDATE/DELETE) automatically prune partitions that are being archived to OSS. OFF: DML operations do not automatically prune partitions that are being archived to OSS.
`hybrid_partition_query_mix_engine_enabled`	Global / Session	The switch for automatic query pruning. When a SELECT query is executed, the system automatically prunes OSS (ORC/CSV) partitions to prevent slow queries caused by accessing cold data. Valid values: OFF (default, recommended): Queries automatically prune OSS partitions. ON (not recommended): Queries do not automatically prune OSS partitions.

Archive to OSS: You must first enable cold data archiving. When archiving cold data, you can choose to archive common tables or partitioned tables.
- Common tables
  - Manually archive common tables in CSV/ORC format.
- Partitioned tables
  - You can manually archive specific partitions of a partitioned table in place or archive them to an OSS external table. For more information, see Archive partitions in place or Archive partitions to an OSS external table.
  - Create a data lifecycle management (DLM) policy to automatically archive cold data. For more information, see Automatically archive cold data.
Archive to X-Engine: You must first enable cold data archiving and the high-compression engine (X-Engine). When archiving cold data, you can choose to archive common tables or partitioned tables.
- Common tables
  - Manually archive data in X-Engine row-store or column-store format.
- Partitioned tables
  - You can manually archive specific partitions of a partitioned table to X-Engine partitions. For more information, see Archive data to X-Engine.
  - Create a data lifecycle management (DLM) policy to automatically archive cold data. For more information, see Archive partitioned tables to X-Engine.

Query cold data

Common tables
Regardless of the archive format you choose, you can query the archived data without changing how you access the table.
Partitioned tables
- Archive to an OSS external table: Querying this data requires a different access method: you must query the specified archive table. For more information, see Archive partitions to an OSS external table.
- Archive partitions in place: The table becomes a hybrid partitioned table after archiving. For more information, see Query hybrid partitions.
  You can query partitions on different storage engines in three ways:
  - Set the hybrid_partition_query_mix_engine_enabled parameter to ON. Queries then return data from InnoDB, X-Engine, and OSS partitions. Example:
```
-- t1 is a hybrid partitioned table.
SELECT * FROM t1;
```
  - Set the hybrid_partition_query_mix_engine_enabled parameter to OFF. Queries then return data only from InnoDB and X-Engine partitions. Example:
```
-- t1 is a hybrid partitioned table.
SELECT * FROM t1;
```
  - Query a specific partition by name. This retrieves data from the specified partition, regardless of its storage engine. Example:
```
-- t1 is a hybrid partitioned table, and p1 is the name of the partition to query.
SELECT * FROM t1 partition (p1);
```

Note

The system stores archived cold data in a multi-file format for each table. To optimize query performance, use a parallel query. For more information, see Parallel query on cold data.

Modify cold data

To infrequently modify cold data stored on Object Storage Service (OSS), use the ALTER statement to move it back to the PolarDB storage space for modification. The system then automatically deletes the data from OSS. After you modify the data, you can archive the table data to OSS again.

Common tables

Syntax

ALTER TABLE table_name ENGINE[=]engine_name;

Parameters

Parameter	Description
table_name	The name of the OSS table to move back to the PolarDB storage space.
engine_name	The storage engine to use after the table is moved back.

Usage notes

OSS tables are read-only. You cannot modify them by usingINSERT,UPDATE, orDELETE statements. To modify archived cold data, you must convert the OSS table into a read-write table, such as an InnoDB table. Attempting to modify a read-only OSS table returns the following error:

1036 - Table 't' is read only

Example

Move the OSS table t from the oss_test database back to the PolarDB storage space.

ALTER TABLE `oss_test`.`t` ENGINE = InnoDB;

Modify the data in the InnoDB tablet. After the modification is complete, archive the tablet from the InnoDB engine back to OSS. Example:

ALTER TABLE t ENGINE = CSV CONNECTION = 'default_oss_server';

ALTER TABLE t ENGINE = CSV STORAGE OSS;

Partitioned tables

Note

To modify cold data in a partitioned table, your PolarDB for MySQL cluster must be version 8.0.2 with revision version 8.0.2.2.34 or later.

Syntax

ALTER TABLE table_name REORGANIZE PARTITION part_name INTO (partition_definition);

Parameters

Parameter	Description
table_name	The name of the OSS table to move back.
part_name	The name of the partition to move back.
partition_definition	This must be consistent with the `partition_definition` of the partition that you want to move back.

Example

Move the data in partition p1 of the partitioned table t, which is archived on OSS, back to the PolarDB storage space.

ALTER TABLE t REORGANIZE PARTITION p1 INTO(PARTITION p1 values less than(100));

Delete cold data

Note

This feature is supported only on PolarDB for MySQL clusters that run the following versions:
- Version 8.0.1, and the minor version must be 8.0.1.1.42 or later.
- Version 8.0.2, and the minor version must be 8.0.2.2.23 or later.
If the minor version of your cluster does not meet the preceding requirements, you cannot delete the corresponding files on OSS. Upgrade the minor version of your cluster. For more information, see minor version management.

After you delete a table on OSS or import it back to PolarDB storage space, the files on OSS are not automatically deleted. After you confirm that the data is no longer needed, delete the corresponding files on OSS as follows. For more information, see Delete the corresponding files on OSS.

Common tables and OSS external tables
Use DROP TABLE to delete the archived table. Then, run the CALL dbms_oss.delete_table_file('database_name', 'table_name'); command to delete the corresponding files on OSS.
Partitioned tables
Run the CALL dbms_oss.delete_table_file('database_name', 'table_name'); command to delete the corresponding files on OSS.

The operation to delete the corresponding files on OSS is performed asynchronously. The files are completely deleted only after all nodes in the cluster no longer depend on them. This process may be delayed when traffic is heavy.

The FORCE STORAGE OSS option

Note

The FORCE STORAGE OSS option to forcibly delete OSS files is supported only on PolarDB for MySQL 8.0.2 clusters with a minor version of 8.0.2.2.29 or later.

Common tables and OSS external tables
```
DROP TABLE table_name [FORCE STORAGE OSS];
```
Note
The FORCE STORAGE OSS option of the DROP TABLE command provides a one-click cleanup feature. It automatically deletes the associated OSS storage files when you delete the table structure.
Partitioned tables
```
ALTER TABLE table_name DROP PARTITION part_name [FORCE STORAGE OSS];
```
Note
The DROP PARTITION clause of the ALTER TABLE statement, when used with the FORCE STORAGE OSS option, lets you synchronously delete the metadata of a partitioned table and its corresponding OSS storage files.
CHANGE PARTITION ENGINE
```
  ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = CSV|ORC [FORCE STORAGE OSS]
```
Note
The cold data archiving feature of CHANGE PARTITION ENGINE provides a forced overwrite mechanism that automatically replaces files with the same name.

Archiving format comparison

When archiving cold data, you can evaluate and select the appropriate format based on the following comparison.

Note

Archiving standard tables, OSS external tables, and partitioned tables has specific limitations. Review these limitations before archiving to avoid impact on your business.
After cold data is archived, the data is stored in the default system-provided Object Storage Service (OSS) bucket, not in your own OSS. Currently, you can view the list of archived data only in the PolarDB console.
Partitioned table archiving methods:
- Archive a partitioned table: Archives a partition in place. The data remains in the original table, but the storage medium for that partition changes from PolarDB (hot storage) to OSS (cold storage). The table becomes a hybrid partitioned table that contains both hot and cold partitions.
- Archive a partition to an OSS external table: Moves the data of a partition out of the table and stores it in a new, independent OSS external table. The original partition is deleted from the source table.

Comparison item	CSV	ORC	X-Engine
Open-source format	Yes	Yes	No
Archiving method	Manual archiving: Archive a standard table Archive a partition to an OSS external table Archive a partitioned table Automatic archiving: Archive a partitioned table	Manual archiving: Archive a standard table Archive a partitioned table	Manual archiving: Archive to X-Engine format Automatic archiving: Archive partitioned tables to X-Engine
Archiving speed	Fast Note Only single-threaded archiving is supported.	Slow Note Only single-threaded archiving is supported.	Fast Note Data is archived to PolarStore storage.
Query speed	Relatively slow. Without indexes and using sequential queries, query performance is about 1/5 to 1/10 that of the InnoDB storage engine. Faster than ORC format on row store nodes. Note Both single-threaded and multi-threaded reads are supported.	Relatively slow. Without indexes and using sequential queries, query performance is about 1/5 to 1/10 that of the InnoDB storage engine. Suitable for AP queries through a dedicated column store node. Note Only single-threaded reads are supported.	Fast. Data is stored in PolarStore storage, so query speed is significantly faster than OSS cold data, but about 30% slower than InnoDB. Row-store tables are suitable for TP queries, while column-store tables are suitable for AP queries on a column store node.
Transaction support	No	No	Yes
Indexing capability	No	No	Yes
How to modify archived data	Archived tables in OSS are read-only. To modify the data, you must import it back from OSS to PolarDB storage.		Archived tables support DML operations.
Storage space usage	Same as an InnoDB table without indexes.	For the same amount of data, ORC uses about 45% of the storage space that CSV uses.	Compresses data to 10%–50% of its InnoDB size. The actual compression ratio depends on data characteristics.
Backup and recovery	Not supported. Note Object Storage Service (OSS) provides 99.9999999999% (twelve 9s) data durability and 99.995% data availability, virtually eliminating the risk of cold data loss. When you perform a PolarDB backup, archived cold data in OSS is not backed up. Therefore, database and table restoration, backup-based recovery, and point-in-time recovery are not supported.		Supported.
Impact after archiving	Archive a standard table After archiving, you can query archived data without changing the access method. Archive a partition to an OSS external table After archiving, you must change the access method to query the archived data by querying the specified archived table. Archive a partitioned table The table becomes a hybrid partitioned table after archiving. For query methods, see Query a hybrid partition.	Archive a standard table After archiving, you can query archived data without changing the access method. Archive a partitioned table The table becomes a hybrid partitioned table after archiving. For query methods, see Query a hybrid partition.	After archiving, you can query archived data without changing the access method.