"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.
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):
- 10k rows sample (400kB): https://s3.amazonaws.com/padak-share/randomData10.csv
- 50M rows sample (1.9GB): https://s3.amazonaws.com/padak-share/randomData.csv
- 50M gzip verze (770MB): https://s3.amazonaws.com/padak-share/randomData.csv.gz
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.
MongoDB
(detaily přípravy viz Vladův blogpost)
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:- vyrobit tabulku
- naimportovat do ní data
- nešlo mi tehdá při importu rozpoznat formát času ISO8601, tak jsem tabulku musel alterovat
- přidat sloupec pro timestamp
- nastavit jej podle timestampu v zdrojových datech
- smazat původní sloupc s datumy
- 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 (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:
- Založit projekt v https://console.developers.google.com/ a pod API&auth povolit BigQuery (a mít tam platební kartu)
- založit “projekt” v BigQuery
- naimportovat do něj data (samotné nahrání dat trvalo docela dlouho, přesný čas jsem si omylem nezaznamenal)
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 secondsTest 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
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,068sTest 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) ]
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é.
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>
