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