# TiDB Query Latency Breakdown — Stage-to-Metric Templates

> Source: https://docs.pingcap.com/tidb/stable/latency-breakdown/
> Read on demand when walking the latency tree; do not load into context by default.

## How to use

Walk top-down: measure each stage p99 for the affected entity vs peers and baseline, descend into the stage that dominates, stop at the first stage that explains the latency delta. Per-stage p99 template:

```promql
histogram_quantile(0.99, sum(rate(<metric>_bucket{<selector>}[1m])) by (le, instance))
```

## 1. TiDB e2e decomposition

`e2e = token_wait + parse + compile + execute`

| Stage | Metric |
|---|---|
| token wait (usually negligible) | `tidb_server_get_token_duration_seconds` |
| parse (SQL→AST; skipped by PREPARE) | `tidb_session_parse_duration_seconds` |
| compile (AST→plan; cut by plan cache) | `tidb_session_compile_duration_seconds` |
| execute | `tidb_session_execute_duration_seconds{type="general"}` |

## 2. Execute — read paths

| Path | Decomposition |
|---|---|
| Point Get | TSO wait `pd_client_cmd_handle_cmds_duration_seconds{type="wait"}` + `tidb_tikvclient_request_seconds{type="Get"}` |
| Batch Point Get | TSO wait + `tidb_tikvclient_request_seconds{type="BatchGet"}` |
| Table / Index Scan | TSO wait + req_per_copr × `tidb_distsql_handle_query_duration_seconds{sql_type="general"}` |
| Index Lookup | TSO wait + index-scan distsql + table-scan distsql (two rounds) |

`req_per_copr = rate(tidb_distsql_handle_query_duration_seconds_count) / rate(tidb_distsql_scan_keys_partial_num_count)`

## 3. Execute — write paths

| Phase | Decomposition |
|---|---|
| lock | round × `tidb_tikvclient_request_seconds{type="PessimisticLock"}`, round = ⌈avg_regions / committer_concurrency⌉ |
| lock-time point get | `tidb_tikvclient_txn_cmd_duration_seconds{type="lock_keys"}` (×2 for non-clustered PK) |
| commit | get_latest_ts + prewrite + get_commit_ts + commit |
| — get ts | `pd_client_cmd_handle_cmds_duration_seconds{type="wait"}` |
| — prewrite | prewrite_round × `tidb_tikvclient_request_seconds{type="Prewrite"}` |
| — commit | commit_round × `tidb_tikvclient_request_seconds{type="Commit"}` |

## 4. Batch client layer

`tidb_tikvclient_request_seconds{type="?"}` =
`tidb_tikvclient_batch_wait_duration` (enqueue) + `tidb_tikvclient_batch_send_latency` (encode) + `tikv_grpc_msg_duration_seconds{type="kv_?"}` (TiKV) + `tidb_tikvclient_rpc_net_latency_seconds{store="?"}` (network)

A gap between the TiDB-side request duration and the TiKV-side gRPC duration points at batching or the network, not TiKV.

## 5. TiKV gRPC decomposition by request type

| Request | Decomposition |
|---|---|
| kv_get | snapshot + `tikv_engine_seek_micro_seconds{type="seek_average"}` + value read |
| kv_batch_get | snapshot + n × (seek_max + value reads) |
| coprocessor | `tikv_coprocessor_request_wait_seconds{type="snapshot"/"schedule"}` + `tikv_coprocessor_request_handler_build_seconds` + `tikv_coprocessor_request_handle_seconds` |
| kv_pessimistic_lock | `tikv_scheduler_latch_wait_duration_seconds{type="acquire_pessimistic_lock"}` + snapshot + in-mem/on-disk lock reads/writes |
| kv_prewrite | prewrite_key_count × read + write |
| kv_commit | commit_key_count × read + write |

Snapshot: `tikv_storage_engine_async_request_duration_seconds{type="snapshot"}` = `tikv_raftstore_request_wait_time_duration_secs` (read-index propose) + `tikv_raftstore_commit_log_duration_seconds` (read-index commit) + rocksdb snapshot (negligible).

## 6. TiKV async write (propose → commit → apply)

| Phase | Metrics |
|---|---|
| propose wait | `tikv_raftstore_store_wf_batch_wait_duration_seconds` |
| propose | `tikv_raftstore_store_wf_send_to_queue_duration_seconds` − batch_wait |
| persist raft log | (`before_write` − `send_to_queue`) + (`write_end` − `before_write`) from `tikv_raftstore_store_wf_*_duration_seconds` |
| replicate to followers | (`send_proposal` − `send_to_queue`) + (`commit_log` − `send_proposal`) |
| commit | max(persist, replicate); with async IO: max(`persist` − `send_to_queue` wait-by-worker, replicate) |
| apply | `tikv_raftstore_apply_wait_time_duration_secs` + `tikv_raftstore_apply_log_duration_seconds` |

Raft DB write (raft-engine on): `raft_engine_write_preprocess/leader/apply_duration_seconds`. KV DB apply / RocksDB write breakdown: `tikv_raftstore_apply_perf_context_time_duration_secs{type="write_thread_wait"/"write_scheduling_flushes_compactions_time"/"write_wal_time"/"write_memtable_time"}` — `write_scheduling_flushes_compactions_time` growth is the write-stall / flow-control signature.

## 7. Disk read

`read_value_duration(from_disk)` = `tikv_storage_rocksdb_perf{metric="block_read_time",req="?"}` sum / `{metric="block_read_count"}` count, req ∈ get/batch_get_command, batch_get, acquire_pessimistic_lock, prewrite, commit.

## 8. Diagnostic formulas

- Latch conflict location: `tikv_scheduler_latch_wait_duration_seconds_sum{type="acquire_pessimistic_lock"}` by instance — high on one instance → hot-region conflicts; high everywhere → application-level conflicts.
- `lock_in_mem_key_count` = rate(`tikv_in_memory_pessimistic_locking{result="success"}`) / rate(`tikv_grpc_msg_duration_seconds_count{type="kv_pessimistic_lock"}`); `result="full"` gives on-disk count.
- `prewrite/commit_key_count` = rate(`tikv_scheduler_kv_command_key_write_sum{type=...}`) / rate(`..._count{type=...}`).