Archive to X-Engine format - PolarDB - Alibaba Cloud Documentation Center

As business data continues to grow, storing all data—including infrequently accessed warm and cold data—in standard InnoDB tables causes storage costs to rise steadily. Archiving such data to external storage, on the other hand, sacrifices online read, write, and analytical access. PolarDB for MySQL provides data tiering based on X-Engine. You can convert an entire table or specific partitions within a table to the high-compression X-Engine, so that InnoDB (hot data) and X-Engine (warm or cold data) coexist in the same table. This reduces storage costs while preserving online data access, and data in X-Engine continues to support DML writes and online DDL schema changes.

How it works

The hybrid partitioning feature of PolarDB for MySQL allows different partitions within a single partitioned table to use different storage engines. It works as follows:

Data tiering: You can keep frequently accessed "hot" data partitions in the InnoDB engine for optimal read and write performance, and migrate less frequently accessed "warm" or "cold" data partitions to the X-Engine engine.
Smart routing: When you query the table, PolarDB automatically routes the request to the correct partition and its storage engine based on the query conditions. The entire process is transparent to the application layer.
High compression ratio: The X-Engine engine, through its advanced compression algorithms and the optional column store format, delivers an extremely high data compression ratio. This significantly reduces physical storage usage and lowers costs.

Scope of application

Before you use this feature, make sure that your PolarDB for MySQL cluster meets the following version requirements.

Archive a standard table:
- MySQL 8.0.1 with a revision of 8.0.1.1.31 or later.
- MySQL 8.0.2 with a revision of 8.0.2.2.12 or later.
Archive a partitioned table: MySQL 8.0.2 with a revision of 8.0.2.2.12 or later.
Archive to an X-Engine column-store table: MySQL 8.0.2 with a revision of 8.0.2.2.34.1 or later.

Preparations

Before you start archiving data, complete the following preparations.

Enable X-Engine: Make it available as a storage engine option for tables or partitions.
To archive a partitioned table as a hybrid partitioned table, the following conditions must be met:
- MySQL 8.0.2 and the minor version is 8.0.2.2.34.1 or later. You must set the loose_allow_create_hybrid_partition parameter to ON.
- MySQL 8.0.2 and the minor version is earlier than 8.0.2.2.34.1. Upgrade to a later minor version.

Archive standard tables

Convert the data and storage engine of an entire non-partitioned table from InnoDB to X-Engine.

Syntax

ALTER TABLE table_name ENGINE=engine_name[ TABLE_FORMAT=COLUMN];

Parameters

Parameter	Description
table_name	The name of the table to archive to X-Engine.
engine_name	The name of the storage engine. XEngine: archive to the X-Engine row store format. InnoDB: revert to the InnoDB engine.
TABLE_FORMAT	Optional. The data storage format of X-Engine. ROW (default): row-oriented table. COLUMN: column-oriented table.

Examples

Archive a standard table t1 to an X-Engine row-oriented table:
```
ALTER TABLE t1 ENGINE=XEngine;
```
Archive a standard table t1 to an X-Engine column-oriented table:
```
ALTER TABLE t1 ENGINE=XEngine TABLE_FORMAT=COLUMN;
```
Revert a standard table t1 back to the InnoDB engine:
```
ALTER TABLE t1 ENGINE=InnoDB;
```

Archive partitioned tables

Archive a specified partition

Archive a specific partition in a partitioned table—typically an older partition that stores cold data—to X-Engine. This implements tiered storage of hot and cold data, and the resulting table becomes a hybrid partitioned table.

Syntax

ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = XEngine[ TABLE_FORMAT=COLUMN];

Parameters

Parameter	Description
table_name	The name of the table to archive to X-Engine.
part_name	The name of the partition to archive to X-Engine.
TABLE_FORMAT	Optional. The data storage format of X-Engine. ROW (default): row store. COLUMN: column-oriented table.

Examples

Archive the p1 partition of a partitioned table t1 to the X-Engine row store format:
```
ALTER TABLE t1 CHANGE PARTITION p1 ENGINE = XEngine;
```
Archive the p1 partition of a partitioned table t1 to an X-Engine column-oriented table:
```
ALTER TABLE t1 CHANGE PARTITION p1 ENGINE = XEngine TABLE_FORMAT=COLUMN;
```

Revert to the InnoDB engine

Syntax

ALTER TABLE table_name REORGANIZE PARTITION part_name INTO (partition_definition);

Parameters

Parameter	Description
table_name	The name of the hybrid partitioned table.
part_name	The name of the partition to revert to the InnoDB engine.
partition_definition	Must be consistent with the `partition_definition` of the partition to revert. Note The ENGINE clause in the partition definition is optional. If no engine name is specified, the default engine is InnoDB, the same as the table. If `ENGINE=InnoDB` is specified, the InnoDB engine is used.

Example

Revert the data stored in the p1 partition of a partitioned table t1 that was archived to X-Engine back to the InnoDB engine.

ALTER TABLE t1 REORGANIZE PARTITION p1 INTO(PARTITION p1 VALUES LESS THAN(100));

Apply to production

Best practice: Archive only warm and cold data with low access frequency to X-Engine to achieve the best balance between cost and query performance. For write-intensive data with high compression potential (such as logs and instrumentation data), prefer the X-Engine column store format (TABLE_FORMAT=COLUMN).
Risk mitigation: Data migration (ALTER TABLE) is a resource-intensive operation. Although it runs online, we recommend that you perform it during off-peak hours to avoid performance fluctuations on production workloads.
Monitoring and alerting: During archiving, you can run SHOW FULL PROCESSLIST; to track the progress of DDL operations. At the same time, closely monitor key metrics such as CPU usage, IOPS, and disk space of the cluster in the console.