PolarDB:Physical replication-based semi-sync in PolarDB for MySQL

Background

Standard MySQL provides semisynchronous replication based on the binary log. In this mode, the primary must wait for a secondary to confirm that it has received the binary log generated by a transaction before the transaction can be committed. This synchronization method introduces extra latency and affects the write performance of the primary.

PolarDB for MySQL uses a physical replication architecture to efficiently synchronize the redo log between the primary and secondary zones. This approach significantly improves semisynchronous replication efficiency and reduces performance loss on the primary. Under high-concurrency workloads, the performance degradation is about 10% compared to asynchronous replication.

Compared to the binary log-based semisynchronous replication in standard MySQL, the physical replication-based approach in PolarDB for MySQL offers higher synchronization efficiency. During transaction execution, redo log records are generated and streamed to the secondary zone in real time. Therefore, at commit time, the primary only needs to wait for the corresponding redo log record to be successfully synchronized to the secondary zone. In contrast, the traditional binary log-based semisynchronous mechanism generates the complete binary log only at commit time, and the primary can return a success message to the client only after the entire binary log is synchronized.

How it works

PolarDB for MySQL semisynchronous replication uses a physical replication architecture to synchronize data between the primary and secondary zones by using redo logs. For a write request, redo log records are generated when data is modified in the primary zone and are then synchronized over the physical replication link. The primary must wait for the secondary zone to acknowledge receipt of the redo log before the corresponding write transaction can return a success response.

• Maximum wait time before commit

  To prevent issues in the secondary zone from indefinitely blocking write requests in the primary zone, the kernel limits the maximum wait time before a write transaction commits. If the secondary zone does not send an acknowledgment within this period, the primary automatically commits the transaction.

• Adaptive mechanism

  In extreme cases, the secondary zone may fail to send timely acknowledgments to the primary zone. This can cause every write request to wait until it times out before committing, leading to performance loss. To prevent this issue, semi-sync includes an adaptive mechanism that dynamically monitors network communication between the primary and secondary zones. If timeouts occur frequently, the system automatically falls back to asynchronous replication to ensure that writes in the primary zone are not affected. When the system detects that synchronization has returned to normal, it automatically re-enables semi-sync.

Applicability

Your cluster must meet the following requirements:

• Product edition: Enterprise Edition and Standard Edition

• Engine version:

  • MySQL 8.0.1:

    • Revision version 8.0.1.35.1 or later: Semi-sync can be enabled.

    • Revision version 8.0.1.1.40 or later: Semi-sync can be enabled, and the adaptive mechanism is supported.

    • Revision version 8.0.1.1.44.2 or later: Semi-sync can be enabled, and the innodb_polar_wait_slave_reply_max_time parameter is available to control the default maximum wait time before a write transaction commits. The default value is 500 ms.

  • MySQL 8.0.2: Revision version 8.0.2.2.32 and later: Semi-sync can be enabled, and the adaptive mechanism and the innodb_polar_wait_slave_reply_max_time parameter are also supported.

Precautions

RPO and RTO:

• With asynchronous replication, a cross-zone automatic switchover is lossy. The RPO is less than 100 ms in most cases and less than 60s in the worst-case scenario. Evaluate the impact before you use this feature.

• Enabling semisynchronous replication reduces performance by about 10%. The default transaction commit wait time is 500 ms. If this time is exceeded, the system falls back to asynchronous replication and no longer waits for synchronization to the secondary zone. When no fallback occurs, the RPO is 0.

• The RTO is less than 30s for both asynchronous and semisynchronous replication.

Performance tests

Test method: This test compares the queries per second (QPS) performance of clusters with identical specifications across three modes: PolarDB for MySQL with asynchronous replication, PolarDB for MySQL with semi-sync, and standard MySQL with semi-sync.

Test tool: Sysbench (oltp_write_only).

Test specifications: 16-core, 64 GB.

Tested version: PolarDB for MySQL 8.0.1 with revision version 8.0.1.35.1. Performance may vary slightly from the latest version.

Data volume: 10 tables, with 10 million rows per table.

The results show that in high-concurrency scenarios, enabling semisynchronous replication reduces performance by about 10%. Under any level of concurrency, the performance of redo log-based semisynchronous replication in PolarDB for MySQL is superior to that of binary log-based semisynchronous replication in MySQL.

FAQ

Q1: Why does performance degrade by more than 10% after I enable semi-sync?

A1: In high-concurrency scenarios, the best-case performance degradation is approximately 10%. This is because redo logs are processed in batches, which effectively reduces the overhead from network latency. In low-concurrency scenarios, the performance improvement from batching is less significant, which can lead to more severe performance degradation. With a single write thread, redo I/O cannot be batched, and the added network round-trip delay from enabling semisynchronous replication significantly reduces performance.

Q2: Why can't I see the innodb_polar_wait_slave_reply_max_time parameter in the console? How should I adjust it to an appropriate value?

A2: You can modify this parameter only on PolarDB for MySQL Enterprise Edition clusters with major version 8.0.1 and revision version 8.0.1.1.44.2 or later. If you cannot find the parameter in the console, first verify that your cluster version meets the requirements. If it does not, you can perform a version upgrade.

You do not need to modify this parameter in most cases. The default value is 500 ms. If you have specific requirements, such as ensuring that a transaction must wait for the secondary zone to be synchronized before it commits, you can increase this value. However, the default of 500 ms is sufficient for most scenarios. If you want to limit the transaction wait time, you can decrease the parameter value. Note that if you set the value to be very small, such as 0 or 1 ms, it may cause the semi-sync mode to fall back to asynchronous replication. This is because network latency between availability zones is typically within 1 ms, and semisynchronous replication requires at least one network round-trip. Therefore, consider your environment's network latency when modifying this parameter.

Q3: When does the semi-sync adaptive mechanism take effect? Can I enable semi-sync but disable the adaptive mechanism?

A3: Currently, when you enable semi-sync, the adaptive mechanism is also enabled by default. It dynamically monitors the synchronization status between the primary and secondary zones and makes real-time adjustments. You cannot disable the adaptive mechanism separately. If you want the semi-sync feature to remain active, you can set innodb_polar_wait_slave_reply_max_time to a larger value. The adaptive mechanism uses this parameter to detect timeouts.

Q4: The RPO is 0 when semi-sync does not fall back. Is this "fallback" the same as when the adaptive mechanism dynamically disables semi-sync?

A4: They are different concepts. "No fallback" means that a transaction is committed only after its redo log has been synchronized to the secondary zone. In this case, an RPO of 0 can be strictly guaranteed. However, the adaptive mechanism monitors at the network packet level, not the transaction level. When the adaptive mechanism dynamically disables semi-sync, it is usually because multiple transactions have already been committed due to synchronization timeouts. In other words, even if semi-sync is enabled and not disabled by the adaptive mechanism, the RPO is not strictly 0. A few transactions might be committed due to synchronization timeouts, but this is a rare event. Therefore, the semi-sync feature ensures that the RPO is close to 0.