«Мы стояли на плоскости» (БГ)

«Кто на ком стоял?» (МБ)

Аннотация

Предлагаемый текст представляет опыт ознакомления автора с тестом SSB в свете обычных прикладных вопросов BI‑проекта умеренного размаха. Автор пытается бросить пытливый взгляд на спектр перспективных возможностей, обеспечиваемых доступностью и масштабируемостью теста SSB.

Ключевые слова

SSB, Postgres, ClickHouse, Sizing, MDX, Flat, Star, BI, DWH, КХД

Введение

В [1] описан тест Star Schema Benchmark (SSB), предназначенный для оценки производительности СУБД через моделирование ее работы в режиме платформы аналитического хранилища (КХД, DWH). Краеугольным камнем для КХД и вообще для BI‑решений является быстродействие. Скорость ответа аналитической системы должна приближаться к «скорости мысли», ибо «ложка хороша к обеду». Есть разные стратегии в обхождении с этой проблематикой, например, не задавать вопросы, на которые не будет быстрого ответа, фокусируясь на узких ракурсах, или предвидеть вопросы, которые будут заданы, и формировать ответы заранее и так далее. Другой подход — организовывать модели данных так, чтобы они давали быстрый отклик или использовать новаторские архитектуры управления данным так, чтобы специально хлопотать об оптимальности модели было бы не так уж обязательно. Таково, как в последнем случае, преимущество аналитических баз данных (OLAP), в отличие от транзакционных (OLTP).

Разработчики аналитических СУБД публикуют результаты SSB для акцентирования преимуществ своих решений: эти результаты призваны наглядно показать, что архитектура БД настолько хороша, что «плоская» (flat) модель в ней ведет себя лучше, а как правило даже гораздо лучше, чем прославленная «звездная» (star), лежащая в основе технологий ROLAP. Тесты, очевидно, не охватывают всего разнообразия потребностей, но, во‑первых, служат красноречивым критерием, а во‑вторых, как синица в руке, являют собой нечто лучшее, чем ничего. Таковы, например, результаты SSB, представленные в [2] или [3]. В обоих источниках даны скрипты для развертывания модели данных и программа‑генератор массива для этой модели с любым наперед заданным числом записей.

Преимущества аналитических баз имеют и оборотную сторону, заставляющую «оплачивать» производительность за счет снижения удобства использования и переноса фокуса с содержательной стороны задачи на обеспечивающую. С другой стороны, у обычных OLTP‑баз наработан богатый инструментарий для решения частных задач оптимизации, что позволяет «не терять лица» при реализации аналитических приложений даже в конкуренции с чудесами от columnar, in‑memory и пр. И да, авторы SSB в [1] справедливо отмечают, что у отдельных представителей систем управления БД могут иметься вполне эффективные инструменты для оптимизации запросов.

Не претендуя на точность определений, непререкаемость мнений, однозначность результатов, рассмотрим ниже применимость SSB для оценки «быстроты и натиска» в самом простом их понимании, когда у нас имеются бытовая «персоналка» и пара open‑source инструментов — PostgreSQL (PG) и ClickHouse (CH).

Обсуждаемые ниже результаты получены с помощью скриптов и программ, заимствованных у [3].

Параметры ПК

Тест SSB проведен на обычном ПК:

SSD 256, Postgres (14.3), 64 Bit Windows;
HDD 256, ClickHouse (26.4.1.134), WSL, Ubuntu 24.04;
CPU 2 ядра x 2 потока (G4400);
RAM 16 GB
Windows 10.

Параметры более, чем скромные, но как говаривал один классик, «других писателей у меня для вас нет». А ещё оценка, полученная в «худшем» случае, является точкой отсчёта для поиска улучшений.

Параметры SSB

Генератор данных для SSB из [3] может выдавать любой наперёд заданный объем, но с учётом ограниченности дискового пространства имеющегося ПК выбран близкий к минимальному объем фактов, равный 60*10^6 (Рисунок 1), при этом:

Потребление дискового пространства для CH составило ~ 10Gb;
Примерное потребление дискового пространства для PG составило ~ 30Gb (без индексов).

Более крупный массив, размером 600*10^6 как в [2] или в [3], был бы интереснее, но под него понадобилось бы больше ресурсов, притом, что почти наверняка качественная картина вряд ли бы стала рельефнее.

Рисунок 1 Обзорный дашборд с метриками размерности таблицы фактов и справочников – для наглядности показатели выручки на графике модифицированы случайным образом в интервале +/-δ — Рисунок 1 Обзорный дашборд с метриками размерности таблицы фактов и справочников — для наглядности показатели выручки на графике модифицированы случайным образом в интервале +/‑δ

Ошибки генерации, способы их выявления и исправления

Массив, генерируемый для SSB, и SSB‑запросы из [3] содержали пару некритических для метрик скорости ошибок, проявившихся в некоторых расхождениях между результатами запросов к модели flat и к модели star. Так, например, найдены и исправлены неточности:

D_WEEKNUMINYEAR содержал неправильный номер недели для даты 1994–02-11 разница была выявлена через условия EXTRACT(WEEK FROM LO_ORDERDATE)<> D_WEEKNUMINYEAR;
В тесте Q2_3 для flat и star были указаны разные ограничения для P_BRAND (в настоящее время неточность уже исправлена в [3]).

Следует быть готовым к тому, что программы, данные и тесты могут содержать опечатки, не влияющие, тем не менее, на качественную картину результатов.

Результаты сравнительных тестов — для PG «как есть»

На (Рисунок 2) ниже представлен рейтинг среднего времени выполнения запросов SSB для flat и star в CH и PG. При этом, схемы данных в PG использовались «как есть», без применения оптимизирующих приёмов. На (Рисунок 2) видно, что CH безусловный рекордсмен, притом, очевидно, лидирует flat.

Рисунок 2 Среднее время выполнения запросов SSB – до индексации в PG — Рисунок 2 Среднее время выполнения запросов SSB — до индексации в PG

На (Рисунок 3) представлен другой ракурс той же результативности, где PG без оптимизации «равномерно» уступает CH в интервале от 10 до 100 раз:

Рисунок 3 Сравнительный порядок времени выполнения запросов SSB – до индексации в PG — Рисунок 3 Сравнительный порядок времени выполнения запросов SSB — до индексации в PG

Результаты сравнительных тестов после оптимизации в PG

Чтобы проявить справедливость в отношении PG, была проведена вторая серия тестов SSB, в которой для каждого отдельного запроса опытным, конечно не исчерпывающим все возможности и не гарантирующим оптимальности, путем для star и flat был подобран «наилучший сценарий». Он включал сбор статистики, активацию /деактивацию опций, индексацию и пр. Этот прием, конечно, идет несколько вразрез с потребностями OLAP, отступая в сторону фиксированной аналитики из OLTP. Тем не менее, это один из вполне продуктивных подходов к акселерации в ROLAP‑архитектурах, наряду с другими, такими как использование агрегатных таблиц, материализованных представлений и пр.

На (Рисунок 4)‑(Рисунок 5) показан прирост производительности схемы star в PG после оптимизации сценария выполнения. Но еще лучше, чем star, «подтянулась» схема flat:

Рисунок 4 Среднее время выполнения запросов SSB – после подбора сценария в PG — Рисунок 4 Среднее время выполнения запросов SSB — после подбора сценария в PG

Рисунок 5 Сравнительный порядок времени выполнения запросов SSB – после подбора сценария в PG — Рисунок 5 Сравнительный порядок времени выполнения запросов SSB — после подбора сценария в PG

Обсуждение

В таблице ниже показаны нормированные графики размера результата n и времени выполнения запроса t(n) для 13 тестов из SSB, где тесты указаны не в порядке их нумерации, а по возрастанию n. Также в подписях показано значение КОРРЕЛ() из EXCEL, которое характеризует тесноту линейной связи двух рядов. Красная кривая на (Рисунок 6-Рисунок 11) показывает рост n и, как видно, почти всюду выступает в роли «тренда» для графика t(n). Также видно из подписей, что CH показывает высокую тесноту линейной связи между t(n) и n (Рисунок 6-Рисунок 7), что могло бы считаться полезным результатом, приближающимся, особенно для star, к O(n). Повторимся, что в CH, тем не менее, время t(n) для flat почти всюду лучше, чем для star (Рисунок 3).

Почти такой же, как для flat в CH, или даже лучше, корреляции удалось добиться (подбором сценария) для flat в PG (Рисунок 11). «Подтянулась» по корреляции и star в PG (Рисунок 10) при улучшившихся временах t(n) (Рисунок 5). Как и раньше, star в PG работает быстрее, чем flat, хотя разница уже не столь контрастна.

Забавно, что хотя PG безо всякого подбора сценария показывает «плохие» времена t(n), но, вместе с тем, имеет место отсутствие заметного прироста t(n) при увеличении n: графики на (Рисунок 3), (Рисунок 8-Рисунок 9) выглядят горизонтальными, а корреляция почти отсутствует, см. значения в подписях к (Рисунок 8-Рисунок 9). Это навевает предположение, что PG для тестов SSB без «оптимизации» обеспечивает время, похожее на константу, что, скорее всего, не так «во всемирном» масштабе, но близко к тому по крайней мере для данного экземпляра SSB.

После подбора сценариев PG даёт лучшие, чем до «оптимизации», времена t(n), но уже начинает, как и для CH, проявляться рост n и корреляция уже не столь «безразлична». В связи с этим представляется уместным для некоторых случаев в PG использовать «смешанную стратегию». Предположим n растёт неограниченно, тогда в зависимости от оценки n стоит уже выбирать, использовать оптимизированный сценарий t«(n) или нет t»«(n). В этом случае мы получим минимум из двух времен: t(n) = min(t»(n), t«„(n)).“»

Мы здесь говорим всего лишь о «предположениях», опирающихся на частный экземпляр SSB и на выборочный набор приёмов, оптимизирующих в PG план выполнения запросов. Полученные результаты нуждаются в проверке и в критическом осмыслении, начиная, хотя бы, с апробации SSB более представительного масштаба.

Применение к выбору программной платформы

Индексация, включение/выключение опций и прочие методы ускорения применимы для фиксированных запросов и вполне подходят для решений, где нет потребности в сводных таблицах или в элементах типа self‑service‑BI. Таковы, например, отчеты и дашборды заданной структуры. И наоборот, для обеспечения гибкости средств аналитики, когда конечный пользователь сам выбирает ракурс и способы группировки, частная оптимизация запросов не решает проблему быстроты в общем случае, — здесь необходимо решение на специализированной аналитической платформе, работающей быстро без ухищрений.

Подбор сценария — подход далеко не универсальный и не автоматизируемый: даже для простых запросов из SSB план «подгоняется» вручную, индивидуально для каждого теста. Стоит отметить также, что индексация, хоть иногда и ускоряет запросы, но формирование индекса — процесс неспешный. Индексы тормозят актуализацию данных и порождают ощутимый расход места.

Применение к подбору аппаратных характеристик

SSB‑тест, как мы уже отметили, несет в себе как сами тесты, так и генератор данных заданного объема. На практике же часто возникает вопрос подбора подходящей под заданный объем данных инфраструктуры, но самого массива требуемого размера в наличии нет. Так что приходится или заниматься мысленными экспериментами на основе проектного опыта, или доверяться мнению «знатоков», или использовать специализированный платформно‑ориентированный софт типа SAP Quick Sizer Tool. В то же время доступность массивов любого наперед заданного размера и набора тестов для оценки быстродействия позволяют вполне конструктивно помечтать:

о применении SSB для проверки адекватности имеющейся инфраструктуры размеру перспективной задачи;
о наработке «в лабораторных условиях» статистики результативности SSB‑тестов для разных размеров и платформ и «обучить» с её помощью какой‑нибудь гипотетический Self‑Service‑Sizer — «решающее правило», прогнозирующее параметры инфраструктуры, подходящие под размер перспективной задачи;
об использовании идей open‑source генератора данных из SSB для наполнения моделей, отличных от хрестоматийной из [1].

К вопросу о многомерном анализе

Венец BI‑решения — гибкая аналитика (ad‑hoc). Тесты SSB вместе с доступностью массива любого размера несут в себе черты ad‑hoc — разнообразные срезы, калькулируемые показатели, фильтрация, сортировка, группировка. Зародилось любопытство: можно ли повторить SSB‑тесты средствами MDX? Популярный инструмент Pentaho Schema Workbench (Mondrian) из BI‑платформы Pentaho, отмеченной Gartner® Magic Quadrant™, позволяет «набросить» ROLAP‑схему для любой из моделей — как star, так и flat — и для любой БД, имеющей JDBC‑коннектор. Притом на такой схеме без признаков «с высокой кардинальностью» отлично работают многочисленные OLAP‑плагины из арсенала Pentaho. А что у нас получится в случае с высокой кардинальностью? Вопрос оказался из ряда тех, которые не следует задавать. Итак, в Mondrian было реализовано подобие теста Q1_1 над моделью flat в CH, на чём опыты и застопорились: потребность в расчётном показателе наткнулась, увы, на ограничения Mondrian OLAP‑server. Так тест Q1_1 вычисляет SUM(a*b) по выбранным позициям заказов (Рисунок 12). Всего в таблице порядка ~10^6 строк. Позиция заказа — это составной признак LO_ORDERKEY+LO_LINENUMBER с высокой кардинальностью, где |{LO_ORDERKEY,LO_LINENUMBER}|~10^6. В то же время корректное вычисление SUM(a*b) должно быть выполнено в правильном порядке, то есть сначала вычисляем a*b для каждой отдельной позиции из выборки в Q1_1, а затем суммируем произведения. Обработчик MDX‑запросов в исполнении Mondrian декомпозирует MDX‑запрос в последовательность обыкновенных SQL‑запросов, которые вместо SUM(a*b) вычисляют SUM(a) и SUM(b) по‑отдельности. Затем Mondrian перемножает результаты. Увы, автору не удалось заставить Mondrian на исходной модели начать создавать выражения вида именно SUM(a*b). Без явного обхода детальных позиций заказов мы с такой арифметикой получим не SUM(a*b), а SUM(a)*SUM(b), что, конечно, не одно и то же если выборка не ограничивается единственной позицией. Таким образом, чтобы‑таки получить правильный ответ на Q1_1 средствами Mondrian необходимо заставить Mondrian вычислить значение a*b для всех подробных позиций заказов, которых ~10^6. Но тогда Mondrian сформирует порядка ~10^6 обычных SQL‑запросов. Для любой отдельной позиции, например, LO_ORDERKEY=3 AND LO_LINENUMBER=4, такой запрос выполнится почти мгновенно. Но какова бы не была производительность БД, эти ~10^6 запросов будут суммарно считаться бесконечно долго, не говоря уже о том, что Mondrian довольно быстро и не дожидаясь финиша упадет с ошибкой переполнения кучи. Проблема расчёта SUM(a*b) устраняется «конструктивно»: если в таблицу фактов добавить новый, предварительно рассчитанный, показатель a*b и новый признак, нумерующий позиции заказов сквозным образом. Итог для Q1_1 теперь будет получен за долю секунды:

Рисунок 12 Оригинал запроса в CH и его результат

Рисунок 13 Схема Mondrian рассчитывает итог из Q1_1

Таким образом, правдивый ответ, на заданный в начале этого раздела вопрос, следующий: средствами Mondrian без модификации модели данных, — почти никак. Калькулируемые меры MDX применимы в исполнении Mondrian для запросов к небольшим срезам. Для множеств с «высокой кардинальностью» расчеты следует выполнять заранее и, вероятно, потребуется расширение модели признаков.

Заключение

Резюмирую изложенные выше личные субъективно‑идеалистические, безо всяких претензий, впечатления от знакомства с SSB:

SSB убедительно показывает преимущества аналитических баз;
у OLTP‑баз, как видно на примерах для PG выше, определенно сохраняется потенциал для оптимизации частных запросов;
SSB — как тесты, так и генератор, представляются мощным средством для проверки адекватности текущей инфраструктуры, а также средством подбора подходящих под размер задачи перспективных аппаратных характеристик;
SSB — это также готовый тест для ROLAP‑движков и OLAP‑клиентов и, как автору стало видно на примере Mondrian, выполнимость MDX‑запросов, повторяющих тесты из SSB без модификации модели данных, не очевидна. Таким образом, на данном этапе развития производительных сил разработчик BI‑решений пока не освобождается от мучительного творческого поиска способов ускорения аналитики;
генерация SSB может содержать немногочисленные некритические неточности.

Благодарности

Автор признателен Виктору Ш. за моральную поддержку в материализации этой заметки и полезные замечания. Так же многие результаты по оптимизации в PG оказались бы недостижимыми без деятельного творческого участия и энтузиазма Сергея Д. Просвещенный совет Георгия М. проложил путь в мир прогрессивных IT‑образных медиа. Спасибо вам!

Ссылки

[1]	Pat O'Neil, Betty O'Neil, Xuedong Chen, Star Schema Benchmark, Boston: UMass, 2009.
[2]	Apache Doris 1.2 star‑schema‑benchmark performance test report, 2022.
[3]	Star Schema Benchmark, 2009.

SSB — «мы стояли на „плоскости“»