mysqld_exporter ↔ Prometheus Alertmanager: static versus dynamic thresholds for variable workloads

For a small operations team, the best trade-off is a hybrid approach: use static thresholds for critical failures (e.g., instance down) and recording-rule-based baselines for variable performance metrics. This avoids the complexity of ML tooling while reducing noise during predictable load spikes.

Implementing Seasonal Approximations

Prometheus can approximate dynamic behavior using recording rules to calculate moving averages. Instead of a fixed number, you compare the current value against its own historical average over a specific window (e.g., the last hour or 24 hours).

1. Create a Baseline Recording Rule

Define a rule to calculate the average value of a metric, such as mysql_global_status_threads_connected, to serve as a floating baseline:

# recording_rules.yml
- record: job:mysql_threads_connected:avg_1h
  expr: avg_over_time(mysql_global_status_threads_connected[1h])

2. Define the Alert with a Relative Threshold

Trigger the alert only when the current value deviates significantly (e.g., 2x) from that baseline, and use the for clause to filter out transient spikes:

# alert_rules.yml
- alert: MySQLHighConnectionSpike
  expr: mysql_global_status_threads_connected > (job:mysql_threads_connected:avg_1h * 2)
  for: 15m
  labels:
    severity: warning
  annotations:
    summary: "MySQL connections are significantly above the 1h average"

Managing Maintenance Windows

To prevent batch jobs or backups from triggering alerts without masking genuine crashes, use Alertmanager Inhibition Rules. This requires a "Maintenance" alert to be active to suppress other warnings.

• The Trigger: Create a manual alert or a scheduled Prometheus rule that fires during backup windows (e.g., alert: MySQLBackupInProgress).
• The Inhibition: Configure Alertmanager to suppress severity: warning alerts if MySQLBackupInProgress is firing for the same target.

# alertmanager.yml
inhibit_rules:
  - source_match: 
      alertname: 'MySQLBackupInProgress'
    target_match: 
      severity: 'warning'
    equal: ['instance', 'cluster']

Verification and Assumptions

This strategy assumes you are using a standard mysqld_exporter deployment and a Prometheus version that supports recording rules (v2.0+). To verify:

1. Test the 'for' clause: Simulate a 5-minute spike; the alert should not fire if for is set to 15m.
2. Test Inhibition: Trigger the backup alert and confirm that the connection spike alert is suppressed in the Alertmanager UI.

Missing Diagnostic: Do your backup windows occur at a fixed time daily, or are they triggered dynamically by an external orchestrator? (If dynamic, you must expose the backup status as a metric for the inhibition trigger to work).