- Постановка задачи
- Заполнение документа "Резервы по оплате труда" в базе ЗУП
- Описание проблемы
- Как планировщик делает оценку по таблицам без статистики
- Как работает платформа с временными таблицами, создаваемыми из таблицы значений
- Решаем проблему с помощью плагина online_analyze
- Расчет себестоимости за период в базе УХ
- Описание проблемы
- Сравниваем данные логов СУБД и "1С"
- Анализируем окружение и готовим скрипты
- Неожиданный результат
- Причины долгой вставки
- Перепроведение документов в базе УХ
- Заключение
Меня зовут Александр Симонов, я руководитель направления развития и поддержки 1С в компании "Тантор Лабс".
В рамках клиентских пилотов по миграции на СУБД Tantor Special Edition 1C мы сопровождаем процесс приемо-сдаточных работ и оказываем поддержку в ходе тестирования.
В данной статье рассмотрим пример доработки СУБД Tantor под потребности 1С, а также расследуем причины медленного выполнения запросов у одного из наших клиентов.
Постановка задачи
Тестовый стенд
Название |
ОС |
ПО |
Конфигурация |
|||||
| Сервер приложений |
|
|
|
|||||
|
|
|
|
|||||
|
|
|
|
Оба сервера СУБД виртуализированы. Они сильно различаются по характеристикам, но организовать равноценные стенды у заказчика не было возможности.
Тестируемые операции:
- Заполнение документа "Резервы по оплате труда" в базе ЗУП.
- Расчет себестоимости за период в базе УХ.
- Перепроведение документов в базе УХ.
Каждая из них сначала выполняется на MS SQL, а затем – на Tantor.
Заполнение документа "Резервы по оплате труда" в базе ЗУП
Описание проблемы
Были получены следующие результаты:
MS SQL – 478, Tantor – 855 сек.
Созвонившись с заказчиком, выясняем, что настройки Tantor у него дефолтные. Настраиваем систему в соответствии с нашими рекомендациями и получаем результат 697 сек.
Разница более 200 секунд, это плохой результат, поэтому начинаем разбираться в причинах. Заказчик собрал технологический журнал и логи СУБД. Проанализировав его, видим, что у нас есть топ запросов, которые долго выполняются.
Все эти запросы характеризует то, что в них идет соединение временных таблиц, отсюда напрашивается вывод: скорее всего, это типичная проблема, когда во временной таблице нет подходящего индекса и оптимизатор неверно выбирает план.
Находим проблемный запрос в логе СУБД, получаем план и видим причину: дело в том, что планировщик неправильно оценил количество строк во временной таблице:
Но почему планировщик может так ошибаться? Ведь для таблицы должна рассчитываться статистика, т.к. при ее создании платформа 1С сама вызывает команду ANALYZE. Ситуация выглядит странно.
После этого мы развернули пустую конфигурацию ЗУП и нашли запросы по проблемному контексту:
|
Документ.РезервыПоОплатеТруда.Форма.ФормаДокумента.Форма : 2403 : Результат = ДлительныеОперации.ЗапуститьВыполнениеВФоне( |
Временные таблицы в этом случае создавались путем передачи таблицы значений как параметра запроса. Мы рекомендовали при создании этих временных таблиц в запросе сразу создать индексы для тех полей, по которым далее идет соединение в проблемных запросах. На это заказчик ответил, что код полностью типовой, и они не готовы его переписывать, но смогут обезличить свою базу и передать нам для анализа.
Полученную базу мы развернули на своем стенде:
Назначение |
ОС |
ПО |
Конфигурация |
|---|---|---|---|
| Сервер приложений | Astra Linux 1.7.5 | Платформа "1С" 8.3.23.1997 | CPU 6, 16 Gb RAM |
| Сервер СУБД | Astra Linux 1.7.5 | "Tantor SE 1C" 15.4 | CPU 4, 16 Gb RAM |
Проблемные запросы у нас выполнялись так же долго, и мы cобрали лог технологического журнала (ТЖ) по событию DBPOSTGRS без фильтра по длительности, и из такого лога причина стала понятна: по временной таблице не была рассчитана статистика. Поговорим немного о ней.
Как планировщик делает оценку по таблицам без статистики
Каким образом планировщик получил оценку в 5 строк (метка 1 на рисунке ниже), если по таблице нет статистики?
В подобных случаях планировщик оценивает количество строк, исходя из размера файла с таблицей и ширины строки (метка 2 на рисунке).
Оценка в 5 строк учитывает наложенные отборы из поля Filter (метка 3 на рисунке). Для колонок, по которым нет статистики, планировщик использует фиксированную оценку – 0.5% от количества строк. У нас отбор накладывается на 2 колонки, и эту оценку планировщик дает для каждой из них. В итоге мы можем получить формулу:
Количество строк в таблице * 0.005 * 0.005 = 5.
Давайте вспомним базовые уроки алгебры и вычислим, сколько, по мнению планировщика, в данной таблице строк (до наложения отборов): 5/(0.005*0.005) = 200 000
Получается, исходя из ширины одной строки и размера файла, планировщик подумал, что в таблице tt268 примерно 200 тысяч строк. После наложения фильтра по указанной выше формуле получается оценка – 5 строк.
Как работает платформа с временными таблицами, создаваемыми из таблицы значений
Давайте вернемся к нашей проблеме и посмотрим какие команды выполняются при создании временной таблицы из таблицы значений.
Такая временная таблица создается конструкцией "COPY pg_temp.tt FROM STDIN BINARY" вместо привычной конструкции "INSERT INTO pg_temp.tt (список колонок) SELECT ... FROM ...", которая используется при создании временной таблицы выборкой из результата запроса.
Создание временной таблицы без индексов
Запрос 1С:
|
ВЫБРАТЬ |
В этом случае на СУБД будут выполнены две команды, согласно данным ТЖ событий DBPOSTGRS:
|
1. Создание временной таблицы tt1 |
В поле RowsAffected содержится количество строк, помещенных в созданную временную таблицу.
Создание временной таблицы с индексом
Если при создании временной таблицы сразу создать в ней индекс, получится следующее:
Запрос 1С:
| ВЫБРАТЬ ТЗ.Поле1 КАК Поле1 ПОМЕСТИТЬ ВТ ИЗ &ТЗ КАК ТЗ ИНДЕКСИРОВАТЬ ПО Поле1 |
В этом случае, по данным ТЖ событий DBPOSTGRS, на СУБД будут выполнены следующие команды:
|
|
| // 1. Создание временной таблицы tt1 Sql='drop table if exists tt1 cascade;create temporary table tt1 (_Q_000_F_000 numeric(12, 2) ) without oids ',RowsAffected=0 // 2. Заполнение ВТ tt1 данными, переданными в запрос как таблица значений Sql=COPY pg_temp.tt1 FROM STDIN BINARY,RowsAffected=4 // 3. Создание временной таблицы tt2 с такой же структурой колонок как и у tt1 Sql='drop table if exists tt2 cascade;create temporary table tt2 (_Q_000_F_000 numeric(12, 2) ) without oids ',RowsAffected=0 // 4. Если существует индекс tmpind_0, удалим его и создадим новый индекс tmpind_0 на временной таблице tt2 Sql=drop index if exists tmpind_0,RowsAffected=0 Sql=create index tmpind_0 on pg_temp.tt2(_Q_000_F_000),RowsAffected=0 // 5. Вставляем во временную таблицу tt2 все записи из tt1 Sql='INSERT INTO pg_temp.tt2 (_Q_000_F_000) SELECT T1._Q_000_F_000 FROM pg_temp.tt1 T1',RowsAffected=4 // 6. Вызываем расчет статистики на временной таблице tt2 Sql=ANALYZE pg_temp.tt2,RowsAffected=0 // 7. Очищаем временную таблицу tt1 Sql="SELECT FASTTRUNCATE ('pg_temp.tt1')",RowsAffected=1 |
Получается, что платформа не выполняет команду ANALYZE по временной таблице (создаваемой в результате передачи таблицы значений в запрос), если при этом в ней сразу не создать индекс.
Возникшую проблему отсутствия статистики на временной таблице можно было бы решить двумя способами:
- При создании ВТ создать индекс.
- Из созданной ВТ переложить данные в новую ВТ, которую далее и использовать.
Доработка кода 1С выглядит не слишком рациональным решением, поэтому посчитать статистику по такой таблице попробуем заставить саму СУБД.
Вступайте в нашу телеграмм-группу Инфостарт
