Чтобы понимать предпочтения пользователей и оценивать эффективность работы сервисов 2ГИС, мы собираем обезличенную информацию. Наши заказчики — это продакт-менеджеры, представители коммерции и маркетинга, партнёры и рекламодатели, которые смотрят статистику в личном кабинете.
Пользовательская статистика насчитывает от 21 до 27 параметров. Она включает в себя город, рубрику, фирму и так далее.
Большое количество параметров событий ведёт к большому количеству отчётов: суммарные показатели, средние значения, отклонения, топ-10, -100, -1000 и ещё много чего. При таком раскладе трудно предсказать, какая именно информация пригодится завтра. А когда эта необходимость появится, предоставить данные будет нужно as soon as possible.
Знакомо?
В цифрах
26 млн пользователей 2ГИС формируют в сутки около 200 млн событий. Это примерно 2400 rps, которые надо получить, обработать и сохранить. Полученные данные нужно оптимизировать для произвольных Ad hoc и аналитических запросов.
Задача заключается в следующем:
— Подготовить данные (ETL). Это самый значимый и трудоёмкий этап.
— Рассчитать агрегаты (препроцессинг).
Для начала решим первый вопрос.
Как было раньше
Когда-то давно система нашей бизнес-аналитики выглядела совсем иначе. Она отлично подходила для работы с небольшим количеством городов, но когда мы вышли на новые рынки, эта система оказалась громоздкой и неудобной:
- Данные хранились в не разбитых по секциям таблицах и обновлялись стандартными «insert» и «update». Операции применялись ко всему массиву данных.
- При новых запросах к данным, таблицы обрастали индексами, которые:
а) приходилось перестраивать при получении новых данных;
б) занимали всё больше и больше места. - Операции «join» многомиллионных таблиц были почти невозможны.
- Административные операции — «резервное копирование», «сжатие» и «перестроение индексов» — занимали много времени.
- Для обработки многомерной базы данных приходилось ежедневно обрабатывать весь массив данных. Даже те, которые не изменились.
- Аналитические запросы к многомерной базе данных также занимали много времени.
Поэтому мы решили обрабатывать данные по-другому.
Новый подход
Секционирование + файлы + файловые группы
Секционирование — это представление таблицы в качестве единой логической сущности, в то время как её части — секции — могут физически находиться в разных файлах и файловых группах.
Таблица разбивается на секции при помощи функции секционирования. Она определяет границы диапазонов для значений столбца секционирования. На ос��ове функции секционирования строится схема секционирования. Выбор функции секционирования является ключевым, т.к. профит в запросах на выборку данных будет лишь при использовании в запросе столбца секционирования. В этом случае схема секционирования укажет, где искать требуемые данные.
Зачастую в качестве функции секционирования выбирают время (день, месяц, год). Это обусловлено историчностью данных: старые данные не меняются. А значит, что секции, в которых они находятся, можно поместить в файловые группы и обращаться к ним только по мере необходимости поиска в старых периодах. Для экономии ресурсов их можно даже положить на медленные диски.
Мы в качестве функции секционирования выбрали месяц, т.к. большая часть запросов строится помесячно.
Однако при этом появляется несколько проблем.
- Вставка всё ещё происходит в большую таблицу. Если на ней есть индекс, то вставка новых данных приведёт к перестроению индекса, а увеличение числа индексов — к неминуемому торможению операции вставки новых данных.
- Для секционирования многомерных баз данных Microsoft рекомендует использовать секции до 20 млн записей. Наши секции оказались на порядок больше. Это грозило нам провалами в производительности на этапе препроцессинга. Неконтролируемый рост размера секций мог свести на нет всю идею секционирования.
Чтобы решить второй вопрос, мы увеличили число секций для каждого периода. Если в каче��тве функции секционирования использовать месяц и некий порядковый номер секции этого месяца, получится следующее.
С первой проблемой было сложнее. Мы с ней справились, но чтобы оценить наше решение, нужно знать о Columnstore index.
Columnstore index
Справедливости ради стоит сказать, что Columnstore index — это не индекс в классическом понимании. Он работает по-другому.
MS SQL Server начиная с версии 2012 года поддерживает Columnstore — хранение данных в столбцах. В отличие от обычного хранения данных в строках, информация там группируется и хранится по 1 столбцу за раз.
Такой формат имеет ряд преимуществ:
— Читаются только те столбцы, которые мы запрашиваем. Некоторые столбцы могут вообще никогда не попасть в память.
— Столбцы сильно сжимаются. Это сокращает байты, которые надо прочитать.
— В индексе Columnstore не существует понятия ключевых столбцов. Ограничение числа ключевых столбцов в индексе (16) не применяется к индексам Columnstore. В нашем случае это важно, т.к. число параметров (столбцов Rowstore) значительно больше 16.
— Индексы Columnstore работают с секционированием таблиц. Columnstore на секционированной таблице должен быть выровнен по секциям с базовой таблицей. Таким образом, некластеризованный индекс Columnstore может быть создан для секционированной таблицы, только если столбец секционирования является одним из столбцов в этом индексе. Для нас это не проблема, т.к. секционирование производится по времени.
«Отлично!» — подумали мы. — «Это то что надо». Однако одна особенность Columnstore index оказалась проблемой: в SQL Server 2012 таблица с индексом Columnstore не может быть обновлена. Операции «insert», «update», «delete» и «merge» недопустимы.
Вариант удаления и перестроения индекса при каждой операции вставки данных оказался неприменимым. Поэтому задачу мы решили при помощи переключения секций.
Переключение секций
Вернёмся к нашей таблице. Теперь она с Columnstore index.
Создадим ещё одну таблицу со следующими свойствами:
— всё те же столбцы и типы данных;
— такое же секционирование, только по 1 секции для каждого месяца;
— без Columnstore index.
В неё мы заливаем новые данные: будем перекладывать оттуда секции в стабильную таблицу.
Поехали.
Шаг 1. Определяем секции, требующие переключения. Нам нужны секции по 20 млн записей. Загружаем данные и на определённой итерации обнаруживаем, что одна из секций наполнилась.
Шаг 2. В стабильной таблице создаём секцию под переключаемые данные. Секцию надо создать в соответствующей файловой группе — Октябрь 2013. Имеющаяся пустая секция (14) в файловой группе Сентября нам не подходит. Секцию (15) создаём для загрузки туда данных. Плюс, делаем одну лишнюю секцию (16), которую будем «размножать» в следующий раз, т.к. всегда для размножения нужна одна пустая секция в конце.
Шаг 3. Переключаем секцию-назначение в промежуточную таблицу.
Шаг 4. Заливаем данные из таблицы для загрузки данных в промежуточную таблицу. После этого на промежуточной таблице можно создать Columnstore index. На 20 млн записей это делается очень быстро.
Шаг 5. Переключаем секцию из промежуточной таблицы в стабильную.
Теперь:
— столбцы и типы данных совпадают;
— новая секция в соответствующей этой секции файловой группе;
— Columnstore index в новой секции мы уже создали, и он полностью соответствует индексу стабильной таблицы.
Шаг 6. Для полной чистоты закроем в стабильной таблице пустую (14) секцию, которая нам уже не нужна.
Итог — таблица для загрузки снова готова к приёму данных.
Стабильная таблица пополнилась одной секцией (15). Последняя секция (16) готова к размножению, идентичному шагу 2.
В целом задача достигнута. Отсюда можно переходить к препроцессингу (предварительной подготовке) данных.
Обработка многомерной базы данных (OLAP)
Напомню: нам надо обеспечить online-анализ данных по произвольному набору столбцов с произвольной фильтрацией, группировкой и сортировкой. Для этого мы решили воспользоваться многомерной базой данных OLAP, которая также поддерживает секционирование.
Создаём секции, идентичные нашим таблицам. Только для основной «стабильной» таблицы нарезаем секции 1-в-1 в соответствии с реляционной базой данных.
А для таблицы загрузки вполне хватит одной общей секции.
Теперь мы получили просчитанные агрегаты (суммы) по декартову произведению всех параметров в каждой секции. Во время пользовательского запроса многомерная база данных прочитает секции, соответствующие запросу, и просуммирует агрегаты между собой.
Сжатие старых периодов
Итак, мы сделали всё, как написано выше, но обнаружили, что один и тот же месяц размазан по значительному числу секций. При этом число секций для каждого месяца возрастает с ростом числа пользователей, городов покрытия, платформ и т.д.
Это ежемесячно увеличивает время подготовки отчёта. Да и просто — занимает лишнее место на диске.
Мы проанализировали содержимое секций одного месяца и пришли к выводу, что можем сжать его, агрегируя по всем значимым полям. Детализации по времени нам оказалось достаточно с точностью до даты. Сжатие старых периодов особенно эффективно, когда исторические данные уже не поступают и число секций для сжатия максимально.
Как мы это делали:
Шаг 1. Определяем все секции одного месяца.
Шаг 2. Добавляем остатки из таблицы загрузки.
Шаг 3. Агрегируем по всем значимым полям. Операция производится примерно раз в месяц, поэтому здесь вполне можно пожертвовать ресурсы.
На этом этапе может оказаться, что размер полученных секций не соответствует нашим идеальным 20 млн. С этим ничего не поделаешь. По крайней мере, то, что последняя секция каждого месяца будет неполной, никак не влияет на производительность.
Шаг 4. В конце не забываем перекроить многомерную базу данных. Полная обработка занимает порядка 5—6 часов. Это вполне допустимо для ежемесячной операции.
Итоги
Секционирование для больших таблиц — это must have
Секционирование позволяет размазывать нагрузку по файлам, файловым группам и дискам. При этом даже с использованием секционирования размер имеет значение.
Мы ставили перед собой цель сформировать 20-млн секции, чтобы использовать их в дальнейшем для построения многомерной базы данных. В каждом конкретном случае размер секции должен определяться решаемой задачей.
Также критически важна функция секционирования.
Columnstore index решает!
Мы покрыли все Ad hoc запросы. У нас нет необходимости создавать/перестраивать индексы при появлении новых задач на выборку данных.
Реализация Columnstore index в MS SQL Server 2012 фактически дублирует исходную Rowdata таблицу, создавая такую же, но с колоночным хранением.
Тем не менее объём данных, занимаемый индексом значительно меньше, чем если бы мы создавали набор специальных индексов под каждую задачу.
Ограничение на insert вполне обходится переключением секций.
Итоги в цифрах
Например, одна из таблиц: 3 940 403 086 строк; 285 887,039 MB
| Время выполнения запроса | Секционированная таблица | Секционированная таблица + Columnstore | Многомерная база данных OLAP |
|---|---|---|---|
| Количество звонков 5-го мая из iPhone-версии в Москве | 8 мин. 3 сек. | 7 сек. | 6 сек. |
| Физический размер событий типа А | 285,9 GB | 285,9 GB + 0,7 GB index |
67 GB |
Какие ещё есть варианты?
Не MS
Исторически сложилось, что вся Enterprise-разработка в компании строится на базе решений Microsoft. Мы пошли по этому же пути и другие варианты не рассматривали в принципе. Благо MS SQL Server поддерживает работу с большими таблицами на всех уровнях обработки. Сюда входят:
— реляционная база данных (Data Warehousing);
— Sql Server Integration Services (ETL);
— Sql Server Analysis Services (OLAP).
MS SQL Server 2014
В SQL Server 2014 функциональность Columnstore была расширена, он стал кластерным. Вновь поступающие данные попадают в Deltastore — традиционное (Rowstore) хранение данных, которые по мере накопления переключаются к основному Columnstore.
Если вам не нужно чётко фиксировать размер секций, SQL Server 2014 будет отличным решением для сбора, обработки и анализа пользовательской статистики.
