PolarDB:Parallel DDL

Applicability

For more information, see Query the version number.

Important notes

Enabling the parallel DDL feature increases the number of parallel threads. This consumes more hardware resources, such as CPU, memory, and I/O, and can affect other SQL operations that are running at the same time. We recommend that you use this feature during off-peak hours or when hardware resources are sufficient.

Limits

The parallel DDL feature currently supports DDL operations that create primary key indexes and secondary indexes. It does not support creating full-text indexes, spatial indexes, or secondary indexes on virtual columns.

Background information

Traditional DDL operations were designed for single-core processors and traditional hard disks. This design can result in long execution times and high latency for DDL operations on large tables. For example, a high-latency DDL operation to create a secondary index can block subsequent DML queries that depend on the new index. The development of multi-core processors provides the hardware support for parallel DDL to use more threads. The widespread use of the Solid State Disk (SSD) makes random access latency comparable to sequential access latency. This makes it especially beneficial to use parallel DDL to accelerate index creation on large tables.

How to use

• innodb_polar_parallel_ddl_threads

  You can enable the parallel DDL feature by setting the innodb_polar_parallel_ddl_threads parameter:

  Parameter	Level	Description
  innodb_polar_parallel_ddl_threads	Session	Controls the number of parallel threads for each DDL operation. Value range: 1 to 16. The default value is 1, which executes a single-threaded DDL. If this parameter value is not 1, parallel DDL is automatically enabled when you create a secondary index. Note When this parameter is set to 1, the system uses two parallel threads by default.

• innodb_parallel_build_primary_index

  You can use the innodb_parallel_build_primary_index parameter to control whether to allow parallel DDL when you create a primary key index:

  Note

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

  Parameter	Level	Description
  innodb_parallel_build_primary_index	Global	Controls whether to allow parallel DDL when creating a primary key index. The valid values are as follows: ON: Allows parallel DDL for primary key index creation. OFF: Disallows parallel DDL for primary key index creation (default).

• innodb_polar_use_sample_sort

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

  Parameter	Level	Description
  innodb_polar_use_sample_sort	Session	The switch for the sample sort optimization feature. The valid values are as follows: 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 performance requirements, you can also use the innodb_polar_use_parallel_bulk_load parameter to further optimize the index tree creation process.

  Parameter	Level	Description
  innodb_polar_use_parallel_bulk_load	Session	The switch for the parallel bulk load optimization feature. The valid values are as follows: 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 cluster has 50 TB of storage space.

• Test table schema

  Create a table named t0 using the following statement:

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

• Test table data

  Generate test data 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

  • When you run the test, replace $table_size with the number of records in the table, such as 1000000.

  • This test uses tables with 1,000,000, 10,000,000, 100,000,000, and 1,000,000,000 records, and a table with 1 TB of data.

  • The 1 TB test table is generated using the Sysbench tool. For more information about how to use the Sysbench tool, see Test tools.

• Test method

  This test measures the performance improvement of creating a secondary index on the b field of the INT data type for tables of different sizes. In this test, parallel DDL is enabled, and the innodb_polar_parallel_ddl_threads parameter is set to 1, 2, 4, 8, 16, and 32 to vary the number of parallel threads.

• Test results

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

  • The following graph 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 graph 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.

Contact us

If you have any questions about DDL operations, please contact technical support.