Database-Level Cold and Hot Data Separation

Scenarios

In enterprise data management, as business volume grows rapidly, the amount of data stored in databases increases sharply. This leads to rising storage costs and declining query performance. To address this issue, inactive data is typically archived to reduce storage costs and improve query efficiency. However, performing cold and hot data separation manually is complex and error-prone. Automating the process through stored procedures can significantly reduce manual operations and improve the efficiency and accuracy of data management. When performing cold and hot data separation at the database level, TaurusDB allows you to execute the separation through stored procedures. Depending on whether incremental data exists, there are two methods:

Archiving tables as cold data sequentially using a stored procedure
Use this method when the entire database no longer receives incremental data. This method guides you to connect to your TaurusDB instance through DAS and use a stored procedure to sequentially archive all tables (for partitioned tables, each partition is treated as a table) in the database as cold data. To avoid excessively long archiving time, it is recommended that the database contain no more than 50 tables.
Retaining the latest N partitions of partitioned tables in a specific database and archiving all other tables using a stored procedure
Use this method when partitioned tables ingest incremental data. 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 execute a stored procedure to sequentially archive all remaining tables (for partitioned tables, each partition is treated as a table) as cold data. You are advised to use INTERVAL RANGE for automatic partition expansion, combined with automatic cold table configuration, to archive low-frequency partitions to OBS.

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.

Archiving Each Table as Cold Data 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_non_first_partition(
    IN dbName VARCHAR(64)  -- Input parameter: database name
)
BEGIN
    -- ===================== All DECLARE statements must be placed at the beginning. =====================
    -- 1. Declare variables.
    DECLARE tableName VARCHAR(64);       -- Table name
    DECLARE partitionName VARCHAR(64);   -- Partition name
    DECLARE done INT DEFAULT 0;           -- Flag indicating the end of the cursor loop
    DECLARE partitionCount INT DEFAULT 0;-- Total number of partitions in a single table
    DECLARE partitionIndex INT DEFAULT 0;-- Partition traversal index (used to skip the first partition)
    -- 2. Declare all cursors (declare them together to prevent syntax errors).
    -- Cursor 1: Retrieve all partitioned tables in a specified database.
    DECLARE cur_partition_tables CURSOR FOR
        SELECT DISTINCT t.TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES t
        INNER JOIN INFORMATION_SCHEMA.PARTITIONS p
            ON t.TABLE_SCHEMA = p.TABLE_SCHEMA
            AND t.TABLE_NAME = p.TABLE_NAME
        WHERE t.TABLE_SCHEMA = dbName
          AND t.TABLE_TYPE = 'BASE TABLE'
          AND p.PARTITION_NAME IS NOT NULL  -- Select partitioned tables.
        ORDER BY t.TABLE_NAME;
    -- Cursor 2: Retrieve all partitions of a single partitioned table (sorted by creation sequence).
    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;
    -- Cursor 3: Retrieve all non-partitioned tables in a specified database.
    DECLARE cur_non_partition_tables CURSOR FOR
        SELECT t.TABLE_NAME
        FROM information_schema.TABLES t
        WHERE t.TABLE_SCHEMA = dbName
          AND t.TABLE_TYPE = 'BASE TABLE'
          AND NOT EXISTS (  -- Exclude partitioned tables.
              SELECT 1
              FROM information_schema.PARTITIONS p
              WHERE p.TABLE_SCHEMA = t.TABLE_SCHEMA
                AND p.TABLE_NAME = t.TABLE_NAME
                AND p.PARTITION_NAME IS NOT NULL
          );
    -- 3. Declare a handler for the cursor termination (only once).
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    -- ===================== Part 1: Process partitioned tables (excluding the first partition). =====================
    -- Step 1: Traverse all partitioned tables.
    OPEN cur_partition_tables;
    partition_table_loop: LOOP
        FETCH cur_partition_tables INTO tableName;
        IF done = 1 THEN
            LEAVE partition_table_loop;
        END IF;
        -- Reset variables and count total partitions in for the current table.
        SET done = 0;
        SET partitionIndex = 0;
        SELECT COUNT(DISTINCT PARTITION_NAME) INTO partitionCount
        FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_SCHEMA = dbName
          AND TABLE_NAME = tableName
          AND PARTITION_NAME IS NOT NULL;
        -- Skip tables with only one partition (no non-first partitions to process).
        IF partitionCount <= 1 THEN
            ITERATE partition_table_loop;
        END IF;
        -- Step 2: Traverse all partitions of the current partitioned table.
        OPEN cur_partitions;
        partition_loop: LOOP
            FETCH cur_partitions INTO partitionName;
            IF done = 1 THEN
                LEAVE partition_loop;
            END IF;
            -- Skip the first partition.
            SET partitionIndex = partitionIndex + 1;
            IF partitionIndex = 1 THEN
                ITERATE partition_loop;
            END IF;
            -- Concatenate and execute SQL statements for archiving partitions.
            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 LOOP partition_table_loop;
    CLOSE cur_partition_tables;
    -- ===================== Part 2: Process non-partitioned tables. =====================
    -- Reset the termination flag (to avoid inheriting the previous cursor status).
    SET done = 0;
    -- Step 1: Traverse all non-partitioned tables.
    OPEN cur_non_partition_tables;
    non_partition_table_loop: LOOP
        FETCH cur_non_partition_tables INTO tableName;
        IF done = 1 THEN
            LEAVE non_partition_table_loop;
        END IF;
        -- Concatenate and execute SQL statements for archiving non-partitioned tables (the parameter sequence is the same as that of partitioned tables).
        SET @sql = CONCAT(
            'call dbms_schs.make_io_transfer(\'start\', \'',
            dbName, '\', \'', tableName, '\', \'\', \'\', \'obs\')'
        );
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP non_partition_table_loop;
    CLOSE cur_non_partition_tables;
END //
DELIMITER ;

(Optional) Run the following SQL statement to check the types of tables in the current database.

Use the test database as an example. It has three partitioned tables (t1_p, t2_p, and order_info) and three non-partitioned tables (t1, t2, and t3).
```
SELECT TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME from information_schema.PARTITIONS where TABLE_SCHEMA = 'test';
```
Execute the stored procedure to archive all tables in the specified database. The results are only returned after all tables are successfully archived. The entire process may take a long time.
```
call archive_table_non_first_partition('test');
```
Run a stored procedure to query cold data archiving details.

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 only 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", "");
```
TaurusDB's cold and hot data separation does not support archiving the first partition of partitioned tables, so the first partition p0 of table t1_p, the first (and only) partition p0 of table t2_p, and the first partition p_Beijing of table order_info are not archived.

The following uses the test database as an example:
```
call sys.schs_show_all('test', '', '');
```
If the status column displays FINISH, the partitioned tables have been 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 of Partitioned Tables in a Specific Database and Archiving All Other Tables 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 //
-- Define a single-table stored procedure. Skip creation if it already exists.
CREATE PROCEDURE archive_table_skip_first_and_retain_n(
    IN dbName VARCHAR(64),
    IN tableName VARCHAR(64),
    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 //

CREATE PROCEDURE archive_database_skip_first_and_retain_n(
    IN dbName VARCHAR(64), -- Name of the database to archive
    IN retainPartitionNum INT -- Number of the N latest partitions to retain per table
)
BEGIN
    DECLARE tableName VARCHAR(64);
    DECLARE done INT DEFAULT 0;
    -- Cursor: Traverses all business tables (partitioned + non-partitioned).
    DECLARE cur_all_tables CURSOR FOR
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = dbName
          AND TABLE_TYPE = 'BASE TABLE' -- Business tables only
        ORDER BY TABLE_NAME;

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

    -- Open the cursor and traverse all tables.
    OPEN cur_all_tables;
    table_loop: LOOP
        FETCH cur_all_tables INTO tableName;
        IF done = 1 THEN
            LEAVE table_loop;
        END IF;

        -- ===================== Core logic: Reuse the single-table stored procedure. =====================
        -- Automatically perform the actions for each table: Skip the first partition, retain N partitions, and avoid duplicate archiving.
        CALL archive_table_skip_first_and_retain_n(
            dbName,
            tableName,
            retainPartitionNum
        );
    END LOOP table_loop;
    CLOSE cur_all_tables;

    -- Clear variables.
    SET done = 0;
END //
DELIMITER ;

(Optional) Check the archiving status of tables in the database.

Using the test database as an example, which contains partitioned tables sales, t1_p, and t2_p.

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

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 the tables in the test database, except the first partition and the latest two partitions. Each month, the event checks whether new partitions have been created and archives any partitions older than the latest two.
```
CREATE EVENT IF NOT EXISTS event_archive_test_monthly
ON SCHEDULE
EVERY 1 MONTH
STARTS '2026-04-01 01:00:00'
DO
CALL archive_database_skip_first_and_retain_n('test', 2);
```
(Optional) Check the archiving status of the table.

Insert data to trigger automatic creation of new partitions using INTERVAL RANGE.

After the new partitions _p20220601000000 and _p20220701000000 are created, the older partitions _p20220401000000 and _p20220501000000 are automatically archived.