Несколько слов про платформенный механизм оптимизации RLS

07.04.22

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

Смотрим, как работает платформенный механизм оптимизации RLS, сравним поведение на разных СУБД MS SQL, Postgres 11,13,14.

Начиная с версии 8.3.18 платформы 1С появился механизм оптимизации RLS. С данной особенностью мы столкнулись при анализе проблемы быстродействия конфигурации ERP. Проведем небольшой анализ и посмотрим что это такое и как оно работает.

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

Исходные условия проводимого анализа:

  • исследование проводили на 5 СУБД: MS SQL, Postgres Pro, Postgres 11, Postgres 13, Postgres 14!
  • использовали 3 версии платформы 1C: 8.3.16, 8.3.19, 8.3.20.
  • операционные системы Windows server для 1с и postgre SQL, MS SQL; linux под postgre SQL.
  • база использовалась для исследования конфигурация ERP с достаточным объемом данных (заказы клиентов ~600 000 документов, регистр заказов ~7 500 000 записей);
  • для тестов использовались 3 пользователя с различными RLS ограничениями и администратор;
  • для демонстрации были использованы более 20 тестовых запросов, а были выбраны в статью 2 примера (этого достаточно). 

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

Если проанализировать шаблон, то что в нем не изменяется? Это под запрос, определяющий группы доступа пользователя.

 

С точки зрения логики — можно вообще посчитать это значение один раз при входе пользователя и передавать в шаблон, как массив доступных значений. У ребят из Раруса где-то была подобная статейка на сайте - "Оптимизация типового шаблона RLS (получение групп доступа)". Кто у кого подсмотрел?

Давайте посмотрим, как эта платформенная особенность выглядит в работе. Открываем базу под любым пользователем с RLS, берем консоль запросов и вбиваем простой текст — эмуляция динамического списка заказов клиента конфигурации ERP/УТ/КА:

 
 Простой запрос (имитация динамического списка заказов клиента)

 

ВЫБРАТЬ РАЗРЕШЕННЫЕ ПЕРВЫЕ 45
	Т.Номер КАК НомерДокумента,
	Т.Дата КАК ДатаДокумента,
	Т.Организация КАК Организация,
	Т.Контрагент КАК Контрагент
ИЗ
	Документ.ЗаказКлиента КАК Т

 

 

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

 
 SQL запрос получения групп доступа и с подстановкой таблиц

 

SELECT
	T1._Reference116_IDRRef AS ACCESS_GROUPRRef
FROM _Reference116_VT3635 T1
	INNER JOIN _InfoRg36262 T2
	ON (T2._Fld36264_TYPE = '\\010'::bytea AND T2._Fld36264_RTRef = '\\000\\000\\001,'::bytea AND T2._Fld36264_RRRef = '\\272-\\000PV\\2346\\034\\021\\354X0\\246}4\\017'::bytea) AND (T2._Fld36263_TYPE = T1._Fld3637_TYPE AND T2._Fld36263_RTRef = T1._Fld3637_RTRef AND T2._Fld36263_RRRef = T1._Fld3637_RRRef)
WHERE ((T1._Fld1585 = CAST(0 AS NUMERIC))) AND (T2._Fld1585 = CAST(0 AS NUMERIC)) LIMIT 21

или воспользуемся обработкой конвертации SQL в представления 1С

ВЫБРАТЬ ПЕРВЫЕ 21
	T1.Ссылка КАК ACCESS_СГРУППИРОВАТЬRRef
ИЗ Справочник.ГруппыДоступа.ТабличнаяЧасть.Пользователи КАК T1
	ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.СоставыГруппПользователей КАК T2
	ПО (T2.Пользователь = '\\010'::bytea И T2.Пользователь = '\\000\\000\\001,'::bytea 
	И T2.Пользователь = '\\272-\\000PV\\2346\\034\\021\\354X0\\246}4\\017'::bytea) 
	И (T2.ГруппаПользователей = T1.Пользователь 
	И T2.ГруппаПользователей = T1.Пользователь 
	И T2.ГруппаПользователей = T1.Пользователь)
ГДЕ ((T1.ОбластьДанныхОсновныеДанные = ВЫРАЗИТЬ(0 КАК ЧИСЛО))) 
	И (T2.ОбластьДанныхОсновныеДанные = ВЫРАЗИТЬ(0 КАК ЧИСЛО)) 

И эти данные подставляются как массив значений уже в запросе RLS

 
 Пример запроса выбора ЗК SQL и с подстановкой таблиц 1С

План запроса

https://explain.tensor.ru/archive/explain/a6ae34c18cc0c12a54c04d6796eeeae3:0:2022-04-05#visio

 

 

Теперь давайте вручную добавим в запрос группы:

https://explain.tensor.ru/archive/explain/5a6575d3332264f3e01d741488876da4:0:2022-04-05#visio


 

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

Пример плана с оптимизацией:

 

 

План без оптимизации:

 

 

Алгоритм действий платформы выглядит как показано на рисунке ниже. Сначала идет запрос поучающий группы доступа (выделен красной рамкой). Далее идет следующий запрос с подставленными группами доступа в операторе «В» запроса с RLS.

 

 

Т.е. выбирается 21 значение. А что происходит, если будет равно или больше? Ответ: оптимизация отрабатывать не будет, см. рис. ниже! Может было надо использовать 42?

 

 

Давайте сравним время выполнения и планы запросов с оптимизацией и без. Без оптимизации будем ручками подставлять порезанную вставку в SQL (мы провели большее количество тестов и среди них выбрали несколько, по остальным картинка подобная).

1. Запрос имитации динамического списка ЗК (приведен выше)

2. Давайте построим более сложный запрос. Добавим выбор нескольких полей через точку и сортировку по полю «ДатаДокумента» составного типа:

 
 Запрос с выбором через точку и сортировкой

 

ВЫБРАТЬ РАЗРЕШЕННЫЕ ПЕРВЫЕ 45
	"test_rg_12345" КАК Ключ,
	ЗаказыКлиентов.ЗаказКлиента.Номер КАК НомерДокумента,
	ЗаказыКлиентов.ЗаказКлиента.Дата КАК ДатаДокумента,
	ЗаказыКлиентов.ЗаказКлиента КАК ЗаказКлиента,
	ЗаказыКлиентов.Номенклатура КАК Номенклатура,
	ЗаказыКлиентов.Характеристика КАК Характеристика,
	ЗаказыКлиентов.Заказано КАК Заказано,
	ЗаказыКлиентов.КОформлению КАК КОформлению,
	ЗаказыКлиентов.Сумма КАК Сумма
ИЗ
	РегистрНакопления.ЗаказыКлиентов КАК ЗаказыКлиентов

УПОРЯДОЧИТЬ ПО
	ДатаДокумента

 

 

  • Postgres 11:
    • без оптимизации (~541 с) 
    • с оптимизацией (~6 900 с)
  • На Postgres 13 поведение совсем другое:
    • без оптимизации (~8 с)
    • с оптимизацией (~8 с)
  • На Postgres 14 особых отличий нет
  • На Postgres pro 13 поведение подобное обычной версии
  • На MS SQL Server
    • без оптимизации (16 с)
    • с оптимизацией (14 с)

Как так получилось? Оптимизация ухудшает производительность! Давайте взглянем и сравним поведение старшей версии Postgres 13 и младшей Postgres 11. Пример отличий в работе планировщика - в старшей версии по условию вхождения групп ищется по индексу, а в младшей выбирается сначала данные из индекса по условию, а потом накладывается фильтр с группами (это медленнее) см. рис. ниже.
 

 

Резюме:

  • На MS SQL Server оптимизация работает, эффект виден
  • Если вы работаете на 1С в связке с Postgres 11, то срочно переходите на Postgres 13 или 14
  • Не увидел в выбранных тестах отличий Postgres pro от Postgres
  • Качество работы СУБД Postgreres с выходом 13 и 14 версии положительно удивило и еще больше приблизилось к уровню MS SQL
  • На мой взгляд, оптимизация от 1С получилась не однозначной для СУБД Postgres:
    • - запросы стали проще, схема плана стала меньше
    • - серьезного отрыва в производительности увидеть не удалось
  • Можно переходить на версии платформы 1С 8.3.18, 8.3.19 и выше особенно при использовании СУБД MS SQL, положительный эффект будет.

 

См. также

Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы

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

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    3800    spyke    28    

47

Быстродействие типовой 1С

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

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    5825    vasilev2015    19    

38

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

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

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

1 стартмани

15.02.2024    8894    175    ZAOSTG    74    

104

Переход на Clickhouse для анализа метрик

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

Принимать, хранить и анализировать показания счетчиков (метрики) в базе 1С? Почему бы нет? Но это решение быстро привело к проблемам с производительностью при попытках построить какую-то более-менее сложную аналитику. Переход на PostgresSQL только временно решил проблему, т.к. количество записей уже исчислялось десятками миллионов и что-то сложное вычислить на таких объемах за разумное время становилось все сложнее. Кое-что уже практически невозможно. А что будет с производительностью через пару лет - представить страшно. Надо что-то предпринимать! В этой статье поделюсь своим первым опытом применения СУБД Clickhouse от Яндекс. Как работает, что может, как на нее планирую (если планирую) переходить, сравнение скорости работы, оценка производительности через пару лет, пример работы из 1С. Все это приправлено текстами запросов, кодом, алгоритмами выполненных действий и преподнесено вам для ознакомления в этой статье.

1 стартмани

24.01.2024    3766    glassman    17    

38

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

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

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    7356    doom2good    49    

65

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    9981    ivanov660    6    

76

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

Казалось бы, КОРП-системы должны быть устойчивы, быстры и надёжны. Но, работая в рамках РКЛ, мы видим немного другую картину. Об основных болевых точках КОРП-систем и подходах к их решению пойдет речь в статье.

15.11.2023    5610    a.doroshkevich    20    

72

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

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

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

11.10.2023    16985    skovpin_sa    14    

102
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. sapervodichka 6823 07.04.22 13:34 Сейчас в теме
Красота, Владимир, спасибо за проделанную работу и легкоусвояемые выводы!
METAL; Дмитрий74Чел; SerVer1C; ivanov660; +4 Ответить
2. babys 90 07.04.22 14:20 Сейчас в теме
Хорошие выводы :)
ivanov660; +1 Ответить
3. Kaval88 172 07.04.22 15:28 Сейчас в теме
Прекрасная статься)
ivanov660; +1 Ответить
4. Brawler 455 07.04.22 20:20 Сейчас в теме
Включите производительный режим RLS в ERP, погоняйте тесты.
Отличия есть?
5. ivanov660 4359 07.04.22 21:02 Сейчас в теме
(4)
1. Это другой механизм RLS. В нем нет подзапросов не относящихся к защищаемому объекту, соответственно, работать данный платформенный алгоритм не будет (который рассматривается в статье).
2. Да, отличия в производительности будут.
3. Просто так взять и погонять тесты мы не можем и не будем, для нас должна быть какая-то выгода или задача. Демонстрационную базу брать для выполнения тестов бессмысленно из-за отсутствия достаточного количества данных. Текущая реализация нас не устраивает полностью, поэтому сейчас мы проводим анализ на необходимые доработки, затем будем проверять и согласовывать вариант с заказчиком. Это вопрос достаточного количества времени. Когда "звезды сойдутся" и все получится, тогда мы обязательно расскажем.
METAL; mrChOP93; sapervodichka; +3 Ответить
6. Brawler 455 07.04.22 22:03 Сейчас в теме
(5) Правильно ли я понимаю, что новый придуманный 1С в БСП механизм производительных RLS настолько плох?
7. ivanov660 4359 07.04.22 22:44 Сейчас в теме
(6) Отвечу за себя, а выводы делайте самостоятельно:

На мой взгляд, реализация данного механизма не доведена до кондиции.

Идея его реализации позволяет избежать основной проблемы "старого" шаблона - линейного роста количества условий в ограничении доступа к таблице от количества накладываемых прав и количества таблиц в запросе. Т.е. чем больше прав на один объект данных вы назначите, то тем больше условий через "И" добавится в секцию "ГДЕ" SQL запроса платформой. Новый подход должен исправить эту проблему.

С другой стороны архитектурно алгоритм предлагает все данные базы запихать в две таблицы - ключи объектов и ключи регистров и за счет этого добиться применения единого условия. А теперь вопрос! Какой размер будет этих регистров на большой базе? Я видел базы в которых только в одном регистре было несколько миллиардов записей. Сотни и тысячи миллиардов? И другие проблемы подобного рода.

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

Интересная статья тут: Производительность механизма ограничения прав на уровне записи в «1С» (RLS).
chemezov; Yashazz; mrChOP93; sapervodichka; +4 Ответить
8. support 4449 08.04.22 22:20 Сейчас в теме
Спасибо за такие статьи!)
ivanov660; +1 Ответить
9. karpik666 3783 09.04.22 11:58 Сейчас в теме
Спасибо, очень интересно и познавательно
ivanov660; +1 Ответить
10. Yashazz 4734 10.04.22 10:12 Сейчас в теме
Очень так себе публикация. По сути, просто разбор одного случая, одного "кейса" в сугубо конкретных условиях. Скриншотики, 2-3 замерчика одного запроса. На "статью" не тянет. Ну не у всех ERP, и не всем нравится использовать очередную уродливую поделку от 1С, которую по недоразумению называют "механизмом БСП".

Нет, я понимаю, что пионэры млеют и исторгают восторги уже от одного только упоминания слов "профайлер", "план запроса" и пары сканов, но - извините, мне маловато. Нет экстраполяции, нет категоризации, нет более охватывающего анализа. Не айс.
user1040271; +1 3 Ответить
11. ivanov660 4359 10.04.22 12:15 Сейчас в теме
(10) Дерзайте, напишите статью, проведите исследование, покажите мастер класс. Прям из вас так и льется последнее время желчь(
1. Механизм БСП думаю занимает более 90% используемых конфигураций. поэтому это очень актуально. А нравится или не нравится - такова жизнь.
2. Условия анализа довольно серьезные (куда еще круче)?
- провели исследование на 5 СУБД: MS SQL, Postgres pro, Postgres 11, 13, 14!
- использовали 3 версии платформы 1C: 8.3.16, 8.3.19, 8.3.20.
- операционные системы windows server под 1с и postgre SQL, MS SQL; linux под postgre SQL
- база использовалась для исследования с достаточным объемом данных ЗК~600 000 документов, регистр заказов ~7 500 000 записей
- для демонстрации были выбраны 2 примера, все остальные примеры запросов (мы по факту использовали более 20 и 3 различных пользователя с RLS), показывают подобную картинку и смысла приводить их я не вижу.
3. Нам это исследование очень помогло понять причины плохого поведения связки 1С 8.3.19 и Postgres 11. Поэтому считаю будет полезно и остальным.
dimaster; karpik666; PLAstic; artbear; +4 Ответить
12. Yashazz 4734 10.04.22 13:11 Сейчас в теме
(11) Во-первых, ответ в стиле "давай сделай сам, чем критиковать" - это, общеизвестно, аргумент на уровне песочницы, посему мимо. Не говоря уж о том, что это публичный ресурс и каждый имеет право на высказывание своей точки зрения. А вот вы, милсдарь, пытаетесь перейти на личности (это про "желчь"), что вас не красит.
Во-вторых, ни в одной целевым образом написанной, действительно серьёзной, промышленных масштабов системе БСП не используется, либо используется очень фрагментарно, и это многократно говорилось на соответствующих ресурсах самими представителями 1С. Пруфлинков уже не дам, у меня подписка кончилась, но факт. И вот к чему бы оно так, а?
В третьих, никто не оспаривает серьёзность анализа. А вот серьёзность статьи - да. Потому что разобран, подчёркиваю, один случай. Всесторонне, да, но один. А такие вещи, если уж говорить о статье, должны делаться с большим охватом, нежели один фрагмент запроса.
user1040271; +1 2 Ответить
14. ivanov660 4359 10.04.22 13:25 Сейчас в теме
(12)
Я вот считаю, что в данном случае рассматривалась конкретный случай. Он рассмотрен с достаточным охватом для нас. Нужен больший охватите самостоятельно, я с удовольствием почитаю, или может еще кто-то дерзнет.
Это, возможно, похвально что вы подошли с такой мощной критикой, но я тут не диссертацию пишу и не защищаю ее перед сообществом.
Поэтому предлагаю завершить полемику - не понравилось, что же на всех не угодишь, денег и времени не напасешься. На ресурсе еще много других более качественных материалов или появятся вскоре.
13. Yashazz 4734 10.04.22 13:23 Сейчас в теме
Я даже так сформулирую. Вывод "срочно переходите на Postgres 13 иди 14" для любого руководителя будет неубедителен, когда он спросит, сколько разных точек системы вы проверяли в ходе исследования. Когда вы скажете, что речь шла об одном запросе, вам придётся очень потрудиться, доказывая, что это ключевой запрос и через него идёт множество процессов, важных для автоматизации.
15. ivanov660 4359 10.04.22 13:35 Сейчас в теме
(13)
1. Мы готовы для этого руководителя (или другого) провести всесторонний анализ по его критериям или выдать результаты по нашим, пусть обращаются контакты мои видны обсудим сотрудничество, либо пусть обращаются к своим специалистам, которые все это сделают.
2. Я написал что был проведен анализ для более чем 20 ключевых для нас запросов, но т.к. результаты не отличаются сильно, смысла проводить их тут не вижу.
3. Т.е. тут кейс, в котором расписано, что, где и как влияет, где смотреть и на что - этого достаточно для специалиста. Хотите расширенную аналитику для других конфигураций и условий - это уже конкретная задача, которая стоит времени и денег.
16. Yashazz 4734 10.04.22 16:19 Сейчас в теме
(15)
2. Я написал что был проведен анализ для более чем 20 ключевых для нас запросов, но т.к. результаты не отличаются сильно, смысла проводить их тут не вижу.
ОК, принято.
17. user1040271 13.04.22 11:36 Сейчас в теме
Пара вопросов к автору:
1. С какой версии платформы 1С появилась поддержка PostgreSQL 14?
2. Какая версия MS SQL Server использовалась в анализе?
18. ivanov660 4359 13.04.22 12:45 Сейчас в теме
(17)
1. Информацию по поддержке платформой 1C версий Postgres смотрите на its
2. MS SQL 13.0.5026.0
19. user1040271 13.04.22 13:50 Сейчас в теме
(18)
Смотрю здесь и вижу что в 8.3.20 появилась поддержка PostgreSQL 13.

Для конфигурации которая была предметом анализа перешли на PostgreSQL 14 или только другим посоветовали?
21. ivanov660 4359 16.04.22 09:26 Сейчас в теме
(19)Есть базы на 11, на 13 и на 14м.
22. aqis 12.10.22 09:23 Сейчас в теме
(21) Подскажите пожалуйста, почему не используется 12?
23. ivanov660 4359 12.10.22 13:09 Сейчас в теме
(22) Несколько причин:
- На момент, когда анализировали на что перейти, были доступны уже 12, 13 и в релиз вышел 14. По идее каждая новая версия лучше (не учитываем детские болячки). Хотели попробовать 14, но если вдруг слишком сырой, то 13 была как план Б.
- Проверять все версии слишком ресурсоемко.
Оставьте свое сообщение