Parallel DDL

Scope

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

Considerations

After you enable parallel DDL, the consumption of hardware resources such as CPU, memory, and I/O increases with the number of parallel threads. This may affect other SQL operations that run at the same time. When you set a large number of parallel threads, the process uses more memory. If there is not enough available memory, an out-of-memory (OOM) error may occur and affect cluster stability. Therefore, use parallel DDL during off-peak hours or when hardware resources are sufficient.

Limits

Parallel DDL acceleration supports DDL operations that create primary key indexes and secondary indexes (excluding full-text indexes, spatial indexes, and secondary indexes on virtual columns).

Background information

Traditional DDL operations are designed based on single cores and traditional hard disk drives, so DDL operations on large tables take a long time and incur excessively high latency. For example, when you create a secondary index, a DDL operation with excessively high latency blocks subsequent DML query operations that depend on the new index. The development of multi-core processors provides hardware support for parallel DDL to use more threads, and the adoption of solid state disks (SSDs) makes random access latency close to sequential access latency. Therefore, using parallel DDL to accelerate index creation on large tables is particularly important.

Usage

• innodb_polar_parallel_ddl_threads

  You can enable parallel DDL by using the following innodb_polar_parallel_ddl_threads parameter:

  Parameter	Level	Description
  innodb_polar_parallel_ddl_threads	Session	The number of parallel threads for each DDL operation. Valid values: 1 to 16. Default value: 1, which indicates that DDL is executed in single-threaded mode. If the value of this parameter is not 1, parallel DDL is automatically enabled when a secondary index is created. Note When the value of this parameter is 1, the system enables two parallel threads by default.

• innodb_parallel_build_primary_index

  You can use the following innodb_parallel_build_primary_index parameter to control whether parallel DDL is allowed when a primary key index is created:

  Note

  To use the parallel primary key index creation feature, go to Quota Center. Find the row whose Quota ID is polardb_mysql_pddl_for_pk, and in the Actions column, click Apply to request a trial.

  Parameter	Level	Description
  innodb_parallel_build_primary_index	Global	Specifies whether parallel DDL is allowed when a primary key index is created. Valid values: ON: allows parallel DDL when a primary key index is created OFF: does not allow parallel DDL when a primary key index is created (default)

• innodb_polar_use_sample_sort

  If enabling parallel DDL alone does not meet your requirements, you can use the innodb_polar_use_sample_sort parameter to further optimize the sorting performed during index creation.

  Parameter	Level	Description
  innodb_polar_use_sample_sort	Session	Specifies whether to enable the sample sort optimization feature. Valid values: ON: enables the sample sort optimization feature OFF: disables the sample sort optimization feature (default)

• innodb_polar_use_parallel_bulk_load

  If the preceding features still do not meet your requirements, you can also use the innodb_polar_use_parallel_bulk_load parameter to further optimize the construction of the index tree.

  Parameter	Level	Description
  innodb_polar_use_parallel_bulk_load	Session	Specifies whether to enable the parallel bulk load optimization feature. Valid values: ON: enables the parallel bulk load optimization feature OFF: disables the parallel bulk load optimization feature (default)

Performance test

• Test environment

  • A Standard Edition PolarDB for MySQL 8.0 cluster with 16 cores and 128 GB of memory.

  • The storage capacity of the cluster is 50 TB.

• Test table schema

  Create a table named t0 by using the following statement:

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

• Test table data

  Generate test data by using the following statement:

  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 actual number of records in the table, such as 1000000.

  • This test uses tables that contain 1,000,000, 10,000,000, 100,000,000, and 1,000,000,000 records, as well as a table that contains 1 TB of data.

  • The test table that contains 1 TB of data is generated by using Sysbench. For more information about how to use Sysbench, see Test tools.

• Test method

  This test uses different numbers of parallel threads (that is, it sets the innodb_polar_parallel_ddl_threads parameter to 1, 2, 4, 8, 16, and 32). For tables of different data volumes, the test measures the ratio of improvement in DDL execution efficiency achieved by enabling parallel DDL to create a secondary index. The index is created on a column whose data type is INT and whose name is b.

• Test results

  • The following figure shows the parallel DDL speedup ratio when only the innodb_polar_parallel_ddl_threads parameter is enabled.

  • The following figure shows the parallel DDL speedup ratio when both the innodb_polar_parallel_ddl_threads and innodb_polar_use_sample_sort parameters are enabled.

  • The following figure shows the parallel DDL speedup ratio when the innodb_polar_parallel_ddl_threads, innodb_polar_use_sample_sort, and innodb_polar_use_parallel_bulk_load parameters are enabled.

Related videos

Contact us

If you have any questions about DDL operations, you can search for the DingTalk group number and join the group for consultation. You can directly @ the experts in the group with your questions. The group also has a PolarDB for MySQL assistant online 24/7 to answer your questions. DingTalk group number: 15375044501.