PolarDB:DDL physical replication optimization

Test setup

Schema:

CREATE TABLE t0(a INT PRIMARY KEY, b INT) ENGINE=InnoDB;

Data: Tables with 1 million, 10 million, 100 million, and 1 billion rows, populated with random values:

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

Replace $table_size with the number of rows to insert, for example 1000000.

DDL operations tested:

• ADD PRIMARY KEY

• ADD INDEX (secondary index)

• OPTIMIZE TABLE

Test 1: DDL execution time on the primary node

Compare the time (in seconds) for ADD PRIMARY KEY and OPTIMIZE TABLE across table sizes, with innodb_bulk_load_page_grained_redo_enable set to ON versus OFF.

Add primary key:

Optimize table:

Test 2: Combined with parallel DDL

Compare the time (in seconds) for ADD INDEX on a 1 billion row table across different thread counts (innodb_polar_parallel_ddl_threads set to 1, 2, 4, 8, 16, and 32), with innodb_bulk_load_page_grained_redo_enable set to ON versus OFF.

With parallel DDL enabled (innodb_polar_use_sample_sort and innodb_polar_use_parallel_bulk_load both enabled):

With parallel DDL disabled (innodb_polar_use_sample_sort and innodb_polar_use_parallel_bulk_load both disabled):

For more information about parallel DDL parameters, see Parallel DDL.

Test 3: Read-only node stability under concurrent DDL

Test the read-only node's status, CPU usage, memory usage, IOPS, and replication latency when the primary node runs 1, 2, 4, 6, and 8 concurrent DDL operations on a 1 billion row table.

Feature enabled (`innodb_bulk_load_page_grained_redo_enable = ON`):

Feature disabled (`innodb_bulk_load_page_grained_redo_enable = OFF`):

For 4 concurrent DDL operations, the data shown is from before the read-only node became unavailable. A hyphen (-) indicates the DDL operations failed at that concurrency level and no result was returned.

With the feature disabled, the read-only node becomes unavailable at 4 or more concurrent DDL operations and replication latency spikes to 211 seconds under 4 concurrent operations. With the feature enabled, the node stays healthy across all tested concurrency levels with replication latency under 1 second.