Use DDL read-ahead to speed up DDL execution - PolarDB

DDL read-ahead speeds up DDL operations on large tables by prefetching data pages before the index build scan reaches them, reducing I/O wait time during the operation.

When you run an ALTER TABLE statement on a large table, InnoDB reads every data page sequentially to rebuild indexes or restructure rows. DDL read-ahead prefetches pages ahead of the scan so they are already available when needed.

Prerequisites

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

PolarDB for MySQL 8.0, revision version 8.0.1.1.28 or later
PolarDB for MySQL 5.7, revision version 5.7.1.0.22 or later
PolarDB for MySQL 5.6, revision version 5.6.1.0.34 or later

To check your cluster's revision version, see Query the engine version.

I/O impact and scheduling

DDL read-ahead dedicates additional threads to prefetching pages, which increases I/O consumption for the duration of the DDL operation. This additional I/O load can affect concurrent SQL workloads. Run DDL read-ahead during off-peak hours to minimize impact on production traffic.

Parameters

Use the following two global parameters to enable DDL read-ahead and tune its prefetch volume.

`loose_innodb_polar_ddl_build_index_readahead`

Parameter	Level	Default	Valid values	Description
`loose_innodb_polar_ddl_build_index_readahead`	Global	OFF	ON, OFF	Enables or disables DDL read-ahead.

`loose_innodb_polar_ddl_build_index_readahead_page_num`

Parameter	Level	Default	Valid values	Description
`loose_innodb_polar_ddl_build_index_readahead_page_num`	Global	64	32–256	Number of pages to prefetch per read-ahead request. Each page is 16 KB. Higher values prefetch more data per request, which benefits large sequential scans but consumes more I/O bandwidth.

Start with the default of 64 pages (1 MB per prefetch). Increase toward 256 pages (4 MB per prefetch) for very large tables where the DDL scan is the dominant I/O consumer and the cluster has available I/O headroom.

Performance test

The following benchmark compares DDL execution time with and without read-ahead enabled.

Test environment

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

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

Insert 100,000,000 rows using the following stored procedure:

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);

Restart PolarDB processes before running each test to clear the buffer pool cache. This ensures results reflect disk I/O performance rather than cached data.

Results

All tests use loose_innodb_polar_ddl_build_index_readahead_page_num=256 when read-ahead is enabled.

ALTER TABLE table_1 ADD INDEX name_index (seller_name):

DDL read-ahead	Time (seconds)
Disabled	485
Enabled	412

ALTER TABLE table_1 ADD COLUMN c1 varchar(100) after id:

DDL read-ahead	Time (seconds)
Disabled	264
Enabled	159

Contact us

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

PolarDB:DDL read-ahead