Cold data archiving (TTL)

Technical architecture

To reduce the storage costs of continuously accumulating historical data, PolarDB-X 2.0 Enterprise Edition provides the cold data archiving feature. Unlike the default method of storing data on local disks, the cold data archiving feature divides data into hot and cold data based on time. It separates expired cold data from the source table and archives it to the more cost-effective Object Storage Service (OSS). Applications can also query the archived data.

• Cold data is stored in columnstore format on remote OSS. Hot data remains in row-store format on local disks.

• After cold data archiving is complete for an online table, a row-oriented table and an archived table are created:

  • Row-oriented table: The original business table, which retains only recent hot data. The data is stored on local disks.

  • Archived table: The archived columnstore index of the original business table. It stores all expired cold data. The data is stored in OSS.

• After an online table is archived, you can query the archived table directly on both the primary instance and the column store read-only instance. However, the names of the archived table and the row-oriented table are handled differently on the primary instance and the column store read-only instance:

  • Primary instance: The archived table and the row-oriented table have different names. The archived table name is the name of a query view. This is because the query performance of the archived table is usually lower than that of the row-oriented table. Their service-level agreements (SLAs) for queries, such as concurrency and response time (RT), are different. Therefore, your application must explicitly specify whether to query the archived table or the row-oriented table.

  • Column store read-only instance: The archived table and the row-oriented table have the same name. You can access the archived table in the same way as the row-oriented table. Applications can use the original business table name to query cold data directly. In a column store read-only instance, the query performance for the archived table is typically better than on the primary instance, which makes it more suitable for complex analytical queries. Therefore, you should use a column store read-only instance to query archived data frequently.

Cold data compression ratio

Archived tables organize data by column and store it in OSS. Each column in an archived table is compressed. For the same data volume, the storage space required for column-oriented data in an archived table is typically only about 5% to 10% of the original row-oriented storage.

Cold data storage costs

Storage space for cold data is billed separately and supports only the pay-as-you-go billing method. The pricing is as follows:

Features of columnstore archiving

The In-Memory Column Index (IMCI) feature provided by PolarDB-X 2.0 Enterprise Edition synchronizes data in real time from row-oriented InnoDB storage to the IMCI in OSS storage. It provides applications with low-cost, high-compression, real-time, and fully MySQL-compatible integrated and transparent hybrid transactional and analytical processing (HTAP) capabilities.

By organizing and storing data by column in OSS, the columnstore index has the following three main features:

• High compression ratio: A columnstore index organizes and stores data by table column. It automatically selects the optimal data compression algorithm for each column's data type. This results in a very high overall data compression ratio.

• Low storage cost: Object Storage Service (OSS) has low storage costs and is highly cost-effective.

• High real-time performance and strong consistency: A columnstore index subscribes to incremental binary logs to maintain real-time synchronization with the primary table. The columnstore index duplicates all columns of the primary table and can be considered a read-only replica or image of the primary table.

The cold data archiving solution based on columnstore indexes provides an integrated solution for various application needs for cold data archiving and inherits several excellent attributes of columnstore indexes. This gives the cold data archiving solution the following distinct advantages:

• Cold data is archived to OSS by subscribing to binary logs. The data archiving process does not affect online services. For example, it does not cause lock contention or consume I/O and CPU resources.

• Archived cold data has high real-time performance and strong consistency. It supports lightweight analytical processing (AP) queries through a column store read-only instance.

• Archived cold data has a high compression ratio. The compressed size is about 1/10 to 1/20 of the original size, which results in low storage costs.

Overall cold data archiving solution

Cold data archiving consists of two main steps:

1. Transfer of cold data: All cleared cold data is migrated to a storage medium with lower costs and a higher compression ratio to achieve data archiving.

2. Cleanup of cold data: Expired cold data in business tables is automatically or manually cleared, leaving only the recent hot data defined by the application.

Unlike other cloud database products that use a conventional "archive while cleaning" solution, PolarDB-X 2.0 Enterprise Edition uses an "early archiving and scheduled cleanup" solution.

• Early archiving:

  You can create a corresponding columnstore index for an online table and use it as a dedicated archived table for storing cold data. When you create a columnstore index, the system uploads all historical data from the online table to the OSS storage of the archived table. It also uses the real-time subscription capability of the columnstore index to subscribe to binary log changes from the online table. This allows all incremental writes to the online table to be synchronized in real time to the OSS storage of the archived table. Therefore, in this phase, all data in the online table, including historical and incremental data, is uploaded to OSS storage, regardless of whether the data has reached the archiving time point. This achieves the "early archiving" effect. When the archived table is created, the first goal of cold data archiving, which is the transfer of cold data, is complete.

  

• Scheduled cleanup:

  After all data in the business table is archived, you can trigger the predefined TTL task for the business table to clear expired data, either automatically or manually. Internally, this TTL task uses DELETE DML statements for row-based archiving or DROP PARTITION DDL statements for partition-based archiving to clear the expired data. When this TTL task clears data, it generates binary logs with special markers. The column store node ignores these binary logs. Therefore, the DELETE operations for row-based archiving or the DROP PARTITION operations for partition-based archiving that are generated by the TTL task on the online table do not affect the data in the archived table. This allows the TTL task to clear expired data only from the online table without clearing data from the archived table. This completes the second goal of cold data archiving: the cleanup of cold data.

  

• Archiving strategies:

  TTL tables support the following two archiving strategies, which differ in the method used to clear expired data:

  • Row-based archiving: The TTL table uses DELETE DML statements to clear expired data. All cleared expired data is archived to OSS in advance through the columnstore index, as shown in the following figure:

    

  • Partition-based archiving: The TTL table uses DROP (SUB)PARTITION DDL statements to clear expired data. The data in all cleared expired partitions is archived to OSS in advance through the columnstore index, as shown in the following figure:

    
    Note

    The principles of row-based archiving and partition-based archiving are the same. Both are based on using a columnstore index to archive expired data to OSS in advance. The core difference is the method that the TTL table uses to clear expired data.

Cold data archiving procedure

Creating an archived table for a TTL table is the process of creating a columnstore index. During this process, the column store node uploads the historical data of the TTL table to the OSS storage of the archived table based on a snapshot read. It also subscribes to the TTL table's binary logs in real time. This allows it to perform row-to-column conversion for incremental data and upload incremental updates to the OSS storage of the archived table in real time. The following figure shows the procedure:

Row-based cleanup

By default, an algorithm that gradually cleans up data from oldest to newest is used. The cleanup process always starts by clearing the oldest data in the table. After multiple rounds of cleanup, the most recent data is dynamically retained.

Because row-based cleanup uses DML statements and distributed transactions, it generates row locks and binary logs. To avoid creating large-range row locks or consuming excessive CPU and I/O resources during data cleanup, the daily scheduled TTL task automatically calculates a small, appropriate time range for cleanup based on the minimum value of the TTL column.

SET CDC GLOBAL TASK_EXTRACT_FILTER_ARCHIVE_ENABLED = TRUE;
STOP MASTER;
START MASTER;

// Target upper bound of the cleanup time range
CleanupMaxUpperBound = Now - ExpiredDataInterval

// Upper bound of the current cleanup time range
CleanupUpperBound = MIN((MinValue + CleanupDataInterval), (Now - ExpiredDataInterval))

CREATE TABLE `tbl` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`time` datetime DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`id`)
PARTITIONS 8;

ALTER TABLE `tbl`
MODIFY TTL
SET
TTL_EXPR = `time` EXPIRE AFTER 1 MONTH TIMEZONE '+08:00';

Partition-based cleanup

For partition-based archiving scenarios, different cleanup logic is used based on the partition expiration policy:

• Expiration by fixed time interval: The TTL task calculates the target time range for the current cleanup based on the data expiration interval set in the TTL definition and the current time. It then scans all RANGE partitions. If a partition's data falls completely within the target time range, the partition is considered expired and is deleted.

• Expiration by fixed number of partitions: The TTL task determines which partitions are expired based on the number of RANGE partitions to retain, as set in the TTL definition. It starts from the smallest RANGE partition and checks each one until the number of remaining partitions equals the number specified in the TTL definition.