MariaDB Performance Schema ↔ Prometheus Exporter: Determining Optimal Sampling Interval for Alert Accuracy

Answer to the core question

The most pragmatic approach is to set the Performance Schema sampling interval to 5 seconds, let the MariaDB Prometheus exporter scrape every 15 seconds, and use recording rules that compute max_over_time over a 1‑minute window and avg_over_time over a 5‑minute window. This combo captures transient slowdowns while keeping database overhead low and alert noise manageable.

Why this works

• Performance Schema: the default 10‑second interval can miss brief spikes; reducing it to 5 s halves that risk without a dramatic CPU or I/O hit on most workloads.
• Exporter: it simply translates tables to metrics; it doesn’t down‑sample, so the scrape interval should be longer than the sampling interval to avoid redundant work.
• Recording rules: max_over_time(metric[1m]) keeps the worst value in the last minute, surfacing short high‑latency periods; avg_over_time(metric[5m]) smooths the metric for stability and reduces false positives.
• Performance impact: a 5‑second sampling interval adds modest overhead; monitoring SHOW STATUS LIKE 'performance_schema%'; will confirm CPU and disk usage stay within acceptable limits.

Implementation steps

1. Adjust sampling:

   # In my.cnf
   performance_schema_setup_consumers = 5
   # Or via SQL
   UPDATE performance_schema.setup_consumers
   SET ENABLE = 'YES', INTERVAL = 5
   WHERE NAME = 'performance_schema';
   # Restart MariaDB to apply.
   

2. Configure exporter scrape:

   scrape_interval: 15s
   

3. Create Prometheus recording rules:

   groups:
   - name: ps_latency
     rules:
     - record: ps_http_max_1m
       expr: max_over_time(http_response_time_seconds[1m])
     - record: ps_http_avg_5m
       expr: avg_over_time(http_response_time_seconds[5m])
   

4. Update alert rules to use the derived metrics, e.g., alert when ps_http_max_1m > 0.5.
5. Verify:

   # Check current sampling interval
   SELECT INTERVAL FROM performance_schema.setup_consumers WHERE NAME='performance_schema';
   # Monitor exporter scrape duration
   curl -s http://localhost:9104/metrics | grep "scrape_duration_seconds"
   # Observe alert firing pattern in Prometheus UI
   

When to adjust further

If you still see false positives or miss critical spikes, consider:

• Reducing sampling to 1 second (requires a restart and careful monitoring of CPU/disk).
• Shortening the max_over_time window to 30 seconds if ultra‑low latency is essential.
• Increasing the scrape interval to 30 seconds to reduce exporter load, provided the 5‑second sampling still captures the spikes you care about.

Missing diagnostic detail

Do you need to detect latency spikes that last less than one second? If so, a 1‑second sampling interval may be required, which will increase database overhead.

Key takeaways

• 5‑second sampling + 15‑second scrape + 1‑min max + 5‑min avg balances visibility and overhead.
• Exporter does not change granularity; adjust only Performance Schema and Prometheus rules.
• Monitor CPU/disk and scrape duration to ensure the chosen interval stays within acceptable limits.