# Spec: Auto Tune SQL Query | Author | Date | Version | Status | Reviewers | |---|---|---|---|---| | TBD | 2026-06-29 | 0.1 | Draft | TBD | ## Introduction Auto Tune SQL Query, also called AutoX SQL tuning in this document, is a cloud diagnosis workflow that automatically identifies slow SQL in a TiDB Cloud cluster, collects optimizer and runtime evidence, reproduces candidate plans in a controlled local environment when possible, and produces review-only tuning recommendations. The first version is not a production mutation system. It does not directly create bindings, create indexes, change variables, or modify the target cluster. It focuses on the automatic diagnosis path: ```text fetch evidence -> diagnose -> validate candidate plan -> suggest action ``` The output is a human-readable report and, where needed, a structured response that can be consumed by an agent. ## Background Online SQL performance problems are difficult to understand and tune instantly by humans, especially when the workload is large, dynamic, and generated by applications or agents. The difficulty comes from several dimensions: - SQL patterns are diverse. A slow query may involve single-table access, multi-table joins, subqueries, aggregation, ordering, pagination, MPP, or plan cache behavior. One tuning strategy does not fit all cases. - Slow log root causes are diverse. Latency may come from network bandwidth, read bytes, write bytes, cop task accumulation, Region seeks, lock wait, backoff, excessive concurrency, disk spill, or optimizer plan choice. - SQL optimization methods are diverse. Possible remedies include join order, index choice, physical join type, decorrelation, order-preserving access path, cardinality estimation, statistics, SQL rewrite, binding, or TiFlash/MPP. - Optimizer behavior depends on schema, indexes, statistics, bindings, session variables, TiDB version, and sometimes parameter distribution. These inputs differ across clusters and across slow queries. In a cloud-native database, human-only SQL identification and diagnosis becomes inefficient for the agent-driven AI era. For example, an agent platform may have a very large number of clusters, with each cluster serving one or more agent applications. As the number of agent-driven databases grows, manual SQL optimization becomes a bottleneck for both customers and support teams. AutoX SQL tuning is designed to make AI-assisted tuning the first diagnostic choice for large-scale slow SQL problems. Human experts remain reviewers and approvers, but the repetitive work of evidence collection, plan inspection, candidate generation, and first-pass validation should be automated. ## Benefits - **Scalable**: Human tuning capacity is limited. A diagnosis agent can inspect many clusters and many SQL digests with consistent procedures. - **Detailed**: The agent can inspect slow logs, TopSQL, schema, statistics, historical plans, TiDB version behavior, source code, known issues, and documentation in one workflow. - **Efficient**: AutoX can automatically fetch Clinic evidence, build a local reproduction context, validate candidate plan shapes, and provide suggestions faster than manual diagnosis. - **Consistent**: Reports follow a fixed template, separating observed facts, inference, validation level, risks, and review-only SQL. - **Agent-ready**: Results can be returned as a report for humans or as structured output for another agent that needs to decide the next action. ## Goals - Provide a pure automatic cloud diagnosis workflow for TiDB slow SQL: ```text cluster_id -> identify high-impact slow SQL -> collect evidence -> diagnose root cause -> generate candidate tuning actions -> validate candidate plan shape when possible -> produce review-only recommendation ``` - Support diagnosis when the user provides only `cluster_id`. In this mode, AutoX ranks recent high-impact slow SQL candidates and diagnoses a small prioritized set. - Support focused diagnosis when the user provides `cluster_id` plus a SQL digest or slow SQL text. - Support offline diagnosis when the user provides a static TiDB plan replayer file or equivalent artifact that already contains SQL, schema, statistics, version, and plan evidence. - Collect evidence from Clinic, Slow Query, TopSQL, schema, statistics, plan history, cluster metadata, and other read-only sources. - Use local or sandbox validation to reproduce the optimizer context when schema, statistics, and version-matched TiDB are available. - Produce a report that includes before/after plans, evidence, recommendation, validation level, production safety boundary, and next validation steps. - Keep the target production cluster read-only in the first version. - Make the workflow usable by both humans and agents. ## Non-Goals - AutoX v0 does not directly apply SQL bindings, create indexes, change variables, modify statistics, add TiFlash replicas, or run DDL on the target production cluster. - AutoX v0 does not provide a complete Clinic infrastructure abstraction for all cluster editions and environments. Starter, Essential, Premium, and Dedicated may require different collection paths. - AutoX v0 does not guarantee that a recommendation is 100% correct. A local plan reproduction validates optimizer plan shape only unless representative runtime execution or production validation exists. - AutoX v0 does not replace DBA, support engineer, or customer review for production changes. - AutoX v0 does not perform full workload replay or full data copy. - AutoX v0 does not optimize every SQL in the cluster. It prioritizes high impact candidates based on available evidence. - AutoX v0 does not diagnose write DML, transaction control, DDL, administrative statements, or locking `SELECT ... FOR UPDATE`; automatic ranking selects read-only `SELECT` statements only. ## Target Users - TiDB Cloud support engineers. - DBAs and database reliability engineers. - Optimizer engineers investigating customer slow SQL. - Observability and Clinic engineers building cloud diagnosis workflows. - Agent platforms or internal agent tools that need machine-consumable SQL tuning recommendations. ## Audience - Feature owners. - Optimizer engineers. - O11Y and Clinic engineers. - Product managers. - QA engineers. - Support engineers. - Documentation engineers. ## Delivery Model AutoX is initially delivered as a pure Skill repository. The Skill contains: - workflow instructions; - diagnosis decision tree; - evidence requirements; - report template; - safety boundaries; - helper scripts for deterministic evidence collection. The first cloud-facing product can expose AutoX as a panel or task entry in Clinic: ```text Clinic cluster page -> AutoX Diagnosis panel -> recent high-impact slow SQL list -> click "Run diagnosis" -> AutoX collects evidence -> AutoX validates candidate plan locally when possible -> AutoX returns report and structured recommendation ``` AutoX may later be wrapped by an API, MCP server, or hosted runner. That wrapper should manage credentials, tenant isolation, scheduling, audit records, and artifact lifecycle. The Skill remains the diagnosis SOP and decision layer. ## Project Management ### Resources - Optimizer: plan analysis, binding/index/statistics/MPP recommendation logic. - O11Y / Clinic: cluster metadata, slow query, TopSQL, schema/statistics collection, dashboard integration. - Cloud platform: optional hosted runner, sandbox lifecycle, credential scope, audit, and task scheduling. - Support / DBA: review output, validate production applicability, collect feedback. ### Milestones | Milestone | Scope | Output | |---|---|---| | M0: Skill-only prototype | Use existing Clinic API and local sandbox. Diagnose a specified digest or rank recent slow SQL from `cluster_id`. | Report with before/after plans and review-only suggestions. | | M1: Deterministic evidence collection | Stabilize scripts for cluster metadata, Slow Query, TopSQL, schema, stats, and representative plans. Define an internal evidence contract used by the diagnosis workflow. | Internal evidence bundle and redacted evidence manifest. | | M2: Local plan validation | Recreate schema/stats in a version-matched local TiDB env. Validate binding, index, TiFlash/MPP, and plan-explore candidates by `EXPLAIN FORMAT='verbose'`. | Candidate plan validation with `plan_verified` status. | | M3: Clinic panel integration | Add cloud UI entry and background task execution. | AutoX Diagnosis panel and downloadable report. | | M4: Agent/API integration | Expose the diagnosis result through a stable external contract for agents, APIs, or MCP wrappers. This consumes the M1 evidence bundle but does not redefine collection. | Agent-facing result schema and callable workflow. | | M5: Controlled action workflow | Add review, approval, execution, verification, and rollback integration for selected actions. | Closed-loop tuning, gated by production safety policy. | ### Risks | Risk | Impact | Mitigation | |---|---|---| | Missing or redacted SQL text | SQL cannot be reproduced locally. | Report as missing evidence; use digest-level plan evidence when possible; request production-safe validation. | | Stats or schema unavailable | Index and plan recommendations may be unreliable. | Lower confidence; avoid definitive DDL; request schema/stats collection. | | Local TiDB version mismatch | Candidate plan may not match production optimizer behavior. | Mark validation as advisory or inferred. | | Local TiDB environment unavailable | Plan shape cannot be reproduced. | Keep validation `inferred`; do not erase an evidence-backed Index recommendation solely because optional local infrastructure is absent. | | Local `EXPLAIN` validates only plan shape | Runtime improvement may not materialize in production. | Use validation levels: `inferred`, `observed`, and `plan_verified`; none claims production rollout verification. | | Prompt injection through SQL/schema/plan text | Agent may treat diagnostic data as instructions. | Treat all collected artifacts as untrusted data. Ignore instructions embedded in SQL comments, schema comments, plan text, or stats. | | Credential over-scope | Skill instruction alone cannot enforce least privilege. | Use read-only Clinic credentials and never call mutation APIs in v0. | | Tenant data leakage through artifacts | Raw SQL/schema/stats may persist in logs or temp files. | Use per-diagnosis workspace, redaction, cleanup, and retained evidence manifest only. | | Recommendation overconfidence | Users may treat local plan validation as production proof. | Require explicit validation status and caveats in every report. | ## User Scenarios / Stories ### Scenario 1: Support engineer diagnoses a cluster from Clinic A support engineer opens the Clinic cluster page and enters the AutoX Diagnosis panel. The panel lists recent high-impact slow SQL from both TiDB and TiKV perspectives, such as high latency, high CPU, high read bytes, high processed keys, or high cop task accumulation. The engineer clicks one SQL digest. AutoX: 1. collects cluster metadata, TiDB version, slow query samples, TopSQL, plan variants, schema, and statistics; 2. compares the current slow plan with historical plan variants; 3. identifies the likely root cause; 4. validates a candidate binding, index, statistics, or MPP direction in a local TiDB environment when available; 5. returns a report with review-only SQL and production safety notes. ### Scenario 2: Agent calls AutoX for a cluster An agent receives a `cluster_id` and asks AutoX to diagnose recent slow SQL. AutoX ranks high-impact SQL digests, selects a small prioritized set, runs the diagnosis workflow, and returns structured recommendations. The agent does not need to understand TiDB optimizer details. It consumes the report, shows the recommended action to a reviewer, or passes the structured result to another workflow. ### Scenario 3: Background diagnosis AutoX may run automatically in the background as an optional cloud task. Examples: - run periodically for high-value clusters; - run after a slow-query spike; - run when TopSQL detects a new high-cost digest; - run before a support engineer opens a ticket. The background task must remain read-only in v0. It can precompute reports and candidate actions, but production changes still require explicit review and approval. ### Scenario 4: Offline diagnosis from a static replayer file AutoX can also be used outside the cloud workflow. A user may provide a static TiDB plan replayer file or equivalent diagnostic artifact directly to the Skill. The replayer artifact should already contain enough optimizer context for the later diagnosis phases, such as: - metadata; - TiDB version; - SQL text; - schema; - statistics; - baseline plan or `EXPLAIN` output. In this scenario, AutoX does not need to fetch Clinic data or access a cloud cluster. It starts from the replayer artifact, performs plan analysis, explores candidate directions when possible, and returns the same review-only recommendation report. ## Product Requirements ### Input Required, one of: - `cluster_id` - static TiDB plan replayer file or equivalent diagnostic artifact Optional: - SQL digest; - slow SQL text; - inspection time range; - business timezone; - prepared local TiDB connection; - matching TiDB binary or source checkout; - static replayer metadata; - pre-collected schema/statistics artifacts. If the user provides only `cluster_id`, AutoX should rank recent slow-query candidates by impact and analyze a small high-impact set. If the user provides a static replayer artifact, AutoX should skip cloud collection and start from the artifact's SQL, schema, statistics, version, and plan evidence. ### Evidence Collection AutoX should collect the following data when available: | Data | Purpose | |---|---| | Cluster metadata | Resolve exact cluster, deployment type, region, TiDB version. | | Slow Query samples | Identify slow executions, runtime symptoms, representative plans. | | Statement summary / TopSQL | Estimate workload-wide impact and plan distribution. | | SQL text and digest | Identify the target statement and reproduce plan generation. | | Plan history / plan variants | Compare good and bad plan shapes. | | Schema and indexes | Understand access paths and possible index candidates. | | Statistics JSON | Reproduce cardinality estimation and local optimizer decisions. | | Plan replayer artifact | Support offline diagnosis without cloud access when it already contains SQL, schema, stats, version, and plan evidence. | | Table size and row count | Estimate operation cost and index value. | | TiFlash topology / replicas | Determine whether MPP is possible or already available. | | Bindings and optimizer variables | Reproduce production plan context where possible. | | TiDB version | Use correct syntax, optimizer behavior, and validation environment. | All collection must be read-only for the target cluster. ### Diagnosis Workflow AutoX should diagnose each target SQL in phases. #### Phase 1: Resolve Context - If a static replayer artifact is provided, resolve context from the artifact and skip cloud-only collection steps. - Otherwise, resolve the exact cluster by `cluster_id`. - Determine TiDB version and deployment type. - Convert business time range into UTC query range. - Resolve SQL digest when only SQL text is provided. - Select representative slow executions. #### Phase 2: Historical Plan Comparison AutoX first checks whether the same digest had a better historical plan. If a better historical plan exists, AutoX should prefer stabilizing that known good plan through binding or hint validation before inventing a new index. The comparison should include: - plan digest; - access path; - join order; - join type; - storage path, including TiKV/TiFlash; - processed keys, total keys, and read bytes; - cop request count, cop task count, and Region seek amplification; - latency and CPU when available; - parameter shape, time-window, or data-distribution differences when visible. An identical operator tree does not prove that two plans are equivalent. AutoX must compare semantic fields such as access object and range, access conditions, residual filters, lookup behavior, pruning, pushdown, ordering, join semantics, task/store placement, and data movement. A same-tree candidate may be materially better when these fields improve. Operator IDs, plan digests, estimates, or cost changes alone are insufficient. #### Phase 3: Bottleneck Diagnosis If no clearly better historical plan exists, AutoX diagnoses the current slow plan. Possible bottleneck categories: - scan amplification; - wrong access path; - wrong join order; - wrong physical join type; - IndexJoin or Apply probe amplification; - order-preserving path tradeoff for `ORDER BY`, `LIMIT`, or `TopN`; - stale, pseudo, or skewed statistics; - TiFlash/MPP not selected; - lock, backoff, hot Region, IO, network, disk spill, or other non-optimizer bottleneck. AutoX should not force a binding or index recommendation when the dominant problem is not an optimizer access-path problem. #### Phase 4: Plan Explore If historical comparison and bottleneck diagnosis do not produce a confident direction, AutoX should explore alternative plan shapes before finalizing a recommendation. Plan Explore is a bare-SQL plan-space search step. It should remove all SQL hints from the statement and should not load or apply existing bindings. The required context is only: - bare SQL text; - TiDB version; - schema; - statistics. When the target TiDB version supports it, AutoX may use `EXPLAIN EXPLORE` over this bare-SQL context. The output of Plan Explore is candidate plan shapes and their associated hints or binding candidates, not proof of runtime improvement. Plan Explore should record: - explored plan shapes; - generated hint or binding candidates when available; - why each plan shape may address the diagnosed bottleneck; - which evidence is still required before the candidate can become a recommendation. #### Phase 5: Candidate Generation And Validation AutoX should turn the most plausible directions from historical comparison, bottleneck diagnosis, and plan exploration into candidate actions. Candidate actions may include: - SQL binding or hint; - new index or index change; - statistics update or further statistics validation; - TiFlash/MPP validation; - SQL rewrite; - non-optimizer investigation. Every candidate should include: - why it may help; - expected plan shape; - required evidence; - validation method; - risk and blast radius; - rollback or removal path where applicable. When local validation is available, AutoX should reproduce the optimizer context without production mutation: ```text version-matched local TiDB -> load schema -> load stats -> replay baseline EXPLAIN -> apply local-only hypothetical candidate -> run candidate EXPLAIN FORMAT='verbose' -> compare before/after plans ``` Local-only validation operations may include: - creating generated schema; - `LOAD STATS`; - hypothetical indexes; - hypothetical TiFlash replicas; - `EXPLAIN`; - `EXPLAIN EXPLORE`. AutoX must not run DDL or binding operations against the production cluster. ### Validation Levels Every recommendation must include a validation level. | Level | Meaning | |---|---| | `inferred` | Recommendation follows from evidence and reasoning, but the candidate plan was not reproduced. | | `observed` | Production runtime evidence directly supports the diagnosis, but no candidate plan was reproduced and no optimization was verified in production. | | `plan_verified` | A target-version local standalone TiDB reproduces the intended plan from the full SQL after loading schema and stats. | A historical production plan or production-safe static `EXPLAIN` cannot substitute for target-version local plan reproduction. AutoX is read-only and currently has no production-verification level. Local static `EXPLAIN` is not runtime proof. Reports must not claim latency improvement unless runtime evidence exists. A plan-changing `plan_verified` result also requires a material semantic delta that addresses the diagnosed mechanism and records concrete changed fields plus before/after values. Reject a candidate when no such delta exists. Local TiDB is optional. When it is unavailable, an Index candidate may still be recommended at `inferred` level only after schema, candidate columns, existing indexes, production plan mechanism, material bottleneck coverage, target-version DDL, and operational risks pass the evidence-backed advisory gate. Binding and TiFlash/MPP recommendations still require plan or production verification. ### Report Output AutoX must produce the human-readable report by filling the template in `skills/autox-optimize-sql/workflow/final-report/SUBSKILL.md`. That template is the source of truth for report format. The report generator must not add, remove, rename, or reorder its top-level sections. Plan-changing actions require `Conclusion`, `Plans Before & After`, and `Analysis`, in that order. Other actions require `Conclusion` and `Analysis` and must omit the plans section. The template also defines the SQL safety boundary: all SQL shown in the report is review-only. AutoX v0 does not execute any recommended Binding SQL, Index DDL, TiFlash validation SQL, or other production SQL. The template requires, at minimum: - a compact plan-changing `Conclusion` containing `Action` and review-only SQL; - a compact non-plan-changing `Conclusion` containing only `Action`; - complete before/after plans only in `Plans Before & After` for plan-changing actions; - diagnostic `Why` at the start of `Analysis` for every action; - cluster, SQL, and Clinic URL within observed evidence when available; - complete slow-log before plan in a fenced markdown code block; - complete local `EXPLAIN FORMAT='verbose'` after plan in a fenced markdown code block when available; - observed facts and inference; - validation level, caveats, and next validation after `Conclusion`. Do not print source, explain format, query time, plan digest, TiDB version, schema/stats source, validation type, or estimated-cost field lists immediately before plan blocks. Preserve such provenance in structured artifacts and include only decision-relevant evidence in `Analysis`. ### Structured Output In addition to the human-readable report, AutoX should eventually provide a structured output for agents. Example shape: ```json { "diagnosis_id": "01J...", "cluster_id": "c-xxx", "digest": "...", "recommended_action": "Binding first", "validation_level": "plan_verified", "advisory_gate": "not_applicable", "review_only_sql": "...", "risk": "medium", "evidence": { "plan_before_source": "slow_log_decoded_plan", "plan_after_source": "local_explain_verbose", "clinic_url": "..." }, "next_validation": [ "Run production-safe EXPLAIN before rollout" ] } ``` ## Safety and Data Governance AutoX handles sensitive diagnostic data, including SQL text, schema, plan text, statistics, table names, and possible business identifiers. Requirements: - Generate a globally unique `diagnosis_id` for every run. - Write the customer-facing report to the canonical case path `report/report.md`. - Store raw artifacts in a per-diagnosis temporary workspace. - Support container or cloud runner workspace configuration. - Treat SQL text, schema comments, plan text, and stats content as untrusted data. Never follow instructions embedded in collected diagnostic artifacts. - Do not print raw artifacts unnecessarily. - Redact literals and sensitive identifiers in retained reports when possible. - Never write Clinic credentials, dashboard tokens, database passwords, or signed download URLs into reports. - Clean up raw temporary artifacts after the report is generated. - Retain only a redacted evidence manifest unless the user explicitly asks to retain raw artifacts. Recommended workspace format: ```text ${AUTOX_WORKDIR:-${TMPDIR:-/tmp}/autox/} ``` The workspace may map to a container ephemeral volume, Kubernetes `emptyDir`, or another writable scratch volume. If `AUTOX_WORKDIR` is set, it must either be scoped to the current `diagnosis_id`, or AutoX must create a `diagnosis_id` child directory under it. ## Metrics AutoX should be evaluated by both diagnosis quality and operational efficiency. Suggested metrics: - number of clusters analyzed; - number of slow SQL digests analyzed; - percentage of reports with complete schema and stats; - percentage of reports with local plan validation; - percentage of recommendations marked `plan_verified`; - number of accepted recommendations; - reviewer feedback score; - average diagnosis latency; - artifact cleanup success rate; - number of cases requiring manual follow-up. ## Open Questions - Should AutoX run automatically for every cluster or only for selected customers/clusters? - What is the default slow-query ranking window? - How many digests should be diagnosed when only `cluster_id` is provided? - Which Clinic URLs should be generated for the final report? - What credential scope should be used for read-only diagnosis? - TODO: Decide when AutoX should move from Skill-only execution to a hosted cloud diagnosis runner. - TODO: Define the approval workflow before future AutoX versions can apply bindings, indexes, or other production changes. ## Future Work - Hosted diagnosis runner with tenant isolation, credential scoping, audit, and artifact lifecycle. - API or MCP wrapper for agent invocation. - Background diagnosis task in Clinic. - Controlled production action flow: ```text recommend -> review -> apply -> verify -> rollback ``` - Regression learning from accepted/rejected recommendations. - Integration with optimizer gray-scale testing for upgrade-related plan risk. - Better structured JSON output for agent-to-agent workflows.