Cold data archiving - PolarDB - Alibaba Cloud Documentation Center

Cold data refers to data in database tables that is rarely updated or accessed. You can use the cold data archiving feature of PolarDB for MySQL to archive cold data to Object Storage Service (OSS). This way, you can significantly reduce data storage costs.

How it works

PolarDB for MySQL allows you to archive data in the CSV or ORC format. The archiving methods differ for the CSV and ORC formats. The following figure shows how data is archived in the formats.

You can manually or automatically archive data. The archived data is converted to the CSV or ORC format and stored in multiple files in OSS. The corresponding storage space in PolarDB is released after data is archived. As a result, the storage fees are reduced. The cold data stored in OSS is accessible to PolarDB nodes over the internal network of Alibaba Cloud. For more information, see Manually archive cold data and Automatically archive cold data.

Note

The partitioned table archiving feature is in canary release. To use the feature, go to Quota Center, search for the quota name based on the polardb_mysql_hybrid_partition quota ID, and then click Apply in the Actions column.

Comparison of archiving data in different formats

The following table compares data archiving in different formats. Select a suitable format based on your business requirements.

Item	CSV	ORC
Open source	Yes	Yes
Archiving method	Manual archiving Archive common tables Archive table partitions to an OSS foreign table Archive partitioned tables Automatic archiving Archive partitioned tables	Manual archiving Archive common tables Archive partitioned tables
Archiving speed	Faster than the ORC format. Note Only single-threaded archiving is supported.	Slow. Note Only single-threaded archiving is supported.
Query speed	Slow. When queries are executed without indexes and serially performed, performance is approximately one-fifth to one-tenth of the performance achieved on the InnoDB storage engine. Faster than the ORC format on row store nodes. Note Single-threaded reading and multi-threaded reading are supported.	Slow. When queries are executed without indexes and serially performed, performance is approximately one-fifth to one-tenth of the performance achieved on the InnoDB storage engine. Suitable for AP queries on a separate column store node. Note Only single-threaded reading is supported.
Transactions	Not supported	Not supported
Indexing	Not supported	Not supported
How to modify archived data	Archive tables in OSS are read-only. If you want to modify archived data, you must import the data from the OSS table to the PolarDB storage space.
Storage consumption	Same storage consumption as tables without indexes that use the InnoDB engine.	ORC files occupy 45% of the storage space occupied by CSV files for the same data volume.
Backup and restoration	Not supported. Note When you perform backup operations, the cold data archived in OSS is not backed up. As a result, you cannot restore archived cold data from backups.
Impact	Archive common tables After data is archived, you can still query the data by specifying the original table name. Archive table partitions to an OSS foreign table After data is archived, you must specify the archive table name to access the archived data. Archive partitioned tables The archive table is a hybrid partitioned table. For information about how to query the archived data, see Query data in a hybrid partitioned table.	Archive common tables After data is archived, you can still query the data by specifying the original table name. Archive partitioned tables The archive table is a hybrid partitioned table. For information about how to query the archived data, see Query data in a hybrid partitioned table.

Note

Before you archive common tables, OSS foreign tables, and partitioned tables, review the limits to prevent negative impacts on your business.
Archived data is stored in the default OSS bucket. You can view the archived data list only in the PolarDB console.

Supported versions

To archive data in the CSV or ORC format, a cluster of Enterprise Edition must meet the following requirements:

If the cluster is of Cluster Edition, it must run one of the following database engine versions:
- PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.31 or later
- PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.9 and later.
If the cluster is of Multi-master Cluster (Database/Table) Edition, it must run PolarDB for MySQL 8.0.1.0.13 or later.

Billing

You are charged for cold data stored in OSS based on the amount of storage space the data occupies. The following billing rules apply. You can purchase a PolarDB storage plan to offset the storage costs of cold data in OSS. For more information, see Storage plans.

Regions in the Chinese mainland	China (Hong Kong) and regions outside China
USD 0.0000325 per GB-hour	USD 0.0000455 per GB-hour

Example: You enable and perform cold data archiving for a cluster deployed in the Chinese mainland. The amount of cold data archived in OSS is 100 GB. The hourly cold data storage fee is 100 GB × USD 0.0000325 per GB-hour = USD 0.00325 per hour.

Note

For information about how to view the amount of archived cold data, see View information about the cold data archived in OSS.

Procedure

For more information, see Usage notes.

Expert service

If you have questions about the cold data archiving feature, join the DingTalk group 24490017825 to obtain technical support. You can consult with experts in the group to obtain answers to your questions. You can also use the 24/7 PolarDB assistants to answer your questions