Записки оптимизатора 1С (ч.17). Как избежать падения Postgres при большом потреблении памяти запросами / Хабр

Речь пойдет об одной из старых проблем со стабильностью работы Postgres – об отсутствии полного контроля за потреблением оперативной памяти при выполнении запросов (через work_mem), что в ряде случаев приводит либо к свопированию, либо к падению СУБД и остановке работы информационной системы. Происходит это неожиданно, без предупреждения и в не самый удачный момент, например, ночью, когда все спят. В системах 1С эта проблема точно существует, в силу использования платформой сложных многоэтажных запросов с большим количеством соединений, группировок и сортировок.

Вроде бы на помощь должна прийти технология OOM Killer Linux, но как обычно не все так радужно и есть неприятные нюансы.

В управлении памятью Linux существует концепция overcommit, которая позволяет системе выделять больше виртуальной памяти, чем физически доступно. Это может быть полезно, но требует осторожного подхода для предотвращения проблем с производительностью и стабильностью.

По умолчанию Linux использует overcommit памяти, когда ядро разрешает процессам запросить больше памяти, чем физически доступно (vm.overcommit_memory=0). Если вся доступная память заканчивается, ядро вызывает OOM Killer, который выбирает процесс-жертву по oom_score (чем больше памяти потребляет процесс, тем выше шанс быть убитым)

Особенности и недостатки:

Непредсказуемость. Ядро выбирает тот процесс, снос которого приведёт к максимальному освобождению памяти. И управляющий процесс Postgres с его огромным shared_buffers является идеальным кандидатом на вылет.
Грубость. OOM Killer посылает сигнал SIGKILL, что не дает Postgres возможности корректно завершить транзакции и сбросить данные на диск.

Почему OOM вообще случается с Postgres

Главная причина – особенность работы postgres с памятью, а точнее, её практически неограниченное суммарное потребление при высокой конкуренции запросов.

Про память в Postgres у нас был цикл из трёх статей. Если кто не читал, рекомендую.

Самый интересный параметр, который напрямую влияет на потребление памяти отдельно взятым запросом – work_mem. Задаёт базовый максимальный объём памяти, который будет использоваться во внутренних операциях (JOIN, ORDER, GROUP BY, UNION и т.д.) при обработке запросов в рамках одного рабочего процесса (сессии) прежде, чем будут задействованы временные файлы на диске.

Соблазн установить заведомо большое значение для work_mem может очень легко привести к падению всего postgres. Вот выдержка из совсем недавней нашей статьи про высвобождение памяти postgres’ом:

Почему нельзя увеличить слишком сильно work_mem, выставив, например, сразу 20 Гб и ничего не проверять по временным файлам?
Потому что есть два риска:
1. Появится сложный запрос, а в 1С:Предприятие это очень вероятно, с большим количеством HASH JOIN, GROUP BY, ORDER и прочее, который съест всю память и приведет к падению PG.
2. По мере роста базы данных прежние настройки могут начать работать против вас. Из-за излишне высокого значения work_mem (20 ГБ) запрос, который раньше не требовал много памяти, на увеличенных таблицах может потреблять ее в разы больше. Как следствие, это может спровоцировать падение Postgres.
Ну а маленькое значение work_mem приведет к избыточной нагрузке на диск и увеличению длительности выполнения запросов.

Но даже правильный и обдуманный выбор значения work_mem не защищает от того, что можно написать такой сложный запрос, который потребит огромное количество памяти.

Итого, никакая настройка не гарантирует что Postgres не возьмет всю память сервера, в то время как в MS SQL есть жесткий гарантированный лимит max server memory, который никогда не будет превышен ни при каких обстоятельствах.

Почему падает весь сервер Postgres, а не один запрос?

Архитектурная причина.

PostgreSQL использует многопроцессную архитектуру (в отличие от потоковой в MS SQL). Есть главный процесс postmaster и дочерние backend-процессы (по одному на соединение), а также фоновые процессы (walwriter, checkpointer, autovacuum launcher и др.).

Когда OOM Killer убивает любой дочерний процесс (хотя бы один backend), postmaster обнаруживает смерть потомка и инициирует аварийное завершение ВСЕХ процессов. Это защитный механизм для сохранения целостности shared memory: после убийства любого backend-процесса pоstmaster не может гарантировать, что shared memory осталась в консистентном состоянии, поэтому чтобы предотвратить повреждение данных Postgres переходит в режим Crash Recovery – принудительно «убивает» все остальные дочерние процессы, запускается заново.

Итого «убийство» даже одного дочернего процесса ООМ-киллером равнозначно перезагрузке всей СУБД. Поэтому использование OOM Killer’а для Postgres – это, скорее, зло, нежели благо.

Натурный эксперимент

Хочу продемонстрировать простым синтетическим примером как один тяжелый запрос может положить всю СУБД. А вторым примером продемонстрирую один из вариантов как с этим бороться и стабилизировать ситуацию. Вы можете легко повторить их самостоятельно.

Пример 1. Моделирование падения Postgres из-за OOM Killer

Стенд:

Debian 13 + ванильный Postgres 17.9
ВМ с 4 Гб ОЗУ без swap.
Настройки postgresql.conf:
- max_parallel_workers_per_gather = 0 (Параллелизм отключен)
- work_mem = 1GB

В тестовой БД создаем таблицу со случайными данными:

CREATE TABLE table1 (column1 TEXT, column2 INT);

DO $$
DECLARE 
_id int :=0;
BEGIN
  WHILE _id < 10000000 LOOP
  INSERT INTO table1 (column1, column2) VALUES (array_to_string(array(
SELECT SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',((random()*(36-1)+1):: INTEGER),1)
FROM generate_series(1,256)),''), round(random()*100 ));
  _id := _id+1;
  END LOOP;
END $$;

Далее из консоли psql с помощью скрипта (script.sql) будем запускать в нескольких потоках тяжелый запрос, требующий много памяти.

SELECT t3.* FROM table1 t1
FULL JOIN table1 t2 ON t1.column2=t2.column2 
FULL JOIN table1 t3 ON t3.column2=t2.column2
LIMIT 1;

4 Гб ОЗУ хватает только для запуска двух параллельных запросов:

$ psql -f script.sql -tA & psql -f script.sql -tA & wait

В другом окне был запущен htop. Видно, что наши фоновые процессы с PID 753 и 754 привели к запуску двух процессов postgres – 755 и 756:

Запросы выполнились успешно и в пике потребили примерно по 1,7 Гб оперативной памяти.

Теперь запустим одновременно три таких запроса:

psql -f script.sql -tA & psql -f script.sql -tA & psql -f script.sql -tA & wait

Три фоновых процесса [765-767] привели к запуску трёх процессов postgres, которые в итоге не смогли выполниться из-за возникших ошибок.

Непосредственно перед их (ошибок) появлением ситуация в htop выглядела следующим образом: backend-процессы [768-770] потребили каждый почти по 30% оперативной памяти:

А мгновением позже OOM-killer срубил часть важных процессов, оставив лишь мастер-процесс [680] и checkpointer [773], а так же запустился процесс остановки кластера postgres [771, 776]:

Попытка повторно запустить хотя бы два запроса (sql-скрипта) сталкивается с ошибками подключения к postgres:

Итого. Postgres был «убит» механизмом OOM-killer, причём мастер-процесс остался, но подключения больше не устанавливаются, т.е. сервер postgres на запросы больше не отвечает. Команда # systemctl restart postgresql не перезапускает сервер, а висит на ожидании. Команда # systemctl stop postgresql тоже не помогает. Перезапустить postgres можно только после отправки сигнала SIGKILL мастер-процессу.

Стоит отметить, что в ходе исследования мы сталкивались в том числе и с ситуациями, когда аварийно завершались все процессы postgres, и в таком случае запустить postres было проще.

В выводе команды dmesg отчётливо видно, что поработал именно механизм oom-killer:

Пример 2. Изменение стратегии vm.overcommit_memory

Теперь изменим стратегию overcommit, сделав так, чтобы память выделялась только в рамках установленного лимита оперативной памяти. Напомню, swap отключен.

# sysctl -w vm.overcommit_memory=2 – работа без overcommit.

# sysctl -w vm.overcommit_ratio=95 – размер доступной памяти для процессов в процентах.

Итого, мы отключили механизм overcommit (vm.overcommit_memory=2) и настроили доступную для выделения долю памяти (vm.overcommit_ratio=95). Следует сразу подчеркнуть, что параметр vm.overcommit_ratio зависит от объёма ОЗУ на сервере и размера swap, поэтому его следует устанавливать, основываясь на их значениях. Для серверов с большим количеством ОЗУ, значение vm.overcommit_ratio может состявлять 95-98, но это не предмет обсуждения данной статьи. Значение по умолчанию равно 50, и оно категорически нам не подходит, т.к., по сути, ограничивает объём доступного ОЗУ ровно в два раза, и такого количества памяти не хватило бы на успешное одновременное выполнение даже двух наших sql-скриптов. Для нашего тестового сервера без swap, значение vm.overcommit_ratio=95 является вполне допустимым.

Снова запустим два одновременных запроса, чтобы убедиться, что всё в порядке:

$ psql -f script.sql -tA & psql -f script.sql -tA & wait

Запросы выполнились успешно.

Теперь пробуем запустить три одновременных запроса:

$ psql -f script.sql -tA & psql -f script.sql -tA & psql -f script.sql -tA & wait

Ни один из запросов не выполнился из-за ошибки:

В этот раз были завершены только процессы, пытавшиеся взять больше памяти, чем есть – [776-778]. При этом сам postgres продолжал работать и принимать подключения. Т.е. если повторно запустить команду для двух одновременных запросов, то она успешно выполнится. Предлагаем убедиться в этом самостоятельно.

Теперь немного изменим начальные условия, смоделировав ситуацию, когда один из трех запросов будет запускаться с некоторой задержкой:

$ psql -f script.sql -tA & psql -f script.sql -tA & sleep 5; psql -f script.sql -tA & wait

Здесь также возникла ошибка, но результат другой.

Перед возникновением ошибки первые два процесса postrges [784 и 785] потребляли немного больше памяти, чем третий [787].

В какой-то момент процессу [784] первому не хватило памяти, и он завершился с ошибкой. При этом, что немаловажно, вернул память в систему, позволив оставшимся двум выполняться, т.к. им памяти уже хватило:

В итоге, один запрос завершился неудачно из-за ошибки, а оставшиеся два выполнились успешно:

Таким образом, изменив стратегию overcommit, мы предотвратили само возникновение ситуации, когда возможен перерасход памяти и срабатывание механизма OOM killer, пожертвовав только теми запросами, которым не хватило памяти. Остальные запросы продолжат выполняться, и сама СУБД осталась на плаву.

В ходе последнего эксперимента поведение postgres было разным. Чаще всего завершались с ошибками два запроса из трёх, и только один (тот, что с задержкой) выполнялся успешно. Но в статью я внес именно пример, когда был срублен только один запрос из трех. Такое поведение мне нравится больше.

Резюме

Пожалуй, можно утверждать, что OOM Killer – это по-прежнему одна из самых главных проблем в эксплуатации Postgres. И это не баг и не ошибка, а следствие архитектурных решений, заложенных много лет назад.

Конечно, в этом плане MS SQL гораздо более устойчив к таким ситуациям. Да, под пиковой нагрузкой MS SQL может так сильно урезать память у всех остальных запросов что весь инстанс полностью зависает на какое-то время. А вот Postgres может «упасть» сразу, без предупреждения и полностью. В больших системах 1С наблюдали это неоднократно. Ситуация малоприятная.

Рекомендации от Softpoint:

Отключить стратегию overcommit (vm.overcommit_memory=2) на сервере Postgres и не забыть изменить размер доступной памяти для процессов в процентах (vm.overcommit_ratio) или в килобайтах (vm.overcommit_kbytes). По умолчанию vm.overcommit_ratio = 50%.
В Postgres в отличие от MS SQL с его max server memory нет встроенного жёсткого лимита на общее потребление памяти. Поэтому лучше пусть будет срублен один процесс (сессия sql), которому не хватило памяти, нежели упадет вся СУБД.
Правильно настроить работу с памятью в postgres. Там гораздо больше настроек, нежели в MS SQL. Мы написали целый цикл из трёх статей на эту тему.
Вести перманентный мониторинг системы. Так при наступлении инцидента можно будет ретроспективно проанализировать причины завершения процесса – понять почему ему требовалось столько памяти, что это был за запрос, как часто он вызывается, с какими параметрами он был в тот раз и т.д. Качественный анализ позволит исправить причину, например, переписать запрос или изменить настройки памяти.

Надеюсь, это небольшое исследование кому-то пригодится и теперь вы будете спать спокойнее и крепче, а PG останется в рабочем состоянии при любом потреблении памяти запросами. Мы не претендуем на истину в последней инстанции и, наверняка, можно предложить другие интересные решения, поэтому делитесь в комментариях своими кейсами и историями.

Ссылки на остальные части Записок оптимизатора 1С:

Записки оптимизатора 1С (ч.17). Как избежать падения Postgres при большом потреблении памяти запросами