How to Safely Drop Massive Data in TiDB Without Causing Write Stall

TiDB clusters accumulate cleanable historical data after prolonged writes. Deleting this data frees space but can trigger compaction flow‑control, causing QPS jitter and increased operation duration.

Background

After a period of writes, the TiDB cluster contains historical data that can be removed. During a large‑scale drop (approximately 30 TB) the cluster experienced QPS fluctuations and abnormal duration spikes because the drop triggered compaction flow‑control, leading to write back‑pressure.

Cluster version: v3.0.5 Cluster configuration: ordinary SSD disks, 128 GB memory, 40 CPU cores

Symptoms

During the drop operation, multiple alerts were triggered, including pending_task and write_stall . Grafana showed server‑is‑busy peaks, indicating TiKV's flow‑control mechanism was engaged. The write stall was caused by excessive level‑0 SST files and a large number of pending compaction bytes.

Investigation Steps

First, the cluster’s busy state was confirmed via Grafana → TiKV → errors, revealing server‑is‑busy peaks caused by TiKV’s flow‑control after the massive drop.

Next, the write‑stall source was identified by examining RocksDB logs. Level‑0 SST overload and high pending compaction bytes were the main culprits.

<code>2020/06/30-12:36:33.758200 7f3586fff700 [WARN] [db/column_family.cc:823] [write] Stalling writes because of estimated pending compaction bytes 69565867323 rate 13421767
2020/06/30-12:36:34.692021 7f359727e700 [WARN] [db/column_family.cc:823] [write] Stalling writes because of estimated pending compaction bytes 69653348050 rate 10737413
... (additional log lines omitted for brevity) ...
2020/06/30-12:40:659559 7f3587dfc700 [WARN] [db/column_family.cc:823] [write] Stalling writes because of estimated pending compaction bytes 68785639852 rate 8589927</code>

Key mitigation actions included:

Reducing max-sub-compactions to accelerate level‑0 SST compaction.

Increasing soft-pending-compaction-bytes-limit (default 64 GB) and hard-pending-compaction-bytes-limit (default 256 GB) to raise the compaction threshold.

Adjusting compression settings to lower disk I/O pressure.

Configuration changes were applied via tikv-ctl :

<code>./tikv-ctl --host=tidb01:20160 modify-tikv-config -m kvdb -n write.soft_pending_compaction_bytes_limit -v 256GB
./tikv-ctl --host=tidb01:20160 modify-tikv-config -m kvdb -n write.hard_pending_compaction_bytes_limit -v 512GB
... (similar commands for other nodes) ...</code>

After applying the new limits, QPS stabilized, write stalls disappeared, and all alerts returned to normal.

Related Knowledge

Region Merge

Since TiDB v3.0, Region Merge is enabled by default. Dropping tables creates empty regions that increase Raftstore heartbeat load. Enabling Region Merge reduces the number of regions, alleviating resource consumption.

<code>pd-ctl config set max-merge-region-size 20
pd-ctl config set max-merge-region-keys 200000
pd-ctl config set merge-schedule-limit 8</code>

Configuration Details

max-merge-region-size : Upper bound for region size to be merged (default 20).

max-merge-region-keys : Upper bound for region key count to be merged (default 200000).

split-merge-interval : Minimum interval between split and merge on the same region (default 1 h).

Accelerating Merge

Adjust max-merge-region-size and max-merge-region-keys to smaller values for faster merges. Reduce patrol-region-interval to “10ms” if CPU permits. Disable split-region-on-table when many empty regions remain after massive table drops.

Large‑Scale Deletion Best Practices

Use batched deletes such as DELETE FROM t WHERE condition LIMIT 1000; in a loop, stopping when affected rows reach zero. For very large deletions, consider partitioned or physically sharded tables; physical sharding showed better performance in tests.

Flow‑Control

TiKV 3.0.6+ supports GC flow‑control via gc.max-write-bytes-per-sec . Set to a non‑zero value to limit GC write throughput and reduce impact on normal requests.

<code>tikv-ctl --host=ip:port modify-tikv-config -m server -n gc.max_write_bytes_per_sec -v 10MB</code>

Conclusion

This guide demonstrates the pitfalls of dropping massive data in TiDB, outlines a systematic troubleshooting workflow, and provides configuration adjustments to mitigate write stalls and compaction‑induced flow‑control. Future updates on large‑scale deletion practices will be added as they become available.