MySQL alerting is most effective when it’s driven by the same source of truth you use to debug issues: the database itself. Instead of relying only on host-level metrics, query-based alerting turns specific MySQL observations into clear, actionable notifications—“replication lag exceeded 60s” or “InnoDB log waits spiked”—so you can respond quickly without drowning in noise.
This guide walks through practical alert patterns, example SQL, threshold strategies, and noise-reduction techniques. It’s written for developers, DBAs, and data analysts who want alerts that map directly to symptoms and decisions.
What “good” MySQL alerting looks like
High-quality alerts share a few traits:
- Actionable: the alert implies an investigation path (what to check next).
- Specific: it identifies the scope (which instance, schema, or replica).
- Low-noise: it avoids flapping and only triggers when sustained.
- Safe: it uses read-only access and secure connections.
Alert on symptoms that require a human decision, not on every metric that moves.
Prerequisites: choose your data sources inside MySQL
Query-driven alerting typically pulls from:
- Performance Schema (recommended): statement digests, waits, I/O, memory.
- information_schema: table sizes, metadata.
- sys schema (if installed): friendlier views over Performance Schema.
- Replication views: depending on your MySQL version and replication mode.
- Application tables: business KPIs (orders, signups, payments).
Tip: If you can only run a handful of checks, prioritize the ones that detect (1) outages, (2) performance degradation, and (3) replication/data freshness issues.
A practical alert catalog (what to monitor)
The table below maps common operational risks to queryable signals and suggested alert styles.
| Risk | Signal to query | Alert style |
|---|---|---|
| Instance down / unreachable | Connection check | Immediate critical |
| Replication falling behind | Replica lag / apply delay | Warning→critical with sustain |
| Performance regression | Slow statement rate, lock waits | Threshold + trend |
| Resource saturation | InnoDB buffer pool pressure, temp tables | Sustained threshold |
| Storage creep | Schema/table growth | Daily/weekly budget |
| Data pipeline stale | Latest timestamp in key tables | Deadline-based |
Core MySQL alerting queries (copy/paste patterns)
Below are query patterns you can adapt. The exact columns and views can vary by MySQL version and configuration, so treat them as templates.
1) Replication lag and broken replicas
If you rely on replicas for reads, reporting, or failover, replication health should be top-tier alerting.
Lag check (classic pattern):
-- Returns a single row with replication lag in seconds when available.
-- Depending on your setup, you may use performance_schema replication tables or SHOW commands.
SHOW SLAVE STATUS;Many alerting systems can parse the returned fields; commonly used ones include Seconds_Behind_Master, Slave_IO_Running, and Slave_SQL_Running (naming may differ in newer terminology).
Alert rule ideas:
- Critical: IO/SQL thread not running (broken replication).
- Warning: lag > 30s for 3 consecutive checks.
- Critical: lag > 120s for 3 consecutive checks.
2) Deadlocks and lock contention
Locking issues often correlate strongly with user-visible latency.
-- Deadlocks since startup (quick indicator, not perfect alone)
SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';For richer detail, query lock waits via Performance Schema (exact objects depend on enabled consumers). A practical approach is to alert on a rising rate rather than an absolute count:
- Warning: deadlocks increased by > 5 in the last 10 minutes.
- Critical: deadlocks increased by > 20 in the last 10 minutes.
3) Slow query “pressure” using digest summaries
If Performance Schema statement summaries are enabled, you can identify when slow statements are increasing—often earlier than user complaints.
-- Top statements by total time (requires Performance Schema summaries enabled)
SELECT
DIGEST_TEXT,
COUNT_STAR AS exec_count,
ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_sec,
ROUND(AVG_TIMER_WAIT/1e9, 2) AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;Alert rule ideas (rate-based):
- Alert when avg_ms for a known critical digest increases by > 2x baseline.
- Alert when total time for top digests exceeds a budget (e.g., > 60s per minute of wall-clock).
4) Connection saturation and thread pressure
When the database is near its connection or thread limits, failures can cascade quickly.
-- How close are we to max connections?
SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';Alert rule ideas:
- Warning:
Threads_connected> 80% ofmax_connectionsfor 5 minutes. - Critical: connection errors rising (e.g.,
Aborted_connectsspike).
5) InnoDB log waits (a common hidden bottleneck)
Log waits can indicate write pressure, disk latency, or undersized log configuration. They often show up as rising commit latency.
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';Better than alerting on the raw value: alert on the delta (increase) over a time window.
6) Temporary table spill to disk
Disk-based temporary tables can signal missing indexes, large sorts, or memory pressure.
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';Alert rule idea: if the ratio Created_tmp_disk_tables / Created_tmp_tables exceeds (say) 0.25 for 10 minutes, investigate query plans and sort/group operations.
7) Storage growth by schema or table
Storage issues are often predictable. A daily check with a growth threshold is usually more useful than minute-by-minute paging.
-- Approx size by schema (InnoDB and others)
SELECT
table_schema,
ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_gb DESC;Alert rule ideas:
- Warning: schema grows by > 10% week-over-week.
- Critical: largest table grows by > X GB/day unexpectedly.
8) Data freshness for pipelines and key business tables
For analytics and operational reporting, “data is late” is often more important than “CPU is high.”
-- Example: freshness of events ingested into a fact table
SELECT
TIMESTAMPDIFF(MINUTE, MAX(created_at), NOW()) AS minutes_since_last_event
FROM analytics.events;Alert rule ideas:
- Warning: no new events for 15 minutes.
- Critical: no new events for 60 minutes (during expected traffic hours).
Noise control: make alerts stable and trustworthy
Most alert fatigue comes from flapping thresholds and context-free triggers. Use these patterns to keep MySQL alerting calm:
Sustained breach (“N out of M”)
Instead of firing on one bad sample, require persistence.
- Example: lag > 60s for 3 checks in a row.
- Example: disk temp table ratio > 0.25 for 10 minutes.
Hysteresis (separate trigger and recovery thresholds)
Prevent alerts from toggling rapidly:
- Trigger at 80% utilization, recover at 70%.
Rate-of-change alerts for counters
Many MySQL status variables are monotonically increasing counters. Alert on deltas, not absolute values, to avoid “always critical” behavior after long uptimes.
Time-of-day expectations
For business KPIs (orders/minute, ingestion freshness), set different expectations for peak vs off-peak windows to reduce false alarms.
Scheduling checks: picking intervals that match the failure mode
Use faster checks for fast failures and slower checks for slow-moving risks:
- 30–60 seconds: replication broken, connection saturation, thread pressure.
- 2–5 minutes: slow query pressure, lock contention rate, temp table spill ratio.
- Daily: storage growth, index bloat review, top table size changes.
Security essentials for query-based monitoring
Because alerting runs frequent queries, secure configuration matters:
- Use a read-only user with the minimum privileges needed (often
SELECTon specific schemas plus access to required metadata views). - Enforce TLS for database connections, especially over public networks.
- Never store credentials in plaintext; use your platform’s secret storage.
- Query allowlists help avoid accidental heavy queries in production.
Operational note: Prefer lightweight queries (aggregations over summary tables/views) and avoid scanning large application tables on tight intervals.
From alert to action: attach a mini runbook
The fastest teams treat every alert as a link to a decision tree. Even a short runbook block reduces mean time to resolution:
- Confirm scope: which instance/replica, which region, which schema.
- Check immediate symptoms: error logs, replication status, top waits.
- Identify top offenders: statement digests, lock waits, long transactions.
- Mitigate: throttle job, add index, kill runaway query, fail over if needed.
- Follow-up: capture digest, add a regression test, refine thresholds.
Putting it together: a simple starter alert set
If you’re starting from scratch, this bundle tends to catch the highest-impact MySQL issues with minimal noise:
- Replication broken (critical, immediate)
- Replication lag > 60s sustained (warning/critical)
- Threads_connected > 80% max sustained (warning)
- Deadlocks delta spike (warning)
- Innodb_log_waits delta spike (warning)
- Data freshness for 1–3 critical pipelines (warning/critical)
- Daily schema growth (warning)
Once those are stable, expand into statement-level regressions and per-service KPIs.
Final thought
MySQL alerting works best when alerts are query-defined, rate-aware, and paired with clear next steps. If you already express “health” as SQL, you can turn that into reliable notifications—on a schedule that matches how problems actually unfold.
Note: Some teams implement these patterns using mobile-friendly tooling that runs read-only queries and delivers push/email notifications—apps like Query Alert are designed around that query-to-alert workflow.