Parallel DDL

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 The Optimization 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 as 1000000.
  • 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.
• Test method

  Test the increased rate of execution efficiency of the DDL operations that create secondary indexes on the b field of the INT 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.