Periodic 1.2-Second Query Stalls on a 64 GB Database Host
A 16-core, 64 GB PostgreSQL host also runs a log shipper that writes 40 MB/s of small files. Users report p99 query latency that is normally 40 ms but spikes to 1.2 s roughly every 90 s. During a spike, kswapd0 saturates one CPU for several seconds, MemAvailable falls to about 1.5 GB, and nr_dirty climbs to about 7 GB before dropping sharply. The sysctls are defaults scaled for a small VM: vm.dirty_ratio=20, vm.dirty_background_ratio=10, vm.dirty_expire_centisecs=3000, vm.swappiness=60. The data directory is 400 GB on NVMe, the buffer pool is 24 GB, pgscan_direct increases in bursts, and transparent huge pages are set to always. Kernel is 5.15 with no custom patches. The team proposes adding 64 GB of RAM.
1. Explain the causal chain from dirty-page accumulation to the 1.2 s query stall, naming the mechanism that actually blocks the query.
Read the worked answer
At 64 GB, dirty_background_ratio=10 schedules background writeback at about 6.4 GB and dirty_ratio=20 throttles writers at about 12.8 GB, but dirty_expire_centisecs=3000 means a page can sit dirty for 30 s. The log shipper plus database writes therefore accumulate roughly 7 GB of dirty data, above the background threshold, before flusher threads start draining it. The NVMe queue then sits congested while dirty pages remain resident, and MemAvailable collapses because those pages cannot be reclaimed until writeback completes.
The query block is not one mechanism but three acting together. First, balance_dirty_pages throttles any task that dirties pages once thresholds are crossed, so a query doing a sort spill or a checkpoint waits on writeback. Second, allocation for page cache or anonymous memory triggers direct reclaim, which is the burst in pgscan_direct; reclaim runs in the allocating task and holds locks while scanning, so latency appears in the query, not in a background thread. Third, with MemAvailable near 1.5 GB and swappiness 60, anonymous pages get pushed to swap, adding more I/O on the same device while kswapd0 saturates a CPU.
Adding 64 GB of RAM raises the ratio-derived thresholds proportionally, so the same 30 s expire window simply accumulates more dirty data. It delays the problem and enlarges the burst. Fix writeback behaviour first.
2. Give concrete tuning values and the trade-off each one makes.
Read the worked answer
Prefer absolute byte limits over ratios so behaviour does not change when RAM is added. Set vm.dirty_background_bytes to 268435456 (256 MB) and vm.dirty_bytes to 1073741824 (1 GB); note these are mutually exclusive with the ratio knobs, so zero the ratios to avoid ambiguity. Set vm.dirty_expire_centisecs to 500 and vm.dirty_writeback_centisecs to 100 so data starts draining within seconds instead of sitting for 30 s. Lower vm.swappiness to 10, since the database has its own 24 GB cache and swapping anonymous memory only adds NVMe traffic. Consider raising vm.min_free_kbytes to 1-2 GB so kswapd wakes earlier, but check that it does not cause premature reclaim.
Trade-offs are real. A 1 GB dirty ceiling reduces write coalescing, so the filesystem issues more, smaller writes; on NVMe the IOPS cost is acceptable but write amplification and endurance should be checked. Lower swappiness raises OOM risk if anonymous memory grows, so add an OOM and MemAvailable alert rather than assuming it is safe. Earlier writeback increases steady-state device utilisation, which may reduce peak headroom for checkpoints.
Measure after each change: nr_dirty, pgscan_direct, /proc/pressure/io and /proc/pressure/memory full avg10, and p99 latency over at least an hour, changing one knob at a time.
3. How would you determine whether residual spikes are huge-page compaction rather than reclaim, and what would you change if so?
Read the worked answer
Compare the timing of spikes against counters that distinguish the two. Reclaim and compaction both stall an allocating task, but they leave different fingerprints: reclaim increments pgscan_direct and pgsteal, while compaction increments compact_stall, compact_fail and thp_fault_alloc in /proc/vmstat. If a spike coincides with compact_stall rising but pgscan_direct flat, it is compaction, not reclaim. Confirm with a sampled stack profile or off-CPU trace during the spike showing the allocation path inside compaction, and with /sys/kernel/mm/transparent_hugepage/enabled showing always.
The mechanism is that with always, every fault tries to allocate a 2 MB contiguous region; when memory is fragmented, the allocator synchronously migrates pages to build one, and that migration is the latency. With 1.5 GB free after a 90 s writeback cycle, fragmentation is expected.
If confirmed, switch to madvise and set defrag to defer+madvise, so only explicitly marked regions get huge pages. Alternatively set defrag to defer, which lets khugepaged collapse pages in the background instead of in the fault path. Expect a modest throughput cost for workloads that benefit from huge pages; measure database buffer-pool performance before and after. Do not disable reclaim tuning, because the two problems can coexist and the writeback fix still stands.