[Proposal]: Binlog Commit Optimization For Large Transaction #77

Pre-flight Checklist

• I have searched existing GitHub issues and did not find a duplicate proposal.
• I have removed or redacted sensitive information.

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)

• Related Issues:
  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.
• References:
  https://bugs.mysql.com/bug.php?id=118332

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

• SQL syntax or statements
• Configuration options or system variables
• Command-line options or utilities
• User-visible behavior
• Observability
• Security or privilege model
• Protocol or replication behavior
• Upgrade / downgrade compatibility
• Performance or resource usage
• Files, persistence, or metadata formats
• APIs or internal interfaces
• Testing or QA coverage needs

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