Порівняння аналітичних in-memory баз даних



В останні два місяці літа в управлінні сховищ даних (Data Warehouse, DWH) Тінькофф Банку з'явилася нова тема для кухонних спорів.
Все це час ми проводили масштабне тестування декількох in-memory СУБД. Будь-яка розмова з адміністраторами DWH в цей час можна було почати з фрази «Ну як, хто лідирує?», і не прогадати. У відповідь люди отримували довгу і дуже емоційну тираду про складнощі тестування, премудростях спілкування з досі невідомими вендорами та недоліки окремих випробовуваних.
Подробиці, результати і якусь подобу висновків тестування з — під катом.

Мета тестування — доглянути собі швидку аналітичну in-memory базу даних, що відповідає нашим вимогам і оцінити складність її інтеграції з іншими системами сховища даних.
Також ми включили до тестування дві СУБД, не позиціонуються як in-memory рішення. Ми розраховували на те, що наявні в них механізми кешування, за умови зразкового відповідності обсягу даних обсягом оперативної пам'яті серверів, дозволять цим СУБД наблизитися до продуктивності до класичних in-memory рішень.

Опис кейса використання

Передбачається, що обрана в результаті тестування СУБД буде працювати в якості front-end БД сховища для вибіркового набору даних (2-4 Тб, однак обсяг даних може зростати з часом): приймати на себе запити від BI-системи (SAP BusinessObjects) і частина ad-hoc запитів деяких користувачів. Запити, в 90% випадків, це SELECT'и c від 1 до 10 join-ами за умов рівності і, іноді, умовами входження дат в інтервал.
Нам потрібно, щоб такі запити працювали значно швидше, ніж вони працюють зараз в основний БД сховища — Greenplum.

Також важливо, щоб кількість одночасно виконуваних запитів не сильно впливало на час виконання кожного запиту — воно повинно бути приблизно постійним.
На наш погляд, цільова БД повинна володіти наступною функціональністю:
  • горизонтальна масштабованість;
  • можливість виконувати локальні join-и — «правильний» ключ розподілу в таблицях
  • поколоночное зберігання даних;
  • вміння працювати з кешем і великим обсягом доступної пам'яті.
Завантаження даних в цільову систему передбачається з основної БД сховища — Greenplum, а тому для нас також важливо наявність способу швидко і надійно доставити дані (бажано, инкрементально) з Greenplum в цільову БД.
Також важлива можливість інтеграції з SAP BO. На щастя, з цією системою добре працює майже все, що має стабільний ODBC-драйвер для Windows.
З дрібних, але вагомих вимог можна виділити віконні функції, резервування (здатність зберігати декілька копій даних на різних ноди), простота подальшого розширення кластеру, паралельна завантаження даних.

Стенд для тестування

На кожну БД було виділено два фізичних сервера:
  • 16 фізичних ядер (32 з HT)
  • 128 Гб ОП
  • 3.9 Тб дискового пространтства (RAID 5 з 8 дисків)
  • Сервера пов'язані 10 Гбіт-ой мережею.
  • ОС для кожної БД була обрана виходячи з рекомендацій по установці цієї самої БД. Те ж стосується налаштувань ОС, ядра і багато іншого.


Критерії тестування

  • Швидкість виконання тестових запитів
  • Можливість інтеграції з SAP BO
  • Наявність швидкого і відповідного способу імпорту даних
  • Наявність стабільного ODBC-драйвера
  • У випадку, якщо продукт не поширюється вільно, вдалося адекватне час зв'язатися з представниками компанії виробника і отримати інсталяцію (дистрибутив) БД, необхідну для тестування


БД, що увійшли до тестування

Greenplum

Старий, добрий, добре знайомий нам Greenplum. Про нього у нас є окрема стаття.
Строго кажучи, Greenplum не є in-memory БД, проте експериментально доведено, що за рахунок властивостей XFS, на якій він зберігає дані, що при певних умовах він веде себе як така.
Так, наприклад, при читанні, якщо кількість пам'яті достатньо, а також якщо дані, запитувані запитом, вже лежать в пам'яті (закешовані), диски для отримання даних порушені не будуть взагалі — всі дані Greenplum візьме з пам'яті. Треба розуміти, що такий режим роботи не властивий для Greenplum, а тому спеціалізовані in-memory DB повинні (в теорії) справлятися з таким завданням краще.
Для тестування Greenplum був встановлений за умовчанням, без дзеркал (тільки primary-сегменти). Всі налаштування дефолтні, таблиці стиснуті zlib.

Yandex Clickhouse

Колоночная СУБД для аналітики і звітів в реальному часі від відомого пошукового гіганта.
СУБД встановлена з урахуванням рекомендацій виробника, движок для локальних таблиць — MergeTree, поверх локальних таблиць були створені Distributed-таблиці, які й брали участь у запитах.

SAP HANA

HANA (High performance ANalytics Appliance) позиціонується як універсальний інструмент для аналітичної та транзакційні навантаження. Вміє зберігати дані поколоночно. Є потрібні для продуктової бази Disaster recovery, віддзеркалення і реплікації. HANA дозволяє гнучко налаштувати партіціі (шарды) для таблиць: як за hash, так і по інтервалу значень.
В наявності багаторівневе партиционирование, на різних рівнях можна застосовувати різні типи партіцій. В одну партицию можна записати до 2 мільярдів записів.


Архітектура рішення на базі SAP HANA

Одна з цікавих можливостей цієї СУБД — потабличная налаштування «unload priority» — пріоритету вивантаження з пам'яті, від 1 до 10. Вона дозволяє гнучко управляти ресурсами пам'яті і швидкістю доступу до таблиць: таблиця використовується рідко, то їй установлюється найменший пріоритет. В такому випадку таблиця буде рідко завантажуються в пам'ять і буде однією з перших завантажуватися при нестачі ресурсів.

Exasol

Продукт в Росії практично невідомий, темна конячка. З великих компаній з цієї СУБД працюють тільки Badoo (про що на Хабре є стаття) і ще пара не-ІТ-компаній, чиє ім'я на слуху — повний список є на офіційному сайті.
Вендор обіцяє феєрично швидку аналітику, стабільність каменю в лісі і простоту адміністрування на рівні кавомолки.
Працює Exasol на своїй ОС — ExaOS (свій дистрибутив GNU/Linux на основі CentOS/RHEL). Установка СУБД як мінімум незвичайна, так як це не установка окремого шматка на готову ОС, а установка ОС на окрему ліцензійну машину (в нашому випадку віртуальну) з завантаженого образу і мінімальна настройка (розбиття дисків, мережеві інтерфейси, дозволити завантаження по PxE) робочих нсд.


Спрощена архітектура Exasol

Краса цієї системи в тому, що, так як на ноди нічого встановлювати не треба (ОС, параметри ядра і інші радощі), додавання нової ноди в кластер відбувається дуже швидко. З того моменту, як сервер встановлений і скомутовано (bare metal, без ОС), можна ввести ноду в кластер менш ніж за півгодини. Все управління базою здійснюється через веб-консоль. Зайвим функціоналом вона не перевантажена, але і урізаною її назвати не можна.
Дані зберігаються в пам'яті поколоночно і непогано стискаються (налаштувань стиснення при цьому виявити не вдалося).
Якщо при обробці запиту треба даних більше ніж є ОЗУ, база почне використовувати своп (спилл) на диски. Запит не впаде (привіт Hana і memSQL), просто буде працювати повільніше.
Exasol автоматично створює і видаляє індекси. Якщо ви робите запит вперше і СУБД вирішує, що з індексом запит буде працювати швидше, то індекс буде створений у процесі відпрацювання запиту. А якщо цей індекс 30 днів нікому не був потрібний, то база сама його видалить.
Ось яка розумна конячка.

Memsql

In-memory СУБД на основі mySQL. Кластерна, присутні аналітичні функції. За замовчуванням зберігає дані по рядках.
Щоб зробити поколоночное зберігання, потрібно при створенні таблиці додати спеціальний індекс:
KEY `keyname` (`fieldaname`) USING CLUSTERED COLUMNSTORE

При цьому rowstore-дані зберігаються в пам'яті завжди, а ось columnstore-дані, у разі нестачі пам'яті, можуть бути автоматично скинуті на диск.
Ключ дистрибуції називається SHARD KEY. Автоматично для кожного поля shard key створюється btree індекс.
Базова версія — повністю безкоштовно, без обмежень щодо обсягу даних і оперативної пам'яті. У платній версії є high availability, онлайн бекапи і ресторы, реплікація між дата-центрами і керування правами користувачів.


Спрощена архітектура Memsql

Impala

Продукт Cloudera, SQL движок, розроблений на С++, входить в екосистему Apache Hadoop. Працює з даними, що зберігаються в HDFS і HBase. В якості сховища метаданих використовує HiveMetastore, що входить до складу СУБД Hive. На відміну від Hive, не використовує MapReduce. Підтримує кешування часто використовуваних блоків даних. Позиціонується як СУБД для обробки аналітичних запитів, що вимагають швидкого відповіді. Вміє працювати з основними BI-інструментами. Повна підтримка ANSI SQL, присутні віконні функції.
Impala доступна у вигляді пакета і парцела в репозиторії Cloudera. При проведенні тестування використовувався дистрибутив Cloudera CDH 5.8.0. Для установки був вибраний мінімальний набір сервісів для роботи Impala: Zookeeper, HDFS, Yarn, Hive. Велика частина налаштувань використовувалася за замовчуванням. Для Impala було виділено сумарно 160 Гб пам'яті з обох серверів. Для контролю утилізації ресурсів серверів контейнерами використовувалися cgroups.

Були виконані всі оптимізації, рекомендовані статье, а саме:
— як формат зберігання таблиць в HDFS був обраний Parquet;
— оптимізовані типи даних, де це можна було зробити;
— попередньо виконувався збір статистики для кожної таблиці (compute stats);
— дані всіх таблиць були записані в кеш HDFS (alter table… set cached in ...);
— оптимізовані join'и (наскільки це було можливо).

На початковому етапі планування тестування і визначення СУБД для участі Impala була відкинута, оскільки ми вже працювали з нею кілька років тому і на той момент вона не виглядала production-ready. Ще раз подивитися в бік «Антилопи» нас переконали колеги по галузі, переконуючи, що за минулий час вона дуже покращала і навчилася грамотно працювати з пам'яттю.
Ще трохи інформації про ImpalaСклад:
Impala Daemon – основний сервіс, який служить для прийому, обробки, координування, розподілу по кластеру та виконання запиту. Підтримує інтерфейси ODBC і JDBC. Також має CLI-інтерфейс і інтерфейс для роботи з Hue (Web UI для аналізу даних в Hadoop). Виконується як демона на кожному з воркеров кластера.

Impala Statestore – використовується для перевірки стану инстансов Impala Daemon, що працюють у кластері. Якщо на якому-небудь воркере виходить з ладу Impala Daemon, то Statestore повідомляє про це инстансы на інших воркерах, щоб запити до пішла в offline инстансу не передавалися. Як правило, працює на майстер-ноде кластера, не є обов'язковим.

Impala Catalog Server – цей сервіс служить для отримання та агрегації метаданих з HiveMetastore, HDFS Namenode, HBase у вигляді структури, підтримуваної Impala Daemon. Також цей сервіс використовується для зберігання метаданих, що використовуються виключно самої Impala, таких, наприклад, як користувальницькі функції. Як правило, працює на майстер-ноде кластера.



Деякі важливі для нас характеристики всіх БД, зібрані в одну таблицю на Google Docs

Для сміливих — ця ж таблиця у форматі Хабра (обережно, редизайн хабра зробив мало-мальськи широкі таблиці спотворені)














  Greenplum  Exasol  Clickhouse Memsql  SAP Hana  Impala  Вендор EMC Exasol  Yandex  Memsql SAP  Apache / Cloudera  Використовувана версия 4.3.8.1 5.0.15 1.1.53988  5.1.0 1.00.121.00.1466466057  2.6.0 Майстер-ноди Майстер-сегмент. Резервується.
Точка входу — будь-яка нода. Є license-нода, резервується. Точка входу — будь-яка нода Точка входу — будь-яка нода Є майстер-нода.  Резервується.  Точка входу — будь-яка нода. Однак, потрібен Hive metastore server. Використовувана ОС RHEL 6.7 EXA OS (проприетарная) Ubuntu 14.04.4 RHEL 6.7 RHEL 6.7 RHEL 6.7 Можливе залізо Будь — Будь-яке з підтримкою PXE Boot Будь — Будь — Лише зі списку SAP Будь — Імпорт з Greenplum External http tablesPipes External http tablesJDBC import External http tables CSV з локального сервераSPARK  CSV з локального сервера External GPHDFS tables Інтеграція з SAP BOджерело для юниверсов) Так, ODBC Так, ODBC Немає даних Немає даних Так Так, ODBCSIMBA Інтеграція з SAS Так, SAS ACCESS  Так, ODBC  Немає даних Немає даних Немає даних Так, SAS ACCESS Віконні функції Є  Немає  Дистрибуція по нодам По полю/полів По полю/полям  По полю/полям  По полю/полям  По полю/полямШарды раскидываютсяпо нодам вручну Рандомно Колоночное зберігання Є  Є  Є  Є на дискеНет в пям'яті Є (Parquet) При нестачі пам'ятіпри виконанні запиту Дані спилятся на диск  Дані спилятся на диск Запит падає  Запит падає  Запит падає  Дані спилятся на диск Відмовостійкість Тобто, механізм дзеркал Тобто, механізм дзеркал Тобто, на рівні таблиць     Є, силами HDFS  Спосіб поширення Open source, APACHE-2 Закритий код, платна Open source, APACHE-2 Закритий код, безкоштовна Закритий код, платна Open source, APACHE-2


Результати тестування

Опис і текст тестових запитів, які використовуються в тестуванні

Для тестування були підібрані запити з d_financial_account_not_additive. d_financial_account_not_additive — це уявлення (view) з даними по кожному рахунку на кожен день. View зроблено на основі трьох таблиць з метою оптимізації місця на диску, і, відповідно, читання з диска. Для тестування була обрана частина даних по першому мільйону рахунків з початку 2015 року. Це трохи більше 522 мільйонів рядків. До not_additive ми приєднуємо дані за рахунками (financial_account) і по заявкам (financial_account_application і application_calling_channel). У Greenplum (для прикладу) для таблиць задані ключі розподілу за сегментами: для рахунків це account_rk, для заявок – financial_application_rk. У запитах join-и між основними таблицями відбуваються з рівності, тому ми можемо очікувати hash join, без nested loop, коли потрібно порівняти порядково велика кількість рядків з різних таблиць.

Загальний обсяг даних склав близько 200 Гб в стислому вигляді (розраховуємо, що весь цей обсяг з невеликим запасом влазить в пам'ять).
Кількість рядків у таблицях:

Таблиця Число рядків
d_financial_account_date 522726636
d_financial_account_data_bal 229255701
financial_account_application 52118559
application_calling_channel 28158924
d_financial_account_data_scd 3494716
financial_account 2930425
currency_rates 3948
dds_calendar_date 731
loyalty_program_types 35
financial_institution 5
Запит N1
SELECT
date_trunc('year', d_financial_account_not_additive.business_dt) || '-' || date_trunc('month',d_financial_account_not_additive.business_dt) as yymm,
d_financial_account_not_additive.business_dt,
financial_account.financial_account_subtype_cd,
case when d_financial_account_not_additive.ofn_balance_amt <0 then 1 0 else end,
loyalty_program_by_day.loyalty_program_type_nm,
financial_account.currency_cd,
sum(d_financial_account_not_additive.balance_amt*Table__14.rate),
sum(d_financial_account_not_additive.balance_amt)
FROM
prod_emart.loyalty_program_types loyalty_program_by_day RIGHT OUTER JOIN prod_emart.d_financial_account_not_additive d_financial_account_not_additive ON (d_financial_account_not_additive.loyalty_program_type_rk=loyalty_program_by_day.loyalty_program_type_rk
AND loyalty_program_by_day.valid_to_dttm > now())
INNER JOIN prod_emart.financial_account financial_account ON (financial_account.account_rk=d_financial_account_not_additive.account_rk)
INNER JOIN (
SELECT
r.currency_from_cd,
r.currency_to_cd,
r.rate,
r.rate_dt
FROM
prod_emart.currency_rates r
WHERE
( r.currency_to_cd='RUR' )
union all
SELECT
'RUB',
'RUR',
1,
d.calendar_dt
FROM
prod_emart.dds_calendar_date d
) Table__14 ON (Table__14.currency_from_cd=financial_account.currency_cd)

WHERE
( Table__14.rate_dt=d_financial_account_not_additive.business_dt
)
AND
(

d_financial_account_not_additive.business_dt >= to_date(( 2016 - 2)::character varying||'-01-01', 'YYYY-MM-DD')
AND

d_financial_account_not_additive.business_dt <= (current_date-1)
AND
financial_account.financial_account_subtype_cd IN ( 'DEP','SAV','SVN','НОГА','CUR' )
)
GROUP BY
date_trunc('year', d_financial_account_not_additive.business_dt) || '-' || date_trunc('month',d_financial_account_not_additive.business_dt),
d_financial_account_not_additive.business_dt,
financial_account.financial_account_subtype_cd,
case when d_financial_account_not_additive.ofn_balance_amt <0 then 1 0 else end,
loyalty_program_by_day.loyalty_program_type_nm,
financial_account.currency_cd

Запит N2
select count(*) from (SELECT
date_trunc('year', d_financial_account_not_additive.business_dt) || '-' || date_trunc('month',d_financial_account_not_additive.business_dt) as yymm,
d_financial_account_not_additive.business_dt,
financial_account.financial_account_subtype_cd,
loyalty_program_by_day.loyalty_program_type_nm,
application_calling_channel.appl_channel,
case when ( financial_account_application.application_product_type )='010222' then 'Y' else 'N' end ,
case when ( financial_account_application.application_product_type )='020202' then 'Y' else 'N' end,
case when financial_account.parent_account_rk is null then 'N' else 'Y' end,
prod_emart.financial_institution.financial_institution_nm,
sum(d_financial_account_not_additive.principal_balance_amt),
sum(d_financial_account_not_additive.interest_balance_amt),
sum(d_financial_account_not_additive.f2g_balance_amt),
sum(d_financial_account_not_additive.f2n_balance_amt),
sum(d_financial_account_not_additive.overdue_fee_balance_amt),
sum(d_financial_account_not_additive.pastdue_gvt_balance_amt),
sum(d_financial_account_not_additive.annual_fee_balance_amt),
sum(d_financial_account_not_additive.sim_kke_balance_amt)
FROM
prod_emart.loyalty_program_types loyalty_program_by_day RIGHT OUTER JOIN prod_emart.d_financial_account_not_additive d_financial_account_not_additive ON (d_financial_account_not_additive.loyalty_program_type_rk=loyalty_program_by_day.loyalty_program_type_rk
AND loyalty_program_by_day.valid_to_dttm > now())
INNER JOIN prod_emart.financial_account financial_account ON (financial_account.account_rk=d_financial_account_not_additive.account_rk)
LEFT OUTER JOIN prod_emart.financial_account_application ON financial_account.financial_application_rk=financial_account_application.financial_application_rk
LEFT OUTER JOIN prod_emart.application_calling_channel on financial_account.financial_application_rk=application_calling_channel.financial_application_rk
LEFT OUTER JOIN prod_emart.financial_account parent_account ON (financial_account.parent_account_rk=parent_account.account_rk)
LEFT OUTER JOIN prod_emart.financial_institution ON (financial_account.financial_institution=financial_institution.financial_institution)

WHERE
(
d_financial_account_not_additive.business_dt >= to_date('2014-01-01', 'YYYY-MM-DD')
AND
d_financial_account_not_additive.business_dt <= (current_date-1)
AND
(
financial_account.financial_account_subtype_cd IN ( 'CCR','INS','CLN','VKR','EIC' )
OR
(
financial_account.financial_account_subtype_cd IN ( 'PHX' )
AND
(
parent_account.financial_account_subtype_cd Is Null
OR
parent_account.financial_account_subtype_cd NOT IN ( 'IFS' )
)
)
)
)
GROUP BY
date_trunc('year', d_financial_account_not_additive.business_dt) || '-' || date_trunc('month',d_financial_account_not_additive.business_dt),
d_financial_account_not_additive.business_dt,
financial_account.financial_account_subtype_cd,
loyalty_program_by_day.loyalty_program_type_nm,
application_calling_channel.appl_channel,
case when ( financial_account_application.application_product_type )='010222' then 'Y' else 'N' end ,
case when ( financial_account_application.application_product_type )='020202' then 'Y' else 'N' end,
case when financial_account.parent_account_rk is null then 'N' else 'Y' end,
financial_institution.financial_institution_nm) sq

Запит N3
SELECT
date_trunc('year', d_financial_account_not_additive.business_dt) || '-' || date_trunc('month',d_financial_account_not_additive.business_dt) as yymm,
d_financial_account_not_additive.business_dt,
financial_account.financial_account_subtype_cd,
case when ( prod_emart.financial_account_application.application_product_type )='010222' then 'Y' else 'N' end ,
d_financial_account_not_additive.risk_status_cd,
case when financial_account.utilization_dt<=d_financial_account_not_additive.business_dt 1 then else 0 end,
case when ( d_financial_account_not_additive.current_limit_amt) > 0 then 1 0 else end,
prod_emart.financial_institution.financial_institution_nm,
--sum(d_financial_account_not_additive.credit_balance_amt),
sum(d_financial_account_not_additive.principal_balance_amt),
sum(d_financial_account_not_additive.current_limit_amt),
count(d_financial_account_not_additive.account_rk),
sum(case when d_financial_account_not_additive.current_limit_amt > 0 then d_financial_account_not_additive.principal_balance_amt / d_financial_account_not_additive.current_limit_amt end)
FROM
prod_emart.d_financial_account_not_additive
INNER JOIN prod_emart.financial_account financial_account ON (financial_account.account_rk=d_financial_account_not_additive.account_rk)
LEFT OUTER JOIN prod_emart.financial_account_application on financial_account.financial_application_rk=prod_emart.financial_account_application.financial_application_rk
LEFT OUTER JOIN prod_emart.financial_institution ON (financial_account.financial_institution=prod_emart.financial_institution.financial_institution)
WHERE
(
d_financial_account_not_additive.business_dt >= to_date(( 2016 - 2)::character varying||'-01-01', 'YYYY-MM-DD')
AND
d_financial_account_not_additive.business_dt <= (current_date-1)
AND
financial_account.financial_account_subtype_cd IN ( 'CCR','CLN','VKR','INS','EIC' )
AND
case when financial_account.close_dt<=d_financial_account_not_additive.business_dt 1 then else 0 end
IN ( 0 )
)
GROUP BY
date_trunc('year', d_financial_account_not_additive.business_dt) || '-' || date_trunc('month',d_financial_account_not_additive.business_dt),
d_financial_account_not_additive.business_dt,
financial_account.financial_account_subtype_cd,
case when ( prod_emart.financial_account_application.application_product_type )='010222' then 'Y' else 'N' end ,
d_financial_account_not_additive.risk_status_cd,
case when financial_account.utilization_dt<=d_financial_account_not_additive.business_dt 1 then else 0 end,
case when ( d_financial_account_not_additive.current_limit_amt) > 0 then 1 0 else end,
prod_emart.financial_institution.financial_institution_nm

Запит T1
SELECT count(*)
FROM
(
SELECT *
FROM prod_emart.d_financial_account_data_bal
) ALL INNER JOIN
(
SELECT *
FROM prod_emart.d_financial_account_date
) USING account_rk, valid_from_dt


Запит T2
SELECT
count(*)
FROM prod_emart.d_financial_account_data_bal a JOIN prod_emart.d_financial_account_date b
ON a.account_rk = b.account_rk AND a.valid_from_dt = b.valid_from_dt
LEFT JOIN prod_emart.d_financial_account_data_scd sc
ON a.account_rk = sc.account_rk AND b.scd_valid_from_dt = sc.scd_valid_from_dt;


D1 (Декартів добуток однієї колонки)
-- В таблиці - 291 157 926 408 рядків
select count(*)
from
(SELECT * FROM WRK.D_FINANCIAL_ACCOUNT_DATE) t1 INNER JOIN (SELECT * FROM WRK.D_FINANCIAL_ACCOUNT_DATE) t2
on t1.account_rk = t2.account_rk;

D2 (Декартів добуток кількох колонок)
select count(*)
,sum(t1.last_day_of_month_flg - t2.last_day_of_month_flg) as sum_flg
,sum(t1.business_dt - t2.valid_from_dt) as b1v2
,sum(t1.valid_from_dt - coalesce(t2.scd_valid_from_dt,current_date)) as v1s2
,sum(coalesce(t1.scd_valid_from_dt,current_date) - t1.business_dt) as s1b2
from
prod_emart.D_FINANCIAL_ACCOUNT_DATE t1 INNER JOIN prod_emart.D_FINANCIAL_ACCOUNT_DATE t2
on t1.account_rk = t2.account_rk;


Результати вказані в секундах виконання запиту.









Запит 
Greenplum 
Exasol 
Clickhouse
Memsql 
SAP Hana 
Impala 
N1
14
< 1
108
6
78
N2
131
11
  — 127
Error
N3
67
85

  — 122
733
T1
14
1.8
64
70
20
100
T2
17
4.2
86
105
20
127
D1
1393
284 
45
1500
D2
 > 7200
1200
 > 7200
Error 



Виявлені нюанси в роботі БД

Yandex Clickhouse
В процесі тестування з'ясувалося, що ця БД для наших завдань не підходить — джойны в ній представлені тільки номінально. Так, наприклад:
  • підтримується тільки JOIN з підзапит в якості правої частини;
  • умови в join-е не пробрасываются всередину подзапроса;
  • розподілені join-и виконуються неефективно.
Виявилося практично неможливим переписати «важкі» запити (N1-N3) на синтаксис Clickhouse. Також засмучує обмеження по пам'яті — результат будь-якого підпорядкованого в будь-який запит повинен вміщуватися в пам'ять на одному (!) сервері з кластера.
Незважаючи на те, що для BI-завдань ця БД виявилася непридатна, за результатами тестування вона знайшла застосування в сховищі в іншому проекті.
Окремо хочеться відзначити дуже докладну і зручну документацію, доступну на офіційному сайті (на жаль, поки вона не покриває всі аспекти використання БД), а також подякувати розробників Yandex за оперативні відповіді на наші питання при проведенні тестування.

SAP HANA
Основну частину з налаштування серверів та оптимізації запитів зробили колеги з однієї консалтингових компаній, які є представниками SAP в Росії. Без них подивитися на базу і оцінити її переваги ми б не змогли: як показала практика, для роботи з HANA потрібна наявність досвіду.

Дуже цікаво показала себе HANA при підрахунку кількості рядків join-а таблиці самій на себе:


EXPLAIN запиту з join-му таблиці самій з собою в HANA

Оптимізатор обчислює результат з допомогою статистики навіть не виконуючи join. Непоганий трюк, але якщо where дописати умова, яке завжди True, наприклад «1 = 1», то трюк не спрацює і ми отримаємо 25 хвилин, майже стільки ж, скільки й у Greenplum.
На поточний момент HANA при виконанні запиту не вміє розміщувати проміжні результати запитів на диску. Тому якщо пам'яті не вистачає, сесія обривається, і користувач залишається без результатів.

Як з'ясувалося в процесі тестування, навіть за умови, що дані двох таблиць, які беруть участь у join-е, розподілені по нодам кластера правильно, join за фактом виконується тільки на одній ноде кластера. Перед виконанням запиту дані з усього кластера просто переливаються на одну ноду і вже там відбувається обрахування join-а. За відведений під час тестування перемогти таку логіку і змусити join виконуватися локально не змогли.
До речі, виробник рекомендує використовувати однонодовую конфігурацію БД, що і підтверджують результати нашого тестування – змусити конфігурацію з двох машин оптимально працювати в рази складніше, ніж з одним.

Exasol
Головне враження від роботи з базою — працює дуже швидко і напрочуд стабільно, прямо ось так, з коробки. Практично у всіх наших тестах видно перевагу в швидкості в порівнянні з іншими СУБД. Однак, на відміну від багатьох інших СУБД, це чорний ящик — у вас немає можливості навіть підключитися до ноде по ssh і запустити iotop, htop і так далі.
Відсутність контролю своїх серверів, безумовно, змушує напружитися. Хоча справедливості заради треба відзначити, що всі потрібні дані по роботі бази і навантаження на залізо є в системних view всередині бази.
Є JDBC,ODBC, драйвери, відмінна підтримка ANSI SQL і деяких специфічних особливостей Oracle (select 1 from dual, як приклад). Ще в БД вже вбудовані JDBC-драйвери для підключення до зовнішніх баз даних (Oracle, PostgreSQL,MySQL та інших), що дуже зручно для завантаження даних.
EXASOL дозволяє подивитися план тільки виконаних запитів. Це пов'язано з тим, що план створюється на ходу під час виконання за рахунок аналізу проміжних результатів. Відсутність класичного explain роботі не заважає, але з ним звичніше.
Швидка, зручна, стабільна, не вимагає довгого тюнінгу — поставив і забув. Загалом, якась вся правильна. Але чорний ящик, що насторожує.
Окремо хочеться відзначити адекватність підтримки, яка оперативно відповідала на абсолютно всі наші питання, що виникли.

Memsql
Ставиться просто і швидко. Адмінка гарна, але не дуже розумна. Приклади: можна додавати ноди в кластер, але видаляти з адмінки не можна/складно/неочевидно; можна бачити поточні та завершені запити, але ніяких подробиць про них побачити не можна.


В адмінці memsql можна твітити кількість записів в секунду

При роботі MemSQL дуже любить навантажити процесори, помилок з переповненням пам'яті майже не було.
MemSQL перед тим, як з'єднати дві таблиці, робить repartitioning (перерозподіл даних по нодам) по ключу join-а.
В нашому випадку ми можемо таблицю data_bal та date зберігати зі складним shard key (account_rk, valid_from_dt), для scd-таблиці ключ шардирования буде (account_rk, scd_valid_from_dt). З'єднанням між data_bal та date в такому випадку буде відбуватися швидко, далі при виконанні запиту дані будуть перерозподілені за account_rk і scd_valid_from_dt, і на наступному кроці вже за account_rk для з'єднання з таблицею financial_account. Як запевняє підтримка, repartitioning – це дуже витратна за часом операція.

Таким чином, наші запити виявилися важкуватими для бази з-за великої кількості різнопланових join-ів. У Greenplum join-и між перерахованими таблицями відбуваються локально, і, відповідно, швидше, без перерозподілу по вузлах, так званого Redistribute Motion.
В цілому, MemSQL бачиться відмінною СУБД для міграції з MySQL і не найскладнішою аналітики.

Impala
Установка кластера Cloudera, до складу якого і входить Impala, досить проста і добре задокументована.
Однак, варто зазначити, що швидкістю роботи Impala щодо інших БД не відзначилася — наприклад, запит, який підраховував count(*) в d_financial_account_not_additive, працював в Impala 3,5 хвилини, що значно більше, ніж у суперників, у яких результати — це десятки секунд і менше.
Також ми провели цікавий експеримент: як було написано раніше, під view d_financial_account_not_additive є два join-а. В кожному з них відбувається з'єднання з account_rk з типом даних integer, а також з полів із типом даних дата. В Impala немає даних типу date, тому ми використовували timestamp. Заради інтересу була зроблена заміна timestamp на bigint, у якому зберігався unix timestamp. Результат запиту відразу покращився на хвилину. На наступному кроці дані з account_rk і поля з датою, це valid_from_dt і scd_valid_from_dt, були об'єднані, щоб забезпечити join тільки по одному полю. Це було зроблено нехитрим способом:
account_valid_from = account_rk * 100000 + cast(unix_timestamp(valid_from_dt)/86400 as int)

Join по одному полю дав нам ще близько півхвилини виграшу, але в будь-якому разі це в кілька разів більше ніж в інших СУБД.
Основні запити працювали в кілька разів довше. Запит N2 падав із-за браку пам'яті, тому у нього немає результатів.
На поточний момент в Impala відсутній розподіл даних по вузлах з hash-розподілом, тому для використовуваних запитів навряд чи ми б отримали гарний час виконання.

Замість висновку

Ми свідомо не хочемо робити висновок з результатів тестування виду «база А показала себе добре в тому-то і те-то, а база Б — вміє твітів з адмінки у тому-то і те-то, А тому краще Б», надавши це читачам. Сподіваємося, цей текст допоможе комусь відкрити для себе нові продукти на ринку СУБД для ETL і BI, а комусь- прийняти остаточне рішення у виборі.

Експеримент вважаємо таким, що відбувся, на основі його результатів, враховуючи всі безліч критеріїв, в даний момент вибираємо СУБД для використання в BI-проекті.

Стаття публікується від імені чотирьох осіб, які проводили тестування:
Максим Білоусов
Дмитро Нємчин (aka 4etvegr)
Георгій Безруких
Дмитро Павлов (aka kapustor)

Ілюстрації, так чудово описали досліджувані БД, взяті з концепту Virtua Hamster (так і не вийшла гра для Sega 32).
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.