MySQL/PostgreSQL Exporters

Introduction

Database monitoring is a critical aspect of maintaining healthy applications and infrastructure. Prometheus, with its powerful time-series data collection and alerting capabilities, provides an excellent framework for monitoring database systems. This guide focuses on two important Prometheus exporters designed specifically for relational databases: the MySQL Exporter and the PostgreSQL Exporter.

These exporters allow you to collect metrics from your MySQL and PostgreSQL database instances and integrate them into your Prometheus monitoring environment. By the end of this guide, you'll understand how these exporters work, how to set them up, and how to use them effectively to monitor your database performance and health.

MySQL Exporter Overview

The MySQL Prometheus Exporter is an official exporter maintained by the Prometheus community. It connects to a MySQL server, collects various metrics, and exposes them in a format that Prometheus can scrape.

Key Metrics Collected

The MySQL Exporter collects metrics including:

• Query performance statistics
• Connection pool status
• Buffer pool utilization
• InnoDB metrics
• Replication lag
• Query cache statistics
• Table lock contention metrics

Installing and Configuring MySQL Exporter

Prerequisites

• A running MySQL server
• Prometheus server set up
• Basic understanding of database monitoring concepts

Installation

You can download the MySQL Exporter binary from the Prometheus GitHub repository or use Docker for a containerized setup.

Using Docker

bash

docker pull prom/mysqld-exporter:latest

docker run -d -p 9104:9104 \
  -e DATA_SOURCE_NAME="exporter:password@(hostname:3306)/" \
  prom/mysqld-exporter

Manual Installation

bash

# Download the latest release
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz

# Extract the binary
tar xvfz mysqld_exporter-*.tar.gz
cd mysqld_exporter-*

# Start the exporter
./mysqld_exporter

Creating MySQL User for Monitoring

For the exporter to function properly, it needs a MySQL user with appropriate permissions:

sql

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'password' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';

Configuration

Create a configuration file .my.cnf with your MySQL credentials:

ini

[client]
host=localhost
user=exporter
password=password

Start the exporter with this configuration:

bash

export DATA_SOURCE_NAME="exporter:password@(localhost:3306)/"
./mysqld_exporter

Adding MySQL Exporter to Prometheus

Add this configuration to your prometheus.yml file:

yaml

scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']
    metrics_path: /metrics

PostgreSQL Exporter Overview

The PostgreSQL Exporter works similarly to the MySQL Exporter but is designed specifically for PostgreSQL databases. It collects a wide range of PostgreSQL-specific metrics.

Key Metrics Collected

• Database and table statistics
• Transaction rate and query execution times
• Connection pool status
• WAL (Write-Ahead Log) metrics
• Replication status
• Buffer cache hit ratio
• Lock statistics

Installing and Configuring PostgreSQL Exporter

Prerequisites

• A running PostgreSQL server
• Prometheus server set up

Installation

Using Docker

bash

docker pull wrouesnel/postgres_exporter:latest

docker run -d -p 9187:9187 \
  -e DATA_SOURCE_NAME="postgresql://postgres:password@hostname:5432/postgres?sslmode=disable" \
  wrouesnel/postgres_exporter

Manual Installation

bash

# Download the latest release
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.11.1/postgres_exporter-0.11.1.linux-amd64.tar.gz

# Extract the binary
tar xvfz postgres_exporter-*.tar.gz
cd postgres_exporter-*

# Start the exporter
./postgres_exporter

Creating PostgreSQL User for Monitoring

Create a dedicated user for monitoring:

sql

CREATE USER postgres_exporter WITH PASSWORD 'password';
GRANT pg_monitor TO postgres_exporter;

For PostgreSQL versions earlier than 10:

sql

CREATE USER postgres_exporter WITH PASSWORD 'password';
ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog;

-- If pg_stat_statements is enabled
GRANT SELECT ON pg_stat_statements TO postgres_exporter;

-- Necessary for basic functionality
GRANT SELECT ON pg_stat_activity TO postgres_exporter;

Configuration

Set the database connection string:

bash

export DATA_SOURCE_NAME="postgresql://postgres_exporter:password@localhost:5432/postgres?sslmode=disable"
./postgres_exporter

Adding PostgreSQL Exporter to Prometheus

Add this configuration to your prometheus.yml file:

yaml

scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['localhost:9187']
    metrics_path: /metrics

Understanding Key Database Metrics

Connection Metrics

Monitoring database connections helps identify potential connection pool issues:

Query Performance Metrics

Tracking query performance helps optimize slow or problematic queries:

For MySQL:

sql

mysql_global_status_queries
mysql_global_status_slow_queries
mysql_global_status_questions

For PostgreSQL:

sql

pg_stat_activity_count
pg_stat_activity_max_tx_duration
pg_stat_statements_total_*

Storage and Cache Metrics

Buffer cache hit ratios indicate how effectively your database is using memory:

Dashboard Creation

Grafana Dashboard for MySQL

Here's a sample Grafana dashboard configuration for MySQL:

json

{
  "panels": [
    {
      "title": "MySQL Connections",
      "type": "graph",
      "datasource": "Prometheus",
      "targets": [
        {
          "expr": "mysql_global_status_threads_connected",
          "legendFormat": "Connected Threads"
        },
        {
          "expr": "mysql_global_variables_max_connections",
          "legendFormat": "Max Connections"
        }
      ]
    },
    {
      "title": "MySQL Query Rate",
      "type": "graph",
      "datasource": "Prometheus",
      "targets": [
        {
          "expr": "rate(mysql_global_status_queries[5m])",
          "legendFormat": "Queries/sec"
        }
      ]
    }
  ]
}

Grafana Dashboard for PostgreSQL

Here's a sample Grafana dashboard configuration for PostgreSQL:

json

{
  "panels": [
    {
      "title": "PostgreSQL Connections",
      "type": "graph",
      "datasource": "Prometheus",
      "targets": [
        {
          "expr": "pg_stat_activity_count",
          "legendFormat": "Active Connections"
        },
        {
          "expr": "pg_settings_max_connections",
          "legendFormat": "Max Connections"
        }
      ]
    },
    {
      "title": "PostgreSQL Transaction Rate",
      "type": "graph",
      "datasource": "Prometheus",
      "targets": [
        {
          "expr": "rate(pg_stat_database_xact_commit{datname=\"postgres\"}[5m])",
          "legendFormat": "Commits/sec"
        },
        {
          "expr": "rate(pg_stat_database_xact_rollback{datname=\"postgres\"}[5m])",
          "legendFormat": "Rollbacks/sec"
        }
      ]
    }
  ]
}

Setting Up Alerts

Important Alerts for MySQL

yaml

groups:
- name: mysql_alerts
  rules:
  - alert: MySQLHighConnectionRate
    expr: rate(mysql_global_status_threads_connected[5m]) > 0.75 * mysql_global_variables_max_connections
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "MySQL connection pool approaching limit"
      description: "MySQL instance {{ $labels.instance }} is using over 75% of available connections."
      
  - alert: MySQLSlowQueries
    expr: rate(mysql_global_status_slow_queries[5m]) > 5
    for: 15m
    labels:
      severity: warning
    annotations:
      summary: "High number of slow queries"
      description: "MySQL instance {{ $labels.instance }} has {{ $value }} slow queries per second."

Important Alerts for PostgreSQL

yaml

groups:
- name: postgresql_alerts
  rules:
  - alert: PostgreSQLHighConnectionRate
    expr: pg_stat_activity_count > 0.75 * pg_settings_max_connections
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "PostgreSQL connection pool approaching limit"
      description: "PostgreSQL instance {{ $labels.instance }} is using over 75% of available connections."
      
  - alert: PostgreSQLLongRunningQueries
    expr: pg_stat_activity_max_tx_duration > 600
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "Long running PostgreSQL queries detected"
      description: "PostgreSQL instance {{ $labels.instance }} has queries running for more than 10 minutes."

Real-World Example: Monitoring Database Replication

MySQL Replication Monitoring

Monitoring replication lag is crucial for MySQL-based applications:

yaml

groups:
- name: mysql_replication_alerts
  rules:
  - alert: MySQLReplicationLag
    expr: mysql_slave_status_seconds_behind_master > 300
    for: 5m
    labels:
      severity: critical
    annotations:
      summary: "MySQL replication is lagging"
      description: "MySQL replication on {{ $labels.instance }} is {{ $value }} seconds behind master."

PostgreSQL Replication Monitoring

For PostgreSQL replication:

yaml

groups:
- name: postgresql_replication_alerts
  rules:
  - alert: PostgreSQLReplicationLag
    expr: pg_stat_replication_lag_bytes > 50000000
    for: 5m
    labels:
      severity: critical
    annotations:
      summary: "PostgreSQL replication is lagging"
      description: "PostgreSQL replication on {{ $labels.instance }} is {{ $value }} bytes behind master."

Troubleshooting Common Issues

MySQL Exporter Issues

1. Connection Errors

   If the exporter cannot connect to MySQL, check:

   • MySQL server is running
   • Credentials are correct
   • Network connectivity between exporter and database
   • Firewall settings

2. Missing Metrics

   If certain metrics are missing:

   • Ensure the monitoring user has proper permissions
   • Check if specific plugins (like InnoDB) are enabled
   • Verify MySQL version compatibility

PostgreSQL Exporter Issues

1. Authentication Failures

   For auth issues:

   • Verify the PostgreSQL user exists
   • Check password is correct
   • Ensure pg_hba.conf allows connections from the exporter

2. Custom Queries Not Working

   If custom queries don't work:

   • Confirm the query works when run directly in PostgreSQL
   • Check for syntax errors in your custom query configuration
   • Ensure the monitoring user has permissions for the tables queried

Performance Considerations

When deploying database exporters in production environments, consider:

1. Resource Usage: Exporters should be lightweight but still require some resources.
2. Scrape Interval: Balance between data granularity and performance impact.
3. Query Complexity: Custom queries can impact database performance.

Best Practices

1. Dedicated Monitoring User: Always create a separate database user with minimal permissions for monitoring.
2. Regular Updates: Keep exporters updated to benefit from bug fixes and new features.
3. HA Setup: For critical databases, consider running multiple exporter instances.
4. Custom Metrics: Extend exporters with custom queries for application-specific metrics.
5. Correlation with Application Metrics: Combine database metrics with application metrics for a complete picture.

Summary

Database monitoring is essential for maintaining reliable and high-performance applications. Prometheus exporters for MySQL and PostgreSQL provide a comprehensive solution for collecting and analyzing database metrics.

By setting up these exporters, you can:

• Proactively identify performance bottlenecks
• Monitor resource utilization
• Set up alerts for critical issues
• Track query performance
• Ensure replication health

With the knowledge gained from this guide, you should be able to effectively monitor your MySQL and PostgreSQL databases using Prometheus, leading to more stable and performant database systems.

Additional Resources

• Official MySQL Exporter Repository
• Official PostgreSQL Exporter Repository
• Prometheus Documentation
• MySQL Performance Schema Documentation
• PostgreSQL Monitoring Documentation

Exercises

1. Set up a MySQL Exporter and create a Grafana dashboard showing connection counts, query rates, and buffer pool utilization.
2. Configure a PostgreSQL Exporter with custom queries to monitor specific tables in your database.
3. Create alert rules for both MySQL and PostgreSQL that notify you when:
   • Database is down
   • Connection pool is near capacity
   • Replication lag exceeds a threshold
   • Disk space is running low
4. Compare performance metrics between MySQL and PostgreSQL for a similar workload.
5. Extend the default metrics with custom queries specific to your application needs.