При администрировании и оптимизации крупных информационных систем на платформе «1С:Предприятие 8» разработчики часто сталкиваются с неочевидным поведением СУБД. Вроде бы тяжелых и долгих запросов в базе данных нет, но общая производительность падает, а дисковая подсистема, выделенная под базу tempdb (в случае MS SQL Server), перегружена. При анализе СУБД с помощью профайлера обнаруживается колоссальное количество быстрых запросов типа INSERT, выполняющихся тысячи раз в минуту.
В этой статье мы подробно разберем, почему возникает такое поведение, как правильно настроить технологический журнал 1С для локализации подобных запросов и какими способами можно навсегда устранить эту проблему.
Обычно технологический журнал (ТЖ) настраивают на сбор долгих запросов (например, длительностью более 10 или 30 секунд). Однако в нашем случае запросы INSERT выполняются мгновенно (за доли миллисекунды), но их количество огромно. Если мы настроим ТЖ стандартным образом с отбором по длительности, эти события просто не попадут в лог.
Для того чтобы зафиксировать контекст вызова этих частых мелких запросов в коде 1С, нам необходимо настроить сбор событий DBMSQL без ограничения по времени, но с фильтрацией по ключевым словам. Проанализируем правильную структуру файла конфигурации технологического журнала logcfg.xml:
<?xml version="1.0" encoding="UTF-8"?>
<config xmlns="http://v8.1c.ru/v8/tech-journal">
<log location="C:\v8\logs\dbmsql_insert" history="2">
<event>
<eq property="Name" value="DBMSQL"/>
<like property="sql" value="%INSERT%"/>
</event>
<property name="all"/>
</log>
</config>
Разберем ключевые особенности этой настройки:
DBMSQL, которое отвечает за все запросы, отправляемые платформой 1С к СУБД.duration) мы используем условие фильтрации like по свойству sql. Значение %INSERT% (символы процента обязательны для поиска подстроки) позволяет нам записывать в лог только те запросы, которые содержат операцию вставки.history="2" указывает платформе автоматически удалять файлы логов старше двух часов, чтобы жесткий диск сервера приложений не переполнился из-за огромного объема записываемой информации.После применения этой настройки в логах технологического журнала появятся события, в которых будет четко виден контекст исполнения (стек вызова на языке 1С) и сам текст SQL-запроса.
Когда мы проанализируем полученные логи ТЖ, мы увидим, что контекстом вызова практически всегда выступает открытие или обновление некоторого динамического списка на управляемой форме, а в тексте запроса фигурирует ограничение доступа на уровне записей (RLS).
Выясним причину этого поведения на уровне работы платформы 1С. Платформа оптимизирует передачу массивов или списков значений в параметры запросов (в том числе в условиях RLS вида В (&Параметры)) следующим образом:
IN (?, ?, ?, ...). СУБД легко справляется с таким запросом.INSERT, а исходное условие В (&Параметры) заменяет во внутреннем SQL-запросе на соединение (INNER JOIN) с этой созданной временной таблицей.Порог в 128 элементов является фиксированным для большинства современных версий платформы 1С 8.3. Если в вашем параметре RLS (например, список разрешенных подразделений, складов, групп доступа или контрагентов) содержится 129 и более элементов, СУБД гарантированно получит команду на создание временной таблицы и соответствующее количество запросов INSERT при каждом обновлении данных.
Сам по себе разовый запрос с созданием временной таблицы не создает критической нагрузки. Проблема приобретает лавинообразный характер, когда такое условие накладывается на динамический список на форме:
Обновить() или при фоновом обновлении конфигурации.tempdb и выполняет туда более 128 микро-запросов INSERT.PAGELATCH_UP или PAGELATCH_EX из-за постоянной борьбы за страницы метаданных в системной базе tempdb.Ситуация усугубляется, если в условиях RLS используется конструкция В ИЕРАРХИИ (&Параметры). Платформа сначала программно разворачивает всю иерархию до «плоского» списка элементов. Если итоговый размер дерева превысит 128 записей, мы получим ту же проблему с созданием временных таблиц при каждом обращении к базе.
Рассмотрим основные способы, которые позволяют избавиться от избыточных INSERT-запросов и существенно снизить нагрузку на дисковую подсистему сервера СУБД.
Если вы работаете на современных типовых конфигурациях (таких как ERP 2, Комплексная автоматизация 2, Управление торговлей 11), наиболее правильным решением будет включение встроенного механизма БСП — «Производительный режим работы RLS».
В этом режиме права рассчитываются системой предварительно (при записи объектов или изменении настроек прав) и сохраняются в специализированных плоских регистрах сведений (например, КлючиДоступаКОбъектам). Ограничение доступа в самом запросе RLS упрощается до обычного соединения с этими регистрами по физическим индексам. Это полностью исключает передачу больших массивов данных в параметрах и, как следствие, ликвидирует генерацию временных таблиц «на лету».
Если вы разрабатываете собственную систему разграничения прав или дорабатываете существующую, откажитесь от передачи больших массивов непосредственно в параметры сеанса или параметры запроса RLS. Вместо этого используйте вспомогательный регистр сведений.
Проанализируем пример. Вместо неоптимального условия:
ГДЕ Ссылка В (&ДоступныеСклады)
Где &ДоступныеСклады — это массив из 150 элементов, используйте запрос с соединением к физической таблице регистра сведений:
ГДЕ Ссылка В (
ВЫБРАТЬ Рег.Склад
ИЗ РегистрСведений.ДоступныеСкладыПользователей КАК Рег
ГДЕ Рег.Пользователь = &ТекущийПользователь
)
В данном случае платформа 1С передает СУБД единый оптимизированный SQL-запрос с соединением таблиц. Создание временной таблицы силами платформы 1С и её наполнение через INSERT выполняться не будет, так как все необходимые данные уже физически находятся внутри СУБД.
Если оперативно переписать код RLS или перенастроить права доступа в конфигурации невозможно, необходимо минимизировать негативный эффект частых блокировок на уровне СУБД MS SQL Server:
tempdb на несколько файлов одинакового размера. Оптимальное количество файлов — по числу ядер процессора (но не более 8, если ядер больше). Это позволит распределить нагрузку на запись по разным потокам.tempdb на максимально быстрые твердотельные накопители (SSD NVMe).
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_TEMPDB_METADATA = ON;
Это перенесет нагрузку по управлению метаданными временных таблиц из дисковых страниц базы данных непосредственно в оперативную память сервера СУБД, убрав узкое горлышко в виде ожидания блокировок страниц (PAGELATCH).