Notify When a MySQL Table Exceeds a Threshold (Rows or Size)

Published Feb 13, 2026

Safe SQL and alert patterns to notify when a MySQL table exceeds a threshold (rows, size, or growth rate).

Notify When a MySQL Table Exceeds a Threshold (Rows or Size)

Tables don’t usually “break” all at once—they drift into trouble. A queue table grows until it slows writes, an audit log eats disk, or a multi-tenant “events” table balloons and makes routine queries unpredictable. That’s why a pragmatic monitoring goal is simple: notify when MySQL table exceeds threshold conditions you define (rows, size, or abnormal growth). The best implementations are read-only, low-impact, and easy to tune as your data changes.

This guide walks through reliable ways to define thresholds, query them safely, and generate actionable alerts—without triggers, heavy scans, or risky privileges.

What “exceeds a threshold” should mean (choose your metric)

“Too big” can mean different things depending on your workload. Pick a metric that maps to an operational risk you care about.

Metric Best for How to measure Common pitfall
Row count Runaway queues, logs, staging tables information_schema.TABLES.TABLE_ROWS It’s an estimate for InnoDB (not exact)
On-disk size Disk pressure, backup time, storage cost DATA_LENGTH + INDEX_LENGTH Includes overhead; not equal to “useful data”
Growth rate Detecting sudden ingestion spikes Compare snapshots over time Needs history (store last value)
Oldest record age (TTL drift) Retention violations (e.g., keep 30 days) MIN(created_at) Can be expensive without an index

Recommendation: start with size (disk risk) and growth rate (surprises). Add row count thresholds when you have a clear operational limit.

Use low-impact MySQL queries (no table scans)

To keep monitoring safe in production, prefer metadata tables over counting rows directly. Avoid SELECT COUNT(*) on large tables unless you truly need exact counts and can afford it.

1) Size threshold (best default)

This checks table size (data + indexes) using information_schema:

SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'app_db'
  AND TABLE_NAME = 'events';

Turn it into a boolean “breach” result (easy for alerting systems):

SELECT
  CASE
    WHEN (DATA_LENGTH + INDEX_LENGTH) > (5 * 1024 * 1024 * 1024) THEN 1
    ELSE 0
  END AS exceeds_threshold
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'app_db'
  AND TABLE_NAME = 'events';

2) Row threshold (fast but approximate for InnoDB)

For InnoDB, TABLE_ROWS is typically an estimate—still good enough for “this table is exploding” alerts:

SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'app_db'
  AND TABLE_NAME = 'job_queue';

3) Retention threshold (detect missing cleanup)

Instead of measuring “size,” sometimes you care that cleanup jobs are working. If you retain 30 days, alert when the oldest row is older than 30 days. This is only safe if the timestamp is indexed.

SELECT
  TIMESTAMPDIFF(DAY, MIN(created_at), NOW()) AS oldest_age_days
FROM app_db.audit_log;

Index requirement: make sure you have INDEX(created_at) (or a composite index where created_at is the leading column), otherwise MIN() may scan.

Define thresholds that won’t page you every day

A threshold should reflect a concrete action, not a vague worry. Use one of these patterns:

  • Hard limit: “If size > 10 GB, we must archive or partition.”
  • Capacity-based: “Alert at 70% of disk budget allocated to this schema.”
  • Change-based: “Alert if growth > 20% in 24 hours.”

Operational rule of thumb: alerts should be tied to a playbook step (archive, purge, shard, add index, investigate ingestion source).

Implement a simple alert workflow (cron + script + email/webhook)

You don’t need a heavy monitoring stack to get reliable notifications. The basic loop is:

  1. Run a read-only query on a schedule (e.g., every 10 minutes).
  2. Compare the result to your threshold.
  3. If breached, send a notification (email, Slack webhook, incident tool).
  4. Optionally dedupe (only alert on state change or cooldown).

Create a read-only MySQL user (safer by default)

Use least privilege. For metadata checks, you typically need access to information_schema and (optionally) SELECT on target tables if you run retention queries.

CREATE USER 'monitor_ro'@'%' IDENTIFIED BY 'use-a-strong-password';
GRANT SELECT ON information_schema.* TO 'monitor_ro'@'%';
GRANT SELECT ON app_db.* TO 'monitor_ro'@'%';
FLUSH PRIVILEGES;

Example: Bash script that alerts when size exceeds a threshold

This script checks table size and posts to a webhook when it crosses a limit. It’s intentionally minimal—add retries, cooldowns, and logging for production.

#!/usr/bin/env bash
set -euo pipefail

DB_HOST="mysql.example.com"
DB_USER="monitor_ro"
DB_PASS="..."
SCHEMA="app_db"
TABLE="events"
THRESHOLD_MB=5120   # 5 GB
WEBHOOK_URL="https://hooks.slack.com/services/..."

SIZE_MB=$(mysql -N -s \
  -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" \
  -e "SELECT ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2)
      FROM information_schema.TABLES
      WHERE TABLE_SCHEMA='${SCHEMA}' AND TABLE_NAME='${TABLE}';")

# Handle empty result (table missing / permission issue)
if [[ -z "$SIZE_MB" ]]; then
  echo "No size result returned" >&2
  exit 2
fi

EXCEEDS=$(awk -v a="$SIZE_MB" -v b="$THRESHOLD_MB" 'BEGIN{print (a>b)?1:0}')

if [[ "$EXCEEDS" -eq 1 ]]; then
  curl -sS -X POST -H 'Content-type: application/json' \
    --data "{\"text\":\"MySQL alert: ${SCHEMA}.${TABLE} size is ${SIZE_MB} MB (threshold ${THRESHOLD_MB} MB)\"}" \
    "$WEBHOOK_URL" > /dev/null
fi

Schedule it with cron (every 15 minutes):

*/15 * * * * /usr/local/bin/mysql_table_size_alert.sh

Make it actionable: include context in the notification

A useful alert answers: “What happened?” and “What should I do next?” Consider adding:

  • Current value and threshold
  • Change since last check (growth rate)
  • Top contributors (if you can query by tenant, source, or type)
  • Runbook link (purge job, archiving steps, owner)

Optional: track growth rate with a lightweight snapshot table

If you can write to a small internal monitoring table, you can compute growth without external storage. Create a simple snapshot table:

CREATE TABLE IF NOT EXISTS ops.table_size_snapshots (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  schema_name VARCHAR(64) NOT NULL,
  table_name VARCHAR(64) NOT NULL,
  size_bytes BIGINT NOT NULL,
  captured_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_tbl_time (schema_name, table_name, captured_at)
);

Insert the current size periodically:

INSERT INTO ops.table_size_snapshots (schema_name, table_name, size_bytes)
SELECT TABLE_SCHEMA, TABLE_NAME, (DATA_LENGTH + INDEX_LENGTH)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='app_db' AND TABLE_NAME='events';

Then alert on a 24-hour delta:

SELECT
  (MAX(size_bytes) - MIN(size_bytes)) / 1024 / 1024 AS growth_mb_24h
FROM ops.table_size_snapshots
WHERE schema_name='app_db'
  AND table_name='events'
  AND captured_at >= NOW() - INTERVAL 24 HOUR;

Performance and correctness pitfalls (and how to avoid them)

Avoid COUNT(*) unless you know what it costs

For large InnoDB tables, exact row counts can be expensive. If you must measure row count precisely, consider:

  • Counting a narrow covering index (still work, but sometimes faster)
  • Maintaining a counter table updated by the app (best accuracy, added complexity)
  • Using estimates from information_schema for alerting

Remember that size isn’t only rows

A table can exceed size thresholds due to:

  • Large secondary indexes (especially on high-cardinality columns)
  • Long VARCHAR/TEXT payloads
  • Bloat from frequent updates (depending on engine/workload)

If you need deeper diagnosis, pull index sizes as well (MySQL 8+ supports better visibility, but even basic metrics help). A quick next step is to compare DATA_LENGTH vs INDEX_LENGTH and see which dominates.

Handle schema changes and renames

Alerts fail silently if a table is renamed. Treat an empty result as an error condition and notify the owner—missing monitoring is also a risk.

Practical threshold examples (copy/paste)

  • Alert when events table > 5 GB: size check via DATA_LENGTH+INDEX_LENGTH
  • Alert when queue table rows > 200k: use TABLE_ROWS estimate
  • Alert when audit log oldest record > 45 days: MIN(created_at) with an index
  • Alert when growth > 1 GB/day: snapshot and delta

Checklist: production-safe table threshold alerting

  • Use a read-only monitoring user
  • Prefer metadata queries over full table scans
  • Define thresholds that map to a clear action
  • Add deduplication (cooldown or alert-on-state-change)
  • Include context in the notification (value, threshold, growth, owner)
  • Test on a replica if possible and watch query latency

When you want this on your phone

If you prefer running these same read-only queries on a schedule and receiving push/email notifications without building your own runner, tools like Query Alert can turn query results into smart alerts while keeping connections encrypted and queries read-only.