Agregace v MongoDB, Oracle, Redshift, BigQuery, VoltDB, Vertica, Elasticsearch, GoodData, Postgres a MySQL

"Manažerský shrnutí"

Celý se mi to nakonec asi vymklo z rukou. Snažím se tu dát dohromady popis toho, jak jednu a tu samou úlohu udělat na různých systémech. Na konci jsem to samé udělal s GoodData (linkuju sem z toho 2 screencasty), což je víc high-level nástroj než databáze - přesto je na tom skvěle vidět, jak se s ní dobře pracuje a jak je rychlá. 

Tady je souhrnná tabulka:

Intro

Koncem loňského roku jsem našel blogpost "MongoDB 'Lightning Fast Aggregation' Challenged with Oracle”, kde Lukas Eder provádí na Oracle stejné agregace, jako o týden před ním Vlad Mihalcea dělal s MongoDB.

Lukas Edler mi dal zdrojová data, která mají 50.000.000 řádků eventů s časem a hodnotou

created_on,value
2012-05-02T06:08:47Z,0.9270193106494844
2012-09-06T22:40:25Z,0.005334891844540834
2012-06-15T05:58:22Z,0.05611344985663891
2012-01-05T20:47:19Z,0.2171613881364465
2012-02-10T00:35:17Z,0.4581454689614475
2012-06-19T17:46:41Z,0.9841521594207734
2012-08-20T21:42:19Z,0.3296361684333533
2012-02-24T20:29:17Z,0.9760254160501063

Tady je 10.000 řádků na “nažhavení” a celý dataset - obojí s hlavičkou, bez enclosures, delimiter je čárka (ke stažení z mé S3 to bude do půlky září, pak to spadne do Glacieru):

Nad daty se provádí 2 testy:

  • Test A - dělá agregaci po letech a dnech v roce, přičemž počítá počet záznamů každý den a jejich průměrnou, minimální a maximální hodnotu
  • Test B - dělá to samé, ale filtrované na konkrétní hodinu

Zkusil jsem to samé (nebo co nejvíc podobné) provést na dalších databázích - Amazon Redshiftu, Google BigQuery, Volt DB, HP Vertica, Elasticsearch, GoodData, Postgres, MySQL. Nejde mi o to mlátit se po hlavě, kdo je rychlejší, proto moc neřeším, jaké byly HW konfigurace; navíc Google BigQuery je “unknown hw”. Dívám se víc po složitosti, s jakou toho dosáhnout. Na Redshiftu jsem ještě zkoušel 10x to samé - tedy 500.000.000 řádků, které jsou ale 10x opakování zdrojového datasetu. V případě GoodData (na konci) jsem to ještě na závěr zkomplikoval, aby bylo vidět, jak snadné je tam věci řešit.

Tady jsou výsledky: 

MongoDB

(detaily přípravy viz Vladův blogpost)

Test A: 129s
Test B: 0.2s

Oracle

(detaily přípravy viz Lukasuv blogpost)

Test A: 32s
Test B: 20s první spuštění, 0.5s druhé spuštění

Redshift

Data jsem do Redshiftu nahrával z S3. Celé to obnášelo udělat před samotným testem tyhle kroky:
  1. vyrobit tabulku
  2. naimportovat do ní data
  3. nešlo mi tehdá při importu rozpoznat formát času ISO8601, tak jsem tabulku musel alterovat
    1. přidat sloupec pro timestamp
    2. nastavit jej podle timestampu v zdrojových datech
    3. smazat původní sloupc s datumy
  4. přidal jsem si tam SortKey, udělal analyze a vacuum

(tady je soupis přesných SQL příkazů a časů: https://s3.amazonaws.com/padak-share/blog/redshift.sql)

Test A 

Použitý dotaz:
SELECT
     EXTRACT(YEAR FROM created_at),
     EXTRACT(dayofyear FROM created_at),
     COUNT(*),
     AVG(value),
     MIN(value),
     MAX(value)
FROM RandomData
GROUP BY
     EXTRACT(YEAR FROM created_at),
     EXTRACT(dayofyear FROM created_at)
ORDER BY
     EXTRACT(YEAR FROM created_at),
     EXTRACT(dayofyear FROM created_at);

Redshift dw1.xlarge (15s)
Redshift dw2.large (7s)

Verze s 500.000.000 řádků:

Redshift dw1.xlarge (182s)
Redshift dw2.large (53s)

Output


Test B

Použitý dotaz:

SELECT
     EXTRACT(YEAR FROM created_at),
     EXTRACT(DAYOFYEAR FROM created_at),
     EXTRACT(HOUR FROM created_at),
     COUNT(*),
     AVG(value),
     MIN(value),
     MAX(value)
FROM RandomData
WHERE
     created_at BETWEEN
     TIMESTAMP '2012-07-16 00:00:00'
     AND
     TIMESTAMP '2012-07-16 01:00:00'
GROUP BY
     EXTRACT(YEAR FROM created_at),
     EXTRACT(dayofyear FROM created_at),
     EXTRACT(HOUR FROM created_at)
ORDER BY
     EXTRACT(YEAR FROM created_at),
     EXTRACT(dayofyear FROM created_at),
     EXTRACT(HOUR FROM created_at);

Redshift dw1.xlarge (1.3s)(first run)
Redshift dw1.xlarge (0.27s)(second run)

Verze s 500.000.000 řádků:

Redshift dw1.xlarge (46s)(first run)
Redshift dw1.xlarge (0.61s)(second run)

Output:


Pro fajnšmekry ještě query plán:

Google BigQuery:


Je služba od Google, kterou trochu popisuju tady: http://padak.keboola.com/google-bigquery-workshop-s-felipe-hoffa

Komunikuje se s ní pouze přes REST API nebo webový interface. Já jsem použil klienta v konzoli na serveru. Sample data jsem musel stáhnout na disk.

Celé to obnášelo udělat před samotným testem tyhle kroky:
  1. Založit projekt v https://console.developers.google.com/ a pod API&auth povolit BigQuery (a mít tam platební kartu)
  2. založit “projekt” v BigQuery
  3. naimportovat do něj data (samotné nahrání dat trvalo docela dlouho, přesný čas jsem si omylem nezaznamenal)
U BigQuery se nedá nic moc “ladit”. Nejsou tam žádné indexy, klíče, apod.

Založení projektu z konzole:
./bq mk rad.randomData
--0s

Import dat:
./bq load --noallow_quoted_newlines --max_bad_records 500 --skip_leading_rows=1 rad.randomData ./randomData.csv created_on:timestamp,value
-- ~1500s (čas jsem bohužel přesně nezaznamenal)

Test A


Použitý dotaz:
SELECT
  YEAR(created_on) AS Year,
  DAYOFYEAR(created_on) AS DayOfYear,
  COUNT(*) AS Count,
  AVG(value) AS Avg,
  MIN(value) AS Min,
  MAX(value) AS Max
FROM [rad.randomData]
GROUP BY
  Year, DayOfYear
ORDER BY
  Year, DayOfYear;

-- 7s (1.3GB)

Test B


Použitý dotaz:

SELECT
  YEAR(created_on) AS Year,
  DAYOFYEAR(created_on) AS DayOfYear,
  HOUR(created_on) AS Hour,
  COUNT(*) AS Count,
  AVG(value) AS Avg,
  MIN(value) AS Min,
  MAX(value) AS Max
FROM [rad.randomData]
WHERE created_on >= '2012-07-16 00:00:00' AND created_on <= '2012-07-16 01:00:00'
GROUP BY
  Year, DayOfYear, Hour
ORDER BY
  Year, DayOfYear, Hour;

-- 2.9s / 1.6s (cached)

VoltDB


Tuhle databázi jsem našel nějak náhodou. Pyšní se spoustou věcí, ale nakonec jsem narazil na to, že tam vůbec nejde z timestampu extrahovat třeba den v roce a je potřeba tam udělat pre-precessing a data upravit. Celá moje anabáze skočila na supportu, kde mi začal pomáhat VoltDB Solution Engineer jménem Dheeraj Remella, který tvrdil, že ten test provede. Emailování se nám trochu táhlo, takže mezitím stihli vydat verzi 4.0, kde už EXTRACT() funkci měli. Jeho výsledky jsou následující:

Import dat:

Read 50000001 rows from file and successfully inserted 50.000.000 rows (final)
Elapsed time: 1735.586 seconds

Test A:

SELECT
     EXTRACT(YEAR FROM create_on_ts) AS Year,
     EXTRACT(DAY_OF_YEAR FROM create_on_ts) AS DayOfYear,
     COUNT(*) as groupCount,
     SUM(value) as totalValue,
     MIN(value) as minimumValue,
     MAX(value) as maximumValue
FROM RandomData
GROUP BY
     EXTRACT(YEAR FROM create_on_ts),
     EXTRACT(DAY_OF_YEAR FROM create_on_ts);

-- 70ms

Test B:

-- 330 ms

Časy jsou to super! Zjišťuju, jestli si předpočítal Rok, Den, Hodinu...

UPDATE: Tak ano, ve testovací DB měl pro daný dotaz předem dopočítané odvozené hodnoty roků, dnů, apod. Tady je jeho DDL: https://s3.amazonaws.com/padak-share/blog/voltdb-ddl.sql

Použitý HW byl MacBook Pro (Intel i5 2.5 GHz processor - 2 cores, Memory 16 GB).

VoltDB vypadá zajímavě a podezřele zároveň. Databáze se tam zakládají voláním klienta v konzoli, který spouští nějaké věci v javě a nasává SQL:
voltdb compile -o random.jar random.sql
voltdb create catalog random.jar
csvloader randomdata -f randomData10.csv --skip 1
... řekl bych, že mi to moc nesedlo, nicméně to budu dál sledovat. Mohla by se z toho vyklubat docela super věc!

HP Vertica 


Tenhle test pro mě udělal Honza Císař, protože to bylo v době, kdy instalátor neuměl ani zalogovat vystřílený file descriptory a nainstalovat ji bylo dost peklo (dneska už to neplatí a instalátor je celkem fajn)

Příprava:

SET TIMEZONE 'UTC';
CREATE TABLE RandomData_T (
     created_on TIMESTAMP,
     value DECIMAL(22,20)
);

COPY RandomData_T from '/tmp/randomData.csv' delimiter ',' null as '' enclosed by '"' exceptions '/tmp/load.err';
Time: First fetch (1 row): 121.609s. All rows formatted: 121.609s

#předžhavuje :)
SELECT * FROM RandomData_T LIMIT 10;

Test A


SELECT
     EXTRACT(YEAR FROM created_on),
     EXTRACT(doy FROM created_on),
     COUNT(*),
     AVG(value),
     MIN(value),
     MAX(value)
FROM RandomData_T
GROUP BY
     EXTRACT(YEAR FROM created_on),
     EXTRACT(doy FROM created_on)
ORDER BY
     EXTRACT(YEAR FROM created_on),
     EXTRACT(doy FROM created_on);

--  Time: First fetch (366 rows): 2,068s

Test B


SELECT
     EXTRACT(YEAR FROM created_on),
     EXTRACT(doy FROM created_on),
     EXTRACT(HOUR FROM created_on),
     COUNT(*),
     AVG(value),
     MIN(value),
     MAX(value)
FROM RandomData_T
WHERE
     created_on BETWEEN
     TIMESTAMP '2012-07-16 00:00:00'
     AND
     TIMESTAMP '2012-07-16 01:00:00'
GROUP BY
     EXTRACT(YEAR FROM created_on),
     EXTRACT(doy FROM created_on),
     EXTRACT(HOUR FROM created_on)
ORDER BY
     EXTRACT(YEAR FROM created_on),
     EXTRACT(doy FROM created_on),
     EXTRACT(HOUR FROM created_on);

--   Time: First fetch (2 rows): 0,026s

Honza na to použil AWS instanci typ 4xlarge, takže asi 30GB ram, SSD disky a 4x Intel Xeon E5-2680

Elasticsearch


Dal jsem na Nový rok na tyhle testy teaser na facebook a ozval se mi Karel Minařík, že by to vyšvihl v Elasticsearchi. Nadšeně jsem souhlasil a tady je výsledek. Managerské summary je, že je to pekelně rychlé, ale poměrně složitě a dlouho to data importuje. Pro mě osobně ovšem nedosažitelné díky množství kódu v Ruby. Tady je jak to Karmi popsal:

Čau,
tak jsem se na to mrknul (http://goo.gl/A3j1Up): 
Je tam hezký detail, že se nedají použít klasické "date" agregace, protože jestli jsem to dobře pochopil tak v tom SQL seskupuješ podle dne v roce *bez ohledu na rok*, a to samé pro hodiny atd. Nejprve jsem to udělal zkusmo přes `script` ve facetu, ale to bylo dooooost pomalé -- nepřekvapivě, musí evaluovat script pro každý dokument z těch 50M. S filtrem to bylo i tak pod sekundu. Takže jsem tu manipulaci udělal při indexaci, tzn. v Ruby rovnou parsovat datum a ukládat `day_of_year` atd, což logicky zpomalilo indexaci, ale rychlost je teď po zahřátí:
  • Agregace: 16398ms
  • Agregace + filtr: 10ms

Tím se mi potvrdilo že ES je na tyhle věci pekelně, pekelně rychlý.

Nějak extra jsem to netunil -- je to EC2 xlarge stroj, ES má 7GB paměti, index má 1 shard, disk to používá ephemeral, tzn. relativně rychlý, ale spinning crap, ES je kromě paměti v továrním nastavení.

Celkové výstupy v příloze, nevím jestli jsem ty query převedl správně, neměl jsem na to moc klidu.

Vcelku zajímavé a hezké cvičení :) 

Měj se!,

Karel 


GoodData


Na závěr jsem si nechal test GoodData. Určitě nebude útočit na milisekundové mety, ale suveréně zničí všechno ostatní v jednoduchosti jakou toho dosáhnete.  

Data z S3 jsem nahrál do Keboola Connection (což je složitost asi jako připojit přílohu do emailu). V Keboola Connection jsem řekl, jak se to má do GoodData nahrát. Samotná příprava GoodData projektu je jednoduchá - označím první sloupec jako datum (s časem) a druhý že je číslo.


Po zmáčknutí tlačítka “Upload Table” Keboola Connection připraví vše ostatní sama: na straně GoodData vyrobí fyzický i logický datový model, zparsuje a exportuje data do formátu který GoodData spolehlivě načte. Pro fajnšmekry přidávám odkaz na log komunikace s jejich API. Klient má tohle schované za jedno tlačítko, případně za jeden API call a nemusí se tím vůbec zabývat.

GoodData si pak data přebere, různě je dál podle náma dodaného manifestu překroutí a naimportuje do BI projektu. Lidem říkám, ať nad tím dál přemýšlí jako nad kompaktní tabulkou, kterou vidí u sebe na vstupu, přestože je to vlastně celé roztrhané po sloupečcích, které na sebe různě odkazují a vytváří vztahy, které se zakreslují jako “sněhová vločka”. Víc o tom, co se v GoodData vevnitř děje, jsem popsal v listopadu 2013 tady (GoodData XAE):  Load dat (po zparsování v Keboola Connection má tabulka asi 4GB) trvá asi 60 minut. 

Testy:


Pro první test - agregaci přes celých 50 milionů řádků - je potřeba vyrobit tři metriky:
  • Metrika 1 - počítá počet záznamů [ COUNT(Records of randomData) ]
  • Metrika 2 - počítá průměrnou hodnotu [ AVG(value) ]
  • Metrika 3 - počítá minimální hodnotu [ MIN(value) ]
  • Metrika 4 - počítá maximální hodnotu [ MAX(value) ]
a "podívat se na ně” přes Rok a Den v roce.

Pro druhý test se pouze přidá filter na konkrétní datum a hodinu.

Obojí jsem natočil a dal nesestříhané na youtube - můžete tak vidět, jak je to svižné. Změřit přesně dobu spočítání reportu je možné, ale není to úplně snadně dostupné - v tuhle chvíli to neřeším. Jde mi hlavně ukázat, že je to ve srovnání s jiným přístupem dost snadné.



Ještě mě napadlo to trochu zkomplikovat a ukázat v reportu jak udělat "O kolik % se změnil počet agregovaných záznamů oproti předchozímu dni". Opět nesestříhané video zde: 


GoodData nabízí ke každému reportu něco jako “explain” v DB - tedy "co je pro získání dat potřeba udělat". Nakreslené to vypadá takhle:

Postgres

Honza Winkler udělal Test A a B na Postresu (9.3.4). Detaily jsou tady: http://goo.gl/xt3qZM

  • Import trval: 2 minuty (od oka, neměřeno)
  • Test A (bez indexu, vacuum, ničeho): 33.55 s
  • Test B (bez indexu): 4.5 s
  • Test B (s indexem na created_on, první spuštění): 0.06 s
  • Test B (s indexem na created_on, další spuštění): 0.015 s

MySQL

Dodatečně jsem udělal ještě test v MySQL. Server má 64GB RAM, SSD disky...

Test A

SELECT
     YEAR(`created_on`),
     DAYOFYEAR(`created_on`),
     COUNT(*),
     AVG(value),
     MIN(value),
     MAX(value)
FROM `randomData`
GROUP BY 1,2
ORDER BY 1,2;

-- 46sec

Většina času je na tmp tabulku:

Test B

SELECT
     YEAR(`created_on`),
     DAYOFYEAR(`created_on`),
     HOUR(`created_on`),
     COUNT(*),
     AVG(value),
     MIN(value),
     MAX(value)
FROM `randomData`
WHERE
     `created_on` BETWEEN '2012-07-16 00:00:00' AND '2012-07-16 01:00:00'
GROUP BY 1,2,3
ORDER BY 1,2,3;

-- 0.022sec

Resume k MySQL: je to děs :) Hynek Vychodil to vydřenil na 33sec (http://goo.gl/7qKsP4).

Závěr


Pokud nepočítáme loady dat do databází (které jsou pokaždé o rozličné složitosti), lze říct, že na testovaném objemu dat je agregace pro koncového uživatele vždycky celkem svižná. Pokud umíte SQL a potřebujete pár dotazů, hodí se BigQuery, pokud chcete dělat kvantum dotazů a nechcete si spravovat vlastní DB cluster, hodí se Redshift. Pokud nemají data úplně perfektní strukturu, oceníte Elasticsearch (Karmi mi například říkal, že nějaký němec sype do ES clusteru 1TB dat denně a sviští mu to o 106). Pokud budete chtít dál data zpracovávat a/nebo budou vaše dotazy o fous komplikovanější a bude vhodné, aby je někdo kladl interaktivně, užijete Keboola Connection + GoodData. 

<Ad> 
Klientům přibalujeme kurzy z Keboola Academy, aby uměli psát svoje vlastní jednoduché i složitější metriky, tak jak je vidět třeba tady na videu + jim se vším ochotně pomáháme. Chcete-li si to zkusit, napište mi! 
</Ad>

UPDATE 2014-08-05:

Našel jsem Orzo.js od Tomáše Machálka. Jeho vlastní test je tady.
12 responses
Video je super, celkem me prekvapilo ze jsme s datamartem tak rychli :o). Ten RedShift byl predpokladam ta mensi instance? Jako drobnou vytku zminim ze ti ujela timezona a v GD dochazi k datumovym nesrovnalostem... "Ktere odbornika drazdi, ale vy si jich ani nevsimnete..."
paradni clanek :) Diky
Honza: Ano, vždy šlo o ty nejslabší nody (ten co má 2TB a druhý co má SSD). Co myslíš "ujetá timezona"? Martin: Díky!
After optimizing the MongoDB model and warming-up the cache I managed to get better results than the previous optimized ones: http://vladmihalcea.com/2014/01/17/mongodb-and-... Basically I managed to randomly aggregate the data as follows: Type seconds in a minute minutes in an hour hours in a day T1 0.003s 0.037s 0.855s T2 0.002s 0.037s 0.834s T3 0.001s 0.037s 0.835s T4 0.001s 0.036s 0.84s T4 0.002s 0.036s 0.851s Average 0.0018s 0.0366s 0.843s
Pěkný článek. Akorát závěr bych dal za sebe jiný: tabulku s čísly klidně dál zpracovávejte ve své oblíbené SQL databázi, nosql pro to nenabízí nic podstatného navíc.
PS: A pokud by člověku šlo o maximální rychlost celého kolečka, tak jsem si dost jistý, že specializovaný scriptík by to měl zvládnout do 15 sekund (load i zpracování dohromady) a nebyl by o moc delší než ty SQL(-like) dotazy.
Vlad - thanks for update! It's very fast!
Michal Illich - Souhlas! Nicméně psát na všechno scriptík nedává smysl, proto práve máme SQL :) Ve finále totiž ladíš změnu ve scriptu dýl než 20 sec - což vrací obyčejný SQL zpět na scénu.
Tou TZ jsem myslel ze jsou tam v agregaci data i za predchozi rok, coz je zpusobeno IMHO tim ze to loadujes jako CET, ne UTC - proto mam jako prvni krok u te Verticy nastaveni master TZ. Mimochodem, jaky engine jste pouzili u MySQL? Predpokladam ze InnoDB, jinak mi ten cas na load prijde dost velky, MyISAM bych cekal rychlejsi.
Honza Císař - jo tak, na to v tomhle případě prdím :) Ten load do MySQL obsahuje export z našeho Storage API (rest nad AWS rds) do S3, stáhnutí Transformation API na nějakej worker a pak loadnuti do transformacniho serveru, kterej v tomhle případě je u OVH na východě Kanady. Reálný load do mysql by měl asi proběhnout z tempu toho serveru.
Offtopic: O OVH mi ráno i říkal Mergim (z toho albánského vyhledávače ve StartupYardu) - jak jste s nima spokojení?
ad OVH: máme tam perzistentní servery na transformace, hlavně jako fallback pokud nám selžou SPOT instance v AWS. Jsme s tim spokojený, ale nijak extenzivně to tam nedřeníme. OVH jako firma je peklo, spárovat fakturu s objednávkou, pokud spleteš var.sym. je peklo na týden žhavení telefonů.