PolarDB:DDL multi-way merging and sorting

Prerequisites

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

• PolarDB for MySQL 8.0.2 with revision version 8.0.2.2.5 or later

• PolarDB for MySQL 8.0.1 with revision version 8.0.1.1.28 or later

• PolarDB for MySQL 5.7 with revision version 5.7.1.0.23 or later

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

How it works

Index creation in InnoDB proceeds in three phases:

1. Scan: The clustered index is scanned and sort entries are written to the sort buffer. When the buffer fills, entries are flushed to a temporary intermediate file as a sorted run.

2. Merge sort: After all runs are written, InnoDB merges them. The default two-way sort merges two runs at a time, requiring multiple passes over the intermediate file. N-way merging combines more runs per pass, reducing total passes and intermediate I/O.

3. B-tree insertion: The fully sorted entries are inserted into the B-tree index.

Increasing the number of ways reduces intermediate file passes at the cost of additional memory: each additional way requires one extra sort buffer of innodb_sort_buffer_size bytes.

Limitations

• Full-text indexes and spatial indexes are not supported.

• Memory usage scales with the number of ways. Activating N-way merging increases the occupied memory size of the cluster by (N+1) × innodb_sort_buffer_size bytes.

Configure multi-way merging

Set innodb_polar_parallel_merge_ways at the session level before running your DDL statement.

The setting applies to the current session only and reverts to the default after the session ends.

Example: Enable eight-way merging and create an index on a large table.

SET innodb_polar_parallel_merge_ways = 8;
ALTER TABLE table_1 ADD INDEX name_index (seller_name);

Performance test

The following test compares two-way and eight-way merging on a 100-million-row table to show the impact of increasing the number of ways.

Test environment

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: 100,000,000 rows generated 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);

Test operation: ALTER TABLE table_1 ADD INDEX name_index (seller_name);

Results

Eight-way merging reduced index creation time by approximately 27% compared to two-way merging on this dataset and hardware configuration.

Contact us

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

Related topics

• Query the engine version

• Contact us