Use the DLM feature - PolarDB - Alibaba Cloud Documentation Center

The data lifecycle management (DLM) feature can periodically transfer cold data from PolarFileSystem to Object Storage Service (OSS) to reduce storage costs and improve storage efficiency.

Prerequisites

Your cluster runs one of the following versions:
- PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.32 or later.
- PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.9 or later.
For information about how to query the version number of a cluster, see the "Query the engine version" section of the Engine versions topic.
Note
If your cluster runs PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.11.1 or later, DLM-related changes to the database are not recorded in binary logs.
To use DLM policies, you must first enable the cold data archiving feature. For more information, see Enable cold data archiving.
Note
If the cold data archiving feature is not enabled when you use DLM policies, the following error is reported:
```
ERROR 8158 (HY000): [Data Lifecycle Management] DLM storage engine is not support. The value of polar_dlm_storage_mode is OFF.
```

Limits

The DLM feature is supported only on partitioned tables that do not contain subpartitions. Partitioned tables must be of the RANGE COLUMN type.
You cannot use the DLM feature on a partitioned table on which GSIs are created.
You cannot directly modify a DLM policy for a PolarDB for MySQL cluster. To modify a DLM policy, you must delete the policy and then create a new policy.
After you create a DLM policy on a table, cold data is archived based on the policy. If DDL operations such as adding or removing columns and modifying data types cause inconsistent schemas between the original table and the archived table, the archived data cannot be parsed. Before you perform such DDL operations, you must delete the DLM policy on the current table. To use the DLM feature later, you can create another DLM policy and specify a new name for the archived table. The new archived table name cannot be the same as the previous archived table name.
We recommend that you use INTERVAL RANGE partitioning to automatically partition the table and use the DLM feature to archive infrequently accessed partition data to OSS.
Note
INTERVAL RANGE partitioning is supported only for a cluster that runs PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.0 or later.

To use the DLM feature, configure the feature when you execute the CREATE TABLE or ALTER TABLE statement.
DLM policies are not displayed in the output of the SHOW CREATE TABLE statement. The mysql.dlm_policies table records all available DLM policies on the current cluster. For information about the mysql.dlm_policies table, see the "mysql.dlm_policies table" section of the Table formats topic.

Precautions

After cold data is archived, the archived table in OSS is read-only and provides poor query performance. You must check whether the archived table meets query performance requirements in advance.
After a partition in a partitioned table is archived to OSS, data in the partition becomes read-only. SQL statements cannot be executed on the partitioned table.
When you perform a backup operation, data that is transferred to OSS is not backed up. When you restore data to a previous point of time, data stored in OSS is not included in the restoration process.

Syntax

Create a DLM policy

Create a DLM policy when you execute the CREATE TABLE statement

CREATE TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]
    [dlm_add_options]

dlm_add_options:
    DLM ADD
        [(dlm_policy_definition [, dlm_policy_definition] ...)]

dlm_policy_definition:
    POLICY policy_name
    [TIER TO TABLE/TIER TO PARTITION/TIER TO NONE]
    [ENGINE [=] engine_name]
    [STORAGE SCHEMA_NAME [=] storage_schema_name]
    [STORAGE TABLE_NAME [=] storage_table_name]
    [STORAGE [=] OSS]
    [READ ONLY]
    [COMMENT 'comment_string']
    ON [(PARTITIONS OVER num)]

Create a DLM policy when you execute the ALTER TABLE statement

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options]
    [dlm_add_options]

dlm_add_options:
    DLM ADD
        [(dlm_policy_definition [, dlm_policy_definition] ...)]

dlm_policy_definition:
    POLICY policy_name
    [TIER TO TABLE/TIER TO PARTITION/TIER TO NONE]
    [ENGINE [=] engine_name]
    [STORAGE SCHEMA_NAME [=] storage_schema_name]
    [STORAGE TABLE_NAME [=] storage_table_name]
    [STORAGE [=] OSS]
    [READ ONLY]
    [COMMENT 'comment_string']
    ON [(PARTITIONS OVER num)]

DLM policy parameters

Parameter	Required	Description
tbl_name	Yes	The name of the table.
policy_name	Yes	The name of the DLM policy.
TIER TO TABLE	Yes	Archives cold data to the table.
TIER TO PARTITION	Yes	Archives the partition to OSS. Note This parameter is in the canary release phase. To enable this parameter, go to Quota Center. On the Privileges page, find the quota whose ID is `polardb_mysql_hybrid_partition` and click Apply in the Actions column. You can archive the partitions of a partitioned table to OSS only when your cluster runs PolarDB for MySQL 8.0.2 Enterprise Edition whose revision version is 8.0.2.2.17 or later. If you enable this parameter, make sure that the total number of partitions in the partitioned table does not exceed 8,192.
TIER TO NONE	Yes	Deletes the data to be archived.
engine_name	No	The storage engine to which the data is archived. Data can be archived only to the CSV storage engine.
storage_schema_name	No	The database that contains the table to be archived. By default, the database for the current table is selected.
storage_table_name	No	The name of the table to be archived. You can specify a table name. By default, the table name is in the following format: `<Current table name>_<Current DLM policy name>`.
STORAGE [=] OSS	No	Specifies that the archived data is stored in OSS (default).
READ ONLY	No	Specifies that the archived data is read-only (default).
comment_string	No	The comment of the DLM policy.
ON (PARTITIONS OVER num)	Yes	Archives data when the number of partitions is greater than the specified num value.

Manage a DLM policy

Enable a DLM policy.

ALTER TABLE table_name DLM ENABLE POLICY [(dlm_policy_name [, dlm_policy_name] ...)]

Disable a DLM policy.

ALTER TABLE table_name DLM DISABLE POLICY [(dlm_policy_name [, dlm_policy_name] ...)]

Delete a DLM policy.

ALTER TABLE table_name DLM DROP POLICY [(dlm_policy_name [, dlm_policy_name] ...)]

In the preceding statements, the table_name field specifies the name of the current table, and the dlm_policy_name field specifies the name of the policy to be modified. You can specify multiple policy names.

Execute a DLM policy

Execute the DLM policies on all tables in the current cluster.
```
CALL dbms_dlm.execute_all_dlm_policies();
```
Execute the DLM policies on a single table.
```
CALL dbms_dlm.execute_table_dlm_policies('database_name', 'table_name');
```
In the preceding statements, the database_name field specifies the name of the database for the current table, and the table_name field specifies the name of the current table.

You can use the MySQL event feature to execute DLM policies during cluster O&M. This prevents the performance of your database from being affected during peak hours. You can also periodically transfer expired data to reduce storage costs. Syntax to execute DLM policies by creating an event:

CREATE
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [COMMENT 'comment']
    DO event_body;

schedule: {
  EVERY interval
  [STARTS timestamp [+ INTERVAL interval] ...]
}

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

event_body: {
      CALL dbms_dlm.execute_all_dlm_policies();
    | CALL dbms_dlm.execute_table_dlm_policies('database_name', 'table_name');
}

The following table describes the parameters in the sample code.

Parameter	Required	Description
event_name	Yes	The name of the current event.
schedule	Yes	The execution time and cycle of the current event.
comment	No	The comment of the current event.
event_body	Yes	The content of the current event. The content must be the statement that executes the DLM policy by using the event. Note After the `CALL dbms_dlm.execute_all_dlm_policies()` statement is executed, the current event executes all DLM policies on the cluster. Therefore, you must create one event for each cluster. After the `CALL dbms_dlm.execute_table_dlm_policies('database_name', 'table_name');` statement is executed, the current event executes all DLM policies only on a table. Therefore, you must create one event for which DLM policies are defined to archive data in the table at a specified time.
interval	Yes	The execution cycle of the event.
timestamp	Yes	The time when event execution starts.
database_name	Yes	The name of the database.
table_name	Yes	The name of the table.

For information about the MySQL event feature, see MySQL event documentation.

For information about sample statements, see Archive cold data to OSS tables.

Examples

Archive cold data to OSS foreign tables

Create a DLM policy

In the following example, a partitioned table named sales is created. The table uses the order_time column as the partition key and adopts interval partitioning. An interval policy and a DLM policy are also defined for the table.

Interval policy: When data falls out of the partitioning interval of one year, a new partition is automatically created.
DLM policy: When the number of partitions in the table exceeds 3, the DLM policy is executed.
- If the OSS foreign table named sales_history does not exist, create the sales_history table and transfer cold data to the sales_history table.
- If the OSS foreign table named sales_history exists, transfer cold data to the sales_history table.

Note

To create an INTERVAL RANGE partitioned table, specific requirements must be met. For more information, see Interval range partitioning.

Create a table named sales with a DLM policy defined.

CREATE TABLE `sales` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE  COLUMNS(order_time) INTERVAL(YEAR, 1)
(PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
DLM ADD POLICY test_policy TIER TO TABLE ENGINE=CSV STORAGE=OSS READ ONLY
STORAGE TABLE_NAME = 'sales_history' ON (PARTITIONS OVER 3);

The DLM policy is named test_policy. When the number of partitions in the table exceeds 3, cold data is archived from the current table to OSS in the CSV format. The archived table is named sales_history and is read-only.

The DLM policies of the current table are stored in the mysql.dlm_policies system table. You can view the details of the DLM policies in the system table. For information about the mysql.dlm_policies system table, see Table formats. Execute the following DDL policy to view the mysql.dlm_policies system table:

SELECT * FROM mysql.dlm_policies\G
*************************** 1. row ***************************
                   Id: 1
         Table_schema: test
           Table_name: sales
          Policy_name: test_policy
          Policy_type: TABLE
         Archive_type: PARTITION COUNT
         Storage_mode: READ ONLY
       Storage_engine: CSV
        Storage_media: OSS
  Storage_schema_name: test
   Storage_table_name: sales_history
      Data_compressed: OFF
 Compressed_algorithm: NULL
              Enabled: ENABLED
      Priority_number: 10300
Tier_partition_number: 3
       Tier_condition: NULL
           Extra_info: null
              Comment: NULL
1 row in set (0.00 sec)

The sales table contains three partitions. No data is archived.

Insert 3,000 rows of test data into the sales table to trigger the automatic creation of interval partitions.

DROP PROCEDURE IF EXISTS proc_batch_insert;
delimiter $$
CREATE PROCEDURE proc_batch_insert(IN begin INT, IN end INT, IN name VARCHAR(20))
BEGIN
SET @insert_stmt = concat('INSERT INTO ', name, ' VALUES(? , ?, ?);');
PREPARE stmt from @insert_stmt;
WHILE begin <= end DO
SET @ID1 = begin;
SET @NAME = CONCAT(begin+begin*281313, '@stiven');
SET @TIME = from_days(begin + 737600);
EXECUTE stmt using @ID1, @NAME, @TIME;
SET begin = begin + 1;
END WHILE;
END;
$$
delimiter ;
CALL proc_batch_insert(1, 3000, 'sales');

The automatic creation of interval partitions is triggered. The sales table contains more than three partitions. The new table has the following schema:

SHOW CREATE TABLE sales\G
*************************** 1. row ***************************
       Table: sales
Create Table: CREATE TABLE `sales` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
/*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.03 sec)

The table contains more than three partitions. Data can be archived because the execution requirement for the DLM policy is met.

Execute the DLM policy

You can execute SQL statements to directly execute DLM policies or use the MySQL event feature to periodically execute DLM policies. If the O&M start time of your cluster is 1:00 every day starting October 11, 2022, the DLM policy is executed at 1:00 every day. You can create the following event:
```
CREATE EVENT dlm_system_base_event
       ON SCHEDULE EVERY 1 DAY
    STARTS '2022-10-11 01:00:00'
    do CALL 
dbms_dlm.execute_all_dlm_policies();
```
At 1:00 every day, the event begins to execute DLM policies on all tables.

After the DML policy is executed, the sales table has the following schema:

SHOW CREATE TABLE sales\G
*************************** 1. row ***************************
       Table: sales
Create Table: CREATE TABLE `sales` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ INTERVAL(YEAR, 1)
/*!50500 (PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.01 sec)

The current table contains only three partitions.

You can view the execution records of DLM policies in the mysql.dlm_progress table. For more information about the mysql.dlm_progress table, see Table formats. View the mysql.dlm_progress table. Execution records of the current DLM policy:

SELECT * FROM mysql.dlm_progress\G
*************************** 1. row ***************************
                  Id: 4
        Table_schema: test
          Table_name: sales
         Policy_name: test_policy
         Policy_type: TABLE
      Archive_option: PARTITIONS OVER 3
      Storage_engine: CSV
       Storage_media: OSS
     Data_compressed: OFF
Compressed_algorithm: NULL
  Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
       Archive_stage: ARCHIVE_COMPLETE
  Archive_percentage: 100
  Archived_file_info: [{"origin": "p20200101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256316304018.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}, {"origin": "p20210101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256316532347.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}, {"origin": "p20220101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256316789675.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}, {"origin": "_p20230101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256317088201.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}, {"origin": "_p20240101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256317332186.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}, {"origin": "_p20250101000000", "archived_file_name": "default_oss_server/test/sales_history-1673256317596882.CSV", "archived_file_stage": "ARCHIVE_COMPLETE"}]
          Start_time: 2023-01-09 17:25:16
            End_time: 2023-01-09 17:25:18
          Extra_info: null
1 row in set (0.03 sec)

The cold data originally stored in the p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, and _p20250101000000 partitions is transferred to OSS foreign tables.

View the schema of an OSS foreign table.

SHOW CREATE TABLE sales_history\G
*************************** 1. row ***************************
       Table: sales_history
Create Table: CREATE TABLE `sales_history` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ CONNECTION='default_oss_server/sales_history'
1 row in set (0.00 sec)

The table is a CSV-formatted table whose data is stored in OSS.

Query data in the sales and sales_history tables.

SELECT COUNT(*) FROM sales;
+----------+
| count(*) |
+----------+
|      984 |
+----------+
1 row in set (0.01 sec)

SELECT COUNT(*) FROM sales_history;
+----------+
| count(*) |
+----------+
|     2016 |
+----------+
1 row in set (0.57 sec)

The preceding results show that the total number of data rows is exactly 3,000, which is consistent with the number of data rows originally inserted into the sales table.

Archive partitions in a partitioned table to OSS

Create a DLM policy

Interval policy: When data falls out of the partitioning interval of one year, a new partition is automatically created.
DLM policy: When the table contains more than three partitions, the DLM policy is executed to transfer previous partitions to OSS.

Create a table named sales.

CREATE TABLE `sales` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE  COLUMNS(order_time) INTERVAL(YEAR, 1)
(PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
DLM ADD POLICY policy_part2part TIER TO PARTITION ENGINE=CSV STORAGE=OSS READ ONLY ON (PARTITIONS OVER 3);

The DLM policy of the table is named policy_part2part. When the table contains more than three partitions, previous partitions are transferred to OSS.

View DLM policies in the mysql.dlm_policies table.

SELECT * FROM mysql.dlm_policies\G

The following output is returned:

*************************** 1. row ***************************
                   Id: 2
         Table_schema: test
           Table_name: sales
          Policy_name: policy_part2part
          Policy_type: PARTITION
         Archive_type: PARTITION COUNT
         Storage_mode: READ ONLY
       Storage_engine: CSV
        Storage_media: OSS
  Storage_schema_name: NULL
   Storage_table_name: NULL
      Data_compressed: OFF
 Compressed_algorithm: NULL
              Enabled: ENABLED
      Priority_number: 10300
Tier_partition_number: 3
       Tier_condition: NULL
           Extra_info: null
              Comment: NULL
1 row in set (0.03 sec)

Use the proc_batch_insert stored procedure to insert test data into the sales table to trigger the automatic creation of interval partitions.
```
CALL proc_batch_insert(1, 3000, 'sales');
```
The following results indicate that the data is inserted:
```
Query OK, 1 row affected, 1 warning (0.99 sec)
```

Execute the following statement to view the schema of the sales table:

SHOW CREATE TABLE sales \G

The table has the following schema:

*************************** 1. row ***************************
       Table: sales
Create Table: CREATE TABLE `sales` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
/*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.03 sec)

Execute the DLM policy

Execute the following statement to execute the DLM policy:
```
CALL dbms_dlm.execute_all_dlm_policies();
```

View the execution records of the DLM policy in the mysql.dlm_progress table.

SELECT * FROM mysql.dlm_progress \G

The following output is returned:

*************************** 1. row ***************************
                  Id: 4
        Table_schema: test
          Table_name: sales
         Policy_name: policy_part2part
         Policy_type: PARTITION
      Archive_option: PARTITIONS OVER 3
      Storage_engine: CSV
       Storage_media: OSS
     Data_compressed: OFF
Compressed_algorithm: NULL
  Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
       Archive_stage: ARCHIVE_COMPLETE
  Archive_percentage: 100
  Archived_file_info: null
          Start_time: 2023-09-11 18:04:39
            End_time: 2023-09-11 18:04:40
          Extra_info: null
1 row in set (0.02 sec)

Execute the following statement to view the schema of the sales table:

SHOW CREATE TABLE sales \G

The following output is returned:

*************************** 1. row ***************************
       Table: sales
Create Table: CREATE TABLE `sales` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CONNECTION='default_oss_server'
/*!99990 800020205 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
/*!99990 800020205 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = CSV,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = CSV,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = CSV,
 PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = CSV,
 PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = CSV,
 PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = CSV,
 PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.03 sec)

The preceding results show that the p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, and _p20250101000000 partitions in the sales table are transferred to OSS. Only the _p20260101000000, _p20270101000000, and _p20280101000000 partitions that store hot data in the table are retained in InnoDB. The sales table becomes a hybrid partitioned table. For information about how to query data in a hybrid partitioned table, see the "Query data in a hybrid partition table" section of the Create a hybrid partitioned table topic.

Delete cold data

Create a DLM policy

Interval policy: When data falls out of the partitioning interval of one year, a new partition is automatically created.
DLM policy: When the number of partitions in the table exceeds 3, the DLM policy is executed to delete cold data.

Create a table named sales with a DLM policy defined.

CREATE TABLE `sales` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE  COLUMNS(order_time) INTERVAL(YEAR, 1)
(PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
DLM ADD POLICY test_policy TIER TO NONE ON (PARTITIONS OVER 3);

The DLM policy of the table is named test_policy. The policy is executed when the table contains more than three partitions. Cold data is deleted when the DLM policy is executed.

View the mysql.dlm_policies table. Details of the current DLM policy:

SELECT * FROM mysql.dlm_policies\G
*************************** 1. row ***************************
                   Id: 4
         Table_schema: test
           Table_name: sales
          Policy_name: test_policy
          Policy_type: NONE
         Archive_type: PARTITION COUNT
         Storage_mode: NULL
       Storage_engine: NULL
        Storage_media: NULL
  Storage_schema_name: NULL
   Storage_table_name: NULL
      Data_compressed: OFF
 Compressed_algorithm: NULL
              Enabled: ENABLED
      Priority_number: 50000
Tier_partition_number: 3
       Tier_condition: NULL
           Extra_info: null
              Comment: NULL
1 row in set (0.01 sec)

Insert test data into the sales table to trigger the automatic creation of interval partitions. Use the proc_batch_insert stored procedure to insert new data. The table has the following schema:

CALL proc_batch_insert(1, 3000, 'sales');
Query OK, 1 row affected, 1 warning (0.99 sec)

SHOW CREATE TABLE sales \G
*************************** 1. row ***************************
       Table: sales
Create Table: CREATE TABLE `sales` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
/*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.03 sec)

Execute the DLM policy

Execute the following SQL statement to directly execute the DLM policy:
```
CALL dbms_dlm.execute_all_dlm_policies();
```

The mysql.dlm_progress table contains the following execution records of the current DLM policy:

SELECT * FROM mysql.dlm_progress \G
*************************** 1. row ***************************
                  Id: 1
        Table_schema: test
          Table_name: sales
         Policy_name: test_policy
         Policy_type: NONE
      Archive_option: PARTITIONS OVER 3
      Storage_engine: NULL
       Storage_media: NULL
     Data_compressed: OFF
Compressed_algorithm: NULL
  Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
       Archive_stage: ARCHIVE_COMPLETE
  Archive_percentage: 100
  Archived_file_info: null
          Start_time: 2023-01-09 17:31:24
            End_time: 2023-01-09 17:31:24
          Extra_info: null
1 row in set (0.03 sec)

The cold data originally stored in the p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, and _p20250101000000 partitions is deleted.

The sales table has the following schema:

SHOW CREATE TABLE sales \G
*************************** 1. row ***************************
       Table: sales
Create Table: CREATE TABLE `sales` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
/*!50500 (PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.02 sec)

Create or delete a DLM policy by executing the ALTER TABLE statement

Create a DLM policy by executing the ALTER TABLE statement.
```
ALTER TABLE t DLM ADD POLICY test_policy TIER TO TABLE ENGINE=CSV STORAGE=OSS READ ONLY
STORAGE TABLE_NAME = 'sales_history' ON (PARTITIONS OVER 3);
```
The DLM policy of the t table is named test_policy. The policy is executed when the table contains more than three partitions. When the policy is executed, data in the previous partitions of the t table is archived to OSS, and the archived table in OSS is named sales_history.
Enable the test_policy DLM policy on the t table.
```
ALTER TABLE t DLM ENABLE POLICY test_policy;
```
Disable the test_policy DLM policy on the t table.
```
ALTER TABLE t DLM DISABLE POLICY test_policy;
```
Delete the test_policy DLM policy on the t table.
```
ALTER TABLE t DLM DROP POLICY test_policy;
```

Troubleshoot execution errors

After DLM policies are executed, errors may occur due to inappropriate configurations. Error records are stored in the mysql.dlm_progress table. Execute the following statement to view the error records:

SELECT * FROM mysql.dlm_progress WHERE Archive_stage = "ARCHIVE_ERROR";

Find the details of an error in the Extra_info field, confirm the cause of the error, and then delete the current error record or change the value of the Archive_stage field of the current error record to ARCHIVE_COMPLETE. Execute the call dbms_dlm.execute_all_dlm_policies; statement to manually execute the DLM policy or wait for the next execution cycle to automatically execute the DLM policy.

Note

If a policy has an execution record in the ARCHIVE_ERROR state, the policy is not automatically executed to ensure data security. You must confirm the cause of the error and modify the status of the error record before the policy is executed.