Use asynchronous I/O for DDL to reduce DDL operation time - PolarDB

The DDL asynchronous I/O feature is added in PolarDB. It is time-consuming to create indexes in large tables by executing the DDL statement. You can use the DDL asynchronous I/O feature to shorten the time to create indexes.

Prerequisites

Before you begin, ensure your PolarDB cluster meets one of the following version requirements:

PolarDB for MySQL 8.0.2, revision version 8.0.2.2.6 or later
PolarDB for MySQL 8.0.1, revision version 8.0.1.1.29 or later

To check your current version, see Query the engine version.

Limitations

DDL asynchronous I/O does not apply to:

Full-text indexes
Spatial indexes

Enable DDL asynchronous I/O

Set the innodb_polar_ddl_async_io parameter to ON in your cluster parameter settings. For instructions, see Configure cluster and node parameters.

Parameter	Level	Default	Valid values	Description
`innodb_polar_ddl_async_io`	Global	`OFF`	`ON`, `OFF`	Enables or disables DDL asynchronous I/O

Performance test

The following test measures the impact of DDL asynchronous I/O on large-table index creation.

Test environment

PolarDB for MySQL 8.0, 8 CPU cores, 32 GB memory, 50 TB storage capacity

Schema

CREATE TABLE `table_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seller_id` bigint(20) DEFAULT NULL,
  `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `gmt_create` varchar(30) DEFAULT NULL,
  `update_time` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Test data

The following stored procedure inserts 100,000,000 rows into table_1:

delimiter ||
CREATE PROCEDURE populate_0(IN NUM INT)
BEGIN
  DECLARE sid INT;
  DECLARE suffix_name INT;
  DECLARE i INT;
  SET sid=1000;
  SET suffix_name=10;
  SET i=1;
  START TRANSACTION;
  WHILE i <= NUM
  DO
    INSERT INTO table_1(seller_id,seller_name,gmt_create,update_time)
      VALUES(sid,CONCAT('sellername',suffix_name),NOW(),NOW());
    SET suffix_name=suffix_name+1;
    SET sid=sid+1;
    SET i=i+1;
  END WHILE;
  COMMIT;
END ||
delimiter ;

CALL populate_0(100000000);

Test method and results

Run the following statement with DDL asynchronous I/O enabled and then disabled, and compare the execution time:

ALTER TABLE table_1 ADD INDEX name_index (seller_name);

DDL asynchronous I/O	Time (seconds)
Enabled	368
Disabled	485

Enabling DDL asynchronous I/O reduced index creation time by approximately 24% in this test.

Contact us

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

PolarDB:Asynchronous I/O for DDL