Revert "Snapshot release triggered compaction without multiple tombstones"

[v01dstar]

Revert "Revert " Revert "Snapshot release triggered compaction without multiple tombstones (#8357)" (#8410)" (#8438)"

We observed:

1. Performance regression
2. Compaction pattern change, especially during TiDB's add index test (using ingestions). We observed abnormal "compaction pending bytes", irregular write batching ("done by others" were significantly lower), flush, level 0 compaction were impacted, level 0 files accumulation (triggered write stall).

We also tried the new option introduced in https://github.com/facebook/rocksdb/pull/11701 for delaying such compactions, but it did not resolve the issue.

So, we are reverting the commit. This reverts commit f89423a57a78e28de68c9a618a6e5f2f00ec5270.

Maintainer's node: We need to cherry-pick this in future RocksDB upgrades, until new improvements are proposed in upstream.

[ti-chi-bot[bot]]

[APPROVALNOTIFIER] This PR is APPROVED

This pull-request has been approved by: LykxSassinator, overvenus

The full list of commands accepted by this bot can be found here.

The pull request process is described here

Needs approval from an approver in each of these files: - ~~[OWNERS](https://github.com/tikv/rocksdb/blob/8.10.tikv/OWNERS)~~ [LykxSassinator,overvenus] Approvers can indicate their approval by writing `/approve` in a comment Approvers can cancel approval by writing `/approve cancel` in a comment

[ti-chi-bot[bot]]

[LGTM Timeline notifier]

Timeline:

• 2024-10-29 02:37:20.377488311 +0000 UTC m=+317353.216643857: :ballot_box_with_check: agreed by overvenus.
• 2024-10-29 02:46:28.055712397 +0000 UTC m=+317900.894867939: :ballot_box_with_check: agreed by LykxSassinator.