Transform MySQL Slow Queries from Passive Fixes to Proactive Risk Scoring

Background

MySQL slow queries are statements whose execution time exceeds a configured threshold and are recorded in the slow‑query log. Traditional slow‑query platforms merely collect and display these queries, but they create a "slow‑query ocean" where DBAs cannot prioritize which queries to address, leading to long resolution cycles and significant business risk.

Analysis

The core issue is the sheer volume of slow queries and the lack of clear prioritization. DBA teams spend weeks analyzing, testing, and deploying fixes for individual queries, while developers often ignore slow‑query metrics, resulting in passive handling and potential service outages.

Solution Idea

A scoring mechanism is proposed to assign a risk score to each slow query. The total score ranges from 0 to 100, with higher scores indicating greater risk. Queries are then ranked and pushed to the responsible business owners for proactive remediation.

Model Design

Scoring Items Selection

The model selects five key indicators extracted from the slow‑query log:

QueryCount (maximum daily execution count)

Query_time (execution time)

Lock_time (lock wait time)

Bytes_sent (data sent to client)

Rows_examined (rows scanned)

Scoring Item Boundaries

For each indicator, the 95th percentile of historical data is used as the upper boundary after discarding the top 5% outliers. This normalizes scores and prevents any single extreme value from dominating the total.

Scoring Functions

Four scoring functions are supported, implemented in Go:

/**
 * @Description: Calculate a single item score within minScore‑maxScore range.
 * @Param val: current value
 * @Param minVal: minimum historical value
 * @Param maxVal: maximum historical value
 * @Param minScore: minimum score for the item
 * @Param maxScore: maximum score for the item
 * @Param calWay: scoring model (likeSin, sin, exponent, linear)
 * @Return float64: item score
 */
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":
        // Approximation of a sinusoidal curve using a 5‑degree polynomial
        b := 0.0547372760360247
        c := -0.0231045458864445
        d := 0.00455283203705563
        e := -0.000281663561505204
        f := 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
        scoreRatio = (val - minVal) / (maxVal - minVal)
    }
    return scoreRatio * (maxScore - minScore)
}

Overall Scoring Formula

The final risk index is calculated as:

RiskScore = sum(itemScore * weight)   // total capped at 100

Testing

Test 1

Initial weight distribution was applied and the resulting score distribution was visualized. Sample SQL analysis revealed that the rows‑examined metric had a disproportionate impact and should receive a higher weight.

Test 2

After re‑balancing weights and adjusting scoring functions, a new model was evaluated. The updated score distribution showed improved differentiation, and sample queries confirmed that high‑risk queries were correctly highlighted.

Conclusion

Adopting the second weight configuration focuses attention on queries with a risk score above 50, enabling DBAs to shift from reactive troubleshooting to proactive risk management.

Future Outlook

Future work includes incorporating richer metrics from Percona Server/MariaDB (e.g., temporary tables, InnoDB statistics) and introducing a business‑level weight to normalize risk across different applications. The final composite index is:

FinalRiskIndex = SlowQueryRiskScore * BusinessLevelWeight

By continuously pushing high‑risk queries to owners and iteratively refining weights, the median and high‑percentile risk scores can be driven down, achieving a healthier database performance posture.