Cassandra Admin: Session-state table: compaction backlog, oversized heap, and 34 SSTables per read

Session-state table: compaction backlog, oversized heap, and 34 SSTables per read

A six-node single-datacenter cluster (RF=3, 32 GB RAM, 16 cores, NVMe, 3 TB per node) holds a session-state workload. Writes are 90% overwrites of a key that was written less than five minutes earlier; rows carry a one-hour TTL and are read only by primary key. The table uses SizeTieredCompactionStrategy. Over three weeks p99 write latency has risen from 4 ms to 45 ms. nodetool compactionstats shows 180 pending compactions and tpstats shows a permanently full CompactionExecutor queue. tablestats reports p99 of 34 SSTables per read and a bloom filter false positive ratio of 4%. Heap is 16 GB with G1, and GC logs show 700 ms mixed collections twice per minute. Disk write throughput is only 30% utilized, and most of the 64 GB of page cache on each host sits unused. The team has budget approval for faster NVMe drives.

1. Diagnose the compaction problem and choose a strategy. Quantify the effect and state the caveats of your choice.

Read the worked answer

STCS merges SSTables of similar size and has no concept of time. With a one-hour TTL and a five-minute overwrite window, every compaction rewrites data that is about to expire, and because the same keys are updated repeatedly across SSTables, STCS keeps re-merging the same hot set. That is why the executor queue is full and 180 compactions are pending: the compaction write amplification exceeds what the workload can absorb, and the backlog is what produces 34 SSTables per read.

TimeWindowCompactionStrategy with compaction_window_unit: MINUTES and compaction_window_size: 60 (matching the TTL, not the overwrite interval) is the right shape: SSTables are bucketed into fixed windows, compaction only merges within a window, and once everything in a window is expired and any tombstones are past gc_grace, whole SSTables are dropped rather than compacted. Steady-state compaction work falls by roughly the overwrite factor because the rewrite happens once per window instead of repeatedly.

Caveats:

Reads spanning more than one window must merge across windows, so a window much smaller than the TTL trades compaction for read amplification. Sixty minutes is a deliberate choice here.
Repeated overwrites inside one window still leave multiple versions until the window closes; that is bounded and acceptable.
Expired windows cannot be dropped before gc_grace, so with gc_grace at 10 days you retain 10 days of dead sessions on disk. Lower gc_grace to what your repair schedule actually supports, or the space saving never materialises.
Leveled compaction would make this strictly worse: higher write amplification on a workload that is 90% overwrite.

2. Evaluate the 16 GB heap on a 32 GB host and the 700 ms mixed collections. What would you change, and how does it connect to the write latency?

Read the worked answer

16 GB is too large for a 32 GB node and it is causing the pauses. The heap holds memtables, bloom filters, partition indexes and caches; SSTable data and the page cache are off-heap and belong to the operating system. A 16 GB heap leaves little for page cache and, more importantly, gives G1 far more live data to trace during a mixed collection, which is exactly why pauses reach 700 ms.

Concrete changes:

Drop MAX_HEAP_SIZE to 8 GB. This is the single highest-leverage change on this node shape.
Size memtables explicitly with memtable_heap_space / memtable_offheap_space and file_cache_size so the freed memory is actually claimed by the page cache rather than left idle.
If bloom filter memory becomes a problem at higher filter accuracy later, move filters off-heap.
Verify with GC logs and nodetool gcstats that pauses fall under ~200 ms, then re-measure p99.

The connection to write latency is direct: a 700 ms stop-the-world pause stalls the mutation stage, requests queue behind it, and the node misses its write deadlines - which also shows up as dropped MUTATION messages on that node and hinted handoff on its peers. A 45 ms p99 is far better explained by two 700 ms pauses per minute than by disk, especially with the device at 30% utilisation. Fix the heap before buying hardware.

3. Explain where the read latency actually comes from given 34 SSTables per read and a 4% bloom filter false positive rate, and why faster disks will not fix it.

Read the worked answer

34 SSTables per read means one partition read consults 34 files: a bloom filter probe, a partition index lookup and a potential disk seek per file. The 4% false positive rate is behaving as configured - about 1.4 wasted disk lookups per read - so the filters are not the problem; the file count is. The dominant costs are the 34 index lookups and file opens, each of which can miss the page cache, and they scale with the compaction backlog rather than with data volume.

What actually helps, in order:

Fix compaction so SSTables per read lands under 5–10. This is the root fix and everything else is secondary.
Once the file count is low, bloom_filter_fp_chance: 0.01 reduces wasted lookups, at roughly double the filter memory. It is a poor trade while 34 files are being probed.
Increase file_cache_size after the heap shrink, so partition indexes and compression chunks stay resident.
speculative_retry and read repair tuning do not help: this is a single-node local read path, not a straggler problem.

Faster disks do not fix it because the bottleneck is the number of random lookups and the CPU cost of probing them, not throughput - the device is at 30% write utilisation, which is the clearest evidence that it is not saturated. New drives would shorten each individual seek that misses cache and leave the 34-file amplification intact.