MySQL and PostgreSQL Exporters for Prometheus

Base de Datos monitoreo es esencial for understanding application rendimiento and system health. The mysqld_exporter and postgres_exporter tools expose database-specific metrics to Prometheus, enabling detailed monitoreo of queries, connections, rendimiento, and replication. Esta guía covers both exporter installation, configuration, metric collection, Grafana dashboards, and alerting rules.

Tabla de Contenidos

Introducción

Base de Datos exporters bridge the gap between database rendimiento and observability platforms. They expose hundreds of metrics about query rendimiento, connections, locks, replication lag, and operational health, enabling operators to detect issues before they impact applications.

Requisitos del Sistema

  • Linux (Ubuntu 20.04+, CentOS 8+)
  • MySQL 5.7+ or PostgreSQL 10+
  • At least 512MB RAM
  • Red access to databases
  • Base de Datos user with monitoreo privileges
  • Prometheus for metric collection

MySQL Exporter Instalación

Paso 1: Crear Base de Datos Usuario

# Connect to MySQL
mysql -u root -p

# Create monitoring user
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'exporter_password';

# Grant privileges
GRANT SELECT, PROCESS, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'exporter'@'localhost';

# For MySQL 8.0+
ALTER USER 'exporter'@'localhost' IDENTIFIED WITH mysql_native_password BY 'exporter_password';

# Flush privileges
FLUSH PRIVILEGES;

# Verify
SELECT user, host FROM mysql.user WHERE user='exporter';

Paso 2: Install Exporter

# Create user
sudo useradd --no-create-home --shell /bin/false mysqld_exporter

# Download
cd /tmp
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0/mysqld_exporter-0.15.0.linux-amd64.tar.gz
tar -xvzf mysqld_exporter-0.15.0.linux-amd64.tar.gz
cd mysqld_exporter-0.15.0.linux-amd64

# Install
sudo cp mysqld_exporter /usr/local/bin/
sudo chown mysqld_exporter:mysqld_exporter /usr/local/bin/mysqld_exporter
sudo chmod +x /usr/local/bin/mysqld_exporter

Paso 3: Crear Configuración

# Create .my.cnf for credentials
sudo tee /etc/mysqld_exporter/.my.cnf > /dev/null << 'EOF'
[client]
user=exporter
password=exporter_password
host=localhost
port=3306
EOF

sudo chown mysqld_exporter:mysqld_exporter /etc/mysqld_exporter/.my.cnf
sudo chmod 600 /etc/mysqld_exporter/.my.cnf

# Or use environment variable
export DATA_SOURCE_NAME="exporter:exporter_password@(localhost:3306)/"

Paso 4: Systemd Servicio

sudo tee /etc/systemd/system/mysqld_exporter.service > /dev/null << 'EOF'
[Unit]
Description=MySQL Exporter
After=mysql.service
Wants=mysql.service

[Service]
User=mysqld_exporter
Group=mysqld_exporter
Type=simple
ExecStart=/usr/local/bin/mysqld_exporter \
  --config.my-cnf=/etc/mysqld_exporter/.my.cnf \
  --web.listen-address=0.0.0.0:9104 \
  --collect.auto_increment.columns \
  --collect.binlog_size \
  --collect.global_status \
  --collect.global_variables \
  --collect.info_schema.innodb_metrics \
  --collect.info_schema.innodb_tablespaces \
  --collect.info_schema.query_response_time

Restart=always
RestartSec=10

StandardOutput=journal
StandardError=journal
SyslogIdentifier=mysqld_exporter

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl daemon-reload
sudo systemctl enable mysqld_exporter
sudo systemctl start mysqld_exporter

Paso 5: Verificar Instalación

# Check metrics
curl http://localhost:9104/metrics | head -30

# Test connectivity
mysql -u exporter -p'exporter_password' -e "SHOW PROCESSLIST;"

PostgreSQL Exporter Instalación

Paso 1: Crear Base de Datos Usuario

# Connect to PostgreSQL
psql -U postgres

-- Create monitoring user
CREATE USER postgres_exporter WITH PASSWORD 'exporter_password';

-- Grant privileges
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
GRANT pg_monitor TO postgres_exporter;

-- For older PostgreSQL versions
GRANT SELECT ON pg_stat_statements TO postgres_exporter;

Paso 2: Install Exporter

# Create user
sudo useradd --no-create-home --shell /bin/false postgres_exporter

# Download
cd /tmp
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.13.2/postgres_exporter-0.13.2.linux-amd64.tar.gz
tar -xvzf postgres_exporter-0.13.2.linux-amd64.tar.gz
cd postgres_exporter-0.13.2.linux-amd64

# Install
sudo cp postgres_exporter /usr/local/bin/
sudo chown postgres_exporter:postgres_exporter /usr/local/bin/postgres_exporter
sudo chmod +x /usr/local/bin/postgres_exporter

Paso 3: Crear Configuración

# Create connection string
sudo tee /etc/postgres_exporter/.pgpass > /dev/null << 'EOF'
localhost:5432:postgres:postgres_exporter:exporter_password
EOF

sudo chown postgres_exporter:postgres_exporter /etc/postgres_exporter/.pgpass
sudo chmod 600 /etc/postgres_exporter/.pgpass

# Or set environment variable
export DATA_SOURCE_NAME="postgresql://postgres_exporter:exporter_password@localhost:5432/postgres?sslmode=disable"

Paso 4: Systemd Servicio

sudo tee /etc/systemd/system/postgres_exporter.service > /dev/null << 'EOF'
[Unit]
Description=PostgreSQL Exporter
After=postgresql.service
Wants=postgresql.service

[Service]
User=postgres_exporter
Group=postgres_exporter
Type=simple
Environment="DATA_SOURCE_NAME=postgresql://postgres_exporter:exporter_password@localhost:5432/postgres?sslmode=disable"
ExecStart=/usr/local/bin/postgres_exporter \
  --web.listen-address=0.0.0.0:9187 \
  --extend.query-path=/etc/postgres_exporter/queries.yaml

Restart=always
RestartSec=10

StandardOutput=journal
StandardError=journal
SyslogIdentifier=postgres_exporter

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl daemon-reload
sudo systemctl enable postgres_exporter
sudo systemctl start postgres_exporter

Paso 5: Verificar Instalación

# Check metrics
curl http://localhost:9187/metrics | head -30

Prometheus Integración

MySQL Scrape Configuración

# /etc/prometheus/prometheus.yml

scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets:
          - 'localhost:9104'
        labels:
          instance: 'primary-mysql'
          environment: 'production'
      - targets:
          - '192.168.1.20:9104'
        labels:
          instance: 'replica-mysql'
          environment: 'production'
    scrape_interval: 30s
    scrape_timeout: 10s

PostgreSQL Scrape Configuración

scrape_configs:
  - job_name: 'postgres'
    static_configs:
      - targets:
          - 'localhost:9187'
        labels:
          instance: 'primary-postgres'
          environment: 'production'
      - targets:
          - '192.168.1.30:9187'
        labels:
          instance: 'standby-postgres'
          environment: 'production'
    scrape_interval: 30s
    scrape_timeout: 10s

Reload Prometheus

curl -X POST http://localhost:9090/-/reload

Base de Datos Métricas

MySQL Key Métricas

# Connections
mysql_global_status_threads_connected

# Query performance
mysql_global_status_questions
mysql_global_status_slow_queries

# Replication
mysql_slave_status_slave_io_running
mysql_slave_status_slave_sql_running
mysql_slave_status_seconds_behind_master

# InnoDB
mysql_global_status_innodb_buffer_pool_pages_free
mysql_global_status_innodb_buffer_pool_pages_total
mysql_global_status_innodb_rows_read
mysql_global_status_innodb_rows_written

# Database size
mysql_info_schema_table_size_bytes

PostgreSQL Key Métricas

# Connections
pg_stat_activity_count

# Query statistics
pg_stat_statements_calls
pg_stat_statements_mean_exec_time
pg_stat_statements_max_exec_time

# Replication
pg_stat_replication_state
pg_stat_replication_write_lag
pg_stat_replication_flush_lag
pg_stat_replication_replay_lag

# Cache effectiveness
pg_stat_user_tables_heap_blks_hit
pg_stat_user_tables_heap_blks_read

# Database size
pg_database_size_bytes

Grafana Paneles

MySQL Panel Panels

# Connections over time
rate(mysql_global_status_threads_created[5m])

# Query rate
rate(mysql_global_status_questions[5m])

# Slow queries
rate(mysql_global_status_slow_queries[5m])

# Replication lag
mysql_slave_status_seconds_behind_master

# Buffer pool usage
mysql_global_status_innodb_buffer_pool_pages_free / 
mysql_global_status_innodb_buffer_pool_pages_total * 100

PostgreSQL Panel Panels

# Active connections
pg_stat_activity_count

# Query latency p95
histogram_quantile(0.95, pg_stat_statements_mean_exec_time)

# Replication lag
pg_stat_replication_replay_lag

# Cache hit ratio
pg_stat_user_tables_heap_blks_hit / 
(pg_stat_user_tables_heap_blks_hit + pg_stat_user_tables_heap_blks_read) * 100

# Connections per application
count by (usename) (pg_stat_activity_count)

Alerting Rules

MySQL Alertas

groups:
  - name: mysql_alerts
    rules:
      - alert: MySQLDown
        expr: mysql_up == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "MySQL instance {{ $labels.instance }} is down"

      - alert: MySQLTooManyConnections
        expr: mysql_global_status_threads_connected > 80
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL {{ $labels.instance }} has {{ $value }} connections"

      - alert: MySQLReplicationLag
        expr: mysql_slave_status_seconds_behind_master > 60
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL replication lag is {{ $value }} seconds"

      - alert: MySQLSlowQueries
        expr: rate(mysql_global_status_slow_queries[5m]) > 0.1
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL has {{ $value }} slow queries per second"

PostgreSQL Alertas

groups:
  - name: postgres_alerts
    rules:
      - alert: PostgreSQLDown
        expr: pg_up == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL instance {{ $labels.instance }} is down"

      - alert: PostgreSQLTooManyConnections
        expr: pg_stat_activity_count > 100
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL has {{ $value }} connections"

      - alert: PostgreSQLReplicationLag
        expr: pg_stat_replication_replay_lag > 10000000000
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL replication lag is {{ $value | humanize }}bytes"

      - alert: PostgreSQLSlowQueries
        expr: pg_stat_statements_max_exec_time > 30000
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL slow query detected: {{ $value }}ms"

Rendimiento Tuning

Consulta Optimización

# For MySQL - disable expensive collectors if needed
--no-collector.binlog_size
--no-collector.info_schema.query_response_time

# For PostgreSQL - limit query collection
--extend.query-path=/etc/postgres_exporter/queries.yaml

Scrape Interval Tuning

# Reduce for less frequent checks
scrape_configs:
  - job_name: 'mysql'
    scrape_interval: 60s
    scrape_timeout: 30s

Solución de Problemas

Verificar Exporter Estado

# MySQL
curl http://localhost:9104/metrics | grep mysql_up

# PostgreSQL
curl http://localhost:9187/metrics | grep pg_up

# Check connectivity
mysql -u exporter -p'password' -e "SELECT VERSION();"
psql -U postgres_exporter -d postgres -c "SELECT VERSION();"

Debug Issues

# View exporter logs
journalctl -u mysqld_exporter -f
journalctl -u postgres_exporter -f

# Test exporter directly
./mysqld_exporter --collect.global_status --web.listen-address=:9999

# Check query response time
mysql -u exporter -p'password' -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';"

Conclusión

Base de Datos exporters provide essential insights into application data layer rendimiento. By following Esta guía, you've deployed comprehensive database monitoreo for both MySQL and PostgreSQL. Focus on collecting metrics that align con su SLOs, setting alert thresholds based on operational baselines, and using Grafana dashboards to communicate database health a su team. Base de Datos observability es crítico for preventing outages and maintaining application rendimiento.