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

Публикация № 733782

Администрирование - Системное

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

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

Итак, исходные данные: база 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с. Наверное это действительно оптимальные значения, и планировщик срабатывает нормально.

 

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

34

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

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

См. также

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо
2. a.doroshkevich 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 37 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 37 31.01.18 10:59 Сейчас в теме
(4)Ну да, только установка enable_nestloop=off приводит к замедлению многих других запросов. Оно вроде небольшое, но в итоге база даных вращается достаточно вяло.
Смысл статьи в том, что возможно настроить выбор оптимального плана не отключая nestloop. Ну и еще запрос конечно - достаточно удачно получилось, что проблему можно воспроизвести на простеньком запросе. Это ведь практически готовый тестовый пример.
7. vasilev2015 913 01.02.18 10:25 Сейчас в теме
(4) Сначала покажите, что
используются сложные запросы с большим количеством соединений больших таблиц
, тогда сможете воспользоваться вариантом решения из документации. ))

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

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

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