To reduce data storage costs, improve query performance, and increase system resource utilization, ApsaraDB for ClickHouse Community-compatible Edition and Enterprise Edition clusters that use ESSD_L1 or ESSD_L2 storage support tiered storage of hot and cold data. This feature stores hot and cold data on different storage media based on data access frequency. This topic describes how to enable tiered storage and configure storage policies.
Prerequisites
Community-compatible Edition
The cluster runs version 20.8 or later.
The data backup feature is disabled for the cluster.
If you want to use tiered storage on a cluster that runs an earlier version, migrate the data to a cluster that runs version 20.8 or later and then enable tiered storage. For more information, see Migrate data between ApsaraDB for ClickHouse Community-compatible Edition clusters.
Enterprise Edition
The cluster uses ESSD_L1 or ESSD_L2 storage. To use this storage type, submit a ticket to add the feature to the whitelist.
Background information
Tiered storage of hot and cold data classifies data into two categories based on access frequency:
Hot data: Data that is frequently accessed. It is stored on hot data disks, such as enterprise SSDs (ESSDs) or ultra disks selected when you create the cluster. This meets high-performance access requirements.
Cold data: Data that is infrequently accessed. It is stored on less expensive cold data disks. This meets cost-effective storage requirements.
The storage policies for tiered storage of hot and cold data are as follows:
Community-compatible Edition
Storage policy | Details |
Default storage policy | New data is written to hot data disks to provide efficient queries. When the amount of hot data reaches a specified threshold, the data part that occupies the most space on the hot data disk is automatically moved to a cold data disk. This frees up storage space on the hot data disk. |
TTL storage policy | A Time to Live (TTL) statement is added to the default storage policy. This automatically moves all data created before a specified time interval to a cold data disk. |
Enterprise Edition
Tiered storage is implemented based on a global TTL. Data that no longer changes is automatically dumped to Object Storage Service (OSS), which is less expensive, to reduce costs.
Global TTL: Enterprise Edition controls data tiering using a global TTL.
Dump time: After you enable tiered storage, the system scans all data files at 00:00 UTC every day. Data parts that have existed for longer than the global TTL are dumped from hot disks to cold disks (OSS).
Precautions
Community-compatible Edition
Enable tiered storage during off-peak hours. After you enable this feature, the cluster restarts immediately. During the restart, you can only view cluster information. You cannot upgrade, scale out, scale in, or modify parameter settings.
When you use tiered storage, you must specify a storage policy for each table.
When you create a table, specify the storage policy using
SETTINGS storage_policy = 'hot_to_cold'.Modify the storage policy of an existing table.
For clusters where the tiered storage policy was enabled after January 1, 2024
Execute the following statement to modify the policy.
ALTER TABLE <table_name> MODIFY SETTING storage_policy = 'hot_to_cold';For clusters where the tiered storage policy was enabled before January 1, 2024
Add a new tiered storage policy through O&M operations. This lets you use the new policy to migrate data from a hot disk to a cold disk.
After you enable tiered storage, you cannot disable it.
Enterprise Edition
If data in cold storage changes, it is reloaded to hot storage to rebuild the data files. Changes include `Mutation` or `Optimize` operations that you run, or `Merge` operations that the system triggers based on rules. The new data files are not immediately moved to cold storage. They are dumped again only after they have existed for longer than the global TTL. Therefore, avoid frequent additions, deletions, or modifications to cold data.
The dump operation is not immediate. The system dynamically adjusts the dump speed based on factors such as current access pressure on the storage system. The operation may take several hours to complete, depending on the volume of data to be dumped.
Rules for TTL changes to take effect:
Increase the TTL: The change takes effect at the next dump time. However, data that is already dumped to cold storage is not moved back to hot storage.
Decrease the TTL: The change takes effect at the next dump time.
The minimum value for the global TTL is 1 day. The minimum adjustment step size is 1 day.
Billing
The billing rules are as follows.
Storage type
Billing details
Hot data storage
After you purchase a cluster, you have hot data storage space equal to the storage capacity of the cluster.
Cold data storage
Cold data storage uses the pay-as-you-go billing method. You do not need to purchase a fixed amount of storage space in advance. You are billed hourly for the storage space that you use.
For detailed pricing of cold data storage, see Community Edition bucket pricing and Enterprise Edition pay-as-you-go billing method.
Billing formula: Cold storage fee = Unit price of cold data storage × Cold data storage usage × Usage duration.
For example, if the unit price of cold data storage is USD 0.000026 per GB/hour and you use 2,000 GB of cold storage from 19:00 to 20:00, the total fee is USD 0.000026/GB/hour × 2,000 GB × 1 hour = USD 0.052. If you release 500 GB of cold storage space from 20:00 to 21:00 and use 1,500 GB, the total fee for this hour is USD 0.000026/GB/hour × 1,500 GB × 1 hour = USD 0.039.
The prices in these examples are for reference only. The actual prices in the console prevail.
Enable tiered storage of hot and cold data
Community-compatible Edition
Log on to the ApsaraDB for ClickHouse console. In the upper-left corner of the page, select the region where the cluster is located.
On the Clusters page, click the Clusters of Community-compatible Edition tab, and then click the ID of the destination cluster.
In the navigation pane on the left, click Cold and hot layered storage.
Click Click to Enable Layered Storage.
In the dialog box that appears, click OK.
After you confirm, the cluster restarts. The feature is enabled when the cluster status changes from Changing Configuration to Running.
Enterprise Edition
For ApsaraDB for ClickHouse Enterprise Edition, first submit a ticket to add tiered storage of hot and cold data to the whitelist. Then, perform the following steps to enable the feature for the destination cluster.
Log on to the ApsaraDB for ClickHouse console. In the upper-left corner of the page, select the region where the cluster is located.
On the Clusters page, click the Enterprise Edition Clusters tab, and then click the ID of the destination cluster.
In the navigation pane on the left, click Cold and hot layered storage.
Click Click to Enable Layered Storage.
In the dialog box that appears, configure the global TTL.
Click OK.
The feature is enabled when the cluster status changes to Running and the Tiered Storage page shows Enabled.
After you enable the feature, you can click the edit icon next to the Parameter Value to adjust the global TTL. The new TTL takes effect in the next dump epoch (00:00 UTC on the next day).
Manage tiered storage policies for Community-compatible Edition
Default storage policy for tiered storage of hot and cold data
After you enable tiered storage, the default storage policy has the following parameters.
Parameter | Description |
move_factor | When the percentage of free space on a hot data disk is less than this value, the data part that occupies the most space on the hot data disk is automatically moved to a cold data disk. The value must be in the range of Note To learn how to view disk space, see View disk space. |
prefer_not_to_merge | Specifies whether to merge data on a cold data disk. Valid values:
|
Set a TTL-based tiered storage policy when you create a table
Add a TTL statement to the default storage policy. This automatically moves all data created before a specified time interval to a cold data disk.
Syntax
TTL <time_column> + INTERVAL <number> TO DISK 'cold_disk'Parameter description
Parameter | Description |
time_column | A column of the Date or DateTime data type. |
number | The time interval. Common units are Day, Week, Month, and Year. |
For more information about TTL clauses, see TTL.
Example
Move all data that is more than 90 days old to a cold data disk based on the `date` column. The statement to create the table is as follows.
CREATE TABLE ttl_test_tbl
(
`f1` String,
`f2` String,
`f3` Int64,
`f4` Float64,
`date` Date
)
ENGINE = MergeTree()
PARTITION BY date
ORDER BY f1
TTL date + INTERVAL 90 DAY TO DISK 'cold_disk'
SETTINGS storage_policy = 'hot_to_cold';Modify a TTL-based tiered storage policy
You can change the date or date-type column and the time interval in a TTL-based tiered storage policy.
Precautions
Data impact
Historical data: By default, historical data is stored according to the new policy. This process is asynchronous and takes time. To make the change take effect immediately, execute the
ALTER TABLE materialize TTL;statement after you modify the policy. If you do not want to change the storage policy for historical data, execute theSET materialize_ttl_after_modify=0;statement before you modify the policy.Incremental data: By default, incremental data is also stored according to the new policy.
After you modify the policy, data that is already in a cold data disk is not automatically moved to a hot data disk. To move the data, see Move data between hot and cold disks.
Syntax
ALTER TABLE <table_name> ON CLUSTER default MODIFY TTL <time_column> + INTERVAL <number> TO DISK 'cold_disk';Parameter description
Parameter | Description |
table_name | The table name. |
time_column | The target date or date-type column. |
number | The time interval. Common units are Day, Week, Month, and Year. |
Data management for Community-compatible Edition
Move data between hot and cold disks
Move data from a hot data disk to a cold data disk.
ALTER TABLE <table_name> ON CLUSTER default MOVE PARTITION <partition> TO DISK 'cold_disk';Move data from a cold data disk to a hot data disk.
ALTER TABLE <table_name> ON CLUSTER default MOVE PARTITION <partition> TO DISK 'default';Notepartitionis the partition name. You can find it in the `system.parts` system table.
View data on hot and cold disks
View data on a hot data disk:
SELECT * FROM system.parts WHERE database = '<db_name>' AND TABLE = '<tbl_name>' AND disk_name ='default' AND active = 1;View data on a cold data disk:
SELECT * FROM system.parts WHERE database = '<db_name>' AND TABLE = '<tbl_name>' AND disk_name ='cold_disk' AND active = 1;
View disk space
After you enable tiered storage, execute the following statement to view disk space.
SELECT * FROM system.disks;The following result is returned.
┌─name─────┬─────────path───────────────────────────┬─free_space───────────┬─total_space──────────┬─used_space───┬─keep_free_space───┬──type──┐
│ cold_disk│ /clickhouse/data/data/disks/cold_disk/ │ 18446744073709551615 │ 18446744073709551615 │ 115312080698 │ 0 │ OSS │
│ default │ /clickhouse/data/data/ │ 201663209472 │ 207083249664 │ 5420040192 │ 0 │ local │
└──────────┴────────────────────────────────────────┴──────────────────────┴──────────────────────┴──────────────┴───────────────────┴────────┘The columns in the result set are described as follows.
Parameter | Description |
name | The disk name.
|
path | The data storage path on the disk. |
free_space | The remaining free space on the disk. The free space on a cold data disk is not limited. Unit: byte. |
total_space | The total space on the disk. The free space on a cold data disk is not limited. Unit: byte. |
used_space | The used space on the disk. Unit: byte. |
keep_free_space | The reserved free space. Unit: byte. |
type | The storage medium.
|
FAQ
Q: Why is data not moved from a hot data disk to a cold data disk?
A: Whether data is moved to a cold data disk depends on the values of move_ttl_info.min and move_ttl_info.max in the `system.parts` table. You can use the following statement to retrieve these values.
SELECT move_ttl_info.min, move_ttl_info.max FROM system.parts WHERE database = '<db_name>' AND table = '<tb_name>' AND disk_name = 'default'If the current time is earlier than either of the returned values, that part of the data is not moved to cold storage. It is also possible that the system is in the process of moving the data, but the operation is not yet complete.