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.
The version of the PolarDB cluster need to be one of the followings:
- PolarDB for MySQL 8.0 and the revision version is 126.96.36.199.7 or later.
- PolarDB for MySQL 5.7and the revision version is 188.8.131.52.7 or later.
For more information about how to confirm the cluster version, see Query the kernel version number.
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.
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.
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.
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.
If your requirements cannot be met after the parallel DDL feature is enabled, you can use the
Optimization Afeature. The Optimization A feature can further optimize the sorting procedure or the procedure of creating index trees when indexes are created.Note The
Optimization Afeature 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.
- 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
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_sizewith the number of records in your table, such as
- 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
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 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 Afeature is used for optimization.