1С это не только удобный инструмент для быстрой прикладной разработки и кастомизации готовых конфигураций, это еще и идеальный нагрузочный тест в стиле ORM (Object relationship mapping) для СУБД (на текущий момент поддерживается MS SQL, Oracle, PostgreSQL\Pro, IBM DB2). Количество DML порождаемых только одной операцией. Записать() для регистра накопления позволяет быстро найти узкое место вашего сервера СУБД, а кому-то снять розовые очки 0^0 в идеологии ORM решений. Еще с версии 1С 7.7 ORM подход реализованный 1С мне казался очень удобным и правильным поскольку позволял не думать при разработке о многих вещах как
Реальная структура таблиц СУБД, индексов, первичных ключей
Вид СУБД (файловая, Сервер баз данных)
Сколько таблиц и индексов в одном объекте.
И наконец быстрый результат при меньшем количестве кода
Можно сосредоточится на бизнес логике, где визуальное! создание одного объекта на уровне платформы создает все необходимые структуры СУБД, а управление идет через простые методы. Недостатки в производительности были видны и тогда, но ведь компания 1С это решит это со временем думал я? Однако активная работа с горизонтальным маштабированием в 1С 8.2\8.3 см Язык мой враг мой привела к мысли, что это не отдельные недостатки отдельной компании. Это системная проблема идеологии ORM.
Сам тест проведем на реальной базе 1С платформы 8.3 с оборотным регистром накопления с включенными агрегатами (подробнее тут О регистрах накопления в 1С). Смоделируем перепроведение документов с переформированием движений\проводок, частая операция при закрытии месяца либо меньшего периода. Если кому то не интересно как организован накопительный регистр, сразу посмотрите ниже таблицу DML операторов, которые порождает одна операция .Записать()
Такой тип регистра наиболее быстрый для записи, поскольку регистры накопления с остатками или регистр бухгалтерского учета имеют более сложную структуру. Кроме того, при записи в регистры остатков, обращение к итогам происходит при записи в текущую дату, происходит даже если отключены итоги на текущую дату, а период рассчитанных итогов сдвинут на месяц назад. В регистрах накопления с остатками при записи в период с рассчитанными итогами они обновляются при записи каждого блока проводок, что может провоцировать блокировки при интенсивной записи, несмотря на использование механизма разделителей.
В регистре накопления с агрегатами обновление агрегатов производит перенос данных из таблиц движений выбранного регистра в соответствующие таблицы агрегатов. Переносятся те движения, которые были созданы в таблице движений после предыдущего обновления агрегатов. Обновление агрегатов можно делать регламентным заданием, в этом его основное преимущество. Подробнее можно почитать тут Агрегаты оборотных регистров.
Возьмем регистр полупроводок, который расширяет функциональность регистра Бухгалтерии
Все измерения проиндексированы, поэтому индексов замедляющих запись и ускоряющих выборки достаточно. Регистр достаточно типичный – результаты теста на нем можно использовать для предсказания поведения на подобных решениях
На уровне SQL Profiler мы можем увидеть, что происходит при записи в регистр
Полный трейс можно посмотреть тут <>
Поскольку часть логики скрыта в коде платформы, некоторые вещи можно только предполагать, но нам достаточно и этой информации. Допустим мы вставляем набор из нескольких записей подчиненных одному регистратору
Кратко что делает 1С |
Как я это интерпретирую |
|
SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION |
Устанавливаем уровень изоляции и начинаем транзакцию |
|
SELECT … FROM dbo._AccumRg16920 T1 … WHERE ((T1._Fld628 = @P1)) AND (T1._RecorderTRef = 0x00003FFA AND T1._RecorderRRef = @P2) |
Чтение основной таблицы регистра РегистрНакопления.СУУ_КубПрибылейИУбытков (dbo._AccumRg16920), даже если мы исполняем просто метод .Записать() в режиме замещения |
SELECT …. FROM dbo._AccumRgAggOpt18422 T1 WHERE ((T1._Fld628 = @P1)) AND (T1._RegID = @P2 AND T1._Fld628 = @P3) |
Чтение настроек НастройкиРежимаАгрегатовРегистровНакопления РегистрНакопления.СУУ_КубПрибылейИУбытков ( dbo._AccumRgAggOpt18422). Зачем это делать каждый раз при записи? |
|
DELETE FROM T1 FROM dbo._AccumRg16920 T1 WHERE (T1._RecorderTRef = 0x00003FFA AND T1._RecorderRRef = @P1 AND ((T1._LineNo = @P2) |
Удаление предыдущего набора записей из основной таблицы dbo._AccumRg16920. Один DML для КАЖДОЙ записи с LineNo |
INSERT INTO dbo._AccumRg16920 |
Вставка записи в основную таблицу регистра накопления РегистрНакопления.СУУ_КубПрибылейИУбытков. Один Insert на КАЖДУЮ запись регистра |
INSERT INTO #tt1 |
Вставка записи в таблицу #tt1, на КАЖДУЮ запись из основной таблицы регистра накопления |
|
INSERT INTO #tt2 WITH(TABLOCK) …. FROM #tt1 T1 WITH(NOLOCK) GROUP BY DATETIME2FROMPARTS(DATEPART(YEAR,T1._Period),DATEPART(MONTH,T1._Period),1,0,0,0,0,0) ….. |
Подсчет новых оборотов для последующей вставки в таблицу НовыеОбороты _AccumRgDl18433 |
INSERT INTO dbo._AccumRgDl18433 …. FROM #tt2 T1 WITH(NOLOCK) .. |
Вставляем НовыеОбороты _AccumRgDl18433 которые предварительно аккумулировали в #tt2 |
|
TRUNCATE TABLE #tt1 TRUNCATE TABLE #tt2 COMMIT TRANSACTION |
Чистим за собой и делаем COMMIT |
|
|
В общем Вы поняли что SQL Server скучать не будет, когда 50 потоков начнут в цикле обходить свои 1000 регистраторов, которым подчинены наборы записей.
Код выглядит так
//Производим запись наборов в несколько циклов
Для Счетчик=1 По ЦикловПерезаписи Цикл
СУУ_Лог.ЗаписатьСообщение(ИДВызова, "СтартоватьНагрузочныйПоток", Перечисления.СУУ_ВидСообщения.Информация, " Цикл перезаписи № "+Счетчик);
Для Каждого Элем Из МассивНаборов Цикл
Элем.ДополнительныеСвойства.Вставить("Проверять",Ложь);
Если Элем.Количество()>0 Тогда
Для Каждого Запись Из Элем Цикл
//Запись=Элем.Получить(0);
//Одной записи заменить недостаточно, она и запишет только одну а неизмененную записывать не будет
Запись.Период=Запись.Период+1;
КонецЦикла;
КонецЕсли;
Если Элем.Модифицированность() Тогда
КоличествоЗаписей=КоличествоЗаписей+Элем.Количество();
Элем.ОбменДанными.Загрузка=Истина; //Если так сделать – в методе .Записать не будет лишних проверок
Элем.Записать(); //Всего лишь один вызов порождает вышеперечисленное
КонецЕсли;
КонецЦикла;
КонецЦикла;
Тут нужно заметить, что на уровне платформы отслеживается – была ли изменена запись или нет? Только измененные записи записываются, поэтому в нагрузочном тесте я меняю поле Период на одну секунду.
Оптимальность реализации ORM внутри метода .Записать() со стороны 1С это не тема данной статьи.
Может быть это только в 1С «неправильный» ORM?
Если Вы думаете что в «нормальных» языках программирования, например Java все хорошо - прочитайте вот эту свежую статью Bulk update spring JPA про то как обстоит дело в популярном Java Framework – Spring. Где они эти стандарты Java как бы улучшают, делая свои обертки. Там таже самая ситуация – даже если Вы подготовили наборы в памяти Java и сделали saveAll() - на каждую запись будет отдельный DML оператор.
Автор статьи предлагает методы решения
А) JDBC batching – по сути агрегирование однотипных DML операций. Он лечит не причину а следствие
Б) Написать свой метод с одной DML операцией на несколько записей. Это радикально, но по сути мы уже строим свой ORM, а не используем стандартные методы.
Проблема в том, что базовые методы JPA ориентированы на работу с отдельными записями по первичному ключу, а не наборами по списку первичных ключей. Ну а разработчики используют, то что им предлагают. Ведь если сервер СУБД достаточно мощный – он справится? Посмотрим
Почему много маленьких DML это плохо
Запускаем тест с параметрами
50 параллельных потоков делают запись в регистр накопления, изменяя у каждой записи время на секунду.
Железо для СУБД у нас ориентировано на высокую нагрузку и работает с 2014 года
Дисковая подсистема реализована на SSD на оборудовании HP MSA2040, с высокоскоростными контроллерами. Жесткие диски SSD HP 400GB 2.5''(SFF) SAS ME 6G Hot plug SSD for MSA2040. Если посмотреть IOPS тут MSA 2040 от HP, то на запись где-то 32 тысячи, а на чтение 85 тысяч в режиме Random. Много это или мало - видно по результатам ниже. MS SQL Server 2019, памяти 64 гигабайта. 1С на отдельном сервере приложений такой же конфигурации, но только с одним Raid1 для ОС и приложения
Сначала смотрим SQL Server waits именно они покажут нам, где больше всего ждет SQL Server
Чемпион по Wait Transaction log, за ним идут Wait на запись в СУБД из буфера. Результат не является неожиданным – похожее видели, когда исследовали проблемы производительности в виртуальной среде см статью 1C в Матрице виртуализации. Много мелких DML операций (не путать с транзакцией) должны пройти через Transaction log в определенной последовательности, определяемой транзакциями.
Картинка ниже показывает, как работает лог. Видно, что данные пишутся в асинхронном режиме. В последних версиях появились даже Delayed transaction, причем у на Хабре докладывают, что производительность растет Delayed Durability или история о том как получилось ускорить выполнение автотестов
Если посмотрим на IOPS по SSD Диску D: где находится и лог и нужные нам таблицы СУБД – мы далеко от максимума его возможностей
Количество трансферов на диск SSD (фиолетовый) не превышает 20 тыс - это далеко от максимума, на который он способен даже если не учитывать реальные размеры блоков. Очереди при большом количестве IOPS фиксируются (голубой).
На таблице ниже видно, что IOPS не зависят от количества Batch Request( фиолетовый пунктир ), процессорного времени (красный). В момент возникновения очереди закономерно растет Avg.Disk sec/transfer (желтый).
Таблица ниже показывает, что количество Compilations (фиолетовый жирный) гораздо меньше Batch request (фиолет пунктир) – значит все-таки повторное использование планов мелких DML работает
И наконец отдельно статистика по логу. Сброс Flushes (песочный) идет равномерно как Log Bytes Flushed (голубой). Ожидания тоже распределены равномерно Log Flush Waits (зеленый).
На этой картинке видно что Disk queue length (голубая ) находится в противофазе к Log flushes (желтая) – это означает что SSD для лога НЕ является узким местом
Пределов для 1С нет, но есть узкое место в СУБД
Выводы очевидны.
Создать много потоков для записи в 1С труда не составит, учитывая возможность подключить нужное количество серверов к кластеру (сервер приложений) 1С. Однако много мелких DML от 1С упрутся в Transaction log даже если он на SSD диске и остается запас по IOPS, просто по архитектурному построению СУБД. Из статистики выше Вы видели, что waits по Writelog не только заняли 50%, но еще среднее время ожидания достаточно большое 0.0132 против 0.001 в нормальной ситуации. Исправить это можно только укрупнением DML операторов (не путать с транзакцией), но это можно реализовать только под капотом платформы поскольку сейчас на каждую запись делается один INSERT\DELETE. СУБД бессильна против такого подхода к реализации ORM (Object Relationship Mapping).
Можно конечно заняться более глубоким тюнингом Transaction log (тема отдельной статьи), но это кардинально не решит проблемы из за его архитектуры последовательной записи. Напр в Oracle есть разделение на Transaction log и Redo log с более совершенной архитектурой, но он тоже имеет пределы по количеству мелких DML в секунду
Архитектор 1С может использовать данную ниже статистику при планировании возможной нагрузки на решение. Запись\удаление самая тяжелая операция, которая кроме изменения данных таблиц меняет связанные индексы. Природа Transaction log как узкого места реляционной СУБД – предполагает зависимость от последовательности транзакций, поэтому количеством ядер ее не решить, а рост тактовый частот уже не тот.
Как видно из таблицы скорость записи в параллельных заданиях неравномерна. Причины разные.
Под документом регистратором может быть разное количество записей в регистре накопления, а метод .Записать() идеологически может быть выполнен только по одному регистратору
Общее количество документов в заданиях примерно одинаково, но общее количество записей под документами регистраторами разное
Количество заданий 50 а 1С 8.3 пытается дать всем заданиям равномерно отработать, но мы сейчас не ставим задачу максимальной производительности 1С, просто делаем нагрузочный тест СУБД. На сервере приложений общая загрузка процессора не более 30 процентов. На нас даже задержки 10 гигабитной сети не влияют поскольку, распараллеливание использует широту пропускания
Посчитаем общее количество записей в секунду которое мы можем записать с распараллеливанием. Если суммировать средние по заданиям то будет 1374 если от общего проведенного то 1397 записей в секунду.
Т.е. данный регистр позволяет записывать, без дополнительных операций чтения подготовки и т.д. 1400 записей (ORM записей ????) в секунду на указанном оборудовании. Если вы ходите провести 1 миллион регистраторов, где у каждого в среднем 5 движений \проводок. У Вас это займет минимум 3571 секунду, т.е почти час. Исходя из этой цифры Вы можете оценить хватит ли Вам технологического окна для закрытия месяца или других действий. Напр у российского брокера – 1 миллион операций в день, может быть обычным объемом.
Что важно – оборудование позволяет пропустить больше IOPS, но много мелких DML не позволяют эффективно использовать Transaction log.
Конечно можно оптимизировать регистр, убрать «лишние индексы» и еще перечень мероприятий достойных отдельной статьи, но Вы не уберете красную линию – вы просто ее отодвинете.
Может быть бывает правильный ORM?
Детальный разбор записи в регистр накопления через SQL Profiler (см выше), сразу показывает, что компания 1С может оптимизировать алгоритм записи в регистр накопления, даже в существующей идеологии (запись наборов по одному регистратору), но это лишь отодвинет момент Bottleneck в Transaction log. Как это возможно поменять кардинально – описано в первой статье цикла Язык мой Враг мой путь один трансформировать методы с записи одиночных объектов (регистратор набора записей), на запись наборов объектов (массив регистраторов набора записей).
Фактически в организации методов ORM должна быть заложена не только возможность пакетной записи по нескольким значениям первичного ключа, но ее эффективная внутренняя в базе реализация крупными DML. Я вижу многие ищут убийцу 1С, но без эффективного с точки зрения СУБД ORM он таковым не будет. Надеюсь в комментариях кто-нибудь покажет такой ORM – ориентированный на пакетную запись объектов, будет печально если его не существует ☹. Если 1С сделает это первой (а они об этой проблеме знают), то это будет самый качественный ORM на рынке со всех точек зрения. А оптимизировать 1С есть что – на оптимизации только механизма общих реквизитов\разделителей можно в 2 раза план запросов улучшить см Лучшее соединение враг хорошего.
Вторая сторона медали – горизонтальное маштабирование. Понятно что всю запись на один Instance СУБД бесконечно возлагать не возможно, даже если решить проблему с мелкими DML. Распределение таблицы по отдельным узлам в духе Map Reduce резко усложнит разработку, если только платформа 1С не возьмет это на себя.
Хорошим и дорогим вариантом может стать Oracle Real application cluster Oracle Real application cluster, который позволяет работать с одной СУБД из нескольких Instance. В принципе он может быть достаточным для большинства приложений с интенсивным OLTP. Но бомбить его мелкими DML не стоит, сначала нужен навести порядок внутри ORM и сделать возможность записи нескольких объектов более крупными DML. Если нагрузка за запись сильно велика, что не спасает Oracle Real application cluster то тут единственный вариант делить ее на несколько баз данных с разными transaction log, но это не тема для 1С. С нагрузкой по чтению вообще проблем нет, поскольку она хорошо кэшируется и есть много вариантов с ней работать.
Не следует путать Oracle RAC c разного рода репликациями, которые могут снизить нагрузку только по чтению и повысить отказоустойчивость. Его кстати тестировали еще 10 лет назад
Тест 1С на Oracle RAC и как я понял Microsoft нечего было на это ответить ???? Microsoft не согласен с правилами теста
Из конкурентов сейчас я нашел только решение от Amazon Amazon Aurora
Мы ответственны за тех, кого научили
Вообще программный код библиотек уже перерос задачу повторного использования кода (использование готовых решений). Любая библиотека несет в себе Best Practice, реальное использование Patterns или говоря просто – т.е. “мы изучили как лучше эти задачи решать и закодили это в библиотеку\framework”. Для примера можно взять библиотеки на Java для RabbitMQ
Где в «нормальных» языках приучаться к правильной архитектуре как не в библиотеках к языку?
По Patterns? Ну почитайте эти книги по паттернам, там пишется как рекомендуется сделать, но не Best practice проверенный опытом. На работе некогда наступать на одни и те же грабли.
Но разве строить библиотеку ORM так чтобы бомбить мелкими DML Вашу СУБД это лучшая практика?
В целом попытка отделить программиста от СУБД кончается игнорированием вопросов внутреннего устройства. Да это необходимо для построения сложных систем, но тогда нужно заботится о том, чтобы библиотека давала Вам хороший стиль программирования.
Позиция 1С понятна по поведению - им на данном этапе важен только функционал, а вопросы производительности вторичны. Но почему тогда при проектировании стандарта JPA в Java, где community людей с разным опытом заложен тот же изьян? Понятно, что архитектор свободно плавающий и в устройстве СУБД и в устройстве программ, должен иметь редкую трудовую биографию чтобы знать все, но community предполагает синергию от взаимодействия разных специалистов. Тут хорошо почитать эту статью про историю логгирование в Java История логгирования в Java из нее сразу становится понятно почему в Java можно легко найти решение на любой случай, но так трудно найти качественное решение. При всех равных я сторонник платных решений, по крайней мере 1С достаточно демократична в обратной связи с клиентами и общими усилиями продукт улучшается. Какой ORM будет использовать убийца 1С, покажет будущее и надеюсь подскажут читатели данной статьи – невозможно изучить их все. До новых встреч на нашем телеграмм канале t.me/Chat1CUnlimited
Комментарии (62)
ptr128
05.11.2022 11:39+3С одной стороны, ORM позволяет существенно снизить стоимость разработки, которая, обычно, существенно выше стоимости железа. С другой стороны, опыт показывает, что "золотых молотков" не бывает.
Следует понимать, что императивный язык программирования (1С, Java) по своей сути плохо стыкуется с декларативным (SQL). Отсюда два варианта. Либо добавлять в язык декларативные возможности, позволяющие транслировать конструкции языка напрямую в SQL, обрабатывая одним запросом массивы данных. Либо напрямую использовать SQL, там где это необходимо. Вплоть до вынесения какой-то части бизнес-логики в хранимые процедуры.
1CUnlimited Автор
05.11.2022 12:23+1С моей точки зрения можно просто библиотеки ORM доработать на пакетную запись/удаление нескольких экземпляров обьектов, а реализация под капотом через крупные dml. Да возможно придется от стандартов sql отойти и использовать table of record в памяти субд но я думаю и сейчас полностью стандарт соблюдать внутри orm не возможно
ptr128
05.11.2022 12:43Так в том то и проблема, что в рамках императивного языка это можно только частично. Ну не подразумевает императивная парадигма параллельной обработки сразу массива данных. Отсюда, кстати, и необходимость в out-of-order execution с предсказанием ветвлений в CPU.
1CUnlimited Автор
06.11.2022 13:00Может я чегото не понимаю, но почему нельзя разделить массив данных на таблицы по 100 записей и передавать их на СУБД как Table valued параметры . jOOQ 3.10 Supports SQL Server’s Table Valued Parameters – Java, SQL and jOOQ. или Table-Valued Parameters in SQL Server (sqlshack.com) или Using Table-Valued Parameters in SQL Server and .NET (sommarskog.se) . Есть масса вариантов в MS SQL и Oracle точно. Эти Table valued переданные на сервер СУБД дальше просто использовать для крупных DML. Но почему то известные мне ORM под капотом так не делают . Врядли это из за следования стандартам sql (кстати последний уже 2016)
ptr128
06.11.2022 14:38+1Можно, но это все же менее эффективно, чем INSERT BULK (SqlBulkCopy в C#). Так почему бы явно им не пользоваться, если надо вставить больше нескольких десятков записей?
На самом деле задача какая-то надуманная. Ну сколько строк и как быстро пользователь вколотит? Для импорта из того же Excel или еще чего-то - INSERT BULK. А если надо трансформировать данные из БД и результат туда вставить, то это просто выходит за рамки применимости ORM. Не хотите на SQL писать - используйте тот же SSIS, который под такие задачи заточен.
1CUnlimited Автор
06.11.2022 16:38Почему задача надуманная. Классический пример из 1С это перепроведений документов за месяц для фин учета. Иногда по нескольку раз делают в процессе сверки. Это инициирует формирование проводок, а если там их 30 миллионо только документов за месяц, все эти пакеты без распараллеливания не провести ну и утюжит базу очень хорошо. 1С это ORM в чистом виде даже без возможностей прямых запросов к СУБД, я думаю многие другие прикладные системы тоже по данной идеологии делают, чтобы сохранить целостность данных.
ptr128
06.11.2022 17:35И зачем это делать средствами ORM? Подключайте ADODB через COMОбъект - и будет Вам счастье.
Хотя я предпочитал так не делать, ограничиваясь только штатными средствами системы для помещения задания в очередь на выполнение (в терминах 1С - просто создание документа). А уже внешний планировщик сидел на CDC и запускал асинхронно хранимые процедуры-обработчики, в пределах выделенного ему пула соединений. Пользователь же перенаправлялся на отчет, в котором мог наблюдать процесс обработки, обновляя отчет. Уведомление о завершении обработки, опционально, приходило пользователю по e-mail.
timka05
05.11.2022 17:36А о чём статья? О том, что ещё один слой абстракции над данными замедляет работу? Так это как бы очевидно.
О том, что ORM от 1С работает неоптимально? Да в 1С таких приколов полно. И не исправляют годами. Достаточно поискать на инфостате статью про "Все операции" (ныне "Функции технического специалиста"), где меню собиралось мин 2 вместо нескольких секунд, только потому, что у разработчиков 1С руки оказались откуда-то из плохого места.
1CUnlimited Автор
05.11.2022 20:04А о чём статья? О том, что ещё один слой абстракции над данными замедляет работу? Так это как бы очевидно.
Основной посыл статьи в том, что в СУБД для производительности рекомендуют использовать один крупный dml опрератор insert update delete вместо 10 мелких. Но реализация современных orm нацелена на создание 10 мелких dml вместо одного крупного. А поскольку СУБД это разделяемый ресурс Orm следует подстраивать идеологию под СУБД а не наоборот .
Наличие слоя абстракции над данными не обязательно замедляет работу . Возьмите тот же ORM 1С - я логику вычисления сумм проводок (движений) реализую на сервере приложений. Это можно сделать пакетным образом (по 1000 документов ) как тут описано https://habr.com/ru/post/674282/ , а для СУБД остается только метод .Записать() для каждого документа (с учетом ограничений 1С) . В итоге я архитектурно распределяю нагрузку между сервером приложений и СУБД. При этом даже 1С процессы я могу так раскидать по серверам приложений, что они по производительности будут превосходить ресурсы любой СУБД
GoodGod
05.11.2022 20:03Допустим вы загрузили из базы 10 товаров и у них поменяли prop1 = value1. А у четных товаров еще поменяли prop2 = value2, просто для сферического примера.
Когда вы сохраняете товары по-одному, у вас товар сохраняются единой сущностью, т.е. либо весь товар сохранен, либо весь не сохранен. Т.е. генерируется запрос update product set prop1 = value1, prop2 = value2 where id = 1. И так для 10 товаров по очереди.
А если оптимизировать скорость и разбивать данное обновление с помощью крутой ORM автоматически на 2 SQL запроса: update product set prop1 = value1 where id in (1, 2, 3, ..., 10), а потом update product set prop2 = value2 where id in (2, 4, 6, ..., 10), то в случае невыполнения 2-го запроса, т.е. если он завершился с ошибкой у вас в базе и в вашем аппликейшене будет полностью мешанина из обновленных данных и не обновленных - часть полей обновилась, часть не обновилась, непонятно что обновилось а что не обновилось, и у каких товаров. Откатить выполнившиеся update'ы в базе не всегда возможно, по-этому придется проверять и разруливать ситуацию в ручную всегда, писать кастомный код, а разрулить данную ситуацию в плане объектов ORM будет в ну очень сложно.
По-этому для таких ситуаций пишут голый SQL, которые сделает необходимые обновления. И это гораздо проще, чем потом разрулить ситуацию с частично обновленными 10 товарами. Т.е. прям в коде пишешь функцию: update product set prop1 = :prop1_value where id in (:prop1_update_ids). И потом еще один SQL: update product set prop2 = :value2 where id in (:prop2_update_ids), и всё, код очень простой. По сравнению с тем, что надо писать метод прохода по 10 товарам и проверки, что в них обновилось а что - нет и отката всего до какого-то нормального состояния когда автоматический откат невозможен (а непонятно что ORM смогло откатить назад автоматически, а что не смогло, по-этому проверять надо всё).
1CUnlimited Автор
05.11.2022 20:56А почему для одной сессии нельзя это делать в одной транзакции? Даже в 1С есть понятие транзакция которая работает на уровне платформы и влияет сразу на объект. Там вложенных транзакций нет, но я не знаю почему они не смогли. И ORM Достаточно быть эффективным в рамках одной сессии .
GoodGod
05.11.2022 21:13Я тут подумал, скорее всего эффективный батчинг - это реально то место, где можно что-то доработать, и действительно является недоработанным местом текущих ORM, и там можно что-то сделать. Но скорее всего там будет другой метод сохранения сущностей, т.к. нельзя при дефолтном вызове save открывать транзакции, рассчитывать что надо как-то доработать клиентский код под откат батчинга и т.д. Пока катимся на голом SQL.
ijsgaus
05.11.2022 23:35+1Факт пр ORM известный - за использование упрощенной модели взаимодействия объект<-> реляционная БД (лень разработчика и экономия его времени - эфемерная кстати), придется платить клиенту. Чаще всего не только за железо, но и за разболтанные нервы и впустую потраченноле время на ожидение пользователя. Хотя чаще всего тут ДАЖЕ не нужно массовых вставок, достаточно одного-двух мерджей и индекированной вьюшки. Я лично уже несколько лет пользую Linq2Db - прекрасную возможность писать практически на Sql, но с проверками типов на этапе компиляции. К сожелению, библиотека строго под Csharp, аналогов этой библиотеки я не встречал в других языках и экосистемах.
ptr128
06.11.2022 13:55+1Часто бывает, что на C# один разработчик, а на SQL - другой. Чтобы упростить их взаимодействие, приходится код на SQL оформлять в виде VIEW, функции или хранимой процедуры.
Я ни в коем случае не утверждаю, что не бывает разработчиков, профессионально владеющих и C#, и SQL. Сам такой. Но нет никакой уверенности в том, что на стороне заказчика есть и будет такой специалист. Поэтому, даже когда пишу сам, то если ORM не применима, оформляю SQL код не в строках C#, а VIEW, функцией или хранимой процедурой. Да и читать потом удобней, особенно если запрос развесистый на несколько экранов.
PROgrammer_JARvis
06.11.2022 19:55К сожелению, библиотека строго под Csharp, аналогов этой библиотеки я не встречал в других языках и экосистемах.
Java: jOOQ: query builder с максимально близким к SQL DSL и проверкой типов на этапе компиляции
Rust: Diesel: гибрид ORM и query builder'а, работающий поверх статически генерируемой схемы
Rust: Sqlx: статический валидатор и маппер SQL-запросов
borovinskiy
Тезис, что вы не уперлись в жесткий диск не обоснован.
Его легко проверить: поставьте optane или даже ramdisk и проведите тест еще раз, если есть существенное ускорение, значит в диск вы все-таки уперлись.
Поясню, почему ваши выводы про диск преждевременны.
1. Если система не работает быстро, значит она где-то имеет бутылочное горлышко, оно может быть в процессоре, шине, памяти, диске.
2. То, что показывает вам система как уровень нагрузки не обязано означать то, что вы думаете. На самом деле вы не знаете, что значит загрузка 100% (как и я, кстати).
3. Производительность SSD сильно зависит использует ли по direct-запись. Сброс же кеша (не который SQL flash, а который дисковый flash) оказывает катастрофическое воздействие на скорость SSD, так как SSD без сброса кеша сразу рапартует, что типа он записал, но по факту гарантия записи будет только по flash на SSD.
4. ПО баз данных хочет быть уверено, что все записалось, поэтому часто делает flash на дисковую систему.
5. Так как flash делается часто, а запись последовательная, ни о каких 20-30 тыс. random IOPS не может быть и речи.
6. У вас диски не напрямую в сервер воткнуты, а через хранилку, хранилка может иметь собственную логику обработки flash с собственными кешами и как она работает конкретно в вашем случае - не известно.
7. Скачки в IOPS на графике с записью журнала однозначно говорят, что есть корреляция и видимо какой-то периодически запускающийся процесс. Но нет причин утверждать только лишь из графика, что диск не нагружен. Положим профиль нагрузки такой: запиши 16к, сделать flash, долго ждать flash, запиши 16k... так как это последовательная запись в журнал, она будет долгой, так как латентность из-за flash каждой записи большая, но вот только накопитель у вас на 100% при этом не нагружен! Вы бы могли параллельно еще сделать 10 очередей и производительность выросла бы (так как SSD свою пиковую производительность показывают на большом количестве очередей, а на одной очереди она обычно 50-100МБ/c).
8. Если бы база данных могла писать не последовательно в журнал, а параллельно в несколько потоков, то производительность была бы выше, но база данных так не умеет.
9. Ну и наконец, график загрузки ясно говорит, что основное время занимает запись в журнал, процессор не нагружен а значит вариантов где может быть затык остается не сильно много, ага?
В качестве вывода: да, большое количество запросов на запись отрицательно сказывается на скорости этой записи, здесь можно ускоряться, но на самом деле причина такой работы находится в архитектуре СУБД, которая до сих пор оптимизирована для работы с жесткими дисками и пишет в журнал с частыми flash-ами.
1CUnlimited Автор
Прям заинтриговали :) т.е . Сайзингу ssd в спецификациях верить нельзя , счетчикам ос тоже (и это без всякой виртуализации!) .
Вопрос - почему тогда avg waits sec по записи в субд 0.0034 в разы меньше чем в logwriter 0.0131? SSD не всем пишет одинаково, если я в него уперся?
Я конечно могу сделать крамолу и поместить log на ram диск, но хотелось бы знать Ваши аргументы это личный опыт или есть пруфлинк
borovinskiy
Не очень понимаю о каком avg waits sec идет речь, но вот пруф лично измеренной зависимости работы СУБД от SSD: https://elibsystem.ru/node/586#pgbench
Спецификация SSD верить можно, просто они снимаются без flush на диск (ну или с неизвестной очередностью flush). И почему вы тогда берете random IOPS? Берите последовательный, чего мелочиться, запись ведь в лог транзакций последовательная (кстати, почему вы решили, что СУБД в журнал пишет по 4k, раз сравниваете IOPS с random 4k?) А потом возьмите fio и проверьте скорость последовательной записи при flush после каждой операции. Удивитесь, что IOPS не походит ни на random ни на sequence.
Потом можно еще раз подумать и понять, что запись на самом деле не случайная и не последовательная, а ее профиль вообще говоря от нагрузки зависит и тогда придет понимание, что и бенчмарк на левой базе не даст вам точные значения по которым вы сможете количественные выводы делать, а в лучшем случае приблизительные, которые можно ожидать, если у вас будет на реальной базе схожая нагрузка, чем и в той ситуации, с которой вы сравниваете.
В итоге: проводите бенчмарки реальных баз, тогда вы и получите конкретные цифры производительности, а синтетика не про точные цифры, а про поведение при различных профилях нагрузки на конкретном оборудовании.
На RAMDISK добрые люди уже проверили: https://habr.com/ru/post/694554/
1CUnlimited Автор
По wait я ответил тут https://habr.com/ru/post/697504/comments/#comment_24886618, по поводу RAMDISK на который Вы предлагаете поместить Transaction log чтобы была "чистота" эксперимента хочу спросить - Вы сами приведенную ссылку https://habr.com/ru/post/694554/ читали? Видели что там RAM диск проиграл SSD на записи особенно? Если многие хотят убедится что проблема не в диске, RAM это эталон. Ок я могу прогнать это на RAM , давайте ссылку на RAM DISK который
а) Можно скачать для win 2016 бесплатно хотябы на триальный период
б) Диспетчер позволяет обслуживать много потоков.
Последнее поясню, сейчас часто пишут ПО которое не использует многоядерность по факту. Т.е. диспетчер ram садится на одно ядро и как только оно загрузится вся скорость превращается в тыкву.
edo1h
а что счётчики ос?
вот смотрите, вы пишете:
понятное дело, что вас внешняя полка (что для нагруженных БД не очень интересно на мой взгляд), но даже с учётом этого задержки чрезмерно велики.
у меня, если честно, в голове не укладывается, как вы можете глядя на задержки в/в в 0.1с говорить, что дисковая подсистема справляется. это даже для hdd много, а для ssd это на три порядка (!!!) выше задержек накопителя.
понятное дело, что эта ваша msa по сегодняшним временам выглядит уныло, на чтение она всего лишь немного быстрее одиночного sata-диска, а на запись так вообще медленнее, но всё равно результат слишком плохой. уж не гигабитным ли iscsi у вас подключен сервер к схд?
1CUnlimited Автор
Ответ для @edo1h и @borovinskiy ниже картика waits со стороны SQL для SSD raid на MSA2040 где проводился тест. Raid1 . Для понимания нужно применить синтез анализ и сравнение.
Факт 1 - AvgWait_Second показывает среднее время задержки которое включает в том числе работу подсистем SQL на для выполнения операции. Т.е. не только SSD, контроллер (Fibre channel) , а еще работу процессов самого MS SQL
Факт 2
Write log получился 0.0132 это много
Но PageIoLatch (файлы данных СУБД) - от 0.0017 до 0.0034
Применяем сравнение - странно почему на одном рейде такая разница если эти две подсистемы SQL пишут в одно место? Из за разных размеров блока?
Ну хорошо - повторю тест уменьшив число параллельных потоков в 2 раза
Вау - задержка на writelog уменьшилась в 2.5 раза. Как так ? Он же пишет одинаковыми блоками.
Синтез - Может быть не в дисковой системе проблема? Про ram диск отвечу отдельно
edo1h
уменьшили нагрузку на дисковую подсистему в 2 раза, после чего задержки упали в два раза.
вы делаете вывод:
гхм… какая-то у вас странная логика:
я сужу по скриншоту метрик дисковой подсистемы: у вас слишком высокие задержки:
https://hsto.org/getpro/habr/upload_files/552/3d1/08b/5523d108beb3a69082fa500c8d15239c.PNG
ну не то что верить нельзя, но нужно внимательно читать. а лучше перепроверять.
если можете, попробуйте поставить fio и запустить такой ps-скрипт:
(пути в первых строчках, разумеется, надо заменить на актуальные для вас)
вот что у меня выдал локальный nvme:
P. S. знатоки ps тут есть? почему после первой строки
--numjobs…идёт пустая строка?1CUnlimited Автор
Основной посыл статьи в том, что в СУБД для производительности рекомендуют использовать один крупный dml опрератор insert update delete вместо 10 мелких. Но реализация современных orm нацелена на создание 10 мелких dml вместо одного крупного. А поскольку СУБД это разделяемый ресурс Orm следует подстраивать идеологию под СУБД а не наоборот
murkin-kot
Вы сами выше писали про JDBC batch, но потом как-то невнятно от него отказались. А между тем, он как раз и компонует несколько мелких запросов в один большой. Так что с этой стороны в нормальных ORM всё хорошо.
ptr128
Batch - это всего лишь выполнение группы DML за одно обращение к СУБД. Радикально он ничего не меняет.
А речь, в качестве очень упрощенного примера, о замене сотни
INSERT ... SELECT ... WHERE Id=<something>
на один
INSERT ... SELECT ... WHERE Id IN (<something>)
То бишь, в первом случае будет сотня записей в журнал транзакций, а во втором - только одна. И это не считая прочих издержек.
murkin-kot
Во первых, батч выглядит по другому, примерно так:
Вместо insert into xxx values (1,2,3) будет insert into xxx values (1,2,3), (4,5,6), (7,8,9), ...
То есть БД получит всё необходимое для любых оптимизаций.
Соответственно, во вторых, вместо сотни отдельных актов записи в журнал транзакций будем иметь один акт, включающий все данные. А раз запись на диск одна, то и время сокращается очень существенно, примерно как вы бы и могли ожидать, но почему-то сочли невозможным.
Хотя стоит признать, что умение оптимизировать батчи свойственно не всем СУБД.
ptr128
Вы привели только еще более простой пример, а потому менее показательный. Если вставляемые в таблицу строки идут с клиента, то они были введены вручную, а значит их немного и оптимизировать нечего. Если бы их было много, то при любых раскладах речь бы шла о INSERT BULK или, в крайнем случае, BULK INSERT.
А к реальным задачам мой пример намного ближе, так как запрашивать данные по сети от сервера клиенту, чтобы их же потом, пусть даже после трансформации, вставлять в таблицу на том же сервере - крайне неэффективно. А на больших объемах данных - вообще не реально.
Ну и я не знаю ни одной СУБД, которая умеет оптимизировать пакеты DML запросов. Хотя бы один пример привели бы.
1CUnlimited Автор
В статье Торбена не показан сам итоговый оператор dml который сделал batch показан только улучшенный результат выполнения. Хотя итересно посмотреть trace. Но это не так важно. Если документ или набор записей как обьект, который Вы пишите состоит из двух таблиц и вы пишите Doc1 Doc2 последовательно - jdbc batch не сможет их консолидировать. У Торбена более простой тест.
Еще я думаю что jdbc batch будет узким местом при параллельной генерации запросов в рамках даже одного сервера приложений javaee . Это сколько вычислений нужно провести чтобы разгрести то что настрочил ORM
ptr128
Да методу AddBatch() вообще безразлично, какое именно SQL предложение добавляется в пакет.
Он просто группирует все запросы в один пакет, что сокращает накладные расходы при обращении к БД. А с точки зрения БД, от клиента всегда приходит пакет. Просто, в частном случае, он может содержать только одно SQL предложение. А в общем случае - сколько угодно и каких угодно, за редкими исключениями, когда какое-то SQL предложение допустимо только в начале пакета.
murkin-kot
Две таблицы отображаются на два набора объектов, для каждого набора отдельно генерируется dml, затем два dml в режиме batch идут в БД. Всё прекрасно ускоряется.
Параллельная обработка примерно так же - ничего страшного там нет, пройтись по списку объектов очень легко. Возможно, вы имели в виде работу нескольких клиентов, но их запросы ни в коем случае нельзя объединять в один, так что с этой стороны опять всё обстоит так, как должно.
Большинство мифов про страшный ORM базируется на непонимании как внутренней механики сабжа, так и на непонимании цели, ради которой вообще что-то пишут в БД, Если же понимание есть, то всё отлично оптимизируется. В крайнем случае, если реально упёрлись в недостатки ORM, то грамотный разработчик легко напишет оптимизированный SQL. Но до ограничений ORM чаще всего далеко. Главное ограничение - молодые разработчики.
ptr128
Во-первых, Вы какую-то детскую задачу описываете. На практике же, обычно, есть сложный запрос (c CTE, рекурсиями, оконными функциями и т.п), возвращающий миллионы строк и есть задача, результат этого запроса вставить в таблицу. И на всех ORM, которые я знаю, такая задача решается очень неэффективно.
Во-вторых, объединяя два DML в один пакет, вы экономите только на накладных расходах одного пакета, что очень и очень немного. И ни одна СУБД, которые я знаю, не будут исполнять два DML в одном пакете параллельно. Строго последовательно. Можете убедиться в профайлере.
В-третьих, удел ORM - десятки и сотни записей. Ну максимум тысячи. На миллионах ни одной эффективной ORM я не встречал. Тем более, на миллиардах, с которыми я регулярно сталкиваюсь.
Вашими бы устами, да мёд пить. Я уже не один десяток лет занимаюсь оптимизацией SQL запросов и процедур. И "легко" это не было ни разу. На практике, как только у разработчика на C#/1C/X++/ABAP/Python и т.п. возникает задача оптимизации SQL, эффективней отдать эту задачу специалисту по SQL, оформить в виде VIEW, функции или процедуры, и пусть уже этим первый разработчик пользуется.
murkin-kot
В плане "обычно" как раз есть много очень простых запросов. Ваш же пример с миллиардами (!!!) записей является редчайшим исключением.
Про объединение в пакете - каждый dml уже содержит требуемые от нас сотни записей, поэтому со стороны бд нет никаких проблем его исполнить с одной записью на диск. В оракле есть insert all, в mysql есть возможность напрямую указывать множественные значения, это всё к тому, что разработчики СУБД уже подумали о таком варианте использования и сделали необходимые синтаксические конструкции, а значит они просто обязаны были подумать и о собственно исполнении этих конструкций. Поэтому два dml по 1000 записей дадут нам две записи на диск (во время транзакции, а во время коммита может ещё много чего писаться, здесь я не спец).
Про "удел ORM" согласен. Но в тексте статьи речь шла о сотнях записей, так что всё опять уместно. Хотя в батчах и тысячи легко пойдут.
По разделению труда между базистами и программистами тоже в принципе согласен, но одно дело, когда на каждого программиста нужно по базисту нанимать, и совсем другое, когда большую часть оптимизаций программисты делают сами, что избавляет нас от раздувания (дорогих) штатов. Так что здесь опять нужно просто внимательно смотреть на проблему - есть экономия или нет. Может ваши миллиарды записей действительно требуют спеца по имеющейся в конторе СУБД ради реально нужных и сложных оптимизаций, но в большинстве случаев привлекать дополнительного базиста нет необходимости просто из-за достаточной грамотности обычных программистов. А вот если там набрали молодёжь и они пишут что-то более или менее сложное без проверки старшими товарищами, то да, быстро возникнет потребность всё это оптимизировать.
ptr128
Я же явно написал, для меня обычно. Так как уже лет десять не только крупный, но и средний, а порой и мелкий бизнес, заинтересован в прогнозировании, оптимизации и прочих задачах, которые решаются с использованием миллиардов записей. Ну а миллионы - это вообще нормальное явление даже для 1С. Мне тут недавно тут доказывали, что 1С спокойно справляется с 200 тыс. документами в день. Сколько это будет при закрытии месяца?
В Вашем то примере с INSERT ... VALUES ... ? Не похоже. По одной вижу.
Вот с этим я полностью согласен. Но я же именно об этом и писал. Что средствами SQL обрабатывать миллионы записей - не проблема. А средствами ORM?
Не утрируйте. На большинстве проектов у меня хватало одного специалиста по SQL. Естественно, были и исключения. Зависит от проекта. На оптимизационных задачах и прогнозировании бывало и наоборот, что на одного full-stack разработчика приходилось до трех специалистов по SQL. Но там и задачи специфичные. Как раз уже с миллиардами записей, которые надо фильтровать, чистить, трансформировать и только потом скармливать моделям.
murkin-kot
Я вот только не пойму, зачем вам в data science нужен ORM? Стоило из-за него вообще что-то начинать, когда он принципиально для другой области?
Плюс миллиард даже вам будет нужен редко, потому что нормальная практика давно уже предполагает агрегирование промежуточных данных, а это уже даже не миллионы, а уже где-то тысячи, ну пусть десятки тысяч. Без агрегирования же вы сначала новую модель придумать должны, и только потом чего-то там ей скормить. То есть опять нечасто.
Есть принципиальная возможность, поддерживаемая на уровне языка БД, поэтому я и говорил про оптимизацию на стороне БД. Она тривиальная, вы даже сами о ней знаете, но почему-то предпочитаете возражать. Поступление хоть миллиона инсертов в одну таблицу очень (очень!) легко выявить и обработать.
Вообще, конечно нужен чистый эксперимент. Я чистого не делал, но обычно мои запросы (через ORM) были достаточно производительными для задачи и потому не было нужды дальше оптимизировать. Но если вы принципиально не согласны, то что мешает написать простейший кусок кода в цикле создающий тысячу объектов, а потом скормить эти объекты например hibernate? Для уверенности добавьте внешний цикл шагов на 100, пусть среднее время покажет. Да даже без ORM ведь тривиально - написать инсерт и в цикле его с батчем прогнать, а потом без батча. Тогда будет обоснованное мнение.
Средства ORM служат для ускорения написания кода, а не для ускорения выполнения программы. Но в среднем на стандартных задачах ORM не сильно отстаёт от чистого SQL. На специфических же, разумеется, универсальный инструмент хуже, чем специализированный, тоже давно известная истина.
ptr128
Потому что и для Data Scince необходим пользовательский интерфейс. Там и живет ORM.
Естественно, например, из полутора миллиардов исходных данных получается несколько десятков тысяч временных серий, с длиной всего по 300-400 значений. Вот только чем, спрашивается, выполнять эту трансформацию, как не SQL? Надо чистить данные от явно некорректных записей по ряду признаков. Затем надо фильтровать их, например, медианным фильтром и/или скользящим средним (как обычным, так и назад-смотрящим), и только потом агрегировать (медианой, средним по двум квартилям или еще как). При этом еще имеем ворох справочников, в которых пользователь может указать, например, признаки, по которым событие или даже целый ряд событий должны исключаться из обработки. Или наоборот, управляет весом каких-то событий. В комментарии я могу только очень упрощенно написать о том, как выглядит трансформация, для того, чтобы получить данные приемлемые для обучения модели. Это тема уже для целой статьи, а может и не одной.
Просто это никому не нужно, так как любая СУБД поддерживает массовую загрузку данных. Для MS SQL - это INSERT BULK (предпочтительно) и BULK INSERT (если деваться некуда).
А если нужно вставить сто записей в одну таблицу, то писать сто INSERT ... VALUES (...), вместо одного INSERT ... VALUES (...), (...) ... (...) - граничит с идиотизмом )))
А в одном пакете могут быть вообще-то почти любые DML и DDL предложения. Их то как прикажете СУБД оптимизировать?
А зачем? Он уже 100500 раз проводился. Сравните производительность INSERT BULK (хотя бы загружая данные bcp, которая именно им и пользуется) и любым другим способом. Обнаружите кратную разницу в производительности. Лично я на C#, даже 100 записей вставлять буду INSERT BULK, благо готовый класс SqlBulkInsert доступен их коробки.
И совсем другое дело, если мне действительно нужно в цикле по курсору вставлять в одной транзакции по одной записи и необходимо по ROLLBAK все модификации, в случае чего, откатить. Но это уж точно не последовательность INSERT ... VALUES, а куда более сложный код.
Я вообще-то с этого и начинал:
murkin-kot
Здесь стандартная проблема наибольшего общего деноминатора. ORM должен работать с максимальным количеством БД, поэтому деноминатор в виде относительно древнего стандарта SQL неизбежен. Но с другой стороны batch предназначен именно для ускорения, не смотря на то, что принимает он всё подряд в любой последовательности. То есть ORM базируется на идее эффективного выполнения batch. А уж как конкретно написан этот батч, зависит от реализации библиотек языка, где батч задействован. Но было бы просто глупо сделать в языке батч и не выделять в нём инсерты в одну и ту же таблицу. Зачем тогда вообще он нужен? Вот поэтому выбранный в ORM подход правильный - может кто-то сдуру и сделает дебильный батч, но в среднем по больнице он всё же будет более или менее оптимальный. Так что идиотизма там нет, но есть вполне разумная логика.
Сортировка по типу (update, insert, delete) и по целевой таблице. Что тут сложного?
В нашем случае - для спортивного интереса. Попадётся мне задача со вставкой большого количества записей - попробую. Потому что так будет ликвидировано сомнение.
ptr128
Не вижу никакого смысла поддержки БД до стандарта 1992 года, в котором конструкция VALUES (...), (...) ... (...) уже была. Более ранние стандарты не смотрел.
Во-первых, в ссылочной целостности, так как поля целевой таблицы могут ссылать на первичные ключи других таблиц, которые к моменту INSERT/UPDATE должны быть. И наоборот, другие таблицы могут ссылаться на первичный ключ целевой таблицы и к моменту DELETE/UPDATE таких ссылок уже быть не должно.
Во-вторых, СУБД без понятия, что и где может модифицировать триггер в одной из нецелевых таблиц в целевой таблице.
В-третьих, есть такие сущности, как IDENTITY, SEQUENCE и т.п., которые не смогут обеспечить строго возрастающие значения, так, как хотел разработчик, если выполнять DML предложения не в том порядке.
Так сравнивали уже не раз. INSERT BULK (SqlBulkCopy и Table Valued Parameter) выигрывает с большим отрывом. Например:
https://timdeschryver.dev/blog/faster-sql-bulk-inserts-with-csharp
murkin-kot
Не знаю, насчёт была она там или нет, но знаю, что оракл в 12-й версии её не поддерживал, так что ваши пожелания отправляйте им - они виновники.
Всё это актуально и для bulk insert, а потому давно решено. Ссылки ищут одним и тем же алгоритмом, независимо от количества ссылок. Триггеры чаще всего отсутствуют, а если присутствуют - простые. Только в каких-то там тысячных долях случаев триггеры есть и сложные, тогда БД всё делает последовательно и без оптимизации, но в 999 из 1000 случаев этого не бывает. Sequence так же без разницы, какой диапазон отдать, это одна операция и никак более ни на что серьёзное не влияет.
Здесь проверка только на стороне сервера. Есть ещё клиент с его батчами. Сервер, как показывает тест, легко справляется с тривиальной оптимизацией. И даже bulk insert не сильно улучшает ситуацию в сравнении с множественным инсертом, там возможно на парсинг время уходит. Остальное осталось за кадром - кто и как реально оптимизирует. Скорее всего, серверу вообще не нужна информация от клиента о наличии батча, поскольку очевидно, что её сервер может собрать сам, тогда вообще весь разговор про "неправильный ORM" окажется полностью некорректным. И это наиболее вероятный вариант, хотя в тесте он не отражён явно, поэтому не 100% уверенности.
И да, перец из статьи по ссылке забыл нам рассказать, какую СУБД он использовал. Или у шарпистов по умолчанию всегда ms sql?
ptr128
Просто у Oracle, как и во ряде иных случаях, синтаксис отклоняется от стандарта. Просто тоже самое в нем делается через INSERT ALL. Какая разница?
Во-первых, пользоваться BULK INSERT не рекомендую, это устаревший костыль с кучей ограничений и более низкой производительностью, чем INSERT BULK.
Во-вторых, если надо вставить несколько записей в одну таблицу, то и не пользуйтесь INSERT ... VALUES. А вот если порядок вставки зависит от той же ссылочной целостности или SEQUENCE, во тогда INSERT ... VALUES может потребоваться. Например, в цикле по курсору.
Вы вообще о чем? Как их искать, если в начале выполнения пакета их может еще не быть? Более того, первичный ключ может быть IDENTITY, а значит, будет известен только после завершения INSERT/MERGE из их OUTPUT.
Если на железнодорожной ветке поезда "чаще всего отсутствуют", приляжете ли вы поспать там, положив голову на рельсы?
В ряде случаев, может быть очень большая разница. Например, сохранение порядка полученных показаний с датчика, даже если они пришли в одну миллисекунду, может быть необходимо.
Прочитайте внимательней. Код на C#, всегда выступает в качестве клиента, даже в CLR по context connection.
В статье BULK INSERT даже не пробовали. Только INSERT BULK.
Не может. Именно для явного разделения пакетов и используется не SQL оператор GO в SSMS, SQLCMD и т.п. Во-первых, потому что есть ряд SQL предложений, которые обязаны быть в пакете первыми. Во-вторых, для локализации переменных, так как область видимости DECLARE - пакет.
Достаточно ссылок на используемые им классы и методы, ведущие на сайт microsoft.com
Если бы мы затронули не только MS SQL, а еще и PostgreSQL, то я бы упомянул о volatile функциях.
Если честно, я уже перестаю понимать ход Ваших мыслей. То, что для вставки нескольких строк в одну таблицу можно и нужно пользоваться INSERT BULK я уже указал. Если используется INSERT ... VALUES - значит разработчику именно это и требуется. В обоих случаях какое-либо переупорядочивание SQL предложений или не нужно или недопустимо.
1CUnlimited Автор
В статье я привел пример с 1 миллионом документов в день под каждым 5 движений\проводок (в объектом смысле) , а каждое движение если посчитаете требует 4 последовательных DML операции . Т.е. 20 миллионов DML . Это реально даже в 1С. Обычное такое перепроведение за день
И если для реляционных СУБД в мануалах (точно для Oracle и Microsoft) просят использовать более крупные DML вместо 10 маленьких то и ORM следует реализовывать эти особенности СУБД. А мы видим другое, библиотеки ORM реализуют так как будто они предназначены для дохлых объемов. А ведь всего нужно при проектировании чтобы findById(ID id) позволял искать по списку ID,
saveAll(Iterable<S> entities)создавал один DML для каждой таблицы а не кучу маленьких.1С с таким подходом уже получает результаты - популярность системы возрасла( кстати за счет удобства ORM) , а попытки применения на больших объемах сразу показывают горизонты. И если сразу в библиотеке нет идеологии правильного использования это и любое другое решение ждет
murkin-kot
Раз в день этот объём, даже неоптимальным образом исполненный, будет вполне по силам любой СУБД.
Выше отвечал - если реализация batch не от группы идиотов, то никаких проблем не будет.
findById как раз по списку и ищет. saveAll вы можете руками допилить, на то он и враппер. Но повторюсь - маленькие dml наверняка агрегируются, потому что ms sql всё же довольно старая СУБД и было бы удивительно, если они такую тривиальную оптимизацию до сих пор не сделали.
Не знаю про 1С, возможно дело в кривом драйвере, отправляющем на сервер запросы без batch-a.
Идеология ORM везде одна. Вопрос только к конкретной реализации. Со стороны БД наверняка возможность есть, а что там одноэсовцы намутили со своей стороны - ну кто-ж этих умников знает? Хотя вы можете провести предложенное выше простой тестирование и сравнить. Правда не удивлюсь, если в 1С забыли вообще выставить наружу возможность включить batch.
1CUnlimited Автор
У Вас есть по другой правильный Batching который корректно отработает ситуацию? Язык и ORM не важен, И показать трассировку на любой СУБД
insert tab1
insert tab2
insert tab1
insert tab2
insert tab1
insert tab2
insert tab1
insert tab2
murkin-kot
Здесь ответ на те сложности, которые могут показаться неустранимыми, но на самом деле очень просты.
Трассировку не покажу, потому что нужно тратить приличное время. Если нет сложных (для автоматического разбора) триггеров, то трассировка и не нужна - БД сумеет оптимизировать. Примеров в сети много, правда скорее всего не всем вашим критериям они смогут удовлетворить. Но если вам это важно - ничто не мешает провести собственное расследование с важным для вас набором критериев. Принципиально смешанная последовательность, при соблюдении простых условий (присутствующих в 99,9% случаев), ничем не отличается от однородной.
ptr128
Хорошо, на пальцах. Имеем такие таблицы:
И такой пакет:
Как Вы предлагаете его переупорядочить?
murkin-kot
Да, такой вариант оптимизатор большинства БД наверняка сочтёт неоптимизируемым и БД выполнит его строго последовательно, по одной записи на каждый инсерт. Но это не значит, что все запросы имеют подобный характер. Я же писал про 99%. Да, вы можете придумать запрос из группы 0.1%, но что это доказывает?
Но, с другой стороны, даже ваш запрос некоторые оптимизаторы могут улучшить. Разработка оптимизаторов такого уровня, разумеется, не для массовых СУБД, а в массовых, возможно, оптимизатор сумеет объединить в один инсерт каждую пару для t2. Принципиально там применим вполне стандартный алгоритм, особенно если оптимизатор знает, что в пределах одной транзакции временную таблицу никто не видит. Ну а дублирующийся селект например даже mysql вообще легко оптимизирует просто запоминая результат, я уж не говорю про оракла или ms sql.
edo1h
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.
https://dev.mysql.com/doc/refman/5.7/en/query-cache.html
Default Value: OFF
https://mariadb.com/kb/en/server-system-variables/#query_cache_type
ptr128
То, что даже если Вы такой "оптимизатор" напишете, то он никому не будет нужен, так как с ним уже существующий код перестанет работать. Да и в этом случае, более правильно производить такую "оптимизацию" на клиенте, а не в СУБД. Хотя бы с той точки зрения, что СУБД часто является бутылочным горлышком в ИС и не стоит нагружать ее тем, что может быть выполнено на клиенте. И снова возвращаемся к тому, что не надо проблемы ORM сваливать на СУБД. Пусть разработчики ORM сами решают свои проблемы.
Какой ценой? Сравните время компиляции GCC с -O0 и -O3. Разница будет в 6-7 раз. А GCC при этом выполняет куда более простую оптимизацию, чем хотите Вы.
А если бы в t2 тоже был IDENTITY и для дальнейшей обработки важно, чтобы он возрастал с возрастанием номера строки и никак иначе?
Разработчик и так может объединить несколько строк в один DML. И если он так не сделал - значит на то были причины. Не надо принимать решения за разработчика.
Были такие безуспешные попытки. Сейчас, насколько я знаю, от этого повсеместно отказались. План запроса - кешируется. Результат запроса - нет. Слишком много для этого ограничений и слишком большие накладные расходы при очень малой отдаче. Это же, по сути, на все таблицы БД надо CDC включить и отдельным сервисом отслеживать (что совсем не просто), не изменились ли данные в таблицах, используемых кешированным запросом. А профита никакого, кроме поддержки кривых рук, зачем-то посылающих в БД одинаковые запросы. Если действительно надо сохранить результат запроса для дальнейшего использования - пользуйтесь материализированными представлениями, отдавая себе отчет, что в общем случае, данные в них не актуальны.
murkin-kot
Оптимизация всегда производится прозрачно для использующей её стороны. Если же оптимизатор меняет результат алгоритма, ожидаемый разработчиком (а только тогда код перестанет работать), то это означает ошибку в коде оптимизатора. То есть с нормальным оптимизатором всё будет работать.
Стоимостной анализ придумали сотни лет назад. Автоматизировали десятки лет назад.
То же самое можно сказать про SQL - профита никакого, кроме кривых рук, ленящихся писать специфические для каждой БД запросы сразу в машинных кодах.
Вообще, я не понимаю, зачем вы упорствуете и встаёте на пути прогресса? Оптимизаторы есть. Они развиваются. Если вам в их применении кажется что-то сложным, это совсем не значит, что от оптимизаторов нужно отказаться. Ну а конкретно в случае с ORM ваша собственная ссылка выше показывает, что существенной разница между универсальным множественным инсертом и специализированным для конкретной субд очень мала, соответственно, упрощение работы по созданию ORM вполне приемлемо, поскольку БД умеет оптимизировать неидеальное поведение ORM.
Хотя да, если бы все вещи на свете были бы идеальными, то я был бы только рад. Но мир так устроен, что эффективнее оптимизировать на стороне нескольких известных БД, нежели в сотнях (а то и тысячах) ORM.
Когда-нибудь ORM подтянется, но сейчас это просто неактуально. Возражения же скорее показывают нам этакое старческое брюзжание (независящее от возраста), вечное недовольство тем "какая молодёжь нынче пошла", а затем следует ожидать чего-то вроде "вот в наши-то годы...". Примите мир таким, какой он есть. Или улучшите сами. Не получается? Тогда почему вы вините других, а себе разрешаете быть неидеальным? И не оправдывайтесь другой сферой интересов, потому что в итоге все мы - население одного маленького шарика, и если кто-то на шарике отлынивает, находя очень веские оправдания, то почему то же самое не может сделать другой?
ptr128
Значит в данном случае Вы ее или не сделаете, или затраты на семантический анализ окажутся неприемлемы.
А я не вижу тут прогресса. Я вижу попытку оптимизировать 0.1% операций с БД ценой затрат на семантический анализ остальных 99.9% операций. Причем при этом любая СУБД из коробки предоставляет простой и прямой путь оптимизации этих 0.1% операций.
Если Вас так волнует оптимизация, то лучше займитесь оптимизацией при построении планов запросов. Вот тут широчайший простор для академической деятельности.
Докажите. Чем лучше увеличивать нагрузку на сервер, обслуживающий тысячи клиентов, а не наоборот, переносить нагрузку на клиентов?
Вообще то, разговорным языком это называется перекладыванием с больной головы на здоровую. Вы же, по сути, открытым текстом заявляете: "Я без объяснения причин отказываюсь пользоваться массовой вставкой данных, и хочу, чтобы сервер сам догадывался, когда мне эта массовая вставка нужна". Так если сами отказываетесь, то и делайте сами то, от чего отказываться не будете. Заводите проект на github, пишите семантический анализатор и пользуйтесь им у себя на клиенте перед отправкой пакета серверу. Лично я не только не вижу смысла в таком семантическом анализаторе, но вижу его вред в увеличении времени обработки запросов и появлению новых сложностей в разработке, отладке и поддержке.
murkin-kot
Прогресс в оптимизаторах.
Анализ (чаще всего не семантический) производится всегда. Вопрос лишь в его полноте. Полноту постепенно подтягивают, добавляя те или иные дополнительные варианты. Если вариант достаточно общий, то внезапно становится простым то, что раньше казалось сложным (затратным по времени). На а, например, граф изменений, который вы задали в своём примере, как раз и укладывается в общий подход по работе с такими графами. Вся сложность там - количественная. Нужно сказать оптимизатору, когда и где меняются переменные. Если для вас непривычно под переменными понимать значения в таблицах, то это не значит, что кто-то другой уже давно не использует именно такой подход. И подход этот достаточно дёшев по сравнению с вашими ожиданиями. Скорее всего дешевле даже одной записи на диск, а потому вполне применим.
Тысячу ORM пилят десятки тысяч разработчиков. Пяток-другой оптимизаторов в известных БД пилят хорошо если десятка два разработчиков. Десятки тысяч намного больше пары десятков.
Оптимизация распределённых вычислений на порядки сложнее, ибо вносятся вероятностные задержки и неопределённое поведение. Поэтому не надо про "всё делать на клиенте". Он не справится.
Я объяснял - это усложнение практически не нужно, поскольку БД справляется. В том небольшом количестве случаев, когда БД не справится - напишем чистый SQL или вообще хранимку.
1CUnlimited Автор
Любой управленческий , финансовый, бэкофисный учет это работа с проведением (автоматическим формированием проводок) , пересчетом итогов. Это фактически перманентный Delete\Insert Классический OLTP - то самое "небольшое количество случаев" . Эти пересчеты делаются роботами. У меня ежедневно отрабатывают 40 роботов по разным видам учета. А любой робот по нагрузке опередит тысячу пользователей. А делать такие системы проще с ORM поскольку прикладной код требует еще высокой скорости разработки. Такая жизнь
ptr128
Почему-то это принято только в 1С. Ведь и в SAP, и в AX, и в OEBS постинг через ORM не делают, хотя и используют механизм, позволяющий на своем языке описать конструкции, напрямую транслирующиеся в [WITH ...] INSERT/MERGE/DELETE/UPDATE ... FROM, избегая лишней пересылки массивов данных между сервером приложений и СУБД (это еще полбеды) и излишних блокировок, включая dead lock, что важнее. Впрочем, в особо важных случаях все равно применяют хранимые процедуры/функции или VIEW на SQL.
К слову, я всегда предпочитал на 1С для обработок использовать хранимые процедуры, о чем Вам уже писал. Не понимаю, что Вам мешает делать так же.
1CUnlimited Автор
Мне пока средств 1с хватает даже на базе 5 терабайт. Хранимые процедуры хороши если они делают универсальные вещи (типа передачи table valued ) параметров. Писать прикладные запросы и хранить их на SQL сервере - не нравится поскольку это размывает программный код и мне нужно использовать несколько репозиториев для деплоя готового приложения. А так я уверен что все в репозитории 1С . В общем чисто архитектурные причины для упрощения коммуникаций внутри отдела.
Если что нужно сделать не вписывающееся в 1С делаю это на Java как отдельный микросервис и далее интегрирую с 1С
Могу за SAP сказать так как работал с ним и внутреннюю кухню знаю он достаточно старый и сначала там работа была с таблицами и ABAP более неприятные чем 1С язык по стилю.
А потом они тоже сделали ORM, можете таже посмотреть с картинками
ABAP Blog | ABAP Object Services — Persistence Service — часть 1. (abap-blog.ru)
Ну и вообще если брать новые их разработки типа SAP HANA они тянут туда нужные технологии. Чтото напоминает :) https://sappro.sapland.ru/kb/articles/spj/modelirovanie-tranzaktsionnih-prilozhenii-v-cds.html
Причем система у них еще более монолитная чем 1С. Правда до rapid application development они так и не доросли по причине идеологического непринятия "типовых конфигураций" и как следствие code reuse это у них копипаст репозиториев между проектами и докручивание через spro
Просто когда Вы делаете прикладную разработку, Вам ORM показан как RAD tool
murkin-kot
Что-то вы путаете. Правильная классификация такая - есть OLTP, то есть небольшие количественно изменения, и есть пакетные операции, к которым все ваши проводки раз в день и относятся. OLTP из-за небольшого количества изменений вполне красиво ложится на ORM. Массовое же изменение, естественно, логичнее делать более специализированными средствами. Но поскольку вы сами говорили, что массово проводки сохраняются один раз в день, то простым смещением этого счастливого момента на ночь вы решите все проблемы вашего магазина, и при этом возможная неэффективность используемого вами ORM на больших объёмах никого не заденет.
Но это общие принципы. Конкретика будет когда вы проведёте тесты на своей конфигурации со свойственной именно вам нагрузкой.
ptr128
Вы заблуждаетесь. СУБД даже не пытается анализировать все операторы пакета, код триггеров всех используемых таблиц, использование недетерменированных функций, дерево зависимостей FOREIGN KEY и т.п.
А зачем каждой ORM свой оптимизатор уже созданного пакета для отправки СУБД? Напишите один. И если вдруг кто-то из разработчиков ORM пожелает им воспользоваться - он просто возьмет Ваш код. В чем проблемы?
Сами поняли что сказали? Говоря клиент, я подразумевал клиент СУБД, который, в подавляющем большинстве случаев, является сервером приложений. А распределенные вычисления как раз и реализуются множеством серверов приложений, каждый из которых выступает в качестве клиента СУБД.
А в нашем случае о каких-то распределенных вычислениях вообще речи нет. Есть пакет, просто строка символов. Нужно провести его глубокий семантический анализ и на его основании преобразовать эту строку в другую. Никакими распределенными вычислениями тут и не пахнет.
Я тогда вообще не понимаю о чем Вы. Потому что во всех остальных случаях, когда в пакете лишь ряд [WITH ...] INSERT/MERGE/UPDATE/DELETE ... FROM - никаких оптимизаций этого пакета совершенно не требуется.
Keeper13
Перепутали flash и flush.
edo1h
Для datacenter накопителей нет.
Табличка с тестами некоторых dc и десктопных ssd
borovinskiy
Спасибо. Отличный пример, как на десктопных SSD 30 000 IOPS превращается в 500. У серверных тоже есть просадка раза в 3, но за счет конденсатора они жульничают с flush.
@1CUnlimited стоит посмотреть табличку.
edo1h
Нет, на большинстве никакой разницы не наблюдается.
Если вы судите по той табличке, там просто нет однопоточной асинхронной записи
1CUnlimited Автор
В таблице не описаны условия тестирования ( размер блока для journal iops , linear write) . Это как минимум принципиально важно, поскольку если вы пишите 32к это может быть в 8 раз медленней чем писать 4К , но это не говорит о том что диск перегружен . Из Ваших постов выше я вижу непонимание приведенных мной цифр, разъясняю выше
edo1h
промотайте вниз, указаны конкретные параметры, с которыми запускается
fio