Parallel DDL speeds up index creation on large tables by distributing work across multiple threads instead of one. When hardware resources are available, it reduces DDL execution time and shortens the window during which subsequent Data Manipulation Language (DML) operations are blocked.
Prerequisites
Before you begin, ensure that you have:
A PolarDB for MySQL cluster that meets the version requirements below
Secondary index creation requires one of the following cluster versions:
PolarDB for MySQL 8.0.2, revision version 8.0.2.1.7 or later
PolarDB for MySQL 8.0.1, revision version 8.0.1.1.10 or later
PolarDB for MySQL 5.7, revision version 5.7.1.0.7 or later
Primary key index creation requires one of the following cluster versions:
PolarDB for MySQL 8.0.2, revision version 8.0.2.2.9 or later
PolarDB for MySQL 8.0.1, revision version 8.0.1.1.31 or later
To check your cluster version, see Query the version number.
Limitations
Parallel DDL supports creating primary key indexes and secondary indexes. It does not support:
Full-text indexes
Spatial indexes
Secondary indexes on virtual columns
Resource impact
Parallel DDL increases thread usage, which raises CPU, memory, and I/O consumption. Run parallel DDL during off-peak hours or when the cluster has sufficient hardware resources, to avoid affecting concurrent SQL operations.
Enable parallel DDL
Set the thread count for secondary index creation
The innodb_polar_parallel_ddl_threads parameter controls how many threads run in parallel for each DDL operation.
| Parameter | Level | Description |
|---|---|---|
innodb_polar_parallel_ddl_threads | Session | Number of parallel threads per DDL operation. Range: 1–16. Default: 1 (single-threaded). Set to any value other than 1 to enable parallel DDL for secondary index creation. Note When set to 1, the system still uses two parallel threads internally. |
Example:
-- Use 8 parallel threads for the current session
SET innodb_polar_parallel_ddl_threads = 8;
-- Create a secondary index using the configured thread count
ALTER TABLE t0 ADD INDEX idx_b (b);Enable parallel primary key index creation
Parallel DDL for primary key indexes requires an additional trial activation.
To use this feature, go to Quota Center. Find the quota with the Quota ID of polardb_mysql_pddl_for_pk, and click Apply in the Actions column to request a trial.
| Parameter | Level | Description |
|---|---|---|
innodb_parallel_build_primary_index | Global | Controls whether parallel DDL is allowed for primary key index creation. Valid values: ON (allowed), OFF (default, not allowed). |
Optimize the sort phase
If the default thread parallelism does not meet your performance requirements, enable sample sort to further optimize the sort phase.
| Parameter | Level | Description |
|---|---|---|
innodb_polar_use_sample_sort | Session | Enables sample sort optimization during index creation. Valid values: ON, OFF (default). |
Example:
SET innodb_polar_parallel_ddl_threads = 8;
SET innodb_polar_use_sample_sort = ON;
ALTER TABLE t0 ADD INDEX idx_b (b);Optimize the load phase
To further accelerate index tree construction, enable parallel bulk load.
| Parameter | Level | Description |
|---|---|---|
innodb_polar_use_parallel_bulk_load | Session | Enables parallel bulk load optimization for index tree creation. Valid values: ON, OFF (default). |
Example — all three optimizations enabled:
SET innodb_polar_parallel_ddl_threads = 8;
SET innodb_polar_use_sample_sort = ON;
SET innodb_polar_use_parallel_bulk_load = ON;
ALTER TABLE t0 ADD INDEX idx_b (b);Performance test
Test environment
Cluster: Standard Edition PolarDB for MySQL 8.0, 16 cores, 128 GB memory
Storage: 50 TB
Test table
CREATE TABLE t0(
a INT PRIMARY KEY,
b INT) ENGINE=InnoDB;Test data was generated using this procedure:
DELIMITER //
CREATE PROCEDURE populate_t0()
BEGIN
DECLARE i int DEFAULT 1;
WHILE (i <= $table_size) DO
INSERT INTO t0 VALUES (i, 1000000 * RAND());
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL populate_t0();Replace $table_size with the number of rows, such as 1000000. The test uses tables with 1,000,000, 10,000,000, 100,000,000, and 1,000,000,000 rows, plus a 1 TB table generated with Sysbench. For details on Sysbench setup, see Test tools.
Test method
Each test measures the time to create a secondary index on the b column (INT type) across tables of increasing size, with innodb_polar_parallel_ddl_threads set to 1, 2, 4, 8, 16, and 32.
Test results
The graphs below show the speedup ratio relative to single-threaded DDL (thread count = 1) under three parameter combinations.
Thread parallelism only (`innodb_polar_parallel_ddl_threads`):
Thread parallelism + sample sort (`innodb_polar_parallel_ddl_threads` + `innodb_polar_use_sample_sort`):
All three parameters (`innodb_polar_parallel_ddl_threads` + `innodb_polar_use_sample_sort` + `innodb_polar_use_parallel_bulk_load`):
Contact us
If you have any questions about DDL operations, please contact technical support.
Related resources
If you have questions about DDL operations, contact technical support.