Pre-flight Checklist
Primary Contact Name
Genze Wu
Primary Contact Email
genze.wgz@alibaba-inc.com
Company / Organization
Alibaba
Role
Software Engineer
Additional Authors / Contributors
Component
Replication
Target Release (Optional)
Future Release
Roadmap Section
Performance & Observability
Related Issues / Pull Requests / References (Optional)
No response
Executive Summary
1. Background and Problem Statement
In standard MySQL source–replica replication, events generated by a transaction are first buffered in the source's binlog cache. The complete transaction is written to the binlog file only when it commits. A binlog dump thread then reads the committed transaction from the binlog file and sends it to the replica.
On the replica, the I/O thread writes the transaction to the relay log. SQL worker threads subsequently read and apply the transaction.
This commit-oriented pipeline works well for ordinary transactions, but it creates substantial replication lag for long-running, large transactions and DDL operations. The replica cannot begin processing such a transaction until the source has committed it and written the full transaction to the binlog. The replica then repeats most or all of the execution after the source has already finished.
Large transactions can also destabilize semi-synchronous replication. Sending the binlog for a large transaction to a replica can take a considerable amount of time and block the dump thread for several seconds. During that interval, other unrelated small transactions cannot be transferred promptly and therefore cannot commit until the large transaction has finished transferring. While the binlog for a large transaction is being transferred, the throughput of the entire instance may temporarily fall to zero. If the transfer takes longer than the semi-synchronous replication timeout, replication falls back to asynchronous mode, weakening durability guarantees.
2. Binlog Real-Time Replication
To reduce replication lag for large transactions and DDL operations and to make semi-synchronous replication more reliable, we introduce the Binlog Real-Time Replication (BRR) feature. BRR transfers the binlog events of uncommitted large transactions and DDL operations to the replica and executes them concurrently on the replica.
With this feature, the lifecycle of a large transaction or DDL operation is shown in the diagram above. Even if the transaction or DDL operation takes a long time to execute, the replica experiences no significant replication lag.
Performance jitter and timeouts in semi-synchronous replication caused by large transactions are also eliminated.
This feature is available in Alibaba Cloud RDS for MySQL.
2.1 High-Level Architecture
- While large transactions and DDL operations are executing, the source streams their binlog events—referred to as BRR events—to the replica. For large transactions, the dump thread reads BRR events from the binlog cache. For DDL operations, BRR events are generated from the DDL statements.
- The replica stores BRR events in a relay log cache.
- The replica concurrently applies BRR events in an extra worker thread, which stops and waits at the
before_commit stage of the transaction or DDL operation.
- The source sends a message to the replica when a large transaction or DDL operation commits or rolls back.
- For a large transaction, the replica converts the relay log cache into a regular relay log.
- Based on the message from the source, the replica commits or rolls back the large transaction or DDL operation.
2.2 Relay Log Cache
The relay log cache is an IO_CACHE object similar to the binlog cache. The only difference is that the binlog cache uses an unlinked temporary file, whereas the relay log cache uses a linked temporary file. Each transaction or DDL has one relay log cache.
2.2.1 Relay Log Cache for DDL
The I/O thread receives BRR events from the source and writes them to the relay log cache. An extra worker thread reads the BRR events from the relay log cache's temporary file and applies them concurrently.
For a DDL operation, the relay log cache contains the DDL statement and, if the operation commits on the source, its GTID. Otherwise, it contains a rollback message. The relay log cache is deleted after the extra worker thread finishes applying the DDL operation.
2.2.2 Relay Log Cache for Large Transactions
For a large transaction, in addition to transferring BRR events between the I/O thread and the extra worker thread, the relay log cache can be converted into a regular relay log after the source commits the transaction. This reduces the time required to transfer the binlog from the source to the replica and prevents large transactions from destabilizing semi-synchronous replication.
Some space is reserved at the beginning of the relay log cache for the GTID event and relay log header events. The GTID event is sent to the replica after the transaction commits on the source. The relay log header events are written when the relay log cache is converted into a regular relay log. The empty event acts as a placeholder to fill excess reserved space.
2.3 Extra Worker Thread
To avoid blocking normal worker threads, we introduce extra worker threads to execute BRR events. Each large transaction or DDL operation is assigned to an extra worker thread, which continuously reads from the relay log cache and applies the events. When the extra worker thread receives a commit message or an XID event, it sets the GTID and commits the large transaction or DDL operation.
Whenever a normal worker thread receives a transaction, it determines from the GTID whether the transaction is a BRR transaction. If it is, the normal worker thread waits for the extra worker thread to finish committing the transaction and then skips the transaction identified by that GTID.
2.4 Conflict Avoidance
2.4.1 Large-Transaction Dependency Boundary
After InnoDB locks and modifies some rows, it records the corresponding binlog event in the binlog cache. Transactions committed before that event was recorded may have modified the same row. Transactions attempting to modify the row after that point cannot complete the conflicting modification until the current transaction commits and releases its lock.
BRR therefore records, through gtid_executed, all transactions that had committed on the source when an event was written to the binlog cache. Before the replica applies that event, it ensures that all transactions within the recorded predecessor boundary have already been applied. This establishes the required happens-before relationship and prevents the extra worker from overtaking a potentially conflicting committed transaction.
2.4.2 DDL Dependency Boundary
For copy DDL, transactions that commit before the DDL operation acquires an X lock use the old table structure, whereas transactions that execute after the DDL operation acquires the X lock use the new table structure. Before the replica begins concurrently applying the DDL operation in an extra worker thread, all transactions that preceded the acquisition of the X lock on the source must have been applied.
For online DDL, transactions that commit before the DDL operation acquires an X lock during the commit stage use the old table structure, whereas transactions that execute after the DDL operation acquires the X lock during the commit stage use the new table structure. Before the DDL operation enters the commit stage in an extra worker thread, all transactions that preceded the acquisition of the X lock during the commit stage on the source must have been applied.
3. Limitations
- This feature supports only GTID-based replication.
- This feature does not currently support Group Replication.
- This feature may consume additional network bandwidth and replica computing resources when a large transaction or DDL operation is rolled back, although this occurs very rarely.
4. What's Next?
4.1 An Extra Thread for the Current Dump and I/O Thread
Currently, the dump thread on the source not only reads the binlog and sends normal events but also reads the binlog cache and sends BRR events. The I/O thread on the replica similarly handles both normal events and BRR events. This additional work may negatively affect performance in semi-synchronous replication mode.
We plan to add an extra dump thread, an extra I/O thread, and a separate TCP connection between them.
4.2 Resuming After a Replication Interruption
Currently, this feature does not support resuming interrupted work. When replication is interrupted, all relay log caches are deleted, and all transactions and DDL operations being applied by extra worker threads are rolled back. When replication restarts, the concurrent application of those large transactions and DDL operations must begin again from the start.
We plan to make this feature resumable in the future.
4.3 Instant Rollback
In many clusters, a replica serves as a hot standby for the source. If the source crashes, the replica must be promoted to the source within a short time. If a large transaction or DDL operation is being concurrently applied on the replica, it must be rolled back immediately. A DDL operation can be rolled back quickly, but rolling back a large transaction may take a long time. In the current version, large transactions are rolled back in the background, so the rollback does not block the stopping of replication or the high-availability switchover. However, the transaction's row and table locks are not released until the rollback finishes, which may block new transactions on the newly promoted source.
To avoid this prolonged lock duration after a high-availability switchover, we plan to support the instant rollback of large transactions.
We have already developed a feature that instantly rolls back large transactions during crash recovery, and we plan to support instant rollback for large transactions in Binlog Real-Time Replication.
If you are interested in the algorithm of instant rollback of large transactions during crash recovery, see the following work that our team contributed to MariaDB:
User / Developer Stories
No response
Proposed Scope
No response
Out of Scope / Future Work
No response
References
No response
Functional Requirements
No response
Non-functional Requirements
No response
Impact Areas
Summary of the Approach
No response
User Interface
No response
Configuration / Knobs
No response
Observability
No response
User Procedure
No response
Security Considerations
No response
Compatibility and Behavior Changes
No response
Block Diagram
No response
Interface Specification
No response
Proposed Implementation Plan
No response
QA Notes
No response
Pre-flight Checklist
Primary Contact Name
Genze Wu
Primary Contact Email
genze.wgz@alibaba-inc.com
Company / Organization
Alibaba
Role
Software Engineer
Additional Authors / Contributors
Component
Replication
Target Release (Optional)
Future Release
Roadmap Section
Performance & Observability
Related Issues / Pull Requests / References (Optional)
No response
Executive Summary
1. Background and Problem Statement
In standard MySQL source–replica replication, events generated by a transaction are first buffered in the source's binlog cache. The complete transaction is written to the binlog file only when it commits. A binlog dump thread then reads the committed transaction from the binlog file and sends it to the replica.
On the replica, the I/O thread writes the transaction to the relay log. SQL worker threads subsequently read and apply the transaction.
This commit-oriented pipeline works well for ordinary transactions, but it creates substantial replication lag for long-running, large transactions and DDL operations. The replica cannot begin processing such a transaction until the source has committed it and written the full transaction to the binlog. The replica then repeats most or all of the execution after the source has already finished.
Large transactions can also destabilize semi-synchronous replication. Sending the binlog for a large transaction to a replica can take a considerable amount of time and block the dump thread for several seconds. During that interval, other unrelated small transactions cannot be transferred promptly and therefore cannot commit until the large transaction has finished transferring. While the binlog for a large transaction is being transferred, the throughput of the entire instance may temporarily fall to zero. If the transfer takes longer than the semi-synchronous replication timeout, replication falls back to asynchronous mode, weakening durability guarantees.
2. Binlog Real-Time Replication
To reduce replication lag for large transactions and DDL operations and to make semi-synchronous replication more reliable, we introduce the Binlog Real-Time Replication (BRR) feature. BRR transfers the binlog events of uncommitted large transactions and DDL operations to the replica and executes them concurrently on the replica.
With this feature, the lifecycle of a large transaction or DDL operation is shown in the diagram above. Even if the transaction or DDL operation takes a long time to execute, the replica experiences no significant replication lag.
Performance jitter and timeouts in semi-synchronous replication caused by large transactions are also eliminated.
This feature is available in Alibaba Cloud RDS for MySQL.
2.1 High-Level Architecture
before_commitstage of the transaction or DDL operation.2.2 Relay Log Cache
The relay log cache is an
IO_CACHEobject similar to the binlog cache. The only difference is that the binlog cache uses an unlinked temporary file, whereas the relay log cache uses a linked temporary file. Each transaction or DDL has one relay log cache.2.2.1 Relay Log Cache for DDL
The I/O thread receives BRR events from the source and writes them to the relay log cache. An extra worker thread reads the BRR events from the relay log cache's temporary file and applies them concurrently.
For a DDL operation, the relay log cache contains the DDL statement and, if the operation commits on the source, its GTID. Otherwise, it contains a rollback message. The relay log cache is deleted after the extra worker thread finishes applying the DDL operation.
2.2.2 Relay Log Cache for Large Transactions
For a large transaction, in addition to transferring BRR events between the I/O thread and the extra worker thread, the relay log cache can be converted into a regular relay log after the source commits the transaction. This reduces the time required to transfer the binlog from the source to the replica and prevents large transactions from destabilizing semi-synchronous replication.
Some space is reserved at the beginning of the relay log cache for the GTID event and relay log header events. The GTID event is sent to the replica after the transaction commits on the source. The relay log header events are written when the relay log cache is converted into a regular relay log. The empty event acts as a placeholder to fill excess reserved space.
2.3 Extra Worker Thread
To avoid blocking normal worker threads, we introduce extra worker threads to execute BRR events. Each large transaction or DDL operation is assigned to an extra worker thread, which continuously reads from the relay log cache and applies the events. When the extra worker thread receives a commit message or an XID event, it sets the GTID and commits the large transaction or DDL operation.
Whenever a normal worker thread receives a transaction, it determines from the GTID whether the transaction is a BRR transaction. If it is, the normal worker thread waits for the extra worker thread to finish committing the transaction and then skips the transaction identified by that GTID.
2.4 Conflict Avoidance
2.4.1 Large-Transaction Dependency Boundary
After InnoDB locks and modifies some rows, it records the corresponding binlog event in the binlog cache. Transactions committed before that event was recorded may have modified the same row. Transactions attempting to modify the row after that point cannot complete the conflicting modification until the current transaction commits and releases its lock.
BRR therefore records, through
gtid_executed, all transactions that had committed on the source when an event was written to the binlog cache. Before the replica applies that event, it ensures that all transactions within the recorded predecessor boundary have already been applied. This establishes the required happens-before relationship and prevents the extra worker from overtaking a potentially conflicting committed transaction.2.4.2 DDL Dependency Boundary
For copy DDL, transactions that commit before the DDL operation acquires an X lock use the old table structure, whereas transactions that execute after the DDL operation acquires the X lock use the new table structure. Before the replica begins concurrently applying the DDL operation in an extra worker thread, all transactions that preceded the acquisition of the X lock on the source must have been applied.
For online DDL, transactions that commit before the DDL operation acquires an X lock during the commit stage use the old table structure, whereas transactions that execute after the DDL operation acquires the X lock during the commit stage use the new table structure. Before the DDL operation enters the commit stage in an extra worker thread, all transactions that preceded the acquisition of the X lock during the commit stage on the source must have been applied.
3. Limitations
4. What's Next?
4.1 An Extra Thread for the Current Dump and I/O Thread
Currently, the dump thread on the source not only reads the binlog and sends normal events but also reads the binlog cache and sends BRR events. The I/O thread on the replica similarly handles both normal events and BRR events. This additional work may negatively affect performance in semi-synchronous replication mode.
We plan to add an extra dump thread, an extra I/O thread, and a separate TCP connection between them.
4.2 Resuming After a Replication Interruption
Currently, this feature does not support resuming interrupted work. When replication is interrupted, all relay log caches are deleted, and all transactions and DDL operations being applied by extra worker threads are rolled back. When replication restarts, the concurrent application of those large transactions and DDL operations must begin again from the start.
We plan to make this feature resumable in the future.
4.3 Instant Rollback
In many clusters, a replica serves as a hot standby for the source. If the source crashes, the replica must be promoted to the source within a short time. If a large transaction or DDL operation is being concurrently applied on the replica, it must be rolled back immediately. A DDL operation can be rolled back quickly, but rolling back a large transaction may take a long time. In the current version, large transactions are rolled back in the background, so the rollback does not block the stopping of replication or the high-availability switchover. However, the transaction's row and table locks are not released until the rollback finishes, which may block new transactions on the newly promoted source.
To avoid this prolonged lock duration after a high-availability switchover, we plan to support the instant rollback of large transactions.
We have already developed a feature that instantly rolls back large transactions during crash recovery, and we plan to support instant rollback for large transactions in Binlog Real-Time Replication.
If you are interested in the algorithm of instant rollback of large transactions during crash recovery, see the following work that our team contributed to MariaDB:
User / Developer Stories
No response
Proposed Scope
No response
Out of Scope / Future Work
No response
References
No response
Functional Requirements
No response
Non-functional Requirements
No response
Impact Areas
Summary of the Approach
No response
User Interface
No response
Configuration / Knobs
No response
Observability
No response
User Procedure
No response
Security Considerations
No response
Compatibility and Behavior Changes
No response
Block Diagram
No response
Interface Specification
No response
Proposed Implementation Plan
No response
QA Notes
No response