что такое olap куб простыми словами

olap для маленькой компании

В посте Многомерные кубы, OLAP и MDX Vitko написал: «тема очень интересная и с каждым днем становится все более актуальной». К сожалению, это заклинание произносится уже очень давно (по крайней мере я его слышу с 2004 года ), но olap проектов до сих пор очень мало. Возможно, потому что традиционно считается, что всё, что связанно с olap нужно только для крупных компаний с большими объемами накопленных данных и стоит очень дорого. Но это не совсем так. Я хочу рассказать о проекте, который внедрен в одной относительно небольшой компании.

Проект очень древний, начинался ещё в 2003 году. Про некоторые вещи можно сказать «так исторически сложилось». Но, мне кажется общая идея, может быть полезной.

Итак. Компания занимается оптовой торговлей кондитерскими изделиями. Опт кондитерки достаточно специфичный бизнес. При относительно небольших оборотах приходится иметь дело с большими объемами данных. Клиентами компании являются как крупные торговые сети, так и небольшие магазинчики в деревнях области. Плюс огромный ассортимент продукции. Причем клиент может купить любой объем товара – от одного сникерса до вагона печенья (были прецеденты, когда на склад возврата товара поступало половинка зефиринки (история умалчивает какой было причина возврата) ).

Основная учетная система — 1С «Торговля и склад» 7.0, причем dbf версия. Она достаточно успешно справляется с задачами учета товара. Но получить в ней отчеты за большие периоды времени практически нереально. Подобные попытки создают серьезную нагрузку на сервер, начинаются проблемы у операторов 1с, жалобы в It отдел.

Потребность в таких отчетах была постоянная. Сложилась идеальная ситуация для реализации bi проекта: большой объём информации + люди заинтересованные в её анализе.

Для начала, небольшой ролик, демонстрирующий, как пользователь может сам получать информацию.

Посмотреть avi в нормальном качестве можно скачав отсюда 5,25Mb ( 6 минут )
Поработать с локальным кубом можно скачав пример 2,64Mb
или тут 8Mb

Как это реализовано:

В принципе необязательно строить «хранилище». Данные для куба можно получать напрямую из базы 1с ( MsSQL или dbf ). Но в моем случае из 1с данные прошлых периодов периодически удаляются и очищаются справочники. Кроме того перед загрузкой в хранилище данные немного «чистятся».

С кубами работают сотрудники в офисе – руководство, менеджеры, маркетинг, бухгалтерия. Так же информация отправляется поставщикам и торговым представителям в разных городах области.

Любой пользователь может получить информацию разными путями:

Сначала использовался только excel, но возникало много проблем с тем, что екселевские файлы «разбредались», нужно было получить одну «точку входа» для выбора информации.
Поэтому был создан локальный сайт, на котором опубликованы страницы с PivotTable. Сотрудник, который хочет получить пару цифр «здесь и сейчас» заходит на этот сайт и строит отчет в нужной ему форме. Если человеку нужно использовать этот отчет в дальнейшем – он может написать заявку, чтобы его отчет опубликовали в SSRS или сам сохраняет его в excel.

Локальные кубы

Иногда пользователю нужно периодически получать отчеты, содержащие большие объемы данных. Например, отдел маркетинга отправлял отчеты поставщикам в виде екселевских файлов содержащих по несколько десятков страниц.
Olap не «заточен» для получение такой информации – отчеты формировались очень долго.

Как правило, поставщику тоже неудобно работать с большими отчетами. Поэтому большая часть, попробовав работать с локальными кубами, согласилась получать отчетность в таком виде. Список отчетов, которые формировал отдел маркетинга, значительно сократился. Оставшиеся тяжелые отчеты были реализованы в SSRS, созданы подписки (отчеты формируются автоматически и рассылаются поставщикам по расписанию)

Основные параметры системы

Конфигурация сервера:

процессор: 2xAMD Opteron 280
память: 4Gb
дисковые массивы:
операционная система: RAID 1 (зеркало) 2xSCSI 15k
данные: RAID 0+1 4xSCSI 10k

Согласитесь, такую машинку сложно назвать «мощным» сервером

Объем данных:

хранилище 10Гб, данные с 2002 года
агрегация 30%
Размер многомерной базы 350М
кол-во членов «больших измерений»: товары 25 тыс., адреса – 20 тыс.
кол-во документов в день — 400. среднее кол-во строк в документе — 30

Что в итоге получила компания:

Плюсы

Для руководства предприятия

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

Для менеджера

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

Для поставщиков

Возможность интерактивной работы с информацией

С точки зрения it-специалиста

Уменьшение рутинной работы. Большую часть отчетов пользователь получает самостоятельно.

Источник

Многомерные кубы, OLAP и MDX

Довольно давно являюсь обитателем Хабра, но так и не доводилось читать статьи на тему многомерных кубов, OLAP и MDX, хотя тема очень интересная и с каждым днем становится все более актуальной.
Не секрет, что за тот небольшой промежуток времени развития баз данных, электронного учета и онлайн систем, самих данных накопилось очень много. Теперь же интерес также представляет полноценный анализ архивов, а возможно и попытка прогнозирования ситуаций для подобных моделей в будущем.
С другой стороны, большие компании даже за несколько лет, месяцев или даже недель могут накапливать настолько большие массивы данных, что даже их элементарный анализ требует неординарных подходов и жестких аппаратных требований. Такими могут быть системы обработки банковских транзакций, биржевые агенты, телефонные операторы и т.д.
Думаю, всем хорошо известны 2 разных подхода построения дизайна баз данных: OLTP и OLAP. Первый подход (Online Transaction Processing — обработка транзакций в реальном времени) рассчитан на эффективный сбор данных в реальном времени, второй же (Online Analytical Processing – аналитическая обработка в реальном времени) нацелен именно на выборку и обработку данных максимально эффективным способом.

Немного подробнее о возможностях

Быстрый доступ к данным
Собственно быстрый доступ к данным, независимо от размеров массива, и является основой OLAP систем. Так как основной упор именно на этом, хранилище данных обычно строится по принципам, отличным от принципов реляционных баз данных.
Здесь, время на выборку простых данных измеряется в долях секунды, а запрос, превышающий несколько секунд, скорее всего, требует оптимизации.

Преагрегация
Кроме быстрой выборки существующих данных, также предоставляется возможность преагрегировать «наиболее вероятно-используемые» значения. Например, если мы имеем ежедневные записи о продажах какого-то товара, система может преагрегировать нам также месячные и квартальные суммы продаж, а значит, если мы запросим данные помесячно или поквартально, система нам мгновенно выдаст результат. Почему же преагрегация происходит не всегда – потому, что теоретически возможных комбинаций товаров/времени/и т.д. может быть огромное количество, а значит, нужно иметь четкие правила для каких элементов агрегация будет построена, а для каких нет. Вообще тема учета этих правил и собственно непосредственного дизайна агрегаций довольно обширна и сама по себе заслуживает отдельную статью.

Иерархии
Закономерно, что анализируя данные и строя конечные отчеты, возникает потребность учитывать то, что месяцы состоят из дней, а сами образуют кварталы, а города входят в области, которые в свою очередь являются частью регионов или стран. Хорошая новость то, что OLAP кубы изначально рассматривают данные с точки зрения иерархий и взаимоотношений с другими параметрам одной и той же сущности, так что построение и использования иерархией в кубах – дело очень простое.

Работа с временем
Так как в основном анализ данных происходит на временных участках, именно времени в OLAP системах выделено особое значение, а значит, просто определив для системы, где у нас тут время, в дальнейшем можно с легкостью пользоваться функциями типа Year To Date, Month To Date (период от начала года/месяца и до текущей даты), Parallel Period (в этот же день или месяц, но в прошлом году) и т.п.

Язык доступа к многомерным данным
MDX (Multidimensional Expressions) — язык запросов для простого и эффективного доступа к многомерным структурам данных. И этим все сказано – внизу будет несколько примеров.

Key Performance Indicators (KPI)
Ключевые показатели эффективности — это финансовая и нефинансовая система оценки, которая помогает организации определить достижение стратегических целей. Ключевые показатели эффективности могут быть достаточно просто определены в OLAP системах и использоваться в отчетах.

Дата майнинг
Интеллектуальный анализ данных (Data Mining) — по сути, выявление скрытых закономерностей или взаимосвязей между переменными в больших массивах данных.
Английский термин «Data Mining» не имеет однозначного перевода на русский язык (добыча данных, вскрытие данных, информационная проходка, извлечение данных/информации) поэтому в большинстве случаев используется в оригинале. Наиболее удачным непрямым переводом считается термин «интеллектуальный анализ данных» (ИАД). Впрочем, это отдельная, не менее интересная тема для рассмотрения.

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

Поддержка мультиязычности
Да-да-да. Как минимум Analysis Services 2005/2008 (правда, Enterprise Edition) нативно поддерживают мультиязычность. Достаточно привести перевод строковых параметров ваших данных, и клиенту, указавшему свой язык, будут приходить локализированные данные.

Многомерные кубы

Так что же все-таки эти многомерные кубы?
Представим себе 3-х мерное пространство, у которого по осям Время, Товары и Покупатели.
Точка в таком пространстве будет задавать факт того, что кто-то из покупателей в каком-то месяце купил какой-то конкретный товар.

Фактически, плоскость (или множество всех таких точек) и будет являться кубом, а, соответственно, Время, Товары и Покупатели – его измерениями.
Представить (и нарисовать) четырехмерный и более куб немного сложнее, но суть от этого не меняется, а главное, для OLAP систем совершенно неважно в скольких измерениях вы будете работать (в разумных пределах, конечно).

Немного MDX

Итак, в чем же прелесть MDX – скорее всего в том, что описывать нужно не то как мы хотим выбрать данные, а что именно мы хотим.
Например,

Что означает – хочу количество iPhone-ов, проданных в июне и июле в Мозамбике.
При этом я описываю какие именно данные я хочу и как именно я хочу их увидеть в отчете.
Красиво, не правда ли?

А вот чуть посложнее:

Фактически, вначале определяем формулу подсчета «среднего размера покупки» и пытаемся сравнить – кто же (какой пол), за один заход в магазин Apple, тратит больше денег.

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

Заключение

На самом деле, данная статья очень мало покрывает даже базовых понятий, я бы назвал ее «appetizer» — возможность заинтересовать хабра-сообщество данной тематикой и развивать ее дальше. Что же касается развития – тут огромное непаханое поле, а я буду рад ответить на все интересующие вопросы.

Источник

Создаем OLAP куб. Часть 1

Продолжая тематику Многомерные кубы, OLAP и MDX и olap для маленькой компании, традиционно, предлагаю начать с простенького «Hello World» куба, который будет анализировать процессы и тенденции голосований на Хабре.

Немного теории.

Каким же должен быть Data Warehouse?

Все очень просто – ваш Data Warehouse должен иметь структуру формы звездочки (star model) или снежинки (snowflake model) и состоять из фактов (facts) и измерений (dimensions).

Факты – это фактические записи (records) о каком-то процессе, который мы хотим анализировать, например, процесс голосования на Хабра, или процесс изменения цены товара на бирже. Очень часто факты содержат какие-нибудь числовые данные, например, фактическое значение голоса или цены.

Измерения – это определяющие атрибуты фактов, и обычно отвечают на всякие вопросы: когда произошел факт, над чем или с чем именно, кто был объектом или субъектом и т.п. В основном, измерения имеют более описательный (то есть текстовый) характер, например, имя пользователя или название месяца, так как конечному пользователю будет намного легче воспринимать результаты описанные текстом (например, название месяца), нежели цифрами (номер месяца в году).

Определив где у нас факты, а где измерения — очень просто построить модель звезды.

Звезда.

В центре указываем нашу таблицу фактов, а лучами выводим измерения.

А теперь снежинка.

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

Каждая из этих моделей имеет свои достоинства и недостатки и собственно выбор модели должен базироваться на требованиях к дизайну куба, скорости загрузки данных, дискового пространства и т.д.
Естественно, конечные Data Warehouse обычно намного сложнее и состоят из нескольких звезд или снежинок, которые могут совместно использовать общие измерения.

HabraDW.

Перейдем к собственно разработке нашего Data Warehouse-а.

Итоговая схема нашей звезды будет такой.

А здесь исходный SQL скрипт, который создает и наполняет (пока что только случайными данными) наше хранилище.

Ну вот, теперь все готово, чтобы загрузить данные в куб.
До встречи в следующей статье.

Источник

OLAP и многомерные СУБД: как устроен оперативный анализ данных

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

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

Чтобы получить информацию, нужную для принятия взвешенного решения, надо собрать данные из различных источников, обработать и проанализировать. Для этого корпоративное хранилище данных должно быть организовано особым образом, в частности с использованием технологии OLAP. Ее мы и рассмотрим в статье.

Что такое OLAP и зачем нужны такие системы

OLAP — это online analytical processing, оно же — оперативный анализ данных. Давайте попробуем определить это понятие на человеческом языке.

В IT-системах данные хранятся в разных источниках — это несвязанные между собой базы данных, хранилища событий, файлы, быстрые хранилища, системы статистики. В этой куче информации прячется то, что важно знать для эффективного управления IT-продуктом и бизнесом. Но достать нужные сведения из столь разнородной структуры и представить в виде, удобном для менеджеров и аналитиков — проблематично.

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

А почему «оперативный»? Допустим, вы управляете большим интернет-магазином и прямо сейчас тестируете на эффективность несколько рекламных кампаний. Из всех кампаний нужно отобрать самую эффективную и уже с ней работать дальше. Система обработки данных, конечно, позволит увидеть нужные цифры и принять правильные решения. Но данные из нее надо достать быстро — если построение отчета займет недели, то с такой задержкой хорошие решения принять нельзя.

Поэтому инженеры сделали не просто систему обработки и анализа данных из разнородных источников — они сделали ее быстрой, чтобы вся нужная информация попадала на стол менеджеров практически в режиме реального времени.

OLAP и многомерный анализ данных

Работа OLAP-систем опирается на многомерную модель данных, то есть такие системы позволяют анализировать множество разных параметров с разных сторон. Они обрабатывают многомерные массивы данных, то есть такие, в которых каждый элемент массива связан с другими элементами.

Поэтому OLAP позволяет строить гипотезы, выявлять причинно-следственные связи между разными параметрами, моделировать поведение системы при изменениях.

Данные при этом организованы в виде многомерных кубов — осями будут отслеживаемые параметры, на их пересечении находятся данные. Пользователи могут выбирать нужные параметры и получать информацию по разным измерениям.

Вот так выглядит многомерная модель данных. Источник

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

Для визуализации данных многомерного куба используют обычные таблицы — тут видно число продаж по регионам за месяц

OLAP-система собирает информацию из баз данных, ERP, CRM и других источников, а затем формирует многомерный массив данных. В общем виде структура OLAP выглядит так:

Как можно реализовать OLAP на практике: виды таких систем

Самый простой и очевидный подход — создать систему, которая напрямую ничего не хранит, но умеет быстро вынимать разные записи из разных мест и в правильном виде показывать данные менеджерам. Такие системы хорошо работают, когда данные разложены по однотипным СУБД. Например, все подразделения сидят на реляционной СУБД PostgreSQL.

OLAP с такой архитектурой будет называться Relational OLAP (ROLAP) — OLAP, построенный на отношениях таблиц и баз данных между собой. Такая система не требует предварительной подготовки записей в таблицах для анализа — можно брать все нужные значения напрямую и в режиме онлайн.

Если же данные лежат не только в однотипных корпоративных базах данных, то надо собирать информацию по разным источникам и сводить всё это вместе. Появляется этап предварительной подготовки данных на отдельном сервере. И такая система — это уже Multidimensional OLAP (MOLAP), или многомерный OLAP. Такую штуку построить сложнее, но иногда без нее никак — чем больше ваша компания, тем больше разнородных систем хранения данных в ней будет задействовано. Это наиболее эффективный тип для аналитической обработки, так как позволяет структурировать данные под разные запросы пользователей.

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

Самое интересное: многомерный анализ данных

Самая интересная технология из всех этих — многомерный OLAP и многомерные системы, которые применяют для сбора информации из всех подразделений компании. Софт для таких систем чертовски сложен и интересен, он умеет работать с различными источниками, при этом делать это быстро и эффективно, одновременно опрашивая десятки многотерабайтных таблиц.

Однако впечатляющая способность опрашивать разных поставщиков — не самое главное, у таких систем есть еще крутейший набор инструментов для работы с самими данными.

Давайте бросим взгляд на несколько представителей рынка многомерных БД для OLAP:

Источник

Что под капотом у BI? Детальный разбор технологии In-Memory OLAP

Привет, Хабр! Меня зовут Иван Вахмянин, и сегодня я хочу рассказать о том, что находится “под капотом” у современной BI-системы, от чего зависит ее производительность (и как можно её ненароком убить), и какие технические оптимизации позволяют технологии In-Memory OLAP выигрывать по скорости у других подходов.

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

С точки зрения бизнеса это бывает очень грустно. Какой-то пользователь создает дашборд, и всё падает. При этом увеличение объема памяти и количества процессоров не даёт почти никакого эффекта. Предотвратить или быстро решить такую проблему гораздо проще, если хотя бы в общих чертах представляешь, как система работает «под капотом».

Когда мы только начинали работать в сфере BI 5 лет назад, в основу продукта Visiology легла open-source библиотека Pentaho — Mondrian. Но достаточно быстро мы столкнулись с проблемами по части производительности и начали самостоятельно разрабатывать In-Memory OLAP движок под названием ViQube (об этом можно почитать в другой нашей статье — Как разработать BI-платформу — наш трудный, но интересный опыт). Собственно, в процессе этой разработки мы и накопили опыт, которым сейчас хотим поделиться.

Как работает OLAP

На первый взгляд, все BI-платформы выглядят одинаково: у вас есть источники информации, у вас есть инструменты загрузки, анализа и визуализации данных, а на выходе пользователь получает разнообразные отчеты — от печатных форм до дашбордов, в том числе на мобильных, на видеостенах, на любых устройствах. В своей основе все BI-инструменты используют модель данных на основе OLAP (On-Line Analytical Processing, многомерное представление данных), но техническая реализация OLAP движка (который непосредственно занимается вычислениями) может быть реализован по-разному, и от этого очень сильно зависит производительность и масштабируемость системы.

Технология OLAP возникла ещё в 80-х годах. В то время процессоры были намного медленнее, да и память была в дефиците, поэтому чтобы аналитик мог реально работать с данными в онлайн-режиме, придумали такую вещь как MOLAP (Multidimensional OLAP). Идея подхода в том, что для всего многомерного куба после загрузки данных производится предрасчет: на узлах иерархий предварительно рассчитываются агрегации, чтобы под любой более или менее типовой запрос пользователя можно было получить результат запроса без необходимости пересчитывать все строки. Да, при любом изменении данных нужно долго пересчитывать куб, а объем рассчитанного куба может быть в разы больше исходного датасета, но в то время других вариантов не было. MOLAP до сих пор существует и используется, например, в SQL Server Analysis Services, но на практике его используют все реже и реже.

Позже появилась реляционный OLAP, или ROLAP. Отличие от MOLAP заключается в том, что не происходит никакого предварительного расчёта агрегаций, а вычисления происходят на СУБД из бэкэнда BI-платформы. В этом случае пользователь работает с удобными инструментами, например, с конструктором дашбордов, а под капотом ROLAP-движок преобразует его запросы на лету в SQL, и они просто выполняются на какой-то СУБД.

Подобный подход характерен, например, для таких open-source систем, как Pentaho или Metabase или проприетарного SAP Business Objects, Oracle OBIEE.

У ROLAP есть целый ряд недостатков. Во-первых, если, не использовать на бэкенде специальные аналитические СУБД, такие как ClickHouse или Vertica, все будет работать ооочень медленно (дальше будет понятно, почему). Во-вторых, даже при использовании аналитической СУБД, при работе с ROLAP очень неэффективно используется кэш, потому что СУБД и BI-платформа работают отдельно друг от друга. В-третьих, поскольку не все аналитические задачи можно завернуть в SQL-запрос, ограничивается аналитическая функциональность. Но зато, на сегодняшний день ROLAP — это единственный способ работы с реально большими объемами данных, которые не помещаются в память.

Если речь идет о работе с данными объемом до терабайта, как правило, используется схема In-Memory. Данные постоянно находятся в памяти, и за расчеты отвечает специальный движок. В системах Qlik — это QIX, Power BI использует SQL Server Tabular Engine, который раньше был продуктом xVelocity, но Microsoft купил эту компанию, и теперь движок является частью MS SQL Server. У нас в Visiology движок In-Memory OLAP называется ViQube.

In-Memory OLAP привлекает простотой установки и работы, подобные движки изначально интегрированы в BI-платформы и укомплектованы удобными визуальными интерфейсами настройки, загрузки данных, управления правами и т.п. За счет размещения данных в памяти и специальных оптимизаций (про них ниже) многократно растет скорость обработки, расширяются возможности для аналитиков.

При этом у подхода In-Memory есть и свои недостатки. И главный из них — это предел емкости памяти. Если объем данных измеряется в терабайтах, вам нужно либо строить дорогой кластер, либо склоняться к ROLAP. Кроме этого, при таком подходе не всегда удается минимизировать задержку отображения обновлений, потому что для этого данные приходится перегружать из источника в память.

Основной схемой работы для большинства промышленных BI становится гибридная схема с одновременным использованием и In-Memory OLAP, и реляционного OLAP-движка. Горячие данные хранятся в In-Memory, холодные данные, которые не влезли в заданный объем, — в СУБД. Такое решение в QlikView, например, называется Direct Discovery, в Power BI — Direct Query. В Visiology тоже поддерживаются интеграции с несколькими СУБД, в том числе с ClickHouse.

Кстати, выбор СУБД для гибридного режима также критически важен. Если мы будем работать с PostgreSQL, в котором лежит 5 терабайт данных, аналитические запросы будут исполняться крайне медленно. И если у вас не SAP HANA, придется вручную распределять данные на холодные и горячие. Как следствие, не все аналитические функции будут доступны на полном объёме данных. Но если памяти не хватает, увы, с таким положением дел приходится мириться.

Откуда “растут” плюсы In-memory OLAP?

Для скорости работы In-Memory OLAP есть как очевидные, так и скрытые причины. Тот факт, что работа движка происходит в памяти, а она намного быстрее, чем жесткий диск (спасибо, кэп) — это только 1/10 правды. Но давайте подумаем, как работают реляционные СУБД, например, тот же PostgreSQL. Ведь он в какой-то мере тоже является In-Memory. И вообще, любая современная СУБД активно использует как блочный кэш в памяти, так и внутренний.

Когда обычной дисковой СУБД, такой как PostgreSQL, нужно считать данные с жёсткого диска, она обращается к накопителю и считывает какую-то страницу. Эта страница помещается в блочный кэш (в Linux он располагается в свободном пространстве памяти). Допустим, у нас есть 128 гигабайт памяти, и 20 из них мы занимаем софтом. Всё остальное может использоваться под блочный кэш. И если СУБД нужно будет считать с этой страницы ещё что-нибудь, она возьмет эту информацию из памяти. И от того, насколько эффективно используется кэш, зависит производительность. Если для анализа используется, скажем, 30-40 гигабайт данных, мы можем расширить емкость оперативной памяти на сервере и уже после первого чтения СУБД все данные окажутся In-Memory, а обращения к диску на чтения будут происходить лишь эпизодически.

Кроме этого, у “умных” СУБД, в том числе у Postgres, имеется механизм cache-aware управления. Они могут выбирать, что складывать в кэш, а что – нет, какие данные надо заново прочитать с диска.

Источник: www.enterprisedb.com/blog/autoprewarm-new-functionality-pgprewarm

На графике выше — влияние прогрева кэша на производительность PostgreSQL. Жёлтым показана производительность в зависимости от времени, и наглядно видно, что по мере работы пользователей СУБД считывает данные, постепенно раскладывает всё в In-Memory и достигает предела своей производительности. Если же использовать prewarm и дать Postgres команду поднять все данные в память сразу, максимальная производительность достигается сразу.

Также стоит учитывать, что мы говорим об аналитической нагрузке. Она очень сильно отличается от транзакционных задач, когда в базу нужно внести запись о покупке в интернет-магазине или считать 10 строк с историей заказов. На графике ниже показан типовой аналитический запрос из теста TPC-H. Этот тест состоит из нескольких десятков реальных аналитических запросов и широко используется для нагрузочного тестирования.

Источник: www.tpc.org/information

В SQL-запросе из теста TPC-H можно найти много чего интересного. Во-первых, запрос идет не на поля, а на агрегации. Во-вторых, здесь присутствуют линейные арифметические операции. В-третьих, здесь часто встречаются фильтры по полям с малой кардинальностью: регионы и федеральные округа, в которых работает компания, типы клиента — активный, неактивный и так далее. В-четвертых, часто используются фильтры по полю с датой. Если мы изучаем динамику выручки, то нас интересуют такие периоды как год или квартал.

На входе подобного запроса всегда очень большое количество строк — миллионы или даже миллиарды. Поэтому СУБД вынуждена делать серию полных сканирований. С другой стороны, на выходе получается небольшое количество строк, ограниченное количеством точек, которые можно отобразить на графике — чаще всего десятки или сотни значений. Зная эти особенности аналитических запросов, можно провести оптимизацию и получить прирост производительности.

In-Memory OLAP: конкретные примеры оптимизации для BI

Учитывая особенности аналитических запросов, о которых мы уже говорили ранее, для движка BI возможен целый ряд оптимизаций, причем как технических, так и эвристических. Давайте рассмотрим их подробнее.

1. Колоночное хранение данных

Это первый шаг, дающий заметный эффект. Для обычных транзакционных запросов хранение в строках подходит оптимально. Но для аналитики необходимо получать данные по столбцам. Казалось бы, какая разница, ведь все это уже In-Memory? Но на практике кроме памяти, которая быстрее дисков, у нас также есть кэш процессора, как правило, трехуровневый. Он работает намного быстрее, чем память, доступ к которой занимает примерно 200 тактов процессора.

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

Источник: arstechnica.com/gadgets/2002/07/caching/2

В этой оптимизации есть свои особенности. Для дисковых СУБД — это обязательная оптимизация. Здесь мы выигрываем за счет того, что реже ходим в медленное хранилище, но также проигрываем, потому что данные надо распаковать, а это — вычислительно ёмкая операция. Для дисковых СУБД получается очень выгодно, для In-Memory — все не так очевидно, потому что читать из памяти обычно быстрее, чем заниматься распаковкой.

Источник: www.percona.com/blog/2016/03/09/evaluating-database-compression-methods

Самый быстрый из алгоритмов сжатия по скорости распаковки — LZ4. Он в среднем уменьшает размер всего в 2 раза, но зато очень быстро распаковывает, со скоростью порядка 500 мегабайт в секунду на ядро процессора. В бенчмарке на графике LZ4 вообще показал результат 3 гигабайта данных в секунду. Такая скорость дает очень хороший выигрыш для дисковых СУБД, скорость чтения для которых – те же 500 мегабайт в секунду. Но для памяти скорость передачи данных составляет десятки гигабайт в секунду, получить преимущество за счёт LZ4 оказывается сложно.

Однако не стоит забывать, что мы чаще всего работаем с данными низкой кардинальности. Например, в строке с названиями торговой точки очень часто будут повторяться одни и те же значения или их ID: “Москва, Москва, Москва, Москва…”

Источник: Guassian and speckle noise removal from ultrasound images using bivariate shrinkage by dual tree complex wavelet transform (Professor G R Sinha, 2015)

Для подобных данных есть ещё один алгоритм, который называется Run-length encoding. Он работает очень просто: строки типа ААААВВВВВСС он сжимает в виде 4A5B2C. Это прекрасный подход для данных с низкой кардинальностью, он помогает экономить память и оптимальнее использовать кэш процессора.

3. Векторные инструкции

Чтобы сложить 2 числа, мы кладём в один регистр процессора одно число, а в другой регистр — другое. Для ускорения этого процесса существуют векторные регистры и векторные операции (SIMD — Single Instruction Multiple Data). Они позволяют за одну операцию сложить сразу N чисел. Это уже очень зрелая технология, которая появилась в процессорах еще в 1993 году. Она поддерживалась еще в Pentium MMX (166 МГц — у меня такой был, до сих пор помню, как на него термопасту намазывал).

Источник: www.codetd.com/en/article/9633503

В Pentium MMX векторных регистров было 8, и они были рассчитаны только на целочисленную арифметику. На текущий момент практически во всех процессорах есть 128-битные регистры SSE и наборы инструкций. Регистры AVX уже 256-битные, а в серверах есть даже AVX 512. Они работают с числами с плавающей запятой, и их можно использовать для оптимизации аналитической нагрузки.

Источник: technews.tw/2020/07/16/linus-torvalds-avx-512-critique

При работе с AVX 512 мы можем обрабатывать в одном регистре 16 чисел со стандартной точностью. Теоретически тут можно получить хороший выигрыш, но для этого необходимо преодолеть ряд технических сложностей. Чтобы при компиляции оптимизация работала, нужно предусмотреть ее при написании кода. Для этого используются интринсики – особый набор функций, которые в явном виде указывают компилятору, какие инструкции процессора нужно использовать.

Вот пример, как для расчёта скалярного произведения используется AVX-инструкции 256-битных регистров.

На нашей практике использование именно AVX даёт от 1 до 10% прирост производительности, в зависимости от задач. Когда вы используете современную BI-систему, в зависимости от конкретного процессора, система будет применять разный код. Производительность от этого увеличивается, но не драматически, а в лишь в небольших пределах.

4. Поздняя материализация

Материализация — это процесс формирования результата, ответа на запрос. Например, простой SQL-запрос SELECT C1, С2, D1, D2 из 2 таблиц, получит 2 поля из одной и 2 поля из другой таблицы. Далее мы соединяем их по ключу — С1=D1.

В случае ранней материализации мы получаем 4 колонки и работаем с колоночной базой. То есть мы сначала берём С1 и С2, соединяем их в таблицу. Потом делаем то же самое с D1, D2 и после этого выполняем Join, то есть формируем строки из этих 2 таблиц, для которых истинно условие С1=D1.

В случае аналитической нагрузки на выходе всегда мало данных, потому что фильтры отсекают достаточно много значений. Когда выполняется JOIN, создаётся очень большая таблица, которая не нужна. Ведь на последней операции будет отброшено много данных.

При поздней материализации мы сначала разбираемся, что и в какой колонке нам нужно. Сначала выполняется операция JOIN С1 = D1, и мы выбираем нужные нам значения только из правой таблицы. Это можно сделать за один проход. А после этого можем взять из таблицы С только те поля, строки которых после JOIN остались. В итоге не нужно создавать большую промежуточную таблицу.

Конечно, такая оптимизация не сработает на любой нагрузке. Исследования Vertica, например, показывают, как многопрофильная СУБД позволяет выбирать различные стратегии материализации, в зависимости от задач. Но именно для BI характерна нагрузка, связанная с поздней материализацией, поэтому ее использование предпочтительно.

5. Эвристические оптимизации

In-Memory OLAP чаще всего является неотъемлемой частью BI-платформы, а BI-платформа прекрасно “знает”, какие данные в нее загружаются, как пользователь с ними работает. Конечно, это неочевидные вещи, они происходят “под капотом” BI-системы и не всегда даже видны, но позволяют получить хороший прирост производительности.

Во-первых, часто применяется автоматическая нормализация или денормализация. Данные с низкой кардинальностью иногда бывает выгодно нормализовывать, а иногда — наоборот. Чаще всего BI-платформы стараются максимально денормализовать таблицы. Такой подход позволяет максимально избегать достаточно тяжелых операций JOIN. Пользователь может даже не видеть этого: если мы загрузили в систему 2 таблицы и связали их по ключу, система может сразу превратить их в одну таблицу.

В некоторых случаях, наоборот, происходит автоматическая нормализация. Если поле очень жирное, например, с текстовым комментарием, держать его по всей колонке будет неоптимально. Вместо этого такое поле можно автоматически вывести в справочник. Вы будете видеть в таблице и строку, и столбец, а на самом деле данные будут находиться в отдельной таблице, а в памяти будет находиться только ID. Такие методики используются довольно часто.

6. Сортировка по календарю

Поскольку мы работаем с данными, которые практически всегда растянуты во времени, стоит учитывать интерес пользователей – а он чаще всего сконцентрирован на ближайших данных. Поэтому оказывается выгодно сортировать данные по календарному измерению. Действительно, если пользователь работает с текущим годом, зачем ему данные “с начала времён”?
Однако, если вы храните дату в виде строкового столбца, BI-система не сможет оптимизировать данные. Но если сортировка удается, иногда получается свести большой датасет к очень компактному, просто выкинув из него все года, кроме текущего.

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

Разделяемый кэш под запросы позволяет кэшировать данные внутри запросов. Например, один пользователь запросил аналитику и отфильтровал её по отделу А, а другой хочет по отделу В. В этом случае оптимизации не получится, потому что фильтрация происходит на раннем этапе. Но в некоторых случаях и для подобных ситуаций удается оптимизировать запрос.

Многие BI-системы выстраивают обратный индекс для строковых полей. Он позволяет ускорить поиск с операторами по строкам. И хотя BI-системы и OLAP движок In-Memory — это не замена полнотекстовому поиску, подобные оптимизации встречаются достаточно часто.

Каков эффект?

Все перечисленные меры помогают сделать BI-систему, основанную на In-Memory OLAP, более устойчивой и производительной, не прибегая к гибридным схемам работы с подключением реляционных БД. Рост производительности сильно зависит от используемых задач, но в процессе работы над ViQube мы убедились в том, что оптимизации лучше всего закладывать на этапе исходного кода и изначально проектировать систему с учетом особенностей аналитических запросов.

Кроме этого, на своем опыте мы нашли ряд кейсов, которые позволяют легко “убить” производительность In-Memory OLAP. Можно считать этот набор “вредных советов” пасхалочкой для тех, кто дочитал до конца 🙂

BI-система, работающая на базе In-Memory OLAP, уже по определению имеет высокую производительность — однако она не будет неубиваемой! Ниже — список из 5 “вредных советов” по In-Memory, выстраданных на своей шкуре в процессе разработки движка ViQube и его использования в реальных проектах.

1. Сложные вложенные выражения

Прекрасными убийцами производительности In-Memory систем являются запросы, в которых плохо работает векторизация. Любые вычисления с использованием нелинейных функций, которые задействуют много полей, приводят к радикальному снижению скорости работы системы.

Очень часто в своей практике я сталкивался с тем, что аналитик пишет какое-то выражение на Qlik Expressions или на DAX в Power BI, которое хорошо работает, когда в базе 10 тысяч строк. Но по мере роста масштабов производительность начинает деградировать невероятными темпами.

Обычно это происходит потому, что формула запроса сложна и не даёт движку использовать преимущества колоночного хранения векторных инструкций. Вместо этого системе приходится находить все поля по строкам, перебирать их одну за другой. В этом случае производительность падает до уровня СУБД со строчным хранением. Конец оптимизации.

2. Вложенные запросы

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

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

Кстати, такой запрос может вообще не пройти в принципе, если движок упрётся в предельную емкость доступной памяти. А может привести к очень сильному замедлению работы BI-системы. Поэтому при создании вложенных запросов нужно оценивать размеры промежуточного результата.

3. Частое обновление данных

Частое обновление данных не так страшно, если вы используете специальные инструменты для работы в режиме Real-time. Для чего? Почему BI-платформа In-Memory OLAP не очень любят Real-time и для Real-time предлагают, по сути, отдельные инструменты. В Power BI, например, Streaming Dataset. Зачем это сделано? Почему просто не дописывать и не считать заново?

Каждый раз, когда мы что-то меняем в исходных данных, происходит серьёзная инвалидация кэша. То есть и общий кэш, и разделяемый кэш (свой для каждого пользователя), кэш под запрос — всё это приходится выкинуть и ждать, пока оно рассчитается заново. Даже добавление одной новой строки приводит к инвалидации, особенно если она реализована не слишком умно. К тому же BI-платформы чаще всего инвалидируют кэш с запасом, чтобы исключить показ пользователю неправильных данных.

Частая инвалидация ведёт к деградации производительности BI-системы, и это особенно заметно, если одновременно BI-система обрабатывает тысячи запросов. Как правило, большинство пользователей работают с готовыми дашбордами, для которых кэш — основной источник оптимизации. Когда мы проводили нагрузочное тестирование для профиля пользователя, который просто работает с дашбордом, 90-95% запросов в принципе не доходили до движка и обслуживались из кэша. Именно поэтому частая инвалидация ведет к падению производительности в 10 и более раз.

4. Маленький запас свободной памяти

Иногда кажется, что для работы системы неважно, сколько имеется свободной оперативной памяти, лишь бы хватало общей емкости. Но на самом деле свободная память используется для буферного кэша. Можно сказать, что для In-Memory движков это не так критично, потому что они не так часто работают с жёстким диском. Но в те моменты, когда он “поднимает” данные, использует snapshot, сохраняет или загружает что-то, наличие буферного кэша оказывается очень даже важным фактором. К тому же, помимо In-Memory движка в любой BI есть и другие части системы, например, компоненты ОС. И если память кончится, они начнут резко тормозить, потому что не смогут использовать буферный кэш.

Но и это еще не все. Нехватка свободной памяти ведет к рискам просадки производительности в сложных запросах. Когда происходит создание больших объемов промежуточных данных, они тоже могут вытеснить буферный кэш, не позволяя использовать его. Что ещё хуже, такие запросы могут достичь предела доступной памяти, и вся система упадёт в swap. Это будет полный коллапс с точки зрения производительности.

5. Строковые поля с высокой кардинальностью

Последний “вредный совет” — загружать строковые поля с высокой кардинальностью. Добавляя к датасету комментарии к заказам, сообщения из чата или что-то подобное, можно сильно просадить производительность. То, что хорошо подходит для полнотекстового поиска, плохо работает для движков In-Memory OLAP. Такие данные не дают использовать RLE, векторные инструкции. Здесь мы снова падаем в выполнение строковых операций, производительность для которых намного меньше, чем для арифметических. BI-система в принципе не всегда может создать индекс на такие строковые поля со всеми вытекающими последствиями.

Да пребудет с вами производительность

Как я уже говорил, In-Memory OLAP — это продвинутая и умная технология, которая, чаще всего, «просто работает» и позволяет не задумываться о том, что внутри у BI-платформы. Но, исключения из правил случаются, и, я надеюсь, что эта статья поможет быть к ним готовым.

Всех с наступающим, и отличной производительности всем вашим сервисам в Новом Году!

Источник