🌟 work_mem — тихий пожиратель оперативки

shared_buffers и кэш ОС памяти занимают немало, но ведут себя спокойно и предсказуемо. А вот из-за work_mem бывает что оперативка «внезапно» улетает в потолок 📈

🤔 Что это
Запросы к БД могут быть простыми, состоящими из одного шага (операции): достань одно поле по такому-то идентификатору. А могут быть сложными, из нескольких операций: достань данные из разных таблиц, отсортируй, сгруппируй, выдай сумму.

Некоторым операциям внутри запроса нужна рабочая память: место, чтобы разложить промежуточные данные. Чаще всего это сортировка (ORDER BY, построение индекса) и хеш-операции (соединение таблиц через хеш, группировка).

work_mem — это параметр, который можно задать как на уровней всей СУБД, так и в рамках запроса. Он указывает, сколько оперативки одна такая операция имеет право взять, прежде чем начнёт сбрасывать промежуточные данные на диск. По умолчанию лимит очень скромный — 4 MB.

🔬 Пощупаем
Берём таблицу users на миллион строк и сортируем по email (дикпик 1)

При work_mem = 4MB сортировка в лимит не влезает, и Postgres досортировывает её через диск. В плане запроса (его показывает команда EXPLAIN) это видно по строке Sort Method: external merge Disk — «внешняя сортировка слиянием»: данные бьются на куски, частично уходят во временные файлы и потом сливаются.

Поднимаем work_mem до 256MB и повторяем ту же сортировку. Теперь она целиком умещается в памяти: Sort Method: quicksort Memory, и рядом её размер — около 71 MB. И это на одну операцию!🤯

⚠️ Где подвох
К БД может быть открыто несколько подключений, если используется пул подключений, например, или несколько клиентов. Каждое подключение к Postgres обслуживается отдельным процессом операционной системы, его называют бэкендом. Если у тебя сто подключений, значит на сервере с БД работает сто процессов. По каждому из этих подключений могут одновременно прийти сложные запросы, и все эти запросы будут выполняться параллельно. В каждом таком запросе несколько операций.

И теперь главная мысль:
⚠️work_mem выделяется на каждую операцию ⚠️

Не на запрос целиком и не на весь сервер. То есть свой лимит у каждой сортировки и у каждой группировки⚡️

Дальше простая арифметика беды: work_mem × число операций × число соединений. Если щедро выставить 256 MB и открыть пару сотен активных соединений с тяжёлыми запросами, то десятки гигабайт оперативки тут же растворятся😯

🧪 Важная оговорка
Объём work_mem не резервируется заранее. Память берётся только тогда, когда она понадобилась для выполнения операции, и ровно столько, сколько нужно (но не больше лимита). Так что «256 MB × 200 соединений = сразу отожрёт 51 GB» — это не совсем так. Столько наберётся, только если все разом запустят достаточно тяжёлые операции🤩

И ещё тонкость: для хеш-операций по умолчанию работает множитель hash_mem_multiplier, равный 2. То есть хеш имеет право взять не work_mem, а вдвое больше, потому что хеш-таблица в памяти прожорливее сортировки⚠️

🅰️ Что унести с собой
🟢 work_mem — это лимит рабочей памяти на одну сортировку или хеш-операцию, по умолчанию 4 MB
🟢 Если лимита не хватает, операция досчитывается через диск и работает медленнее
🟢 Реальный расход — это work_mem × число операций × число соединений, поэтому слишком большой work_mem легко съедает гигабайты оперативки
🟢 Память не резервируется заранее, а хеши по умолчанию берут вдвое больше из-за hash_mem_multiplier

🧑‍💻dp🥁
#бд #postgresql #инженерныештучки #heavywednesday

🩺 Диагностика — как понять, на что ушла оперативка (часть 1)

Мы с тобой разобрались с shared_buffers, кэш ОС и work_mem. Теперь можем понять, если на сервере память забита под завязку (как на дикпике из графаны), проблема это или норма, и куда смотреть в первую очередь 👀

📊 Доля попаданий в кэш (дикпик 1)
Первый вопрос — насколько хорошо данные ложатся в память. По каждой базе Postgres в pg_stat_database (системное представление, содержащее накопленную статистику по каждой базе данных в кластере) содержит два счётчика: blks_hit (сколько страниц нашлись прямо в shared_buffers) и blks_read (сколько пришлось дочитывать мимо него). Их отношение hit / (hit + read) называют долей попаданий в кэш (по-английски cache hit ratio) 🔖

На прогретой базе эта доля стремится к 99% и выше. У меня после прогрева вышло около 97.84%, а сразу после рестарта она была заметно ниже, потому что кэш ещё пустой.

Если доля стабильно низкая, это сигнал: рабочий набор (та часть данных, к которой реально обращаются запросы) не помещается в память, либо запросы идут мимо индексов и вычитывают всё подряд 📖

🔎 Разрез по таблицам и индексам (дикпик 2)
Одно общее число по базе мало о чём говорит. Чтобы понять, что конкретно не попадает в кэш, есть два представления: pg_statio_user_tables (счётчики чтений и попаданий по каждой таблице) и pg_statio_user_indexes (то же самое по каждому индексу). В нём сразу видно, какая таблица или какой индекс постоянно бегает на диск 💽

🅰️ Что унести с собой
🟢 Доля попаданий из pg_stat_database показывает, помещаются ли данные в память. Если она стабильно низкая, это повод разбираться
🟢 Представление о таблицах и индексах в кэше дают... представления pg_statio_user_tables и pg_statio_user_indexes

Ещё пару инструментов рассмотрим в следующем посте 👉

🧑‍💻dp🥁
#бд #postgresql #инженерныештучки #heavywednesday