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

30.01.18

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

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

Скачать файл

ВНИМАНИЕ: Файлы из Базы знаний - это исходный код разработки. Это примеры решения задач, шаблоны, заготовки, "строительные материалы" для учетной системы. Файлы ориентированы на специалистов 1С, которые могут разобраться в коде и оптимизировать программу для запуска в базе данных. Гарантии работоспособности нет. Возврата нет. Технической поддержки нет.

Наименование По подписке [?] Купить один файл
Файл конфигурации postgresql.conf
.conf 22,45Kb
6
6 Скачать (1 SM) Купить за 1 850 руб.

Итак, исходные данные: база 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С v8.3 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Россия Платные (руб)

Инструмент представляет собой обработку для проведения свёртки или обрезки баз данных. Работает на ЛЮБЫХ конфигурациях (УТ, БП, ERP и т.д.). Поддерживаются серверные и файловые базы, управляемые и обычные формы. Может выполнять свертку сразу нескольких баз данных и выполнять их автоматически без непосредственного участия пользователя. Решение в Реестре отечественного ПО

8400 руб.

20.08.2024    14831    109    46    

108

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

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

15000 руб.

10.11.2023    11989    45    33    

67

Инструменты администратора БД Роли и права Системный администратор Программист Пользователь 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, пытаясь понять какими правами они обладают? Вы все время смотрите права в конфигураторе или отчетах чтоб создать нормальные профили доступа? Вы хотите наглядно видеть какие права дает профиль и редактировать все в простом виде? А может хотите просто указать подсистему и дать права на просмотр и добавление на объекты и не лезть в дебри прав и чтоб обработка сама подобрала нужные роли? Все это теперь стало возможно! Обновление от 18.09.2024, версия 1.2

18000 руб.

06.12.2023    10831    47    8    

78

SALE! %

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

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

4800 3840 руб.

14.01.2013    191451    1153    0    

920

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

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

14400 руб.

29.04.2020    33890    108    152    

75

Инструменты администратора БД Системный администратор Программист Платформа 1С v8.3 1C:Бухгалтерия Платные (руб)

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

3600 руб.

06.02.2017    32707    145    18    

51

Архивирование (backup) Инструменты администратора БД Платформа 1С v8.3 Управляемые формы 1C:Бухгалтерия 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:Управление торговлей 11 1С:Управление нашей фирмой 3.0 1С:Розница 3.0 Платные (руб)

Данная разработка позволит решить вопрос с резервным копированием Ваших баз в автоматическом режиме, расположенных на сервере 1С. Система умеет ставить блокировки на вход, блокировать фоновые задания, принудительно отключать сеансы пользователей. И все это система делает в автоматически при создании бэкапа (или через команду). Выгрузка происходит в родной формат 1С - .dt. Так же система умеет архивировать данные выгрузки с установкой пароля. Умеет менять расширение файла zip или dt на любое указанное вами, что позволит сохранить выгрузки от шифровальщика. Может удалять старые копии выгрузок, оставляя указанное количество резервных копий, начиная с самой поздней. Только для WINDOWS!

6000 руб.

06.11.2012    73813    629    45    

88

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

Многие сталкиваются с проблемой когда изображения, находящиеся в базе разные по объему и размерам. Менеджеры могут добавить файлы в очень высоком разрешении, объемом свыше 20 Мегабайт. База данных становится слишком большой, выгрузка на сайт идёт медленно и требуется много место на хостинге. Как сжать картинки и уменьшить размер базы 1С? Это можно сделать с помощью данной обработки. Существует возможность выбрать различные варианты для того чтобы уменьшить картинки: в разы, в процентах от первоначального объема, а также сделать картинки одинаковой ширины. В результате размер базы 1С значительно сократится (в зависимости от количества и размера картинок), а изображения станут небольшого объема, равными по ширине, почти без потери качества. Работает на управляемых формах для УТ 11, КА, ERP.

3000 руб.

21.07.2022    10200    10    4    

18
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. пользователь 31.01.18 05:46
Сообщение было скрыто модератором.
...
2. a.doroshkevich 1521 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 2746 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/
Оставьте свое сообщение