Parallel Copy DDL
In native MySQL, when a DDL operation cannot use the INPLACE algorithm (for example, when you modify the row format, convert the storage engine, change a regular table to a partitioned table, or alter certain column types), it falls back to the COPY algorithm. This algorithm creates a temporary table and copies data row by row from the original table to the new table. Because this copy process is single-threaded, it can be slow for large tables. PolarDB for MySQL implements the Parallel Copy DDL feature, which significantly accelerates DDL operations on large tables by distributing the data copy task across multiple worker threads.
Applicability
This feature is available for PolarDB for MySQL clusters that run version 8.0.2 with revision 8.0.2.2.34.1 or later. To check your cluster version, see View the version of a cluster.
Limitations
This feature supports only tables that use the InnoDB storage engine.
This feature does not support tables with foreign key constraints or spatial indexes.
This feature does not support hybrid partitioned tables.
This feature does not support temporary tables.
Before running a DDL operation, use the EXPLAIN DDL command to determine if the operation supports parallel DDL.
Considerations
Enabling Parallel Copy DDL uses more system resources, such as CPU, memory, and I/O, due to the additional worker threads. This may impact the performance of other SQL operations that are running concurrently. Use this feature during off-peak hours or when sufficient system resources are available.
Procedure
To enable Parallel Copy DDL, set the corresponding cluster parameter. After you enable the loose_polar_enable_parallel_copy_ddl parameter, you can run a DDL statement to start a Parallel Copy DDL task. For detailed instructions, see Modify cluster and node parameters. The following table lists the relevant parameters.
Parameter | Scope | Description |
| Global | Enables or disables the Parallel Copy DDL feature.
|
| Global/Session | Controls the parallelism.
|
Performance test
Test environment
A Standard Edition PolarDB for MySQL 8.0 cluster with 16 cores and 64 GB of memory.
Test table schema
A test table named t1 is created by using the following statement:
CREATE TABLE IF NOT EXISTS `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(255) NOT NULL DEFAULT '',
`pad` char(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_10` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;Test data
Use the following statements to generate 100 million rows of test data:
DELIMITER //
CREATE PROCEDURE populate_t1()
BEGIN
DECLARE i int DEFAULT 1;
WHILE (i <= 100000000) DO
INSERT INTO t1 (k, c, pad) VALUES (1, REPEAT('a', 255), REPEAT('b', 255));
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL populate_t1();Test method
The test measures the time required for a full table rebuild by using the COPY algorithm. The test runs with different numbers of worker threads by setting the loose_polar_max_parallel_copy_ddl_workers parameter to 1, 2, 4, 8, 16, and 32.
Test results
The results show that the DDL operation time decreases significantly as the number of worker threads increases. With 16 threads, the test achieves a maximum speedup ratio of approximately 3.47x, after which the performance gains level off. Configure the parallelism based on your cluster specifications and workload.
