Performance Tuning and Query Optimization
This domain tests why queries are fast or slow: partition size, tombstones, compaction, caching, indexes, paging, and repair. The exam focuses on diagnosing read/write amplification and choosing schema or operational fixes rather than tuning random settings.
Compaction and write path
Writes go to commitlog and memtable, then flush to immutable SSTables. Compaction merges SSTables, removes tombstones after gc_grace_seconds, and controls read amplification. SizeTieredCompactionStrategy (STCS) is the default and write-oriented; LeveledCompactionStrategy (LCS) reduces read amplification for read-heavy or update-heavy workloads, at the cost of more write amplification; TimeWindowCompactionStrategy (TWCS) suits time-series data with TTL and time-based expiry. Choose based on workload, not fashion.
Tombstones and repairs
A DELETE writes a tombstone. It must survive gc_grace_seconds so repair can propagate the delete to replicas that missed it. Lowering gc_grace_seconds below repair frequency can resurrect data. Wide partitions and many tombstones slow reads because Cassandra must scan and discard them. Repair reconciles replicas; run it within gc_grace_seconds. nodetool compactionstats and tpstats help identify compaction backlog and dropped mutations.
Worked example
A time-series table stores one row per device per second and deletes old data with TTL 30 days. Each partition is one device per day. The table uses TWCS with a 1-day window. Old SSTables expire and compact within their window, keeping read amplification bounded. If the team instead used a single partition per device for all time, partitions would grow without bound and reads would scan many tombstones. If they used LCS for this TTL workload, compaction would rewrite data more often than necessary.
Indexes and caches
Secondary indexes are not a substitute for partition-key design; they can fan out to many nodes. SAI can improve flexible search but still has cost. The key cache stores partition-index locations and is usually helpful; row cache is rarely appropriate because it caches full rows and can consume heap. Bloom filters reduce disk reads for absent partitions but do not replace good modeling.
Traps
Do not lower gc_grace_seconds without repair discipline. Do not use ALLOW FILTERING for routine queries. Do not assume more cache is always better. Do not ignore large partitions and tombstones when latency rises. Do not pick LCS for a pure write-heavy or TTL-expiry workload without measuring.