# Wetech Slow Query Diagnostic Fields

<!-- auto-oncall-optimization-record
{
  "schemaVersion": "auto-oncall-optimization-record-v1",
  "optimizationId": "wetech-slow-query-fields-2026-07-20",
  "problems": [
    {
      "problemId": "wetech-slow-query-missing-scan-fields",
      "summary": "Slow Query evidence omits scan, Cop wait, and RocksDB diagnostic fields.",
      "status": "optimized_verified",
      "issueKeys": ["APID-29189"],
      "sourceRefs": [],
      "signalTextIncludesAny": ["process_keys", "total_keys", "cop wait", "rocksdb", "scan fields", "slow query diagnostic", "扫描字段", "慢查询诊断字段"],
      "observedThrough": "2026-07-20",
      "effectiveAt": "2026-07-20T17:57:00+08:00",
      "verifiedAt": "2026-07-20T18:54:09+08:00",
      "attemptedAction": "Select all supported Slow Query diagnostic fields before interpreting scan and wait evidence.",
      "evidenceUrl": "https://github.com/tidbcloud/aisupport/pull/377"
    },
    {
      "problemId": "sql-tuning-mvcc-reference-not-loaded",
      "summary": "SQL tuning does not consistently apply the existing MVCC scan-amplification reference.",
      "status": "optimized_verified",
      "issueKeys": ["APID-29189"],
      "sourceRefs": [],
      "signalTextIncludesAny": ["mvcc", "scan amplification", "扫描放大", "schema 才能判断", "未加载 mvcc"],
      "observedThrough": "2026-07-20",
      "effectiveAt": "2026-07-20T17:57:00+08:00",
      "verifiedAt": "2026-07-20T18:54:09+08:00",
      "attemptedAction": "Install tidb-query-tuning and require the MVCC scan-amplification reference for matching SQL diagnostics.",
      "evidenceUrl": "https://github.com/tidbcloud/aisupport/pull/377"
    },
    {
      "problemId": "topsql-missing-capability-looks-zero",
      "summary": "Unavailable TopSQL network or logical-I/O fields can look like zero-valued evidence.",
      "status": "optimized_unverified",
      "issueKeys": ["APID-29189"],
      "sourceRefs": [],
      "signalTextIncludesAny": ["topsql", "network_bytes", "logical_io_bytes", "logical i/o", "network bytes", "字段显示为 0"],
      "observedThrough": "2026-07-20",
      "effectiveAt": "2026-07-20T17:57:00+08:00",
      "verifiedAt": null,
      "attemptedAction": "Record returned and unavailable TopSQL fields instead of interpreting absence as zero.",
      "evidenceUrl": "https://github.com/tidbcloud/aisupport/pull/377"
    }
  ]
}
-->

- Optimization ID: `wetech-slow-query-fields-2026-07-20`
- Title: Select Slow Query fields needed for TiDB query diagnosis
- Owner: auto-oncall and external `wetech-op-grafana` skill
- Source type: external skill change plus repository integration
- Source reference: `APID-29189`; aisupport PR
  https://github.com/tidbcloud/aisupport/pull/377; merge revision
  `f16870d5c8539e3e9df87d7806b7c69c6b543582`; external skill source
  `/home/nizheming/wetech-op-grafana/wetech-op-grafana`
- Problem: Wetech Slow Query list requests omitted the `fields` projection, so
  Dashboard returned only required identity values while diagnostic fields such
  as `process_keys`, `total_keys`, Cop timing, and RocksDB counters appeared as
  empty or zero. Auto-oncall therefore could not apply the existing MVCC
  scan-amplification workflow reliably.
- Capability: Wetech `sql_performance_tuning` diagnosis from Slow Query and
  TopSQL evidence.
- Implementation status: implemented, merged, and deployed to staging
- Effective status: available to staging since 2026-07-20T17:57+08:00

## Problem validation

| Problem signal | Ticket keys | Status | Evidence |
| --- | --- | --- | --- |
| Slow Query diagnostic projection omits scan and wait fields | `APID-29189` | `optimized_verified` | Staging replay run `e562529f-bf62-4096-b399-ffb2dbae9a48` selected all supported diagnostic fields and returned exact scan, wait, Cop, and RocksDB evidence for all three digests. |
| SQL tuning does not consistently load the MVCC reference | `APID-29189` | `optimized_verified` | The same replay calculated approximately 7.1x and 6.5x scan amplification, used sub-2 ms processing versus approximately 4.86 s Cop wait to reject MVCC/SQL complexity as primary, and did not request schema. |
| TopSQL version capability gaps look like zero-valued evidence | `APID-29189` | `optimized_unverified` | The deployed transport records returned and missing recommended fixed-response fields. Direct Java-Back validation shows network and logical-I/O unavailable, but the post-change replay did not need TopSQL. |

## Verification performed

- External skill unit tests passed: 14/14.
- External skill validation and Python compilation passed.
- A headless Java-Back Slow Query query at 2026-07-20T17:20+08:00 returned
  `process_keys=24`, `total_keys=156`, Cop timing, wait timing, and RocksDB
  counters from the selected diagnostic projection.
- A headless Java-Back TopSQL query confirmed the fixed response contains CPU,
  execution rate, duration, scan rates, SQL text, and plan data, while
  `network_bytes` and `logical_io_bytes` are unavailable in that Dashboard
  version.

- aisupport PR https://github.com/tidbcloud/aisupport/pull/377 and app-delivery PR
  https://github.com/tidbcloud/app-delivery/pull/74978 merged.
- Staging Deployment rolled out image revision
  `f16870d5c8539e3e9df87d7806b7c69c6b543582`; health and readiness returned
  HTTP 200.
- The live pod installed `tidb-query-tuning` and the updated external
  `wetech-op-grafana`; its transport script SHA-256 matched the published
  source (`36bc67631c3e44906126e8787f849584f9191e109ca1729e35dea508389ba436`).
- The deployed `APID-29189` dry-run `e562529f-bf62-4096-b399-ffb2dbae9a48`
  completed at the historical replay boundary with `decision=internal_only`,
  `requestedContext=[]`, and no Jira comment request.
- The replay classified the entry problem as `sql_performance_tuning`, queried
  all three digests headlessly, used exact scan/Cop/RocksDB fields, and
  attributed the multi-second latency to a node-local transient request wait
  while keeping the lower-level trigger unverified.
- 2026-07-20T18:54:09+08:00: follow-up staging dry-run
  `a45ffa1e-011c-4eac-84cd-6031033c39ed` reproduced the corrected conclusion
  and updated the existing Jira internal comment `23180127` in place through
  idempotency key `auto-live:v1:APID-29189:initial`; Jira API readback confirmed
  the same comment ID and updated timestamp, with no duplicate internal note.
- Live DB verification found no `queued` or `running` jobs and confirmed both
  Jira poll cursors were present and advancing after rollout.

## Unresolved gaps

- TopSQL network/logical-I/O capability marking still needs a case-specific
  post-change replay before it can become `optimized_verified`.
- Automatic weekly-report consumption of `docs/opts/` records remains a
  separate known gap documented in `docs/AUTO_ONCALL_EVALUATION.md`.

## Next validation action

When a future Wetech case needs TopSQL network or logical-I/O evidence, verify
that the missing-field contract prevents unavailable dimensions from being
reported as zero, then update only that problem row.