Оптимизация настроек планировщика запросов в PostgreSQL

30.01.18

База данных - Инструменты администратора БД

Хочу сказать несколько слов о своем опыте настройки PostgreSQL для работы с 1С. А поскольку в сети уже достаточно много хороших мануалов о настройке Postgres, ограничусь тем, как я поборол неоптимальное использование плана nestloop.

Скачать файлы

Наименование Файл Версия Размер
Файл конфигурации postgresql.conf
.conf 22,45Kb
6
.conf 22,45Kb 6 Скачать

Итак, исходные данные: база 1С Бухгалтерия для Украины версии 1.2. Размер — 9Гб, до 20 пользователей, файловый вариант. Работает на Win сервере Xeon E5-2640x2 32 Гб ОЗУ. Сервер является одновременно сервером терминалов. Решено переходить на клиент-серверный вариант с использованием PostgreSQL, заодно решено и обновить платформу 1С: устанавливаем 1С 8.3.11.2899, сервер 1С 64х и PostgreSQL 9.6.6 (версия с сайта 1С).

После предварительной настройки по статьям с ИТС и интернета скорость работы удалось довести до вполне комфортных значений. Отзывчивость форм и в особенности журналов с отборами явно повысилась. Запись документов/справочников сопоставима с файловым вариантом. Но вот проведение документов сразу же показало трехкратное замедление. Виновен в тормозах оказался простенький запрос по остаткам:

ВЫБРАТЬ
	Счет, 
	Субконто1, 
	Субконто2,
	НЕОПРЕДЕЛЕНО КАК Субконто3,
	ЕСТЬNULL(СуммаОстаток, 0) КАК СуммаОстаток, 
	ЕСТЬNULL(Валюта, НЕОПРЕДЕЛЕНО) КАК Валюта, 
	ЕСТЬNULL(ВалютнаяСуммаОстаток, 0) КАК ВалютнаяСуммаОстаток
ИЗ
	РегистрБухгалтерии.Хозрасчетный.Остатки(&Период, Счет = &Счет, &ВидыСубконто,  Организация = &Организация И Субконто1 = &Субконто1 И Субконто2 = &Субконто2) КАК Остатки

ГДЕ
	ЕСТЬNULL(Остатки.СуммаОстаток, 0) > 0

ДЛЯ ИЗМЕНЕНИЯ

УПОРЯДОЧИТЬ ПО
	Счет,
	Субконто1,
	Субконто2,
	Субконто3,
	Валюта

Параметры запроса:

  • ВидыСубконто: Контрагенты, Договора.
  • Счет: 6811 (счет учета авнсов).
  • Период: момент времени проводимого документа.

Тормоза были в основном на контрагентах с большим количеством операций. Реиндексация и пересчет остатков ситуацию не исправили. Правда в процессе экспериментов с запросом проявилась интерестная особенность: если в качестве Периода передавать дату — запрос выполняется достаточно быстро — 0.5с, а вот если передавать момент времени, то выполнение запроса занимает 18с. Виной тому скорее всего неоптимальное построение запроса на стороне СУБД, поэтому после непродолжительных экспериментов с настройками нахожу вариант решения: если запретить использование вложеных циклов в запросе, то выполнение занимает те же 0.5с:

enable_nestloop = off

Правда ложка меда оказалась с каплей дегтя: общее проведение все равно до уровня файловой базы не дотягивало. Кроме того все-таки сомнения насчет целесообразности отключения nestloop все-равно присутствовали, и поиск в интернете их подтвердил: после отключение по некоторым отзывам были заметны торможения в ЗУП и в Бухгалтерии при расчете износа ОС:

После этого, исполненый мрачных предчувствий, начал искать узкие места у себя, и таки нашел: формирование карточки счета из ОБС по счету выполнялось неоправданно долго при отключенном nestloop и вполне нормально при включенном. Искать дальше и проверять модуль ЗП и ОС желания уже не было, поскольку и так стало понятно что нужно искать выход. Выходов я видел два: переписание запроса (скорость выполнения оказалась все тех же 0.5с), но не было гарантии что запрос только один, да и переписывать модуль на поддержке как-то не хотелось. Второй же выход — как то заставить оптимизатор выбирать менее затратный план исполнения запроса. Использование хинтов СУБД из 1С непредусмотрено, остаются настройки самого Постгреса. В интернете встречал информацию, что со временем планировщик стал выбирать оптимальный план исполнения. Окрыленный, начинаю гонять тесты изменяя параметры оптимизатора, и в какой то момент действительно запрос начал нормально исполнятся при включенном nestloop. В моем случае это:

enable_nestloop = on
seq_page_cost = 0.1
random_page_cost = 0.4
cpu_operator_cost = 0.00025
effective_cache_size = 12GB

Самое интерестное, что при этом запрос начал испонятся еще быстрее — 0.3с. Наверное это действительно оптимальные значения, и планировщик срабатывает нормально.

 

Напоследок хочу порекомендовать статьи и ресурсы которыми пользовался в процессе настройки:

PostgreSQL Postgre постгрес постгри nestloop enable_nestloop запрос оптимизация МоментВремени postgresql.conf.

См. также

Автоподбор ролей для профилей и групп доступа в любых типовых базах 1С УТ 11, КА 2, ERP2, Розница 2/3, УНФ 16/3, БП 3, ЗУП 3 и подобных (УФ, Платформа 8.3.14+)

Инструменты администратора БД Роли и права 8.3.14 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:Документооборот 1С:Зарплата и кадры государственного учреждения 3 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Зарплата и Управление Персоналом 3.x 1С:Управление нашей фирмой 3.0 1С:Розница 3.0 Платные (руб)

Роли… Вы тратите много времени и сил на подбор ролей среди около 2400 в ERP или 1500 в Рознице 2, пытаясь понять какими правами они обладают? Вы все время смотрите права в конфигураторе или отчетах чтоб создать нормальные профили доступа? Вы хотите наглядно видеть какие права дает профиль и редактировать все в простом виде? А может хотите просто указать подсистему и дать права на просмотр и добавление на объекты и не лезть в дебри прав и чтоб обработка сама подобрала нужные роли? Все это теперь стало возможно! Обновление от 15.12.2023, версия 1.1.

12000 руб.

06.12.2023    2999    15    1    

34

SALE! 20%

Infostart УДиФ: Управление данными и формами

Инструменты администратора БД Инструментарий разработчика Роли и права Платформа 1С v8.3 Конфигурации 1cv8 Россия Платные (руб)

Расширение позволяет без изменения кода конфигурации выполнять проверки при вводе данных, скрывать от пользователя недоступные ему данные, выполнять код в обработчиках. Не изменяет данные конфигурации, легко устанавливается практически на любую конфигурацию на управляемых формах.

10000 8000 руб.

10.11.2023    3590    11    1    

34

SALE! 30%

PowerTools

Инструментарий разработчика Инструменты администратора БД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Россия Платные (руб)

Универсальный инструмент программиста для администрирования конфигураций. Сборник наиболее часто используемых обработок под единым интерфейсом.

3600 2520 руб.

14.01.2013    177775    1074    0    

849

Ускоренное проведение документов (x4), устранение ошибок 60/62 счетов и зачет авансов (Бухгалтерия 3.0)

Закрытие периода Инструменты администратора БД Корректировка данных Бухгалтерский учет 1С:Бухгалтерия 3.0 Россия Бухгалтерский учет Платные (руб)

Расширение «Оперативное проведение» в 4 раза уменьшает время проведения документов и закрытия месяца. Является комплексным решением проблем 62 и 60 счетов. Оптимизирует проведение при включенной функциональной опции «Раздельный учет НДС». Используется в более 10 организациях уже 2 года. Совместимо с конфигурацией Бухгалтерия 3.0 (+КОРП).

14400 руб.

29.04.2020    27402    79    146    

59

Система хранения присоединенных файлов в томах на диске

Инструменты администратора БД Платформа 1С v8.3 1С:Комплексная автоматизация 1.х 1С:Управление производственным предприятием Платные (руб)

Конфигурация Комплексная автоматизация 1.1 (и УПП 1.3 тоже) хранит файлы и изображения в справочнике Хранилище дополнительной информации в реквизите Хранилище типа ХранилищеЗначений. Та же история с ВложениямиЭлектроннойПочты. Но при этом присоединенные файлы в Электронном документообороте хранит в томах на диске. Эта доработка позволяет использовать стандартный механизм хранения файлов, изображений и вложений электронных писем в томах на диске. При этом можно разделить тома хранения по объектам конфигурации.

4200 руб.

10.11.2015    61322    88    59    

73

"Менеджер потоков 2.1": УПП: "Восстановление партий"

Инструменты администратора БД Платформа 1С v8.3 1С:Управление производственным предприятием Россия Бухгалтерский учет Управленческий учет Платные (руб)

Как оптимизировать то, что, считалось, не поддается оптимизации? Как повысить доступность базы данных? Как проводить самую «времяемкую» операцию не по паре раз в неделю, а по несколько раз в день*? Ответ есть!

20000 руб.

12.09.2019    11751    5    9    

7

Брандмауэр для сервера 1С Предприятие 8 - внешнее управление сеансами

Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Платные (руб)

Управление возможностью начала и возобновления сеансов пользователей по различным условиям, ограничение общего числа возможных сеансов для работы с информационной базой, резервирование возможности работы с информационной базой определенных польззователей, запрет запуска нескольких сеансов для пользователя, журнализация событий начала (возобновления) и завершения (гибернации) сеансов, ведение списка активных сеансов для информационных баз кластера серверов

3600 руб.

06.02.2017    31115    31    18    

47

Хранилище файлов на SQL

Инструменты администратора БД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Управленческий учет Платные (руб)

Привязка файлов / сканов к объектам 1С с сохранением их на SQL-сервере

12000 руб.

09.10.2019    10991    5    8    

9
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. пользователь 31.01.18 05:46
Сообщение было скрыто модератором.
...
2. a.doroshkevich 1407 31.01.18 06:20 Сейчас в теме
(0)Руслан, добрый день

Согласно документации: Значение имеют только их отношения, поэтому умножение или деление всех переменных на один коэффициент никак не повлияет на выбор планировщика. По умолчанию эти переменные определяются относительно стоимости чтения последовательной страницы: то есть, переменную seq_page_cost удобно задать равной 1.0, а все другие переменные стоимости определить относительно неё

По умолчанию:
seq_page_cost = 1
random_page_cost = 4

У Вас:
seq_page_cost = 0.1
random_page_cost = 0.4

Т.е. это точно не повлияло на выбор планировщика.

А вот: cpu_operator_cost вы уменьшили в 10 раз, скорее всего именно это дало такой эффект.
effective_cache_size = 12GB, тут тоже эффект положительный, так как по умолчанию скорее всего было 4GB, а при наличии у сервера 32GB оптимальное значение как раз 12-16GB
Если есть желание, попробуйте вернуть параметры seq_page_cost = 1 и random_page_cost = 4 и посмотреть на поведение системы.
5. Gorus 48 31.01.18 10:42 Сейчас в теме
(2)
Согласно документации: Значение имеют только их отношения

Я также сначала думал, поэтому первоначально и не изменял их, но оказалось что установка именно отношения 0.1/0.4 приводит к выбору оптимального плана. Думаю тут дело в том, что в секции "Planner Cost Constants" есть и другие настройки, и устанавливая seq_page_cost в 0.1 мы автоматом увеличиваем стоимость остальных в 10 раз, а значения random_page_cost фактически возвращаем в исходное.
3. a.ivanov 31.01.18 09:12 Сейчас в теме
Не очень понятно зачем менять именно эти глобальные параметры, что бы заработал один запрос. Не проще запрос переписать?
vasilev2015; +1 2 Ответить
9. GreenDragon 01.02.18 14:53 Сейчас в теме
(3) Т.е. для вас проще снять конфигурацию с поддержки, переписать запрос, затем следить за его сохранность от релиза к релизу... вместо тюнинга параметров в конфиге?
VVi3ard; Gang031; Gorus; +3 Ответить
4. alexkashsh 31.01.18 10:27 Сейчас в теме
Выдержка из документации 1с ( ссылка ):
Решение проблемы с зависанием PostgreSQL

При выполнения некоторых регламентных операций (Закрытие месяца, Расчет себестоимости и т.п), где используются сложные запросы с большим количеством соединений больших таблиц, возможно существенное увеличение времени выполнения операции. В основном, эти проблемы связаны с работой оптимизатора PostgreSQL и отсутствием актуальной статистики по таблицам, учавствующим в запросе.

Варианты решения проблемы:
...
Отключение оптимизатору возможности использования NESTED LOOP при выборе плана выполнения запроса в конфигурации PostgreSQL:
Файл postgresql.conf - enable_nestloop=off.
Отрицательным эффектом этого способа является возможное замедление некоторых запросов, поскольку при их выполнении будут использоваться другие, более затратные, методы соединения (HASH JOIN).
...
Изменение параметров настройки оптимизатора:
Файл postgresql.conf:
seq_page_cost = 0.1
random_page_cost = 0.4
cpu_operator_cost = 0.00025
Показать
VVi3ard; mindcannon; +2 Ответить
6. Gorus 48 31.01.18 10:59 Сейчас в теме
(4)Ну да, только установка enable_nestloop=off приводит к замедлению многих других запросов. Оно вроде небольшое, но в итоге база даных вращается достаточно вяло.
Смысл статьи в том, что возможно настроить выбор оптимального плана не отключая nestloop. Ну и еще запрос конечно - достаточно удачно получилось, что проблему можно воспроизвести на простеньком запросе. Это ведь практически готовый тестовый пример.
7. vasilev2015 2687 01.02.18 10:25 Сейчас в теме
(4) Сначала покажите, что
используются сложные запросы с большим количеством соединений больших таблиц
, тогда сможете воспользоваться вариантом решения из документации. ))

Попробуйте делать запросы для валютных и не валютных счетов отдельно, затем ОБЪЕДИНИТЬ ВСЕ результаты запросов. А то из-за пары валютных счетов очень много возни: null, сортировка и т.д.

Попробуйте убрать "Для изменения", замерить скорость и вернуть. Обязательно вернуть ))
8. Gorus 48 01.02.18 11:57 Сейчас в теме
(7) "Для изменения" у меня также был главный подозреваемый ) Но проблема оказалась все-таки не в нем а в том, что Период принимал значение момента времени и построитель выбирал неоптимальный план исполнения. Переделать запрос было можно - вариант с получением остатка на время перед документом и объединением с оборотами между этим временем и позицией документа, исполнялся нормально (0.5с). Но представленный в статье вариант получился все-таки быстрее (0.3с), да и несомненный плюс что не пришлось изменять модуль на поддержке.

Кстати, уже когда публиковал статью - встретил информацию что подобная проблема с запросами по позиции документа наблюдается также и в MS SQL: https://infostart.ru/public/362406/
Оставьте свое сообщение