Недавно мы протестировали подход, именуемый нами QDM, при работе с большими объемами данных — сотни гигабайт. В рамках задачи мы обрабатывали по 12-24 млн записей и сравнивали производительность квинтетного решения с аналогичным функционалом в обычных таблицах.
Мы не сделали каких-то новых открытий, но подтвердили те гипотезы, что озвучивали ранее: насколько всё таки универсальный конструктор в руках условного «чайника» проигрывает профессионально настроенной базе данных.
Также мы теперь знаем, что делать в подобной ситуации — решение достаточно простое и надежное, и имеем опыт организации компромиссного решения для сколько угодно больших данных.
В ходе разработки системы для сверки отчетности нам понадобилось загрузить данные одной из отчетных форм, в которой на каждую отчетную дату содержится до 24 млн записей. Данных там должно быть на 7 лет ежедневных расчетов. Объемы, прямо скажем, не для универсального конструктора, а для узкоспециализированной системы, но мы уже ввязались в эту затею и надо было искать решение.
Пользователи работают с этим большим массивом данных только в пределах одной отчетной даты, поэтому в эталонной системе всё это хранится в таблице, партиционированной по этой дате. Индексы по остальным 26 полям данных для этой формы не используются.
Первое, что мы сделали, разумеется — создали нужную структуру данных в конструкторе и загрузили туда несколько дат. Загрузка одной самой небольшой даты занимает около 14 часов, что уже неприемлемо долго: 12.5 млн записей по 27 атрибутов, положенные в полмиллиарда записей из 5 полей с построением трёх индексов, два из которых — составные.
Общий объем этих данных на диске чуть больше 18 ГБ.
Стоит заметить две особенности этой формы:
- Она почти не поддается нормализации, в отличие, например, от Формы 110, рассмотренной в предыдущей публикации
- В ней не используются индексы по атрибутам записей — пользователю выгоднее подождать минуту, чем тратиться на индексы
Это, можно сказать, самый радикальный случай, какой можно выбрать для сравнения. В большинстве случаев разница в объемах данных QDM и обычной базы не такая драматичная, а то и совсем мала.
Для сравнения, те же данные, загруженные в обычную таблицу реляционной базы данных, занимают 2.3 ГБ (в 8 раз меньше) вместе с индексом по дате, а их загрузка длится менее получаса (в 28 раз быстрее). В обоих случаях данные вставлялись напрямую из файла порциями по 100 тысяч записей, без отключения индексов.
Памятуя, что для таких объемов данных использовать конструктор нецелесообразно, мы, тем не менее, сделали тесты производительности: для разных случаев сравнили массовую обработку записей конструктором и нашей неиндексированной таблицей. Нам нужно было определить границу объема данных, для хранения которых мы будем отныне использовать обычную таблицу, а не наш конструктор.
Как мы ожидали, работа с небольшими наборами данных, например, по отдельному счету или клиенту, в конструкторе выглядит достаточно комфортно (время отклика в пределах секунды), в отличие от таблицы без индексов, где отклика приходится ждать минутами. В то же время основная задача приложения — массовая выборка и агрегация данных в различных разрезах — может занимать в конструкторе в разы больше времени.
Ниже приведены сводные результаты выборок, которые мы делали для ступенчато возрастающего объема агрегируемых данных:
| К-во записей | Время выборки, с | |
|---|---|---|
| Конструктор | Таблица без индексов | |
| 1 | 0.16 | 56 |
| 5 | 0.23 | 55 |
| 50 | 1.86 | 53 |
| 600 | 2.35 | 56 |
| 5000 | 14.7 | 56 |
| 12000 | 125 | 56 |
| 100000 | 254 | 57 |
| 650000 | 2663 | 57 |
| 1000000 | 2314 | 57 |
| 5000000 | 9675 | 69 |
| 12500000 | 764 | 89 |
Там, где это было возможно, мы делали несколько замеров, предварительно выбрав статистику и отбирая количество записей по маске номера счета.
Из таблицы и графика ниже видно, что агрегация полного набора данных внутри дня занимает заметно меньшее время, чем выборка свыше 5% данных с использованием индекса. Именно поэтому оптимизаторы запросов РСУБД игнорируют индекс в подобной ситуации, в то время как движок нашего сервиса на данных момент лишён такой возможности.
Графическое отображение тех же результатов с использованием логарифмической шкалы, чтобы сравнить числа разных порядков:
Быстродействие конструктора при обработке полного набора данных почти на порядок ниже, чем у обычной таблицы, что вполне нормально для конструктора — важно избежать лавинообразной деградации производительности, и эта цель достигнута.
Исследование показало, что количество записей в базе практически не влияет на скорость построения страниц, навигации и небольших выборок в квинтетной модели данных. При количестве обрабатываемых данных до 10 000 записей (а это максимальная выборка связанных данных для экземпляра любой бизнес-сущности в информационной системе) можно комфортно работать с базой в сотни гигабайт и больше.
Далее мы научили наш табличный плагин (описанный здесь) вызывать коннектор к произвольной базе данных, чтобы он прозрачно работал как с квинтетной моделью данных, так и с традиционными таблицами.
С точки зрения пользователя, ему не важно, с каким источником данных он работает, пока он может делать важную для него работу в привычном интерфейсе, получая свои отчеты:
Теперь мы вынесем остальные подобные огромные таблицы, которых в нашей базе всего две дюжины из сотен, в отдельное хранилище, чтобы комфортно работать с ними.
Итак, мы можем использовать конструктор для небольших и средних таблиц, требующих интенсивного поиска и агрегацию по произвольным атрибутам, а большие неиндексированные объекты хранить в плоских традиционных таблицах, вызывать из стороннего хранилища или специализированных баз данных (Hadoop и прочих NoSQL).
Наилучшим образом конструктор подходит для CRM-систем и подобных продуктов, где пользователь работает с отдельно взятым клиентом, счетом или иной сущностью, а аналитические функции вынесены в отдельное специализированное хранилище.
michael_vostrikov
Это неправда. Скорее всего, вы или тот кто это делал просто неправильно что-то настроили. Возможно даже намеренно, чтобы ваша система выглядела лучше.
В такой форме это вообще намеренная ложь и манипуляция, так как создает у вашего потенциального заказчика, который в технических тонкостях не разбирается, мнение, что на результат влияет только то, что это "традиционная таблица". 1 минута для выборки 1 записи (и гораздо меньшее время в "конструкторе") позволяют предположить, что для решения этой проблемы нужно было просто создать нужный индекс, это несколько минут на изучение сторонним специалистом и один запрос в БД.
UltimaSol Автор
Если вы подозреваете обман в этой или любой другой моей заметке, то свяжитесь со мной лично, разберем всё по скайпу (я и ранее так предлагал), и если это таки обман, то тогда расскажете об этом здесь всем.
Я намеренно взял радикальный случай, чтобы показать достоинства и недостатки решений для разных режимов: выигрываем в одном, проигрываем в другом. Никакой магии, только возможность выбора из больше 1 варианта.
Вот смотрите, у нас есть больше миллиарда записей в таблице с индексом по дате и мы делаем выборку, затрагивающую 12.5 млн записей. По индексу эти записи будут найдены и просмотрены все, одна за одной, даже если под условие выборки подходит всего одна запись. Это долго, но дешево в плане пространства.
В конструкторе то же самое — мы находим те 12.5 млн записей, а среди них ищем одну опять по индексу — получается очень быстро (но дорого в плане пространства!). Когда же под выборку попадают десятки тысяч записей, то конструктор будет метаться среди них, собирая данные с диска и тратя драгоценное время.
Это всё и показано в таблице.
Мы использовали номер счета, а там еще есть другие атрибуты — норма резервирования, класс качества, имя счета, несколько дат и другие параметры — десятки их, и по всем индекс не построить, иначе всё это хозяйство как раз сравнится с конструктором по размеру и скорости.
При 26 атрибутах каждый индекс добавит некие %% к требуемому пространству (вероятно, 4% в среднем), в итоге, как это часто можно наблюдать, индексы могут занимать больше места, чем данные. В рассматриваемом случае это неприемлемо, равно как и использование конструктора.
Так, за несколько минут, не получится в Production системе.
michael_vostrikov
Некорректность присутствует в высказываниях в тексте вашей статьи. Никакие другие факты эту некорректность не уберут. Если вы пишете техническую статью на техническом ресурсе, вы эти факты должны привести в статье, пусть даже и в измененном виде.
Я про это и говорю. Это не недостаток решения "традиционная таблица", это неправильная настройка системы. Которое решается правильной настройкой за несколько минут, а не переходом на вашу систему с полным переносом данных в другой формат хранения.
Если выборка затрагивает 12.5 млн записей, значит под условие подходят 12.5 млн записей. Если под условие подходит одна запись, значит выборка затрагивает одну запись. Вы возможно что-то другое хотели сказать, тогда опишите это подробнее.
По индексу будут найдены те записи, которые подходят под условие фильтра. Это именно то, для чего индексы и делают.
Нет никакой принципиальной разницы между индексом в вашей системе и индексом в "традиционной" таблице. Потому что ваша система работает поверх движка БД, и пользуется теми же индексами.
В таблице показаны какие-то цифры для неизвестной структуры бизнес-сущностей, неизвестными запросами к хранилищу данных, и неизвестными настройками. Из того, что известно, можно сделать вывод, который я написал выше — система с "традиционными" таблицами была настроена некорректно, и решается это добавлением нужного индекса. Что и было сделано при переносе данных в вашу систему, так как индекс там автоматически строится на все данные.
Ну так это ровно то, что и происходит в вашем конструкторе. Индекс строится на все значения, причем даже на те, которые логически относятся к другой сущности. Так что нет, и по скорости и скорее всего по размеру обычные таблицы будут лучше, так как такая система более специализирована под нужные случаи. Тем более что у вас в таблице все значения строковые, а целочисленные данные имеют другой формат хранения, который занимает меньше места, и по ним можно строить более оптимизированный индекс, с которым соответственно система будет работать быстрее.
Во-первых, нет никакой необходимости строить индексы по всем полям. Строятся по тем, которые чаще используются в запросах. Во-вторых, сам по себе факт что индексы занимают больше места, чем данные, ничего не значит. Это не хорошо и не плохо. Если нужна скорость — строятся индексы, если нужна экономия места — уменьшаются требования к скорости. Обычно дешевле купить дополнительный жесткий диск. Тем более что 2.3 Гб это очень мало, нормальный SSD-диск на 120 Гб стоит 3000-4000 рублей.
Получится. Смотрим, что в запросе используется номер счета, выполняем запрос на добавление индекса по этому полю. Всё. Сам запрос да, может занимать больше времени, в зависимости от количества данных. Но меньше, чем переносить все данные в вашу систему.
UltimaSol Автор
Вы это серьезно — неправильная настройка системы?
Статья не ищет ответа на вопрос «как правильно настроить систему» и не агитирует переходить на новую систему, а исследует поведение различных решений.
Ок, попробую описать. Условию удовлетворяет одна запись из 12.5 млн, выбранных индексом из миллиарда: индекс указал СУБД, что нужные нам записи следует искать вот в этом диапазоне адресов, где 12.5 млн записей. Индекса по этим 12.5 млн у нас нет, поэтому СУБД просмотрит их все, обработав (просуммировав и посчитав) только те, что удовлетворяют условию. В итоге будет посчитана всего одна запись из просмотренных 12.5 млн.
У конструктора есть индекс и по этим 12.5 млн, поэтому он сразу найдет нужные записи и обработает их — также всего одну штуку. Это выйдет заметно быстрее.
Вы удивитесь, но они утверждают, что нужен поиск по всем свойствам задолженностей. Ну, то есть, они заранее не могут сказать какие важнее других — по всем и всё тут. При этом они зарабатывают деньги компании, а не IT-специалист, поэтому функционально придется сделать так, как они скажут.
Не получится. Вот типичный случай: сотрудники банка работают с картотекой (долги клиентов), при этом несколько отделов рассматривают разные атрибуты на разных стадиях и категориях задолженностей. Всего там около 40 атрибутов. Вы предлагаете им всем создавать индексы по нужным им атрибутам?
В Production, повторюсь, системе, где любое изменение проходит процесс оценки, приоритизации, разработки, тестирования, затем документируется, планируется в релиз (между регулярными мораториями на изменения) и только после этого всего устанавливается.
michael_vostrikov
Еще раз, вы исследуете не поведение решения "традиционная таблица", а поведение решения "традиционная таблица без индексов". А сравниваете это с вашим решением с индексами. И в утверждениях называете это "традиционная таблица", не упоминая, что при других настройках эта же традиционная таблица будет работать быстрее вашего решения. Это манипуляция.
Значит надо его добавить, о чем я и сказал. Будет составной индекс на 2 поля.
Вы удивитесь, но поиск по всем свойствам возможен в моем решении. Просто если не построить нужный индекс, то поиск будет работать дольше.
Ну у вас же так и делается. Почему в вашей системе индекс по всем значениям всех свойств это не проблема, а в другой системе проблема? Тем более что в статье вы пишете, что оставили обычные таблицы, а значит для поиска по ним нужно так же создавать индексы по нужным атрибутам, либо так же долго ожидать результатов при работе с системой.
А для внедрения вашего решения все это не нужно? А при разработке этой системы разве нельзя создать индексы сразу по всем полям, как это делается в вашем решении? Вы приводите это как недостаток, не упоминая, что в вашем варианте все то же самое. Это тоже манипуляция.
UltimaSol Автор
Это паранойя.
Ранее я сравнивал таблицы с частичными индексами: здесь,
а также была статья про полностью проиндексированные атрибуты, где это целесообразно,
и теперь в этой заметке я показал, как быть, если данных совсем много, так много, что бизнес их не индексирует вовсе, предпочитая подождать 1-2 минуты при необходимости.
Я специально неоднократно упоминаю, что конструктор в этом случае применять не следует, но делаю замеры и фиксирую их.
michael_vostrikov
Эм, причем здесь какие-то другие статьи, если я говорю про утверждения в данной статье?
Вы сравнивали, и вам там в комментариях указали на недостатки вашего сравнения.
Если данных много, то это основная причина, чтобы построить индекс. Как раз когда данных мало, то индексы строить необязательно.
Да, я об этом и говорю. Ваша методика измерений неправильная. Вы случайно или намеренно подменяете понятия и делаете неправильные выводы. "Одна минута на выборку одной записи на указанном объеме данных" это не результат замеров системы "традиционная таблица" или "обычная БД", это результат конкретно той системы без нужных индексов. "Традиционная таблица" может работать гораздо быстрее на всех кейсах использования.
UltimaSol Автор
Нет предела совершенству, я учту комментарии и в следующий раз, возможно, получится лучше. Но всегда будет критика. Я сюда за ней пришел, кстати.
В чём же неправильны выводы?
Вот этот, например:
или этот:
Я не тягаюсь с обычной таблицей, а использую её как ориентир, такую, какая она есть сейчас у пользователей, пусть и без индексов, мне это не важно. Я не говорю, что что-либо лучше или хуже. Мне важно, будет ли конструктор работать вообще, если же не будет или будет, но медленно, то что с этим делать.
michael_vostrikov
Я говорил про другие выводы, про те, которые процитировал.
Касательно этих, как я уже сказал, в статье слишком мало известной информации — структура сущностей, что и как считали, и т.д. Например, отчет на 3 строки с агрегацией по миллиону строк это тоже страница. А фраза "а это максимальная выборка связанных данных для экземпляра любой бизнес-сущности в информационной системе" это недоказанное утверждение.
Вот именно, вы подразумеваете конкретную систему с конкретными пользователями, а в статье используете общие названия типа "обычная БД", из чего складывается впечатление, что недостатки обусловлены тем, что это обычная БД. Повторюсь, можно сделать другое решение, которое тоже будет называться "обычная БД", но которое будет работать быстрее вашего и быстрее существующей системы.
Я высказываю все эти возражения потому что вы необоснованно критикуете те решения, которые обычно делаю я, да и многие другие программисты, выставляете их в невыгодном свете.
UltimaSol Автор
Вон оно как. Казалось бы, кого волнуют изыскания какого-то там ещё одного строителя конструктора… Прямо бросил тень на весь мир разработчиков СУБД.
Что вы воспринимаете как критику? Я уберу или перефразирую.
Например, сделаю так:
michael_vostrikov
Замените все выражения "традиционная таблица", "обычная БД" и аналогичные им на "существующая система". Отдельным абзацем укажите, что в существующей системе нет индексов, а в вашей есть. Также можно упомянуть, что вариант добавить индексы в существующую систему не рассматривался. Это будет более соответствовать положению дел и не будет ни у кого создавать неправильных представлений о ситуации и используемых технологиях.
UltimaSol Автор
Постарался исправить перечисленные моменты