Экспертный кейс. Расследование фатального замедления времени расчета себестоимости в 1С:ERP 2

25.03.22

База данных - HighLoad оптимизация

+76 –3

При выполнении нагрузочного тестирования информационной системы на базе 1С:ERP для одного из клиентов с целью оценки возможности миграции системы на PostgreSQL и Astra Linux мы столкнулись с неприемлемым увеличением времени выполнения расчета себестоимости. Строго говоря, сценарий тестирования закрытия месяца не был выполнен вообще – он не укладывался в таймаут выполнения теста, 24 часа. По прошествии 18 часов всё ещё шло выполнение операции «Распределение затрат и расчет себестоимости». Более 16 часов выполнялся подэтап “Расчет партий и себестоимости. Этап. Расчет себестоимости: РассчитатьСтоимость”. Всё это время выполнялся запрос, который в текущей инфраструктуре клиента (СУБД MS SQL Server) выполняется чуть более 3 минут на аналогичных данных.

Инфраструктура исследуемой системы

Технологическая платформа 1С:Предприятие 8, версия 8.3.19.1264

Конфигурация: 1С:ERP Управление предприятием 2, версия 2.5.6.234

СУБД: PostgresPro Enterprise 13

Операционная система: Astra Linux Орел 2.12

Проблема

При выполнении нагрузочного тестирования информационной системы на базе 1С:ERP для одного из клиентов с целью оценки возможности миграции системы на PostgreSQL и Astra Linux мы столкнулись с неприемлемым увеличением времени выполнения расчета себестоимости.

Строго говоря, сценарий тестирования закрытия месяца не был выполнен вообще – он не укладывался в таймаут выполнения теста, 24 часа. По прошествии 18 часов всё ещё шло выполнение операции «Распределение затрат и расчет себестоимости». Более 16 часов выполнялся подэтап “Расчет партий и себестоимости. Этап. Расчет себестоимости: РассчитатьСтоимость”. Всё это время выполнялся запрос, который в текущей инфраструктуре клиента (СУБД MS SQL Server) выполняется чуть более 3 минут на аналогичных данных.

Задача

Выявить причины неприемлемого поведения СУБД при расчете себестоимости в новой инфраструктуре заказчика (Linux/PostgreSQL) и привести показатели к сопоставимым показателями текущей инфраструктуры (MS Windows/MS SQL Server).

Решение

Для удобства повествования разобьем описание процесса расследования на этапы.

Этап 1. Поиск причины длительного выполнения расчета себестоимости

На этом этапе необходимо найти зацепки, которые позволят определиться, куда двигаться в расследовании дальше. Для начала анализируем технологический журнал 1С (ТЖ). Номер сеанса задания расчета интересует в первую очередь – берем его, например, в журнале регистрации 1С. Там же видим, что последнее событие этого сеанса зарегистрировано за ~18 часов до окончания теста. В ТЖ по сеансу аналогичная картина – никаких записей в течение 18 часов до момента отмены. И здесь находим интересное:

9:36.823000-58479984999,DBPOSTGRS,5,process=rphost,p:processName=erp,OSThread=3995,t:clientID=2146,t:applicationName=BackgroundJob,t:computerName==*********\,t:connectID=266,SessionID=65,Usr=Сц_20_Бухгалтер_001,DBMS=DBPOSTGRS,DataBase=*********\erp,Trans=0,dbpid=36634,Sql="INSERT INTO pg_temp.tt521 ..... ",RowsAffected=0,Result=PGRES_FATAL_ERROR,Context='
ОбщийМодуль.ЗакрытиеМесяцаСервер.Модуль : 3513 : Обработки.ОперацииЗакрытияМесяца.ВыполнитьРасчетЭтапов(ПараметрыЗапуска);
	....
		ОбщийМодуль.РасчетСебестоимостиПрикладныеАлгоритмы.Модуль : 15340 : РезультатПодзапроса = Запрос.Выполнить();'

29:36.823003-58479985003,EXCPCNTX,4,SrcName=DBPOSTGRS,process=rphost,p:processName=erp,OSThread=3995,t:clientID=2146,t:applicationName=BackgroundJob,t:computerName==*********\,t:connectID=266,SessionID=65,Usr=Сц_20_Бухгалтер_001,DBMS=DBPOSTGRS,DataBase==*********\\erp,Trans=0

Один запрос выполнялся в течение ~17 часов (58 479 секунд в листинге выше).

Для того, чтобы двигаться дальше, у нас есть текст запроса к СУБД и контекст встроенного языка.

Этап 2. Получение плана запроса

Следующим шагом нужно определиться, что не так с запросом, выполняющимся почти 17 часов. Оптимизировать запрос наугад – соблазнительный, но неверный путь. При этом можно потратить много времени и сил, а получить устойчивый результат – увы, вряд ли. Нужен анализ плана запроса.

Если этого не было сделано ранее, включаем логирование планов запросов в postgresql.conf:

shared_preload_libraries = 'auto_explain'
auto_explain.log_analyze = true
auto_explain.log_min_duration = '0s'

Обратите внимание, что применение параметра shared_preload_libraries требует перезапуска сервера. Можно использовать альтернативу: session preload libraries

Теперь требуемый план запроса найдем в логе СУБД и приступаем к его анализу.

Этап 3. Анализ полученного плана запроса

Для анализа плана запроса воспользуемся бесплатным онлайн-инструментом https://explain.depesz.com/, который представляет его в удобном виде. В качестве альтернативы можно воспользоваться https://explain.tensor.ru/ – тоже бесплатный и полезный онлайн-инструмент.

Обработав этим сервисом полученный на предыдущем этапе план запроса, в первую очередь ищем блоки, которые выполняются медленнее всего, либо которые повторяются чаще всего – т.к. именно это в результате приводит к фатальному замедлению. Такие блоки специально выделяются анализатором отдельным цветом.

Видим, что наша беда – запрос с объединением результатов 9 подзапросов, общее время выполнения данного запроса составляет 28 034 секунд (см. строки 1-5).

Из 28 034 секунд общего выполнения запроса 27 805 секунд заняло выполнение первого подзапроса объединения (строка 6).

Рассмотрим план выполнения этого подзапроса более внимательно:

При детальном анализе видно (внимание на скриншот с подписями – можно открыть в отдельном окне): 3 проблемных левых соединения (строки 15-17) с одной и той же временной таблицей tt786. Строки 9-11 показывают, что три этих левых соединения выполняются при помощи nested loops (вложенными циклами). Первое левое соединение – строка 11, Второе – строка 10, Третье – строка 9. Время выполнения каждого такого объединения занимает по 9 265 секунд.

При этом для каждого соединения было обработано 19 838 704 480 строк, но ни одна строка не подошла под условия соединения. Видно, что временная таблица tt786 имеет индекс, но в него попало только одно условие:

Index Cond: (_q_001_f_003rref = t2._q_001_f_036rref)

Остальные условия по таблице tt786 выполнялись со сканированием:

Filter: (('\\x08'::bytea = _q_001_f_007_type) AND ('\\x000000f8'::bytea = _q_001_f_007_rtref) AND ('\\x08'::bytea = _q_001_f_010_type) AND (t2._q_001_f_037rref = _q_001_f_004rref) AND (t2._q_001_f_038rref = _q_001_f_005rref) AND (t2._q_001_f_006rref = _q_001_f_002rref) AND (t2._q_001_f_056_type = _q_001_f_006_type) AND (t2._q_001_f_056_rtref = _q_001_f_006_rtref) AND (t2._q_001_f_056_rrref = _q_001_f_006_rrref) AND (t2._q_001_f_057rref = _q_001_f_007_rrref) AND (t2._q_001_f_058_type = _q_001_f_009_type) AND (t2._q_001_f_058_rtref = _q_001_f_009_rtref) AND (t2._q_001_f_058_rrref = _q_001_f_009_rrref) AND (t2._q_001_f_059rref = _q_001_f_010_rrref))

Сканирование – операция неэффективная и приводящая к избыточному чтению. Для выяснения причин, по которым СУБД была вынуждена к нему прибегнуть, давайте изучим сам запрос.

Перейдем к тексту запроса на языке 1С. Сам текст этого запроса целиком длиннее нашей статьи, поэтому все интересующиеся могут открыть его в конфигурации и внимательно изучить, как изучали его мы. Мы же видим – в 6-м подзапросе как раз присутствуют три соединения с одной и той же временной таблицей. Т.е. проблемные соединения имеют вид:

	|		ЛЕВОЕ СОЕДИНЕНИЕ
	|			ВтУзлыКорректировки КАК УзлыКорректировкиПриемник
	|		ПО
	|			УчетСебестоимости.КорАналитикаУчетаНоменклатуры  = УзлыКорректировкиПриемник.АналитикаУчетаНоменклатуры
	|			И УчетСебестоимости.КорРазделУчета 				 = УзлыКорректировкиПриемник.РазделУчета
	|			И УчетСебестоимости.КорВидЗапасов 				 = УзлыКорректировкиПриемник.ВидЗапасов
	|			И УчетСебестоимости.Организация 				 = УзлыКорректировкиПриемник.Организация
	|			И УчетСебестоимости.КорОрганизация 				 = ЗНАЧЕНИЕ(Справочник.Организации.ПустаяСсылка)
	|			И УчетСебестоимости.КорПартия              	     = УзлыКорректировкиПриемник.Партия
	|			И УчетСебестоимости.КорАналитикаУчетаПартий      = УзлыКорректировкиПриемник.АналитикаУчетаПартий
	|			И (УчетСебестоимости.КорАналитикаФинансовогоУчета = УзлыКорректировкиПриемник.АналитикаФинансовогоУчета
	|				ИЛИ НЕ &ПартионныйУчетВерсии22)
	|			И (УчетСебестоимости.КорВидДеятельностиНДС        = УзлыКорректировкиПриемник.ВидДеятельностиНДС
	|				ИЛИ НЕ &ПартионныйУчетВерсии22)
	|
	|		ЛЕВОЕ СОЕДИНЕНИЕ
	|			ВтУзлыКорректировки КАК УзлыКорректировкиПриемникПередачи	
	|		ПО
	|			УчетСебестоимости.КорАналитикаУчетаНоменклатуры  = УзлыКорректировкиПриемникПередачи.АналитикаУчетаНоменклатуры
	|			И УчетСебестоимости.КорРазделУчета 				 = УзлыКорректировкиПриемникПередачи.РазделУчета
	|			И УчетСебестоимости.КорВидЗапасов 				 = УзлыКорректировкиПриемникПередачи.ВидЗапасов
	|			И УчетСебестоимости.КорОрганизация 				 = УзлыКорректировкиПриемникПередачи.Организация
	|			И УчетСебестоимости.КорОрганизация 				 <> ЗНАЧЕНИЕ(Справочник.Организации.ПустаяСсылка)
	|			И УчетСебестоимости.РазделУчета 				 <> ЗНАЧЕНИЕ(Перечисление.РазделыУчетаСебестоимостиТоваров.ТоварыПринятыеНаКомиссию)
	|			И УчетСебестоимости.КорПартия              	     = УзлыКорректировкиПриемникПередачи.Партия
	|			И УчетСебестоимости.КорАналитикаУчетаПартий      = УзлыКорректировкиПриемникПередачи.АналитикаУчетаПартий
	|			И (УчетСебестоимости.КорАналитикаФинансовогоУчета = УзлыКорректировкиПриемникПередачи.АналитикаФинансовогоУчета
	|				ИЛИ НЕ &ПартионныйУчетВерсии22)
	|			И (УчетСебестоимости.КорВидДеятельностиНДС        = УзлыКорректировкиПриемникПередачи.ВидДеятельностиНДС
	|				ИЛИ НЕ &ПартионныйУчетВерсии22)
	|
	|		ЛЕВОЕ СОЕДИНЕНИЕ
	|			ВтУзлыКорректировки КАК УзлыКорректировкиПриемникРеглУчет
	|		ПО
	|			УчетСебестоимости.КорАналитикаУчетаНоменклатуры  = УзлыКорректировкиПриемникРеглУчет.АналитикаУчетаНоменклатуры
	|			И УчетСебестоимости.РазделУчета 				 = УзлыКорректировкиПриемникРеглУчет.РазделУчета
	|			И УчетСебестоимости.КорВидЗапасов 				 = УзлыКорректировкиПриемникРеглУчет.ВидЗапасов
	|			И УчетСебестоимости.КорОрганизация				 = УзлыКорректировкиПриемникРеглУчет.Организация
	|			И УчетСебестоимости.КорПартия 					 = УзлыКорректировкиПриемникРеглУчет.Партия
	|			И УчетСебестоимости.КорАналитикаУчетаПартий 	 = УзлыКорректировкиПриемникРеглУчет.АналитикаУчетаПартий
	|			И (УчетСебестоимости.КорАналитикаФинансовогоУчета = УзлыКорректировкиПриемникРеглУчет.АналитикаФинансовогоУчета
	|				ИЛИ НЕ &ПартионныйУчетВерсии22)
	|			И (УчетСебестоимости.КорВидДеятельностиНДС 		 = УзлыКорректировкиПриемникРеглУчет.ВидДеятельностиНДС
	|				ИЛИ НЕ &ПартионныйУчетВерсии22)

Этап 4. Решение выявленной проблемы

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

Для начала сопоставим имена проблемной временной таблицы из плана запроса “tt786” и из текста запроса 1С – это “ВтУзлыКорректировки”.

Поля соединения временной таблицы “ВтУзлыКорректировки” во всех трех случаях идентичны, но проиндексировано только одно: “АналитикаУчетаНоменклатуры”:

	ПараметрыНумерации = РасчетСебестоимостиПрикладныеАлгоритмы.СформироватьПараметрыНумерацииСтрокВременнойТаблицы(
		"АналитикаУчетаНоменклатуры", // разделитель
		, // без группировки
		"АналитикаУчетаНоменклатуры,
			//++ НЕ УТ
			|РазделУчета,
			//-- НЕ УТ
			|ВидЗапасов, Организация, Партия, АналитикаУчетаПартий, АналитикаФинансовогоУчета, ВидДеятельностиНДС", // порядок
		"НомерУзла", // поле номера
    	"АналитикаУчетаНоменклатуры"); // индекс

Очевидно нарушен стандарт разработки, присутствующий на ИТС в разделе “Несоответствие индексов и условий запроса”. Подробнее можно прочитать: https://its.1c.ru/db/v8std#content:652:hdoc

Чтобы избавиться от сканирования по остальным полям при выполнении соединения, необходимо добавить эти поля в индекс:

	ПараметрыНумерации = РасчетСебестоимостиПрикладныеАлгоритмы.СформироватьПараметрыНумерацииСтрокВременнойТаблицы(
		"АналитикаУчетаНоменклатуры", // разделитель
		, // без группировки
		"АналитикаУчетаНоменклатуры,
			//++ НЕ УТ
			|РазделУчета,
			//-- НЕ УТ
			|ВидЗапасов, Организация, Партия, АналитикаУчетаПартий, АналитикаФинансовогоУчета, ВидДеятельностиНДС", // порядок
		"НомерУзла", // поле номера
    	"АналитикаУчетаНоменклатуры, РазделУчета, ВидЗапасов, Организация, Партия, АналитикаУчетаПартий, АналитикаФинансовогоУчета, ВидДеятельностиНДС"); // индекс

Стоит отметить, что соединение вложенными циклами для данных соединений является неудачным выбором оптимизатора запросов Postgres. В данном случае единственным “шансом на успех” было бы соединение хешированием. Эту гипотезу легко проверить, например, вручную в консоли, предварительно запретив nested loops:

SET enable_nestloop = off;

Правда, проверка в консоли осложняется необходимостью добавления запросов вставки данных во временные таблицы. Но мы проверили – hash join выполнился за 50 секунд :)

После добавления нужных полей в индекс, запускаем расчет себестоимости заново, теперь наш запрос выполняется за 4 282 секунды. Уже лучше, но далеко ещё не хорошо…

Этап 5. И снова анализ и исправление

Получив первую оптимизацию, расслабляться рано. На этом этапе нужно дополнительно проанализировать актуальный план запроса (который обновился после нашей оптимизации) и устранить очередные узкие места, если они остались. В принципе, такой повтор “от итерации к итерации” может повторяться несколько раз – просто в этой конкретной ситуации нам хватило двух :)

Открываем свежий план запроса и видим, что теперь проблемным стал 6-й подзапрос (строка 54) из объединения. В нем используется соединение вложенными циклами, хотя до этого использовался hash join и весь подзапрос выполнялся за 38 сек.

Анализируем план аналогично описанному выше процессу:

внешний цикл состоит из 3 316 651 итераций (строка 58)
вложенный цикл представляет из себя поиск по индексу и частичное сканирование временной таблицы tt904 (строка 63)
на каждой итерации было отброшено в среднем 244 строки, за все итерации 809 262 844 строк не подошло под условия соединения (строка 63)

Сопоставляем имена временной таблицы из плана запроса и текста запроса: “tt904 ” -> “ВтУзлыКорректировкиПостатейные”. Переходим к тексту запроса на языке 1С, проблемное соединение имеет вид:

Временная таблица “ВтУзлыКорректировкиПостатейные” имеет индекс по следующим полям:

|ИНДЕКСИРОВАТЬ ПО
|	СтатьяРасходов, АналитикаФинансовогоУчета

Добавляем в индекс необходимые поля:

|ИНДЕКСИРОВАТЬ ПО
|	СтатьяРасходов, 
|	АналитикаФинансовогоУчета, 
|	РазделУчета, 
|	АналитикаРасходов, 
|	Организация, 
|	Партия

После этого запускаем расчет себестоимости заново.

Этап 6. Финал

На этом этапе больше никаких итераций в исследовании планов запросов не понадобилось. Был выполнен оптимизированный запрос, получен актуальный план запроса, после анализа которого решение задачи можно засчитывать как выполненное.

План выполнения исправленного запроса имеет вид:

УРА! Запрос стал выполняться всего 225 секунд, из которых 92 секунды выполняется 6-й подзапрос: соединение вложенными циклами с временной таблицей “ВтУзлыКорректировкиПостатейные” так и осталось самой долгой частью.

Но теперь, как видно (строка 63 на скриншоте ниже), условие соединения полностью попадает в индекс лишние строки не обрабатываются и не отбрасываются:

Заметим, что в изначальной версии запроса, который выполнялся 28 тысяч секунд, проблемное соединение в 6-м подзапросе выполнялось при помощи хеширования, время выполнения составило всего 12 секунд.

Повлиять явно на выбор способа соединения таблиц оптимизатором запроса мы не можем. Поэтому если у вас есть подобный опыт и советы, как можно было бы этого добиться иначе - будем рады обсудить в комментариях :)

Итак, время выполнения запроса на Postgres стало соизмеримым с временем выполнения на MS SQL Server, проблема решена, цель достигнута.

Строго говоря, после исправления этого запроса мы столкнулись с его братом-близнецом в следующем этапе: “Расчет партий и себестоимости. Этап. Расчет себестоимости: РассчитатьСтоимостьРегл”, для которого применили аналогичные изменения. Но это уже не совсем интересно – повторяться не будем.

Автор: Алексей Белый
Эксперт по технологическим вопросам
ИТ-Экспертиза

эксперт быстродействие erp 1С:Эксперт технологическим вопросам платформа 1С деградация производительности производительность высокая нагрузка highload

+76 –3

См. также

SALE! 15%

Infostart Toolkit: Инструменты разработчика 1С 8.3 на управляемых формах

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

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

10000 руб.

02.09.2020 159473 874 399

862

Отчеты с общей формой отчета. Типовые сценарии

Механизмы типовых конфигураций Программист Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Статистическая выборка сценариев и точек изменения отчетов на общей форме ФормаОтчета в типовых конфигурациях. Примеры кода.

03.06.2024 4900 Serg2000mr 32

108

Анализируем SQL сервер глазами 1С-ника

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

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих запросов на sql, ожиданий, конвертация запроса в 1С и рекомендации, где может тормозить.

2 стартмани

15.02.2024 12426 241 ZAOSTG 81

115

Шаблоны новых объектов 1С для 1С:Бухгалтерии предприятия

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

Используются для создания новых объектов в конфигурации, чтобы не забыть, что нужно сделать. Сделано на примере 1С:Бухгалтерия предприятия, в других конфигурациях могут быть другие, а могут быть и похожие объекты.

28.12.2023 6428 mrXoxot 11

112

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023 19326 skovpin_sa 15

105

MS SQL Server: изучаем планы запросов

Запросы HighLoad оптимизация Программист Запросы Бесплатно (free)

Многие знают, что для ускорения работы запроса нужно «изучить план». При этом сам план обычно обескураживает: куча разноцветных иконок и стрелочек; ничего не понятно, но очень интересно! Аналитик производительности Александр Денисов на конференции Infostart Event 2021 Moscow Premiere рассказал, как выполняется план запроса и что нужно сделать, чтобы с его помощью находить проблемы производительности.

20.06.2023 28187 Филин 37

118

Все консоли запросов для 1С

Запросы Инструментарий разработчика Программист Бесплатно (free)

Список всех популярных обработок.

17.03.2023 60051 kuzyara 89

190

1С и Postgres: первый серьезный опыт борьбы и противостояния, постигаем open source

HighLoad оптимизация Программист Платформа 1С v8.3 Россия Бесплатно (free)

А все начиналось с распространенной для этой связки проблемы закрытия месяца…

27.02.2023 12815 shakun_dg 31

123

Комментарии

Подписаться на ответы Инфостарт бот

Свернуть все

1. ivanov660 4578 25.03.22 12:38 Сейчас в теме

1. Почему бы вам не воспользоваться обработкой подстановки таблиц 1с в тексты плана (конвертер SQL в 1С? Очень удобно смотрится. Становится хоть понятно что с чем соединяется.
2. Я рекомендую смотреть прежде всего планы на сайте https://explain.tensor.ru/ , а не https://explain.depesz.com/. Т.к. на ru сайте есть все тоже что и на буржуйном, плюсом офигенные схемы-графики. И на сколько я помню, на ихнем были кое какие косяки при анализе, которые не поправили.
3. Мы частично сделали еще некоторые другие выводы и исправления, кроме индексов.
Посмотрите внимательно (здорово порезали картинки, но все же осталось) - видно что планировщик лажает в предсказаниях (rows=1, а по факту rows=2000 и более), соответственно он логично ставит Nested Loop. Да и упростить ещебы этот запросик.
4. На мой скромный взгляд, оформление и подача материала уже лучше, но все же пока еще не еще) К примеру, приводите вырезку запроса и обрезаете самое важное - основную таблицу facepalm. Также на полученные планы влияют значительно условия и даже выбираемые поля.
5. Я бы добавил в выводы: то как написан расчет себестоимости от вендора - это боль-больная.
Предполагаю тестирование у них этого блока ограничивалось данными демонстрационной базы и не проводилось на базах приближенных к рабочим. На postgres похоже запускает только команда postgres-pro, совсем по все конфе все плохо.

Ответить

2. it-expertise 345 25.03.22 13:39 Сейчас в теме

(1) Благодарим за внимание к нашей статье! Мы старались учесть пожелания по предыдущей ;)

К слову, сервис explain.tensor.ru знаем и уважаем, поэтому сразу упомянули его в статье в качестве приемлемой и удобной альтернативы.

4. ivanov660 4578 25.03.22 14:48 Сейчас в теме

(2) Я как раз имел ввиду мысль наоборот: https://explain.tensor.ru/ основной, а https://explain.depesz.com/ - это приемлемая альтернатива. У нас совсем разные подходы - вкус и цвет у каждого свой.

3. capitan 2507 25.03.22 14:48 Сейчас в теме

Да. Эта статья на порядок круче предыдущей.
Все супер.
Ловите плюс с плюсом.
Я бы еще сходил к разработчикам из postgres, как вариант на форумы.
Это в их огород камень но с 90% вероятностью он настройками может быть решен.
ИМХО
Планировщик не смог корректно определить размеры временной таблицы, как вариант вообще не успел/не стал для нее статистику строить.
Это можно настройками прикрутить.
Ведь MS тоже не хватало индексов но он справился.
postgresql.conf же не секретный, можете его показать?

5. ivanov660 4578 25.03.22 14:59 Сейчас в теме

(3) По моему опыту сбалансированную настройку особо и не прикрутишь, чтобы себестоимость считалась и одновременно обычная оперативная деятельность не глючила. Может кто поделится есть ли вообще для poostgres правильная "магия" настройки конфига 1С, чтобы он не ошибался для подобных случаев.
Обычно онлайн анализ временных таблиц включается online_analyze.table_type = 'temporary' - это допилка, как я понимаю ребят из postgres-pro. Еще есть default_statistics_target, который берет диапазон данных (range 1-10000). На экспериментах скажу так, ставим большую выборку статистики, то при размерах таблиц в сотни миллионов записей приземляется временем расчета этой самой статистики, а наоборот ловим неадекватность. Использовать плагин адаптивной оптимизации запросов?

6. IT_Magnit 25.03.22 15:07 Сейчас в теме

(5) Универсальной настройки нет, которая бы удовлетворила все запросы.
Мы, например, на всех серверах Постгрес используем одни и те же настройки, а проблемные запросы, например, как в этой статье, оптимизируем - без этого никуда.

7. capitan 2507 25.03.22 15:24 Сейчас в теме

(5)
(6)
Я к этому и веду, что есть люди из Postgres Pro, они пилят его под 1С в том числе и к ним надо постучаться.
Насколько я помню штатный от 1С патч как раз исправляет/пытается исправить эту проблему - ошибку планировщика на больших временных таблицах.
Это вечная проблема Postgres + 1С и пинать сильно разработчиков конфигурации наверное не стоит.
19 000 000 строк во временной таблице навряд ли они ожидали.

8. IT_Magnit 25.03.22 15:28 Сейчас в теме

(7) Какой штатный патч от 1С пытается исправить проблему ошибки планировщика на больших таблицах?

12. capitan 2507 25.03.22 15:41 Сейчас в теме

(8)Не корректно написал.
Просто временных таблицах

9. ivanov660 4578 25.03.22 15:30 Сейчас в теме

(7)
1. я так понимаю, что online_analyze как раз их патч для 1с по этой проблеме, остальные доработки доступны только в postgres-pro.
2. стоит пинать разработчиков конфигурации, т.к. если "по феншую" переписать, то все начинает нормально работать.

10. capitan 2507 25.03.22 15:33 Сейчас в теме

(9)

если "по феншую" переписать, то все начинает нормально работать.

На MS же нормально работает?

14. ivanov660 4578 25.03.22 16:49 Сейчас в теме

(10) То что относительно адекватно работает, не значит, что и дальше надо продолжать кодить в таком же духе. У меня достаточно примеров накопилось плохой работы на СУБД из-за косяков разработчиков. Руки дойдут оформлю статью.

11. IT_Magnit 25.03.22 15:39 Сейчас в теме

(9) Этот патч обновляет статистику временных таблиц. Но решить проблему, описанную в кейсе, он не поможет.

13. capitan 2507 25.03.22 15:45 Сейчас в теме

(9)
(11)
Я у автора попросил полный файл настроек приложить.
Потому как у 1С буквами по белому описаны настройки которые надо применить, если оптимизатор ошибается в оценке количества строк
https://its.1c.ru/db/metod8dev/content/5866/hdoc
это взялось как раз из таких внедрений
если настройки правильные то можно пинать разработчиков платформы и далее по списку
а если нет то нет

15. it-expertise 345 25.03.22 17:37 Сейчас в теме

(5) По просьбе Лены Скворцовой передаю её комментарий:

"Начиная с Платформы 8.3.13, статистика во временным таблицам обновляется после вставки во временные таблицы непосредственно Плафтормой, о чем недвусмысленно свидетельствуют соответствующие записи в ТЖ. Использование online_analyze с table_type = 'temporary' имеет zero смысла и вовсе не zero стоимости, в частности, процессорного времени"

Ответить

19. ivanov660 4578 26.03.22 12:16 Сейчас в теме

(15) Вполне возможно. Но если это так, то почему этого вендор не написал и у них до сих пор на итс висит конфиг с текущими настройками?

26. starik-2005 3088 27.03.22 21:37 Сейчас в теме

(19) у них там нерабочий вариант собрать постгрес уже сто лет как висит. При том есть отличный вариант поменять менеджер памяти для платформы, что ускоряет на больших данных систему на 10-30%, но у меня такое впечатление, что об этом вообще никто не знает.

А по поводу настроек, то была маза отрубить nested loop, но тут фигня будет начинаться, когда одна очень большая и очень маленькая таблицы будут мержиться, хеш будет строиться по большой таблице, а поля окажутся низкоселективными с огромным количеством коллизий.

В общем постгрес плохо пока умеет определять, когда надо одно применять, а когда другое. Но в данном конкретном случае проблема была в том, что постгрес неверно оценил размер соединяемых таблиц, поэтому выбрал вроде бы православный nested loop, при этом промахнулся в результатах и по много часов фактически крутил цикл.

37. ivanov660 4578 28.03.22 13:02 Сейчас в теме

(26)

При том есть отличный вариант поменять менеджер памяти для платформы, что ускоряет на больших данных систему на 10-30%, но у меня такое впечатление

Что под этим подразумевается (dynamic_shared_memory_type, для linux posix, для win - windows)?

39. starik-2005 3088 28.03.22 13:07 Сейчас в теме

(37)

Что под этим подразумевается

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

42. sirbusby 28.03.22 15:48 Сейчас в теме

(39)
Платформа давно идет с TCmalloc, чуть ли не с 15-ой версии. Вы наверное про это https://its.1c.ru/db/metod8dev/content/5954/hdoc

48. starik-2005 3088 28.03.22 17:44 Сейчас в теме

(42)

Платформа давно идет с TCmalloc, чуть ли не с 15-ой версии.

Отлично! Но я так понимаю, что можно и с другими менеджерами ее запустить. Народ получает в Гилеве +10% с mimalloc - там все от нагрузки зависит, от объема выделяемой и освобождаемой памяти. У постгреса свой менеджер памяти, но у них там есть ключи компиляции, чтобы получить некоторый прирост при многопоточной нагрузке и прочие фишки, но с этим вообще почти никто не умеет работать.

40. starik-2005 3088 28.03.22 13:16 Сейчас в теме

(37)

Что

https://blog.programs74.ru/how-to-use-jemalloc-and-tcmalloc-in-oracle-mysql-on-debian-9/

Jemalloc является высокопроизводительным менеджером распределения оперативной памяти, который для платформы Linux реализован в виде самостоятельной библиотеки. Jemalloc является оптимизированным вариантом реализации функций malloc, который призван решать проблемы с фрагментацией при выделения памяти в несколько потоков возникающие на однопроцессорных и многопроцессорных системах и оптимальной утилизации ресурсов CPU. Применение jemalloc даёт возможность повысить производительность всей системы, уменьшив фрагментацию и как результат понизить потребление оперативной памяти (RAM). Jemalloc изначально был написан Джейсоном Эвансом (Jason Evans) для FreeBSD, а потом портирован на платформу Linux.

TCMalloc (Thread-Caching Malloc) является аналогом Jemalloc от компании Google.

Дополню, что в репах Дебиана версия jemalloc старая.
Приходится качать и компилировать из сурсов.

18. Gilev.Vyacheslav 1917 26.03.22 10:07 Сейчас в теме

(5) сбалансированную нет, а на время расчета ни кто не запретит подкрутить, а потом вернуть

16. it-expertise 345 25.03.22 17:39 Сейчас в теме

(3) Благодарим за отзыв и плюс.

Весь конфиг публиковать здесь не будем.
Если интересуют конкретные какие-то параметры из него, спрашивайте - попробуем опубликовать.

17. Gilev.Vyacheslav 1917 26.03.22 10:04 Сейчас в теме

удивительно, но эта статья хороша вовсе не "экспертным" кейсом, а самим наглядным примером почему PostgreSQL это не бесплатный аналог MS SQL Server
а то сейчас многие компании пользуются страхами людей и всем обещают кисельные берега и молочные реки на бесплатном аналоге скуля

а здесь вполне наглядно показано - прежде чем переходить, возьми как люди в этой компании и для начала расчет себестоимости запусти и сравни, а только потом принимай решение о переходе, ведь надо потратить время, деньги и человеческие ресурсы на повторное допиливание системы под новую субд, а не так что руководства дало указание завтра всем перейти на постгрес

но мало того что надо допиливать, так 90% 1С программистов не имеют навыков работы с планами запросов этой субд, а тут такая "радость", и руководство начинает задаваться вопросом - а за что мы им платим деньги, разве у нас не хорошие программисты

очень удачная тема

Ответить

24. ip0593 20 27.03.22 20:22 Сейчас в теме

(17)то есть это кейс не "экспертов", а "джуниоров"?

33. Gilev.Vyacheslav 1917 28.03.22 06:10 Сейчас в теме

(24) ну точно не для тех у кого ты либо джуниор либо сраз эксперт )))
статья для тех кто принимает решение из умозрительных соображений без предварительной практической проверки
что касается самой экспертизы, тут несколько аспектов:
1. новизны нет, планы запросов много раз разбирались, подобных статей хватает, я бы понял если бы они что то новое показали, вот вас в школе читать учат, после того как вы научились это делать - вы эксперт?
2. локализовать проблему парни смогли - да, молодцы, но вот с теорией и объяснениями я бы не назвал это образцово-показательным, да они и сами не отрицают и просят в комментариях поделится опытом
я бы в статье осветил для начала что они предприняли для актуализации самой статистики таблиц прежде чем залазить в планы, а то может там регламенты по обсчету статистики не выполняются
но зная как вам подобные любят приписывать свои фантазии мне, отмечу, что качество экспертизы в этой статьте значения не имеет, статья не этим представляет интерес, а фокусированием внимания что один и тот же код на разных субд вовсе не обязательно будет достаточно хорошо работать, и просто так соскакивать на бесплатный постгрес мало того что потянент скрытые расходы на оптимизацию, так еще и времени на это потребоваться может много, да еще людей найти с такой квалификацией надо

Ответить

73. capitan 2507 29.03.22 18:07 Сейчас в теме

(33)Вячеслав, где подвиги вашей команды?
Последняя статья ваша : 31 марта — Международный день резервного копирования от 31.03.2016 )
А эти люди красавчики
Сделали и показали сообществу.
За это им респект и уважение.

Ответить

76. Gilev.Vyacheslav 1917 29.03.22 19:49 Сейчас в теме

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

43. sirbusby 28.03.22 15:58 Сейчас в теме

(17)
Вот только в описываем кейсе совсем не бесплатный, а очень даже платный PostgresPro Enterprise 13.

45. Gilev.Vyacheslav 1917 28.03.22 17:27 Сейчас в теме

(43) вы полагаете что в бесплатном кардинально лучше? )))
по моему опыту частенько ещё хуже в бесплатной версии

72. capitan 2507 29.03.22 18:04 Сейчас в теме

(17)то что PostgreSQL нужно гораздо дольше настраивать чем MS это жеваная пережеваная тема.
С появлением Postgres Pro кстати теряющая актуальность
Тем не менее перейти можно достаточно спокойно на среднего размера базах.
Думаю людей у которых 19000000 записей в расчете себестоимости здесь можно по пальцам пересчитать.
Вполне все будет штатно работать и бояться нечего.

84. cdiamond 235 08.04.22 06:21 Сейчас в теме

(17) Вячеслав, ты продолжаешь жить в каком-то выдуманном мире. Никто страхами людей не пользуется, у клиентов требование получить равноценную замену MSSQL в текущих обстоятельствах вообще не возникает! Главное требование - чтобы вообще работало, потому что начиная с марта 22 года выбора между MSSQL или PostgreSQL не существует. Выбор только в головах у теоретиков и тех кто не брезгует пиратством.
Сейчас первая волна линуксов пошла на новые внедрения и серверы у тех, кто не успел купить, вторая волна пойдет когда сроки годовых подписок закончатся.

86. Gilev.Vyacheslav 1917 08.04.22 09:52 Сейчас в теме

(84) Да как скажешь, только программу можно установить быстро, со знаниями людей так не работает, вот нельзя рынок одних специалистов по щелчку пальца поменять на других
если даже просто конфигурации 1С сами по себе иногда года внедряются, то что говорить о более сложном уровне, где людям нужны годы чтобы вкатится.
А про теоретиков да, смешно было.
https://vk.com/video8507976_456239084
https://vk.com/video-132543994_456239019
https://vk.com/video-132543994_456239017
Вы то как раз и живете в выдуманном мире, чего то там на фантазировали, а что в ваши фантазии не умещается, ищете на кого свои несостыковки спроецировать
на текущий момент ситуация не устоявшаяся, это госам светит астра и pg, а вот с коммерцией всё сложнее, сейчас и вендоры, и заказчики находятся в режиме ожидания и не делают резких движений.
Приобретение ПО, проекты по внедрению заморожены не столько из-за санкций, сколько из-за нестабильного курса валют и невозможности планировать бюджеты

87. cdiamond 235 08.04.22 11:05 Сейчас в теме

(86) В вашей компетенции нисколько не сомневаюсь. Теоретики это те, кто сейчас сидит в РФ/РБ и полагает что может сейчас обратиться куда-нибудь и купить легально недостающие лицензии ms/oralce/vmware и т.д. на проект. Выжидают те к кого пока всё есть, я не про этот случай.

91. Gilev.Vyacheslav 1917 08.04.22 14:32 Сейчас в теме

(87) Так у большинства ПОКА именно этот случай, и это называется прагматизм.
Надо быть идеалистом чтобы думать что малоинсталируемое будет также хорошо отлажено и работающее как то что ставится в 95% компаний. Я то написал о неравнозначности, а не об осутствии проблем с санкциями.
"Простой" вопрос - сколько надо денег на допиливание кода ЕРП под PostgreSQL ? А если там будет еще и проц старый? А если там будет "байкал/эльбрус"? и т.п. и т.д.
Все эти игры с импортозамещением не то чтобы фарс, некоторый толк наверно есть, но в основе своей тупое рубилово бабла на перевнедрение с минусом для бизнеса.

93. XAKEP 24.01.24 20:20 Сейчас в теме

(17)
есть случаи, когда и mssql не на высоте
и проблема не в них, а в 1С.

20. fregl 26.03.22 19:48 Сейчас в теме

(16) Спасибо за статью, весьма познавательно.

Не пробовали на ПГ_14 моделировать, планировщик аналогично ведет себя?

34. it-expertise 345 28.03.22 11:03 Сейчас в теме

(20) пока не пробовали ;)

Прикрепленные файлы:

57. fregl 28.03.22 20:07 Сейчас в теме

(34) На практике сейчас столкнулись с плохой работой планировщика в ПГ10 на таблице в 60млн. строк, ситуация очень близкая к вашей, большие "лупы" и малая фильтрация. Делаем реиндекс, все работает быстро(он ведь стату по всей таблице делает), делаем аналайз и выборка сокращается до 30тыс. страниц и время отчета увеличивается в 10 раз. Апгрейд на 11-12-13ю версию проблемы не решает., но на 14-й версии ПГ планировщик при тех же настройках ведет себя адекватно. Так что пока могу однозначно советовать пощупать, тем боле по ПГ14 понизили требования до 18й платформы(ранее 20я).

21. fregl 26.03.22 19:56 Сейчас в теме

(17) Отчасти верно. Понятно, что МС прощает сильно больше, но это не отменяет факта кривого кода вне стандартов разработки, т.ч. хорошо, когда программист понимает нюансы ПГ, но если бы он изначально написал правильно, то и статья не появилась.

22. Gilev.Vyacheslav 1917 27.03.22 00:49 Сейчас в теме

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

Ответить

23. quazare 3801 27.03.22 18:22 Сейчас в теме

я предположу, что мало кто будет заморачиваться с подобными вещами - но ваше "ИЛИ НЕ &ПартионныйУчетВерсии22" в запросе будет сводить всю оптимизацию на нет.

27. starik-2005 3088 27.03.22 21:39 Сейчас в теме

(23) ну тут или с булево, что погоды вообще не делает.

35. s22 22 28.03.22 12:05 Сейчас в теме

(23)

Вроде 1с устраняет это на этапе преобразования в скл

25. capitan 2507 27.03.22 21:20 Сейчас в теме

Поскольку тут метнули камень в огород разработчиков 1С и постгри заодно, а человек я не ленивый ~~в меру~~ то взял и собрал тестовый стенд для проверки сказанного в этой публикации.
А именно пустую конфигурацию и обработку, которая создает в запросе две временные таблицы, заполняет их миллионом строк из таблиц значений и соединяет левым соединением.
По логике авторов статьи postgres должен пойти Nested Loops, MS SQL Hash Match Join.
Не подтверждаю эту логику от слова совсем.
Оба сервера идут путем Hash Join
И с одинаковой скоростью выполнения

И индексирование полей соединения временных таблиц им не помогает от слова совсем
С чего бы Hash Join должно было индексирование помогать?

hash match же всю таблицу забирает не так ли ?

Наоборот индексирование замедляет операцию в целом.
В случае описанном в публикации, возможно этого времени как раз хватило на обновление статистики и postgres пошел другим путем
Если кто желает проверить - могу описать в отдельной публикации

ИМХО
Как некоторое резюме: нужна очень-очень специфическая база, чтобы в ней словить подобные глюки при переходе MS SQL -> postgres
или очень-очень неправильные настройки postgres )

И чтобы два раза не бегать

статистика во временным таблицам обновляется после вставки во временные таблицы непосредственно Плафтормой, о чем недвусмысленно свидетельствуют соответствующие записи в ТЖ

версия платформы у меня 8.3.13, и я никаких недвусмысленных записей не нашел в ТЖ, да и двусмысленных тоже
Можно еще раз побеспокоить Елену Скворцову и выложить здесь буквы этих недвусмысленных записей?
Спасибо

28. starik-2005 3088 27.03.22 21:54 Сейчас в теме

(25) Так логика не в том, что постгрес выбирает nested loop, а MS SQL -hash join. Ни ту, ни другую систему не писали люди, которые не ориентируются в базовых командах для слияния потоков данных - это ж дискретная алгебра и все такое, которому учат на первых курсах колледжей и прочих учебных заведений чуть получше. Тем более, что постгрес начали пилить в Беркли, а это одно из самых именитых учебных заведений.

Вообще, в теории есть несколько всего методов соединения - тот самый nested loop, который фактически идет по ключам первой таблицы и ищет их во второй. Если ключи в индексе (читай - упорядоченном списке), то поиск происходит в худшем случае (нет ключа) за O(Log2N), где N - количество строк в индексе. Если ключи не в индексе, то поиск происходит за N. Это я классе в 7-м еще узнал, а кое-кто вообще почему-то до старых лет не разобрался (не говорю про Вас лично, но сдается впечатление, что вообще мало кто эти базовые алгоритмы знает). В итоге у нас тут или M * Log2N, или M * N. Если же у нас в обоих таблицах поля в индексе, то соединение вообще может быть произведено за MAX(M, N) итераций. Поэтому Nested Loop используется или для таблиц с ключами, или для соединений большой и маленькой таблицы (при этом маленькая таблица вполне может быть упорядочена по ключам).

Hash join предполагает создание на основе какой-то из соединяемых таблиц отдельной таблицы хеш-функций от ключей соединения. Это требует много памяти - зависит от размера ключа (2^N байт, где N - это размер ключа). Чем меньше размер ключа, тем больше коллизий. Чем ниже селективность индекса - тем больше коллизий. Если коллизий больше 30%, то, по мнению сайта мелкомягких, скорость соединения начинает стремительно деградировать. Поэтому такой подход применяется для высокоселективных и больших таблиц. Низкоселективные проще просканировать по низкоселективным полям и объединить.

Также есть соединение по битовой маске/матрице. Я в уме понимаю, как это можно использовать, но точно про СУБД не скажу, в каких кейсах оно может быть применено.

В итоге проблема не в том, что та или иная система не умеет применять ту или иную стратегию соединения, а в том, что в данном конкретном случае постгрес не смог применить hash join или из-за того, что не правильно оценил размер соединяемых таблиц, или из-за того, что у него просто памяти на это нет рабочей. Хотя в последнем запросе 160 киб всего использовано для хеш-таблицы (если я правильно понял), так что странно, что памяти могло не хватить...

29. capitan 2507 27.03.22 22:16 Сейчас в теме

(28)
Посыл автора статьи в том что они перешли с MS SQL на Postgres и у них сломался расчет себестоимости
Потому что Postgres начал выбирать nested loops вместо hash join.
Они проиндексировали временную таблицу по полям соединения и стал hash join на Postgres
Так вот hash join он как раз планировщиком выбирается когда его индексы не устраивают - планировщик понимает что соединить таблицы целиком будет выгоднее, чем циклы гонять.
Поэтому для hash join как раз индексирование как зайцу стоп-сигнал
И память тут не при чем.
Он так же сбросил бы на диск
Проблема в том почему планировщик выбирал nested loops на таблице 19 000 000 записей.
А потому что он не знал сколько там записей - у него не было статистики по этой таблице, без статистики планировщик в Postgres считает что в таблице 10 страниц
nested loops планировщик выбирает когда 100% маленькие таблицы или когда он 100% не понимает что делать

30. starik-2005 3088 27.03.22 22:25 Сейчас в теме

(29)

Они проиндексировали временную таблицу по полям соединения и стал hash join на Postgres

Если я правильно понял, то у них для соединения в итоге остались только актуальные данные, а данные, которые в предыдущих вариантах отбрасывались, просто "исключились" из финального (или какого там) соединения из-за индекса. В итоге разрядность хеша попала в доступный объем памяти и, о чудо, заработал hash join (считай, что +1 бит хеша = в два раза больше памяти для таблицы), т.к. work_mem для него стало хватить (вот, кстати, и параметр конфига: https://wiki.postgresql.org/wiki/Hash_Join). С другой стороны, postgresql может юзать и больше, чем work_mem, для построения хеш-таблицы, но, как я понял, в виде некоторого исключения.

31. capitan 2507 27.03.22 22:29 Сейчас в теме

(30)

Я такого не увидел, может между строк )
Так то у них написано буквами:

Добавили поля в индекс и стал hash join которому индекс по фигу вообще

32. starik-2005 3088 27.03.22 22:31 Сейчас в теме

(31)

Я такого не увидел, может между строк )

Может.

36. s22 22 28.03.22 12:50 Сейчас в теме

(31)

Добавили поля в индекс и стал hash join которому индекс по фигу вообще

Какая связь?

38. starik-2005 3088 28.03.22 13:04 Сейчас в теме

(36) если рассматривать запрос из двух временных таблиц, как сделал Капитан (Андрей), то никакой. Если же таблиц много, запрос большой, красивый, многополевой, то оптимизатор запроса попытался посчитать, сколько надо чего откуда прочитать, какие операторы к прочитанному применить и в конце концов не смог все осилить и оставил какой-то план, до которого успел досчитать. В итоге индексация временной таблицы помогла снизить нагрузку на оптимизатор, что позволило посчитать чуть дальше и отбросить часть данных еще на вставке во временные таблицы.

41. capitan 2507 28.03.22 14:59 Сейчас в теме

(38)

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

Это хорошее предположение, но есть нюансы)
hash join в любом случае считает хеш по полям соединения, от того, что по ним по отдельности построен индекс ничего не поменяется.
И совсем непонятно почему часть данных будет отброшена, кто ее отбросит?

46. starik-2005 3088 28.03.22 17:30 Сейчас в теме

(41)

И совсем непонятно почему часть данных будет отброшена, кто ее отбросит?

1. 9к+ секунд.

При этом для каждого соединения было обработано 19 838 704 480 строк, но ни одна строка не подошла под условия соединения.

2. 4к+ секунд.

После добавления нужных полей в индекс, запускаем расчет себестоимости заново, теперь наш запрос выполняется за 4 282 секунды. Уже лучше, но далеко ещё не хорошо…

3. 225 секунд.

В данном случае соединение вложенными циклами крайне неудачный выбор оптимизатора, но поскольку явно повлиять мы на это не можем, то пробуем добавить в индекс временной таблицы tt904 все поля, используемые в соединении.
...
Но теперь, как видно (строка 63 на скриншоте ниже), условие соединения полностью попадает в индекс лишние строки не обрабатываются и не отбрасываются:

Вообще, там далеко не только hash join - все эти nested loop тоже остались, просто начали работать по индексам, поэтому производительность выросла. Ну и агрегация производилась не сортировкой на диск, как в первом случае, а той же hashAggregate.

Но вообще на основании предоставленной информации о паре всего строк запроса и только некоторых строках плана достаточно сложно судить о том, что поменялось и как. Факт лишь в том (если авторы не обманывают), что скорость выполнения запроса с 17 часов (или сколько там) снизилась до менее, чем пяти минут. Почему? Предположу, что разобраться на 100% вообще мало кто сможет.

И как сказал Слава, что ценность статьи - это очередное упоминание о том, что нельзя вот так просто взять и перейти на другую СУБД, думая, что все будет "зе бест". Нужны функциональные тесты, которые обязательно (99%) потребуют рефакторинга отдельных участков кода. И если для документооборота вроде бы таких больших и красивых запросов по выборке сложно связанной аналитики для формирования дерева разузлования себестоимости нет - рефактиринг может быть дешев и даже необязателен, то в таких системах, как ERP, нужно закладывать существенный бюджет на рефакторинг, т.к. специалисты, которые в принципе смогут тут помочь разобраться, достаточно дорого стоят (т.к. их в принципе на всех не хватит).

51. it-expertise 345 28.03.22 17:55 Сейчас в теме

(46)

если авторы не обманывают

обман - не наш путь!

52. capitan 2507 28.03.22 18:00 Сейчас в теме

(46)
(51)
Я один что ли публикацию прочитал?
Там написано буквами:

Показать

И вуаля до этого картинка с nested loops а после этого картинка с hash match
Никаких новых отборов там в запросе нет

47. Gilev.Vyacheslav 1917 28.03.22 17:39 Сейчас в теме

(41) я отвечу не конкретно по данному случаю - тут не был на самом деле представлен полный контекст, а по общему подходу:
- вы тест делаете как принято в фирме 1С - т.е. в вакумме на только что созданной таблице со свежей вставкой - что тут нет так - нет шансов устареть статистике, накопиться версиям данных и прочей истории
в статье таблицы перед экспериментом не причесываются, а все делается "находу", с историческими следами
- в статье не оценивается железо, если там унылые диски, то я бы тупо значительно увеличил "стоимость скана", провоцируя выбрать по индексу просто для эксперимента и локализации проблемы
- у данной конкретной проблемы есть куда обратиться - в PG Pro - вполне можно им написать контекст проблемы (и всё что они попросят) и попросить прокомментировать работу оптимизатора, куда тот смотрит и из каких соображений выбирает вариант - это НЕ MS с закрытым кодом, и можно довести работу до конца и разобраться в причинах (ведь ошибка может быть и в движке оптимизатора), тогда бы я и за саму экспертизу плюсанул

50. capitan 2507 28.03.22 17:55 Сейчас в теме

(47)

у данной конкретной проблемы есть куда обратиться - в PG Pro

Я именно так и сказал в первых строках
Что касается статистики, с этим разобрались с помощью Елены Скворцовой, она всегда свежая.
Я невнимательно ТЖ просмотрел.
Отсюда совсем непонятно почему планировщик выбирает вложенные циклы зная про 19000000 строк
И уже совсем непонятно как это индексированием вдруг пролечилось

54. Gilev.Vyacheslav 1917 28.03.22 19:04 Сейчас в теме

(50) как вы поняли что она всегда свежая?

55. capitan 2507 28.03.22 19:23 Сейчас в теме

(54)За запросом следует ANALYZE.

56. Gilev.Vyacheslav 1917 28.03.22 19:34 Сейчас в теме

(55) это хорошо, но не достаточно
мне вот не видно например число строк таблицы, выбираемых для подготовки статистики
не видна селективность данных
я уверен, что команда в БОЛЬШЕЙ ТАБЛИЦЕ не перебирает все строки, а случайную выборку делает, что дает приближенную картину и при неоднородном содержимом может привести к ошибочному выбору

58. fregl 28.03.22 20:17 Сейчас в теме

(55) Только вот аналайз делает статистику по случайной частичной выборке(на больших таблицах) и планировщик может промахнуться.

# analyze verbose _inforg23326;
ИНФОРМАЦИЯ: анализируется "public._inforg23326"
ИНФОРМАЦИЯ: "_inforg23326": просканировано страниц: 30000 из 1218896, они содержат "живых" строк: 1133885, "мёртвых" строк: 0; строк в выборке: 30000, примерное общее число строк: 46069596
ANALYZE
--------------------
* 46млн строк
* страниц: 30000 из 1218896, они содержат "живых" строк: 1133885 - 1,1млн основа статистики из 46млн.
* default_statistics_target - регулирует объем выборки

59. capitan 2507 28.03.22 21:12 Сейчас в теме

(56)
(58)
Как я уже сказал я собрал тестовый стенд и планировщик на непустых таблицах у меня по крайней мере ни разу не выбрал вложенные циклы
Подозреваю как раз понимание что таблица БОЛЬШАЯ уже должно наводить планировщик на мысль что вложенные циклы это плохо.
Он именно не смог по таймауту или по памяти или еще по каким то настройкам выбрать тип соединения в принципе.
А когда планировщик не может выбрать тип соединения он идет вложенными циклами
Так в MS так же точно и в Postgres

44. it-expertise 345 28.03.22 16:37 Сейчас в теме

(25)
25:13.489001-15994,DBPOSTGRS,5,process=rphost,p:processName=Expert_ERP,OSThread=16940,t:clientID=8410,t:applicationName=1CV8C,t:computerName=ITE-MP-00159,t:connectID=14,SessionID=3,Usr=Administrator,AppID=1CV8C,DBMS=DBPOSTGRS,DataBase=localhost\Expert_ERP,Trans=0,dbpid=17244,Sql="INS ERT IN TO pg_temp.tt6 ...",RowsAffected=571,Result=PGRES_COMMAND_OK
25:13.489004-1,DBPOSTGRS,5,process=rphost,p:processName=Expert_ERP,OSThread=16940,t:clientID=8410,t:applicationName=1CV8C,t:computerName=ITE-MP-00159,t:connectID=14,SessionID=3,Usr=Administrator,AppID=1CV8C,DBMS=DBPOSTGRS,DataBase=localhost\Expert_ERP,Trans=0,dbpid=17244,Sql=ANALYZE pg_temp.tt6,RowsAffected=0,Result=PGRES_COMMAND_OK

За запросом следует ANALYZE.

Разумеется, как уже отметили, вся таблица не будет просматриваться.

49. capitan 2507 28.03.22 17:46 Сейчас в теме

(44)

За запросом следует ANALYZE

Не зря я вам поставил плюс)
Спасибо

85. cdiamond 235 08.04.22 06:58 Сейчас в теме

(44) при ANALYZE просматривается количество строк, указанное в конфигурационном файле.
Но мало кто помнит что есть есть ALT ER TABLE table ALTER column SET STATISTICS {-1 ..10000}, т.е.настройки можно сделать индивидуальными для проблемных таблиц и его колонок.
Сейчас непременно скажут что это нарушение лицензионного соглашения 1С, но в данном случае мы меняем конфигурацию статистики, а не структуру базы данных, так что вопрос неоднозначный.
И кстати MSSQL тоже всю таблицу не просматривает при выполнении обновления статистики онлайн, там у него свои формулы

53. capitan 2507 28.03.22 18:39 Сейчас в теме

И еще ложечка дегтя, чтобы два раза не бегать)
Вы сами согласились и понимаете, что планировщик не смог определить оптимальный вид соединения для этого запроса.
В MS я со скрипом представляю как посмотреть почему, но не в postgres.
Как решение вы объявили разработчиков конфигурации криворукими и поправили типовой запрос.
Где гарантия, что в конфигурации нет или не будет в следующем релизе аналогичных запросов, с которыми не справляется планировщик?
Тогда заказчик опять подвиснет, но на другой операции, вы опять придете и поправите запрос и войдете в цикл, если в договоре у вас гарантия то бесплатный).
Три года назад я помню ушлые админы просто в конфиг ставили enable_nestloop = off
Но потом пришел postgres pro и все отрапортовались что все будет хорошо.
Вот и сейчас надо было подтянуть postgres pro к СУБД и понять в чем ошибка на уровне postgres, а не на уровне одного запроса.

60. sapervodichka 6915 29.03.22 09:10 Сейчас в теме

(53) Спасибо, я понял что "Профилактика - лучшее лекарство" (обслуживание базы и настройка позволит избежать нежданных приключений)

61. sapervodichka 6915 29.03.22 09:16 Сейчас в теме

(60) т.е. если у вас нет обслуживания базы данных (обновления индексов и статистики) то Welcome to Hell, и коллеги похоже на это же наткнулась. (если почитать статью, то они устранили сложную проблему (правильные индексы достроили в нужном месте закрытия месяца - это тяжкий труд), а если почитать комментарии, то проблему кажется можно было бы избежать "правильно" наладив обслуживание базы и настройки Postgres - эта задача более понятная не для эксперта-практика и точно обязательная)

63. it-expertise 345 29.03.22 11:04 Сейчас в теме

(53) Лена Скворцова попросила ответить:

Ставить в конфиге enable_nestloop = off кажется очень странным решением, которое не оставляет Postgres вообще никакого выбора по той простой причине, что enable_mergejoin = off делает сама Платформа.

Сразу даю пруф, с учётом того, что выше уже это предложение звучало как "по поводу настроек, то была маза отрубить nested loop, но тут фигня будет начинаться, когда одна очень большая и очень маленькая таблицы будут мержиться":

00:20.159005-1,DBPOSTGRS,3,process=rphost,p:processName=test,OSThread=2996,t:clientID=19,t:applicationName=1CV8C,t:computerName=ITE-MP-00159,t:connectID=1,DBMS=DBPOSTGRS,DataBase=localhost\test,Trans=0,dbpid=,Sql='SET enable_mergejoin = off;',RowsAffected=0,Result=PGRES_COMMAND_OK

Одна очень маленькая и очень большая таблицы будут, очевидно, соединяться хешированием, как и все остальные.

Что касается консультации разработчика Postgres, то она была получена. К сожалению, рекомендаций, кроме тюнинга запроса и индексов, в данном случае не было.

64. capitan 2507 29.03.22 12:04 Сейчас в теме

(63)Давайте так.
Раз уж Лена Скворцова (да прославятся ее видео на ютюбе и продлятся дни ее :) участвует в беседе.
Есть простой вопрос:
Как создание дополнительных индексов временной таблицы могло повлиять на решение планировщика уйти с nested loops на hash match?

По поводу

очень маленькая и очень большая таблицы будут, очевидно, соединяться хешированием

так хеширование так и работает

Соединение хешированием (hash join)
• При соединении хешированием строки одного набора помещаются в
хеш-таблицу, содержащуюся в памяти, а строки из второго набора
перебираются, и для каждой из них проверяется наличие
соответствующих строк в хеш-таблице.
• Ключом хеш-таблицы является тот столбец, по которому выполняется
соединение наборов строк.
• Как правило, число строк в том наборе, на основе которого
строится хеш-таблица, меньше, чем во втором наборе.
• Это позволяет уменьшить ее размер и ускорить процесс обращения к
ней.
• Данный метод работает только при выполнении эквисоединений,
поскольку для хеш-таблицы имеет смысл только проверка на
равенство проверяемого значения одному из ее ключей.
• Метод эффективен для больших выборок.

Показать

А nested loops это декартово произведение в общем случае, ему как раз индексирование может помочь.
Или я не прав?

Если есть тестовый стенд можете попробовать на типовых конфигах enable_nestloop = off никакой фигни не будет ;)

65. it-expertise 345 29.03.22 12:18 Сейчас в теме

(64)
Лена Скворцова:

Создание доп. индексов никак не может склонить планировщик к выбору hash, только наоборот. Вы правы.
Непонятно, где было сказано, что это произошло? Мы вроде бы такого не говорили.

66. capitan 2507 29.03.22 12:24 Сейчас в теме

(65)Да как так то барыня )

Этап 4. Решение выявленной проблемы
На этом этапе требуется внимательно изучить текст запроса, чтобы выявить, какие конструкции вызывают проблемы при последующем его выполнении и устранить их...
Очевидно нарушен стандарт разработки, присутствующий на ИТС в разделе “Несоответствие индексов и условий запроса”. Подробнее можно прочитать: https://its.1c.ru/db/v8std#content:652:hdoc
Чтобы избавиться от сканирования по остальным полям при выполнении соединения, необходимо добавить эти поля в индекс...
Стоит отметить, что соединение вложенными циклами для данных соединений является неудачным выбором оптимизатора запросов Postgres. В данном случае единственным “шансом на успех” было бы соединение хешированием. Эту гипотезу легко проверить, например, вручную в консоли, предварительно запретив nested loops:
SET enable_nestloop = off;
Правда, проверка в консоли осложняется необходимостью добавления запросов вставки данных во временные таблицы. Но мы проверили – hash join выполнился за 50 секунд :)
После добавления нужных полей в индекс, запускаем расчет себестоимости заново, теперь наш запрос выполняется за 4 282 секунды. Уже лучше, но далеко ещё не хорошо…

Можно тогда попросить для таких бестолковых людей как я в следующей публикации вынести наверх описание проблемы и ее решение вкратце.

Я прочитал это как то что вы поймали при переходе на postgres nested loops вместо hash match и победили это созданием дополнительных индексов временной таблицы

Это кстати рекомендуемое оформление статей на ИС.
Как показывает практика, каждый буквально когда много текста читает не то что написано, а то что он хочет увидеть)
Заранее извините если я вас неправильно понял и за мой французский)

78. capitan 2507 30.03.22 16:41 Сейчас в теме

(65)Тут я вижу автор Алексей Белый ходит плюсует комментарии, может он пояснит в чем тогда предлагаемое решение проблемы?

79. AlekseyBelyy 12 30.03.22 19:16 Сейчас в теме

(78) Добрый день. Что именно необходимо пояснить - статью другими словами или конкретный момент вас интересует?

80. capitan 2507 31.03.22 09:15 Сейчас в теме

(79)Добрый день. В чем предлагаемое решение проблемы?
Проблема была в неправильном выборе планировщиком типа соединения, вы сделали что то и планировщик поправился.
Что вы сделали?
И проверяли ли потом в обратную сторону, т.е. вы проиндексировали к примеру временную таблицу и все заработало хорошо, вы убрали индексирование и все опять стало плохо.

81. AlekseyBelyy 12 31.03.22 13:24 Сейчас в теме

(80)
Дополнили индекс. Видимо стоит пояснить как это повлияло подробнее:
Планировщик выбирал соединение вложенными циклами, при этом часть условий соединения попадало в индекс (index cond), оставшаяся часть выполнялась перебором (filter).
19 838 704 480 строк было просмотрено и ни одна не подошла, и так три раза по 9260+ сек в одном из подзапросов. Итого 27 800 сек для подзапроса.
Если говорить в терминах MS SQL, то имеет место быть seek...where (для текстового плана) или seek predicates и predicate (для графического плана).
Раз уж планировщик выбирает вложенные циклы, то поможем выполнить это быстро, дополняем индекс. Все условия попадют в индекс теперь, остается только index cond.
Или в терминах MS SQL:
был seek...where остался только seek
был seek predicates и predicate остался только seek predicates
Ранее просматривались миллиарды лишних строк (тысячи итерация на миллионной таблице) без подходящего индекса за 27 800 сек (для всего подзапроса)
Теперь весь подзапрос выполняется меньше секунды, так как лишнего не читаем

В обратную сторону не проверяли.

82. AlekseyBelyy 12 31.03.22 15:54 Сейчас в теме

(81)
Еще дополню ответ аналогией, чтобы в том числе начинающим специалистам стало понятнее о чем речь:

Допустим у вас есть таблица адресов, в ней следующие колонки: город, улица, номер дома, этаж, номер квартиры, имя жильца
Теперь представьте, что таблица отсортирована только по по городу и улице, все остальное в случайном порядке. Т.е. индекс по первым двум колонкам.
У вас есть адрес и надо понять кто по нему живет. В этой таблице вы
- быстро найдете строки с нужным городом и улицей (index cond, он же seek, он же seek predicates)
- но чтобы найти точный адрес вам придется пересмотреть большое количество строк, пусть и меньше чем во всей таблице в целом (filter, он же where, он же predicate)

А если необходимо найти 10, 100, 1000 жильцов.....

83. capitan 2507 31.03.22 16:15 Сейчас в теме

(81)
(82)
Вспоминается...

...вы либо крестик снимите, либо ...

Алексей, если у вас был частичный поиск по индексу, а потом стал полный, вам тогда наверное надо убрать про hash matsh все размышления.
Тогда все понятно. Ура, ура.
У вас на принскринах и в тексте переход на hash matsh, отключение вложенных циклов в конфигурации и у начинающих специалистов складывается впечатление что вы боролись за переход на hash matsh с nested loops, на части принтскринов так и есть
В нем индексирование не имеет роли вообще.
Определитесь с этим и/или принтскрины поправьте
На самом деле загадочно почему вообще планировщик пошел по пути nested loops на такого размера таблице
Больше скажу. Года три назад постгри норовил все сложить вложенными циклами, потому что у него статистики по временным таблицам не было видимо, а потом чудесным образом все наладилось.
Я думал, что это ребята из постгрес про починили, а Лена Скворцова сейчас объяснила что это в платформе подшаманили.
В те времена просто можно было поставить enable_nestloop = off и база оживала

70. ivanov660 4578 29.03.22 17:04 Сейчас в теме

(61)
(64)

посмотрите внимательно) в результатах оптимизации мы видим, что расчетное количество строк планировщик стал практически угадывать:
было cost=1 actual rows=7840 - логичный выбор nested loop (это по всем соединениям)
стало cost=3 309 000 actual rows=3 316 000 - логичный выбор hash join

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

71. capitan 2507 29.03.22 17:52 Сейчас в теме

(70)
Да. Планировщик стал угадывать размеры таблиц и правильно выбрал метод соединения.

Посмотрите и вы внимательнее )
Автор статьи как средство решения проблемы считает дополнительное индексирование временной таблицы.

Как читаю я по крайней мере

И мой вопрос - почему планировщик неожиданно стал так делать?
в самом 1С в запрос добавилось только несколько индексов, почему исходные запросы могли преобразиться внутри планировщика?

И если поэкпериментировать, то планировщик как раз даже на объединении таблиц по 10 записей в каждой выбирает hash match
Он не выберет его только если условие соединения будет не на равенство.

74. ivanov660 4578 29.03.22 19:03 Сейчас в теме

(71) мы уже в контексте обсуждения опустили вопрос с индексированием временной таблицы как не состоятельный
думаю, что причина по которой изменился план скорее всего две:
- это изменение самих запросов,
- либо изменение настроек постгре (вроде не трогали и ничего не пересчитывали).

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

75. capitan 2507 29.03.22 19:33 Сейчас в теме

(74)

тогда в чем ремонт то?
интересно же как все починилось
Просто тут все под шумок напихали разработчикам, а они явно не при делах

77. ivanov660 4578 29.03.22 22:15 Сейчас в теме

(75) думаю, это вопрос к авторам

67. Gilev.Vyacheslav 1917 29.03.22 14:24 Сейчас в теме

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

62. capitan 2507 29.03.22 09:22 Сейчас в теме

(60)
(60)
Дмитрий, золотые слова, подходящие к любому случаю)
Я бы еще добавил в копилку крылатых фраз:

Чтобы что-то улучшить, это надо измерить.
Чтобы что-то измерить, надо знать, что измерять

68. SergeyMordvin 1962 29.03.22 15:38 Сейчас в теме

я вот ничего не понимаю в статье, но вопрос все равно есть:
- я верно понял, что проблема не в типовой, а в СУБД
- и разработчикам типовой не надо передавать/описывать проблему?

Или все же надо?

69. Gilev.Vyacheslav 1917 29.03.22 16:10 Сейчас в теме

(68) проблема имхо в конкретных данных, для которых надо править код типовой, но это решение не будет мастабируемым и универсальным, передавать нет смысла

89. s22 22 08.04.22 11:11 Сейчас в теме

(69)

Даже если в конкретных то тут логика не меняется, а только индексы добавляются ИМХО может кому то сильно помочь, к тому же на мсскл нет такой проблемы

88. s22 22 08.04.22 11:09 Сейчас в теме

а для 2.5.7 есть аналогичный анализ?

90. it-expertise 345 08.04.22 11:58 Сейчас в теме

(88) не делали.

говорят, там запросы такие отрефакторены все. пока не проверяли.

92. s22 22 11.04.22 09:55 Сейчас в теме

(90)

говорят, там запросы такие отрефакторены все. пока не проверяли.

Да, там нет похожего кода.
Есть только в Партиях21

Оставьте свое сообщение

E-mail:

Автор:

ИТ Экспертиза (it-expertise)

Рейтинг: 345

Для получения уведомлений о новых публикациях автора подключите телеграм бот: Инфостарт бот

Публикация:

№ 1628171

Создание 25.03.22 11:10

Обновление 25.03.22 11:10

Статистика:

Просмотры 8887

Загрузки 0

Рейтинг 73

Комментарии 93

Характеристики:

Код открыт Да

Рубрики HighLoad оптимизация Механизмы типовых конфигураций Запросы

Кому Программист

Тип файла Нет файла

Платформа Платформа 1С v8.3

Конфигурация 1С:ERP Управление предприятием 2

Операционная система Linux

Страна Не имеет значения

Отрасль Не имеет значения

Налоги Не имеет значения

Вид учета Не имеет значения

Доступ к файлу Бесплатно (free)