Настройка мониторинга производительности базы данных (pg_stat_statements/slow query log)

Настройка мониторинга производительности базы данных (pg_stat_statements/slow query log)

Мониторинг базы данных — это не дашборд ради дашборда. Это система, которая за 30 секунд даёт ответ на вопрос: «Что именно замедлилось и почему». Без правильно настроенного мониторинга при деградации начинается угадывание — смотрят не туда, теряют часы.

PostgreSQL: pg_stat_statements

Расширение pg_stat_statements накапливает статистику по каждому уникальному запросу:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max          = 10000   # сколько уникальных запросов хранить
pg_stat_statements.track        = all     # top — только верхнеуровневые, all — вложенные тоже
pg_stat_statements.track_utility = off    # не трекать COPY, VACUUM и т.п.

После перезапуска PostgreSQL:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Полезные запросы для анализа:

-- Запросы, съедающие больше всего суммарного времени
SELECT
    left(query, 120)                                         AS query,
    calls,
    round(total_exec_time::numeric / 1000, 1)               AS total_sec,
    round(mean_exec_time::numeric, 1)                        AS avg_ms,
    round(stddev_exec_time::numeric, 1)                      AS stddev_ms,
    round(rows::numeric / nullif(calls, 0), 0)               AS rows_per_call
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  AND calls > 10
ORDER BY total_exec_time DESC
LIMIT 20;

-- Запросы с большим разбросом — нестабильные (иногда быстро, иногда медленно)
SELECT left(query, 120) AS query, calls,
       round(mean_exec_time::numeric, 1) AS avg_ms,
       round(stddev_exec_time::numeric, 1) AS stddev_ms,
       round(stddev_exec_time / nullif(mean_exec_time, 0) * 100, 1) AS cv_pct
FROM pg_stat_statements
WHERE calls > 100
ORDER BY cv_pct DESC
LIMIT 10;

PostgreSQL: auto_explain

pg_stat_statements показывает что медленно, auto_explain — почему: логирует план выполнения для медленных запросов автоматически.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = 500   # мс: логировать запросы > 500ms
auto_explain.log_analyze      = true  # реальное время выполнения, не только оценка
auto_explain.log_buffers      = true  # сколько страниц hit/read
auto_explain.log_format       = json  # для структурированного парсинга
auto_explain.log_nested_statements = true

Вывод попадает в основной лог PostgreSQL (/var/log/postgresql/). Удобно анализировать через pgBadger.

MySQL/MariaDB: slow query log

# /etc/mysql/conf.d/slow-log.cnf
[mysqld]
slow_query_log           = ON
slow_query_log_file      = /var/log/mysql/slow.log
long_query_time          = 1       # секунды
log_queries_not_using_indexes = ON
min_examined_row_limit   = 1000   # игнорировать запросы, просматривающие < 1000 строк
log_slow_rate_limit      = 100    # MariaDB: логировать каждый 100-й такой запрос

Включить без перезапуска:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Анализ через Percona pt-query-digest:

# Топ запросов за последний час slow.log
pt-query-digest \
  --since="1h ago" \
  --limit 20 \
  --output report \
  /var/log/mysql/slow.log

# Вывод: для каждого уникального запроса — count, avg/max time, rows examined

Prometheus + Grafana: метрический мониторинг

Для PostgreSQL — postgres_exporter:

# docker-compose.yml
services:
  postgres_exporter:
    image: quay.io/prometheuscommunity/postgres-exporter:latest
    environment:
      DATA_SOURCE_NAME: "postgresql://monitoring:password@postgres:5432/mydb?sslmode=disable"
    ports:
      - "9187:9187"

# prometheus.yml — scrape config
- job_name: postgresql
  static_configs:
    - targets: ['postgres_exporter:9187']
  scrape_interval: 15s

Ключевые метрики и алерты:

# alerting rules
groups:
  - name: postgresql
    rules:
      - alert: PostgreSQLSlowQueries
        expr: rate(pg_stat_statements_total_exec_time_seconds_total[5m]) > 10
        for: 2m
        annotations:
          summary: "Высокое суммарное время запросов"

      - alert: PostgreSQLHighConnections
        expr: pg_stat_activity_count > pg_settings_max_connections * 0.8
        for: 1m
        annotations:
          summary: "Использовано > 80% соединений"

      - alert: PostgreSQLReplicationLag
        expr: pg_replication_lag > 30
        for: 1m
        annotations:
          summary: "Реплика отстаёт на {{ $value }} секунд"

Для MySQL — mysqld_exporter:

mysqld_exporter \
  --config.my-cnf=/etc/mysql/monitoring.cnf \
  --collect.global_status \
  --collect.info_schema.innodb_metrics \
  --collect.perf_schema.eventsstatements \
  --collect.perf_schema.eventsstatementssum

pgBadger: анализ логов PostgreSQL

# Установка
apt-get install pgbadger

# Анализ за сутки
pgbadger \
  --format=stderr \
  --outfile=/var/www/reports/pgbadger_$(date +%Y%m%d).html \
  /var/log/postgresql/postgresql-2025-01-15_*.log

# Генерировать ежедневно через cron
0 6 * * * /usr/bin/pgbadger --incremental --outdir /var/www/reports/pgbadger /var/log/postgresql/postgresql.log

pgBadger строит HTML-отчёт: топ медленных запросов, число запросов по времени суток, lock events, checkpoint activity.

Grafana дашборды

Готовые дашборды из Grafana Dashboard Repository:

• PostgreSQL: ID 9628 (postgres_exporter dashboard) — connections, transactions/sec, cache hit rate, replication lag
• MySQL: ID 7362 (MySQL Overview) — InnoDB buffer pool, queries/sec, slow queries
• pgBouncer: ID 13474

Импорт через Grafana UI: Dashboards → Import → ввести ID.

Что мониторить обязательно

Настройка полного стека (postgres_exporter + Prometheus + Grafana + алерты в Telegram) занимает 1–2 дня.