Table structure for automatically archiving cold data - PolarDB

The DLM feature uses two system tables: mysql.dlm_policies and mysql.dlm_progress. You can use a privileged account to view all available DLM policies and their execution records on the current cluster. These two tables are automatically created at system startup, and you do not need to create them manually.

mysql.dlm_policies table

The mysql.dlm_policies table stores 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)

Parameter Description

Parameter	Description
Id	An auto-increment primary key.
Table_schema	The database that contains the table to which the DLM policy applies.
Table_name	The name of the table to which the DLM policy applies.
Policy_name	The name of the DLM policy.
Policy_type	The DLM policy type. Valid values: TABLE (default): Archives data as a table. PARTITION: Archives data as a partition. NONE: Deletes data directly.
Archive_type	The execution method of the DLM policy. Valid values: PARTITION COUNT (default): Archives data based on a specified number of partitions. CONDITION: Archives data based on a specified condition. This value is not supported.
Storage_mode	The storage mode. Valid values: READ ONLY (default): The archived data is read-only. READ WRITE: The archived data is read-write. This value is not supported.
Storage_engine	The storage engine for the archive table. Currently, data can only be archived in CSV format.
Storage_media	The storage medium for the archive table. Currently, only OSS is supported.
Storage_schema_name	The database that contains the archive table when data is archived as a table.
Storage_table_name	The name of the archive table when data is archived as a table.
Data_compressed	Indicates whether to compress the archived data. This parameter is not supported.
Compressed_algorithm	The data compression algorithm. This parameter is not supported.
Enabled	Specifies whether the DLM policy is enabled. Valid values: ENABLED (default): The DLM policy is enabled. DISABLED: The DLM policy is disabled.
Priority_number	The execution priority of the DLM policy. This parameter is not supported.
Tier_partition_number	The number of partitions to archive when Archive_type is PARTITION COUNT.
Tier_condition	The archival condition when Archive_type is CONDITION. This parameter is not supported.
Extra_info	Additional information in JSON format.
Comment	A comment on 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)

Parameter Description

Parameter	Description
Id	An auto-increment primary key.
Table_schema	The database that contains the table to which the DLM policy applies.
Table_name	The name of the table to which the DLM policy applies.
Policy_name	The name of the DLM policy.
Policy_type	The type of the DLM policy. Valid values: TABLE (default): Archives data as a table. PARTITION: Archives data as a partition. NONE: Deletes data directly.
Archive_option	The execution condition of the DLM policy.
Storage_engine	The storage engine for the archive table. Currently, data can only be archived in CSV format.
Storage_media	The storage medium for the DLM policy. Valid values: OSS (default): The storage medium is OSS. DISK: The storage medium is local PFS storage. This value is not supported.
Data_compressed	Indicates whether to compress the archived data. This parameter is not supported.
Compressed_algorithm	The data compression algorithm. This parameter is not supported.
Archive_partitions	The names of the archived partitions.
Archive_stage	The execution phase of the DLM policy. The phases are: INITIALIZE: The task is being initialized. WAITING: The task is queued and waiting for execution. DATA_COPYING: The system is copying data to the new storage medium. STORAGE_MOVING: The system is changing the storage engine for the data to be archived. DATA_VERIFYING: The system is verifying that the archived data is consistent with the source data. DATA_DELETING: The system is deleting the source data to free up storage space. ARCHIVE_COMPLETE: The policy execution is complete. ARCHIVE_ERROR: An error occurred during policy execution. Note While a DLM policy is running (in any phase other than ARCHIVE_COMPLETE), the system automatically skips subsequent requests to execute the same policy. If a policy execution fails and enters the ARCHIVE_ERROR phase, the system stores the error details in the `Extra_info` column of the `mysql.dlm_progress` table. Before you can re-run the policy, you must first identify and resolve the cause of the failure. Then, you must clear the failed record by either deleting it or updating its status to ARCHIVE_COMPLETE by running the statement: `UPDATE mysql.dlm_progress SET Archive_stage = "ARCHIVE_COMPLETE" where Id = current_progress_id;`. Once the record is reset, you can re-run the DLM policy.
Archive_percentage	The execution progress of the DLM policy, as a percentage.
Archived_file_info	Information about the archived files, in JSON format.
Start_time	The start time of the DLM policy execution.
End_time	The end time of the DLM policy execution.
Extra_info	Additional information, such as error messages, in JSON format.