Cold data refers to data in database tables that is rarely updated and infrequently accessed. If you want to reduce costs, you can use the cold data archiving feature to move this data to low-cost Object Storage Service (OSS) and reduce storage expenses.
How it works
PolarDB for MySQL supports archiving data in CSV or ORC format. The archiving process is as follows:
Archive data manually or automatically. Data is converted to CSV or ORC format and stored as multiple files in OSS, then automatically deleted from PolarDB storage to reduce costs. Cluster nodes access archived data over the Alibaba Cloud internal network. Manually archive cold data and Automatically archive cold data.
When you Archive a partitioned table, if your kernel minor version is earlier than 8.0.2.2.33, go to Quota Center, find the quota name by Quota ID polardb_mysql_hybrid_partition, and click Apply in the Actions column to enable this feature.
Archiving format comparison
When archiving cold data, you can evaluate and select the appropriate format based on the following comparison.
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: |
|
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 |
Note Both single-threaded and multi-threaded reads are supported. |
Note Only single-threaded reads are supported. |
|
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
| Supported. | |
Impact after archiving |
|
| After archiving, you can query archived data without changing the access method. |
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.
Billing
Cold data is billed based on the storage capacity of cold data in OSS. The specific charges are as follows.
The Chinese mainland | China (Hong Kong) and other regions |
USD 0.0000325 per GB-hour | USD 0.0000455 per GB-hour |
For example, if you archive 100 GB of cold data for a cluster in the Chinese mainland, the hourly fee is 100 GB × USD 0.0000325 per GB-hour = USD 0.00325 per hour.
For more information about how to view the amount of archived cold data, see View cold data archiving information.
How to use
For more information, see Used cold data archiving.