Pre-flight Checklist
Primary Contact Name
Libing Song
Primary Contact Email
slb.songlibing@gmail.com
Company / Organization
Alibaba Cloud
Role
Software Engineer
Additional Authors / Contributors
No response
Component
Replication
Target Release (Optional)
No response
Roadmap Section
Performance & Observability
Related Issues / Pull Requests / References (Optional)
Executive Summary
For transactions whose binlog payload exceeds binlog_cache_size, MySQL
spills the binlog cache to a temporary file on disk. At commit time, this
temporary file is read back into memory and written out into the active
binary log file. For large transactions (multi-GB DDL, large bulk loads,
INSERT ... SELECT, etc.) this means the same data is written to disk twice
under the LOCK_log mutex, generating significant disk and page-cache
pressure and stalling all other writes for the duration of the copy.
Binlog Commit Optimization (BCO) replaces that read-then-write cycle with a
single rename(2). When the feature is enabled and a transaction's binlog
cache is larger than binlog_large_commit_threshold, the on-disk
binlog cache temporary file is converted in place into the next binary log
file:
- A small, adaptive space is reserved at the head of the temporary file
when the cache first spills to disk. This space will hold the binary
log magic, Format_description event, Previous_GTIDs event, GTID event,
plus a padding Empty event.
- On commit the active binary log file is closed and the temporary file
is opened as the new binary log. The front events are written into the
reserved space and a padding Empty event fills any unused part of the
reserved region exactly.
- The temporary file is fsynced, then renamed to a new binary log file
name, registered in the purge index and the binlog index. The server
then rotates to the next regular binary log file.
The result is one fsync of the (already-on-disk) cache file plus a file
rename, instead of streaming the entire cache through the LOCK_log critical
section. Throughput of large transactions improves substantially and small
concurrent transactions are no longer blocked behind multi-GB copies.
User / Developer Stories
- As a DBA loading large datasets with
LOAD DATA, big INSERT ... SELECT
or batched DML, I want commit latency for those transactions to depend
only on fsync of the already-spilled cache file, not on rewriting it
through the binary log.
- As a DBA running an OLTP workload alongside occasional large transactions,
I want short transactions to keep committing while a large transaction is
in its commit phase, instead of stalling behind tens of seconds of
LOCK_log-held binlog copying.
- As an operator I want a single size threshold knob that gates the
optimization, so small transactions pay no overhead and I can opt out
entirely (by setting the threshold higher than any expected
transaction) without restarting the server.
- As a developer reading the binary logs, I expect the resulting files to
be byte-for-byte valid binary logs that mysqlbinlog and replicas
process normally (with one Ignorable padding event at the start of the
file that replicas already know to skip).
Proposed Scope
- New commit-time optimization for any binlog cache larger than a
configurable size threshold (default 64 MiB).
- One new global system variable (
binlog_large_commit_threshold).
- New error-log messages tagging entry into / failure of the optimization.
- Crash-safety of the new commit path (4 crash points are tested).
- Behaviour fallback when prerequisites are not satisfied (small cache,
encryption on, transaction modified mysql.gtid_executed, reserved space
underestimated by a stale m_reserved_size).
Out of Scope / Future Work
- The optimization is silently disabled when binary log encryption is on:
the binlog cache temporary file is encrypted with a per-cache password
that does not match the binary log file's encryption key, so the file
cannot simply be renamed in.
- The optimization is disabled when the transaction has modified
mysql.gtid_executed (postfix change). This avoids a deadlock between
the attachable_trx_rw used to update mysql.gtid_executed from the
prepared-state optimized-commit transaction.
- The optimization is disabled when the trx cache has not spilled to disk
(nothing to rename) and when reserved space at the head of the cache
file turns out to be smaller than the actual front events (rare; the
server logs a warning and falls back).
- Per-file checksum: binary log files produced by the optimization use
checksum alg OFF because the original cache events were written
without the binary log's checksum stream. binlog_checksum is restored
on the next rotation.
References
https://mariadb.com/resources/blog/binlog-commit-optimization-for-large-transaction/
https://help.aliyun.com/en/rds/apsaradb-rds-for-mysql/binlog-cache-free-flush
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
Libing Song
Primary Contact Email
slb.songlibing@gmail.com
Company / Organization
Alibaba Cloud
Role
Software Engineer
Additional Authors / Contributors
No response
Component
Replication
Target Release (Optional)
No response
Roadmap Section
Performance & Observability
Related Issues / Pull Requests / References (Optional)
Support InnoDB-based binary log (transactional binlog stored within InnoDB) #24, Next-Generation MySQL Replication Performance and Scalability Improvements #46 these 2 issues may change the architecture of replication and related code.
https://bugs.mysql.com/bug.php?id=118332
Executive Summary
For transactions whose binlog payload exceeds
binlog_cache_size, MySQLspills the binlog cache to a temporary file on disk. At commit time, this
temporary file is read back into memory and written out into the active
binary log file. For large transactions (multi-GB DDL, large bulk loads,
INSERT ... SELECT, etc.) this means the same data is written to disk twice
under the LOCK_log mutex, generating significant disk and page-cache
pressure and stalling all other writes for the duration of the copy.
Binlog Commit Optimization (BCO) replaces that read-then-write cycle with a
single rename(2). When the feature is enabled and a transaction's binlog
cache is larger than
binlog_large_commit_threshold, the on-diskbinlog cache temporary file is converted in place into the next binary log
file:
when the cache first spills to disk. This space will hold the binary
log magic, Format_description event, Previous_GTIDs event, GTID event,
plus a padding Empty event.
is opened as the new binary log. The front events are written into the
reserved space and a padding Empty event fills any unused part of the
reserved region exactly.
name, registered in the purge index and the binlog index. The server
then rotates to the next regular binary log file.
The result is one fsync of the (already-on-disk) cache file plus a file
rename, instead of streaming the entire cache through the LOCK_log critical
section. Throughput of large transactions improves substantially and small
concurrent transactions are no longer blocked behind multi-GB copies.
User / Developer Stories
LOAD DATA, bigINSERT ... SELECTor batched DML, I want commit latency for those transactions to depend
only on fsync of the already-spilled cache file, not on rewriting it
through the binary log.
I want short transactions to keep committing while a large transaction is
in its commit phase, instead of stalling behind tens of seconds of
LOCK_log-held binlog copying.
optimization, so small transactions pay no overhead and I can opt out
entirely (by setting the threshold higher than any expected
transaction) without restarting the server.
be byte-for-byte valid binary logs that
mysqlbinlogand replicasprocess normally (with one Ignorable padding event at the start of the
file that replicas already know to skip).
Proposed Scope
configurable size threshold (default 64 MiB).
binlog_large_commit_threshold).encryption on, transaction modified mysql.gtid_executed, reserved space
underestimated by a stale
m_reserved_size).Out of Scope / Future Work
the binlog cache temporary file is encrypted with a per-cache password
that does not match the binary log file's encryption key, so the file
cannot simply be renamed in.
mysql.gtid_executed(postfix change). This avoids a deadlock betweenthe
attachable_trx_rwused to updatemysql.gtid_executedfrom theprepared-state optimized-commit transaction.
(nothing to rename) and when reserved space at the head of the cache
file turns out to be smaller than the actual front events (rare; the
server logs a warning and falls back).
checksum alg
OFFbecause the original cache events were writtenwithout the binary log's checksum stream.
binlog_checksumis restoredon the next rotation.
References
https://mariadb.com/resources/blog/binlog-commit-optimization-for-large-transaction/
https://help.aliyun.com/en/rds/apsaradb-rds-for-mysql/binlog-cache-free-flush
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