Pre-flight Checklist
Primary Contact Name
Libing Song
Primary Contact Email
slb.songlibing@gmail.com
Company / Organization
Alibaba
Role
Software Engineer
Additional Authors / Contributors
Component
Replication
Target Release (Optional)
26.7
Roadmap Section
AI & Cloud
Related Issues / Pull Requests / References (Optional)
Executive Summary
There are a few hot contentions in MySQL's commit path. On MySQL-8.0, the contentions of redo log system, transaction system were optimized. Now binary log system is more hot on MySQL-8.0.
Two things of binlog group commit limit the performance:
- The two sequential I/O operations—redo log sync and binlog sync—add significant latency
- Serialized binlog group commit limits scalability at high concurrency.
This proposal addresses both:
- Binlog in Redo: reduces the sync of redo log from the commit path, but still keep crash-safe.
- Binlog Parallel Flush: introduces a shared binlog buffer. Only the order-sensitive steps stay serialized. The parts most expensive are performed in parallel. It improves the performance at high concurrency.
User / Developer Stories
- As a DBA, I want MySQL instances to perform better at high concurrency on large servers.
- As a DBA, I want to set sync_binlog=1 for better durability, but that costs too much performance, especially on cloud storage where latency is relatively high.
Proposed Scope
Binlog in Redo
- Write binlog events into the redo log after the transaction is prepared, and sync them with the prepare record together.
- Add a redo record type for server-layer data (MLOG_SERVER_DATA) and a server-layer redo recovery phase that dispatches such records to registered appliers.
- Implement a binlog applier that restores the binlog events that never reached the binlog file, at startup.
- Move the binlog file sync off the commit path into a dedicated background syncer thread.
Binlog Parallel Flush
- Introduce a shared binlog buffer that transactions flush their binlog caches into, plus dedicated writer and syncer threads that drain it to the binlog file.
- Add system variables to enable/disable both features, with the current behavior as fallback.
Supported Mode
Out of Scope / Future Work
- Support for storage engines other than InnoDB. Engines without a redo log keep the current path.
- Support for binlog_order_commits
References
DimitriK's (dim) Weblog, MySQL Performance: 8.0 RW & Binlog impact: http://dimitrik.free.fr/blog/archives/2018/05/mysql-performance-80-rw-binlog-impact.html
Functional Requirements
- It MUST has variables to enable/disable the optimizations.
- Durability MUST be unchanged. With sync_binlog=1 and innodb_flush_log_at_trx_commit=1, the binlog events of any transaction reported as committed MUST survive a crash.
- After crash recovery, the binlog file content MUST be equivalent to what the current design would produce: same events, same order, same GTIDs.
Non-functional Requirements
No response
Impact Areas
Summary of the Approach
Binlog in Redo
- Binlog buffer is introduced. Transactions flush their binlog caches to the binlog buffer instead of the binlog file.
- Transactions write the binlog events to the redo log and sync the redo log.
- Transactions commit in the InnoDB engine
- Transactions wait for their binlog events to be flushed to binlog file. It is possibly flushed before the wait. And this step is optional, mainly for test purpose.
- Transactions still commit in groups, leader commits all transactions in the group. Multiple groups run in parallel.
- The binlog file is synced by the background thread asynchronously. Any binlog events not yet persisted to the binlog file can be recovered from the redo log during startup.
Binlog Parallel Flush
The parallel flush optimization can also improve the throughput when sync_binlog is not set to 1, e.g.1000. In this situation, transactions will not persist binlog events into redo.
- Transactions flush their binlog caches to the binlog buffer instead of binlog file.
- Transactions wait for their binlog events to be written to binlog file by the writer thread.
- Transactions commit in the InnoDB engine
- Transactions still commit in groups, leader commits all transactions in the group. Multiple groups run in parallel.
- The binlog file is synced by the background thread asynchronously.
- Since sync_binlog is not set to 1, user won't expect data consistency if crash happens. And data inconsistency happens only when the os crashes(with innodb_flush_log_at_trx_commit = 2).
Persist Only Binlog File
This is an alternative solution for reducing one I/O. Binlog in redo eliminates the sync of binary log, this solution eliminates the sync of redo log.
- Transactions flush their binlog caches to the binlog buffer instead of binlog file.
- Transactions wait for their prepared state to be written to redo log. The state probably has been written into redo before this step.
- Transactions wait for their binlog events to be synced to binlog file by the syncer thread.
- Transactions commit in the InnoDB engine
- Transactions still commit in groups, leader commits all transactions in the group. Multiple groups run in parallel.
- Redo log is synced by the background thread asynchronously. Data and binlog inconsistency happens only when os crash happens. In the inconsistency, data is less than binary log. The lost data could be recovered by applying binlog events at startup.
Binlog in Redo vs Persist Only Binlog File
- Binlog in Redo is more complex than Persist Only Binlog file, it couples binlog and redo log together. Since binlog events will be written twice(one in redo and one is binlog), it increases the total I/O. That may not be good for I/O-bound workloads. Persist Only Binlog File perfectly avoids them.
- Binlog in Redo achieves crash safety by recovering the lost binlog events from the redo log. That is fast and hard to get wrong. Persist Only Binlog File achieves crash safety by applying the binlog events of lost data at startup. It is slower and more error-prone. If any error happens, the server will fail to startup.
However, applying binlog is only needed when an os crash happens. It is rare to run into that situation, failover probably is a better choice. And since redo is written before binlog, the lost data should be little. So I think it is worth bringing the solution Persist Only Binlog File here to discuss and testing, althrough I chose Binlog in Redo when I implemented it in AliSQL.
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
Libing Song
Primary Contact Email
slb.songlibing@gmail.com
Company / Organization
Alibaba
Role
Software Engineer
Additional Authors / Contributors
Component
Replication
Target Release (Optional)
26.7
Roadmap Section
AI & Cloud
Related Issues / Pull Requests / References (Optional)
Executive Summary
There are a few hot contentions in MySQL's commit path. On MySQL-8.0, the contentions of redo log system, transaction system were optimized. Now binary log system is more hot on MySQL-8.0.
Two things of binlog group commit limit the performance:
This proposal addresses both:
User / Developer Stories
Proposed Scope
Binlog in Redo
Binlog Parallel Flush
Supported Mode
Out of Scope / Future Work
References
DimitriK's (dim) Weblog, MySQL Performance: 8.0 RW & Binlog impact: http://dimitrik.free.fr/blog/archives/2018/05/mysql-performance-80-rw-binlog-impact.html
Functional Requirements
Non-functional Requirements
No response
Impact Areas
Summary of the Approach
Binlog in Redo
Binlog Parallel Flush
The parallel flush optimization can also improve the throughput when sync_binlog is not set to 1, e.g.1000. In this situation, transactions will not persist binlog events into redo.
Persist Only Binlog File
This is an alternative solution for reducing one I/O. Binlog in redo eliminates the sync of binary log, this solution eliminates the sync of redo log.
Binlog in Redo vs Persist Only Binlog File
However, applying binlog is only needed when an os crash happens. It is rare to run into that situation, failover probably is a better choice. And since redo is written before binlog, the lost data should be little. So I think it is worth bringing the solution Persist Only Binlog File here to discuss and testing, althrough I chose Binlog in Redo when I implemented it in AliSQL.
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