Оптимизация настроек планировщика запросов в PostgreSQL
База данных - Инструменты администратора БД
Файлы
ВНИМАНИЕ: Файлы из Базы знаний - это исходный код разработки. Это примеры решения задач, шаблоны, заготовки, "строительные материалы" для учетной системы. Файлы ориентированы на специалистов 1С, которые могут разобраться в коде и оптимизировать программу для запуска в базе данных. Гарантии работоспособности нет. Возврата нет. Технической поддержки нет.
Подписка PRO — скачивайте любые файлы со скидкой до 85% из Базы знаний
Оформите подписку на компанию для решения рабочих задач
Оформить подписку и скачать решение со скидкойВы можете заказать платную доработку или адаптацию этой разработки под вашу конфигурацию на «Бирже заказов».
- 0% комиссии — оплата напрямую исполнителю;
- Исполнители любого масштаба — от отдельных специалистов до команд под проект;
- Прямой обмен контактами между заказчиком и исполнителем;
- Безопасная сделка — при необходимости;
- Рейтинги, кейсы и прозрачная система откликов.
Итак, исходные данные: база 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с. Наверное это действительно оптимальные значения, и планировщик срабатывает нормально.
Напоследок хочу порекомендовать статьи и ресурсы которыми пользовался в процессе настройки:
Вступайте в нашу телеграмм-группу Инфостарт
