Рано или поздно любой системный администратор или разработчик 1С сталкивается с ситуацией, когда тяжелая регламентная операция, например закрытие месяца в «1С:Управление нашей фирмой» (УНФ), завершается аварийно. При этом база данных работает под управлением СУБД PostgreSQL, конфигурация является доработанной («нетиповой»), а в файловом варианте та же самая операция проходит без каких-либо сбоев.
В процессе выполнения операции система долго грузится (в среднем от 20 до 30 минут), оперативная память сервера или локальной машины лавинообразно заполняется до 100%, свободное место на жестком диске стремительно сокращается на несколько гигабайт, и в итоге сессия аварийно завершается с ошибкой: «Недостаточно памяти для получения результата запроса к базе данных».
В этой статье мы подробно разберем, почему возникает эта ошибка, как правильно диагностировать ее источник, как оптимизировать параметры PostgreSQL и кластера 1С, а также как устранить коренные причины проблемы на уровне кода запросов.
Многие технические специалисты ошибочно полагают, что раз в тексте ошибки упоминается «запрос к базе данных», то виновником является сама СУБД PostgreSQL. Однако это не так. Ошибка «Недостаточно памяти для получения результата запроса к базе данных» генерируется непосредственно платформой 1С:Предприятие (конкретно — рабочим процессом rphost сервера приложений или клиентским приложением), а не сервером баз данных.
Давайте проанализируем, как ведет себя система при выполнении запроса в разных файловых и клиент-серверных сценариях:
rphost принудительно обрывает соединение, чтобы защитить сервер от падения (Out of Memory), выдавая вышеуказанную ошибку.Таким образом, мы имеем дело с двойной проблемой: неоптимальный запрос заставляет СУБД генерировать огромный объем временных данных, а сервер 1С блокирует передачу этого гигантского объема клиенту из соображений безопасности.
Если для первоначальной настройки СУБД вы использовали популярный конфигуратор PGtune, то ваши настройки, скорее всего, оптимизированы под стандартные веб-серверы или общие базы данных. Для специфической работы платформы 1С эти параметры категорически не подходят. Давайте разберем, какие параметры в файле postgresql.conf требуют ручной корректировки.
Проанализируем конфигурацию сервера из примера автора темы. При объеме ОЗУ сервера около 16 ГБ у него были установлены следующие параметры:
shared_buffers = 512MB
temp_buffers = 8MB (значение по умолчанию)
work_mem = 22719kB
effective_cache_size = 15GB
Эти значения содержат критические ошибки, которые и приводили к падению системы:
shared_buffers (Буферный кэш СУБД): Значение 512 МБ ничтожно мало для базы данных размером почти 6 ГБ на сервере с 16 ГБ ОЗУ. PostgreSQL физически не может удержать в оперативной памяти даже индексы таблиц, не говоря уже о самих данных. СУБД вынуждена постоянно читать данные с диска. Рекомендация: Для 1С значение shared_buffers должно составлять строго 25% от общего объема ОЗУ сервера. При 16 ГБ ОЗУ установим:
shared_buffers = 4GB.
temp_buffers (Временные буферы): Это самый важный параметр для тяжелых расчетов 1С. Платформа 1С при закрытии месяца генерирует гигантское количество временных таблиц в СУБД. По умолчанию этот параметр равен всего 8 МБ. Как только объем временных таблиц превышает этот порог, PostgreSQL начинает сбрасывать их на жесткий диск во временные файлы. Именно это приводило к тому, что у автора темы «сжиралось» по 3 ГБ дискового пространства за транзакцию. Рекомендация: Увеличьте этот параметр до 256 МБ или даже 512 МБ:
temp_buffers = 256MB.
work_mem (Рабочая память для сортировок и группировок): Значение около 22 МБ крайне мало для сложных запросов с множественными соединениями (JOIN), сортировками (ORDER BY) и группировками. Если подзапросу не хватает выделенной памяти work_mem для выполнения сортировки в ОЗУ, СУБД создает временный файл на диске, что колоссально замедляет работу и забивает дисковую подсистему. Рекомендация: Установите этот параметр в пределах от 64 МБ до 128 МБ:
work_mem = 128MB.
Важное предупреждение: Не устанавливайте здесь слишком большие значения (например, 1 ГБ). Память work_mem выделяется под каждую операцию сортировки внутри каждого потока запроса. Если сложный запрос выполняет 10 сортировок одновременно, он заберет 10 * 128 МБ = 1.28 ГБ ОЗУ только на один этот сеанс.
Чтобы временно обойти ограничение платформы на максимальный объем выделяемой памяти под один запрос (особенно если у вас используется лицензия уровня ПРОФ, где тонкие настройки лимитов заблокированы), нам необходимо скорректировать параметры рабочего сервера в консоли Администрирования кластера 1С.
Выполним следующие шаги:
rphost не перезапускались мгновенно при кратковременных скачках потребления ресурсов.После изменения настроек обязательно перезапустим службу 1C:Enterprise 8.3 Server Agent через оснастку services.msc.
Настройка серверов — это борьба с симптомами. Истинная причина проблемы всегда кроется в коде. СУБД PostgreSQL гораздо более чувствительна к качеству написания запросов 1С, чем Microsoft SQL Server. Рассмотрим две самые распространенные ошибки программистов 1С при доработке механизмов проведения и закрытия месяца.
Если в вашей нетиповой конфигурации УНФ разработчики реализовали запрос, содержащий условие отбора по составному типу данных (например, по регистратору, который может быть десятком разных документов), планировщик запросов PostgreSQL часто строит неэффективный план.
Рассмотрим пример неоптимального кода:
ВЫБРАТЬ
ЗапасыОбороты.Номенклатура,
ЗапасыОбороты.КоличествоОборот
ИЗ
РегистрНакопления.Запасы.Обороты(&НачалоПериода, &КонецПериода, Авто, Регистратор В (&МассивДокументов)) КАК ЗапасыОбороты
Поскольку параметр &МассивДокументов содержит ссылки на документы разных типов, PostgreSQL не сможет использовать индекс по полю Регистратор. Вместо этого он выполнит полное сканирование всей таблицы (Seq Scan), загружая гигабайты промежуточных данных в оперативную память.
Для оптимизации перепишем этот алгоритм. Сначала поместим массив во временную таблицу, типизируем его, а затем выполним соединение:
// Шаг 1: Создаем временную таблицу с четким типом данных
ВЫБРАТЬ
ВЫРАЗИТЬ(ВременныйМассив.Ссылка КАК Документ.РасходнаяНакладная) КАК ДокументСсылка
ПОМЕСТИТЬ ВТ_Документы
ИЗ
&МассивДокументов КАК ВременныйМассив
;
// Шаг 2: Соединяемся с временной таблицей
ВЫБРАТЬ
ЗапасыОбороты.Номенклатура,
ЗапасыОбороты.КоличествоОборот
ИЗ
РегистрНакопления.Запасы.Обороты(&НачалоПериода, &КонецПериода, Авто, ) КАК ЗапасыОбороты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Документы КАК ВТ_Документы
ПО ЗапасыОбороты.Регистратор = ВТ_Документы.ДокументСсылка
Если в процессе кастомизации УНФ были созданы новые регистры сведений или накопления, убедитесь, что у измерений, по которым строятся срезы или берутся обороты, взведен флаг «Индексировать». Без этого PostgreSQL будет вынужден сканировать таблицы целиком при каждом расчете себестоимости.
Автор темы обратил внимание на то, что в процессе закрытия месяца физический размер базы данных PostgreSQL вырастает на 1–2 ГБ. Это абсолютно стандартное поведение для данной СУБД, обусловленное технологией многоверсионности (MVCC).
Когда 1С перезаписывает движения документов (удаляет старые проводки и записывает новые), PostgreSQL физически не удаляет старые строки с диска. Он лишь помечает их как «мертвые» (dead tuples), записывая новые версии строк в конец таблицы. Из-за этого база данных мгновенно «раздувается».
Для очистки этого пространства необходимо настроить регулярное обслуживание СУБД:
postgresql.conf включен и правильно настроен фоновый процесс автоматической очистки:
autovacuum = on.
VACUUM FULL ANALYZE;
Обратите внимание: Операция VACUUM FULL накладывает исключительную блокировку на таблицы, поэтому выполнять ее можно только во время полного отсутствия пользователей в базе данных.
Если ваша база 1С на PostgreSQL падает с ошибкой нехватки памяти, пройдитесь по следующим пунктам:
temp_buffers до 256MB и work_mem до 128MB в postgresql.conf, чтобы временные таблицы 1С обрабатывались в ОЗУ, а не на диске.shared_buffers ровно 25% от ОЗУ сервера.В (&Массив) по составным типам и проиндексируйте новые измерения в доработанных регистрах.VACUUM FULL ANALYZE для сжатия базы после закрытия месяца.