Налаштування моніторингу продуктивності бази даних (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   # ms: логувати запити > 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 дні.