As business data grows, storing infrequently accessed warm and cold data in InnoDB increases storage costs. Archiving to external storage removes online read, write, and analytical access. PolarDB for MySQL solves this with X-Engine data tiering: convert an entire table or specific partitions to the high-compression X-Engine, keeping InnoDB and X-Engine partitions in the same table. Archived data retains full DML write support and online DDL compatibility.
How it works
PolarDB for MySQL lets different partitions in a single partitioned table use different storage engines:
Data tiering: Keep high-frequency "hot" data partitions in InnoDB for optimal read/write performance. Migrate "warm" or "cold" partitions to X-Engine.
Smart routing: Queries are automatically routed to the correct partition and storage engine based on query conditions. The application layer sees no difference.
High compression ratio: X-Engine uses advanced compression algorithms and an optional column store format to reduce physical disk usage significantly.
Prerequisites
Before you begin, ensure that you have:
A PolarDB for MySQL cluster meeting the version requirements below
X-Engine enabled on your cluster
Hybrid partitioning enabled (see below)
Version requirements
| Scenario | Minimum version |
|---|---|
| Archive standard (non-partitioned) tables | MySQL 8.0.1 revision 8.0.1.1.31 or later; or MySQL 8.0.2 revision 8.0.2.2.12 or later |
| Archive partitioned tables | MySQL 8.0.2 revision 8.0.2.2.12 or later |
| Archive to X-Engine column-oriented tables | MySQL 8.0.2 revision 8.0.2.2.33 or later |
Enable hybrid partitioning
For clusters running a minor engine version earlier than 8.0.2.2.33, enable hybrid partitioning before archiving partitioned tables:
Go to Quota Center.
Find the quota with Quota ID
polardb_mysql_hybrid_partition.Click Request in the Operation column.
Archive standard tables
Convert an entire non-partitioned table from InnoDB to X-Engine. Use TABLE_FORMAT=COLUMN for write-intensive data with high compression potential, such as logs or instrumentation data.
Syntax
ALTER TABLE table_name ENGINE=engine_name [TABLE_FORMAT=COLUMN];| Parameter | Description |
|---|---|
table_name | Name of the table to archive |
engine_name | XEngine to archive to X-Engine; InnoDB to revert |
TABLE_FORMAT | Storage format (optional). ROW (default): row-oriented. COLUMN: column-oriented. |
Examples
Archive table t1 to an X-Engine row-oriented table:
ALTER TABLE t1 ENGINE=XEngine;Archive table t1 to an X-Engine column-oriented table:
ALTER TABLE t1 ENGINE=XEngine TABLE_FORMAT=COLUMN;Revert table t1 back to InnoDB:
ALTER TABLE t1 ENGINE=InnoDB;Archive partitioned tables
Archive specific partitions (typically older partitions with cold data) to X-Engine. This creates a hybrid partitioned table with InnoDB and X-Engine partitions coexisting.
Archive a partition
Syntax
ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = XEngine [TABLE_FORMAT=COLUMN];| Parameter | Description |
|---|---|
table_name | Name of the partitioned table |
part_name | Name of the partition to archive |
TABLE_FORMAT | Storage format (optional). ROW (default): row store. COLUMN: column-oriented. |
Examples
Archive partition p1 of table t1 to X-Engine row store:
ALTER TABLE t1 CHANGE PARTITION p1 ENGINE = XEngine;Archive partition p1 of table t1 to an X-Engine column-oriented table:
ALTER TABLE t1 CHANGE PARTITION p1 ENGINE = XEngine TABLE_FORMAT=COLUMN;Revert a partition to InnoDB
Syntax
ALTER TABLE table_name REORGANIZE PARTITION part_name INTO (partition_definition);| Parameter | Description |
|---|---|
table_name | Name of the hybrid partitioned table |
part_name | Name of the partition to revert to InnoDB |
partition_definition | Must match the original partition definition. The ENGINE clause is optional; if omitted, the partition reverts to InnoDB (the table's default engine). |
Example
Revert partition p1 of table t1 from X-Engine back to InnoDB:
ALTER TABLE t1 REORGANIZE PARTITION p1 INTO (PARTITION p1 VALUES LESS THAN(100));Production checklist
| Consideration | Recommendation |
|---|---|
| Which data to archive | Archive only warm or cold data with low access frequency. Keep hot data in InnoDB for optimal read/write performance. |
| Storage format | For write-intensive data with high compression potential (such as logs or instrumentation data), use TABLE_FORMAT=COLUMN. |
| Execution timing | Run ALTER TABLE during off-peak hours. Although the operation is online, it consumes significant CPU and IOPS and may cause performance fluctuations on production workloads. |
| Monitoring | During archiving, monitor CPU usage, IOPS, and disk space in the console. Use SHOW FULL PROCESSLIST; to track DDL progress. |