Что понимают под olap технологиями

Введение в многомерный анализ

Некоторое время назад мне довелось организовывать новую группу разработки, которая должна была заняться развитием OLAP и BI продуктов в дружеской софтверной компании. А так как группа была собрана из свежих выпускников ВУЗов, то мне пришлось написать «краткий курс молодого бойца» для того чтобы максимально доступно дать начальные понятия об OLAP людям, которые ни разу с ним не сталкивались, но уже имели опыт программирования и работы с БД.

Выкладываю теперь это Введение в Общественное Достояние.

В статье несколько смешиваются понятия OLAP, Business Intelligence, и Data Warehouse, но и в жизни часто сложно понять, где проходит граница. А уж в реальных проектах, так и подавно, все они ходят рядом. Поэтому прошу не судить строго.

Введение

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

Что такое хранилище данных?

Обычно — место сбора всей информации, представляющей аналитическую ценность. Требования для таких хранилищ соответствуют классическому определению OLAP, будут объяснены ниже.

Иногда Хранилище имеет еще одну цель – интеграция всех данных предприятия, для поддержания целостности и актуальности информации в рамках всех информационных систем. Т.о. хранилище накапливает не только аналитическую, а почти всю информацию, и может ее выдавать в виде справочников обратно остальным системам. Это так называемый УОД — Управление основными данными, оно же НСИ — Нормативно-Справочная Информация.

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

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

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

Как строят хранилище?

ETL – базовое понятие: Extraction, Transformation, Loading. Три этапа:

Добавим еще один этап – очистка данных (Cleaning) – процесс отсеивания несущественных или исправления ошибочных данных на основании статистических или экспертных методов. Чтобы не формировать потом отчеты типа «Продажи за 20011 год».

Вернемся к анализу.

Что такое анализ и для чего он нужен?

Анализ – исследование данных с целью принятия решений. Аналитические системы так и называют — системы поддержки принятия решений (СППР).

Здесь стоит указать на отличие работы с СППР от простого набора регламентированных и нерегламентированных отчетов. Анализ в СППР практически всегда интерактивен и итеративен. Т.е. аналитик копается в данных, составляя и корректируя аналитические запросы, и получает отчеты, структура которых заранее может быть неизвестна. Более подробно к этому мы вернемся ниже, когда будем обсуждать язык запросов MDX.

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

Технология комплексного многомерного анализа данных получила название OLAP (On-Line Analytical Processing). OLAP — это ключевой компонент организации традиционных хранилищ данных. Концепция OLAP была описана в 1993 году Эдгаром Коддом, известным исследователем баз данных и автором реляционной модели данных. В 1995 году на основе требований, изложенных Коддом, был сформулирован так называемый тест FASMI (Fast Analysis of Shared Multidimensional Information — быстрый анализ разделяемой многомерной информации), включающий следующие требования к приложениям для многомерного анализа:

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

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

Многомерные понятия

Мы будем использовать для иллюстрации принципов OLAP базу данных Northwind, входящую в комплекты поставки Microsoft SQL Server и представляющую собой типичную базу данных, хранящую сведения о торговых операциях компании, занимающейся оптовыми поставками продовольствия. К таким данным относятся сведения о поставщиках, клиентах, список поставляемых товаров и их категорий, данные о заказах и заказанных товарах, список сотрудников компании.

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

Какие агрегатные данные мы можем получить на основе этого представления? Обычно это ответы на вопросы типа:

Все эти данные можно получить из этой таблицы вполне очевидными SQL-запросами с группировкой.

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

Представим себе, что нам надо получить информацию по суммарной стоимости заказов из всех стран и их распределение по компаниям доставщиков – мы получим уже таблицу (матрицу) из чисел, где в заголовках колонок будут перечислены доставщики, в заголовках строк – страны, а в ячейках будет сумма заказов. Это – двумерный массив данных. Такой набор данных называется сводной таблицей (pivot table) или кросс-таблицей.

Если же нам захочется получить те же данные, но еще в разрезе годов, тогда появится еще одно изменение, т.е. набор данных станет трехмерным (условным тензором 3-го порядка или 3-х мерным «кубом»).

Очевидно, что максимальное количество измерений – это количество всех атрибутов (Дата, Страна, Заказчик и т.д.), описывающих наши агрегируемые данные (сумму заказов, количество товаров и т.п).

Так мы приходим к понятию многомерности и его воплощению – многомерному кубу. Такая таблица будет у нас называться «таблицей фактов». Измерения или Оси куба (dimensions) – это атрибуты, координаты которых – выражаются индивидуальными значениями этих атрибутов, присутствующих в таблице фактов. Т.е. например, если информация о заказах велась в системе с 2003 по 2010 год, то эта ось годов будет состоять из 8 соответствующих точек. Если заказы приходят из трех стран, то ось стран будет содержать 3 точки и т.д. Независимо от того, сколько стран заложено в справочнике Стран. Точки на оси называются ее «членами» (Members).

Сами агрегируемые данные в данном случае буду назваться «мерами» (Measure). Чтобы избежать путаницы с «измерениями», последние предпочтительней называть «осями». Набор мер образует еще одну ось «Меры» (Measures). В ней столько членов (точек), сколько мер (агрегируемых столбцов) в таблице фактов.

Члены измерений или осей могут быть объединены одной или несколькими иерархиями (hierarchy). Что такое иерархия, поясним на примере: города из заказов могут быть объединены в районы, районы в области, области страны, страны в континенты или другие образования. Т.е. налицо иерархическая структура – континент-страна-область-район-город – 5 уровней (Level). Для района данные агрегируются по всем городам, которые в него входят. Для области по всем районам, которые содержат все города и т.п. Зачем нужно несколько иерархий? Например, по оси с датой заказа мы можем хотеть группировать точки (т.е. дни) по иерархии Год-Месяц-День или по Год-Неделя-День: в обоих случаях по три уровня. Очевидно, что Неделя и Месяц по-разному группируют дни. Бывают также иерархии, количество уровней в которых не детерминировано и зависит от данных. Например, папки на компьютерном диске.

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

Перейдем к языку запросов в многомерных данных.

Язык SQL изначально был спроектирован не для программистов, а для аналитиков (и поэтому имеет синтаксис, напоминающий естественный язык). Но он со временем все больше усложнялся и теперь мало кто из аналитиков хорошо умеет им пользоваться, если умеет вообще. Он стал инструментом программистов. Язык запросов MDX, разработанный по слухам нашим бывшим соотечественником Мойшей (или Мошей) Посуманским (Mosha Pasumansky) в дебрях корпорации Майкрософт, тоже изначально должен был ориентирован на аналитиков, но его концепции и синтаксис (который отдаленно напоминает SQL, причем совершенно зря, т.к. это только путает), еще сложнее чем SQL. Тем не менее его основы все же понять несложно.

Мы рассмотрим его подробно потому что это единственный язык, который получил статус стандартного в рамках общего стандарта протокола XMLA, а во вторых потому что существует его open-source реализация в виде проекта Mondrian от компании Pentaho. Другие системы OLAP-анализа (например, Oracle OLAP Option) обычно используют свои расширения синтаксиса языка SQL, впрочем, декларируют поддержку и MDX.

Работа с аналитическими массивами данных подразумевает только их чтение и не подразумевает запись. Т.о. в языке MDX нет предложений для изменения данных, а есть только одно предложение выборки — select.

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

Select – ключевое слово и в синтаксис входит исключительно для красоты.

[Territory] – это название оси. Все имена собственные в MDX пишутся в квадратных скобках.

[Cities by Countries] – это название иерархии. В нашем случае – это иерархия Страна-Город

[All] – это название члена оси на первом уровне иерархии (т.е. страны) All – это мета-член, объединяющий все члены оси. Такой мета-член есть в каждой оси. Например в оси годов есть «Все года» и т.п.

Children – это функция члена. У каждого члена есть несколько доступных функций. Таких как Parent. Level, Hierarchy, возвращающие соответственно предка, уровень в иерархии и саму иерархию, к которой относится в данном случае член. Children – возвращает набор членов-потомков данного члена. Т.е. в нашем случае – страны.

on rows – Указывает как расположить эти данные в итоговой таблице. В данном случае – в заголовке строк. Возможные значении здесь: on columns, on pages, on paragraphs и т.п. Возможно так же указание просто по индексам, начиная с 0.

from [invoices1] – это указание куба, из которого производится выборка.

Что если нам не нужны все страны, а нужно только пара конкретных? Для этого можно в запросе указать явно те страны которые нам нужны, а не выбирать все функцией Children.

Фигурные скобки в данном случае – обявление набора (Set). Набор – это список, перечисление членов из одной оси.

Теперь напишем запрос для нашего второго примера – вывод в разрезе доставщика:

.Members – функция оси, которая возвращает все члены на ней. Такая же функция есть и у иерархии и у уровня. Т.к. в данной оси иерархия одна, то ее указание можно опустить, т.к. уровень и иерархии тоже один, то можно выводить все члены одним списком.

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

А где же тут фильтрация?

where – ключевое слово

[2007] – это один член иерархии [Date]. Полное имя с учетом всех терминов было бы таким: [Date.By months].[All dates].[2007], но т.к. имя этого члена в рамках оси уникально, то все промежуточные уточнения имени можно опустить.

Почему член даты в скобках? Круглые скобки – это кортеж (tuple). Кортеж – это один или несколько координат по различным осям. Например для фильтрации сразу по двум осям в круглых скобках мы перечислим два члена из разных измерений через запятую. Т. е. кортеж определяет «срез» куба (или «фильтрацию», если такая терминология ближе).

Кортеж используется не только для фильтрации. Кортежи могут быть и в заголовках строк/колонок/страниц и т.п.

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

Crossjoin – это функция. Она возвращает набор (set) кортежей (да, набор может содержать кортежи!), полученный в результате декартового произведения двух наборов. Т.е. результирующий набор будет содержать все возможные сочетания Стран и Годов. Заголовки строк, таким образом, будут содержать пару значений: Страна-Год.

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

Вопрос: чем отличается фильтрация в where от фильтрации путем указания членов осей в on rows. Ответ: практически ничем. Просто в where указывается срез для тех осей, которые не участвуют в формировании заголовков. Т.е. одна и та же ось не может одновременно присутствовать и в on rows, и в where.

Вычисляемые члены

Для более сложных запросов можно объявлять вычисляемые члены. Члены как осей атрибутов, так и оси мер. Т.е. Можно объявить, например, новую меру, которая будет отображать вклад каждой страны в общую сумму заказов:

Вычисление происходит в контексте ячейки, у которой известные все ее атрибуты-координаты. Соответствующие координаты (члены) могут быть получены функцией CurrentMember у каждой из осей куба. Здесь надо понимать, что выражение [Territory].CurrentMember / [Territory].[Cities by Countries].[All]’ не делит один член на другой, а делит соответствующие агрегированный данные срезов куба! Т.е. срез по текущей территории разделится на срез по всем территориям, т.е. суммарное значение всех заказов. FORMAT_STRING – задает формат вывода значений, т.е. %.

Другой пример вычисляемого члена, но уже по оси годов:

Очевидно, что в отчете будет не единица, а разность соответствующих срезов, т.е. разность суммы заказов в эти два года.

Отображение в ROLAP

Системы OLAP так или иначе базируются на какой-нибудь системе хранения и организации данных. Когда речь идет о РСУБД, то говорят о ROLAP (MOLAP и HOLAP оставим для самостоятельного изучения). ROLAP – OLAP на реляционной БД, т.е. описанная в виде обычных двумерных таблиц. Системы ROLAP преобразуют MDX запросы в SQL. Основная вычислительная проблема для БД – быстрая агрегация. Чтобы быстрее агрегировать, данные в БД как правило сильно денормализованы, т.е. хранятся не очень эффективно с точки зрения занимаемого места на диске и контроля целостности БД. Плюс дополнительно содержат вспомогательные таблицы, хранящие частично агрегированные данные. Поэтому для OLAP обычно создается отдельная схема БД, которая лишь частично повторяет структуру исходных транзакционных БД в части справочников.

Навигация

Многие системы OLAP предлагают инструментарий интерактивной навигации по уже сформированному запросу (и соответственно выбранным данным). При этом используется так называемое «сверление» или «бурение» (drill). Более адекватным переводом на русский было бы слово «углубление». Но это дело вкуса., в некоторых средах закрепилось слово «дриллинг».

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

На этом все. Теперь, если вы решили посвятить себя Business Intelligence и 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-платформы. Но, исключения из правил случаются, и, я надеюсь, что эта статья поможет быть к ним готовым.

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

Источник