PolarDB:Archive partitioned tables to X-Engine

Process overview

1. Create a DLM archive policy: Define the archiving rules on the target partitioned table.

2. Execute the DLM archive policy: Trigger the archiving process manually or through a scheduled task.

3. View the archive status and results: Verify that the partitions have been successfully converted to the X-Engine engine.

Step 1: Create a DLM archive policy

You can define an archiving policy when you create a partitioned table or add one to an existing table.

• Method 1: Define a policy when creating a new table

  Use the DLM ADD POLICY clause at the end of the CREATE TABLE statement. The following example creates a sales table that uses the order_time column as the partitioning key. This table has two policies: an INTERVAL policy and a DLM policy.

  • INTERVAL policy: Automatically creates new partitions for one-year intervals when inserted data falls outside the existing partition ranges.

  • DLM policy: Defines a policy named policy_part2part. This policy specifies that when the total number of partitions exceeds three, the oldest partition is marked for archiving to X-Engine.

  CREATE TABLE `sales` (
    `id` int DEFAULT NULL,
    `name` varchar(20) DEFAULT NULL,
    `order_time` datetime NOT NULL,
    primary key (order_time)
  ) 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=XENGINE READ WRITE ON (PARTITIONS OVER 3);

• Method 2: Add a policy to an existing table

  Use the ALTER TABLE statement to add a DLM policy to an existing partitioned table. For more information, see Create or delete a DLM policy in ALTER TABLE.

  ALTER TABLE sales
  DLM ADD POLICY policy_part2part TIER TO PARTITION ENGINE=XENGINE ON (PARTITIONS OVER 3);

  Syntax description: ON (PARTITIONS OVER N) is the core of the policy, where N is the number of recent partitions to keep in the InnoDB engine. When the total number of partitions exceeds N, the oldest partitions beyond this number are marked for archiving.

Insert test data

1. Use the proc_batch_insert stored procedure to insert test data into the sales partitioned table. This triggers the INTERVAL policy to automatically create new 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 following result indicates that the data is inserted successfully:

   Query OK, 1 row affected (0.50 sec)

2. Run the following command to view the sales table structure.

   SHOW CREATE TABLE sales \G

   The output shows the table structure, in which all partitions currently use the InnoDB engine.

   mysql> 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 NOT NULL,
     PRIMARY KEY (`order_time`)
   ) 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) */

Step 2: Execute the DLM archive policy

Trigger the defined archive policy to start the partition engine conversion process. You can choose to execute it manually or set up a scheduled task based on your business needs.

• Method 1: Scheduled execution (Recommended): For production environments that require regular data archiving, we recommend using the MySQL EVENT feature to automatically run the task during off-peak hours, such as in the early morning. This approach automates the task.

  The following example creates an event that starts on 2026-02-01 and runs daily at 01:00 to execute all DLM policies.

  CREATE EVENT dlm_system_base_event
         ON SCHEDULE EVERY 1 DAY
      STARTS '2026-02-01 01:00:00'
      do CALL 
  dbms_dlm.execute_all_dlm_policies();

• Method 2: Manual execution: This method is suitable for one-time archive tasks or for manually retrying a task after troubleshooting.

  Call the following stored procedure to immediately trigger all defined DLM policies.

  CALL dbms_dlm.execute_all_dlm_policies();

Step 3: View the archive status and results

Monitor the archive task's progress. After the task completes, verify that the partition engines have changed.

1. View the policy definition

   Query the mysql.dlm_policies system table to confirm that the policy was created.

   SELECT * FROM mysql.dlm_policies WHERE Table_schema = 'your_database' AND Table_name = 'sales'\G

   The following result is returned:

   *************************** 1. row ***************************
                      Id: 1
            Table_schema: your_database
              Table_name: sales
             Policy_name: policy_part2part
             Policy_type: PARTITION
            Archive_type: PARTITION COUNT
            Storage_mode: READ WRITE
          Storage_engine: XENGINE
           Storage_media: DISK
     Storage_schema_name: NULL
      Storage_table_name: NULL
         Data_compressed: ON
    Compressed_algorithm: Zstandard
                 Enabled: ENABLED
         Priority_number: 200
   Tier_partition_number: 3
          Tier_condition: NULL
              Extra_info: {"oss_file_filter": "order_time"}
                 Comment: NULL

   Key field descriptions:

   Field	Description
   Table_schema, Table_name	The database and table to which the policy belongs.
   Policy_name	The custom name of the policy.
   Storage_engine	The target storage engine for archived partitions. In this example, it is XENGINE.
   Tier_partition_number	The policy defines the number of InnoDB partitions to retain. This is the N in PARTITIONS OVER N.

2. View the execution progress

   During or after policy execution, you can query the mysql.dlm_progress system table to track the task status.

   SELECT * FROM mysql.dlm_progress WHERE Table_schema = 'your_database' AND Table_name = 'sales' ORDER BY Id DESC LIMIT 1\G

   The following result is returned:

   *************************** 1. row ***************************
                     Id: 1
           Table_schema: your_database
             Table_name: sales
            Policy_name: policy_part2part
            Policy_type: PARTITION
         Archive_option: PARTITIONS OVER 3
         Storage_engine: XENGINE
          Storage_media: DISK
        Data_compressed: ON
   Compressed_algorithm: Zstandard
     Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
          Archive_stage: ARCHIVE_COMPLETE
     Archive_percentage: 100
     Archived_file_info: null
             Start_time: 2026-02-06 10:50:00
               End_time: 2026-02-06 10:50:00
             Extra_info: null

   Key field descriptions:

   Field	Description
   Archive_partitions	The partitions archived in this task.
   Archive_stage	The current status of the archive task. ARCHIVE_COMPLETE indicates success, and ARCHIVE_ERROR indicates failure.
   Archive_percentage	The completion percentage of the task.
   Start_time, End_time	The start and end times of the task.
   Extra_info	Additional information. If Archive_stage is ARCHIVE_ERROR, this field contains error details.

3. Verify the table structure

   After the archive task is complete, use the SHOW CREATE TABLE command to view the table structure and confirm that the ENGINE of the archived partitions has changed to XENGINE.

   SHOW CREATE TABLE sales\G

   The following result is returned:

   *************************** 1. row ***************************
          Table: sales
   Create Table: CREATE TABLE `sales` (
     `id` int(11) DEFAULT NULL,
     `name` varchar(20) DEFAULT NULL,
     `order_time` datetime NOT NULL,
     PRIMARY KEY (`order_time`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
   /*!99990 800020216 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
   /*!99990 800020216 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = XENGINE,
    PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = XENGINE,
    PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = XENGINE,
    PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = XENGINE,
    PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = XENGINE,
    PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = XENGINE,
    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) */