# Postmortem: O11Y-2328599 (Re-run)

> Workspace: `ws-1776256323680-07f26bb90`
> Date: 2026-04-20
> Reviewer: claude-code
> Source: GitHub issue #144
> Previous postmortem: `postmortem.md` (workspace `ws-1775365141488-ca92b0b5d`)

## Issue Summary

| Field | Value |
|-------|-------|
| Issue ID | O11Y-2328599 |
| Alert Type | TiDBExpensiveQueryCntGt500AndIncreaseFor10m |
| Cluster ID | 10754652599988043412 |
| Cluster Type | dedicated |
| TiDB Version | v8.5.4 |
| Time Window | 2026-04-05 03:10 - 05:40 UTC |
| Environment | dev |
| User Feedback | "也没有 clinic logs link" (missing Clinic log viewer URLs in output) |

## Actual Root Cause

(Same as original postmortem) Table `dh_message_setting_ext` (alias `c`) lacked a proper index for the `tag_id` filter used in a high-frequency JOIN query. The `IndexLookUp` on table `c` scanned 303,713 rows and took 15 minutes per execution, causing TiKV-19 to become a hotspot node (3.66x average CPU). This triggered cascading `server_is_busy` errors and expensive query accumulation across the cluster.

## Agent Diagnosis Summary

This was an **auto-diagnosis** (0 human interventions, 33 tool calls). The agent:

1. Correctly identified `dh_message_setting` JOIN query as the most impactful (avg_qt=178.8s, 8467 executions)
2. Queried TopSQL for both TiDB and TiKV components
3. Fetched decoded execution plans for the top queries
4. Applied `ic-002` (incorrect conclusions knowledge) to self-correct from "ANALYZE" blame

**However, the agent:**
- Focused on table `a` (`dh_message_setting`) IndexFullScan on PRIMARY as the bottleneck
- Attributed root cause to "stats-driven bad access path" (optimizer choosing wrong index due to stale stats)
- Did NOT identify table `c` (`dh_message_setting_ext`) as the actual time-consuming operator (15 min IndexLookUp)
- Did NOT include instance dimension in TopSQL — missed TiKV-19 hotspot entirely
- Did NOT query Loki logs or cluster events
- Did NOT provide Clinic log viewer links (user complaint)

## Rubric Evaluation

| Rubric Item | Pass/Fail | Notes |
|-------------|-----------|-------|
| Correctly identify `dh_message_setting_ext` (table c) missing index as root cause | FAIL | Focused on table a's stats issue |
| Query TopSQL with component=tikv | PASS | Tool call shows TiDB+TiKV query |
| Include instance dimension in TopSQL to identify TiKV-19 | FAIL | No instance breakdown, no TiKV-19 mention |
| Cross-reference with SlowQuery execution plan | PASS | Fetched decoded plans, cross-referenced with TopSQL |
| Identify correct bottleneck operator by execution time | FAIL | Identified IndexFullScan_183 on table a, not IndexLookUp on table c (15 min) |
| Provide specific index recommendation for table c | FAIL | Recommendations for table a only |

**Result: 2/6 passed. REGRESSION FAILED.**

## Scores

| Dimension | Score (1-5) | Notes |
|-----------|-------------|-------|
| Root Cause Accuracy | 2 | Wrong table (a vs c), wrong mechanism (stats vs missing index) |
| Evidence Completeness | 3 | TopSQL+SlowQuery+plans queried, but no instance dimension, no logs, no events |
| Causal Chain | 3 | Chain exists but points to wrong table |
| Actionability | 2 | Conditional recommendations ("if prefix doesn't match..."), wrong target |
| Efficiency | 4 | 33 tool calls, 0 human interventions, autonomous completion |
| **Overall** | **2.8** | |

## Comparison with Previous Run

| Metric | Previous (ws-1775365141488) | This run (ws-1776256323680) |
|--------|---------------------------|---------------------------|
| Human interventions | 10 | 0 (auto-diagnosis) |
| Tool calls | ~30 (first round) | 33 |
| Root cause accuracy | 2 (blamed ANALYZE) | 2 (blamed stats on table a) |
| TopSQL queried | No (skipped entirely) | Yes (TiDB+TiKV) |
| Instance dimension | No (needed human prompt) | No (still missing) |
| Table c identified | No (needed human prompt) | No (focused on table a) |
| ic-002 applied | No | Yes (self-corrected from ANALYZE) |

**Progress**: TopSQL is now mandatory (ISS-0005 fixed), ic-002 prevents pure ANALYZE blame. But the core issue remains: agent doesn't drill into per-operator execution time to find the real bottleneck (table c), and doesn't use instance dimension for hotspot detection.

## Data Source Coverage

- [x] Metrics (via collect_evidence.py)
- [x] TopSQL TiDB
- [x] TopSQL TiKV
- [x] SlowQuery logs
- [x] Decoded execution plans
- [ ] Loki error logs
- [ ] Cluster events
- [ ] Instance-level hotspot analysis

## Skill Deficiencies (persistent from original postmortem)

| ID | Issue | Status |
|----|-------|--------|
| ISS-0007 | No hotspot node drill-down rule (instance dimension) | open |
| ISS-0009 | Missing cross-source validation | open |
| ISS-0012 | Execution plan analysis qualitative not quantitative | open |

## New Issue: Missing Clinic Links in Output (ISS-0024)

User feedback from issue #144: agent output doesn't include Clinic log viewer URLs. Users expect links like `https://clinic.pingcap.com/clusters/{cluster_id}/logs?query=...&start=...&end=...` in the diagnosis output for easy drill-down. Filed as [ISS-0024](https://github.com/tidbcloud/investibook/issues/230).

## Lessons Learned

1. **TopSQL now works autonomously** — ISS-0005/0006 fixes effective, agent queries both components without human prompting
2. **ic-002 prevents ANALYZE-as-root-cause** — agent self-corrected, but landed on wrong alternative (stats on table a vs missing index on table c)
3. **Instance dimension still missing** — ISS-0007 not fixed, agent never groups by instance to find hotspot nodes
4. **Quantitative plan analysis still absent** — ISS-0012 not fixed, agent identifies "IndexFullScan" qualitatively instead of sorting operators by time
5. **Output lacks deep links** — user wants Clinic URLs for logs/metrics/topsql viewer embedded in the output