PolarDB supports DDL physical replication optimization. This feature optimizes the key paths of writing redo logs on the primary node and using redo logs on read-only logs. This feature greatly accelerates the execution of DDL operations on the primary node and parsing of redo logs that are generated by DDL operations on read-only nodes. This topic describes how to use the DDL physical replication optimization feature.

Prerequisites

The version of the Cluster Edition cluster is ApsaraDB PolarDB MySQL-compatible edition 8.0 and the revision version is 8.0.1.1.10 or later. For more information about how to check the version, see Query the kernel version number.

Limits

This feature supports only DDL operations that are used to create primary keys or secondary indexes. These indexes do not include full-text indexes and spatial indexes.

This feature is not required for DDL operations that only modify metadata, such as the renaming operations. The execution of these operations is efficient.

Background information

PolarDB decouples computing from storage resources and allows the primary node and read-only nodes to share the same stored data. This reduces storage costs and improves the availability and reliability of the cluster. PolarDB uses physical replication technology to achieve data consistency between the primary node and read-only nodes based on the shared storage. It also reduces the I/O overheads caused by fsync operations on binary logs.

Data in InnoDB is indexed by B-tree indexes. In most cases, you need to rebuild or create B-tree indexes for slow DDL operations. For example, you need B-tree indexes to create primary keys, create secondary indexes, or optimize tables. This results in a large number of redo logs. However, operations on redo logs are often performed on the key paths of DDL operations. This increases the execution time of DDL operations. In addition, the physical replication technology requires read-only nodes to parse and apply the newly generated redo logs. A large number of redo logs generated by DDL operations may seriously affect the log synchronization process of read-only nodes, or even cause read-only nodes to become unavailable.

To fix the preceding issues, PolarDB provides the DDL physical replication optimization feature, which optimizes the key paths of writing redo logs on the primary node and using redo logs on read-only nodes. This way, the execution time of the DDL operation of creating primary keys on the primary node can be reduced to approximately 20.6%. The time for parsing redo logs generated by DDL operations on read-only nodes can be reduced to approximately 0.4%.

Use physical replication optimization

You can specify the following parameter to enable the DDL physical replication optimization feature.

Parameter Level Description
innodb_bulk_load_page_grained_redo_enable Global Specifies whether to enable the DDL physical replication optimization feature. Valid values:
  • ON: enables DDL physical replication optimization.
  • OFF: disables DDL physical replication optimization. This is the default value.

Performance testing

  • Preparations
    • Test environment
      • A 16-Core 128 GB cluster of ApsaraDB PolarDB MySQL-compatible edition 8.0 is used. The cluster contains a primary node and a read-only node.
      • The storage capacity of the cluster is 50 TB.
    • Table schema

      Execute the following statement to create a table that is named t0:

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

      Execute the following statement to randomly insert data into the table:

      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();
      Note
      • In the test, replace $table_size with the number of records in your table, such as 1000000.
      • Tables that contain 1,000,000, 10,000,000, 100,000,000, and 1,000,000,000 rows are used in this test.
    • The DDL operations used in the test.
      • add primary key
      • add secondary Index
      • optimize table
  • Test method
    • Test 1: Test the time that is required to perform different DDL operations on tables with different numbers of rows when the physical replication optimization feature is enabled or disabled.
    • Test 2: Test the time that is required to execute add secondary Index on a table of 1 billion rows when you use the physical replication optimization feature together with Parallel DDL.
    • Test 3: Test the performance of the read-only node when the physical replication optimization feature is enabled or disabled. The metrics of performance include the node status, peak CPU usage, and replication latency. In this test, the number of concurrent DDL operations on the primary node in a cluster is 1, 2, 4, 6, and 8, and the cluster contains tables of 1 billion rows.
  • Test results
    • Test 1
      • When innodb_bulk_load_page_grained_redo_enable is set to ON or OFF, test the time (in seconds) required to perform the add primary key(a) operation on tables with different numbers of rows. Tables that contain 1,000,000, 10,000,000, 100,000,000, and 1,000,000,000 rows are used in this test. The following figure shows the test result.1
      • When innodb_bulk_load_page_grained_redo_enable is set to ON or OFF, test the time (in seconds) required to perform the optimize table operation on tables with different numbers of rows. Tables that contain 1,000,000, 10,000,000, 100,000,000, and 1,000,000,000 rows are used in this test. The following figure shows the test result.2
    • Test 2
      • Enable Parallel DDL and Optimization A. When innodb_bulk_load_page_grained_redo_enable is set to ON or OFF, test the time (in seconds) required to execute add secondary Index on a table of 1 billion rows. In this test, the innodb_polar_parallel_ddl_threads parameter that specifies the number of parallel threads is set to 1, 2, 4, 8, 16, and 32. The following figure shows the test result.3
      • Disable Optimization A in Parallel DDL. When innodb_bulk_load_page_grained_redo_enable is set to ON or OFF, test the time (in seconds) required to execute add secondary Index on a table of 1 billion rows. In this test, the number of parallel threads is set to 1, 2, 4, 8, 16, and 32. The following figure shows the test result.4
    • Test 3
      • Test the performance of the read-only node when innodb_bulk_load_page_grained_redo_enable is set to ON. In this test, the number of concurrent DDL operations on the primary node in the cluster is 1, 2, 4, 6, and 8, and the cluster contains tables of 1 billion rows. The following table shows the test result.
        Number of concurrent DDL operations 1 2 4 6 8
        Status of the read-only node Normal Normal Normal Normal Normal
        Peak CPU usage (%) 1.86 1.71 1.76 2.25 2.36
        Peak memory usage (%) 10.37 10.80 10.88 11 11.1
        Read IOPS (times per second) 834 10762 10305 10611 10751
        Peak replication latency (s) 0 0.73 0.87 0.93 0.03
      • Test the performance of the read-only node when innodb_bulk_load_page_grained_redo_enable is set to OFF. In this test, the number of concurrent DDL operations on the primary node in the cluster is 1, 2, 4, 6, and 8, and the cluster contains tables of 1 billion rows. The following table shows the test result.
        Note
        • When the number of concurrent DDL operations is 4, the test results show the data before the read-only node becomes unavailable.
        • The hyphen (-) in the table indicates that the DDL operations fail to be performed in scenarios of a specific number of concurrent DDL operations. Therefore, no test result is returned.
        Number of concurrent DDL operations 1 2 4 6 8
        Status of the read-only node Normal Normal Unavailable Unavailable Unavailable
        Peak CPU usage (%) 4.2 9.5 10.3 - -
        Peak memory usage (%) 22.15 23.55 68.61 - -
        Read IOPS (times per second) 9243 7578 7669 - -
        Peak replication latency (s) 0.8 14.67 211 - -