How to Turn MySQL Slow Queries from Passive Pain to Proactive Risk Scores

Background

MySQL slow queries—statements whose execution time exceeds a threshold—are routinely recorded in the slow‑query log and are a frequent topic among DBAs. Traditional slow‑query platforms merely collect and display these queries, but when the volume grows, it becomes impossible for developers or DBAs to identify which queries to address first, leading to long resolution cycles and potential service outages.

Analysis

The core problem is the sheer number of slow queries and the lack of clear prioritisation. Without a way to assess the business impact of each query, both DBAs and developers treat slow queries passively, fixing them only after incidents occur.

Solution Idea

Introduce a scoring mechanism that evaluates each slow query based on key attributes, assigns a risk score (out of 100), and pushes high‑risk queries to the responsible business owners for early remediation.

Model Design

The scoring model aggregates five metrics:

QueryCount (maximum executions in a 10‑minute sliding window)

Query_time

Lock_time

Bytes_sent

Rows_examined

Each metric is normalised to a 0‑100 range, weighted, and summed to produce the final risk index.

Metric Details

QueryCount : The highest number of executions of a query within a 10‑minute window, reflecting frequency‑driven impact.

Query_time : Total execution time; longer times indicate higher risk.

Lock_time : Time spent waiting for locks; longer waits increase the chance of contention.

Bytes_sent : Amount of data transmitted to the client; larger volumes consume more network resources.

Rows_examined : Number of rows scanned; higher values consume more I/O and CPU.

Boundary Selection

For each metric, the 95th percentile of historical data is used as the upper bound; values above this are capped at the maximum score. The lower bound is the minimum observed value.

Scoring Functions

/**
 * Calculate a single metric score between minScore and maxScore.
 * Supported models: likeSin, sin, exponent, linear.
 */
func calSingleScore(val, minVal, maxVal, minScore, maxScore float64, calWay string) float64 {
    if maxVal == 0 { return 0 }
    if val >= maxVal { return maxScore }
    if val <= minVal { return minScore }
    var scoreRatio float64
    switch calWay {
    case "likeSin":
        b, c, d, e, f := 0.0547372760360247, -0.0231045458864445, 0.00455283203705563, -0.000281663561505204, 5.57101673606083e-06
        ratio := (val - minVal) / (maxVal - minVal) * 20
        scoreRatio = b*ratio + c*ratio*ratio + d*ratio*ratio*ratio + e*ratio*ratio*ratio*ratio + f*ratio*ratio*ratio*ratio*ratio
    case "sin":
        ratio := (val - minVal) / (maxVal - minVal)
        scoreRatio = math.Sin(math.Pi/2 * ratio)
    case "exponent":
        ratio := (val - minVal) / (maxVal - minVal)
        a := math.Log2(maxScore - minScore)
        return math.Pow(2, a*ratio)
    default: // linear
        ratio := (val - minVal) / (maxVal - minVal)
        scoreRatio = ratio
    }
    return scoreRatio * (maxScore - minScore)
}

Overall Scoring Formula

slow_query_risk_index = sum(metric_score * weight)
ps: total risk index is capped at 100

Testing

Test 1 – Initial Weights

Using the first set of weights, the distribution of scores showed that many queries fell below the 50‑point threshold, indicating moderate risk.

Test 2 – Adjusted Weights

After re‑balancing weights (giving more importance to ScanRows), the model highlighted a larger number of high‑risk queries, prompting a revision of the weighting scheme.

Conclusion

The scoring model converts each slow query into a quantitative risk index, enabling DBAs to prioritize remediation, push high‑risk queries to owners, and track risk reduction over time. By integrating business‑level weightings, the model can adapt to different service criticalities.

Future Work

Incorporate additional metrics from Percona Server/MariaDB, refine boundary calculations, and automate the push of top‑N risky queries to responsible teams.