Тюнінг PostgreSQL: shared_buffers, work_mem, effective_cache_size

Тюнінг PostgreSQL: shared_buffers, work_mem, effective_cache_size

Дефолтна конфігурація PostgreSQL розрахована на скромне залізо і неефективна на сучасних серверах. shared_buffers = 128MB, work_mem = 4MB — ці параметри залишають 95% пам'яті невикористаною. Наприклад, сервер з 32 ГБ RAM та дефолтними налаштуваннями використовує лише 128 МБ для кешу — база простоює, а запити гальмують. Правильний тюнінг дає приріст продуктивності щонайменше 30% і знижує навантаження на дискову підсистему. Ми виконуємо налаштування під ваш профіль: OLTP, аналітика або змішане навантаження. Досвід команди — 50+ успішних проєктів за 10+ років, гарантія результату. Компанія працює 5 років на ринку. Оцінимо ваш проект — пишіть.

Основні параметри пам'яті

Як налаштувати shared_buffers для OLTP?

Загальний кеш сторінок бази для всіх процесів. Для виділеного сервера — 25% RAM. На сервері з 32 ГБ це 8 ГБ. Більше 25% може призвести до конфлікту з кешем ОС. Перевірити, чи вистачає shared_buffers, можна по hit rate: якщо cache_hit_ratio < 99% — або shared_buffers малий, або робочий набір не вміщається в пам'ять. Використовуйте pg_buffercache, щоб побачити, які таблиці та індекси займають буфер. Збільшуйте shared_buffers до 25% RAM, але не більше 8 ГБ на Linux через архітектурні обмеження.

Що робити при низькому cache hit ratio

Якщо cache_hit_ratio нижче 99%, це сигнал до тюнінгу. Перевірте shared_buffers — можливо, потрібно збільшити. Також може допомогти додавання індексів. Для аналітичних запитів розгляньте збільшення work_mem. Використовуйте запит з code-блоку для перевірки hit rate.

Чому маленьке work_mem часто краще великого

work_mem — пам'ять для кожної сортування / hash join в одному запиті. Якщо запит має 3 sort node, він може зайняти 3 × work_mem. При 100 паралельних з'єднаннях з важкими запитами споживання може бути 100 × 3 × work_mem. Завелике значення викличе swap. Типова помилка — встановити 64 МБ глобально, тоді як 100 з'єднань з 4 сортуваннями = 100 × 4 × 64 МБ = 25,6 ГБ. Починайте з 16 МБ, збільшуйте для конкретних запитів через SET LOCAL. Для OLTP-навантаження високе work_mem веде до перевитрати пам'яті та падіння продуктивності через свопінг. Наша методика: аналізуємо плани запитів, виявляємо сортування на диску, збільшуємо work_mem тільки для проблемних запитів.

effective_cache_size: проста підказка планувальнику

Підказка планувальнику про доступний кеш ОС + shared_buffers. Для сервера з 32 ГБ: 24 ГБ. Впливає на вибір між index scan і seq scan. Не резервує пам'ять, але критично важливий для правильного вибору плану. Більш детально — в офіційній документації PostgreSQL. Рекомендована установка — 75% від RAM.

maintenance_work_mem для операцій обслуговування

Для VACUUM, CREATE INDEX, ALTER TABLE. Підвищуйте тільки під час обслуговування. Значення 2 ГБ підходить для більшості задач. Не тримайте високим постійно — це збереже пам'ять.

Планувальник та моніторинг

Вартісні параметри та паралельні запити

# Cost model for SSD
random_page_cost = 1.1 # SSD: 1.1, HDD: 4.0 (default)
seq_page_cost = 1.0
# Parallel queries (PostgreSQL 9.6+)
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000.0

Моніторинг hit rate та буферного кешу

-- Cache hit ratio
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;

-- Buffer usage details
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
SELECT c.relname,
       count(*) AS buffers,
       round(count(*) * 8192.0 / 1024 / 1024, 1) AS size_mb
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = c.relfilenode
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 20;

Якщо cache_hit_ratio < 99% — потрібен тюнінг shared_buffers або додавання індексу.

Тюнінг під навантаження: OLTP, аналітика, змішане

Checkpoint і WAL

checkpoint_completion_target = 0.9 checkpoint_timeout = 15min max_wal_size = 4GB fsync = on synchronous_commit = on 

Профілі навантаження: порівняння налаштувань

Практичні поради

Як оптимізувати сортування: приклад

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

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); 

Типові помилки при налаштуванні PostgreSQL

Застосування змін

Після зміни параметрів виконайте SELECT pg_reload_conf(); для застосування.

Висновок

Правильне налаштування PostgreSQL дає значний приріст продуктивності та економію на інфраструктурі. Ми гарантуємо покращення не менше 30% (у 1.3 рази краще за дефолт) або безкоштовно переглянемо конфігурацію протягом 30 днів. Вартість послуг — від 5000 грн за базовий аудит. Економія ресурсів — до $200 на місяць за рахунок зниження навантаження на диск. Зв'яжіться з нами для консультації та замовте професійний тюнінг PostgreSQL під ключ. Оцінимо ваш проект за 2 дні.