TTL, Tombstones and Compaction Backlog on a Metrics Table

Every row carries a 30-day TTL, so every row eventually becomes a tombstone. STCS compacts by size, which means it merges old SSTables with new ones; SSTables holding already-expired data are repeatedly rewritten together with fresh data instead of being dropped. The result is the classic pattern: 1.1 TB of live data occupying 3.2 TB on disk, 9 SSTables touched per read, and reads that must scan large tombstone-covered regions before reaching live rows for the last 6 hours.

Governing settings:

• tombstone_failure_threshold (default 100,000): a single query that scans more tombstones than this is aborted - that is the log message. tombstone_warn_threshold (default 1,000) logs first.
• gc_grace_seconds (default 864,000 = 10 days): how long tombstones survive before compaction may purge them. It is a safety window tied to repair frequency, not a tuning knob to set to 0.
• tombstone_threshold (default 0.2): the tombstone-to-live ratio that allows a single SSTable to be compacted on its own.
• unchecked_tombstone_compaction: forces those single-SSTable compactions past the ratio test. Only safe if every replica is repaired within gc_grace, otherwise purged tombstones resurrect deleted data.
• default_time_to_live: confirms every inserted row is expiring.

The 210 pending compactions and the dropped mutations are the downstream symptom: compaction cannot keep up, so reads pay more and the write path starts shedding load.

Use TimeWindowCompactionStrategy with windows aligned to the write pattern and much smaller than the TTL:

ALTER TABLE metrics WITH compaction = {
  'class': 'TimeWindowCompactionStrategy',
  'compaction_window_unit': 'DAYS',
  'compaction_window_size': '1'
};

Rationale: rows written in one day all expire on the same day, 30 days later. TWCS buckets SSTables by write time and only compacts within a window, so a day window becomes a handful of SSTables that are never merged with other days. When the whole window has expired, it is dropped with no compaction work at all. A query for the last 6 hours touches at most two windows, so SSTables per read falls from 9 to a small number and the tombstone scanning largely disappears.

Sizing rule: keep the number of live windows modest (10-30 is comfortable) and the window much larger than the flush interval. With a 30-day TTL, 1-day windows give 30 live windows - good. A 30-day window would mean one giant window whose expiry is a cliff and whose compactions are enormous.

Caveats:

• TWCS assumes data arrives roughly in time order. Updates or backfills into old time ranges create SSTables in old windows that never compact away, so keep update-heavy data out of this table.
• The ALTER applies to future compactions; existing SSTables are rewritten over time. If space is urgent, force it node by node (nodetool compact) off-peak, never cluster-wide.
• Tombstones still live for gc_grace_seconds. Lower it (for example to 3 days) only once repair is proven to run at least that often on every node.

Dropped MUTATION messages mean the write path exceeded a queue somewhere. Treat it as capacity plus amplification, not as a driver problem, and work in this order:

1. Confirm where. tpstats separates dropped from currently queued in MutationStage; compactionstats shows the backlog; check disk utilization and iostat on the data disks. If compactions are the bottleneck, the strategy change above is the root fix.
2. Reduce write amplification. Moving the metrics table to TWCS removes most of the rewritten bytes. Until the rewrite completes, consider raising concurrent_compactors modestly - only if CPU and IO show headroom, otherwise it makes contention worse.
3. Check the commitlog. If commitlog_total_space_in_mb (default 8 GB) sits on a small or nearly full disk, Cassandra flushes aggressively and can reject writes. Give commitlog its own disk and raise total space; a full commitlog disk stops writes outright.
4. Reclaim space without a storm. nodetool garbagecollect on one node at a time, off-peak, drops overwritten data without a full compaction.
5. Throttle clients at the driver so the cluster is not fed faster than it can compact; shedding at the source beats dropping at the server.

Avoid: nodetool compact across the cluster during peak (it saturates IO and reproduces exactly these drops); unchecked_tombstone_compaction while any replica is past gc_grace without repair (deleted data can resurrect); and lowering gc_grace_seconds as a first move before repair is verified.