---
name: tidb-perf-diagnosis
description: |
  TiDB Cloud performance diagnosis and troubleshooting.
  Use when diagnosing latency spikes, OOM, hotspots, slow queries, lock contention,
  scaling issues, or any performance problem on TiDB Cloud clusters.
  Covers both TiDB-X(starter,essential,Premium,BYOC) and Dedicated architectures.
  Uses a triage-then-read pattern: classify the problem first, then load
  only the relevant knowledge file(s) from the knowledge/ directory.
  Route TiCDC health incidents and transaction-specific oncall issues to
  dedicated diagnosis skills when they become the primary problem.
---

# TiDB Cloud Performance Diagnosis

Diagnose performance issues on TiDB Cloud clusters using structured knowledge.

**CRITICAL**: Always determine which product line the cluster belongs to BEFORE starting diagnosis(Dedicated vs Starter/Essential vs Premium/BYOC). The metrics paths differ somehow.

## Diagnosis Workflow

### Step 1: Classify the Problem

Identify the primary symptom from the user's description.

### Step 2: Load Relevant Knowledge

Use the method in the doc and use the additional routing table to identify which companion file(s) to read. Read ONLY the
relevant file(s) -- do NOT load all files.

```
read("skills/tidb-perf-diagnosis/knowledge/filename.json")
```

TiDB Cloud has 3 distinct product lines with different architectures:

Shared metrics: tidb.json, node-info.json, pd.json, tiflash.json
Starter/Essential(powered by NextGen/TiDB-X): *starter_essential*.json
Premium/BYOC(powered by NextGen/TiDB-X): *premium*.json
Dedicated: *dedicated*.json

### Additional Skill Routing

Use `tidb-perf-diagnosis` as the front door for top-down performance triage. If the primary issue matches one of the
specialized domains below, load that skill and follow its workflow.

| Primary symptom | Route to | Read |
|---|---|---|
| TiCDC replication lag, changefeed `failed` / `warning` / long `pending`, owner or maintainer instability, throughput interruption, backpressure, sink or messaging errors, or a TiCDC new-architecture health audit | `ticdc-health-inspection` | `read("skills/diagnosis/ticdc-health-inspection/SKILL.md")` |
| deadlock, lock wait timeout, write conflict, `PessimisticLockNotFound`, async-commit anomalies, follower-read or stale-read anomalies, GC stalls, transaction hotspot or backoff, or any transaction correctness risk | `transaction-oncall-handbook` | `read("skills/diagnosis/transaction-oncall-handbook/SKILL.md")` |

### Step 3: Apply Diagnostic Procedures

**MUST** cross-check **ALL** the diagnosis data before response. Connect the metrics, with Slow Queries, Logs, SQL Statements, Top SQL, and Conf Profiling.

**MANDATORY TopSQL Cross-Validation** (for any hotspot or CPU-related conclusion):
Before concluding that a specific SQL or operation (e.g., ANALYZE, large scan) is the CPU hotspot root cause, you **MUST** verify it against TopSQL data:
1. Query TopSQL filtered by `component = 'tikv'` + the hotspot instance + incident time window
2. Check whether the suspected SQL appears in the top 20 by `topsql_cpu_time_ms`
3. If the suspected SQL is NOT in the top 20 TopSQL results, the conclusion is **wrong** — re-investigate using TopSQL to find the actual CPU consumer
4. Record the CPU percentage of the identified root-cause SQL in TopSQL as evidence

## Key Points

Use a holistic(top-down) approach to debug the performance issue in TiDB: Performance tuning based on database time

## Diagnostic Procedures (step-by-step)

### Dedicated Disk Preflight (MUST DO before node / disk checks)

When diagnosing TiKV read/write latency on Dedicated / legacy `tidbcloud`:

1. Resolve the **application UUID** for `tidb_*` / `tikv_*` metrics.
2. Resolve the **infra UUID** for `node_*`, `kube_*`, and `aws_ebs_csi_*` metrics.
3. If `count(node_disk_io_time_seconds_total)` or `count(aws_ebs_csi_read_bytes_total)` returns empty on the application UUID, treat this as a **wrong metrics source**, not as "disk metrics absent".
4. Only after the infra UUID is confirmed should the diagnosis use `Disk I/O Util`, disk bandwidth, EBS throughput, IOPS, or wait latency as evidence.

For legacy Dedicated clusters, infra commonly resolves from `applicationID=k8s-infra` + `projectID`.
For NextGen(starter/essential/Premium/BYOC) clusters, infra commonly resolves from tenant-scoped `dp-*` registrations.

### Metrics Guideline

#### database time model
- Use database time to analysis the cluster performance overview, focus on the significant contribution to db time.
- read the file skills/tidb-perf-diagnosis/knowledge/performance-overview*.json according to the cluster type, to process all the metrics under performance overview section
  - Dedicated: performance-overview-dedicated.json
  - Starter/Essential/Premium/BYOC: performance_overview_starter-essential-premium.json
  - instructions: https://docs.pingcap.com/tidb/stable/performance-tuning-methods/
-  understand the system bottleneck
  - if it's inside or outside tidb cluster
  - if it's inside tidb cluster
    - understand the workload characterics
    - check where is the bottleneck inside tidb cluster
    - identify the components bottleneck, it's in tidb/tikv/pd
    - check if there is single slow instance impacting the whole system

#### For NextGen/TiDB X(starter/essential/Premium/BYOC) only

1. analyze the RCU usage characterics

| Type | How RU is calculated? |
|---|---|
| Read | - 1 storage read request consumes 0.475 RU<br>- 64 KiB read request payload consumes 1 RU |
| Write | - 1 storage write request consumes 2.55 RU<br>- 2 KiB write request payload consumes 1 RU |

2. check if the system if throttled by Max RCU:

- for Premium/BYOC cluster, check the max p90 KV client request wait duration greater than 10ms, then the cluster is throttled by max RCU, this is the metrics `histogram_quantile(0.9, sum(rate(resource_manager_client_request_success_bucket{}[2m])) by (le))`
- for starter/essential cluster, check if the available RU less than 1000, then it's throttle. This is the metrics: `resource_manager_resource_unit_available_ru{namespace="tidb-serverless", name="$keyspace_id"}`
- check slow query(if available) if dominante by The `total time queued by RC`

3. other Common Issues and Mitigations

| Issue Type | Provider | Typical Symptoms | Root Cause Clues | Mitigation |
|------|------|------|------|------|
| TiProxy Gateway bottleneck | Essential | TiProxy CPU is high | Tiproxy become the bottleneck | Scale out TiProxy nodes horizontally and retest |
| Cop worker cannot auto-scale | Premium,BYOC | `Concurrency Limiter Waiting Length` remains high, but TiKV does not scale up more cop workers | Cop limiter and cache configuration are too small, limiting cop-side throughput | Tune TiKV configs |

#### Most common issues:
- bottleneck outside of TiDB Cluster: Insufficient stress-client capacity or network latency, Client latency is much higher than internal database latency; TiDB/TiKV CPU is low; when concurrency increases, QPS stops growing while latency rises.

- Slow TiDB issues
  - per-instance outlier: `tidb query latency by instance (p80, p95, p99, p999)` shows one instance persistently much worse than peers
  - per-instance outlier: high resouce utilization(cpu, memory)
  - single slow tidb node by unbalanced connection and workloads
  - high parse/compile overhead
  - High TSO wait (>% 20% execute time) indicate high goruntime pressure and scheduling latency
  - further check the tidb details metrics in tidb.json if neccessary, related setions: Query Summary,Query Detail,Server,Transaction,Executor,Distsql,KV Errors,KV Request,PD Client,Schema Load,DDL,Dist Execute Framework,Statistics & Plan Management,Owner,Meta,GC,Batch Client,TopSQL,TTL,Resource Manager,Follower Read,Import Into,Global Sort

- Slow TiKV issues
  - per-instance outlier: `tikv grpc latency by instance (p80, p95, p99, p999)` shows one instance persistently much worse than peers
  - per-instance outlier: read/write hotspot issue (TiKV grpc qps unbalance)
  - **MUST** check If TiKV shows slow reads or writes, you must check node-info.json including CPU, memory, disk, and network, and whether network or disk(ebs) exceeded is present.
  - **MUST** check: busy or unbalanced threads, check the tikv_thread_cpu_seconds_total by (instance,name)
    - if the threads usage is very unbalanced, need to resolved the hotspot/unbalanced issue
    - if the threads usage is relatively balanced
      - if a threas > 80% usages and tikv is high cpu, need to scale-out/up tikv
      - if a threas > 80% usages and tikv is low cpu, need to increase the thread pool size to allocate more threads

  - **CRITICAL: Distinguish "Slow" vs "Hot" — P99 high ≠ CPU hotspot**
    - **"Slow"** = high P99/max latency per request. Example: ANALYZE P99 = 10.4s. This means each request takes long, but may not consume much total CPU if frequency is low.
    - **"Hot"** = high total CPU consumption. Measured by `sum(rate(tikv_coprocessor_request_handle_seconds_sum[2m])) by (type)` or TopSQL `topsql_cpu_time_ms`. A SELECT with P99 = 200ms but running 100x/sec contributes far more CPU than an ANALYZE with P99 = 10s running once.
    - **When diagnosing CPU hotspot**: Always use **total CPU time** (sum of rate * duration, or TopSQL CPU attribution), NOT P99 latency. P99 tells you which requests are slow, but the hotspot root cause is determined by which SQL consumes the most aggregate CPU time.
    - **Common mistake**: Seeing `tikv_coprocessor_request_handle_seconds` P99 high for `analyze_full_sampling` and concluding ANALYZE causes the CPU hotspot. ANALYZE is IO-bound with high per-request latency but often low total CPU contribution. Check `sum(rate(handle_seconds_sum))` by type to compare actual total CPU cost.

  - **MUST** use TopSQL for instance-level CPU attribution when a TiKV hotspot instance is identified:
    - Query TopSQL with `component = 'tikv'` + specific instance filter + incident time range
    - This directly answers "which SQL is consuming the most CPU on this specific TiKV node"
    - Do NOT rely solely on coprocessor P99 or slow query logs — they show latency, not CPU contribution

  - slow write: kv_prewrite = async write + async snapshot + scheduler pool wait + scheduler command read
    - async write = store duraton + apply duration
  - slow read: = unified pool wait + read duration, high unified read pool running tasks indicates read pressure
    - too many mvcc issues: total keys(next/prev operation in coprocess) much higher than processed keys
  - check flow control/KV errors to rule out the flow control/Server is busy, once flow control is triggered, the whole system will be throttle.
  - further check the tikv details metrics according to the cluster type:
    - dedicated: tikv-details-dedicated.json
    - Premium,BYOC: tikv-details-premium.json
    - starter/essential: tikv-details-starter-essential.json and one-screen-starter-essential.json
    - tikv details related setions: Duration,Cluster,Errors,Server,gRPC,Storage,Local Reader,Thread CPU,IO Breakdown,Raft Waterfall,Cloud Worker Service,Raft IO,Raft Propose,Raft Process,Raft Message,Raft Admin,Raft Log,Raft Engine,RocksDB - kv,Titan - kv,In Memory Engine,Flow Control,Scheduler,Scheduler Worker Pool,Scheduler - <specific kv request type>,Coprocessor Overview,Coprocessor Detail,Unified Read Pool,GC,Pessimistic Locking,Task,PD,Slow Trend Statistics,Snapshot,Resolved TS,Point In Time Restore,Backup & Import,Backup Log,Threads,Memory,Resource Control,Status Server,Encryption,TTL
    - one-screen-starter-essential.json related setions: Clusters,Storage,TiDB,TiKV,TiKV Worker,TiKV masked,PD,TiFlash,Gateway,Manager,ETCD,Cluster Proxy,Cluster Autoscaler,Regional EKS Events,TiDB Worker,Vmagent,Vector Agent
- node issues
  - check the os information by node-info.json, related sections: Node Info,Network,Memory,CPU,Disk,EBS Volume Performance,TCP,
  - when a node is an outlier, like TiKV-side read or write latency becomes slow, `node-info.json` disk checks are mandatory; use the cluster's configured IOPS/Bandwidth limit and latency baseline, not a fixed threshold
- PD issues
  - check high cpu/memory usage
  - check slow service time tso handle, get region rpc or region heartbeat
  - if necessary, further check the pd details metrics: pd.json, including: Cluster,Operator,Statistics - balance,Statistics - hot write,Statistics - hot read,Scheduler,Scatter and Splitter,gRPC,HTTP,etcd,TiDB,Heartbeat,Region storage,DR Autosync,GO Runtime
- TiFlash issue
  - check high cpu/memory/io usage
  - if necessary, further check the tiflash metrics tiflash.json, related sections: Server,Threads CPU,Coprocessor,Task Scheduler,DDL,Storage,Storage Write Stall,PageStorage,Raft,Rough Set Filter Rate Histogram,Vector Search,Disaggregated,S3,Pipeline Model,TiFlash Resource Control,


###  Query Tuning guideline

Use this when the bottleneck is clearly SQL-level rather than infrastructure-level.

1. Identify top-impact SQL templates:
   - **TopSQL** (primary tool for CPU attribution): query with `component = 'tikv'` + instance filter to find which SQL actually consumes the most CPU on hotspot nodes. This is the most direct way to attribute CPU usage to specific SQL.
   - SQL Statements page: highest total latency + execution frequency
   - Slow Queries page: slowest queries and high TiKV-read queries (but note: "slowest" ≠ "most CPU-consuming")
2. Analyze execution plans using **`decoded_plan`** (NOT `plan` which is usually empty) with "first child first" to identify the bottleneck:
   - Focus on operators with highest `execution info` time
   - Compare `actRows` and `estRows` to detect estimation errors — if `actRows >> estRows` (e.g., 10x+), statistics are stale
   - Check per-operator `memory` and `disk` for spill/pressure
   - Check `stats` field for statistics health and `warnings` for optimizer warnings
3. Common solution
    - check if statistics is updated
    - Apply TiDB-specific index strategy:
      - Primary goal: reduce KV RPC count and unnecessary row lookups
      - Prefer covering/composite indexes to avoid full scans and full sorts
      - index column order strategy
          - Index Prefix Columns - Equality Condition Columns First
            - Equality (=) condition columns
            - IS NULL condition columns
            - IN conditions with single values (e.g., IN (1))
          - Add Sortable Columns for Index-Based Sorting. Note: all sort columns must be included:
          - Add none-equal filter columns to prevent additional filtering after table lookups:
          - If feasible, include All select-list Columns to avoid table lookup

    - Route suitable analytical SQL to TiFlash:
      - Complex scans/aggregations/joins on large datasets

### Conf Profilling Metrics

Check the significant usage in the cpu and heap profile

### Source Code Analysis and GitHub Search

If a panic, OOM, fatal message, or recurring error appears in logs, collect the stack trace and relevant error output first, then inspect the code under `~/code`. To navigate the correct code path, identify the exact git commit hash or tag for the affected component from the startup logs around `Welcome`. Search the corresponding repository for related issues and pull requests with `gh`. All relevant repositories for both data plane and control plane are included.

Dataplane code path is under `~/code/{pingcap,tikv,tidbcloud}`. Special code paths:
- TiKV
  - Dedicated: `tikv`
  - Starter/Essential/Premium/BYOC: `cloud-storage-engine`
- TiCDC: `tiflow`

Control plane code is under `~/code/tidbcloud`. For TiDB Cloud resource code analysis, use `knowledge/tidbcloud-source-code.md` as the repo-to-tool index. It maps each Tier x Tool to the relevant global control plane, regional control plane, controller, CRD, and kernel repositories, and includes key call chains and module names.

This resource code analysis workflow applies to control plane issues as well, not only to data plane crashes. Use it whenever the symptom points to:
- control plane workflow or scheduler failures
- regional API or gateway/proxy errors
- Kubernetes resource reconciliation problems
- CRD / controller mismatches
- Import / Changefeed / DM / Export / Backup / Restore state-machine issues

Typical control plane repos to inspect include:
- `dataflow-service`
- `dataflow-service-ng`
- `serverless-service`
- `cluster-service-ng`
- `tidb-management-service`
- `infra-provider`
- `aws-shared-provider`
- `tcidm`
- `tidb-operator`

For control plane resource tracing, start from the user-visible tool and cluster tier, then use `knowledge/tidbcloud-source-code.md` to locate the exact repo path and follow the chain from model or workflow code to regional API, controller or reconciler, CRD type, and finally the data plane entrypoint if needed.

Use this workflow:

1. **Analyze findings**
   - Trace the call stack if needed (search for function callers)
   - Read the source files where errors are defined or returned
   - Understand the conditions that trigger the error
   - Check default values for relevant configuration parameters
   - Look for comments that explain intended behavior or edge cases
   - For control plane cases, trace resource lifecycle and ownership: API or workflow -> regional service -> reconciler/controller -> CRD/resource -> pod or job execution

2. **Cross-reference with GitHub issues**
   - Verify that source code behavior matches issue descriptions
   - Check whether the identified error path aligns with observed symptoms
   - Confirm that the configuration parameters mentioned in issues actually exist
   - For control plane repos, also check PRs/issues related to controller behavior, workflow transitions, CRD schema changes, and version-specific resource handling