Тюнінг продуктивності PostgreSQL (shared_buffers, work_mem, effective_cache_size)

Тюнінг продуктивності PostgreSQL (shared_buffers, work_mem, effective_cache_size)

Стандартна конфігурація PostgreSQL — shared_buffers = 128MB, work_mem = 4MB — написана для запуску на будь-якому залізі 2005 року. На сучасному сервері з 32–128 ГБ ОЗУ вона залишає 95% пам'яті невикористаною. Правильний тюнінг — це не просто «встановити більші цифри», а розуміння того, як PostgreSQL використовує пам'ять і взаємодіє з ОС.

Як PostgreSQL використовує пам'ять

shared_buffers — спільний кеш сторінок бази даних для всіх процесів. Сторінки, які PostgreSQL прочитав з диска, потрапляють сюди. Якщо сторінка в shared_buffers — наступний запит отримує її з пам'яті.

work_mem — пам'ять для кожної операції сортування або hash join у межах одного запиту. Якщо запит має 3 вузли сортування, він може займати 3 × work_mem. При 100 паралельних з'єднаннях з важкими запитами споживання може бути 100 × 3 × work_mem.

effective_cache_size — не реально виділена пам'ять, а підказка планувальнику про те, скільки пам'яті доступно для кеширування (ОС + shared_buffers). Впливає на вибір між index scan та seq scan.

maintenance_work_mem — для VACUUM, CREATE INDEX, ALTER TABLE. Не впливає на звичайні запити.

Рекомендовані значення

Для виділеного сервера PostgreSQL з 32 ГБ ОЗУ:

# postgresql.conf

# 25% ОЗУ для виділеного сервера
shared_buffers = 8GB

# Залишок ОЗУ мінус shared_buffers та система
effective_cache_size = 24GB  # shared_buffers + OS page cache

# Увага: work_mem множиться на кількість паралельних операцій
# Для OLTP з короткими запитами: 4-16 МБ
# Для аналітики з GROUP BY / ORDER BY: 64-256 МБ
work_mem = 16MB

# Для VACUUM та CREATE INDEX — збільшувати тільки під час обслуговування
maintenance_work_mem = 2GB

# WAL буфер — 64 МБ достатньо для більшості навантажень
wal_buffers = 64MB

Інструмент pgtune (https://pgtune.leopard.in.ua) генерує стартові значення за типом навантаження (OLTP, DW, Web).

Налаштування планувальника

# Модель вартості для SSD (за замовчуванням розраховується на HDD)
random_page_cost = 1.1    # SSD: 1.1, HDD: 4.0 (за замовчуванням)
seq_page_cost    = 1.0    # звичайно не змінюють

# Включити паралельні запити (PostgreSQL 9.6+)
max_parallel_workers_per_gather = 4   # до кількості CPU cores
max_parallel_workers = 8
parallel_tuple_cost  = 0.1
parallel_setup_cost  = 1000.0

# Статистика: більше = кращий планувальник вибирає індекси
default_statistics_target = 100  # за замовчуванням 100, для складних стовпців — до 500

Для конкретних стовпців з перекошеним розподілом даних:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

Checkpoint та WAL

# Контрольні точки трапляються рідше — менше спайків I/O
checkpoint_completion_target = 0.9  # розтягнути checkpoint на 90% інтервалу
checkpoint_timeout = 15min          # максимальний інтервал (за замовчуванням 5min)
max_wal_size = 4GB                  # при інтенсивному записі — збільшити

# fsync вимикати НЕМОЖНА у продакшені (втрата даних при відключенні живлення)
fsync = on

# synchronous_commit = off — дозволити втрату до 60ms транзакцій
# Доречно для некритичних даних, не для фінансових
synchronous_commit = on

Тюнінг для конкретного випадку: сортування

Запит повільно виконує ORDER BY на великій таблиці — сортування відбувається через тимчасовий файл на диску:

-- Перевіряємо: чи є temp files у запиті
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM events
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 100;

-- Якщо у виводі "Sort Method: external merge  Disk: 45678kB" — потрібен індекс або більше work_mem

-- Створюємо покриваючий індекс
CREATE INDEX CONCURRENTLY idx_events_user_date
  ON events(user_id, created_at DESC)
  INCLUDE (id, event_type, payload);
-- INCLUDE додає дані в індекс, запит працює без звернення до таблиці

Моніторинг hit rate

-- Ефективність shared_buffers: має бути > 99% для OLTP
SELECT
    sum(heap_blks_hit)  AS heap_hit,
    sum(heap_blks_read) AS heap_read,
    round(
        sum(heap_blks_hit)::numeric /
        nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100,
        2
    ) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- Аналогічно для індексів
SELECT
    round(
        sum(idx_blks_hit)::numeric /
        nullif(sum(idx_blks_hit) + sum(idx_blks_read), 0) * 100,
        2
    ) AS index_hit_ratio
FROM pg_statio_user_indexes;

Якщо cache_hit_ratio < 99% — або shared_buffers малий, або робочий набір даних не вміщується в пам'ять вообще.

Розширення pg_buffercache

CREATE EXTENSION pg_buffercache;

-- Що зараз знаходиться в shared_buffers?
SELECT c.relname,
       count(*) AS buffers,
       round(count(*) * 8192.0 / 1024 / 1024, 1) AS size_mb,
       round(count(*) * 100.0 / (SELECT count(*) FROM pg_buffercache), 1) AS pct
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = c.relfilenode
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 20;

Параметри з'єднань

max_connections = 200   # для прямих з'єднань; з PgBouncer можна менше

# Розмір стека для кожного backend-процесу
# Не зменшувати нижче 100KB
# Зменшення max_stack_depth не рекомендується

При використанні PgBouncer у режимі транзакцій: max_connections = 50-100 на PostgreSQL (реальні бекенди), тисячі клієнтів підключаються до PgBouncer.

Порядок застосування змін

-- Застосувати без перезавантаження
SELECT pg_reload_conf();

-- Перевірити, що зміна вступила в силу
SHOW work_mem;

Профіль навантаження впливає на тюнінг

Web OLTP (багато коротких транзакцій, INSERT/SELECT за PK): малий work_mem (4–16 МБ), великий shared_buffers, max_connections через PgBouncer.

Аналітичні запити (GROUP BY, window functions, великі JOIN): великий work_mem (256 МБ – 1 ГБ), max_parallel_workers_per_gather = 4+, можна зменшити max_connections.

Змішане навантаження: PgBouncer для OLTP-з'єднань + окрема реплика для аналітики з іншим work_mem.