Cassandra Admin: Two-DC cluster: a restore that resurrected 4.1 million purged rows

Two-DC cluster: a restore that resurrected 4.1 million purged rows

A twelve-node cluster spans two datacenters: us-east with eight nodes and eu-west with four, both at RF=3, both receiving all writes at LOCAL_QUORUM. nodetool repair -pr runs from cron on each node once every 30 days, sequentially, at about 14 hours per node. gc_grace_seconds is 864000. A nightly cron takes nodetool snapshot on every node at 02:00 and clears it the following night. Last week: at 02:00 the snapshots ran; between 03:00 and 04:00 an application purge job deleted 4.1 million rows from events; at 20:00 an operator accidentally ran DROP TABLE events. Restore from the 02:00 snapshot completed, but the purged rows are back and the application is now double-counting. Incremental backups and commitlog archiving are disabled. Separately, a failed disk on one node was replaced three weeks ago and the replacement bootstrapped 2.2 TB in nine hours.

1. Explain why the restore resurrected the purged rows and what the correct restore procedure was. What do you do now?

Read the worked answer

The restore rolled the table back to 02:00. The purge at 03:00 wrote 4.1 million tombstones into SSTables that did not exist at 02:00, so replaying the snapshot re-materialised every purged row. The 20:00 DROP is irrelevant to those rows - they are a point-in-time rollback artefact, not data that survived the drop. A snapshot is a hardlink of SSTables at one instant, not a delete-safe backup: it reproduces the state at the moment it was taken, including rows deleted afterwards.

Correct procedure:

First decide the recovery point. It is 02:00 whether you like it or not; if that is acceptable, restore and then re-run the purge, which is what should have happened.
If a tighter recovery point is required, restore the snapshot into a separate keyspace or scratch cluster with sstableloader, replay archived commitlogs to advance to the failure point, re-apply the purge, and only then cut over.
Never load restored SSTables into a live table that already holds newer data. nodetool refresh on the live table mixes two timelines and produces exactly this double-counting.

Now: re-run the purge with the identical predicate, then verify with per-token-range counts on both DCs, and check tablestats for the tombstone counts to confirm it is progressing. Record the actual recovery point for this table as 'last verified snapshot' or enable commitlog archiving before the next incident.

2. The repair cadence is 30 days with gc_grace at 10 days. Quantify the risk and redesign the schedule for a two-DC cluster.

Read the worked answer

gc_grace_seconds 864000 is 10 days; repair runs every 30. That is a three-fold violation of the rule that repair_interval must be shorter than gc_grace. A tombstone becomes purgeable at day 10 on each replica independently. If a replica holding the live row has not been repaired by then, another replica purges its tombstone, the row survives on the un-repaired replica, and the next repair or read repair propagates it back - a zombie. With a 30-day cycle, every delete in this table has a wide window to come back.

Redesign:

Run repair every 7 days (or 3, if the 14-hour-per-node cost allows segmented scheduling), keeping gc_grace at 10 days so there is slack for a slow or failed run.
Use nodetool repair -pr --dc us-east on each of the eight us-east nodes and -pr --dc eu-west on the four eu-west nodes. -pr alone on a two-DC cluster repairs remote replicas as well and streams across the WAN; scoping by DC makes each range repaired exactly once per DC.
Stagger start times so no more than one repair runs at a time, and cap stream_throughput_outbound_megabits_per_sec if repair competes with peak traffic.
The first repair after a node replacement must be --full, not incremental, otherwise ranges that node should own may never validate against its peers.
Confirm completion with repair history (system.repair_history / repair_admin) rather than trusting exit codes; a repair that times out halfway is worse than none, because it looks done.

3. What must be verified after the bootstrap and after this restore, and what changes would you make to the backup design?

Read the worked answer

After a bootstrap:

nodetool status must show the node UN with ownership close to 100/(nodes in DC) adjusted for RF. A node showing 50% ownership is holding data it should not.
Run nodetool cleanup on the existing nodes. Without it they retain the 2.2 TB the new node now also owns, every later repair streams it back, and disk usage drifts upward permanently.
Check tpstats for dropped MUTATION during bootstrap and confirm hinted handoff drained before declaring done.
Nine hours for 2.2 TB is about 68 MB/s, plausible on a shared link; stream_throughput_outbound_megabits_per_sec (default 200 Mb/s per stream, multiple streams) is the knob, and raising it only helps if the link is idle.

After the restore: verify row counts per token range on both DCs, run a targeted nodetool repair on events, and confirm no tombstones were purged prematurely.

Backup design changes:

Retain more than one snapshot generation and only clear the previous night after the new one is verified on every node. Tag with -t and clear selectively with clearsnapshot -t.
Get snapshots off the node. A snapshot lives on the same failed disk; shipping to object storage (or a tool that does so) is mandatory.
Enable commitlog archiving if the recovery point objective is tighter than 24 hours - without it, a point-in-time restore is impossible.
Rehearse a restore into a scratch cluster quarterly; this incident was discovered during a real outage.