LLM-Powered Database Root Cause Analysis: From Manual Triage to Automated AI Diagnosis

The Problem: Repetitive Manual Alert Investigation

Daily database operations at Qunar involved DBAs manually investigating alerts like "MySQL active threads spiked" by checking scan rows, slow queries, TOPSQL, DDLs, and deployments — a process taking minutes to tens of minutes per alert. The core issue: expert investigation experience could not be codified into automated capabilities, and low-probability causes (network jitter, client container resource pressure) were often missed, prolonging diagnosis.

Overall Architecture: Six-Layer Intelligent Diagnostic Engine

The agent comprises six decoupled layers:

User Interaction Layer : Receives natural language (e.g., "cluster xxxx 18:10-18:30 high concurrency") or structured alert payloads via Feishu; replies via streaming cards with real-time progress.

Intent Recognition Layer : Extracts diagnostic target (cluster, port), time window, and intent (RCA vs Q&A) using "LLM understands, code calculates" principle — a four-stage pipeline (preprocess → LLM extract → post-process compute → validation fallback) with Flash model, thinking disabled, zero retries, achieving <0.5s first-token latency.

Orchestration Scheduling Layer : LangGraph StateGraph with 10+ nodes covering initialization, intent recognition, parameter validation, intent-type routing (MySQL RCA, Redis RCA, Q&A, Redis memory growth), confirmation reply, HITL wait, feedback handling. Explicit conditional edges support halt short-circuit and interrupt pause/resume.

Algorithm Analysis Layer : Pulls data from Prometheus, ES, MySQL, DBA platform APIs; performs multi-dimensional correlation analysis producing structured evidence (anomalous metrics, correlations, causality, event timelines) for LLM reasoning. Core tenet: "computation separated from reasoning" — math by code (seconds, zero hallucination), LLM only does logical inference.

LLM Reasoning Layer : Two-stage design — Diagnostic LLM for root cause localization, Suggestion LLM for optimization recommendations; independent prompts, output formats, model configs.

Data Collection Layer : Unified ingestion from Prometheus (metrics), Elasticsearch (slow logs), MySQL (metadata), DBA platform APIs (releases, tickets, cluster info, hardware).

Architecture Choice: Why LangGraph Over LangChain or Dify

Evaluation across six dimensions:

Flow Orchestration : LangGraph explicit conditional edges (fully visible/routable) vs LangChain fixed linear chains vs Dify drag-and-drop canvas (complex branches unmaintainable).

Shared State : LangGraph centralized TypedDict AgentState (70+ fields across 7 categories) vs LangChain manual param passing vs Dify limited variable/session passing.

Human-in-the-Loop : LangGraph native interrupt() + Command(resume=) with auto checkpoint persistence vs LangChain custom middleware + external state vs Dify no native pause/resume.

Long Waits : LangGraph Redis-persisted checkpoints survive hours; LangChain thread-blocking infeasible; Dify workflow timeouts prevent it.

Timeout Cancellation : LangGraph checkpoint timestamp + Redis TTL auto-expiry vs LangChain custom timers vs Dify platform timeout without resume.

Observability : LangGraph per-node timing logs + live graph state vs LangChain opaque intermediate state vs Dify built-in tracing panel.

LangGraph's persistence, explicit edges, centralized state, and native interrupt simultaneously solved: fine-grained progress visibility, long-running human confirmation with arbitrary-time resume, and efficient multi-node state sharing.

Intent Recognition: LLM Understands, Code Calculates

Four-stage pipeline:

Preprocess : Blocks empty input; regex pre-parses Chinese relative dates (e.g., "week before last Tuesday") to concrete dates, denying LLM error-prone date math.

LLM Extract : Few-shot prompt with intent-priority layers (exclude pure IP/AppCode → Q&A then RCA intent; then time range). Dynamic injection of current system date prevents training-data date hallucination.

Post-process Compute : Python handles all deterministic ops — expands time window ±90min/±20min for anomaly detection context, rejects over-length windows, forces port-to-DB-type correction, auto-completes cluster names to instance addresses.

Validation Fallback : Empty intent defaults to Q&A mode ensuring every input gets a response.

Speed optimizations: Flash model (not Pro), thinking disabled (3s → 0.5s first token), temperature=0 zero-retry single-call returning all structured fields.

RCA Root Cause Diagnosis: Five-Layer Correlation from Pearson to Granger

Four diagnostic tiers: MySQL instance (active threads, concurrency, lock time, PXC flow control, QPS, DML rates), Redis instance (memory growth/distribution), Host (CPU, memory free, ping loss), AppCode (request errors, client container pressure). Fallback: full auto-detection mode traverses all metrics using expert-experience prioritization.

Layer 1: Time Alignment & Robust Anomaly Detection

Aligns multi-granularity metrics (30s vs 60s) to unified timestamps via pandas. Forward-fill for regular metrics; zero-fill for sparse (slow queries, TOPSQL). Rolling median + MAD (Median Absolute Deviation) for robust anomaly detection: baseline from prior N points; dual threshold — robust z-score > k AND relative change > threshold. Detects both rising and falling edges.

Layer 2: Pearson Correlation (r)

Measures linear co-movement: r ∈ [-1,1]; |r| near 1 = strong linear correlation. Outputs p-value for statistical significance (p<0.05). r answers "how strong", p answers "how trustworthy". Limitations: only synchronous correlation, no directionality; fails on sparse metrics (mostly zeros) and low-baseline burst metrics (brief 10-30x spikes) where full-series r dilutes to near zero.</p><h3>Layer 3: Data Pattern Recognition & Supplemental Correlation</h3><p>Auto-classifies non-Pearson-passing metrics by data distribution — no hardcoded metric-name rules:</p><ul><li><strong>Sparse</strong>: ≥50% zero values (e.g., slow query count: 2-3 non-zero in 240 points). Pearson fails because 98%+ zeros drown signal.</li><li><strong>Low-baseline Burst</strong>: Values >30x median, burst points ≤10% (e.g., TOPSQL scan rows: normally ~1000, spikes to 100k+ for 3-5 points). Pearson fails because 90%+ normal points swamp bursts. Regular : Neither above; Pearson result stands. For sparse/burst metrics, switches to Time-Window Correlation : dimensionality reduction from "full curve sync" to "event temporal proximity". For each anomaly in related metric, finds nearest alarm-metric anomaly within 60s. Produces two directional match rates:

Related Metric Match Rate : (related anomalies matching alarm) / (total related anomalies) — "how often does this cause trigger the alarm?"

Alarm Metric Match Rate : (alarm anomalies matching related) / (total alarm anomalies) — "how much of the alarm does this cause explain?"

Layer 4: Time-Window Optimization — Peak Matching

Problem: MAD on zero-baseline sparse metrics flags every non-zero as anomaly (e.g., 43 "anomalies" but only 3 business-meaningful peaks), diluting match rates. Solution: only top-N peak anomalies participate in time-window matching, ignoring micro-fluctuations.

Layer 5: Granger Causality Test

Answers directionality: "who moves first?" Two-step:

Predictive Power Comparison : VAR model — predict alarm metric from its own history (error1), then add related metric history (error2). If error2 significantly smaller (p<0.05 across lags 1..max_lag, e.g., 5), related Granger-causes alarm. Best lag = argmin p-value.

Effect Size & Direction : First-difference for stationarity, then VAR on differenced series. Sum of lag coefficients = total effect. Positive = same-direction; negative = inverse; magnitude = strength.

Critical limitation: Granger shows statistical predictive causality, not physical causality. Example: InnoDB rows read and active threads may Granger-cause each other, but true driver could be a slow query raising both. LLM uses DB kernel knowledge to validate causal chains.

Diagnostic vs Suggestion LLM Separation

Two independent LLM calls because data needs are asymmetric: Diagnosis needs lightweight signals (anomalies, correlations, timelines) for fast, focused causal reasoning. Suggestion needs heavy context (table stats, index distribution, hardware config, change history) for professional, actionable recommendations. Combining them dilutes attention and slows diagnosis. Separation halves diagnostic prompt length, speeds inference, and yields better suggestions.

Intelligent Q&A: Three-Speed Tiered Strategy

Prioritizes response speed for high-frequency ops queries (IP lookup, process FAQs, cluster owner). Three channels, cascading fallback:

Zero-Latency Channel (0 LLM calls) : Regex detects pure IP at intent-recognition entry; parallel backend API queries; sub-second response.

Fast Channel (1 LLM call upstream only) : Two deterministic matching layers for FAQ variants ("how to apply" vs "how to request"): substring containment on normalized input; Bigram Jaccard similarity (threshold 0.55 calibrated to distinguish "how to apply" from "how to view"). Hit returns cached answer; upstream intent recognition still uses 1 LLM call.

LLM Channel (2 LLM calls total) : Unmatched queries. Single LLM call with embedded FAQ knowledge base + synonym map does semantic FAQ match + tool-calling decision (IP lookup, cluster owner, etc.). Engineering guards: tool deduplication by name+param signature; 5-thread parallel execution; raw tool output displayed without LLM rephrasing (avoids hallucination). Unlike intent recognition, QA LLM uses thinking mode for semantic matching reliability.

Combating LLM Uncertainty: Global Speed & Accuracy Optimization

Speed: Fighting Attention Drift ("Lost in the Middle")

Unifying principle: LLM attention is scarce; every unnecessary token competes with critical signals. Cross-module patterns:

Task-matched models : Flash for extraction, thinking off for diagnosis.

Stage-gated context : Diagnostic LLM sees only signals; Suggestion LLM sees full context.

Dynamic template injection : InnoDB lock mechanics injected only for lock alarms (saves hundreds of tokens, focuses attention).

Metric name normalization : Rate metrics suffixed (e.g., _per_sec) so LLM instantly distinguishes instantaneous vs cumulative (e.g., Innodb_rows_read vs Innodb_rows_read_per_sec).

Link templating post-hoc : Code generates monitoring/ticket URLs from templates; LLM outputs only placeholders (saves thousands of tokens, 100% correct URLs).

Two-stage compression : Algorithm output (tens of thousands of chars) → LLM input (thousands): only anomaly time ranges (not every sample), only strong correlations, only Granger-causal metrics. 70-80% token reduction.

Accuracy: Seven-Layer Anti-Hallucination Defense

Input Validation : Pure-numeric input bypasses LLM (prevents hallucinating Redis port); system date dynamically injected into System Prompt.

Pre-check Facts Before Reasoning : Code deterministically checks for TRUNCATE/DROP DDLs, NIC down/OOM in system logs, PXC transaction limit errors in MySQL error logs. LLM receives "confirmed facts" and only does inference — shifts from calculation (LLM weak) to reasoning (LLM strong).

Deterministic Output : temperature=0 everywhere — same symptoms → same diagnosis.

Pydantic Output Schema : External validation "mold" for models lacking json_schema; enforces types, required/optional, Chinese aliases.

Hard Exclusion Rules (Rules Trump Statistics) : Prompt-embedded mandatory checks per candidate root cause. Examples: avg scan rows < threshold → exclude even if Pearson=1.0, Granger significant, perfect temporal overlap. DDL time >2min from anomaly window → exclude. SELECT queries in lock alarm → exclude (SELECT holds no row locks — kernel principle).

Counterexample Teaching : Concrete numeric counterexamples block LLM's "reason-to-keep" bias: "Even if sum=85836, Pearson=1.0, lag=0s, avg=354 (<6000) → MUST exclude. Sum passing doesn't rescue it."

Post-Processing Verification & Completion : Code backfills missing optional fields (sql_id, db_name, AppCode) from raw metric names; replaces link placeholders with real URLs; intercepts greeting fluff masquerading as answers.

Two governing principles: (1) Deterministic work → code; probabilistic work → LLM. (2) No data → no fabrication (must say "cause not located" rather than invent UPDATE/DELETE).

Human-in-the-Loop: Teaching AI to Wait

DB ops require multi-turn collaboration. Example: Redis memory growth analysis — AI cannot pick comparison timepoints; must pause, present 10 backup timestamps, wait for user to choose start, then end, then proceed.

Checkpoint: Persisting Execution Progress

LangGraph interrupt() pauses graph, serializes full state (all variables, current node) to Redis checkpoint (~200KB). User reply triggers callback → restore from snapshot → interrupt() returns user input → continue seamlessly. Benefits: unlimited wait (minutes to 2 hours, no memory cost); auto-cleanup via 180-min TTL (expired sessions prompt re-init); natural retry from latest checkpoint on node failure.

Dual Storage: Progress vs Session Index

Checkpointer (RedisSaver) : Heavy — full execution snapshots per node, for LangGraph internal restore.

HitlStore : Light — thread_id ↔ user mapping (~0.5KB), for Feishu callback fast lookup (avoids deserializing 200KB on every callback).

Multi-Pause in Single Node

Redis memory analysis needs two pauses in same node (start time → end time). Challenge: second interrupt() resume value could be consumed by first. Solution: each user choice written to Redis cache with re-entry guard; resume_rca_graph() verifies checkpoint paused state + HitlStore record before restoring, preventing spurious resumes.

Security & Concurrency: Production-Grade Guardrails

Intent-Driven Tiered Authorization

Permission check deferred until after intent recognition: Q&A → zero barrier; RCA & Redis memory → DBA/admin only; AppCode check → DBA + developers. Sensitive ops blocked only after intent clarified — secure without degrading Q&A UX.

Three-Layer Concurrency Control

Global Semaphore : Limits total parallel diagnoses (multi-source Prometheus/ES/LLM load). Queue with Feishu card showing "N tasks ahead"; 90s max wait then graceful exit.

Per-User Lock (held during HITL) : One active diagnosis per user; lock retained across HITL pauses to prevent state collision (e.g., user picking Redis timepoints while launching MySQL RCA). 1h TTL crash safety; auto deadlock detection (>30min lock age → force clear); restart clears residual locks.

Per-Task Resource Protection : e.g., Redis RDB download limited to 2 concurrent per host to avoid bandwidth saturation. Core rule: AI must not harm managed systems.

Production Impact: DBA Daily Work Transformed

Speed : From "running between systems" to "wait for conclusion". Natural language query → Agent auto-analyzes dozens of curves, correlates slow queries, matches changes/tickets. Average 1-2 minutes to diagnosis. DBA shifts from data gathering to evidence review & confirmation.

Coverage : From "experience-dependent" to "nothing missed". Every alert gets full pre-check suite automatically — logs, records, no omissions regardless of time pressure or individual habit.

Knowledge Reuse : From "person-dependent" to "standardized pipeline". Expert reasoning codified into unified flow; quality and efficiency no longer vary by DBA.

Accuracy : RCA diagnostic accuracy 70%. Even on misses, produced anomaly correlation graphs and event timelines drastically narrow manual re-investigation scope.

Summary: Three Core Principles

Code Computes, LLM Reasons : Pearson, Granger, MAD anomaly detection — all math by code; structured evidence feeds LLM. LLM never touches raw numbers, only applies MySQL ops knowledge to infer causal logic. Recurs in intent recognition ("LLM understands, code calculates"), Q&A (zero/fast channels), accuracy guards (pre-checks, link templating).

Feed LLM Only High-Signal Context : Not a slogan — engineering practice. Correlation <0.6 filtered; DDL >2min from window trimmed; slow query details withheld from diagnosis phase. Higher signal-to-noise in prompt → sharper reasoning.

Human-in-the-Loop Closure: Human Holds Final Authority : Redis memory analysis — AI lists backup timestamps, pauses, waits for user choice. AI proposes, human decides. Every production-critical step requires human confirmation — not distrust of AI, but reverence for production systems.

Underlying mindset: Be an AI Business Architect, not just an AI User . Decide what AI should do, what humans must do; design collaboration flows; evaluate and correct AI output continuously. The path from "human finds cause" to "AI finds cause" hinges not on LLM raw power, but on placing LLM correctly: compute where it computes, reason where it reasons, human decides where it must.