The worst latency regression I ever caused on purpose came from a single SET PERSIST. Our order-processing cluster ran about 9,000 commits per second at the 11:00 peak, with a p99 commit latency of just under 3 milliseconds. Then a compliance audit asked the fair question: what happens to committed transactions if the machine loses power? Our honest answer was "up to the operating system's whim," because the cluster had been running sync_binlog=0 since before I joined. We flipped it to 1 on a Tuesday afternoon. By Wednesday's peak, p99 commit latency was 41 milliseconds and checkout timeouts were climbing. Nothing else had changed. Every commit was now standing in line behind a binlog fsync, and the line was long.
The fix was not reverting the setting — the durability guarantee was the point. The fix was understanding that MySQL already batches binlog fsyncs through group commit, and that one microsecond-scale delay knob would let those batches grow large enough to make sync_binlog=1 cheap. We set binlog_group_commit_sync_delay to 1000 microseconds, binlog fsyncs per second dropped from roughly 8,700 to about 220, and p99 commit latency settled at 4.2 milliseconds — a hair above where we started, with a real durability guarantee we did not have before. This article is the full reasoning: what the binlog is for, what sync_binlog actually promises at each value, how the delay knobs work, how this interacts with InnoDB's own flush setting, and where to watch the stalls in performance_schema.
What does the binary log actually do for replication and point-in-time recovery?
It is the durable, ordered record of every committed change on the server, and two entirely different consumers depend on it. Replicas connect and stream binlog events to reconstruct the same changes on their own copies — that stream is the whole of MySQL replication, so if you have ever chased a lagging replica, the binlog is where the story starts, and the diagnosis walkthrough in replication lag diagnosis leans on it heavily. Point-in-time recovery is the second consumer: restore last night's backup, then replay binlog from the backup's recorded position up to the moment just before someone dropped the wrong table, and you recover the data the backup alone cannot give you. Both consumers share one requirement that people miss when they tune for speed: the binlog only serves them if it is complete and durable. A binlog event that existed in memory but never reached disk is a transaction the client believes is committed, the replica may or may not have, and your PITR replay definitely does not. That gap is exactly what sync_binlog prices.
What do sync_binlog values 0, 1, and N really guarantee?
They control how often the server asks the operating system to flush the binary log to stable storage, and the guarantees differ by more than most teams assume. With sync_binlog=0, the server never fsyncs the binlog itself — the operating system flushes its page cache whenever it feels like it, which on a healthy system is often within seconds but is a promise from nobody. On an operating-system crash or power loss, the most recent binlog events can vanish, and here is the part that stings: InnoDB may have already made those transactions durable in its own redo log, so after crash recovery the source has rows whose binlog events are gone. Replicas that already streamed those events are now ahead of a source that denies the transactions exist — silent divergence, the worst kind. With sync_binlog=1, every commit group fsyncs the binlog before the commit is acknowledged, so a committed transaction's events survive an OS crash; this is the default in MySQL 8.0, while 5.7 defaulted to 0, which is how so many older clusters run weaker than their operators believe. Values of N greater than 1 fsync every N commit groups — a middle ground that bounds loss to roughly N groups' worth of events, though on a busy server N groups can pass in milliseconds, which is why I consider N a throughput knob rather than a durability tier. My rule: 1 on anything whose loss would generate a support ticket, 0 only on rebuildable analytics nodes.
How does group commit batch fsyncs, and what do the delay knobs trade?
Group commit, in place since MySQL 5.6, lets one fsync durably cover many transactions instead of each transaction paying for its own. Committing transactions line up in a queue; a leader thread takes the queue through three stages — flush, where transactions write their binlog events into the file; sync, where the leader issues one fsync for the whole group; and commit, where each transaction finishes in the storage engine. Under enough load this happens naturally: while the leader is fsyncing, more transactions pile into the next queue, and each fsync covers a bigger group. The problem on our cluster was that 9,000 commits per second sounds like a lot but still meant a commit every 110 microseconds — groups stayed small, and sync_binlog=1 turned most commits into a nearly solo fsync plus the queueing behind everyone else's. The two knobs that widen the batch are binlog_group_commit_sync_delay, which makes the leader wait up to that many microseconds before the sync stage, and binlog_group_commit_sync_no_delay_count, which skips the wait entirely once that many transactions are already queued. We set the delay to 1000 microseconds and the no-delay count to 100:
-- batch binlog fsyncs: wait up to 1ms per group, skip the wait
-- once 100 transactions are queued (both dynamic, no restart)
SET PERSIST binlog_group_commit_sync_delay = 1000;
SET PERSIST binlog_group_commit_sync_no_delay_count = 100;
-- confirm durability is still on
SHOW VARIABLES WHERE Variable_name IN
('sync_binlog', 'binlog_group_commit_sync_delay',
'binlog_group_commit_sync_no_delay_count');The honest cost sheet: every commit can pay up to sync_delay of extra latency even when it would have committed instantly, so at low concurrency this knob is a pure tax — a lone transaction at 3 a.m. waits the full millisecond for a batch that never forms, and the no-delay count only rescues you when a queue already exists. The knob pays off only when commit throughput is high enough that the added microsecond of latency buys a many-times-larger fsync batch, and the delay applies only when sync_binlog is non-zero. We measured before committing: average group size during peak went from about 4 transactions to about 60, fsyncs per second fell by 97%, and the storage array's fsync latency stopped mattering because there were 40 times fewer of them to wait behind.
How does sync_binlog interact with innodb_flush_log_at_trx_commit?
They guard two different logs with independent settings, and the classic 1/1 versus 2/0 matrix is about which crash you are insuring against. innodb_flush_log_at_trx_commit governs InnoDB's redo log: 1 writes and fsyncs the redo at every commit, 2 writes at commit but fsyncs about once per second, 0 does both about once per second. sync_binlog governs the binlog, and because InnoDB and the binlog commit through an internal two-phase commit, crash recovery reconciles them — prepared transactions found in the redo are committed if their binlog events survived and rolled back otherwise, which keeps the source's data and binlog consistent with each other even when a setting is relaxed. The matrix, honestly: 1/1 means committed transactions survive a power loss and the replica stream survives too — the full guarantee, paid for in fsyncs. 2/0 means a MySQL process crash still loses nothing, but an OS crash or power cut can cost roughly a second of commits, and those lost commits may already be on replicas, so you inherit the divergence problem on top of the data loss. The mixed settings are worth knowing: 1/0 protects the data but not the replication stream, which is the exact trap our cluster was in, while 2/1 is an unusual combination that keeps replication durable while accepting redo loss that InnoDB crash recovery will mostly reconcile. If you relax the InnoDB side for throughput, read the checkpoint angle in redo log capacity and checkpoint stalls first — flushing less often also changes how dirty pages pile up. My default remains 1/1 with group commit doing the batching, because it is the only pairing where "committed" means the same thing to the client, the redo log, the binlog, and every replica.
How do you watch binlog fsync stalls in performance_schema?
Look at two wait events: the file I/O wait on the binlog itself, which tells you what the fsyncs cost, and the group-commit condition wait, which tells you how long transactions queue for them. The instrument wait/io/file/sql/binlog counts every read, write, and fsync against binary log files, and file_summary_by_event_name accumulates their timings. The instrument wait/synch/cond/sql/MYSQL_BIN_LOG::COND_done is where follower transactions park while the leader finishes the flush and sync stages — on our cluster it was the single largest contributor to commit latency during that bad Wednesday, and it is the smoking gun whenever sync_binlog=1 starts hurting at peak. The queries I keep around:
-- how much time goes into binlog file I/O, fsync included
SELECT EVENT_NAME, COUNT_STAR,
ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_sec,
ROUND(AVG_TIMER_WAIT / 1000000000, 3) AS avg_ms
FROM performance_schema.file_summary_by_event_name
WHERE EVENT_NAME = 'wait/io/file/sql/binlog';
-- how long transactions wait for group commit to finish
SELECT EVENT_NAME, COUNT_STAR,
ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_sec,
ROUND(AVG_TIMER_WAIT / 1000000000, 3) AS avg_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/synch/cond/sql/MYSQL_BIN_LOG%'
ORDER BY SUM_TIMER_WAIT DESC;Read them as a pair. High average time on the file event with a low no-delay-count queue means your storage is slow per fsync and bigger batches will help; that was us. High COND_done time with already-large groups means the fsyncs themselves are the wall, and the remaining levers are faster storage, relaxing sync_binlog, or accepting the latency as the price of the guarantee. One more sanity check after any tuning change: run mysqlbinlog over a fresh file, or replay it on a test restore, so you are verifying the log is complete rather than trusting the counters that say it was flushed. Counters tell you the fsyncs happened; only a replay tells you the events you needed were in them.
Where MonPG stands on MySQL
I build MonPG, so to be direct: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. The signals in this piece — binlog fsync latency, group-commit queue depth, sync_binlog drift across a fleet, commit-latency percentiles that suddenly correlate with durability settings — are exactly what the MySQL work is designed to surface as timelines instead of Wednesday-morning incidents. The MySQL monitoring (coming soon) page tracks that work as it lands. Until it ships, the same evidence-first approach runs on the PostgreSQL side, and the rest of these MySQL field notes live on the blog.