Building a Database Monitoring System: From Exporters to Alert Rules in Practice

Overview

When the database slows down, the business slows down. Every ops engineer has heard this, but honestly, not many teams do database monitoring right.

I’ve seen too many teams whose database monitoring looks like this: Zabbix templates running decade-old metrics, only two alert rules — “CPU over 90%” and “disk full” — while connection count, lock waits, cache hit rate, replication lag, and other metrics that actually provide early warning are not being collected at all. When the business team comes saying “the API is slow,” you discover the connection pool has been maxed out for ages and hundreds of slow queries have piled up.

The core challenge of database monitoring isn’t “installing an exporter.” It’s knowing which metrics to watch, how to set thresholds, and how to read database health from metric trends. This article covers all of that.

We’ll cover both MySQL and PostgreSQL, from exporter deployment, core metrics interpretation, alert rule design, to Grafana dashboard configuration — the full chain. The tech stack is based on Prometheus + Grafana, the de facto standard for open-source database monitoring today.

Three-Layer Architecture of Database Monitoring

Before diving in, let’s clarify the monitoring layers. Database monitoring isn’t about dumping every metric you can find — it needs to be layered:

┌──────────────────────────────────────────────────┐
│            Business Application Layer             │
│  Core API response time | Business success rate  │
│  Business throughput                              │
│  → These metrics directly relate to user experience│
├──────────────────────────────────────────────────┤
│            Database Kernel Layer                  │
│  Connections | QPS/TPS | Slow queries | Lock     │
│  waits | Cache hit rate | Replication lag        │
│  Deadlocks | WAL writes | Transaction rollback   │
│  → These metrics reflect internal DB health      │
├──────────────────────────────────────────────────┤
│            Server Resource Layer                  │
│  CPU | Memory | Disk IO | Network | Filesystem   │
│  → These metrics are the physical foundation     │
└──────────────────────────────────────────────────┘

Based on the three-layer architecture design for database performance monitoring, each layer addresses different concerns:

Server Resource Layer: CPU user time over 80% may indicate poor SQL execution efficiency; IOPS near the limit causes query queuing; insufficient memory triggers swap, which can increase query latency by 10x or more. This layer is covered by node_exporter.
Database Kernel Layer: This is the focus of this article. Connection count, QPS, cache hit rate, lock waits — node_exporter can’t collect these. You need specialized database exporters.
Business Application Layer: Ultimately, it comes down to business metrics. If QPS is 10,000 but average response time is 500ms, the database health is not meeting the bar.

MySQL Monitoring: mysqld_exporter

Deploying mysqld_exporter

mysqld_exporter is the officially maintained MySQL monitoring exporter by the Prometheus project. It connects to MySQL and reads SHOW GLOBAL STATUS, information_schema, performance_schema, and other system views to collect metrics.

Step 1: Create a monitoring user

-- Create a dedicated monitoring account with minimal privileges
CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'StrongMonitorPass123!';

-- Grant necessary permissions
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'localhost';

-- For performance_schema metrics (recommended)
GRANT SELECT ON performance_schema.* TO 'mysqld_exporter'@'localhost';

-- Flush privileges
FLUSH PRIVILEGES;

Permission overview:

Permission	Purpose	Necessity
PROCESS	View process list, collect connection info	Required
REPLICATION CLIENT	View replication status	Required for replication
SELECT (performance_schema)	Collect performance schema metrics	Recommended
SELECT (information_schema)	Collect table size, engine status	Recommended

Step 2: Install exporter

# Download latest version (as of July 2026, v0.18.0)
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.18.0/mysqld_exporter-0.18.0.linux-amd64.tar.gz

# Extract
tar xzf mysqld_exporter-0.18.0.linux-amd64.tar.gz
mv mysqld_exporter-0.18.0.linux-amd64 /usr/local/mysqld_exporter

# Create configuration file (stores connection info)
cat > /usr/local/mysqld_exporter/.my.cnf << 'EOF'
[client]
user=mysqld_exporter
password=StrongMonitorPass123!
EOF

# Set file permissions (password security)
chmod 600 /usr/local/mysqld_exporter/.my.cnf

Step 3: Create systemd service

cat > /etc/systemd/system/mysqld_exporter.service << 'EOF'
[Unit]
Description=MySQL Exporter for Prometheus
After=network.target mysql.service

[Service]
Type=simple
User=mysql
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter \
  --config.my-cnf=/usr/local/mysqld_exporter/.my.cnf \
  --web.listen-address=:9104 \
  --collect.perf_schema.eventsstatements \
  --collect.perf_schema.eventswaits \
  --collect.perf_schema.file_instances \
  --collect.global_status \
  --collect.global_variables \
  --collect.info_schema.innodb_metrics \
  --collect.info_schema.innodb_tablespaces \
  --collect.info_schema.processlist \
  --collect.info_schema.tables \
  --collect.auto_increment.columns
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl enable mysqld_exporter
systemctl start mysqld_exporter

# Verify
curl -s http://localhost:9104/metrics | head -20

The --collect parameters control which metric groups to collect. The configuration above is what I use in production — covering performance schema, InnoDB engine status, process list, and table statistics. Each --collect parameter adds query load on MySQL. If the database is already under heavy load, start with just global_status and global_variables, then add more as needed.

MySQL Core Monitoring Metrics

mysqld_exporter collects hundreds of metrics, but the ones you should monitor daily are:

Connections and Concurrency

Metric Name	Meaning	Alert Threshold
`mysql_global_status_threads_connected`	Current connections	> max_connections * 80%
`mysql_global_status_threads_running`	Active threads	Consistently > 50, investigate
`mysql_global_status_max_used_connections`	Historical max connections	Near max_connections, scale up
`mysql_global_status_aborted_connects`	Failed connection attempts	Sudden spike, investigate
`mysql_global_status_threads_created`	Threads created	Continuous growth = thread_cache_size too small

Query Performance

Metric Name	Meaning	Focus
`mysql_global_status_questions`	Total queries	Used to calculate QPS
`mysql_global_status_slow_queries`	Slow query count	Continuous growth = investigate slow SQL
`mysql_global_status_com_select`	SELECT count	Distinguish read/write ratio
`mysql_global_status_com_insert`	INSERT count	—
`mysql_global_status_com_update`	UPDATE count	—

InnoDB Engine

Metric Name	Meaning	Focus
`mysql_global_status_innodb_buffer_pool_pages_free`	Free buffer pages	Too few = insufficient memory
`mysql_global_status_innodb_buffer_pool_read_requests`	Buffer pool read requests	Calculate hit rate with physical reads
`mysql_global_status_innodb_buffer_pool_reads`	Physical disk reads	Buffer pool misses
`mysql_global_status_innodb_row_lock_waits`	Row lock wait count	Spike = heavy lock contention
`mysql_global_status_innodb_row_lock_time_avg`	Average lock wait time	> 50ms, investigate

Replication Status

Metric Name	Meaning	Alert Threshold
`mysql_slave_status_slave_io_running`	IO thread status	!= 1, alert
`mysql_slave_status_slave_sql_running`	SQL thread status	!= 1, alert
`mysql_slave_status_seconds_behind_master`	Replication lag seconds	> 60, alert

Key PromQL Queries

# QPS (queries per second)
rate(mysql_global_status_questions[1m])

# Slow query growth rate
rate(mysql_global_status_slow_queries[5m])

# Connection usage rate
mysql_global_status_threads_connected / mysql_global_variables_max_connections * 100

# InnoDB buffer pool hit rate
1 - (rate(mysql_global_status_innodb_buffer_pool_reads[5m]) /
     rate(mysql_global_status_innodb_buffer_pool_read_requests[5m]))

# Row lock waits per minute
rate(mysql_global_status_innodb_row_lock_waits[1m])

# Replication lag
mysql_slave_status_seconds_behind_master

# Running threads
mysql_global_status_threads_running

Buffer pool hit rate is one of the most important metrics. Below 99% means InnoDB buffer pool memory is insufficient — it’s reading from disk too frequently, which significantly increases query latency. Below 95%, you need to add memory immediately.

PostgreSQL Monitoring: postgres_exporter

Deploying postgres_exporter

postgres_exporter is the PostgreSQL monitoring exporter maintained by the Prometheus community. It connects to PostgreSQL and executes queries against pg_stat_* system views to collect metrics.

Step 1: Create a monitoring user

-- Create monitoring user
CREATE USER monitor WITH PASSWORD 'StrongMonitorPass123!';

-- Set search path
ALTER USER monitor SET search_path TO monitor, pg_catalog;

-- PostgreSQL 10+ uses pg_monitor role (recommended)
GRANT pg_monitor TO monitor;

-- For pg_stat_statements extension (query performance analysis)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
GRANT SELECT ON pg_stat_statements TO monitor;

-- For custom queries
CREATE SCHEMA IF NOT EXISTS monitor AUTHORIZATION monitor;

The pg_monitor role was introduced in PostgreSQL 10. It includes a set of read-only permissions for accessing statistics views. Using this role is much more convenient than manually granting each permission, and it’s the officially recommended approach. According to the postgres_exporter deployment guide, this approach is secure and doesn’t require superuser privileges.

Step 2: Install exporter

# Download
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz

tar xzf postgres_exporter-0.15.0.linux-amd64.tar.gz
mv postgres_exporter-0.15.0.linux-amd64 /usr/local/postgres_exporter

# Pass connection info via environment variable
cat > /usr/local/postgres_exporter/postgres_exporter.env << 'EOF'
DATA_SOURCE_NAME=postgresql://monitor:StrongMonitorPass123!@localhost:5432/postgres?sslmode=disable
EOF

Step 3: Create systemd service

cat > /etc/systemd/system/postgres_exporter.service << 'EOF'
[Unit]
Description=PostgreSQL Exporter for Prometheus
After=network.target postgresql.service

[Service]
Type=simple
User=postgres
EnvironmentFile=/usr/local/postgres_exporter/postgres_exporter.env
ExecStart=/usr/local/postgres_exporter/postgres_exporter \
  --web.listen-address=:9187 \
  --extend.query-path=/usr/local/postgres_exporter/queries.yaml \
  --auto-discover-databases
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl enable postgres_exporter
systemctl start postgres_exporter

# Verify
curl -s http://localhost:9187/metrics | head -20

The --auto-discover-databases parameter lets the exporter automatically discover and monitor all databases. --extend.query-path points to a custom query configuration file for metrics that the exporter doesn’t collect by default.

Custom Query Configuration

postgres_exporter supports extending collected metrics through a YAML file — this is where it’s more flexible than mysqld_exporter. You can write your own SQL queries and export business-related metrics:

# /usr/local/postgres_exporter/queries.yaml

# Table bloat detection
pg_table_bloat:
  query: >
    SELECT
      schemaname AS schema,
      relname AS table_name,
      n_live_tup AS live_tuples,
      n_dead_tup AS dead_tuples,
      ROUND(n_dead_tup::FLOAT / GREATEST(n_live_tup, 1) * 100, 2) AS dead_ratio
    FROM pg_stat_user_tables
    WHERE n_live_tup > 1000
    ORDER BY dead_ratio DESC
    LIMIT 20    
  metrics:
    - schema:
        usage: "LABEL"
        description: "Schema name"
    - table_name:
        usage: "LABEL"
        description: "Table name"
    - live_tuples:
        usage: "GAUGE"
        description: "Number of live tuples"
    - dead_tuples:
        usage: "GAUGE"
        description: "Number of dead tuples"
    - dead_ratio:
        usage: "GAUGE"
        description: "Dead tuple ratio percentage"

# Long-running transaction detection
pg_long_running_transactions:
  query: >
    SELECT
      pid,
      extract(EPOCH FROM (now() - xact_start)) AS duration_seconds,
      state,
      query
    FROM pg_stat_activity
    WHERE xact_start IS NOT NULL
      AND now() - xact_start > interval '5 minutes'    
  metrics:
    - pid:
        usage: "LABEL"
        description: "Process ID"
    - duration_seconds:
        usage: "GAUGE"
        description: "Transaction duration in seconds"
    - state:
        usage: "LABEL"
        description: "Transaction state"
    - query:
        usage: "LABEL"
        description: "Query text"

# Connection pool usage
pg_connection_pool:
  query: >
    SELECT
      datname AS database,
      count(*) AS total_connections,
      count(*) FILTER (WHERE state = 'active') AS active_connections,
      count(*) FILTER (WHERE state = 'idle') AS idle_connections,
      count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_transaction
    FROM pg_stat_activity
    GROUP BY datname    
  metrics:
    - database:
        usage: "LABEL"
        description: "Database name"
    - total_connections:
        usage: "GAUGE"
        description: "Total connections"
    - active_connections:
        usage: "GAUGE"
        description: "Active connections"
    - idle_connections:
        usage: "GAUGE"
        description: "Idle connections"
    - idle_in_transaction:
        usage: "GAUGE"
        description: "Idle in transaction connections"

PostgreSQL Core Monitoring Metrics

Connections and Transactions

Metric Name	Meaning	Focus
`pg_stat_database_numbackends`	Current connections	Near max_connections, scale up
`pg_stat_database_xact_commit`	Committed transactions	Calculate TPS
`pg_stat_database_xact_rollback`	Rolled back transactions	High rollback rate = problem
`pg_stat_database_blks_hit`	Buffer hits	Calculate cache hit rate
`pg_stat_database_blks_read`	Disk reads	Calculate hit rate with blks_hit
`pg_stat_database_deadlocks`	Deadlock count	Should be 0; any = investigate

Replication Status

Metric Name	Meaning	Alert Threshold
`pg_stat_replication_pg_wal_lsn_diff`	WAL lag in bytes	> 1GB, investigate
`pg_replication_lag`	Replication lag seconds	> 60, alert

Locks and Waits

Metric Name	Meaning	Focus
`pg_locks_count`	Lock count	Spike = investigate
`pg_stat_activity_count`	Active sessions	Continuous growth = concern
`pg_stat_activity_max_tx_duration`	Longest transaction duration	> 300 seconds, alert

Key PromQL Queries

# TPS (transactions per second)
rate(pg_stat_database_xact_commit[1m]) + rate(pg_stat_database_xact_rollback[1m])

# Transaction rollback rate
rate(pg_stat_database_xact_rollback[5m]) /
(rate(pg_stat_database_xact_commit[5m]) + rate(pg_stat_database_xact_rollback[5m])) * 100

# Cache hit rate
rate(pg_stat_database_blks_hit[5m]) /
(rate(pg_stat_database_blks_hit[5m]) + rate(pg_stat_database_blks_read[5m])) * 100

# Active connections
pg_stat_database_numbackends

# Replication lag (bytes)
pg_stat_replication_pg_wal_lsn_diff

# Cumulative deadlocks
pg_stat_database_deadlocks

Prometheus Scrape Configuration

After deploying both exporters, you need to add scrape jobs in Prometheus:

# prometheus.yml

scrape_configs:
  # MySQL monitoring
  - job_name: 'mysql'
    scrape_interval: 15s
    static_configs:
      - targets: ['mysql-prod-01:9104', 'mysql-prod-02:9104']
        labels:
          env: 'production'
          cluster: 'mysql-prod'
      - targets: ['mysql-test-01:9104']
        labels:
          env: 'staging'
          cluster: 'mysql-test'

  # PostgreSQL monitoring
  - job_name: 'postgresql'
    scrape_interval: 15s
    static_configs:
      - targets: ['pg-prod-01:9187', 'pg-prod-02:9187']
        labels:
          env: 'production'
          cluster: 'pg-prod'
      - targets: ['pg-test-01:9187']
        labels:
          env: 'staging'
          cluster: 'pg-test'

A 15-second scrape interval is a reasonable default. If you need more granular monitoring (e.g., capturing short spikes), you can set it to 10 seconds, but be aware this increases Prometheus storage pressure and exporter query load on the database. Below 5 seconds is not recommended unless you know exactly what you’re doing.

For large database clusters, service discovery is recommended over static configuration:

scrape_configs:
  # Using Consul service discovery
  - job_name: 'mysql-consul'
    consul_sd_configs:
      - server: 'consul.internal:8500'
        services: ['mysqld-exporter']
    relabel_configs:
      - source_labels: [__meta_consul_tags]
        regex: '.*,production,.*'
        target_label: env
        replacement: 'production'

  # Using file service discovery (good for VM environments)
  - job_name: 'postgresql-file'
    file_sd_configs:
      - files: ['/etc/prometheus/targets/pg-*.yml']
        refresh_interval: 30s

File service discovery example:

# /etc/prometheus/targets/pg-prod.yml
- targets:
    - 10.0.1.10:9187
    - 10.0.1.11:9187
  labels:
    env: production
    cluster: pg-prod
    role: primary

Alert Rule Design

Monitoring isn’t the goal — alerting is. But alert design is the most commonly botched part of database monitoring. Too many alerts and people go numb; too few and problems slip through.

Alert Tiering Principles

Based on the alerting philosophy from the Google SRE Book, I classify database alerts into three levels:

Level	Meaning	Response Requirement	Examples
P0 - Critical	Database unavailable or about to fail	Immediate, anytime	Primary down, replication broken, disk full
P1 - Warning	Severe performance degradation affecting business	Respond during business hours	Connections > 80%, replication lag > 60s
P2 - Info	Potential risk, not yet affecting business	Handle during routine inspection	Cache hit rate dropping, slow queries increasing

MySQL Alert Rules

# /etc/prometheus/rules/mysql-alerts.yml

groups:
  - name: mysql-alerts
    rules:
      # ===== P0 Level =====

      # MySQL instance down
      - alert: MySQLDown
        expr: mysql_up == 0
        for: 1m
        labels:
          severity: P0
        annotations:
          summary: "MySQL instance down {{ $labels.instance }}"
          description: "MySQL instance {{ $labels.instance }} has been offline for over 1 minute"

      # Replication IO thread stopped
      - alert: MySQLReplicationIOStopped
        expr: mysql_slave_status_slave_io_running == 0
        for: 1m
        labels:
          severity: P0
        annotations:
          summary: "MySQL replication IO thread stopped {{ $labels.instance }}"
          description: "IO thread on replica {{ $labels.instance }} has stopped"

      # Replication SQL thread stopped
      - alert: MySQLReplicationSQLStopped
        expr: mysql_slave_status_slave_sql_running == 0
        for: 1m
        labels:
          severity: P0
        annotations:
          summary: "MySQL replication SQL thread stopped {{ $labels.instance }}"
          description: "SQL thread on replica {{ $labels.instance }} has stopped"

      # ===== P1 Level =====

      # High connection count
      - alert: MySQLHighConnections
        expr: >
          mysql_global_status_threads_connected /
          mysql_global_variables_max_connections * 100 > 80          
        for: 5m
        labels:
          severity: P1
        annotations:
          summary: "MySQL high connections {{ $labels.instance }}"
          description: "Connection usage {{ printf \"%.1f\" $value }}%, above 80% for 5 minutes"

      # High replication lag
      - alert: MySQLReplicationLag
        expr: mysql_slave_status_seconds_behind_master > 60
        for: 5m
        labels:
          severity: P1
        annotations:
          summary: "MySQL replication lag {{ $labels.instance }}"
          description: "Replica {{ $labels.instance }} is {{ $value }} seconds behind"

      # Slow query spike
      - alert: MySQLSlowQueries
        expr: rate(mysql_global_status_slow_queries[5m]) > 5
        for: 10m
        labels:
          severity: P1
        annotations:
          summary: "MySQL slow query spike {{ $labels.instance }}"
          description: "Slow query rate {{ printf \"%.1f\" $value }} per second"

      # Excessive row lock waits
      - alert: MySQLRowLockWaits
        expr: rate(mysql_global_status_innodb_row_lock_waits[5m]) > 10
        for: 5m
        labels:
          severity: P1
        annotations:
          summary: "MySQL row lock waits {{ $labels.instance }}"
          description: "Row lock waits {{ printf \"%.1f\" $value }} per second"

      # ===== P2 Level =====

      # Low buffer pool hit rate
      - alert: MySQLLowBufferPoolHitRate
        expr: >
          1 - (rate(mysql_global_status_innodb_buffer_pool_reads[5m]) /
               rate(mysql_global_status_innodb_buffer_pool_read_requests[5m])) < 0.99          
        for: 15m
        labels:
          severity: P2
        annotations:
          summary: "MySQL low buffer pool hit rate {{ $labels.instance }}"
          description: "Buffer pool hit rate {{ printf \"%.2f\" (1 - $value) }}%, below 99%"

      # Connection failures increasing
      - alert: MySQLAbortedConnects
        expr: rate(mysql_global_status_aborted_connects[5m]) > 10
        for: 5m
        labels:
          severity: P2
        annotations:
          summary: "MySQL aborted connects {{ $labels.instance }}"
          description: "Connection failures {{ printf \"%.1f\" $value }} per second"

PostgreSQL Alert Rules

# /etc/prometheus/rules/postgresql-alerts.yml

groups:
  - name: postgresql-alerts
    rules:
      # ===== P0 Level =====

      # PostgreSQL instance down
      - alert: PostgreSQLDown
        expr: pg_up == 0
        for: 1m
        labels:
          severity: P0
        annotations:
          summary: "PostgreSQL instance down {{ $labels.instance }}"
          description: "PostgreSQL instance {{ $labels.instance }} is offline"

      # ===== P1 Level =====

      # High connection count
      - alert: PostgreSQLHighConnections
        expr: >
          pg_stat_database_numbackends /
          pg_settings_max_connections * 100 > 80          
        for: 5m
        labels:
          severity: P1
        annotations:
          summary: "PostgreSQL high connections {{ $labels.instance }}"
          description: "Connection usage {{ printf \"%.1f\" $value }}%"

      # High replication lag
      - alert: PostgreSQLReplicationLag
        expr: pg_stat_replication_pg_wal_lsn_diff > 1073741824
        # 1073741824 bytes = 1GB
        for: 5m
        labels:
          severity: P1
        annotations:
          summary: "PostgreSQL replication lag {{ $labels.instance }}"
          description: "WAL lag {{ printf \"%.2f\" ($value / 1073741824) }} GB"

      # Deadlock detected
      - alert: PostgreSQLDeadlocks
        expr: rate(pg_stat_database_deadlocks[5m]) > 0
        for: 1m
        labels:
          severity: P1
        annotations:
          summary: "PostgreSQL deadlock {{ $labels.instance }}"
          description: "Deadlock detected, rate {{ $value }} per second"

      # High rollback rate
      - alert: PostgreSQLHighRollbackRate
        expr: >
          rate(pg_stat_database_xact_rollback[5m]) /
          (rate(pg_stat_database_xact_commit[5m]) +
           rate(pg_stat_database_xact_rollback[5m])) * 100 > 10          
        for: 10m
        labels:
          severity: P1
        annotations:
          summary: "PostgreSQL high rollback rate {{ $labels.instance }}"
          description: "Transaction rollback rate {{ printf \"%.1f\" $value }}%"

      # ===== P2 Level =====

      # Low cache hit rate
      - alert: PostgreSQLLowCacheHitRate
        expr: >
          rate(pg_stat_database_blks_hit[5m]) /
          (rate(pg_stat_database_blks_hit[5m]) +
           rate(pg_stat_database_blks_read[5m])) * 100 < 95          
        for: 15m
        labels:
          severity: P2
        annotations:
          summary: "PostgreSQL low cache hit rate {{ $labels.instance }}"
          description: "Cache hit rate {{ printf \"%.1f\" $value }}%, below 95%"

Alert Management: Reducing Alert Noise

Too many alerts is more dangerous than too few — when your phone buzzes 30 times an hour, you start ignoring everything. These practices effectively reduce alert noise:

1. Use for duration to filter transient spikes

# Wrong: alert on any momentary spike over 80%
expr: connection_usage > 80
for: 0m

# Right: alert only after sustained 80%+ for 5 minutes
expr: connection_usage > 80
for: 5m

Database connections briefly exceeding 80% during traffic peaks is normal. Only sustained high levels indicate a problem.

2. Differentiate primary/replica roles — only alert on critical metrics for the primary

# Only alert on primary replication status
expr: >
  mysql_slave_status_slave_io_running == 0
  and on(instance) mysql_master_status == 1

Replica replication interruption does need alerting, but if your architecture uses multi-source replication or read-only replicas, the alert level can be lower.

3. Use inhibition rules to prevent alert storms

# Alertmanager config: when MySQL instance is down, suppress all other alerts for that instance
inhibit_rules:
  - source_match:
      severity: P0
      alertname: MySQLDown
    target_match_re:
      severity: P1|P2
    equal: ['instance']

If the instance is already down, alerting about “high connections” or “slow queries” is pointless and distracting.

Grafana Dashboard Configuration

Recommended Community Dashboards

You don’t need to build dashboards from scratch — the Grafana community has plenty of ready-made database monitoring panels:

MySQL Dashboards:

Grafana Dashboard ID: 7362 — MySQL Overview (most popular, covers connections, queries, InnoDB, replication)
Grafana Dashboard ID: 6239 — MySQL Replication (focused on replication monitoring)
Grafana Dashboard ID: 9645 — MySQL InnoDB Metrics (InnoDB engine details)

PostgreSQL Dashboards:

Grafana Dashboard ID: 9628 — PostgreSQL Database (general panel)
Grafana Dashboard ID: 17026 — PostgreSQL Details (detailed metrics)
Grafana Dashboard ID: 12485 — pg_stat_statements (query performance analysis)

Import method: Grafana → Dashboards → Import → enter Dashboard ID → Load.

Key Panels for Custom Dashboards

Community dashboards are convenient, but every team’s business scenario is different. I recommend adding a few business-relevant panels on top of community dashboards:

Panel 1: Connection Trends (multi-instance comparison)

# Panel: Connection count trends
mysql_global_status_threads_connected{cluster=~"$cluster"}

Set up a $cluster variable for filtering by cluster. Use Time series chart type, overlay multiple instances for comparison — you can spot which instance has abnormal connections at a glance.

Panel 2: QPS / TPS Comparison

# Panel: QPS
rate(mysql_global_status_questions[1m])

# Panel: TPS
rate(mysql_global_status_com_insert[1m]) +
rate(mysql_global_status_com_update[1m]) +
rate(mysql_global_status_com_delete[1m])

Put QPS and TPS in the same panel to determine whether the database is read-heavy or write-heavy.

Panel 3: Cache Hit Rate Gauge

# Panel: InnoDB buffer pool hit rate
(1 - rate(mysql_global_status_innodb_buffer_pool_reads[5m]) /
     rate(mysql_global_status_innodb_buffer_pool_read_requests[5m])) * 100

Use Gauge chart type with thresholds at 95% (yellow) and 99% (green) for an at-a-glance view.

Panel 4: Top 10 Slow Queries (Table)

# Panel: Slow query ranking (requires performance_schema)
topk(10,
  sum by (digest_text) (
    rate(mysql_perf_schema_events_statements_sum_total_timer_wait[5m])
  )
)

Use Table chart type to show the 10 slowest SQL statements for quick bottleneck identification.

Panel 5: Replication Lag

# Panel: Replication lag
mysql_slave_status_seconds_behind_master{cluster=~"$cluster"}

Use Time series chart type, overlay all replicas’ lag trends — the abnormal one will stand out.

Monitoring Solution Comparison and Selection

Prometheus vs Commercial Solutions

Besides the self-hosted Prometheus + exporter approach, there are many commercial database monitoring tools. Based on the MySQL monitoring tool comparison, here’s how the mainstream solutions compare:

Dimension	Prometheus + Exporter	Percona PMM	Datadog	Cloud Provider Monitoring
Cost	Free	Free tier available	Per-host pricing	Per-instance pricing
Deployment difficulty	Medium	Low	Low	Very low
Customization	Excellent	Strong	Medium	Weak
MySQL support	Good	Excellent (Percona maintains MySQL forks)	Good	Average
PostgreSQL support	Good	Average	Good	Average
Alerting	Alertmanager, manual config	Built-in	Built-in	Built-in
Long-term storage	Needs Thanos/VictoriaMetrics	Built-in	Built-in	Built-in
Scale fit	Small to large	Small to medium	Medium to large	Small

My selection advice:

Small team / startup: Cloud provider’s built-in database monitoring is sufficient. Don’t over-engineer.
Medium scale / mixed databases: Prometheus + exporters. Good balance of flexibility and cost control.
MySQL-focused / need deep analysis: Percona PMM is worth trying. Its Query Analytics is powerful.
Budget available / don’t want to maintain: Datadog. Works out of the box, alerting and visualization are well done.

PostgreSQL-Specific Solution: pgwatch3

For teams primarily using PostgreSQL, besides postgres_exporter, there’s another tool worth considering: pgwatch3. Based on the pgwatch3 PostgreSQL monitoring analysis, it’s an open-source PostgreSQL-specific monitoring tool developed by CYBERTEC in Go, with dozens of pre-built Grafana dashboards, SQL-based custom metrics, and one-line Docker deployment.

Solution	Deployment Time	Built-in Dashboards	Custom Metrics	Learning Curve
Prometheus + postgres_exporter	0.5-1 day	Need to import community IDs	Modify SQL/exporter	Medium
pgwatch3	10 minutes	Dozens	Write SQL directly	Gentle
Zabbix template	1-2 days	Need to configure	Write scripts	Steep

If your environment is pure PostgreSQL and you don’t want to spend time building Grafana dashboards, pgwatch3 is the more efficient choice. But if your monitoring system is already Prometheus-based, postgres_exporter integrates more easily.

Production Deployment Checklist

#!/bin/bash
# Database monitoring one-click deployment script (Prometheus + Exporter)
# For RHEL/CentOS 8+, MySQL + PostgreSQL environments

set -euo pipefail

# ===== Configuration =====
MYSQL_EXPORTER_VERSION="0.18.0"
PG_EXPORTER_VERSION="0.15.0"
EXPORTER_USER="monitor"
EXPORTER_GROUP="monitor"

# ===== Create user =====
id -u $EXPORTER_USER &>/dev/null || useradd -r -s /sbin/nologin $EXPORTER_USER

# ===== MySQL Exporter =====
echo "=== Installing mysqld_exporter ==="
cd /tmp
wget -q "https://github.com/prometheus/mysqld_exporter/releases/download/v${MYSQL_EXPORTER_VERSION}/mysqld_exporter-${MYSQL_EXPORTER_VERSION}.linux-amd64.tar.gz"
tar xzf "mysqld_exporter-${MYSQL_EXPORTER_VERSION}.linux-amd64.tar.gz"
mv "mysqld_exporter-${MYSQL_EXPORTER_VERSION}.linux-amd64" /usr/local/mysqld_exporter
chown -R $EXPORTER_USER:$EXPORTER_GROUP /usr/local/mysqld_exporter

# Create configuration file template
cat > /usr/local/mysqld_exporter/.my.cnf << 'EOF'
[client]
user=mysqld_exporter
password=CHANGE_ME_STRONG_PASSWORD
EOF
chmod 600 /usr/local/mysqld_exporter/.my.cnf

# systemd service
cat > /etc/systemd/system/mysqld_exporter.service << EOF
[Unit]
Description=MySQL Exporter
After=network.target

[Service]
Type=simple
User=$EXPORTER_USER
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter \\
  --config.my-cnf=/usr/local/mysqld_exporter/.my.cnf \\
  --web.listen-address=:9104 \\
  --collect.global_status \\
  --collect.global_variables \\
  --collect.perf_schema.eventsstatements \\
  --collect.info_schema.innodb_metrics \\
  --collect.info_schema.processlist
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target
EOF

# ===== PostgreSQL Exporter =====
echo "=== Installing postgres_exporter ==="
wget -q "https://github.com/prometheus-community/postgres_exporter/releases/download/v${PG_EXPORTER_VERSION}/postgres_exporter-${PG_EXPORTER_VERSION}.linux-amd64.tar.gz"
tar xzf "postgres_exporter-${PG_EXPORTER_VERSION}.linux-amd64.tar.gz"
mv "postgres_exporter-${PG_EXPORTER_VERSION}.linux-amd64" /usr/local/postgres_exporter
chown -R $EXPORTER_USER:$EXPORTER_GROUP /usr/local/postgres_exporter

# Environment variable template
cat > /usr/local/postgres_exporter/postgres_exporter.env << 'EOF'
DATA_SOURCE_NAME=postgresql://monitor:CHANGE_ME_STRONG_PASSWORD@localhost:5432/postgres?sslmode=disable
EOF
chmod 600 /usr/local/postgres_exporter/postgres_exporter.env

# systemd service
cat > /etc/systemd/system/postgres_exporter.service << EOF
[Unit]
Description=PostgreSQL Exporter
After=network.target

[Service]
Type=simple
User=$EXPORTER_USER
EnvironmentFile=/usr/local/postgres_exporter/postgres_exporter.env
ExecStart=/usr/local/postgres_exporter/postgres_exporter \\
  --web.listen-address=:9187 \\
  --auto-discover-databases
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target
EOF

# ===== Start services =====
systemctl daemon-reload
systemctl enable mysqld_exporter postgres_exporter
systemctl start mysqld_exporter postgres_exporter

# ===== Verify =====
echo "=== Verification ==="
sleep 2
echo "MySQL Exporter:"
curl -s http://localhost:9104/metrics | grep mysql_up | head -1
echo "PostgreSQL Exporter:"
curl -s http://localhost:9187/metrics | grep pg_up | head -1
echo ""
echo "=== Next Steps ==="
echo "1. Edit /usr/local/mysqld_exporter/.my.cnf with real MySQL credentials"
echo "2. Edit /usr/local/postgres_exporter/postgres_exporter.env with real PG credentials"
echo "3. Add scrape configs to prometheus.yml"
echo "4. Import Grafana dashboards: MySQL=7362, PostgreSQL=9628"
echo "5. Deploy alert rules to Prometheus"

Security Considerations

Database monitoring involves sensitive information — security can’t be taken lightly:

Minimal privileges for monitoring accounts: Only grant PROCESS, REPLICATION CLIENT, SELECT. No write permissions, no SUPER.
Exporter configuration file permissions: .my.cnf and postgres_exporter.env contain plaintext passwords. Must be chmod 600, owned by the user running the exporter.
Port access control: Exporter ports 9104/9187 expose database runtime information. They should not be publicly accessible. Use firewall rules to allow only the Prometheus server:

# iptables rules
iptables -A INPUT -p tcp --dport 9104 -s 10.0.1.100 -j ACCEPT  # Prometheus IP
iptables -A INPUT -p tcp --dport 9104 -j DROP

iptables -A INPUT -p tcp --dport 9187 -s 10.0.1.100 -j ACCEPT
iptables -A INPUT -p tcp --dport 9187 -j DROP

TLS encryption: If traffic between exporter and Prometheus goes over the public internet, enable TLS:

# exporter startup parameter
--web.config.file=/usr/local/mysqld_exporter/web.yml

# web.yml
tls_server_config:
  cert_file: /etc/ssl/exporter.crt
  key_file: /etc/ssl/exporter.key

Don’t expose sensitive information in alert annotations: Alerts get sent to Feishu/DingTalk/Slack channels. Don’t put database passwords or connection strings in descriptions.

Summary

How well you do database monitoring directly determines how fast you can troubleshoot issues. Here are my practical takeaways:

All three monitoring layers are essential. Server resource layer with node_exporter, database kernel layer with mysqld_exporter / postgres_exporter, business application layer with APM or custom metrics. Doing only one layer is like walking on one leg.
The core metrics are just a handful. Connections, QPS/TPS, cache hit rate, slow queries, replication lag, lock waits — nail these six and you have a solid picture of database health. Don’t be intimidated by the hundreds of metrics; most you won’t need.
Alert tiering is key. P0 for immediate response, P1 for business hours, P2 for routine inspection. Untiered alerts are the same as no alerts — when everything is high priority, everyone ignores everything.
Cache hit rate is the most overlooked metric. MySQL InnoDB buffer pool hit rate below 99%, PostgreSQL cache hit rate below 95% — you should add memory. By the time it drops below 90%, the business is already slow.
for duration is the key to reducing false alerts. Connections briefly exceeding 80% doesn’t need an alert. Sustained 80% for 5 minutes does. Getting for right can reduce alert noise by over 60%.
Custom queries are PostgreSQL monitoring’s killer feature. postgres_exporter’s queries.yaml lets you define metrics with SQL — table bloat ratio, long transactions, connection pool status — collect whatever you want. MySQL’s mysqld_exporter isn’t this flexible.
Security can’t be forgotten. Minimal privileges for monitoring accounts, chmod 600 for config files, firewall port restrictions. I’ve seen more than one incident where database monitoring itself leaked passwords.

One final word: the database is the heart of your business, and monitoring is the ECG. Without the ECG properly set up, you won’t even know when the heart has a problem.

References & Acknowledgments

The following resources were consulted during the writing of this article. Thanks to the original authors for their contributions:

Building a MySQL Monitoring System: Common Tools and Core Metrics — CSDN, MySQL monitoring tool comparison and core metrics framework
Database Performance Monitoring and Tuning Practical Guide — Book118, three-layer architecture design for database performance monitoring and core metric definitions
Prometheus PostgreSQL Monitoring in Practice — CSDN, postgres_exporter deployment guide and permission configuration
Building an Efficient Database Monitoring System with Prometheus + Grafana — Tencent Cloud Developer Community, database monitoring platform architecture and deployment guide
pgwatch3: PostgreSQL Monitoring Done Right — Tencent Cloud Developer Community, pgwatch3 vs PostgreSQL monitoring solution comparison
Monitoring Distributed Systems — Google SRE Book — Google SRE Team, alerting philosophy and monitoring principles

Overview#

Three-Layer Architecture of Database Monitoring#

MySQL Monitoring: mysqld_exporter#

Deploying mysqld_exporter#

MySQL Core Monitoring Metrics#

Key PromQL Queries#

PostgreSQL Monitoring: postgres_exporter#

Deploying postgres_exporter#

Custom Query Configuration#

PostgreSQL Core Monitoring Metrics#

Key PromQL Queries#

Prometheus Scrape Configuration#

Alert Rule Design#

Alert Tiering Principles#

MySQL Alert Rules#

PostgreSQL Alert Rules#

Alert Management: Reducing Alert Noise#

Grafana Dashboard Configuration#

Recommended Community Dashboards#

Key Panels for Custom Dashboards#

Monitoring Solution Comparison and Selection#

Prometheus vs Commercial Solutions#

PostgreSQL-Specific Solution: pgwatch3#

Production Deployment Checklist#

Security Considerations#

Summary#

References & Acknowledgments#

Overview

Three-Layer Architecture of Database Monitoring

MySQL Monitoring: mysqld_exporter

Deploying mysqld_exporter

MySQL Core Monitoring Metrics

Key PromQL Queries

PostgreSQL Monitoring: postgres_exporter

Deploying postgres_exporter

Custom Query Configuration

PostgreSQL Core Monitoring Metrics

Key PromQL Queries

Prometheus Scrape Configuration

Alert Rule Design

Alert Tiering Principles

MySQL Alert Rules

PostgreSQL Alert Rules

Alert Management: Reducing Alert Noise

Grafana Dashboard Configuration

Recommended Community Dashboards

Key Panels for Custom Dashboards

Monitoring Solution Comparison and Selection

Prometheus vs Commercial Solutions

PostgreSQL-Specific Solution: pgwatch3

Production Deployment Checklist

Security Considerations

Summary

References & Acknowledgments