PolarDB supports the parallel data definition language (DDL) feature. When hardware resources of databases are idle, you can use the parallel DDL feature to speed up the execution of DDL operations. This prevents subsequent relevant data manipulation language (DML) operations from being blocked and shortens the execution windows of DDL operations.
Prerequisites
The version of the PolarDB cluster is PolarDB for MySQL 8.0 and the revision version is 8.0.1.1.7 or later. For more information about how to confirm the cluster version, see Query the kernel version.
Considerations
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 is increased. This may affect other Structured Query Language (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.
Limits
The parallel DDL feature can accelerate only the DDL operations that are performed to create secondary indexes. The secondary indexes exclude clustered indexes, full-text indexes, spatial indexes, and the secondary indexes on virtual columns.
Background information
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 is a crucial way to accelerate the process of creating indexes on large tables.
Use method
- innodb_polar_parallel_ddl_threads
You can enable the parallel DDL feature by specifying the innodb_polar_parallel_ddl_threads parameter.
Parameter Level Value range Description innodb_polar_parallel_ddl_threads Session [1~8] Default value: 1
Specifies the number of concurrent threads for each DDL operation. The default value 1 indicates that single-threaded DDL operations are executed. Assume that the value of this parameter is not 1. When you perform operations to create secondary indexes, the parallel DDL feature is automatically enabled.
Optimization A
If your requirements cannot be met after the parallel DDL feature is enabled, you can use the
Optimization A
feature. The Optimization A feature can further optimize the sorting procedure or the procedure of creating index trees when indexes are created.Note TheOptimization A
feature is in internal preview. Therefore, you cannot enable this feature as needed. If you need to use this feature, Submit a ticket to contact technical support.
Performance testing
- Test environment
- A PolarDB for MySQL 8.0 cluster of Standard Edition is available. The specification of the cluster is 16 cores and 128 GB.
- The storage space of the cluster is 50 TB.
- Schema of the test table
Execute the following statement to create a table that is named
t0
:CREATE TABLE t0( a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
- Data of the test table
Execute the following statement to 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 an actual test, replace
$table_size
with the number of records in your table, such as1000000
. - This test uses tables that have 1,000,000, 10,000,000, 100,000,000, and 1,000,000,000 rows of records, 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 an actual test, replace
- Test method
Test the increased rate of execution efficiency of the DDL operations that create secondary indexes on the
b
field of theINT
data 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 volume of data. - Test results
- The following figure shows the acceleration ratio of parallel DDL after only the innodb_polar_parallel_ddl_threads parameter is enabled.
- The following figure shows the acceleration ratio of parallel DDL after the innodb_polar_parallel_ddl_threads parameter is enabled and the
Optimization A
feature is used for optimization.
- The following figure shows the acceleration ratio of parallel DDL after only the innodb_polar_parallel_ddl_threads parameter is enabled.