41.1 shared_buffers: общий кеш на весь кластер
Один блок разделяемой памяти, выделяемый при старте сервера. Сюда кладутся страницы таблиц и индексов, чтобы не читать их с диска повторно - это тот самый буферный кеш, чьи состояния мы разбирали в части про журнал.
Ключевое свойство: память выделяется один раз и общая для всех backend'ов. Типичная отправная точка - около четверти ОЗУ машины. Больше не всегда лучше: под буферным кешем PostgreSQL работает ещё и страничный кеш операционной системы, и отдавать всю память одному слою смысла нет - они начнут дублировать друг друга.
41.2 work_mem: лимит на одну операцию, а не на запрос
Самый коварный параметр, потому что его легко недооценить в разы.
work_mem - это лимит памяти на одну операцию: сортировку
(ORDER BY), хеш-соединение, хеш-агрегацию. Ключевое слово - «одну».
В одном запросе таких операций бывает несколько: сортировка плюс
хеш-джойн плюс агрегация - и каждая берёт до work_mem независимо.
Теперь умножь это на число одновременных запросов, и получишь
реальный аппетит сервера:
пиковый расход ≈ work_mem × (операций в запросе) × (одновременных запросов)
Поэтому work_mem = 1GB на сервере с сотней коннектов - это заявка
на OOM, а не оптимизация: сотня запросов по несколько операций каждый
легко затребуют десятки гигабайт. Правильная тактика - держать
глобальный work_mem скромным, а под конкретный тяжёлый отчёт
поднимать его точечно через SET work_mem в его сессии.
41.2.1 Копнуть глубже: что происходит при нехватке work_mem
Операция при нехватке памяти не падает - она «проливается» на диск. Сортировка становится внешней (external merge), хеш разбивается на пакеты и часть уходит во временные файлы. Запрос отработает, но медленно: к процессорной работе добавился диск.
Сигнал виден в двух местах. В плане:
Sort Method: external merge Disk: 24480kB. И в счётчиках:
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp
FROM pg_stat_database WHERE temp_files > 0;
Растущий temp_files под нагрузкой - прямая подсказка, что
work_mem мал для текущих запросов. Параметр log_temp_files = 0
пишет каждый пролив в лог сервера, с именем файла и размером.
41.3 maintenance_work_mem: для обслуживания
Отдельный лимит памяти для операций обслуживания: VACUUM,
CREATE INDEX, ALTER TABLE. Логика противоположна work_mem: этих
операций мало, их не запускают сотнями параллельно от клиентов,
поэтому лимит можно ставить заметно выше.
Больше памяти здесь - прямая выгода. Vacuum собирает больше мёртвых идентификаторов за один проход, build индекса делает больше работы в памяти, не проливаясь на диск. На сервере, где регулярно строят индексы или чистят большие таблицы, поднятый maintenance_work_mem заметно ускоряет обслуживание.
41.4 effective_cache_size: подсказка, не аллокация
Этот параметр ничего не выделяет, и в этом главное недоразумение вокруг него. Он лишь сообщает планировщику, сколько суммарно памяти под кеш доступно в системе - shared_buffers плюс страничный кеш ОС.
Планировщик использует это число в стоимостной модели: по нему он оценивает, насколько вероятно, что нужные страницы уже в памяти, а не на диске. Высокий effective_cache_size делает index scan дешевле в глазах планировщика (повторные обращения к индексу скорее попадут в кеш), и тот охотнее выбирает индекс вместо seq scan.
Отсюда практическое следствие: заниженный effective_cache_size отпугивает планировщик от индексов на ровном месте. Сервер с большим объёмом памяти, но дефолтным effective_cache_size, может необъяснимо предпочитать seq scan - потому что планировщик «думает», что кеша мало.
41.5 Почему дефолты осторожны
Дефолтные значения PostgreSQL рассчитаны на то, чтобы сервер запустился где угодно, включая слабую виртуалку. Они намеренно скромны, а не оптимальны. Оставить их на проде - частый анти-паттерн: сервер с гигабайтами памяти работает так, будто памяти у него минимум.
Но и обратная крайность - выкрутить всё в максимум - роняет сервер через work_mem и OOM. Осознанная настройка идёт от механизма: общий кеш под четверть памяти, work_mem скромный (его умножит конкурентность), maintenance_work_mem высокий (операций мало), effective_cache_size честно отражает реальный объём кеша. Как находить запросы, которым реально не хватает памяти - представления из прошлой главы и memory-config.
Уроки в sandbox
lab-41.1. Default против tuned: work_mem и проливы на диск
Заставим сортировку пролиться на диск при маленьком work_mem, увидим это в плане и в счётчиках, потом поднимем work_mem и проверим, что пролив исчез. Сначала предскажешь метод сортировки, потом проверишь по EXPLAIN.
Поставь маленький work_mem в сессии:
SET work_mem = '64kB';.Предскажи: что покажет Sort Method - quicksort в памяти или external merge на диске? Затем выполни:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tickets ORDER BY passenger;.Найди в выводе строку Sort Method: при малом work_mem это external merge с объёмом Disk.
Подними work_mem:
SET work_mem = '64MB';и повтори тот же EXPLAIN (ANALYZE). Теперь Sort Method - quicksort, сортировка целиком в памяти, без Disk.Посмотри накопленные проливы по базе:
SELECT datname, temp_files, temp_bytes FROM pg_stat_database WHERE temp_files > 0;- счётчик temp_files вырос от первого прогона.
sandbox с автопроверкой - открыть в песочнице
Резюме
- shared_buffers - общий кеш страниц, выделяется при старте; отправная точка около четверти ОЗУ, больше не всегда лучше из-за дублирования со страничным кешем ОС.
- work_mem - лимит на ОДНУ операцию сортировки/хеша, а не на запрос; реальный расход = work_mem × операций в запросе × одновременных запросов.
- При нехватке work_mem операция не падает, а проливается на диск (temp-файлы); сигнал - Sort Method: external и растущий temp_files в pg_stat_database.
- maintenance_work_mem - для vacuum и build индекса; его ставят выше work_mem, потому что таких операций мало и они не параллелятся клиентами.
- effective_cache_size ничего не выделяет - это подсказка планировщику об объёме кеша; заниженное значение отпугивает его от индексов.
- Дефолты намеренно осторожны (чтобы запуститься где угодно), а не оптимальны; и оставить их, и выкрутить всё в максимум - оба анти-паттерна.
Контрольные вопросы
Почему work_mem называют лимитом на операцию, а не на запрос, и чем это опасно?
Показать ответ
Потому что каждая сортировка, хеш-джойн и хеш-агрегация внутри одного запроса берёт до work_mem независимо. Запрос с тремя такими операциями возьмёт до 3 × work_mem, а сто одновременных запросов - до 300 × work_mem (на самом деле больше, по числу операций в каждом). Опасность в том, что глобально большой work_mem умножается на конкурентность и приводит к OOM. Поэтому глобально его держат скромным, а под тяжёлый отчёт поднимают через SET в его сессии.
Что произойдёт с запросом, если ему не хватит work_mem на сортировку?
Показать ответ
Запрос не упадёт. Сортировка станет внешней (external merge): часть данных уйдёт во временные файлы на диске, и операция доработает, но медленнее, потому что добавился диск. Это видно в плане как Sort Method: external merge с указанием объёма на диске, и в счётчиках pg_stat_database (temp_files, temp_bytes). Растущий temp_files под нагрузкой - сигнал, что work_mem мал для текущих запросов.
Почему maintenance_work_mem можно ставить выше work_mem?
Показать ответ
Потому что у них разная природа конкурентности. work_mem умножается на число одновременных клиентских запросов и операций в них, поэтому опасен. maintenance_work_mem расходуют операции обслуживания (VACUUM, CREATE INDEX, ALTER TABLE) - их мало, их не запускают сотнями параллельно, поэтому большое значение здесь не грозит OOM, а прямо ускоряет vacuum и построение индексов.
Почему effective_cache_size влияет на выбор плана, хотя ничего не выделяет?
Показать ответ
Это число планировщик использует в стоимостной модели как оценку того, сколько памяти под кеш доступно (shared_buffers плюс страничный кеш ОС). По нему он прикидывает, попадут ли повторные обращения к страницам в кеш. Высокое значение удешевляет index scan в глазах планировщика, и он охотнее берёт индекс; заниженное - отпугивает от индексов и подталкивает к seq scan. Память при этом не выделяется, меняется только оценка стоимости.
Почему оставлять дефолтную конфигурацию памяти на проде - анти-паттерн?
Показать ответ
Дефолты PostgreSQL рассчитаны на запуск где угодно, включая слабую машину, поэтому намеренно скромны. На сервере с гигабайтами памяти они заставляют его работать так, будто памяти минимум: маленький кеш, лишние чтения с диска, проливы сортировок. Но и противоположность - выкрутить всё в максимум - роняет сервер через work_mem и OOM. Настраивать нужно от механизма, понимая, что каждый параметр расходует.