# Postmortem: O11Y-2179140

> Workspace: `ws-1775015706239-80189c8b1`
> Date: 2026-04-07
> Reviewer: lingtao.li

## Issue Summary

| Field | Value |
|-------|-------|
| Issue ID | O11Y-2179140 |
| Alert Type | TiDBP999LatencySurgeNextGen |
| Cluster ID | 10805087899823024166 |
| Cluster Type | premium |
| TiDB Version | v8.5.4 |
| Time Window | 2026-03-09 04:14 - 05:14 UTC |

**Issue Description**: TiDB P999 latency surged to 19.29s (peak 29.61s), triggered by high-frequency UPDATE statements on `t_merchant_online` causing pessimistic lock contention hotspot on a single TiKV instance.

## Actual Root Cause

High-frequency point UPDATE statements on `t_merchant_online.amount_by_day` and `total_amount` (177,529 executions, avg 13.75s each) caused severe pessimistic lock contention on a single TiKV instance (`db-a924bb0f-tikv-29grsn`). Lock Manager conflicts reached 49,990/s (peak 124,313/s), scheduler latch wait P99 hit 45ms (peak 81ms), and the hotspot TiKV gRPC P99 was 98ms vs 3ms on healthy instances. Commit phase consumed 86.9% of DB time with 13.1% abort rate.

A secondary large JOIN UPDATE (`UPDATE t_merchant_online JOIN (SELECT ... SUM(real_amount))`) scanning 177M total_keys added to the contention but was not the primary driver.

## Agent Diagnosis Summary

The Agent's unguided first-round diagnosis (message #3, 42 tool calls) concluded:
- **Root cause correctly identified**: High-frequency UPDATE on `t_merchant_online` causing pessimistic lock contention hotspot on single TiKV instance
- **Evidence chain**: Database Time breakdown → TiKV gRPC latency per instance → Lock Manager conflicts → Scheduler latch wait → SlowQuery digest → Transaction phase breakdown
- **Identified hotspot instance**: `db-a924bb0f-tikv-29grsn` (gRPC P99=98ms vs 3ms on others)
- **Specific recommendations provided**: Async aggregation, batch updates, `SHARD_ROW_ID_BITS`

**Notable improvements vs O11Y-2328599**: Agent followed the skill's Node A→E flow systematically, queried metrics per-instance, identified the lock contention pattern, and provided specific SQL-level recommendations.

**Remaining gaps**: Did not query TopSQL (claimed Premium limitation), did not fetch SlowQuery execution plans, first SlowQuery SQL used non-existent `cluster_id` column.

## Scores

| Dimension | Score (1-5) | Notes |
|-----------|-------------|-------|
| Root Cause Accuracy | 4 | Correctly identified pessimistic lock contention on `t_merchant_online` as root cause. Slight deduction: did not distinguish primary driver (high-freq point updates) from secondary (large JOIN UPDATE) with quantitative evidence from execution plans. |
| Evidence Completeness | 3 | Queried Metrics (20+ queries), SlowQuery (aggregate), Logs, Events. Missing: TopSQL, SlowQuery execution plans. Premium infra metrics unavailable (acceptable). |
| Causal Chain | 4 | Clear chain: high-freq UPDATE → lock contention → single TiKV hotspot → latch wait → commit latency → P999 surge. Evidence well-linked with specific metric values. |
| Actionability | 4 | Specific recommendations: async aggregation, batch updates, SHARD_ROW_ID_BITS. Could be more specific about which SQL to optimize first. |
| Efficiency | 3 | 42 tool calls in first round. 2 calls wasted on clinic-auth.sh path discovery, 1 retry on SlowQuery cluster_id column, 2 calls formatting slow query output. Mostly systematic. |
| **Overall** | **3.6** | |

## Human Guidance Log

### Intervention 1: Summarize evidence chain by dimension

- **Round**: #4
- **User said**: "从log，query，metric，event的角度总结证据链并概括结论"
- **Agent's mistake**: First-round output was very long with interleaved tool calls and analysis text. The structured diagnosis report at the end was comprehensive but embedded in the tool call output stream, making it hard to consume.
- **Correct behavior**: Agent should proactively output a clean, structured summary after completing all evidence collection, without requiring user to ask for it.
- **Skill**: `tidb-clinic-diagnosis`
- **Deficiency type**: FLOW

## Skill Deficiencies

### D1: TopSQL not queried for Premium clusters

- **Type**: FLOW
- **Skill**: `tidb-clinic-diagnosis`
- **Current behavior**: Agent claimed "Premium 集群不支持 TopSQL 表查询" and skipped TopSQL entirely. This may or may not be true — needs verification.
- **Expected behavior**: Agent should attempt TopSQL query for all cluster types. If it fails, document the failure. If Premium truly doesn't support TopSQL, the skill should document this explicitly.
- **Suggested fix**: Add to the skill: "Always attempt TopSQL queries regardless of cluster type. If the query returns empty or errors, document it in the evidence collection as 'TopSQL: not available for this cluster type' with the actual error message."
- **Priority**: P1-high
- **Status**: open

### D2: SlowQuery execution plan not fetched

- **Type**: TEMPLATE
- **Skill**: `tidb-clinic-diagnosis`
- **Current behavior**: Agent only queried SlowQuery aggregate stats (GROUP BY digest). Did not fetch `plan` or `decoded_plan` for the top digests.
- **Expected behavior**: After identifying top slow query digests, Agent should fetch the execution plan to identify bottleneck operators and determine optimization recommendations based on quantitative operator-level data.
- **Suggested fix**: Same as O11Y-2328599 D4. Add two-phase SlowQuery analysis to the skill template.
- **Priority**: P1-high
- **Status**: open (same as O11Y-2328599 D4)

### D3: SlowQuery SQL used non-existent cluster_id column

- **Type**: TEMPLATE
- **Skill**: `tidb-clinic-diagnosis` / `clinic-api`
- **Current behavior**: First SlowQuery attempt used `WHERE cluster_id = '...'` which doesn't exist in the `slow_query_logs` table. Agent self-corrected on retry.
- **Expected behavior**: The `clusterId` should only be passed as API parameter, not in SQL WHERE clause.
- **Suggested fix**: Same as O11Y-2328599 D2. Add explicit "DO NOT USE in SQL: cluster_id" to the skill template.
- **Priority**: P0-critical
- **Status**: open (same as O11Y-2328599 D2)

### D4: clinic-auth.sh path not deterministic

- **Type**: TEMPLATE
- **Skill**: `tidb-clinic-diagnosis`
- **Current behavior**: Agent first tried `~/.opencode/skills/nutshell-skills/...` (wrong), then used `find` to locate the correct path under `/data/workspaces/{ws-id}/.opencode/skills/...`. 2 wasted tool calls.
- **Expected behavior**: Auth script path should be deterministic without filesystem search.
- **Suggested fix**: Same as O11Y-2328599 D7.
- **Priority**: P2-medium
- **Status**: open (same as O11Y-2328599 D7)

### D5: No structured summary auto-generated after evidence collection

- **Type**: FLOW
- **Skill**: `tidb-clinic-diagnosis`
- **Current behavior**: Agent completed Node A→E analysis but the final report was embedded in the tool call stream. User had to ask "从log，query，metric，event的角度总结证据链并概括结论" to get a clean structured output.
- **Expected behavior**: After completing evidence collection and analysis, Agent should automatically produce a structured diagnostic summary organized by data source dimension (Metrics, SlowQuery, Logs, Events) with a clear evidence chain and conclusion.
- **Suggested fix**: Add to Node E: "After completing your analysis, ALWAYS output a structured summary with sections: (1) Metrics Evidence, (2) Query Evidence (TopSQL + SlowQuery), (3) Log Evidence, (4) Event Evidence, (5) Evidence Chain (causal diagram), (6) Root Cause + Severity + Recommendations."
- **Priority**: P1-high
- **Status**: open

## Key Queries

### SlowQuery - aggregate top digests (correct, without cluster_id)

```bash
source /data/workspaces/{ws-id}/.opencode/skills/nutshell-skills/platform/clinic-api/clinic-token.sh && get_clinic_auth

CLUSTER_ID="10805087899823024166"

curl -s -X POST https://clinic.pingcap.com/data-proxy/query \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  -H 'Content-Type: application/json' \
  -d "{
  \"sql\": \"SELECT digest, any_value(query) AS sample_sql, COUNT(*) AS exec_count, MAX(query_time) AS max_time, AVG(query_time) AS avg_time, MAX(mem_max) AS max_mem, AVG(total_keys) AS avg_total_keys, AVG(process_keys) AS avg_process_keys FROM clinic_data_proxy.slow_query_logs WHERE date = '20260309' GROUP BY digest ORDER BY exec_count DESC LIMIT 20\",
  \"clusterId\": \"${CLUSTER_ID}\",
  \"timeout\": 120
}"
```

### Metrics - Lock Manager conflicts (key evidence)

```bash
curl -s -G 'https://clinic.pingcap.com/api/v1/orgs/1372813089197032230/clusters/2028998946604449792/metrics/query_range' \
  -H "Authorization: Bearer ${CLINIC_API_KEY}" \
  --data-urlencode 'query=sum(rate(tikv_lock_manager_waiter_lifetime_duration_count[1m])) by (type)' \
  --data-urlencode 'start=1773029640' \
  --data-urlencode 'end=1773033240' \
  --data-urlencode 'step=60'
```

## Lessons Learned

- **Agent performed significantly better on this case vs O11Y-2328599** (score 3.6 vs 2.2), likely due to the user's explicit instruction to "strictly follow the skill" and the iterative skill improvements from prior learnings.
- **Per-instance metric breakdown was done correctly** — Agent queried TiKV gRPC latency and CPU per instance, correctly identifying the hotspot node without human prompting (improvement over O11Y-2328599 where this required explicit guidance).
- **Lock contention diagnosis was well-executed** — Agent queried Lock Manager conflicts, scheduler latch wait, transaction phase breakdown, forming a strong evidence chain for the pessimistic lock hotspot.
- **TopSQL gap persists** — Even in a case where root cause was identifiable without TopSQL, the absence of TopSQL data means CPU attribution by SQL digest was not verified. The "Premium doesn't support TopSQL" claim needs verification.
- **Execution plan analysis remains a blind spot** — Agent identified the problematic SQLs but could not provide operator-level optimization advice (e.g., which index to add, which join order to change) because execution plans were not fetched.
- **Structured output should be automatic** — The user shouldn't need to explicitly ask for a summary. The skill should mandate a final structured output.