The symptom was a forty-second commit stall every night at 02:05, sharp enough to see in p99 graphs from across the room. During the stall, every write transaction on the primary queued up — latency alerts for three unrelated services — and then everything released at once and the night went back to normal. The slow log was useless (nothing "slow" had run; everything had waited), and the timing fingered the nightly purge job: one DELETE statement removing nine million expired sessions in a single transaction. The disk told the rest. lsof on the mysqld process showed a 3.1GB file in /tmp, already deleted from the directory but held open by the purge connection — MySQL's binlog cache spill file, invisible to ls, visible only to lsof. The DELETE's changes had been buffered to disk all night, and at commit the server had to read 3.1GB of spilled binlog events back off /tmp and write them into the real binlog before the group commit could close — while every other committing transaction waited in line behind it. That is binlog_cache_disk_use in production: a per-session buffer with a tiny default, a spill path nobody graphs, and a stall that arrives exactly at commit time.
What does the binlog cache actually do during a transaction?
It buffers a transaction's binary log events in per-session memory — default binlog_cache_size is just 32KB — and spills them to a temporary file on disk when the transaction's changes exceed that buffer, holding everything until commit. The design makes sense once you see the constraint: a transaction's events must be written to the binlog atomically at commit, in one contiguous group, interleaved with no other transaction's events. Since MySQL cannot know at first-write whether the transaction will commit or roll back, it stages the events privately per session — memory first, then the spill file — and only publishes them at commit; a rollback simply discards the staging area. There are actually two caches: binlog_cache_size for transactional changes and binlog_stmt_cache_size (also 32KB default) for non-transactional statements, tracked by their own status counters. The spill file lives in tmpdir, is created unlinked so it never shows in a directory listing, and grows to whatever the transaction needs — which is why the deleted-but-open file in lsof is the diagnostic signature. Two global counters tell the whole story at fleet level: Binlog_cache_use counts transactions that used the cache (nearly all of them), and Binlog_cache_disk_use counts transactions whose cache spilled to disk. On a healthy OLTP system the second number is zero or close to it, because normal transactions are a few KB of row events.
-- the two counters that matter, and their ratio
SHOW GLOBAL STATUS LIKE 'Binlog_cache%';
-- Binlog_cache_use 847231105
-- Binlog_cache_disk_use 14321 <-- nonzero = spills happened
-- (Binlog_stmt_cache_* are the non-transactional twins)
-- current sizes
SELECT @@binlog_cache_size, @@binlog_stmt_cache_size,
@@max_binlog_cache_size, @@tmpdir;
-- find the spill files while a big transaction runs
-- (they are unlinked, so ls shows nothing — use lsof)
-- shell: lsof -nP -p $(pidof mysqld) | grep -i deleted
-- mysqld 22107 mysql 63u REG 8,1 3261063168 /tmp/MLa4f9X2 (deleted)Why does the spill hurt exactly at commit time?
Because the spilled events have to be read back from the temp file and written to the real binlog inside the commit path, and group commit serializes that path for everyone — one giant transaction's I/O becomes every concurrent committer's wait. During the transaction itself, spilling is nearly free: sequential writes to /tmp, off the critical path, no one waiting. The bill arrives at COMMIT. The session must copy its staged events — from memory and from the spill file — into the binlog as one group, and while that group is being assembled and flushed, other transactions in the same commit queue stack up behind it. The mechanics of that queue are the subject of the binlog group commit notes; the short version is that group commit amortizes fsync by batching, and a multi-gigabyte group poisons the batch for everyone in it. In our incident, reading 3.1GB back from /tmp took the forty seconds the graphs showed — /tmp was on the same cloud volume as everything else, so the read competed with normal I/O too. The replicas paid next: a 3.1GB transaction ships as one atomic unit, so each replica's SQL thread had to fetch and apply it in one gulp, and replication lag spiked in a step function minutes after the primary recovered. One job, three stalls, all from the same spilled cache.
What does error 1197 mean, and when does max_binlog_cache_size matter?
Error 1197 — Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage. Increase this mysqld variable and try again — means a transaction tried to stage more binlog data than the cache ceiling allows, and the server aborted and rolled it back. The default of max_binlog_cache_size is effectively unlimited (18446744073709547520 bytes), so seeing this error in the wild means someone set the variable deliberately — usually as a safety valve against exactly the disk-fill scenario from my incident, since an unbounded spill file on a small /tmp volume is its own outage mode. The trap is what the abort implies: the transaction is rolled back, and if it had been running for an hour, you now own an hour of rollback — the full penalty described in the kill-and-rollback field notes. So the ceiling trades one failure mode for another: without it, a runaway transaction can fill tmpdir and take the server down with ENOSPC; with it set tight, a legitimate big job dies at the finish line and pays rollback. My answer is to set it as a circuit breaker sized well above any legitimate transaction — if your biggest sanctioned job stages 4GB, a 16GB ceiling catches the accidents without killing the known job — and to fix the job shapes underneath rather than tune the ceiling to fit them.
Should you raise binlog_cache_size, and what does that actually cost?
Raising binlog_cache_size converts disk spills into memory use for the sessions that need it — safe to a point, because the memory is allocated per transaction only as the cache grows, not upfront per connection — but it treats the symptom, and chunking the transaction is usually the real fix. The memory arithmetic first: a session's binlog cache starts small and grows toward binlog_cache_size only while that transaction's events accumulate, so raising the global from 32KB to, say, 4MB does not multiply your memory bill by your connection count; it multiplies it by the number of concurrently big transactions, which is the number you should know. Where raising it genuinely helps: workloads with many medium transactions — 100KB to 2MB each — that spill constantly under the 32KB default; moving the default to a few MB eliminates a steady stream of temp-file I/O for the price of memory you can bound. Where it does not help: the nine-million-row purge, because no sane buffer size holds 3GB of staged events, and you would just be moving the stall from disk to memory pressure. That job's fix was the boring one — DELETE ... LIMIT 50000 in a loop with a commit per batch, the same chunking discipline the long transaction detection setup pushes you toward anyway — after which each batch staged a few MB, committed in milliseconds, and the 02:05 stall vanished from every graph. Rule of thumb: tune the cache for the distribution, chunk the outliers.
How do you monitor spills before they stall your commit path?
Graph the rate of Binlog_cache_disk_use, alert on any sustained nonzero rate, and watch tmpdir free space during batch windows — between those three, a spill problem has nowhere to hide. The counter is cumulative since startup, so graph the rate, and know your baseline: on a properly chunked OLTP workload the rate is zero for days, which makes any blip an event worth thirty seconds of attention. When the rate moves, the next question is always "who," and the answer is the processlist sorted by time plus the lsof trick for open spill files — a connection in state "update" with an hours-old transaction and a multi-GB deleted file in /tmp is the whole diagnosis in two commands. Add tmpdir capacity to the same dashboard, because the failure mode is a volume fill: spill files are invisible to du on the directory (unlinked), so a filling /tmp with no visible large files is itself a signature — df shows it used, ls shows it empty, lsof shows the truth. Finally, fold the check into job review: any new batch job that writes more than a trivial number of rows should state its chunk size in its description, because "one big transaction" is never the right answer to a bulk delete, and the binlog cache is simply where that decision sends its invoice first.
Where MonPG stands on MySQL
I build MonPG, so the honest line: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. The signals in this piece — Binlog_cache_disk_use as a graphed rate, commit-stall latency correlated with the transaction that caused it, tmpdir pressure with spill-file attribution, and replica lag annotated against oversized transactions — are exactly what the MySQL work is designed to surface, so a spill reads as evidence instead of a nightly mystery. 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 today, and the rest of these MySQL field notes live on the blog.