Why Traditional DB Inspections Fail and AI-Powered Anomaly Detection Helps

Why Traditional DB Inspections Fail

Traditional inspection systems rely on fixed thresholds that are set high to avoid excessive alerts. While they can catch obvious anomalies, they miss dynamic changes such as gradual increases in QPS, load, or data volume during peak periods, leading to delayed response and potential business impact.

Anomaly Detection: The “Second Pair of Eyes”

Recent advances in machine learning and AI provide new approaches for time‑series monitoring. By learning patterns from historical data, AI can identify behaviors that deviate from the norm, enabling DBAs to detect abnormal metric changes early.

Overall Process

The detection pipeline consists of data transformation, feature extraction, algorithm selection, tuning, and alarm convergence.

Key Steps

Feature Analysis : Identify periodic, stable, or sudden changes in metrics (e.g., Redis memory usage shows daily cycles, MySQL disk usage grows steadily).

Algorithm Selection : Apply sliding‑window transformations, IQR detection, seasonal decomposition, or level‑shift detection based on feature types.

Tuning : Combine dynamic thresholds learned by ML with static thresholds to reduce false positives and handle known interference such as migrations or DDL operations.

Alarm Convergence : Merge repeated alerts within a time window and across instances to avoid alert storms.

Three Typical Application Scenarios

1. Stable Trend Anomaly Detection

Most metrics (MySQL disk usage, Redis memory) exhibit a smooth trend under normal conditions. The DoubleRollingAggregate algorithm creates two sliding windows, computes the difference, and generates a new series where positive values indicate upward trends and negative values indicate declines.

Alarm messages and auxiliary reports help DBAs pinpoint the cause of disk usage spikes, such as large binlog generation during archival tasks.

2. Periodic Change Anomaly Detection

Metrics like Redis memory usage follow a daily cycle. The SeasonalAD algorithm first removes seasonal patterns using ClassicSeasonalDecomposition, then applies a two‑branch detection: one branch flags points where the deseasonalized residual is positive, the other applies IQR on the absolute residual. The intersection yields true periodic anomalies.

Additional rules (e.g., low memory usage, small amplitude, limited anomaly count) further filter out benign fluctuations.

3. Sudden Change Anomaly Detection

CPU Level‑Shift Detection (MySQL)

When CPU usage jumps sharply (e.g., from 20% to 30% within minutes), LevelShiftAD detects the edge using a double‑rolling window to compute absolute differences, followed by IQR and sign checks to isolate upward spikes.

QPS Drop Detection

Sudden drops to near‑zero QPS often indicate large transaction commits or binlog bottlenecks. IQR detection quickly isolates these outliers.

Algorithmic details include computing Q1, Q3, IQR, and applying configurable multipliers (c1, c2) to define the normal range.

# Sort monitoring data
data = [5,7,10,15,19,21,21,22,22,23,23,23,23,23,24,24,24,24,25]
len(data)  # 19
# Scatter plot
import matplotlib.pyplot as plt
plt.scatter(range(len(data)), data)
# Histogram
plt.hist(data)

Practical Experience and Tuning

Deployed detection for CPU, disk, memory, table size, QPS, and scan rows across production, achieving >80% alarm accuracy and reducing noise. Different metrics require tailored algorithm families; dynamic thresholds learned by ML complement static hard limits. Multi‑dimensional alarm convergence (temporal and cross‑instance) prevents alert storms.

Future Outlook

Roadmap moves from single‑metric detection to multi‑metric correlation and automated root‑cause analysis. Ongoing work focuses on expanding joint anomaly detection, refining ML models, and further reducing manual inspection workload for DBAs.