# ISS-0068: 基础设施瓶颈与查询问题并存时，推荐优先级排序错误

## Background

In workspace `ws-1778205637705-1d3a5a116` (O11Y-2530675), the agent correctly identified all relevant signals:
- db-tikv-9 gRPC P99 = 104.86s vs peer average 6s (17.5x hotspot ratio)
- EBS IOPS throttling on 5 TiKV volumes, highest 40% (db-tikv-16)
- Main slow SQL: wait_time=231.7s >> process_time=10.1s
- Statistics divergence: Selection layer 29.4x (site_code on dh_active_receive_logs)
- COMMIT slow queries: avg 480s, exec_count 18,089

Despite correctly identifying all signals, the agent ranked:
- **P0**: ANALYZE TABLE dh_active_receive_logs (statistics refresh)
- **P1**: Investigate EBS IOPS throttling
- **P2**: SQL Binding
- **P3+**: TiFlash offload, TiCDC stability

The user responded with GitHub issue #415 titled "no need diagnose query, TiKV has problem" — indicating the diagnosis prioritized the wrong fix.

## Root Cause Analysis

The tidb-query-tuning skill's MANDATORY CHECKPOINT is triggered when:
1. TopSQL TiKV shows a single SQL consuming large CPU across multiple instances
2. SlowQuery avg_time > 10s AND exec_count > 100

When this checkpoint triggers, the query-tuning skill takes over and applies its own recommendation ordering — which always leads with statistics/plan fixes. The query-tuning skill does not re-evaluate the infrastructure context (IOPS, gRPC hotspot) when forming its priority order.

**Key signal that should override**: `wait_time >> process_time`

When wait_time is an order of magnitude larger than process_time:
- Process_time measures actual execution CPU/IO time
- Wait_time measures time spent waiting for resources (IO queue, lock queue, scheduler)
- A ratio of wait_time/process_time > 10x means the query is mostly waiting, not executing
- Fixing statistics won't reduce wait_time; fixing the queuing cause (IOPS throttle, lock contention) will

## Affected Code / Skill Files

The fix needs to be applied to:
1. `agentkit/skills/builtin/tidb-clinic-diagnosis/SKILL.md` — Node D reasoning rules
2. Possibly `agentkit/skills/builtin/tidb-query-tuning/SKILL.md` — MANDATORY CHECKPOINT exit rules

## Fix Plan

### Step 1: Add wait_time priority rule to tidb-clinic-diagnosis SKILL.md Node D

In the Node D (推荐生成) section, add a **priority override rule** before listing recommendations:

```markdown
#### Recommendation Priority Rules

Before ordering recommendations, apply these priority override checks:

**Infrastructure-First Rule**: If ALL of the following are true:
- wait_time > 10x process_time for the primary slow SQL
- AND at least one of: EBS IOPS throttle > 20%, TiKV gRPC instance hotspot > 5x peer average

Then:
1. Rank infrastructure fixes FIRST (P0): IOPS capacity investigation, hotspot scatter/split
2. Rank statistics/plan fixes LOWER (P1 or P2)
3. Add this sentence at the start of the diagnosis: "Primary bottleneck: TiKV infrastructure
   (IOPS throttle / node hotspot). Query execution plan issues are secondary symptoms of
   IO queuing — they should be fixed after infrastructure stabilization."

**Rationale**: wait_time measures time spent waiting for IO/locks/scheduler resources.
When wait_time >> process_time, the query spends most time waiting, not executing.
Improving the execution plan (via ANALYZE) reduces process_time but does not reduce
wait_time — the bottleneck is the resource queue, not the query's efficiency.
```

### Step 2: Add infrastructure context check to tidb-query-tuning MANDATORY CHECKPOINT

In the tidb-query-tuning skill, the MANDATORY CHECKPOINT currently triggers on:
- TopSQL TiKV single SQL high CPU multi-node + slow_query avg_time > 10s + exec_count > 100

Add a pre-condition check:

```markdown
**Before applying query-tuning recommendations, check infrastructure context:**

If evidence_summary shows:
- wait_time_ratio = wait_time / process_time > 10 for the primary slow SQL
- AND infrastructure bottleneck present (EBS IOPS > 20% OR TiKV gRPC hotspot > 5x)

Then: lead the recommendation with infrastructure fixes, demote ANALYZE/binding to secondary.
Output: "Note: ANALYZE is recommended after infrastructure stabilization, as the primary
bottleneck is IO queuing (wait_time/process_time ratio = X)."
```

### Step 3: Update the diagnosis summary format

In the final diagnosis output, when the Infrastructure-First Rule applies, add a clearly visible banner:

```markdown
> ⚠️ **Priority Notice**: Primary bottleneck is TiKV infrastructure (IOPS throttle/node hotspot),
> not query execution efficiency. `wait_time ({wait_time}s) >> process_time ({process_time}s)`
> indicates IO/resource queuing. Recommendations are ordered accordingly.
```

## Verification

After fix, replay O11Y-2530675 and verify:
1. The P0 recommendation mentions EBS IOPS investigation or TiKV hotspot resolution
2. ANALYZE TABLE is listed as P1 or P2
3. The diagnosis summary includes a note about wait_time >> process_time indicating queuing
4. The user feedback "no need diagnose query, TiKV has problem" would be satisfied by the output

## Verification Result

Verified on 2026-05-15 with dev replay workspace `ws-1778816672288-20374db49`.

- The replay identified EBS IOPS throttling and TiKV hotspot as the primary bottleneck.
- The first recommendation was to rightsize TiKV EBS IOPS provisioning and investigate TiKV node/hotspot failures.
- Query-level work was kept as a secondary follow-up rather than the primary fix.
- This satisfies the original feedback that the case should prioritize TiKV infrastructure over query/statistics work.