# Postmortem: TCID-12704

> Workspace: `ws-1779156165000-34f5ef8b3` (local replay)
> Original prod workspace: `ws-1779071696491-aabaf929c`
> Date: 2026-05-19
> Reviewer: claude-code

## Issue Summary

| Field | Value |
|-------|-------|
| Issue ID | TCID-12704 |
| Alert Type | Customer-reported incident: query latency + client disconnections/retries |
| Cluster ID | 10123293495242577103 |
| Cluster Type | dedicated (GCP, asia-northeast1) |
| TiDB Version | unknown (same cluster as TCID-12692) |
| Time Window | 2026-05-13 14:35～14:53 JST (05:35～05:53 UTC) |

**Issue Description**: Customer (Japan, same cluster as TCID-12692) reported service latency degradation starting 14:35 JST, accompanied by client disconnections and retries. User feedback: "The evidence you provided explains the disconnections caused by TiDB node scale-in, but didn't explain the client retry caused."

## Actual Root Cause

> Derived from Activity Hub events retrieved during follow-up (not available in original workspace diagnosis).

At 14:35 JST, `coupon_attached_items` queries with large IN-clause ranges (177 ranges × 22,427 executions/18 min) drove TiDB CPU to 75% across multiple nodes. At 14:37:37 JST (2 min after incident start), a `ModifyClusterSize` operation scaled TiDB in from 34→33 nodes via OpenAPI key `C2LJ8EC0`. This scale-in during peak load caused:
1. Existing TCP connections to the removed TiDB node were dropped → **client connection errors + retries**
2. Load redistributed to 33 remaining nodes → latency further increased

Scale-out (33→34) at 14:50 began recovery; completed at 14:53. A further scale-out (34→36) at 15:11 stabilized the cluster.

The **client retry** was directly caused by the scale-in terminating active connections — this was the gap in the original diagnosis that the user reported.

## Agent Diagnosis Summary

**Initial diagnosis (no human guidance)**:

The agent correctly identified:
- TiDB CPU high (75% on db-tidb-960, 68-75% on top 5 instances)
- `coupon_attached_items` query (digest `139ecf42`) as top slow query: 22,427 executions, avg 0.58s, max 3.40s, IndexRangeScan with 177 ranges
- TiKV unified_read thread hotspot (db-tikv-26: 17 cores) consistent with bulk range reads
- 3 TiDB "restarts" (db-tidb-1905/1906/1907) flagged but not investigated for cause
- TopSQL unavailable (Delta Lake path not found for GCS cluster)

**Gap**: The agent flagged the 3 TiDB instance restarts as a "possible correlation" but did NOT check Activity Hub events to determine whether they were scale-in operations or OOM/crashes. Without this, the causal chain linking scale-in → connection drop → client retry was missing.

**After 1 follow-up** (explicit prompt to check Activity Hub for the restart cause):

The agent retrieved Activity Hub events and correctly identified:
- All 5 events were `ModifyClusterSize` (no OOM/crash events)
- Scale-in 34→33 at 14:37:37 caused existing connections to drop → client retries
- Scale-out 33→34 at 14:50:30, completed 14:53:36 = incident end
- Additional scale-out 34→36 at 15:11 for prevention

## Scores

| Dimension | Score (1-5) | Notes |
|-----------|-------------|-------|
| Root Cause Accuracy | 3 | Initial diagnosis missed scale-in → client retry (user's complaint). Correct after 1 follow-up. |
| Evidence Completeness | 4 | metrics ✓, slowquery ✓, decoded_plan ✓, logs ✓, events ✓ (after follow-up); TopSQL blocked by Delta Lake (ISS-0057 known) |
| Causal Chain | 4 | After follow-up: complete chain from query load → CPU high → scale-in → connection drop → client retry |
| Actionability | 4 | Good recommendations: batch size reduction for IN-clause, ANALYZE, scale-in policy guard |
| Efficiency | 3 | 1 human intervention required to get Activity Hub events; should have been automatic given detected "restarts" |
| **Overall** | **3.6** | |

## Human Guidance Log

### Intervention 1: Check Activity Hub for restart cause

- **Round**: #3 (user message)
- **User said**: "Please also check cluster events/activity hub to confirm whether the 3 TiDB instance restarts (db-tidb-1905/1906/1907) were caused by scale-in operations or OOM/crash. This is important to explain the root cause of client disconnections and retries."
- **Agent's mistake**: Flagged 3 TiDB "restarts" in initial diagnosis but did not automatically check Activity Hub to determine whether they were scale-in operations or actual failures (OOM/crash). Left the restart cause as "may be correlated" without investigation.
- **Correct behavior**: When node instance count changes or "restarts" are detected in metrics, automatically query Activity Hub events before concluding on restart cause. This applies regardless of alert type — any instance count drop should trigger an Activity Hub lookup.
- **Skill**: `tidb-clinic-diagnosis`
- **Deficiency type**: FLOW

## Skill Deficiencies

### New: Activity Hub check should trigger on any detected node restart/count change

- **Type**: FLOW
- **Skill**: `tidb-clinic-diagnosis`
- **Current behavior**: Agent detects TiDB instance restart metrics but only checks Activity Hub in specific scenarios (ISS-0039 fixed for "node restart scenarios"). In this case the restarts were inferred from metrics showing 3 specific instances, and the agent did not automatically check Activity Hub.
- **Expected behavior**: Whenever node instance count decreases OR specific instances disappear between time windows, automatically query Activity Hub events before concluding on cause. The scale-in event was 2 minutes into the incident and directly caused the client disconnections — it was the critical missing link.
- **Suggested fix**: Strengthen the Activity Hub check trigger in SKILL.md: "If component_health shows instance count decrease OR if any instance appears to have restarted (restart count > 0 in the window), ALWAYS check Activity Hub events before concluding on cause."
- **Priority**: P1-high
- **Status**: open
- **Relates to**: ISS-0039 (Activity Hub for node restart — previously fixed but not comprehensive enough for scale-in detection)

### Known: TopSQL unavailable for GCS clusters (Delta Lake path error)

- **Type**: TOOLING
- **Skill**: `tidb-clinic-diagnosis`  
- **Current behavior**: collect_evidence.py A4 returns HTTP 500 for GCS clusters (Delta Lake path not found)
- **Expected behavior**: Clear message "TopSQL unavailable (data pipeline issue)" in diagnosis output
- **Suggested fix**: See ISS-0057/plan.md
- **Priority**: P2-medium
- **Status**: open (ISS-0057)

### Known: vmstorage sample limit throttling on metrics API

- **Type**: TOOLING
- **Skill**: `tidb-clinic-diagnosis`
- **Current behavior**: TiDB P99 latency metric query returned "error: err: sample limit exceeded" from vmstorage. Agent falls back to other metrics but TiDB P99 is lost.
- **Expected behavior**: Detect sample limit error and retry with reduced time range or step interval to get partial data.
- **Priority**: P2-medium
- **Status**: open (possibly new issue — needs dedup check)

## Key Queries

### Activity Hub events for TCID-12704 (retrieved in follow-up)

```bash
# cluster_id: 10123293495242577103
# time window: 2026-05-13 14:35-15:15 JST (05:35-06:15 UTC)
# Result: 5x ModifyClusterSize events
# 14:37:37 JST: started TiDB 34→33 (scale-in)
# 14:39:11 JST: succeeded
# 14:50:30 JST: started TiDB 33→34 (scale-out)
# 14:53:36 JST: succeeded (incident end)
# 15:11:03 JST: started TiDB 34→36
```

### SlowQuery top digest (coupon_attached_items)

```bash
# digest: 139ecf4233a034aaec9d3a8f45a72928f8fc2244fceda06577fad7ff7e4ad755
# 22,427 executions in 18 min, avg 0.58s, max 3.40s
# Plan: IndexRangeScan on idx_item_id_expire_used_created_coupon_id, 141 tasks, 177 ranges
# process_time=0.014s wait_time=0.028s → TiDB scheduling bottleneck, not TiKV
```

## Lessons Learned

- **Scale-in during high load causes client disconnections**: When `ModifyClusterSize` scale-in occurs, existing TCP connections to the removed node are terminated. Clients see connection errors and retry — this is the direct cause of "client retry" complaints. Agents must check Activity Hub to identify scale-in events when instance counts drop or "restarts" are detected.
- **Activity Hub lookup is mandatory for any restart-like signal**: ISS-0039 fix covered explicit "node restart" scenarios, but `component_health` metric count drops also indicate topology changes that require Activity Hub verification. The fix should be broadened.
- **GCS Delta Lake TopSQL unavailability (ISS-0057) affects Japan clusters**: Same as TCID-12692 — GCS clusters frequently have Delta Lake path errors. Agent should clearly flag this and rely on SlowQuery as primary evidence.
- **vmstorage sample limit throttling**: P99 latency metric for large clusters can exceed vmstorage sample limits. This is a separate data availability issue worth tracking.
- **IN-clause size as a workload indicator**: `coupon_attached_items` query with 177-range IN-clause causing 22K executions/18min is an app-layer scaling pattern — as cluster scales out under load, the query volume doesn't decrease proportionally, suggesting per-connection query generation rather than shared pooling.