innodb_flush_log_at_trx_commit: What 0, 1, and 2 Really Cost at 3 AM
We flipped innodb_flush_log_at_trx_commit to 2 and commit p99 dropped from 11ms to 0.8ms — a beautiful graph. Eleven weeks later a kernel panic proved what that graph cost: 47 orders the app had confirmed and InnoDB had not.
The graph was gorgeous, and that should have made me more suspicious. Commit p99 on our checkout primary dropped from 11 milliseconds to 0.8 the afternoon we set innodb_flush_log_at_trx_commit = 2, after a storage migration to a network volume had turned every commit into a fsync lottery. Deploys stopped timing out, the order-confirmation latency alert went quiet, and the change got logged as a win in the incident review. Eleven weeks later the box kernel-panicked on a firmware bug at 03:17, mysqld came back clean on the reboot, InnoDB crash recovery ran in nine seconds, and by lunch the finance reconciliation job had found 47 orders that the application had confirmed to customers and the database did not have. Every one of them committed in the final second before the panic — the window where their redo records lived in the operating system’s page cache, written by mysqld but never forced to the platter, because we had told InnoDB that once a second was often enough. That is the entire trade hiding inside this variable, and the reason I now treat its value as a business decision that happens to be expressed as a my.cnf line.
What do the three values actually do at commit time?
They decide when a committed transaction’s redo records reach durable storage: at 1 InnoDB writes the log buffer to the redo log files and fsyncs them at every commit, at 0 it writes and flushes roughly once per second in the background while commits wait for nothing, and at 2 it writes to the operating system’s page cache at commit but fsyncs only once per second. The context that makes this make sense: InnoDB is a write-ahead-log engine, so a transaction is durable when its redo is on stable storage, not when the data pages are — those get flushed lazily by the checkpoint machinery, and the redo capacity notes cover that side of the story. The default, 1, is what makes the D in ACID real: COMMIT does not return until the fsync completes, so a transaction that reported success survives any crash, including pulling the power cord. The once-per-second background flush at 0 and 2 is paced by innodb_flush_log_at_timeout, default 1 second, which is where the “you lose about a second” rule of thumb comes from. The variable is dynamic — SET GLOBAL applies to new commits immediately — which matters for the load-window trick later, and also means a hotfix can change your durability posture without a restart, for better and for worse.
-- the durability posture of this instance, one query
SELECT @@innodb_flush_log_at_trx_commit, @@sync_binlog,
@@innodb_flush_log_at_timeout, @@innodb_redo_log_capacity;
-- is the fsync actually the bottleneck? fsyncs per second:
SHOW GLOBAL STATUS LIKE 'Innodb_os_log_fsyncs';
-- and log buffer stalls (should be ~0):
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
-- change it live (takes effect for subsequent commits)
SET GLOBAL innodb_flush_log_at_trx_commit = 2;How much does the fsync at 1 really cost?
Anywhere from a hundred microseconds to tens of milliseconds per commit, and the spread is almost entirely a property of your storage, which is why the first move is measuring fsync latency rather than relaxing durability. On local NVMe with power-loss protection, an fsync lands in the drive’s capacitor-backed cache and returns in 100 to 300 microseconds — at that price, value 1 costs an ordinary OLTP workload nearly nothing, especially because group commit batches many transactions into a single flush when concurrency is high. On network-attached volumes the story changes: our migrated instance was paying 2 to 4 milliseconds per fsync at p50 and spiking past 10 whenever the volume’s burst credits ran dry, and with sync_binlog = 1 also in force, every commit was paying that toll twice — once for the redo log, once for the binary log. The interaction is the part people miss: innodb_flush_log_at_trx_commit and sync_binlog are separate fsync budgets, and relaxing one while the other stays strict leaves you with most of the latency and half of the durability story; the binlog group commit notes walk through how the two flush paths share the commit pipeline. Before touching either, graph the fsync wait itself — performance_schema’s file I/O summary on the redo and binlog files, or Innodb_os_log_fsyncs rate against commit rate — because a storage fix (better volume class, local instance store with replication for safety, or the flush-method tuning in the fsync tuning notes) often buys back the latency without spending any durability at all.
What exactly do you lose at 0 or 2 when something dies?
You lose up to about a second of transactions that clients were told had committed — and the two values differ in which crash triggers the loss, which is the distinction nearly everyone gets wrong. At 2, mysqld writes redo to the OS page cache at commit, so a mysqld-only crash loses nothing: the operating system still holds those pages, they reach disk on their own, and InnoDB crash recovery finds every committed transaction. The loss at 2 requires the operating system itself to die — kernel panic, power cut, hypervisor kill — taking the unflushed page cache with it. At 0 the exposure is wider: writes sit in InnoDB’s own log buffer until the once-per-second background flush, so even a clean mysqld crash on a healthy OS can lose the final second. The truly nasty property is the silence: the client received OK, the application confirmed the order, the outbox row was never written to a downstream system — there is no error to alert on, only absence, discovered by reconciliation or by a customer. This is also where replication changes the calculus honestly: with semi-synchronous replication, a committed transaction already exists on a replica before the client got its OK, so a dead primary’s lost second is recoverable by failing over — the semi-sync pitfalls and replica crash safety notes cover the parts of that promise that leak. Durability is a property of the whole architecture, and this variable is one term in the sum, not the whole equation.
When is 2 — or even 0 — a legitimate production choice?
When the business can state, in writing, that losing one second of confirmed writes is acceptable, or when another layer already carries the durability — everything else is self-deception with a latency graph. The legitimate cases I run today: an analytics ingestion primary whose data is re-derivable from the upstream event stream runs at 2, because replaying a second of events is a non-event; a fleet sitting behind semi-sync replicas runs at 2 on the primaries, because the second copy acked before the client did; and bulk-load windows run at 0 for the duration, set dynamically before the load and back to 1 after, because a load that crashes gets re-run from its checkpoint anyway. The illegitimate case is “commits felt slow and nobody measured why.” My decision checklist, in order: measure fsync latency and fix storage first; check whether sync_binlog is the real fsync bill; quantify what one lost second means in money or reconciliation labor and get that number signed by someone who owns the product; and if you still land on 2, write the choice and its reason into the runbook next to the variable, so the next engineer inherits a decision instead of a mystery. The 47-order incident ended with the money path back at 1 on faster local storage — the correct fix all along — and a standing rule that durability settings never change without the RPO conversation attached.
How do you keep the setting honest over time?
Alert on the value drifting, graph the fsync cost continuously, and re-litigate the trade whenever the storage or the replication topology changes, because the right answer is a function of both. Drift matters because the variable is dynamic: a well-meaning on-call can relax it during an incident and the relaxation quietly becomes permanent — the SET PERSIST drift notes are the companion piece on exactly that failure mode. The metrics that keep you honest are cheap: Innodb_os_log_fsyncs as a rate against commits per second tells you whether group commit is batching well, the redo file I/O latency from performance_schema tells you what value 1 currently costs, and your reconciliation jobs — if you have them, and you should — tell you whether the last incident’s durability posture matched the one in my.cnf. Revisit the setting at every storage migration, every failover-topology change, and every traffic step-change; the value that was right on local NVMe behind semi-sync replicas is not automatically right on a network volume with async replicas. Treat it like a firewall rule: documented, reviewed, and never set-and-forget.
Where MonPG stands on MySQL
For MonPG product capabilities and setup information, see the MySQL monitoring page. Use the diagnostics in this article to identify the measurements and operational checks your deployment needs.