# Additional Troubleshooting — Document Summaries

## Document 1: Troubleshooting: Identify why TiKV drops leaders
**Source**: https://pingcap.feishu.cn/wiki/QWz0w0TlSiv1ckkM6vcca8rTnAe
**Type**: Troubleshooting Guide — Comprehensive checklist
**Reading Status**: Full Read

### Key Points
- Systematic checklist of ALL known reasons TiKV drops leaders, organized into three categories: External, Internal, Hardware
- **External causes**: evict-leader scheduler during upgrade/rolling-update (by design); slow store detection triggering eviction; slow-store false positives from raft log append backlog
- **Internal causes**: gRPC thread busy (can't send heartbeats on time → follower re-election); gRPC stuck from large SQL (>1MB) deserialization; raftstore disk I/O jitter (pre-v8.1 without async IO); high workload pressure causing raftstore slowdown; slow store internal detection
- **Hardware causes**: Network jitter (leader heartbeat can't reach followers → lease timeout at `raft-store-max-leader-lease` 9s → re-election); Memory allocstall or swap causing kernel stalls
- When slow score reaches 100 and evict-slow-store scheduler exists, PD generates evict-leader for that TiKV instance
- gRPC stuck from large SQL: raft client send queue fills up ("full" error in `server report failures`), visible in RPC layer latency from TiDB client side

### Diagnostic Procedures
1. **Check for evict-leader scheduler** — Look in PD logs for `CreateScheduler` / `DeleteScheduler` audit entries with `evict-leader-scheduler`
2. **Check slow store score** — Metric: `TiKV -> PD (or slow trend) -> slow store score`; score reaching 100 triggers eviction
3. **Check gRPC thread utilization** — Metric: `TiKV -> Thread CPU -> gRPC thread pool`; CPU may reach 70-80% without hitting limit
4. **Check raft client queue** — Metric: `TiKV -> Error -> server report failures -> full` indicates send queue overflow
5. **Check RPC layer latency** — Metric: `TiDB -> KV request -> RPC layer latency` for gRPC stuck reflection
6. **Check disk I/O** — Look for long tail in append log duration and propose wait duration
7. **Check allocstall** — Metric: `Node exporter -> Vmstat Page -> Allocstall`

### Metrics / Thresholds
- `raft-store-max-leader-lease`: 9 seconds (follower re-election trigger)
- gRPC thread CPU: 70-80% can cause issues even without reaching limit
- Slow store score threshold: 100 (triggers eviction)
- Large SQL threshold: >1MB causes gRPC deserialization issues; split to <256KB
- Default `server.grpc_concurrency`: 5 (recommend 8 for 16-core TiKV)
- Default `server.grpc_raft_conn_num`: 1 (recommend 2-3)
- Async IO (`store-io-pool-size`): enabled by default after v8.1.0; set to 1 or 2

### Tools / Commands
```bash
# Temporary fix for allocstall
echo 1 > /proc/sys/vm/drop_caches

# Long-term fix: set min_free_kbytes (min(num_numa_nodes * 1G, 16G))
# Example for 2 NUMA nodes:
vi /etc/sysctl.conf
# Add: vm.min_free_kbytes = 2097152
sysctl -p
cat /proc/sys/vm/min_free_kbytes
```

```yaml
# TiKV config tuning for leader drops under high load
server.grpc-raft-conn-num: 3      # default 1
server.grpc-concurrency: 8        # default 5
# store-io-pool-size: 1 or 2      # enable async io (cannot change 0→non-zero dynamically)
```

### Raw Content Excerpt
> All raft heartbeats must be sent and received via the gRPC thread. If some gRPC threads are busy, timely heartbeat transmission/reception becomes difficult. When a region heartbeat times out, a new election will be triggered on the follower. Consequently, under high gRPC load, leader drops may occur.
>
> If there is a large SQL (>1 MB), the gRPC thread will be very busy handling the SQL deserialization of large messages, and can't send the heartbeat on time, which will cause the follower to start re-election of a new leader.
>
> Without async io, the raft log append(i/o operator) is processed by the raft store thread, just like raft messages. If the i/o is hung or slows down, the raft store thread may be stuck, too. And async io is enabled by default after v8.1.0.
>
> allocstall counts the number of times the kernel had to stall a process (delay its execution) while attempting to free up memory during a direct reclaim operation. Allocstall may cause TiKV to slow down in any steps.

### Related Issues / PRs
- gRPC improvement: https://github.com/tikv/tikv/pull/18735
- Large SQL issue: https://github.com/pingcap/tidb/issues/55845
- Slow store false positive fix: `raftstore: fine-tune the updating of store write duration with accurate statistics`
- Related oncalls: GTOC-7832, APID-11596, ONCALL-7593, GTOC-7709, ONCALL-7877

---

## Document 2: Troubleshooting Guide (Meta / Process Document)
**Source**: https://pingcap.feishu.cn/wiki/MMWWwEWmUiC5Xgk2prMcVrW1nKb
**Type**: Process / Meta Document — How to write troubleshooting guides
**Reading Status**: Full Read

### Key Points
- This is NOT a technical troubleshooting document — it's a process guide for contributing troubleshooting articles to the Confluence-based TiDB Knowledge Base
- Instructions for creating articles in Confluence space "TiDB Knowledge Base-Internal"
- Navigate to: Content > English > (en)Troubleshooting Guide
- Required labels: `component` and `troubleshooting_guide`
- Articles should be marked "Verified" status when ready for publication
- Title format: `[Troubleshooting Guide] Brief description`

### Diagnostic Procedures
- N/A (meta document about documentation process)

### Metrics / Thresholds
- N/A

### Tools / Commands
- N/A

### Raw Content Excerpt
> Please put the necessary content into the article. Please put the article title as [Troubleshooting Guide] Brief description. Please ensure the component and troubleshooting_guide labels have been added to the new troubleshooting guide article.

---

## Document 3: Case study: High coprocessor suspend time
**Source**: https://pingcap.feishu.cn/wiki/YONpwWtfuii4LtkfDxQcH3Btnge
**Type**: Case Study — Deep investigation of coprocessor concurrency limiter
**Reading Status**: Full Read

### Key Points
- **Cluster**: TiDB 7.5.6, incident around 2025/10/09 14:23
- **Symptom**: Coprocessor requests took ~30s wall-clock time, but process time <1s and wait time metrics looked normal
- **Root cause**: Coprocessor concurrency limiter (semaphore) — tasks blocked waiting for permits, producing high `total_suspend_time`
- **The hidden limiter**: If a cop task runs >5ms, it must acquire a permit from a semaphore controlled by `end_point_max_concurrency` (default = number of CPU cores). This wait is NOT counted in existing "wait duration" metrics
- **Trigger**: ANALYZE jobs caused burst of expensive cop requests that consumed all concurrency permits, blocking user SELECT queries
- **Two barriers** after a task is picked up by the read pool: (1) Concurrency Limiter (semaphore), (2) Resource Control (resource groups)
- The metric `tikv_coprocessor_waiting_for_semaphore` confirmed the theory but is NOT shown in any default Grafana dashboard

### Diagnostic Procedures
1. Check slow logs for `total_suspend_time` vs `total_process_time` discrepancy
2. Verify `wait_time.schedule` and `wait_time.snapshot` are both low (rules out read pool queuing and snapshot acquisition)
3. Check if ANALYZE jobs were running during the incident window
4. Check `tikv_coprocessor_waiting_for_semaphore` metric (must be queried manually, not in default dashboards)
5. If resource control is off, the concurrency limiter is the only remaining explanation for high suspend time

### Metrics / Thresholds
- `end_point_max_concurrency`: default = number of CPU cores (controls coprocessor semaphore permits)
- Concurrency limiter applies when cop task execution exceeds **5ms**
- TiKV slow-query log threshold: `total_process_time > 1s` (default)
- Key TiKV slow-query log fields:
  - `total_lifetime`: Total wall-clock time
  - `total_process_time`: Actual work time
  - `total_suspend_time`: Time task was NOT doing work (key indicator)
  - `wait_time.schedule`: Time from spawn to first pickup by worker
  - `wait_time.snapshot`: Time acquiring RocksDB snapshot
- Hidden metric: `tikv_coprocessor_waiting_for_semaphore` (not in default Grafana)

### Tools / Commands
```
# TiKV slow-query log entry (key fields):
[total_lifetime=36.88623471s]
[total_process_time=1.517627425s]
[total_suspend_time=35.367982531s]
[wait_time=616.853µs]
[wait_time.schedule=27.363µs]
[wait_time.snapshot=589.49µs]
[tag=analyze_full_sampling]
```

```promql
# Hidden metric to check coprocessor semaphore contention
tikv_coprocessor_waiting_for_semaphore
```

```toml
# TiKV config to tune coprocessor concurrency
# end_point_max_concurrency = <number>
# NOTE: NOT online configurable as of this writing
```

### Raw Content Excerpt
> Coprocessor tasks in TiKV are futures that run on the unified read pool. However, after being picked up by a worker, they must still pass two barriers before process_time even begins:
>
> Concurrency Limiter: If a task runs > 5 ms, it must acquire a permit from a semaphore (configured by end_point_max_concurrency, default = number of CPU cores). If no permit is available → the task blocks, and suspend_time continues ticking. Currently, this block is NOT counted in the existing "wait duration" metrics.
>
> Resource Control (Resource Groups): A task may be forced to sleep if its resource group exceeds the resource limit.

### Related Issues / PRs
- Dashboard fix: https://github.com/tikv/tikv/pull/19195 (adding suspend time + semaphore metrics to dashboards)

---

## Document 4: Pessimistic Transaction Lock Wait Timeout Mechanism
**Source**: https://pingcap.feishu.cn/wiki/NCZ1wtad2i0u7dk0Il3coK0GnCh
**Type**: Mechanism Deep-dive — Three-layer timeout architecture
**Reading Status**: Full Read

### Key Points
- Pessimistic lock wait has a **three-layer timeout and retry mechanism**: TiKV Server → TiKV Client-Go → TiDB
- **TiKV Server**: Waits `min(client_value, tikv_config)` = `min(50000ms, 1000ms)` = 1s per attempt; returns `Locked` error on timeout; NO retry at this layer
- **TiKV Client-Go**: Receives `Locked` error and auto-retries transparently (TiDB is unaware); continues until lock acquired, cumulative time ≥ 50s (converts to `ErrLockWaitTimeout`), or other error
- **TiDB Layer**: Only retries for `ErrWriteConflict` and retryable `ErrDeadlock`; `ErrLockWaitTimeout` is NEVER retried — returned directly to client
- **Critical detail**: Cumulative wait time NEVER resets and is only checked for `ErrWriteConflict` at TiDB level
- `ErrDeadlock` retry does NOT check cumulative time (different from `ErrWriteConflict`)

### Diagnostic Procedures
1. When seeing lock wait timeout errors, understand which layer timed out
2. `ErrLockWaitTimeout` = cumulative client-go retry exceeded 50s → not retried at TiDB level
3. `ErrWriteConflict` triggers TiDB-level retry with cumulative time check
4. Retryable `ErrDeadlock` triggers TiDB-level retry WITHOUT time check
5. Check if `pessimistic-txn.max-retry-count` (256) was exceeded → error: "pessimistic lock retry limit reached"

### Metrics / Thresholds

| Layer | Config | Default | Description |
|-------|--------|---------|-------------|
| TiDB | `innodb_lock_wait_timeout` | 50s (50000ms) | Cumulative timeout for pessimistic lock wait |
| TiDB | `pessimistic-txn.max-retry-count` | 256 | Maximum retry count per statement |
| TiKV | `pessimistic-txn.wait-for-lock-timeout` | 1s (1000ms) | Single lock wait timeout |

- TiKV effective wait time per attempt: `min(client_provided, tikv_configured)` = typically 1000ms
- Max retries at client-go level: ~50 attempts (50s / 1s per attempt)
- TiDB retry limit: 256 retries per statement

### Tools / Commands
```go
// Key code locations:
// TiKV wait time normalization:
// pkg/store/mockstore/unistore/tikv/mvcc.go:642-649 (normalizeWaitTime)

// LockCtx creation with timeout:
// pkg/executor/select.go:345

// TiDB retry check:
// pkg/sessiontxn/isolation/repeatable_read.go:260-264

// TiDB retry execution:
// pkg/executor/adapter.go:1210-1242
```

### Error Differentiation Summary
```
Locked error          → TiKV client-go auto retry (if cumulative < 50s)
ErrWriteConflict      → TiDB retry (checks cumulative time + retry count)
ErrDeadlock (retryable) → TiDB retry (checks retry count only, NO time check)
ErrLockWaitTimeout    → NO retry, returned directly to client
```

### Raw Content Excerpt
> Three-Layer Timeout and Retry Mechanism:
> 1. TiKV Server Layer: Waits at most 1 second per attempt, returns `Locked` error after timeout
> 2. TiKV Client-Go Layer: Automatically retries when receiving `Locked` error if cumulative wait time < 50s
> 3. TiDB Layer: Retries only for `ErrWriteConflict` and retryable `ErrDeadlock`; `ErrLockWaitTimeout` does not trigger retry
>
> Key Points:
> 1. Three Layers: TiKV Server (1s) → TiKV Client-Go (50s auto retry) → TiDB (50s or 256 retries)
> 2. Cumulative Time: Never resets, checked only for `ErrWriteConflict` at TiDB level

---

## Document 5: IndexLookup Slow
**Source**: https://pingcap.feishu.cn/wiki/RveUwctBpiPKYGk2B8ZcX0B2ngb
**Type**: Troubleshooting Guide — IndexLookUp operator performance
**Reading Status**: Full Read

### Key Points
- **IndexLookUp** = TiDB's index-back-to-table operator: Build side (IndexFullScan/IndexRangeScan) collects RowIDs → Probe side (TableRowIDScan) fetches actual rows
- **Factors controlling cop task count**: `tidb_distsql_scan_concurrency` / `tidb_executor_concurrency` (default 15); `tidb_index_lookup_size` (batch size of RowIDs per probe request); `tidb_index_lookup_concurrency` (probe side concurrency, default 15); per-region splitting of individual probe tasks
- **Common root causes of slow IndexLookup**:
  1. Poor index selectivity → too many rows to look up
  2. TiKV unified read pool overloaded → cop wait time
  3. IndexLookup + Limit but Limit can't push down to IndexScan → generates excessive cop tasks
  4. Scattered data distribution → too many regions involved → too many cop tasks
- **Optimization history**: v5.4 paging (`tidb_enable_paging`); v6.4 adaptive concurrency (#37724); v6.5 batch coprocessor (`tidb_store_batch_size`)

### Diagnostic Procedures
1. Check if index selectivity is poor (too many rows returned from index scan vs. final result)
2. Check TiKV unified read pool for cop wait time
3. Check if Limit operator is pushing down to IndexScan (if not, excessive parallel reads)
4. Analyze data distribution — scattered data across many regions means more cop tasks
5. Compare cop task count vs. returned rows to determine if concurrency tuning is needed

### Metrics / Thresholds
- `tidb_distsql_scan_concurrency`: default 15 (controls TiDB→TiKV scan concurrency)
- `tidb_executor_concurrency`: default 15 (general executor concurrency)
- `tidb_index_lookup_size`: batch size of RowIDs for probe requests
- `tidb_index_lookup_concurrency`: default 15 (probe side concurrency)
- `tidb_enable_paging`: introduced v5.4 (can cause OLAP regression on TiKV-backed queries)
- `tidb_min_paging_size` / `tidb_max_paging_size`: tune paging row range
- `tidb_store_batch_size`: v6.5+ batch coprocessor (not stable in v6.5)

### Tools / Commands
```sql
-- Key session variables for tuning IndexLookup
SET tidb_distsql_scan_concurrency = 15;  -- scan concurrency
SET tidb_executor_concurrency = 15;       -- general concurrency
SET tidb_index_lookup_size = 20000;       -- batch size for probe
SET tidb_enable_paging = ON;              -- enable paging (v5.4+)
SET tidb_store_batch_size = 4;            -- batch copr (v6.5+)
```

### Solutions Summary
1. **Optimize SQL**: composite indexes, reorder index columns, push down operators, covering indexes, use TableScan instead of index lookup
2. **Too few cop tasks / too many rows per task**: Use `SPLIT TABLE` to create more regions → higher parallelism
3. **Too many cop tasks / too few rows per task**: Reorganize data distribution (requires reimport with new schema/clustered index)
4. **Use clustered index**: Avoids index lookup entirely for primary key access (trade-off: secondary index still needs lookup, potential hotspot)
5. **Tune concurrency parameters**: Adjust `tidb_distsql_scan_concurrency`, `tidb_executor_concurrency`, `tidb_index_lookup_size`

### Raw Content Excerpt
> IndexLookUp is the operator TiDB uses to fetch data through a secondary index (index table lookup). It first aggregates RowIDs scanned from TiKV on the Build side, then reads exact rows from TiKV on the Probe side using those RowIDs.
>
> Factors affecting Cop task count: the concurrency of `TableScan` and `IndexScan` operators is controlled by `tidb_distsql_scan_concurrency` and `tidb_executor_concurrency` (default 15). `tidb_index_lookup_size` controls how many row IDs are sent in one request to fetch table data (batch size). A single table-lookup task also has extra concurrency when split into cop tasks by region.

---

## Document 6: Slow Query Analysis — @Jinlong
**Source**: https://pingcap.feishu.cn/wiki/SEH0wSjBIiic50koLhxcyW1tnhd
**Type**: Troubleshooting Guide — Systematic slow query diagnosis and mitigation
**Reading Status**: Full Read

### Key Points
- **Step-by-step methodology** for analyzing slow queries in TiDB, from identification to mitigation
- **Step 1**: Identify slow queries from TiDB Dashboard → Slow Query / SQL statements; filter by time range and PlanDigest
- **Step 2**: Decode execution plans using `tidb_decode_binary_plan()` or `EXPLAIN ANALYZE`; compare `estRows` vs `actRows`
- **Step 3**: Verify statistics with `SHOW STATS_META` and `SHOW STATS_HEALTHY`; auto-analyze may not trigger in time; time-based fields can never have accurate future statistics
- **Step 4**: Evaluate operator selectivity across three dimensions:
  - **Index selectivity**: Wrong index chosen, expression on column prevents range scan, no index skip scan support
  - **Table scan selectivity**: Low predicate selectivity, missing/ignored indexes
  - **Join selectivity**: Join type choice (Hash/Index/Merge), join order, build/probe side assignment
- **Mitigation steps**: (1) Run ANALYZE TABLE, (2) SQL hints/bindings for index selection, (3) Create missing indexes (avoid during peak traffic), (4) Optimize join order with LEADING hint

### Diagnostic Procedures
1. **TiDB Dashboard** → Slow Query → filter by time range and PlanDigest → inspect Plan section
2. Compare `estRows` vs `actRows` for estimation accuracy
3. Run `SHOW STATS_META;` and `SHOW STATS_HEALTHY;` to check statistics freshness
4. Check if auto-analyze was triggered and if statistics are stale (especially for high-ingestion tables)
5. Evaluate index selectivity — is the chosen index actually reducing scan rows efficiently?
6. Evaluate join order — smaller tables should be joined first to minimize intermediate result size
7. Gather PLAN REPLAYER for escalation

### Metrics / Thresholds
- `STATS_HEALTHY = 0`: Statistics are severely outdated
- For tables with <100M rows, frequent `ANALYZE TABLE` via crontab is often sufficient
- Statistics on time-based fields are inherently inaccurate (future data cannot be collected)

### Tools / Commands
```sql
-- Step 1: Decode binary plan from slow log
SELECT tidb_decode_binary_plan('<binary_plan>');

-- Step 2: Check execution plan
EXPLAIN ANALYZE <SQL>;

-- Step 3: Check statistics health
SHOW STATS_META;
SHOW STATS_HEALTHY;

-- Step 4: Update statistics
ANALYZE TABLE orders;

-- Step 5: Force index selection via hint
SELECT /*+ USE_INDEX(users idx_email) */ * FROM users
WHERE user_id = 123244541 AND email LIKE 'abc%';

-- Step 6: Force join type and order
SELECT /*+ INL_JOIN(u,o) */ * FROM orders o JOIN users u ON o.user_id = u.id;
SELECT /*+ LEADING(a,b,c) */ a.name, b.age, c.salary
FROM a JOIN b ON a.id = b.id JOIN c ON b.id = c.id;

-- Step 7: Create missing index (schedule during low traffic)
CREATE INDEX idx_user_email ON users(email);

-- Step 8: SQL Binding for persistent plan enforcement
CREATE GLOBAL BINDING FOR <sql> USING <sql_with_hints>;
```

### Must-Gather Information for Escalation
- PLAN REPLAYER dump
- Table statistics dump
- Table structure (DDL)
- Slow query SQL text
- SQL execution plan
- `SHOW STATS_META` output (statistics health)
- Session variables and configuration

### Raw Content Excerpt
> When encountering a hot read or issues in the cluster caused by slow queries, it is necessary to analyze the execution plan to determine whether the SQL has any problems and whether it can be optimized.
>
> Key Checks: estRows vs. actRows → to check if row estimation is accurate. Operator selectivity (scan or join operator efficiency).
>
> Statistics on time-based fields can never be accurate because future data cannot be collected, which can lead to predicate pushdown failure and incorrect estimations.
>
> Note: Binding may not apply to dynamic SQL or columns with uneven data distribution (in where clause).

---

*Generated: 2026-02-15 | Documents read: 6/6 | All fully accessible*