# Expected Diagnosis: O11Y-2377229

> Ground truth for regression testing
> Case type: Infrastructure bottleneck + High-frequency query workload

## Root Cause

**Primary**: EBS IOPS throttling affecting multiple volumes (>20% throttle rate)

**Secondary**: High-frequency migration queries consuming significant TiKV CPU resources

**Contributing Factor**: ResolveLock RPC overhead indicating transaction conflicts or stale locks

## Evidence Chain

### 1. Infrastructure Layer
- 8 EBS volumes experiencing >20% IOPS throttling
- Peak throttle rate: 26% on specific volumes
- Direct impact on I/O latency and TiKV performance

### 2. Query Layer
- Top 3 SQL digests consuming significant TiKV CPU:
  - Digest 5FE6D785: 12.8% TiKV CPU
  - Digest EE719BD1: 10.0% TiKV CPU
  - Digest 37705ED2: 9.3% TiKV CPU
- All queries related to migration table operations

### 3. System Impact
- 3 TiKV instances with CPU >80% (db-tikv-3: 89%, db-tikv-1: 85%, db-tikv-10: 83%)
- ResolveLock RPC taking 54% of query execution time (622.8ms / 1150ms)

## Causality

```
EBS IOPS Throttling
    ↓
Increased I/O Latency
    ↓
TiKV CPU Saturation (handling slow I/O + high query volume)
    ↓
Query Performance Degradation
```

**Amplification Factor**: High-frequency migration queries amplify the impact of I/O throttling by maintaining sustained load on already-stressed TiKV instances.

## Recommendations

### Priority 1: Fix EBS IOPS Throttling
- Increase provisioned IOPS for affected volumes
- Consider migrating to gp3 volumes for better IOPS/cost ratio
- **Why**: Infrastructure bottleneck is the primary cause

### Priority 2: Optimize Migration Queries
- Add WHERE clause filters to reduce scan range
- Implement pagination (LIMIT/OFFSET or cursor-based)
- Create composite index on `migration.table_name` + other filter columns
- **Why**: Reduce CPU load on TiKV even after I/O is fixed

### Priority 3: Investigate ResolveLock Overhead
- Check for long-running transactions
- Examine lock contention on migration tables
- Verify GC safepoint is advancing normally
- **Why**: High ResolveLock time suggests transaction management issues

### Priority 4: Monitor TiDB GC
- Track GC metrics (P99 pause time currently at 0.111s)
- Consider Continuous Profiling if GC pressure increases
- **Why**: Preventive monitoring, not urgent

### Do NOT: Run ANALYZE
- Statistics divergence is only 1.57x (below 2x threshold)
- Current execution plan is already optimal (IndexRangeScan)
- ANALYZE would waste resources without benefit

---

## Scoring Rubric

> The agent MUST satisfy ALL items below to pass regression:

### Data Coverage (5 items)

- [ ] **Metrics**: Query EBS IOPS metrics and identify throttling >20%
- [ ] **Metrics**: Query TiKV CPU and identify ≥3 instances >80%
- [ ] **TopSQL TiKV**: Query TopSQL component=tikv and identify top 3 SQL digests with CPU% ≥5%
- [ ] **TopSQL TiDB**: Query TopSQL component=tidb for cross-validation
- [ ] **SlowQuery + Plan**: Get decoded execution plan for at least one hot query digest

### Root Cause Identification (3 items)

- [ ] **Identify EBS throttling**: Explicitly state EBS IOPS throttling as primary or co-primary root cause
- [ ] **Identify query workload**: Explicitly state high-frequency migration queries as contributing factor
- [ ] **TiKV CPU hotspot**: Identify specific TiKV instances with high CPU (>80%)

### Evidence Quality (4 items)

- [ ] **Specific SQL digests**: Name at least 2 specific SQL digest IDs (e.g., "5FE6D785", "EE719BD1")
- [ ] **Quantify throttling**: Provide specific throttle rate percentages (e.g., "26%", "25%")
- [ ] **Quantify CPU**: Provide specific CPU usage percentages for TiKV instances (e.g., "89%", "85%")
- [ ] **ResolveLock overhead**: Mention ResolveLock RPC time or percentage in query execution

### Recommendations (4 items)

- [ ] **Fix EBS throttling**: Recommend increasing provisioned IOPS or migrating to gp3
- [ ] **Optimize queries**: Suggest adding filters, pagination, or indexes for migration queries
- [ ] **Prioritization**: EBS fix should be higher priority than query optimization
- [ ] **Negative recommendation**: Explicitly state NOT to run ANALYZE (due to low stats divergence)

### Cross-Validation (2 items)

- [ ] **Timeline alignment**: Verify that EBS throttling, TiKV CPU spikes, and query patterns occur in same time window
- [ ] **TopSQL cross-check**: Validate that SQL digests appear in both TiKV and TiDB TopSQL results

---

**Total Rubric Items**: 18

**Pass Criteria**: 18/18 (100%) - All items must pass

**Regression Status**: ✅ PASSED (Initial baseline run)

---

## Notes

- **Case Difficulty**: Medium (requires cross-layer analysis: infrastructure + application)
- **Key Skills**: `tidb-clinic-diagnosis` (metrics, TopSQL, slow query analysis)
- **Critical Insight**: Dual-layer root cause requires both infrastructure and workload optimization
- **Common Pitfall**: Focusing only on queries without identifying EBS throttling as primary bottleneck