PolarDB for MySQL supports the parallel DDL feature. When available hardware resources exist in a cluster, the parallel DDL feature can speed up the execution of DDL operations. This prevents subsequent DML operations from being blocked and shortens the execution windows of DDL operations.

Prerequisites

When you create secondary indexes, your PolarDB for MySQL cluster must meet one of the following requirements:
  • A cluster of PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.1.7 or later.
  • A cluster of PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.10 or later.
  • A cluster of PolarDB for MySQL 5.7 whose revision version is 5.7.1.0.7 or later.
When you create primary key indexes, your PolarDB for MySQL cluster must meet one of the following requirements:
  • A cluster of PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.9 or later.
  • A cluster of PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.31 or later.

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.

    ParameterLevelDescription
    innodb_polar_parallel_ddl_threadsSessionThe 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.
    ParameterLevelDescription
    innodb_parallel_build_primary_indexGlobalSpecifies 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.

    ParameterLevelDescription
    innodb_polar_use_sample_sortSessionSpecifies 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.

    ParameterLevelDescription
    innodb_polar_use_parallel_bulk_loadSessionSpecifies 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. A
    • 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. Acceleration ratio of parallel DDL
    • 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. B