PolarDB:Parallel DDL

Prerequisites

For more information about how to check the cluster version, see Query the engine version.

Usage

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.

Limitations

The parallel DDL feature can accelerate only the DDL operations that are used to create primary key indexes and secondary indexes. The secondary indexes exclude full-text indexes, spatial indexes, and the secondary indexes on virtual columns.

Background

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

• innodb_polar_parallel_ddl_threads

  You can enable the parallel DDL feature by specifying the innodb_polar_parallel_ddl_threads parameter.

  Parameter	Level	Description
  innodb_polar_parallel_ddl_threads	Session	The number of concurrent threads for each DDL operation. Valid values: 1 to 16. 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.

• innodb_parallel_build_primary_index

  You can allow the parallel DDL feature when you create primary key indexes by specifying the innodb_parallel_build_primary_index parameter:

  Note

  This parameter is in the canary release. To apply for a trial use of the parameter, go to Quota Center. Click Apply in the Actions column corresponding to PolarDB hotspot row parameter adjustment.

  Parameter	Level	Description
  innodb_parallel_build_primary_index	Global	Specifies whether to allow the parallel DDL feature when you create primary key indexes. Default value: OFF. Valid values: ON OFF

• innodb_polar_use_sample_sort

  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: ON OFF

• innodb_polar_use_parallel_bulk_load

  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: ON OFF

Performance test

• 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.

• Schema

  Create a table that is named t0.

  CREATE TABLE t0(
  a INT PRIMARY KEY,
  b INT) ENGINE=InnoDB;

• Data

  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_size with the number of records that you want in your table, such as 1000000.

  • 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.

• 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 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.

Contact Us

If you have any questions about DDL operations, please feel free to reach out to Contact us.