Проблема

В разгар перехода на PostgreSQL (далее PG) к нам обратились коллеги из отдела развития информационных систем собственных производств за решением распространенной проблемы: в ERP «Закрытие месяца» на MSSQL идёт 2 часа, а на PG — 11 часов. Требовалось сократить этот срок хотя бы до 3 часов. К слову, все зависало на конкретном пункте: «Распределение затрат и расчет себестоимости». 

Конфигурация 1С:ERP Агропромышленный комплекс 2 (2.5.8), полностью на поддержке, расширения хоть и были, но закрытие месяца не затрагивали. Сервер PG 14.5.3 x64 скачан с сайта 1С, установлен на linux сервер под CentOS 7 (CPU 8 ядер, ОЗУ 64ГБ). Версия сервера 1С 8.3.21.1393 x64. Размер базы около 90ГБ. 

Обновление до последнего релиза не исправило ситуацию, поэтому приступили непосредственно к анализу и разбору. 

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

Перед запуском закрытия месяца для базы был выполнен необходимый регламент: Vacuum, Analyze, Freeze. В дальнейшем он будет повторяться перед каждым тестом в обязательном порядке. 

Техжурнал показал интересную картину. Обнаружили 3 запроса, которые выполнялись по 2.5 — 3 часа.

2 запроса — дубль одного и того же.  

Проблемные запросы находятся в следующих местах: 

• РасчетСебестоимостиКорректировкаСтоимости.ТекстСуммыПрочихРасходов 
• РасчетСебестоимостиРешениеСЛУ.ТекстВтТаблицаСвязейПостатейныеРасходы 

Первым делом под подозрение попал PG и linux сервер, на котором он установлен. Linux и сам PG были настроены по всем известным рекомендациям. Всё перепроверили, ошибок не нашли. Далее обратились за помощью в корпподдержку 1С — рекомендовали поиграться с default_statistics_target (количество записей, просматриваемых при сборе статистики по таблицам). Увеличив дефолтный параметр со 100 до 1000, не получили положительных изменений. И если при значении 100, первый проблемный запрос появлялся где-то через 20 минут от начала расчета себестоимости, причем всегда и при любых тестах, то со значением 1000 это время возросло до 40 минут. Явно сказывается расчет статистики на временных таблицах. Увеличивать его смысла уже не было, даже 1000 точно не будем ставить при таких просадках. 

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

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

Auto_explain

Для снятия плана воспользовались стандартной библиотекой auto_explain. Почему-то считали, что это будет так же просто и легко, как в MSSQL, но нас поджидал сюрприз.  

Нет никаких отборов, кроме длительности запросов. Даже по имени базы. Это, мягко говоря, немного шокировало. Отбор по базе казался, ну как бы, настолько очевидным, что когда его тут не обнаружилось, то решили, что, наверное, не та библиотека, ну или делаем что-то не то. Но, увы, все так. А если это общий сервер для тестирования и там много баз? По ним всем собирать все запросы, серьезно? 

И как следствие, это получение планов уже оптимизированных запросов, которые могут выполняться достаточно быстро, и отбор по длительности придется отключить для получения всего и вся со всех баз. При этом включение и отключение отбора делается настройками (ALTER SYSTEM SET…), и тут главное — не забыть все вернуть обратно. 

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

Но была одна особенность, которую мы не брали в расчет, да и не сразу к этому пришли: все это был open source. А значит можно не только заглянуть под капот, но внести изменения, дописать, переписать и т. д. 

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

Естественно, из любопытства пошли смотреть на код auto_explain. Он входит в состав официального дистрибутива, который можно скачать с сайта PG (https://www.postgresql.org). 

Весь код был в одном файле-модуле auto_explain.c, открыв который, увидели всего около 500 строк кода, где большая часть — это инициализация и подготовка, а основная логика — 70 строк. И это все? Ну как бы привыкли к многотысячным модулям в 1С, но тут все казалось уж слишком простым. Модуль написан на языке СИ, опыта работы с ним, увы, не было, но все казалось очень знакомым: переменные, структуры, условия, циклы и т. д. Где и что поправить, нашли сразу, и конечно, появилась гениальная идея: собрать свой auto_explain с блэкджеком и отборами. 

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

Включение и отключение новых отборов (ALTER SYSTEM SET…) повесили на новые параметры, которые также прописываются в этом модуле.