Parallel DDL

Traditional DDL is designed based on a single core and traditional disks. It takes a long time to perform DDL operations on large tables and the latency is too high. For example, when creating secondary indexes, DDL operations with high latency block subsequent DML queries that depend on new indexes.

GaussDB(for MySQL) supports parallel DDL. When database hardware resources are idle, you can use parallel DDL to accelerate DDL execution, preventing subsequent DML operations from being blocked and shortening the DDL operation window.

Constraints

This function is supported when the kernel version is 2.0.45.230900 or later.
This function is only suitable for BTREE secondary indexes.
This function is not suitable for primary key indexes, spatial indexes, and fulltext indexes. If an SQL statement for concurrently creating indexes contains a primary key index, spatial index, or fulltext index, the client will receive an alarm indicating that the operation does not support concurrent index creation. The statement is executed in single-thread index creation mode. Assume that multiple threads are specified when a primary key index is modified. An alarm will also be reported and the index is created through a single thread.

Enabling Parallel DDL

**Table 1** Parameter description
Parameter	Level	Description
innodb_rds_parallel_index_creation_threads	Global, Session	Number of threads for concurrently creating indexes. If the value is greater than 1, concurrent creation is performed. Otherwise, single-thread creation is performed. Default value: 8. You are advised to set the value to be half of the number of CPU cores and be at most the value of innodb_rds_parallel_index_creation_threads_max.

Example

Prepare a sysbench table with 100 million data records.
Figure 1 Viewing table information
Create an index in the k field of the table.
Create an index for the k field in the table. If a single thread is used to create the index by default, it should take 146.82 seconds.
Figure 2 Creating an index using a single thread
Set innodb_rds_parallel_index_creation_threads = 4 to use four threads to create the index.
It should take 38.72 seconds to create the index, 3.79 times faster than with a single thread.
Figure 3 Creating an index using multiple threads
Assume that a primary key index needs to be modified. Even if multiple threads are specified, a warning will be received and the index is created using just a single thread.
Figure 4 Modifying a primary key index