Database Health Monitor
Overview
Monitor database server health across PostgreSQL, MySQL, and MongoDB by tracking key performance indicators including connection utilization, query throughput, replication lag, disk usage, cache hit ratios, vacuum activity, and lock contention.
Prerequisites
- Database credentials with access to system statistics views (
pgstat*, performance_schema, serverStatus)
psql, mysql, or mongosh CLI tools for running health check queries- Permissions:
pg_monitor role (PostgreSQL), PROCESS privilege (MySQL)
- Baseline metrics from a period of normal operation for threshold calibration
- Alerting channel configured (email, Slack webhook, PagerDuty)
Instructions
- Check connection utilization:
- PostgreSQL:
SELECT count() AS activeconnections, (SELECT setting::int FROM pgsettings WHERE name = 'maxconnections') AS maxconnections, round(count()::numeric / (SELECT setting::int FROM pgsettings WHERE name = 'maxconnections') * 100, 1) AS utilizationpct FROM pgstat_activity
- MySQL:
SELECT VARIABLEVALUE AS connections FROM performanceschema.globalstatus WHERE VARIABLENAME = 'Threads_connected'
- Alert threshold: utilization above 80%
- Monitor query throughput and error rate:
- PostgreSQL:
SELECT datname, xactcommit AS commitstotal, xactrollback AS rollbackstotal, xactrollback::float / GREATEST(xactcommit, 1) AS rollbackratio FROM pgstatdatabase WHERE datname = currentdatabase()
- MySQL:
SHOW GLOBAL STATUS LIKE 'Comcommit' and SHOW GLOBAL STATUS LIKE 'Comrollback'
- Alert threshold: rollback ratio above 5% or throughput drops more than 50% from baseline
- Check disk usage and growth:
- PostgreSQL:
SELECT pgsizepretty(pgdatabasesize(currentdatabase())) AS dbsize and SELECT tablename, pgsizepretty(pgtotalrelationsize(tablename::text)) AS size FROM pgtables WHERE schemaname = 'public' ORDER BY pgtotalrelation_size(tablename::text) DESC LIMIT 10
- Alert threshold: disk usage above 80% or growth rate projecting full disk within 7 days
- Monitor cache hit ratio:
- PostgreSQL:
SELECT sum(heapblkshit)::float / GREATEST(sum(heapblkshit) + sum(heapblksread), 1) AS cachehitratio FROM pgstatiouser_tables
- MySQL:
SELECT (1 - (VARIABLEVALUE / (SELECT VARIABLEVALUE FROM performanceschema.globalstatus WHERE VARIABLENAME = 'I
