# [Known Issue] Internal SQL or large SQL can exhaust TiKV IOPS ([tikv#8616](https://github.com/tikv/tikv/issues/8616))

Source note: adapted from GitHub issue [tikv#8616](https://github.com/tikv/tikv/issues/8616).

## Issue

- Link: [tikv#8616](https://github.com/tikv/tikv/issues/8616)
- Summary: internal SQL such as `ANALYZE`, or very large SQL scanning cold data, can exhaust TiKV disk IOPS and degrade cluster-wide latency.

## Phenomenon

- Frontend latency spikes appear while TiKV disk IOPS reaches the device limit.
- Affected workloads often include `ANALYZE`, statistics collection, or large scans over cold data.
- The issue can look like a storage bottleneck even though the trigger comes from SQL behavior.

## Diagnostic Steps

Check these metrics:

- `TiKV-details -> Coprocessor overview -> total kv cursor operators`
- `TiKV-details -> rocksdb kv -> block-cache operations`
- `TiKV-details -> rocksdb kv -> block cache hit`

Interpretation:

- `total kv cursor operators` can show heavy RocksDB read activity from `ANALYZE`-like SQL
- `block-cache operations` indirectly reflects SST read frequency
- low block-cache hit can indicate cold-data access, though low concurrency sometimes weakens this signal

## Root Cause

TiKV does not provide a direct IOPS throttle for internal SQL or general SQL. When internal SQL reads a large amount of cold data, the workload can directly push disk I/O to the device limit and amplify latency across the cluster.

## Workaround

Use indirect controls:

- TiKV: set [`background-read-bandwidth`](https://docs.pingcap.com/zh/tidb/stable/tikv-configuration-file/#background-read-bandwidth-%E4%BB%8E-v620-%E7%89%88%E6%9C%AC%E5%BC%80%E5%A7%8B%E5%BC%95%E5%85%A5)
- TiDB: reduce concurrency
  - [`tidb_sysproc_scan_concurrency`](https://docs.pingcap.com/tidb/stable/system-variables/#tidb_sysproc_scan_concurrency-new-in-v650)
  - [`tidb_auto_build_stats_concurrency`](https://docs.pingcap.com/tidb/stable/system-variables/#tidb_auto_build_stats_concurrency-new-in-v650)
  - [`tidb_build_sampling_stats_concurrency`](https://docs.pingcap.com/tidb/stable/system-variables/#tidb_build_sampling_stats_concurrency-new-in-v750)
- Use resource control when applicable

Example:

```sql
SET GLOBAL tidb_sysproc_scan_concurrency = X;
SET GLOBAL tidb_auto_build_stats_concurrency = Y;
SET GLOBAL tidb_build_sampling_stats_concurrency = Z;
```

## Related Oncall

- v7.5.6 Triage root cause of EBS IOPS exceeded `TCOC-4356`
- v7.5.3 IOPS exceeded `ST-2120` / `TCOC-4070`
- v7.5.6 IOPS exhaustion due to BIG SQL `TCOC-3993`
- v7.5.6 IOPS exhaustion due to analyzing SQL `TCOC-3992`