Partition-Level Cold and Hot Data Separation

Scenarios

In large-scale data processing scenarios, continuous data volume growth makes efficient cold and hot data lifecycle management a critical challenge. Without proper cold and hot data separation, storage costs increase and query performance degrades. Depending on your environment, you can perform cold and hot data separation using either a shell script or a stored procedure.

If your current kernel version is lower than 2.0.72.251200, stored procedures are not supported for cold and hot data separation. Use shell scripts instead.

If your kernel version is 2.0.72.251200 or later, you are advised to use stored procedures for cold and hot data separation.

Non-partitioned tables
If a data table does not contain partitions, you can configure it as a cold table through the TaurusDB console or using SQL statements. For details, see Using TaurusDB Cold and Hot Data Separation.
Partitioned tables
- Scheduled cold data archiving for partitioned tables using shell scripts
  Each partition is treated as an independent object. This method guides you to perform scheduled cold data archiving on Huawei Cloud ECS using shell scripts. You are advised to use INTERVAL RANGE for automatic partition expansion, combined with automatic cold table configuration, to archive low-frequency partitions to OBS.
- Retaining the latest N partitions and archiving the remaining partitions using a stored procedure
  The latest N partitions of each partitioned table are retained and not archived. This method guides you to connect to your TaurusDB instance through DAS and use a stored procedure to sequentially archive the remaining tables in the database. You are advised to use INTERVAL RANGE for automatic partition expansion, combined with automatic cold table configuration, to archive low-frequency partitions to OBS.
This section provides the best practices for implementing cold and hot data separation for partitioned tables.

Constraints

Using stored procedures to perform cold and hot data separation on an entire database requires a kernel version of 2.0.72.251200 or later. For details about how to check the kernel version, see How Can I Check the Version of a TaurusDB Instance?
Tables to be archived must meet the required constraints. For details, see Archiving a Cold Table.
The following code examples are for reference only. You can test and verify them based on your workload scenario.

Scheduled Cold Data Archiving for Partitioned Tables Using Shell Scripts

Create an ECS.
For details, see Creating an ECS.
- Ensure that the ECS is in the same region, AZ, VPC, and security group as your TaurusDB instance.
- Data disks are not required.
Log in to the ECS and download and install a MySQL client.

For details about how to download and install a MySQL client, see How Can I Install a MySQL Client?
Connect to the TaurusDB instance and check the table structure and archiving status.

The following uses the sales table as an example.

As shown in the following figure, the sales table is not archived as cold data.

Configure automatic cold-table archiving using a shell script.

Create the following script on the ECS. The script schedules archiving for the partitions of the sales table at 01:00 on the first day of each month, starting from the current month. The archiving operation targets all partitions except the first and the last. Every month, the script checks whether new partitions have been created and archives any new partitions (except the last one).

Example script for the sales table:

#!/usr/bin/sh 
passwd=****** 
user="root"
ip=*.*.*.*
conn="mysql -u$user -h$ip -p$passwd" 
database=test 
table=sales 
start_time=$(date "+%Y-%m-01 01:00:00") 
last_time=$start_time 
partition_order=2 
while [ true ] 
  do 
    res=$($conn -se"SELECT TIMEDIFF(current_timestamp(),'$last_time') > 0;") 
    if [ $res -gt 0 ]; then 
      partition_nums=$($conn -se"select count(1) from information_schema.partitions where table_schema=\"$database\" and table_name=\"$table\";") 
      if [ $partition_order -ge $partition_nums ]; then 
        last_time=$($conn -se"SELECT DATE_ADD('$last_time',INTERVAL 1 MONTH);") 
        continue 
      fi 
      partition_name=$($conn -se"select PARTITION_NAME from information_schema.partitions where table_schema=\"$database\" and table_name=\"$table\" and PARTITION_ORDINAL_POSITION = $partition_order;") 
 
 
      $conn -e"CALL dbms_schs.make_io_transfer(\"start\", \"${database}\", \"${table}\", \"${partition_name}\", \"\", \"obs\");" 
      if [ $? -ne 0 ]; then 
        echo "archive failed" 
      fi 
      partition_order=$(($partition_order+1)) 
    else  
      sleep 1d 
      continue 
    fi 
  done

Connect to the TaurusDB instance, execute a stored procedure, and check the archiving status of the table.

The stored procedure sys.schs_show_all requires that the kernel version of your TaurusDB instance be 2.0.60.241200 or later. If the version is older, upgrade it first. For details about how to check the kernel version, see How Can I Check the Version of a TaurusDB Instance?

sys.schs_show_all(database, table, partition) is a built-in stored procedure of TaurusDB. Its three parameters represent the database name, table name, and partition name.
- If the table name and partition name are not specified, all archived tables in the database will be queried.
- If the partition name is not specified, all archived partitions of the specified table will be queried.
Examples:
- Querying all cold tables on an instance
```
CALL sys.schs_show_all( "", "", "");
```
- Querying all cold partitions or cold tables whose database name is test
```
CALL sys.schs_show_all( "test", "", "");
```
- Querying cold partitions or cold tables whose database name is test and table name is table1
```
CALL sys.schs_show_all( "test", "table1", "");
```
The following uses the sales table as an example.
```
call sys.schs_show_all('test', 'sales', '');
```
When the status column shows FINISH, it indicates that the two partitions (excluding the first and last) have been successfully archived.
Verify that the storage usage has decreased. The decrease indicates that the cold table's storage space has been released. (There may be a delay in monitoring data collection.)

Retaining the Latest N Partitions and Archiving the Remaining Partitions Using a Stored Procedure

Log in to the TaurusDB console.
Click in the upper left corner and select a region and project.
On the Instances page, locate the target instance and click Log In in the Operation column.

Alternatively, on the Instances page, click the target instance name. On the displayed Basic Information page, click Log In in the upper right corner. The DAS login page is displayed.
Enter the database username and password and click Test Connection.
After the connection test is successful, click Log In. Then you can access and manage your databases.

Choose SQL Operations > SQL Window and run the following command to create a stored procedure.

Ensure that the database specified in the stored procedure exists. If not, create the database and import data first.

DELIMITER //
CREATE PROCEDURE archive_table_skip_first_and_retain_n(
    -- Database name
    IN dbName VARCHAR(64),
    -- Table name
    IN tableName VARCHAR(64), 
    -- If tableName is a partitioned table, retainPartitionNum specifies the number of latest partitions to retain.
    IN retainPartitionNum INT 
)
BEGIN
    DECLARE partitionName VARCHAR(64);
    DECLARE done INT DEFAULT 0;
    DECLARE totalPartitionCount INT DEFAULT 0;
    DECLARE currentIndex INT DEFAULT 0;

    -- Cursor declaration
    DECLARE cur_partitions CURSOR FOR
        SELECT PARTITION_NAME
        FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_SCHEMA = dbName
          AND TABLE_NAME = tableName
          AND PARTITION_NAME IS NOT NULL
        ORDER BY PARTITION_ORDINAL_POSITION;

    -- Handler for the cursor termination
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- ===================== Unified global handling: case-sensitivity conditions for database and table names =====================
    SET @schema_cond = '';
    SET @table_cond = '';

    -- Case-sensitivity handling for the database name
    IF dbName IS NOT NULL AND dbName <> '' THEN
        IF @@global.lower_case_table_names = 1 THEN
            SET @schema_cond = CONCAT(' AND LOWER(a.schema_name) = \'', LOWER(dbName), '\'');
        ELSE
            SET @schema_cond = CONCAT(' AND a.schema_name = \'', dbName, '\'');
        END IF;
    END IF;

    -- Case-sensitivity handling for the table name
    IF tableName IS NOT NULL AND tableName <> '' THEN
        IF @@global.lower_case_table_names = 1 THEN
            SET @table_cond = CONCAT(' AND LOWER(a.table_name) = \'', LOWER(tableName), '\'');
        ELSE
            SET @table_cond = CONCAT(' AND a.table_name = \'', tableName, '\'');
        END IF;
    END IF;

    -- ===================== Retrieve the total number of partitions. =====================
    SELECT COUNT(DISTINCT PARTITION_NAME) INTO totalPartitionCount
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_SCHEMA = dbName
      AND TABLE_NAME = tableName
      AND PARTITION_NAME IS NOT NULL;

    -- ===================== Non-partitioned table: Check whether it has already been archived. Skip if it is archived. =====================
    IF totalPartitionCount = 0 THEN
        -- Check whether a non-partitioned table has already been archived (partition_name = '')
        SET @check_sql = CONCAT(
            'SELECT COUNT(*) INTO @is_archived FROM mysql.schs_io_transfer a ',
            'INNER JOIN INFORMATION_SCHEMA.INNODB_TABLESPACES c ON a.space_id = c.SPACE AND c.NAME NOT LIKE \'__recyclebin__%\' ',
            'WHERE a.space_id != 4294967295 ',
            'AND a.target_type = \'OBS\' ',
            'AND a.transfer_status = \'FINISH\' ',
            'AND a.partition_name = \'\' ',
            @schema_cond,
            @table_cond
        );

        PREPARE check_stmt FROM @check_sql;
        EXECUTE check_stmt;
        DEALLOCATE PREPARE check_stmt;

        -- Already archived: Skip and do not execute archiving.
        IF @is_archived > 0 THEN
            SET done = 1;
        ELSE
            -- Not archived: Execute archiving.
            SET @sql = CONCAT('call dbms_schs.make_io_transfer(\'start\',\'',
                dbName,'\',\'',tableName,'\',\'\',\'\',\'obs\')');
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SET done = 1;
        END IF;
    END IF;

    -- ===================== Partitioned-table processing =====================
    IF done != 1 THEN
        OPEN cur_partitions;
        partition_loop: LOOP
            FETCH cur_partitions INTO partitionName;
            IF done = 1 THEN LEAVE partition_loop; END IF;

            SET currentIndex = currentIndex + 1;

            -- 1. Skip the first partition.
            IF currentIndex = 1 THEN
                ITERATE partition_loop;
            END IF;

            -- 2. Retain the latest N partitions.
            IF currentIndex > (totalPartitionCount - retainPartitionNum) THEN
                ITERATE partition_loop;
            END IF;

            -- 3. Check whether the partition has already been archived.
            SET @check_sql = CONCAT(
                'SELECT COUNT(*) INTO @is_archived FROM mysql.schs_io_transfer a ',
                'INNER JOIN INFORMATION_SCHEMA.PARTITIONS b ON a.schema_name = b.TABLE_SCHEMA AND a.table_name = b.TABLE_NAME ',
                'AND (a.partition_name = b.PARTITION_NAME OR (a.partition_name = \'\' AND b.PARTITION_NAME IS NULL)) ',
                'INNER JOIN INFORMATION_SCHEMA.INNODB_TABLESPACES c ON a.space_id = c.SPACE AND c.NAME NOT LIKE \'__recyclebin__%\' ',
                'WHERE a.space_id != 4294967295 ',
                'AND a.target_type = \'OBS\' ',
                'AND a.transfer_status = \'FINISH\' ',
                'AND a.partition_name = \'', partitionName, '\' ',
                @schema_cond,
                @table_cond
            );

            PREPARE check_stmt FROM @check_sql;
            EXECUTE check_stmt;
            DEALLOCATE PREPARE check_stmt;

            -- Skip if it has been already archived.
            IF @is_archived > 0 THEN
                ITERATE partition_loop;
            END IF;

            -- Execute archiving.
            SET @sql = CONCAT('call dbms_schs.make_io_transfer(\'start\',\'',
                dbName,'\',\'',tableName,'\',\'',partitionName,'\',\'\',\'obs\')');
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END LOOP partition_loop;
        CLOSE cur_partitions;
    END IF;

    -- Clear session variables.
    SET @schema_cond = NULL;
    SET @table_cond = NULL;
    SET @check_sql = NULL;
    SET @is_archived = NULL;
END //
DELIMITER ;

(Optional) Check the current archiving status of the database test.

The following uses the sales table as an example.

Current archiving status:
Create an event scheduler to periodically call the stored procedure.

Create the following script on DAS. The script defines an event that starts on April 1 and runs at 01:00 on the first day of every month. It archives all partitions except the first partition and the latest two partitions. Each month, the event checks whether new partitions have been created and archives any new partitions (except the latest two).
```
CREATE EVENT IF NOT EXISTS event_archive_sales_monthly
ON SCHEDULE
EVERY 1 MONTH
STARTS '2026-04-01 01:00:00'
DO
CALL archive_table_skip_first_and_retain_n('test', 'sales', 2);
```
(Optional) Check the archiving status of the table.

Insert data to trigger automatic creation of new partitions (_p20220501000000 and _p20220601000000) using INTERVAL RANGE.

After the new partitions (_p20220501000000 and _p20220601000000) are created, the older partitions _p20220301000000 and _p20220401000000 are automatically archived.