# Expected Diagnosis: TCID-12704

## Root Cause

At 14:35 JST, `coupon_attached_items` queries with large IN-clause ranges (177 ranges) drove TiDB CPU to 75% across multiple nodes (22,427 executions/18 min). At 14:37:37 JST, a `ModifyClusterSize` scale-in operation (TiDB 34→33 nodes) during peak load dropped existing connections to the removed node, directly causing client disconnection errors and retries. Scale-out (33→34) at 14:50 began recovery; cluster stabilized after 14:53.

## Evidence Chain

1. **Metrics**: TiDB CPU at 75% (db-tidb-960: 47.89/64 cores) on multiple instances; high SELECT statement rate (381K/s peak); TiKV unified_read thread high (17 cores) consistent with bulk range reads
2. **SlowQuery**: `coupon_attached_items` digest `139ecf42` — 22,427 executions, avg 0.58s, max 3.40s; IndexRangeScan with 177 ranges; process_time=0.014s vs query_time=3.40s indicates TiDB scheduling wait dominates
3. **Activity Hub events**: 5x `ModifyClusterSize` events in incident window; scale-in 34→33 at 14:37:37 JST (2 min after incident start) → scale-out 33→34 at 14:50:30, completed 14:53:36 (incident end)
4. **No OOM/crash events**: All restarts were scale-in terminations, not failures

## Causality

`coupon_attached_items` IN-clause query surge (22K execs/18min) → TiDB CPU 75% saturation across multiple nodes → `ModifyClusterSize` scale-in (34→33) at 14:37:37 during peak load → connections to removed node terminated → **client connection errors + retries** → load concentrates on 33 remaining nodes → latency further increases → scale-out 33→34 at 14:50 → recovery at 14:53

## Severity

High — service errors for 18 minutes (14:35–14:53 JST), client disconnections and retries, multiple TiDB nodes under load.

## Recommendations

1. **Reduce IN-clause batch size**: Limit `item_id IN (...)` parameter count to ≤50 items in `coupon_attached_items` queries (currently 177 ranges)
2. **Scale-in policy guard**: Suppress auto-scale-in when TiDB CPU > threshold (e.g., 60%); review OpenAPI key `C2LJ8EC0` auto-scaling policy
3. **ANALYZE TABLE coupon_attached_items**: stats_info shows potential staleness (`[260017789;32869369]`), may affect plan selection
4. **Check `ratings` table index**: `user_id` queries (21,647 slow queries, avg 0.47s) — verify index on `user_id`

## Scoring Rubric

> Checklist for regression testing. The Agent must satisfy these to pass.

- [ ] Identifies `coupon_attached_items` query with large IN-clause as primary load driver (≥22K executions, avg ≥0.5s)
- [ ] Reports TiDB CPU saturation (≥70% on multiple nodes) as the performance signal
- [ ] Checks Activity Hub events and finds `ModifyClusterSize` scale-in event at ~14:37 JST
- [ ] Correctly identifies scale-in (34→33 nodes) during high load as cause of client disconnections/retries
- [ ] Confirms scale-out (33→34) at ~14:50 JST as recovery action
- [ ] Reports TopSQL unavailability (Delta Lake path error) as a data limitation, not a diagnosis failure
- [ ] Recommends reducing IN-clause batch size for `coupon_attached_items`
- [ ] Recommends scale-in policy guard (suppress during high CPU)