Binlog optimization

AliSQL 8.0.44-2 includes two opt-in binlog mechanisms for different transaction shapes. Persist Binlog Into Redo V2 changes the durability path for eligible transaction events. Binlog Cache Free Flush avoids copying an eligible spilled large-transaction cache a second time during commit.

Both retain normal binlog group commit as a fallback when their guards do not pass.

Choose by transaction shape

Mechanism	Intended path	Default	Key size control	DuckDB boundary in this release
Persist Binlog Into Redo V2	Eligible transactional binlog caches carried by InnoDB redo, then written by background threads	OFF	At most 1 MiB by default; configurable 0–10 MiB	Can run with duckdb_mode=NONE; enabling is rejected while DuckDB mode is active
Binlog Cache Free Flush	Spilled large transaction cache finalized and renamed as the next binlog	OFF	Larger than 256 MiB by default; minimum configurable threshold 10 MiB	Current builds that include DuckDB use normal group commit for this path, even with duckdb_mode=NONE

These are implementation eligibility boundaries, not workload promises. Confirm the selected path with production-shaped transactions and crash-recovery tests.

Persist Binlog Into Redo V2

For an eligible commit, binlog events enter the InnoDB redo durability boundary. Background writer and synchronization threads persist the binlog file; crash recovery can rebuild a missing binlog tail from redo before normal binlog recovery continues.

Configure the required commit-order settings before enabling it:

[mysqld]
sync_binlog=1
innodb_flush_log_at_trx_commit=1
binlog_order_commits=OFF
replica_preserve_commit_order=OFF
persist_binlog_to_redo=ON

The current implementation requires sync_binlog=1, binlog_order_commits=OFF, and replica_preserve_commit_order=OFF. innodb_flush_log_at_trx_commit=1 is the recommended full-durability baseline, not an implementation gate.

An individual transaction falls back to normal group commit when, for example, it uses a nontransactional table, has a statement binlog cache, exceeds persist_binlog_to_redo_size_limit, requires commit ordering, or has an unsupported active 2PC topology.

Variable	Default	Notes
persist_binlog_to_redo	OFF	Global, dynamic enablement
persist_binlog_to_redo_size_limit	1 MiB	Maximum eligible transaction cache; 0–10 MiB
binlog_buffer_size	20 MiB	Read-only background ring buffer; configure at startup
sync_binlog_interval	50000 µs	Background binlog synchronization interval
wait_binlog_flush	ON	Waits for the corresponding file write, not necessarily file synchronization

Binlog Cache Free Flush

When a large transaction cache spills to a temporary file, the normal commit path copies that file into the binlog. Free Flush reserves binlog header space in the temporary file, finalizes it, and renames it as the next binlog file, avoiding the second large copy.

Enable it only when the binlog and InnoDB are the registered 2PC participants:

SET GLOBAL binlog_cache_free_flush_limit_size = 268435456;
SET GLOBAL binlog_cache_free_flush = ON;

Configuration alone does not prove that a transaction used Free Flush. Eligibility also requires a spilled and finalized transaction cache, no statement-cache event or incident, no binlog/cache-file encryption, an open binlog, no update to mysql.gtid_executed, and no registered 2PC engine other than InnoDB.

Variable	Default	Documented range
binlog_cache_free_flush	OFF	ON, OFF
binlog_cache_free_flush_limit_size	256 MiB	10 MiB to ULLONG_MAX bytes

DuckDB has two different boundaries

Do not collapse the two mechanisms into one DuckDB rule. Binlog in Redo does not count the inactive DuckDB registration for eligibility and can run while duckdb_mode=NONE; Free Flush currently falls back in a build that registers the DuckDB 2PC participant even when DuckDB mode is NONE.

Qualification checklist

1. Verify effective durability and commit-order variables after startup.
2. Exercise eligible and deliberately ineligible transactions; confirm fallback remains correct.
3. Measure redo pressure, binlog write latency, storage bandwidth, and commit latency together.
4. Test transactions on both sides of the configured size threshold.
5. Include crash injection, restart recovery, replication catch-up, backup, and restore in qualification.
6. Keep normal group commit available; moving work from the foreground path does not remove the underlying I/O.

Authoritative references

• Persist Binlog Into Redo V2 guide
• Binlog Cache Free Flush guide
• AliSQL 8.0.44-2 feature release notes
• Performance Optimization of RDS AliSQL for Binlog — earlier managed-service engineering context; use the repository guides above for current open-source configuration.