How does store buffer draining impact log writer performance?
In the high-stakes world of database internals, the Log Writer (LGWR in Oracle, WAL Writer in PostgreSQL) is the ultimate throughput bottleneck. While DBAs often blame "slow disks" for commit latency, the real physical bottleneck often begins inside the CPU's Store Buffer.
To understand why your database is waiting, you have to understand the "hidden" delay of Store Buffer Draining.
Modern CPUs don't write directly to the L1 cache or RAM because that's too slow. Instead, they use a Store Buffer—a tiny, lightning-fast "waiting room" for data.
The Benefit: The CPU core can "fire and forget" a write to the buffer and move on to the next instruction immediately.
The Database Catch: For a Log Writer, "fire and forget" is illegal. A database commit is only valid when the data is globally visible and persistent.
When a database issues a COMMIT, it triggers a Memory Barrier (Fence). This instruction tells the CPU: "Stop everything. Empty the Store Buffer into the L1 cache/Memory before proceeding."
This process of Store Buffer Draining impacts Log Writer performance in three distinct ways:
When the Log Writer prepares a redo log block, it fills the Store Buffer with change vectors. When it hits the memory barrier to finalize that block for I/O, the CPU pipeline stalls.
The Impact: The CPU cannot execute the next set of instructions (like calculating the next log sequence number) until the buffer is empty. If the cache lines are "contended" (being read by another core), the drain takes longer, and the Log Writer sits idle.
If the Log Writer is draining a buffer that contains a "Hot" cache line (like the log header), and another CPU core is trying to read that same header, the hardware must resolve the ownership.
The Impact: The Store Buffer cannot drain until the Cache Coherency protocol (MESIF/MOESI) grants the Log Writer's core "Exclusive" ownership. This adds nanoseconds of jitter to every log write. In a system doing 50,000 commits per second, these nanoseconds aggregate into milliseconds of log file sync wait time.
Normally, if a CPU needs to read something it just wrote, it can pull it straight from the Store Buffer (Store-to-Load Forwarding). However, during a heavy Log Writer "Drain" cycle, the complexity of managing the barrier can cause these optimizations to fail.
The Impact: The CPU is forced to wait for the data to hit the L1 cache and then read it back, doubling the local latency of the log-wrap logic.
| Scenario | Store Buffer State | Resulting Bottleneck |
| Low Concurrency | Drains instantly; mostly empty. | I/O (Disk) is the only bottleneck. |
| High Concurrency | Constant "Draining" stalls. | CPU "System" time spikes; log file sync increases even with fast disks. |
| Micro-Partitioning | Shared buffers between "Virtual Cores." | Extreme Jitter; Store buffers compete for cache access, slowing the Log Writer. |
Since you can't disable the Store Buffer, you have to design around its physics:
Use Large Log Buffers: Larger buffers allow the Log Writer to do "fewer, heavier" drains rather than constant "tiny" drains that keep the CPU pipeline in a perpetual stall.
Core Pinning (Affinity): Keep the Log Writer on a dedicated physical core. This prevents "Context Switches" that force an expensive, mandatory Store Buffer flush to maintain security and state.
Instruction Tuning: Modern database engines use "Weakly Ordered" memory instructions where possible, delaying the "Hard Drain" until the absolute last millisecond before the I/O call.
The Store Buffer is a performance accelerator that becomes a serialization point during database commits. If your Log Writer performance is struggling despite having an NVMe or PMEM back-end, the bottleneck isn't your "Disk"—it's the CPU struggling to drain its internal buffers fast enough to satisfy your ACID requirements.