Database Monitoring Tools: Observability Guide

In this tutorial, you'll learn about Database Monitoring Tools: Observability Guide. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

Database monitoring is the practice of collecting, visualizing, and alerting on database performance metrics -- query latency, connection counts, cache hit ratios, Replication lag, and disk I/O -- to maintain healthy production systems.

What You'll Learn

You will understand how to set up monitoring for PostgreSQL, MySQL, and MongoDB, configure Prometheus exporters, build Grafana dashboards, interpret slow query logs, set up meaningful alerts, and diagnose common performance issues.

Why Monitoring Matters

Without monitoring, you discover database problems when users report errors. Durga Antivirus Pro processes 50,000 threat signature lookups per second; a 2-second Replication lag could cause outdated definitions. Monitoring caught this at 500ms.

Monitoring Learning Path

flowchart LR
  A[Query Optimization] --> B[Indexing]
  B --> C[Database Monitoring]
  C --> D[Backup and Recovery]
  C:::current
  classDef current fill:#f90,color:#fff,stroke:#333,stroke-width:2px

Key Metrics to Monitor

Every database monitoring system should track these categories:

PostgreSQL Monitoring

pg_stat_statements

The most important PostgreSQL monitoring extension. It tracks query execution statistics.

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the top 5 queries by total execution time
SELECT
    queryid,
    LEFT(query, 80) AS query_preview,
    calls,
    total_exec_time / 1000 AS total_seconds,
    mean_exec_time AS avg_ms,
    rows,
    shared_blks_hit + shared_blks_read AS total_blocks
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Expected output:

 queryid |            query_preview            | calls | total_seconds | avg_ms
---------+------------------------------------+-------+---------------+--------
 1234567 | SELECT * FROM orders WHERE status. | 54321 |        1234.5 |  22.73
 2345678 | INSERT INTO logs (message, time).. | 99999 |         987.6 |   9.88

Slow Query Logging

# postgresql.conf
log_min_duration_statement = 1000  # Log queries slower than 1 second
log_line_prefix = '%t [%p]: [%l] user=%u,db=%d '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0  # Log temp file creation (disk sort)

PostgreSQL Exporter for Prometheus

# docker-compose.yml for postgres_exporter
services:
  postgres_exporter:
    image: prometheuscommunity/postgres-exporter:latest
    environment:
      DATA_SOURCE_NAME: "postgresql://monitor:password@localhost:5432/postgres?sslmode=disable"
    ports:
      - "9187:9187"
    restart: always

MySQL Monitoring

Performance Schema

MySQL's Performance Schema provides detailed query metrics.

-- Enable Performance Schema (my.cnf)
-- [mysqld]
-- performance_schema = ON

-- Top queries by total latency
SELECT
    DIGEST AS query_digest,
    DIGEST_TEXT AS query_text,
    COUNT_STAR AS execution_count,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_seconds,
    ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 5;

MySQL Slow Query Log

# my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1.0
log_queries_not_using_indexes = 1

MySQL Exporter

# prometheus.yml scrape config
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']

MongoDB Monitoring

Current Operations

// Find slow-running operations
db.currentOp({
  "active": true,
  "secs_running": { "$gt": 5 },
  "ns": { "$not": /admin/ }
})

Output:

{
  "inprog": [{
    "opid": 12345,
    "secs_running": 12,
    "op": "query",
    "ns": "mydb.orders",
    "query": { "status": "shipped" },
    "planSummary": "COLLSCAN]
  }]
}

MongoDB Profiler

// Enable profiling for slow queries (in MongoDB shell)
use mydb;
db.setProfilingLevel(1, { slowms: 100 })  // Log queries over 100ms

// View recent slow queries
db.system.profile.find({
  millis: { $gt: 100 }
}).sort({ ts: -1 }).limit(5).pretty();

Grafana Dashboards

Sample Dashboard Panel: Cache Hit Ratio

-- PostgreSQL cache hit ratio
SELECT
    SUM(blks_hit) / NULLIF(SUM(blks_hit + blks_read), 0) * 100 AS hit_ratio
FROM pg_stat_database;

Alert rule: If cache hit ratio drops below 95%, the buffer pool is too small or queries are scanning too many rows.

Alerting Rules with Prometheus

# prometheus-alerts.yml
groups:
  - name: database
    rules:
      - alert: PostgresHighConnections
        expr: pg_stat_database_numbackends > 80
        for: 5m
        annotations:
          summary: "PostgreSQL connections over 80% of max"

      - alert: MySQLReplicationLag
        expr: mysql_slave_status_seconds_behind_master > 60
        for: 2m
        annotations:
          summary: "MySQL replication lag over 60 seconds"

      - alert: MongoDBSlowQuery
        expr: rate(mongodb_queries_total[1m]) > 100
        for: 5m
        annotations:
          summary: "MongoDB query rate over 100/sec on single node"

Database Health Checks

Automated Health Check Script

#!/bin/bash
# Database health check
DB_HOST=$1
DB_PORT=$2
DB_NAME=$3

echo "=== Connection Check ==="
pg_isready -h $DB_HOST -p $DB_PORT -d $DB_NAME

echo "=== Active Connections ==="
psql -h $DB_HOST -p $DB_PORT -d $DB_NAME -c "
SELECT count(*) as active_connections
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid();
"

echo "=== Long Running Queries ==="
psql -h $DB_HOST -p $DB_PORT -d $DB_NAME -c "
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC
LIMIT 5;
"

echo "=== Replication Lag ==="
psql -h $DB_HOST -p $DB_PORT -d $DB_NAME -c "
SELECT client_addr, state, sent_lsn - write_lsn AS lag_bytes
FROM pg_stat_replication;
"

Common Monitoring Errors

1. Alert Fatigue from Over-Configuring

Too many alerts cause teams to ignore all of them. Start with 5 critical alerts and add more gradually.

2. Not Setting Baseline Metrics

Without historical data, you cannot distinguish a real problem from normal variation. Collect metrics for at least 2 weeks before setting thresholds.

3. Monitoring Only Averages

Average query latency hides P95 and P99 spikes. Always monitor percentiles alongside averages to catch tail latency issues.

4. Ignoring Connection Pool Metrics

Application-side pool metrics (hikaricp_connections_active, pending) reveal problems before database-side metrics show trouble.

5. No Alert on Exporter Failure

If the Prometheus exporter stops working, you are blind. Set up a Dead Man's Switch or synthetic check that fires if metrics stop arriving.

6. Not Monitoring Replication Lag on Read Replicas

Applications reading from replicas may get stale data for minutes before anyone notices. Monitor lag on every replica.

7. Using Default Grafana Dashboards

Default dashboards contain too many panels. Build focused dashboards per team: SRE gets infrastructure metrics, developers get query performance.

Practice Questions

1. What is pg_stat_statements and why is it important?

It tracks execution statistics for every query executed on a PostgreSQL server -- call count, total time, mean time, rows returned, I/O statistics.

2. How do you set up slow query logging in MySQL?

Set slow_query_log = 1, slow_query_log_file = /var/log/mysql/slow.log, and long_query_time = 1.0 in my.cnf, then restart MySQL.

3. What does a low cache hit ratio indicate?

The buffer pool is too small or queries are scanning too many rows. A ratio below 95% means the database is reading from disk instead of memory.

4. How do you monitor MongoDB query performance?

Enable the database profiler with db.setProfilingLevel(1, { slowms: 100 }), check db.currentOp() for active queries, and use MongoDB_exporter for Prometheus.

5. Challenge: Build a monitoring stack.

Set up PostgreSQL with pg_stat_statements, configure postgres_exporter for Prometheus, create a Grafana dashboard showing connections, cache hit ratio, and top 5 slow queries. Configure an alert for connections over 80% of max. Answer: Use docker-compose with postgres-exporter, Prometheus, and Grafana. Create a panel for each metric and configure alerts in Prometheus or Grafana.

FAQ

Try It Yourself

Set up PostgreSQL monitoring in 30 minutes:

1. Enable pg_stat_statements in PostgreSQL.conf
2. Configure slow query logging with log_min_duration_statement = 1000
3. Run docker-compose up with postgres-exporter, Prometheus, and Grafana
4. Import a PostgreSQL dashboard from Grafana.com
5. Run a slow query and verify it appears in the dashboard
6. Configure one alert (e.g., connections > 80%)

What's Next

You have learned how to monitor PostgreSQL, MySQL, and MongoDB effectively. Start by enabling pg_stat_statements and slow query logging on your production database today.