You can use the cold data archiving feature in a PolarDB for MySQL cluster to archive data that is infrequently accessed and rarely updated to Object Storage Service (OSS). This can significantly reduce data storage costs. This topic describes the cold data archiving feature.
How it works
The following figure shows the process of archiving cold data into CSV or ORC format.
The hot data of a PolarDB cluster is stored in PolarStore. The nodes in the cluster access the data in PolarStore by using PolarFileSystem. To reduce data storage costs and separate hot and cold data, you can transfer cold data from PolarStore to OSS.
For common tables, you can archive cold data to OSS in the CSV or ORC format. PolarDB nodes can access cold data in OSS over the internal network.
For partitioned tables, you can archive cold data to OSS in the CSV or ORC format. You can also create data lifecycle management (DLM) policies on the primary node to automatically archive cold data. The cold data archiving feature allows you to archive specific data from a partitioned table to OSS by using an OSS foreign table, or transfer data from specific partitions of a partitioned table to OSS.
The archived data is in the CSV or ORC format and stored in multiple files in OSS. The corresponding data in PolarStore is automatically deleted. Less storage space is occupied and therefore the storage costs are reduced.
The cold data archiving feature for partitioned tables is in canary release. To use the feature, go to Quota Center, enter the quota ID
polardb_mysql_hybrid_partitionto search for the quota name, and then click Apply in the Actions column.After cold data is archived, the archived table in OSS is read-only, and the query performance becomes low. If you use serial queries, the query performance of an archived table without an index is about one-fifth to one-tenth of that on the InnoDB storage engine. We recommend that you use test data to check whether the query performance meets your requirement before you archive data.
Cold data archived in the CSV or ORC format cannot be used for transactions.
Cold data archived in OSS is not backed up when you perform backup operations. Therefore, you cannot restore archived cold data from the backups.
Billing
Cold data is charged based on the space occupied in OSS. The following billing rules are used:
For regions within the Chinese mainland: USD 0.0000325 per GB-hour.
For regions outside the Chinese mainland: USD 0.0000455 per GB-hour.
You can purchase a PolarDB storage package to offset the storage costs of cold data in OSS. For more information about PolarDB storage plans, see What are storage plans
The following table describes the offset rules of PolarDB storage plans.
Region type | Offset ratio | OSS storage capacity offset by a 1-GB storage plan |
Regions in the Chinese mainland | 0.045 | 1/0.045=22.22 GB |
Regions outside the Chinese mainland | 0.045 | 1/0.045=22.22 GB |
For example, you purchase a storage plan with a capacity of 100 GB, and 50 GB is left after it offsets the data storage usage. The cold data of the cluster consumes 50 GB of OSS storage space in a region inside the Chinese mainland. In this case, the storage plan automatically uses 2.25 GB (50/2.25) to offset the backup storage usage and the storage plan has 47.75 GB of capacity left.
If the remaining capacity of the storage plan is insufficient to deduct the OSS storage space consumed by the code data, you are charged for additional storage space on a pay-as-you-go basis.
For information about how to view the storage usage of cold data in OSS, see View information about the cold data archived in OSS.
For information about billing, see Bills.
Supported versions
To archive data in the CSV or ORC format, your Enterprise Edition cluster must meet the following requirements:
A cluster whose Edition is Cluster Edition must run one of the following PolarDB for MySQL 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 or later
A cluster whose Edition is Multi-master Cluster (Database/Table) Edition must run PolarDB for MySQL 8.0.1.0.13 or later.
Comparison between different file formats
When you archive cold data, you can select an appropriate file format based on your requirements.
File format | Archiving speed | Reading speed | Open-source | Storage consumption | How to modify archived data |
CSV | Faster than the ORC format. Note Only single-threaded archiving is supported. | Faster than the ORC format on row store nodes. Note Both single-threaded and multi-threaded reading methods are supported. | Yes. | Consistent with the storage space occupied by tables using the InnoDB engine and without indexes. | Before you modify data, you must import the data from OSS tables to PolarStore. |
ORC | Slow. Note Only single-threaded archiving is supported. | Suitable for AP queries on a separate column store node. Note Only single-threaded reading is supported. | Yes. | For the same amount of data, ORC files occupies 45% of the storage space occupied by CSV files. |
Usage
Archive cold data
To archive cold data, log on to the PolarDB console, enable cold data archiving, connect to the cluster, and then perform archive operations.
For common tables, you can manually archive cold data to OSS in the CSV or ORC format.
For partitioned tables, you can manually archive cold data to OSS in the CSV or ORC format. You can also create DLM policies on the primary node to automatically archive cold data.
Query archived cold data
After you archive cold data, you can use one of the following methods to query the archived cold data:
Common tables: Use the method that you use to query hot data. You do not need to change the access mode.
Partitioned tables: For information about how to query cold data, see Query data in a hybrid partitioned table.
Archived cold data in a single table is distributed across multiple files. You can perform parallel queries to check the data. For more information, see Perform multi-file queries for a single OSS foreign table.
Modify archived cold data
If you want to modify the cold data archived in OSS, you can execute the
ALTER ENGINEstatement to import data from OSS to PolarStore. After the data is imported to PolarStore, the cold data in OSS is deleted. After you modify the data, you can archive the modified table to OSS again. For more information, see Import data from OSS tables to PolarStore.Delete archived cold data
To delete archived cold data, execute the
DROP TABLEstatement to delete the archived table, and then execute theCALL dbms_oss.delete_table_file('database_name', 'table_name');statement to delete the archived cold data.