Use DDL multi-way merging and sorting to reduce index sorting time - PolarDB

PolarDB introduces the DDL multi-way merging and sorting feature. DDL operations that create indexes on large tables are usually time-consuming. You can use DDL multi-way merging and sorting to reduce the time spent on index sorting during index creation.

Prerequisites

PolarDB cluster must meet 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 the version of your cluster, see Query the engine version.

Considerations

DDL multi-way merging and sorting is not supported for full-text indexes or spatial indexes.
When you use DDL multi-way merging and sorting, cluster memory usage increases slightly. If N-way merge sort is enabled, the amount of cluster memory used during sorting is (N+1)*innodb_sort_buffer_size bytes.

Usage

Specify the number of ways for merge sort by using the following innodb_polar_parallel_merge_ways parameter:

Parameter	Level	Description
innodb_polar_parallel_merge_ways	Session	Specifies the number of ways for merge sort during index creation. Valid values: 2 to 16. Default value: 2, which indicates two-way merge sort. A value greater than 2 enables multi-way merge sort.

Performance test

Test environment
- A PolarDB for MySQL 8.0 cluster with 8 cores and 32 GB of memory.
- The storage capacity of the cluster is 50 TB.

Test table schema

Create a table named table_1 by using the following statement:

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 table data

Generate test data by using the following statement:

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
After the data is inserted, configure 8-way merge sort and 2-way merge sort separately, and run the SQL statement alter table table_1 add index name_index (seller_name); to test the improvement in DDL execution efficiency:
Number of merge sort ways
Time consumed (seconds)
8-way merge sort
353
2-way merge sort
485

Contact us

If you have questions about DDL operations, Contact us.

Number of merge sort ways	Time consumed (seconds)
8-way merge sort	353
2-way merge sort	485