«Мы стояли на плоскости» (БГ)
«Кто на ком стоял?» (МБ)
Аннотация
В статье представлен практический опыт использования бенчмарка Star Schema Benchmark (SSB) для решения прикладных задач в рамках BI-проекта среднего масштаба. Автор исследует потенциал и гибкость SSB как инструмента оценки производительности аналитических систем.
Ключевые слова
SSB, Postgres, ClickHouse, Sizing, MDX, Flat, Star, BI, DWH, Хранилища данных
Введение
В источнике [1] детально разобран Star Schema Benchmark (SSB) — стандарт для оценки эффективности СУБД в аналитических нагрузках (DWH/КХД). В мире бизнес-аналитики быстродействие является критическим параметром: отклик системы должен быть практически мгновенным, чтобы соответствовать «скорости мысли». Существует несколько стратегий достижения этой цели: ограничение спектра задаваемых вопросов, предиктивная подготовка отчетов или использование специализированных архитектур. Преимущество аналитических (OLAP) баз данных перед транзакционными (OLTP) как раз заключается в их способности эффективно обрабатывать массивы данных без необходимости ручной подстройки каждой модели.
Разработчики аналитических систем часто опираются на показатели SSB, чтобы продемонстрировать превосходство «плоской» (flat) модели данных над классической «звездой» (star), используемой в ROLAP. Хотя бенчмарк не охватывает все грани реальных задач, он служит удобным и понятным индикатором производительности [2], [3]. Доступные генераторы данных позволяют гибко масштабировать нагрузку в зависимости от целей тестирования.
Однако у аналитических решений есть обратная сторона — необходимость жертвовать гибкостью ради скорости и смещение фокуса разработки на инфраструктурные задачи. В свою очередь, зрелые OLTP-решения обладают богатым арсеналом оптимизационных инструментов, позволяющих успешно конкурировать с «колоночными» инновациями. Авторы оригинального SSB также подчеркивают наличие развитых механизмов оптимизации запросов в современных СУБД.
В настоящем материале, не претендуя на академическую строгость, мы протестируем производительность PostgreSQL (PG) и ClickHouse (CH) на домашнем ПК, используя методики и скрипты, предложенные в [3].
Параметры тестовой среды
Тестирование проводилось на следующей конфигурации:
-
PostgreSQL (14.3) на SSD, 64-bit Windows;
-
ClickHouse (26.4.1.134) в WSL (Ubuntu 24.04) на HDD;
-
Процессор: Intel G4400 (2 ядра, 2 потока);
-
Оперативная память: 16 ГБ;
-
ОС: Windows 10.
Несмотря на скромные ресурсы, полученные данные служат репрезентативной точкой отсчета для дальнейшей оптимизации.
Параметры SSB
Учитывая ограничения дисковой подсистемы, был выбран объем фактов в 60 миллионов записей (Рисунок 1). Это позволило уложиться в доступное пространство: ~10 ГБ для ClickHouse и ~30 ГБ для PostgreSQL (без учета индексов). Выбор большего массива (например, 600 млн строк) потребовал бы значительных мощностей, при этом общая картина производительности вряд ли претерпела бы качественные изменения.
Исправление ошибок генерации
В ходе работы были выявлены мелкие неточности в тестовом коде, влиявшие на результаты: исправлен некорректный расчет номера недели (D_WEEKNUMINYEAR) и устранены различия в фильтрах для P_BRAND в запросах Q2_3. Данный опыт подтверждает необходимость внимательной проверки предоставленных скриптов и данных.
Сравнительные результаты: PostgreSQL «из коробки»
На Рисунке 2 представлен рейтинг среднего времени выполнения запросов. ClickHouse демонстрирует явное лидерство, особенно в конфигурации с плоской таблицей (flat). В свою очередь, PostgreSQL в базовой конфигурации уступает CH на 1–2 порядка (Рисунок 3).
Результаты после оптимизации PostgreSQL
Для объективности была проведена серия доработок PG: сбор статистики, настройка планов выполнения, индексация. Этот подход, хотя и менее гибок, чем нативная аналитическая архитектура, является стандартным способом акселерации ROLAP-решений. Результаты значительно улучшились (Рисунки 4 и 5), причем «плоская» схема показала впечатляющий рост производительности.
Обсуждение
Анализ корреляции времени выполнения запросов (t) и объема данных (n) показал любопытные закономерности. В ClickHouse время отклика демонстрирует высокую линейную зависимость от объема, что свидетельствует о предсказуемости системы. В то же время неоптимизированный PostgreSQL показывал странную «горизонтальную» динамику, что может указывать на специфические накладные расходы, которые перекрывают рост объема. После оптимизации PG «подтянулся» к CH, и зависимость t(n) стала более выраженной. В конечном счете, для сложных сред идеальным вариантом представляется гибридная стратегия, выбирающая метод выполнения в зависимости от объема выборки.
Практические выводы
Индексация и тюнинг запросов эффективны для фиксированных отчетов, но не подходят для self-service BI, где конечный пользователь сам определяет структуру срезов. В таких случаях специализированные аналитические платформы вне конкуренции. Также стоит помнить, что индексы требуют значительных ресурсов на хранение и замедляют процесс записи данных.
Использование SSB в качестве инструмента для проектирования инфраструктуры (Sizing) выглядит крайне перспективным: накопление статистики на эталонных тестах позволит в будущем автоматизировать выбор конфигурации серверов под будущие задачи.
Многомерный анализ (MDX)
Попытка реализовать SSB-запросы через Pentaho (Mondrian) наглядно показала ограничения классического ROLAP при работе с «высокой кардинальностью» данных. Вычисление выражений типа SUM(a*b) для миллионов позиций приводит к декомпозиции запроса на тысячи SQL-операций, что делает систему неработоспособной. Для таких задач требуется либо денормализация (добавление предрассчитанных колонок), либо переход на современные аналитические движки.
Заключение
Подводя итог:
-
SSB наглядно подтверждает преимущество аналитических колоночных баз;
-
Традиционные OLTP-системы (PG) имеют серьезный потенциал для ускорения аналитики при правильном подходе;
-
SSB — мощный инструмент не только для сравнения СУБД, но и для планирования мощностей (Capacity Planning);
-
Гибкость BI-инструментов (как в случае с Mondrian) ограничена производительностью базовой модели данных;
-
Оптимизация аналитических систем остается творческим процессом, требующим учета специфики каждой задачи.
Благодарности
Выражаю благодарность Виктору Ш. за поддержку, Сергею Д. за помощь в оптимизации PostgreSQL и Георгию М. за профессиональные советы по публикации.
Ссылки
| [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. |
