Настройка производительности и мониторинг PostgreSQL

Настройка производительности и мониторинг PostgreSQL

Главная идея

Как это выглядит на практике

1. Новый продакшн-сервер: 32GB RAM, SSD, PostgreSQL только что установлен. Дефолтные настройки: shared_buffers = 128MB (0.4% RAM). Первый же нагрузочный тест показывает высокую latency.
2. Запускаешь pg_stat_statements: SELECT ... ORDER BY total_exec_time DESC. Видишь что 5 запросов занимают 80% времени БД. Один из них — простой SELECT с WHERE по indexed колонке, но планировщик выбирает Seq Scan.
3. EXPLAIN (ANALYZE, BUFFERS): планировщик считает Index Scan слишком дорогим. Причина: random_page_cost = 4.0 (дефолт для HDD). Устанавливаешь random_page_cost = 1.1 для SSD. Планировщик переключается на Index Scan, latency падает с 200ms до 12ms.
4. Устанавливаешь shared_buffers = 8GB (25% RAM), effective_cache_size = 24GB. Cache hit ratio поднимается с 85% до 99%. Многие запросы теперь читают данные из памяти, не с диска.
5. Аналитические запросы с GROUP BY + ORDER BY падают с Memory exceeded errors. work_mem = 4MB (дефолт) — PostgreSQL сбрасывает Sort на диск. SET work_mem = '128MB' для аналитических запросов. Запросы ускоряются в 10 раз.
6. Мониторинг pg_stat_user_tables: таблица events с n_dead_tup = 50M. Autovacuum запускается слишком редко из-за дефолтного scale_factor = 0.2 (20%). Устанавливаешь для этой таблицы autovacuum_vacuum_scale_factor = 0.01 — autovacuum теперь запускается при 1% dead tuples.
7. Приложение открывает 500 соединений при 100 одновременных пользователях. PostgreSQL тратит 2.5GB только на стеки backend-процессов. Устанавливаешь PgBouncer в transaction mode: 500 клиентских соединений → 20 реальных соединений к PostgreSQL. Memory footprint снижается на 2GB.
8. Долгая аналитическая транзакция держит xmin snapshot — VACUUM не может убирать dead tuples. n_dead_tup растёт. Добавляешь statement_timeout = '10min' и idle_in_transaction_session_timeout = '30s' — защита от долгих транзакций.
9. VACUUM FULL для дефрагментации большой таблицы — кто-то запустил в продакшне. Таблица заблокирована на 20 минут. Объясняешь команде: для дефрагментации без блокировки — pg_repack. Добавляешь правило: VACUUM FULL только на staging, никогда в продакшне.
10. Добавляешь log_min_duration_statement = 1000 — PostgreSQL логирует все запросы медленнее 1 секунды. Через день анализируешь логи: находишь 3 новых медленных запроса (N+1 из ORM, missing index, cartesian join).
11. Обнаруживаешь индекс на таблице заказов: idx_scan = 0 за последние 30 дней, занимает 8GB. Это индекс на updated_at, добавленный год назад для несуществующего запроса. Удаляешь: DROP INDEX CONCURRENTLY. INSERT/UPDATE ускоряются на 15%.
12. Составляешь продакшн-чеклист: shared_buffers, random_page_cost, pg_stat_statements, PgBouncer, autovacuum tuning для активных таблиц, мониторинг cache_hit_ratio и n_dead_tup, log_min_duration_statement. Документируешь в runbook команды.

Примеры кода

Ключевые параметры postgresql.conf

-- Файл: /etc/postgresql/16/main/postgresql.conf
-- Или: ALTER SYSTEM SET parameter = value;  (запись в postgresql.auto.conf)

-- ═══ ПАМЯТЬ ═══
-- Сервер: 32GB RAM
shared_buffers = '8GB'          -- 25% RAM; требует перезапуска
effective_cache_size = '24GB'   -- 75% RAM; только подсказка планировщику
work_mem = '32MB'               -- на Sort/Hash операцию; умножается на connections × operations
maintenance_work_mem = '1GB'    -- для VACUUM, CREATE INDEX, REINDEX

-- ═══ I/O ═══
random_page_cost = 1.1          -- SSD: 1.1 (дефолт 4.0 для HDD)
                                -- КРИТИЧНО: без этого планировщик избегает Index Scan!
effective_io_concurrency = 200  -- SSD: 200+; HDD: 2

-- ═══ WAL / CHECKPOINT ═══
max_wal_size = '4GB'            -- реже checkpoint = меньше I/O spike (но дольше recovery)
checkpoint_completion_target = 0.9  -- растянуть checkpoint на 90% интервала
wal_compression = on            -- сжатие WAL (CPU vs I/O trade-off)

-- ═══ ПАРАЛЛЕЛИЗМ ═══
max_worker_processes = 8        -- число фоновых воркеров
max_parallel_workers = 8        -- параллельные запросы
max_parallel_workers_per_gather = 4  -- воркеры на один запрос

-- ═══ CONNECTIONS ═══
max_connections = 100           -- с PgBouncer достаточно 50-100
                                -- без PgBouncer: 200-300 максимум

-- ═══ AUTOVACUUM ═══
autovacuum_max_workers = 5      -- больше воркеров для активных БД
autovacuum_vacuum_scale_factor = 0.05    -- триггер при 5% dead tuples (дефолт 20%)
autovacuum_analyze_scale_factor = 0.02   -- ANALYZE при 2% изменений

-- ═══ ЛОГИРОВАНИЕ ═══
log_min_duration_statement = 1000   -- логировать запросы > 1 секунды
log_checkpoints = on                -- логировать checkpoint события

-- Применить hot-reload параметры без перезапуска:
SELECT pg_reload_conf();

-- Проверить текущие значения:
SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'random_page_cost', 'effective_cache_size');

pg_stat_statements: находим медленные запросы

-- Активация (один раз в postgresql.conf):
-- shared_preload_libraries = 'pg_stat_statements'
-- Затем:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Топ-15 запросов по суммарному времени (самые дорогие по CPU)
SELECT
  round(total_exec_time::numeric / calls, 2)    AS avg_ms,
  calls,
  round(total_exec_time::numeric / 1000, 2)     AS total_sec,
  round(rows::numeric / calls, 0)               AS avg_rows,
  round(100 * total_exec_time /
    sum(total_exec_time) OVER (), 2)            AS pct_of_total,
  left(query, 100)                              AS query
FROM pg_stat_statements
WHERE calls > 100
  AND query NOT LIKE '%pg_%'   -- исключаем системные запросы
ORDER BY total_exec_time DESC
LIMIT 15;

-- Запросы с высоким I/O (много страниц читается с диска)
SELECT
  round(total_exec_time::numeric / calls, 2)  AS avg_ms,
  calls,
  round((shared_blks_read + shared_blks_dirtied)::numeric / calls, 0) AS blks_per_call,
  left(query, 100) AS query
FROM pg_stat_statements
WHERE calls > 50
  AND (shared_blks_read + shared_blks_dirtied) / calls > 1000
ORDER BY (shared_blks_read + shared_blks_dirtied) DESC
LIMIT 10;

-- Cache hit ratio для отдельных запросов
SELECT
  left(query, 80) AS query,
  round(shared_blks_hit * 100.0 /
    NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct,
  calls,
  round(total_exec_time / calls, 1) AS avg_ms
FROM pg_stat_statements
WHERE calls > 100
  AND (shared_blks_hit + shared_blks_read) > 0
ORDER BY cache_hit_pct ASC   -- самые cache-unfriendly запросы
LIMIT 10;

-- Сброс статистики (в начале нагрузочного теста)
SELECT pg_stat_statements_reset();

Мониторинг bloat, autovacuum и cache hit

-- Глобальный cache hit ratio (должен быть > 99% в продакшне)
SELECT
  sum(blks_hit)  AS total_hits,
  sum(blks_read) AS total_reads,
  round(sum(blks_hit) * 100.0 / NULLIF(sum(blks_hit) + sum(blks_read), 0), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();

-- Таблицы с bloat: n_dead_tup / n_live_tup > 10% — проблема
SELECT
  schemaname,
  relname AS table_name,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)) AS total_size,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
  OR n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0) > 10
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Настройка autovacuum для конкретной таблицы (активная таблица)
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- 1% вместо 20%
  autovacuum_analyze_scale_factor = 0.005, -- 0.5% вместо 10%
  autovacuum_vacuum_cost_delay = 2         -- ms (менее агрессивное throttling)
);

-- Ручной VACUUM если autovacuum не справляется
VACUUM ANALYZE orders;              -- без блокировки, убирает dead tuples
-- VACUUM FULL orders;             -- НЕ ИСПОЛЬЗУЙ в продакшне! Полная блокировка
-- Используй pg_repack для дефрагментации без блокировки:
-- pg_repack -t orders -d mydb

-- Unused indexes: индексы, которые не используются (занимают место и замедляют INSERT)
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS times_used
FROM pg_stat_user_indexes
JOIN pg_index ON indexrelid = pg_stat_user_indexes.indexrelid
WHERE idx_scan = 0
  AND NOT indisprimary
  AND NOT indisunique
ORDER BY pg_relation_size(indexrelid) DESC;

Что происходит под капотом

• Shared buffers работает как LRU кеш страниц данных. При чтении: если страница в shared_buffers (shared_blks_hit) — быстро. Если нет (shared_blks_read) — читается с диска в shared_buffers. 25% RAM оптимально: OS page cache тоже кеширует, двойное кеширование выше ~40% RAM контрпродуктивно.
• random_page_cost vs seq_page_cost: планировщик использует эти значения для оценки стоимости Index Scan (много случайных чтений) vs Seq Scan (последовательные чтения). На HDD: случайное чтение в 50-100x медленнее последовательного → random_page_cost = 4.0. На SSD: в 2-4x → 1.1. Неправильное значение → планировщик выбирает Seq Scan вместо Index Scan.
• work_mem используется для in-memory Sort и Hash Join. Если данных больше чем work_mem — PostgreSQL создаёт temporary files на диске (Sort Disk в EXPLAIN). Один сложный запрос может использовать несколько work_mem (несколько Sort/Hash узлов). При 200 соединениях и work_mem=256MB теоретически 51GB — поэтому глобально ставь консервативно.
• Autovacuum threshold: запуск VACUUM при: n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × n_live_tup. С дефолтными настройками (scale_factor = 0.2) для таблицы с 10M строк — VACUUM запускается только при 2M dead tuples. Для активных таблиц снижай scale_factor до 0.01-0.05.
• PgBouncer pooling modes: session mode — соединение закреплено за клиентом до disconnect (как без пулера). Transaction mode — соединение возвращается в пул после каждой транзакции. Statement mode — после каждого statement (несовместимо с транзакциями). Transaction mode: несовместим с LISTEN/NOTIFY, SET в транзакции, временными таблицами — учитывай при использовании с Rails/ActiveRecord.
• pg_stat_statements нормализует запросы: WHERE id = 1 и WHERE id = 2 → WHERE id = $1. Это позволяет агрегировать статистику по всем вызовам одного запроса. calls, total_exec_time, rows, shared_blks_hit/read/written — основные метрики.
• Checkpoint I/O spike: при достижении max_wal_size PostgreSQL выполняет checkpoint — сбрасывает все dirty pages на диск. Это может вызвать I/O spike. checkpoint_completion_target = 0.9 растягивает запись на 90% checkpoint interval — сглаживает нагрузку. max_wal_size больше = реже checkpoint, но дольше восстановление после crash.
• XID wraparound monitoring: SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age DESC. Если age > 1.5 миллиарда — опасная зона, PostgreSQL запустит aggressive autovacuum. Если age > 2 миллиарда — PostgreSQL перейдёт в read-only режим для защиты данных. Мониторь этот показатель в production.

Термины урока

Мини-разбор реальной ситуации

API сервис: 32GB RAM, SSD, 200 соединений, PostgreSQL с дефолтными настройками. Симптомы: p99 latency 800ms, CPU 30%, высокое disk I/O. Диагностика: shared_buffers = 128MB (0.4% RAM, cache_hit = 78%), random_page_cost = 4.0 (планировщик выбирает Seq Scan), work_mem = 4MB (Sort Disk на каждом запросе). Исправления: shared_buffers=8GB, random_page_cost=1.1, work_mem=64MB, PgBouncer (200→20 реальных соединений). Результат: cache_hit = 99%, p99 latency 80ms (10x улучшение), disk I/O снизился на 90%, memory footprint уменьшился на 1.5GB. Ни одной строки кода не изменено.