# Postmortem: O11Y-2190994

> Workspace: `ws-1774410422679-a81490e96`
> Date: 2026-05-09
> Reviewer: claude-code

## Issue Summary

| Field | Value |
|-------|-------|
| Issue ID | O11Y-2190994 |
| Alert Type | TiDB P999 Latency |
| Cluster ID | Unknown |
| Cluster Type | Dedicated |
| TiDB Version | Unknown |
| Time Window | 2026-03-11 02:37:00 - 03:37:00 UTC |

**Issue Description**: TiDB P999 query latency spiked to 5.77s (>10x 24h baseline). Root cause: compound — business read load surge (+1253% SELECT time) triggering EBS IOPS throttling on 58 volumes, with db-tikv-19 as the hotspot instance (coprocessor P99 22.49s).

## Actual Root Cause

> From workspace conversation — no Jira resolution available; inferred from agent diagnosis.

**Compound root cause**: Business read load surged 13.5x (SELECT time 199→2696 s/s), triggering EBS IOPS throttling on 58 volumes (peak 39.6%). db-tikv-19 became the hotspot (coprocessor P99 22.49s at 03:05 UTC, CPU 20+ cores). Multiple unoptimized `SELECT *` queries on `dh_active_receive_logs` (40,865 executions, avg 4.77s) were the primary application-layer driver.

## Agent Diagnosis Summary

The agent correctly:
1. Identified EBS IOPS throttling (58 volumes >20%, peak 39.6%) as infrastructure root cause
2. Identified SELECT load surge as application root cause (+1253%)
3. Identified db-tikv-19 as the hotspot TiKV instance (22.49s coprocessor delay)
4. Identified Top-5 slow queries, all on `dh_*` tables with `SELECT *` anti-patterns
5. Provided causal chain: read surge → EBS IOPS limit → TiKV coprocessor backpressure → P999 spike
6. Provided specific index recommendations for top slow query tables

The agent **skipped**:
- TopSQL TiDB/TiKV (used MCP + clinic-api directly; pre-collect_evidence.py workflow)
- Loki logs (no log evidence collected)
- Decoded query plan analysis (no `decoded_plan` fetched)

Note: This workspace predates collect_evidence.py. The current skill version would collect more data sources.

## Scores

| Dimension | Score (1-5) | Notes |
|-----------|-------------|-------|
| Root Cause Accuracy | 5 | Correctly identified compound root cause with hotspot instance |
| Evidence Completeness | 3 | Metrics + DB time + slow queries good; no TopSQL/Loki/decoded_plan |
| Causal Chain | 5 | Clear: read surge → EBS throttle → TiKV coprocessor → P999 spike |
| Actionability | 5 | Specific index DDL, SELECT optimization, EBS upgrade, TiFlash suggestion |
| Efficiency | 3 | 21 tool calls; pre-collect_evidence.py auth discovery overhead |
| **Overall** | **4.2** | Strong diagnosis limited by pre-collect_evidence.py tooling |

## Human Guidance Log

No manual human interventions recorded. The agent ran autonomously.

## Skill Deficiencies

### ISS-0007 (pre-existing): No hotspot instance TopSQL drill-down

- **Type**: FLOW
- **Skill**: `tidb-clinic-diagnosis`
- **Current behavior**: Agent identified db-tikv-19 as hotspot but did not drill down to its specific TopSQL
- **Expected behavior**: After identifying a hotspot TiKV instance by coprocessor latency, agent should query that instance's TopSQL to identify the SQL causing the hotspot
- **Priority**: P2-medium
- **Status**: open (already tracked as ISS-0007)

### ISS-0005 / ISS-0006 (pre-existing): TopSQL not queried

- **Type**: FLOW
- **Status**: fixed in collect_evidence.py (pre-existing, already tracked)

## Key Queries

### EBS IOPS Throttling

```bash
# clinic-api: 58 volumes >20%, peak 39.6%
```

### TiKV Coprocessor Hotspot Identification

```promql
# hotspot: db-tikv-19 coprocessor P99 = 22.49s at 03:05 UTC
# cpu peak: 20.18 cores
```

### Causal Chain Timeline

- 03:05 UTC: db-tikv-19 coprocessor latency reaches 22.49s
- 03:06 UTC: db-tikv-29, db-tikv-30 also spike
- 03:07 UTC: TiDB P999 alerts at 5.77s

## Lessons Learned

1. **Read-dominant compound root cause pattern**: SELECT time +1253% + EBS throttling is a recurring pattern (also O11Y-2233230, same week). Both cases involve the same `dh_*` table family, suggesting same customer cluster.

2. **Hotspot instance identification is important even without TopSQL**: Agent correctly surfaced db-tikv-19 from coprocessor metrics alone. TopSQL drill-down on the hotspot instance would confirm which SQL.

3. **Pre-collect_evidence.py baseline**: This case shows the skill quality before the current tooling. Root cause accuracy was high (5/5) because EBS+read surge is visible from metrics alone, but evidence completeness suffered from lack of logs and TopSQL.