How Machine Learning Transforms SQL Performance Anomaly Diagnosis

Performance Anomaly Diagnosis Background

When new services are launched or database workloads change, SQL execution can degrade, leading to high‑traffic, bad, or lock‑blocked SQL statements. These issues cause abnormal performance metrics for both the system and the database, potentially resulting in end‑to‑end errors.

The diagnosis process begins with rapid alert notification and data collection, followed by aggregation of SQL metrics, behavior analysis, and ranking of root‑cause SQL to pinpoint the problem.

After identifying the issue, immediate business stop‑loss measures and optimizations are applied, using targeted remediation plans and system refactoring to restore stability.

Root‑Cause SQL and Fix Strategies

Root‑cause SQL originates from business queries or database events such as ETL jobs, scheduled tasks, backups, or migrations. Mitigation includes terminating problematic SQL with kill commands or applying middleware rate‑limiting, as well as pausing or adjusting tasks to reduce load. Adding indexes or rewriting queries further optimizes performance.

Traditional Rule‑Based Diagnosis

Rule‑based methods rely on predefined diagnostic rule trees or graphs, ingesting data from performance monitors, process lists, slow‑query logs, full SQL logs, and system tables. Challenges include diverse threshold settings, complex branching logic, and maintenance difficulties across MySQL versions.

Statistical Model‑Based Diagnosis

Statistical approaches such as PinSQL and BALANCE use correlations between active sessions, SQL execution counts, and KPI metrics to identify problematic SQL. Industrial practices also employ direct SQL‑performance metric correlation, time‑series analysis, and scoring models. Limitations involve single‑data‑source focus, lack of extensibility, and absence of learning capabilities.

Learning‑Based Diagnosis Innovations

1. Adaptive Time‑Window Selection

Choosing an appropriate time window is critical; a 15‑minute window yielded a 1.26% root‑cause SQL execution proportion, while a 5‑minute window increased it to 3.84%, demonstrating the impact of window size on diagnostic accuracy.

Too long windows introduce noise; too short windows miss information. Fixed windows cannot adapt to varied fault scenarios, prompting the use of change‑point detection to identify significant performance shifts.

2. Multi‑Metric SQL Analysis Fusion

SQL templates are transformed into multi‑dimensional vectors, aggregating statistical features (median, 90th percentile, skewness, kurtosis, variance) and handling missing values with sliding‑window smoothing. Dynamic Time Warping (DTW) aligns SQL and performance metric time series, while categorical encoding captures select_type, execution plans, and optimizer hints.

3. Learning‑Based Root‑Cause Ranking

A dataset of over 600 real‑world diagnostic cases was built to train a LambdaRank model, which ranks SQL statements by their impact on system performance. Model outputs are combined with rule‑based adjustments for lock‑conflict scenarios, and SHAP explanations provide feature importance for transparency.

Experimental Results

Using a curated set of 400+ DBA‑labeled root‑cause SQL cases from Meituan, the proposed LESSON model outperformed baselines in recommendation count, recall, precision of top‑ranked root causes, and Mean Reciprocal Rank (MRR), demonstrating superior sorting accuracy and multi‑root‑cause handling.

Case Studies: Performance Anomaly Dataset Generation

Scenario 1 – Traffic Surge Triggering Multiple Alerts : The system diagnosed the issue within 10 seconds, selecting an optimal time window (08:29:40‑08:31:52) where performance metrics lagged behind the SQL spike, and provided a ranked list of affected SQL templates.

Scenario 2 – Sudden Slow‑Query Increase : The model identified a high‑scoring slow SQL (0.91) as the primary root cause, highlighting explanatory features such as scanned rows, returned bytes, and full‑table scans.

Other Scenarios : The platform handles complex cases like overlapping workload spikes, lock conflicts, and missing monitoring data by leveraging historical case libraries and continuous model retraining.