PolarDB:DDL physical replication optimization

Prerequisites

The PolarDB cluster meets one of the following version requirements. For more information about how to query the cluster engine version, see Engine versions.

• A PolarDB for MySQL 8.0.1 cluster whose revision version is 8.0.1.1.10 or later.

• A PolarDB for MySQL 5.7 cluster whose revision version is 5.7.1.0.10 or later.

Limitations

• 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, because the execution of these operations consumes only a small amount of resources.

• This feature is not supported for PolarDB for MySQL 8.0.2 or 5.6.

Background

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 must 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 severely 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 replication latency for parsing redo logs generated by DDL operations on read-only nodes can be reduced to approximately 0.4%.

Enable physical replication optimization

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

Performance testing

• Preparations

  • Test environment

    • A PolarDB for MySQL 8.0 cluster (including one primary node and one read-only node) that has 16 CPU cores and 128 GB of memory.

    • The storage capacity of the cluster is 50 TB.

  • Schema

    Create a table that is named t0.

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

  • Data

    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 million, 10 million, 100 million, and 1 billion rows of data are used in this test.

  • The DDL operations used in the test.

    • add primary key

    • add secondary Index

    • optimize table

• Test method

  • Test 1: Compare 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: Compare 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 with or without 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.

• 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 million, 10 million, 100 million, and 1 billion rows of data are used in this test. The following figure shows the test result.

    • 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 million, 10 million, 100 million, and 1 billion rows of data are used in this test. The following figure shows the test result.

  • Test 2

    • Enable innodb_polar_use_sample_sort and innodb_polar_use_parallel_bulk_load of the Parallel DDL feature. When innodb_bulk_load_page_grained_redo_enable is set to ON or OFF, test the time (in seconds) required to perform the add secondary Index operation 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.

    • Disable innodb_polar_use_sample_sort and innodb_polar_use_parallel_bulk_load of the parallel DDL feature. When innodb_bulk_load_page_grained_redo_enable is set to ON or OFF, test the time (in seconds) required to perform the add secondary Index operation 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.

  • 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 (reads per second)	10965	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 (reads per second)	9243	7578	7669	-	-
      Peak replication latency (s)	0.8	14.67	211	-	-

Contact Us

If you have any questions about DDL operations, please feel free to reach out to Contact us.