Cold data is data in a cluster's tables that is infrequently updated and rarely accessed. To lower storage costs, you can use the cold data archiving feature to transfer this data to the low-cost Object Storage Service (OSS).
How it works
PolarDB for MySQL supports archiving data in CSV or ORC format. The archiving process is as follows:
You can archive data either manually or automatically. The archived data is converted into CSV or ORC format and stored as multiple files in OSS. This data is then automatically deleted from the PolarDB storage space, reducing storage usage and lowering your storage fees. Cluster nodes can then access the data in OSS over the Alibaba Cloud internal network. For more information, see Manually archive cold data and Automatically archive cold data.
When you archive a partition, if your kernel version is earlier than 8.0.2.2.33, go to the Quota Center, find the quota by its Quota ID polardb_mysql_hybrid_partition, and then click Request in the Actions column to enable the feature.
Format comparison
Use the following comparison to select the best archiving format for your needs.
Limitations apply when you archive standard tables, OSS external tables, and partitioned tables. Review these limitations carefully before you archive data to avoid disrupting your business.
After cold data is archived, the data is stored by default in a system-provided Object Storage Service (OSS) bucket, not in your own OSS bucket. Currently, you can view the list of archived data only in the PolarDB console.
Partitioned table archiving methods:
Archive a partition: Archives a partition of a table in place. The data remains in the original table, but the storage medium for that partition is changed 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 from a partition to a new, separate OSS external table. The original partition is then deleted from the source table.
Comparison item | CSV | ORC | X-Engine |
Open-source format | Yes | Yes | No |
Archiving method |
| Manual archiving: |
|
Archiving speed | Medium Note Only single-threaded archiving is supported. | Slow Note Only single-threaded archiving is supported. | Fast Note Data is archived to PolarStore. |
Query speed |
Note Both single-threaded and multi-threaded data reads are supported. |
Note Only single-threaded data 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 into the PolarDB storage space. | DML operations can be performed on the archived table. | |
Storage space usage | Same as the storage space used by a table in the InnoDB engine without an index. | For the same amount of data, ORC format uses 45% of the storage space required by CSV format. | Compared to the InnoDB engine, data can be compressed to 10% to 50% of its original size. The specific compression ratio depends on the data characteristics. |
Backup and recovery | Not supported. Note
| Supported. | |
Impact after archiving |
|
| After archiving, you can query the archived data without changing how you access the table. |
Availability
Archiving to CSV format
For Cluster Edition clusters:
Archive a standard table:
MySQL 8.0.1, revision 8.0.1.1.47 or later.
MySQL 8.0.2, revision 8.0.2.2.10 or later.
Archive a partitioned table:
MySQL 8.0.2, revision 8.0.2.2.34.1 or later.
For Multi-master Cluster (Limitless) Edition clusters:
MySQL 8.0.1, revision 8.0.1.0.13 or later.
Archiving to ORC format
For Cluster Edition clusters:
Archive a standard table: MySQL 8.0.2, revision 8.0.2.2.30 or later.
Archive a partitioned table: MySQL 8.0.2, revision 8.0.2.2.34.1 or later.
For Multi-master Cluster (Limitless) Edition clusters: MySQL 8.0.2, revision 8.0.2.2.30 or later.
Archiving to X-Engine format
Archive a standard table:
MySQL 8.0.1, revision 8.0.1.1.31 or later.
MySQL 8.0.2, revision 8.0.2.2.12 or later.
Archive a partitioned table: MySQL 8.0.2, revision 8.0.2.2.12 or later.
Archive to an X-Engine column-store table: MySQL 8.0.2, revision 8.0.2.2.33 or later.
Billing
Cold data is billed based on the storage capacity that it uses in OSS. The following table lists the billing rates.
Chinese mainland | Hong Kong (China) and other regions |
USD 0.0000325/GB/hour | USD 0.0000455/GB/hour |
For example, if you archive 100 GB of cold data, the hourly cost is 100 GB × USD 0.0000325/GB/hour = USD 0.00325/hour.
To check how much cold data your cluster has archived, see View cold data archiving information.
How to use
For more information, see Usage instructions.