Table schemas for automatically archiving cold data - PolarDB

Prerequisites

Before you begin, ensure that you have:

A privileged account on the PolarDB cluster
The DLM feature enabled on the cluster

Current limitations

The following fields are not yet available and will be supported in a future release:

Field	Table	Description
`Archive_type: CONDITION`	`mysql.dlm_policies`	Archiving data that meets specified conditions
`Storage_mode: READ WRITE`	`mysql.dlm_policies`	Read-write access mode for archived data
`Data_compressed`	Both	Specifies whether archived data is compressed
`Compressed_algorithm`	Both	Compression algorithm for archived data
`Priority_number`	`mysql.dlm_policies`	Execution priority of a DLM policy
`Tier_condition`	`mysql.dlm_policies`	Condition for archiving when `Archive_type` is `CONDITION`
`Storage_media: DISK`	`mysql.dlm_progress`	Archiving data to disk storage

mysql.dlm_policies table

The mysql.dlm_policies table records all DLM policies on the current cluster.

Table schema

CREATE TABLE `dlm_policies` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Table_schema` varchar(64) NOT NULL,
  `Table_name` varchar(64) NOT NULL,
  `Policy_name` varchar(64) NOT NULL,
  `Policy_type` varchar(64) DEFAULT NULL,
  `Archive_type` varchar(20) DEFAULT NULL,
  `Storage_mode` varchar(20) DEFAULT NULL,
  `Storage_engine` varchar(64) DEFAULT NULL,
  `Storage_media` varchar(20) DEFAULT NULL,
  `Storage_schema_name` varchar(64) DEFAULT NULL,
  `Storage_table_name` varchar(64) DEFAULT NULL,
  `Data_compressed` varchar(10) DEFAULT 'OFF',
  `Compressed_algorithm` varchar(64) DEFAULT NULL,
  `Enabled` varchar(10) DEFAULT 'ON',
  `Priority_number` int(11) NOT NULL,
  `Tier_partition_number` int(11) DEFAULT '0',
  `Tier_condition` varchar(512) DEFAULT NULL,
  `Extra_info` json NOT NULL,
  `Comment` varchar(2048) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `unique_policy` (`Table_schema`,`Table_name`,`Policy_name`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='PolarDB DLM policies table'
1 row in set (0.00 sec)

Parameters

Parameter	Default	Valid values	Description
`Id`	—	—	Auto-increment primary key.
`Table_schema`	—	—	The database that contains the table.
`Table_name`	—	—	The name of the table.
`Policy_name`	—	—	The name of the DLM policy.
`Policy_type`	`TABLE`	`TABLE`, `PARTITION`, `NONE`	The type of the DLM policy. `TABLE`: data is archived as a table. `PARTITION`: data is archived as a partition. `NONE`: data is deleted directly.
`Archive_type`	`PARTITION COUNT`	`PARTITION COUNT`, `CONDITION` (not yet available)	The execution mode of the DLM policy. `PARTITION COUNT`: archives data based on the number of partitions.
`Storage_mode`	`READ ONLY`	`READ ONLY`, `READ WRITE` (not yet available)	The storage access mode for archived data.
`Storage_engine`	—	—	The storage engine of the DLM table. Currently, only the CSV format is supported.
`Storage_media`	—	—	The storage medium of the DLM table. Currently, data can be archived only to Object Storage Service (OSS).
`Storage_schema_name`	—	—	The database that contains the archived table. Applies when `Policy_type` is `TABLE`.
`Storage_table_name`	—	—	The name of the archived table. Applies when `Policy_type` is `TABLE`.
`Data_compressed`	`OFF`	—	Specifies whether archived data is compressed. Not yet available.
`Compressed_algorithm`	—	—	The compression algorithm for archived data. Not yet available.
`Enabled`	`ENABLED`	`ENABLED`, `DISABLED`	Specifies whether the DLM policy is enabled.
`Priority_number`	—	—	The execution priority of the DLM policy. Not yet available.
`Tier_partition_number`	`0`	—	The number of partitions to archive when `Archive_type` is `PARTITION COUNT`.
`Tier_condition`	—	—	The archiving condition when `Archive_type` is `CONDITION`. Not yet available.
`Extra_info`	—	—	Additional information stored as JSON.
`Comment`	—	—	The comment for the DLM policy.

mysql.dlm_progress table

The mysql.dlm_progress table stores the execution records of DLM policies on the current cluster.

Table schema

CREATE TABLE `dlm_progress` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Table_schema` varchar(64) NOT NULL,
  `Table_name` varchar(64) NOT NULL,
  `Policy_name` varchar(64) NOT NULL,
  `Policy_type` varchar(64) DEFAULT NULL,
  `Archive_option` varchar(64) DEFAULT NULL,
  `Storage_engine` varchar(64) DEFAULT NULL,
  `Storage_media` varchar(20) DEFAULT NULL,
  `Data_compressed` varchar(10) DEFAULT 'OFF',
  `Compressed_algorithm` varchar(64) DEFAULT NULL,
  `Archive_partitions` varchar(2048) DEFAULT NULL,
  `Archive_stage` varchar(64) DEFAULT NULL,
  `Archive_percentage` int(11) DEFAULT NULL,
  `Archived_file_info` json NOT NULL,
  `Start_time` datetime NOT NULL,
  `End_time` datetime DEFAULT NULL,
  `Extra_info` json NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `unique_progress` (`Table_schema`,`Table_name`,`Policy_name`,`Start_time`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='PolarDB DLM progress table'
1 row in set (0.00 sec)

Parameters

Parameter	Default	Valid values	Description
`Id`	—	—	Auto-increment primary key.
`Table_schema`	—	—	The database that contains the table.
`Table_name`	—	—	The name of the table.
`Policy_name`	—	—	The name of the DLM policy.
`Policy_type`	`TABLE`	`TABLE`, `PARTITION`, `NONE`	The type of the DLM policy. `TABLE`: data is archived as a table. `PARTITION`: data is archived as a partition. `NONE`: data is deleted directly.
`Archive_option`	—	—	The execution condition of the DLM policy.
`Storage_engine`	—	—	The storage engine for the archived data. Currently, only the CSV format is supported.
`Storage_media`	`OSS`	`OSS`, `DISK` (not yet available)	The storage medium for the archived data.
`Data_compressed`	`OFF`	—	Specifies whether archived data is compressed. Not yet available.
`Compressed_algorithm`	—	—	The compression algorithm for archived data. Not yet available.
`Archive_partitions`	—	—	The name of the partition used for data archiving.
`Archive_stage`	—	`INITIALIZE`, `WAITING`, `DATA_COPYING`, `STORAGE_MOVING`, `DATA_VERIFYING`, `DATA_DELETING`, `ARCHIVE_COMPLETE`, `ARCHIVE_ERROR`	The execution stage of the DLM policy. See Archive stages for details.
`Archive_percentage`	—	—	The execution progress of the DLM policy, as a percentage.
`Archived_file_info`	—	—	File information for the archived data, stored as JSON.
`Start_time`	—	—	The start time of the DLM policy execution.
`End_time`	—	—	The end time of the DLM policy execution.
`Extra_info`	—	—	Additional information stored as JSON. If the policy is in the `ARCHIVE_ERROR` stage, the error details are stored here.

Archive stages

The Archive_stage field tracks each execution stage of a DLM policy:

Stage	Description
`INITIALIZE`	The task is being initialized.
`WAITING`	The task is waiting to be executed.
`DATA_COPYING`	Data is being copied to the new storage medium.
`STORAGE_MOVING`	The system is switching the storage engine for the archived data.
`DATA_VERIFYING`	The system is verifying that the archived data matches the original.
`DATA_DELETING`	The system is deleting the original data to free storage space.
`ARCHIVE_COMPLETE`	The DLM policy execution completed successfully.
`ARCHIVE_ERROR`	An error occurred during the DLM policy execution.

If a DLM policy is in any stage other than ARCHIVE_COMPLETE, a duplicate execution of the same policy is automatically skipped.

Handle archiving errors

If the Archive_stage is ARCHIVE_ERROR, check the Extra_info field in mysql.dlm_progress for the error details.

After identifying the cause, do one of the following to reset the policy status before running it again:

Delete the execution record of the failed policy.
Run the following statement to set the status to ARCHIVE_COMPLETE:
```
UPDATE mysql.dlm_progress SET Archive_stage = "ARCHIVE_COMPLETE" WHERE Id = <current_progress_id>;
```
Replace <current_progress_id> with the Id value of the failed execution record.

Then run the DLM policy again.