PolarDB supports the parallel DDL feature. When available hardware resources exist in a cluster, the parallel DDL feature can speed up the execution of DDL operations. This prevents subsequent DML operations from being blocked and shortens the execution windows of DDL operations.
- A cluster of PolarDB for MySQL 8.0 whose revision version is 18.104.22.168.10 or later.
- A cluster of PolarDB for MySQL 5.7 whose revision version is 22.214.171.124.7 or later.
For more information about how to check the cluster version, see Query the engine version.
After the parallel DDL feature is enabled, more hardware resources, such as CPU, memory, and I/O, are occupied as the number of concurrent threads increases. This may affect other SQL operations that are executed in the same period as the parallel DDL operations. Therefore, we recommend that you use the parallel DDL feature during off-peak hours or when hardware resources are sufficient.
The parallel DDL feature can accelerate only the DDL operations that are used to create secondary indexes. The secondary indexes exclude clustered indexes, full-text indexes, spatial indexes, and the secondary indexes on virtual columns.
Traditional DDL operations are designed based on a single core and traditional hard disks. As a result, it takes a long time to execute the DDL operations on large tables, and the latency is high. For example, when you perform DDL operations to create secondary indexes, subsequent DML queries that depend on the new indexes are blocked due to the high latency of the DDL operations. The multi-core processors provide hardware support for the parallel DDL feature to use more threads. In addition, Solid State Disks (SSDs) are widely used so that random access latency is close to sequential access latency. In this case, the parallel DDL feature can be used to accelerate the process of creating indexes on large tables.
Use the parallel DDL feature
You can enable the parallel DDL feature by specifying the innodb_polar_parallel_ddl_threads parameter.
Parameter Level Valid value Description innodb_polar_parallel_ddl_threads Session [1,16]
Default value: 1.
The number of concurrent threads for each DDL operation. The default value 1 indicates that single-threaded DDL operations are executed.
If the value of this parameter is not 1 or if the executed DDL operation is used to create secondary indexes, the parallel DDL feature is automatically enabled.Note If the value of this parameter is 1, two concurrent threads are used for each DDL operation.
If the parallel DDL feature still cannot meet your requirements, you can use the innodb_polar_use_sample_sort parameter to optimize the sorting when you create indexes.
Parameter Level Description innodb_polar_use_sample_sort Session Specifies whether to enable the sample sort optimization feature. Default value: OFF. Valid values:
If the sample sort optimization feature still cannot meet your requirements, you can use the innodb_polar_use_parallel_bulk_load parameter to optimize the process of creating index trees.
Parameter Level Description innodb_polar_use_parallel_bulk_load Session Specifies whether to enable the bulk load optimization feature. Default value: OFF. Valid values:
- Test environment
- A PolarDB for MySQL 8.0 cluster that has 16 CPU cores and 128 GB of memory. The cluster runs the Cluster Edition.
- The storage capacity of the cluster is 50 TB.
Create a table that is named
CREATE TABLE t0( a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
Generate test data.
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() ;Note
- In the actual test, replace
$table_sizewith the number of records that you want in your table, such as
- This test uses tables that have 1 million, 10 million, 100 million, and 1 billion rows of data, and a table that stores 1 TB of data.
- The test table that stores 1 TB of data is generated by the SysBench tool. For more information about how to use the SysBench tool, see Test tool.
- In the actual test, replace
- Test method
Test the increased rate of execution efficiency of the DDL operations that create secondary indexes on the
bfield of the
INTdata type under the following conditions: The number of concurrent threads is different when innodb_polar_parallel_ddl_threads is set to 1, 2, 4, 8, 16, and 32. The parallel DDL feature is enabled for tables that have different volumes of data.
- Test results
- The following figure shows the acceleration ratio of parallel DDL after only the innodb_polar_parallel_ddl_threads parameter is set to ON.
- The following figure shows the acceleration ratio of parallel DDL after both the innodb_polar_parallel_ddl_threads and innodb_polar_use_sample_sort parameters are set to ON.
- The following figure shows the acceleration ratio of parallel DDL after the innodb_polar_parallel_ddl_threads, innodb_polar_use_sample_sort, and innodb_polar_use_parallel_bulk_load parameters are set to ON.