В этом уроке разбираем, из чего реально состоит работающий сервер postgresql: какие операционные процессы крутятся в фоне, кто обслуживает ваше соединение и куда уходит память. Без этой картины настройка shared_buffers и work_mem превращается в гадание, а строчки в выводе ps или в pg_stat_activity выглядят как магия. Цель - чтобы вы смотрели на запущенный кластер и понимали, кто за что отвечает и почему сервер ведёт себя именно так под нагрузкой.
Как это работает
PostgreSQL устроен не как один большой многопоточный демон, а как семья отдельных процессов операционной системы. Главный из них - postmaster (он же supervisor). Он слушает сетевой порт, принимает входящие подключения и на каждое новое соединение порождает отдельный дочерний процесс - backend. Сам postmaster запросы не обслуживает: его задача - принимать соединения, поднимать фоновые процессы и перезапускать упавшие.
Один клиент - один backend. Это важная мысль. Backend живёт ровно столько, сколько открыт ваш сеанс, выполняет ваши команды и умирает при отключении. Отсюда два следствия. Первое: соединение в PostgreSQL дорогое, это полноценный процесс ОС с собственной памятью, поэтому тысячи коротких подключений лучше пропускать через пул (PgBouncer), а не открывать напрямую. Это особенно касается postgresql для 1с и веб-приложений, где соединения плодятся быстро. Второе: между процессами нужна общая память, чтобы они видели одни и те же данные.
Эту роль играет разделяемая память (shared memory) - область, которую postmaster выделяет при старте и к которой подключаются все backend и фоновые процессы. Самая большая её часть - shared_buffers, кэш страниц таблиц и индексов. Когда backend читает строку, он не лезет каждый раз в файл: сначала ищет нужную 8-килобайтную страницу в shared_buffers. Если её там нет, страница подтягивается с диска (а точнее, чаще из кэша операционной системы) и кладётся в буфер. Получается двойное кэширование: shared_buffers внутри Postgres и page cache самой ОС. Поэтому отдавать под shared_buffers всю RAM бессмысленно - вы просто задвоите кэш и отнимете память у ОС.
Теперь фоновые процессы, ради которых сервер вообще держится живым.
checkpointer периодически сбрасывает на диск грязные (изменённые) страницы из shared_buffers и ставит контрольную точку - отметку, с которой можно начать восстановление после сбоя. bgwriter (background writer) помогает ему, заранее выталкивая грязные страницы, чтобы backend не упирался в чистый буфер в самый неподходящий момент. walwriter сбрасывает на диск журнал предзаписи WAL - тот самый журнал, благодаря которому БД переживает падение питания. autovacuum (его launcher и рабочие worker-ы) в фоне чистит мёртвые версии строк после UPDATE и DELETE и обновляет статистику для планировщика. Ещё есть startup-процесс (восстановление при запуске), архиватор WAL, логгер и процессы логической репликации.
Кроме разделяемой памяти у каждого backend есть локальная (приватная) память. Главный её параметр - work_mem. Это лимит на одну операцию сортировки или хеширования внутри запроса. Ключевое слово - на операцию: сложный запрос с несколькими сортировками и хеш-соединениями может занять несколько work_mem сразу, а если таких backend много, суммарный аппетит умножается. Если операции не хватает work_mem, она проливается на диск во временные файлы и резко тормозит. Отдельно стоит maintenance_work_mem - память для тяжёлых служебных операций (CREATE INDEX, VACUUM), её можно ставить заметно крупнее, потому что такие операции редки и обычно идут не параллельно.
Что поменялось в свежих версиях. В PostgreSQL 16 фоновую запись и обработку WAL продолжили оптимизировать, а логическую репликацию научили работать со standby. В PostgreSQL 17 переписали механизм vacuum: вместо линейного массива идентификаторов мёртвых строк, ограниченного maintenance_work_mem, появилось компактное TID-хранилище, так что чистка крупных таблиц стала экономнее по памяти и реже ходит по индексам в несколько проходов. База у нас - PG 15, но эти отличия стоит держать в голове при апгрейде.
SQL и примеры
Посмотрим на живой сервер. Кто сейчас подключён и какие backend активны:
Код: Выделить всё
SELECT pid, usename, application_name, state, backend_type
FROM pg_stat_activity
ORDER BY backend_type, pid;
Сколько памяти отдано под разделяемый кэш и каков размер буфера:
Код: Выделить всё
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;
SELECT current_setting('block_size'); -- обычно 8192 байта
Код: Выделить всё
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
SELECT c.relname,
count(*) AS buffers,
pg_size_pretty(count(*) * 8192) AS in_cache
FROM pg_buffercache b
JOIN pg_class c ON c.relfilenode = b.relfilenode
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 10;
Код: Выделить всё
SELECT f.flight_no, count(*) AS passengers
FROM bookings.flights f
JOIN bookings.ticket_flights tf ON tf.flight_id = f.flight_id
JOIN bookings.boarding_passes bp ON bp.ticket_no = tf.ticket_no
AND bp.flight_id = tf.flight_id
GROUP BY f.flight_no
ORDER BY passengers DESC
LIMIT 5;
Частые грабли
- Ставят shared_buffers в 80-90% RAM. ОС тоже кэширует файлы БД, память задваивается, а реальной памяти под работу backend не остаётся. Разумная стартовая точка - около четверти RAM, дальше по метрикам.
- Считают work_mem лимитом на запрос или на сеанс. Это лимит на одну операцию. Сотня соединений с несколькими сортировками легко вынесет сервер в OOM.
- Открывают тысячи прямых соединений из приложения. Каждое - отдельный процесс с памятью и накладными расходами postmaster. Без пула (PgBouncer) сервер захлёбывается на ровном месте.
- Думают, что один зависший backend можно убить kill -9. Жёсткое убийство backend роняет весь кластер в аварийное восстановление. Корректно - pg_terminate_backend(pid).
- Путают bgwriter и checkpointer. Первый сглаживает запись грязных страниц, второй ставит точки восстановления; настраиваются они разными параметрами.
- Поднимают maintenance_work_mem гигантским при включённом параллельном autovacuum: несколько worker-ов умножают эту память. В PG 16 и старше это менее болезненно, в PG 17 vacuum экономнее по памяти.
- Запустите psql и выполните SELECT backend_type, count(*) FROM pg_stat_activity GROUP BY backend_type. Найдите checkpointer, walwriter, background writer.
- В другом терминале посмотрите процессы ОС: ps aux | grep postgres. Сопоставьте их с тем, что показал pg_stat_activity.
- Выведите текущие значения shared_buffers, work_mem, maintenance_work_mem через SHOW.
- Установите CREATE EXTENSION pg_buffercache и сделайте запрос топ-10 объектов в кэше на пустой нагрузке.
- Прогоните отчёт по boarding_passes из примера выше, затем снова посмотрите pg_buffercache - таблицы должны подняться в топ.
- Сделайте SET work_mem = '64kB' в своём сеансе, выполните большой ORDER BY и через EXPLAIN (ANALYZE) поймайте строку про external merge Disk - сортировка пролилась на диск.
- Верните work_mem к нормальному значению и сравните время выполнения того же запроса.
- Чем занимается postmaster и почему он сам не выполняет SQL-запросы клиентов?
- Почему один backend на соединение делает прямые подключения дорогими и чем здесь помогает пул соединений?
- В чём разница между shared_buffers и кэшем операционной системы и почему вредно отдавать под shared_buffers почти всю RAM?
- work_mem - это лимит на запрос, на сеанс или на операцию? Как это влияет на риск нехватки памяти?
- За что отвечают checkpointer, bgwriter и walwriter и чем их роли различаются?
- Что изменилось в управлении памятью vacuum в PostgreSQL 17 по сравнению с PG 15?
