MySQL vs HP Vertica vs AWS Redshift vs Google BigQuery

by Petr Šimeček

Mám jednu transformaci, ktera v MySQL trvá strašně dlouho (klidně proto, že jsem lopata :). Velmi zjednodušený popis:

Tabulka (106489 řádek, <4MB):


Sloupec "id" určuje nějakou událost, "partner" je dodavatel události (třeba nájemce záboru chodníku) a "start" říká, kdy nastala daná událost. Do tabulky doplňuju sloupec, který říká, kdy měl daný partner předchozí událost (pak se ty 2 datumy od sebe odečtou a zjistím, kolik dní uplnyulo od minulé události - partneři se v datech vyskytují opakovaně). 

Tohle zpracování dělám v MySQL tímhle scriptem s "nested selectem":


Na AWS server cr1.8xlarge (88ECU, 244GB RAM, SSD) to trvá kolem 3000 vteřin (verze s JOINem (viz níže) přes 2 hodiny). Indexy mám správně :) 

Zkusil jsem to samé pustit na HP Vertica, Google BigQuery a AWS Redshift. Na ničem kromě MySQL jsem neladil žádné parametry. Verticu a Redshift jsem do dneška nikdy neviděl, Google BigQuery používám delší dobu.

Vertica:

Nejde mi tam provést nested SELECT, tak jsem to upravil na "verzi s joinem" 


Data jsem tam nahrál takhle:

COPY products FROM '/tmp/products.csv' DELIMITER ',' null AS 'null' EXCEPTIONS '/tmp/products.err';

Bez jakéhokoliv ladění to tam trvá 105 vteřin. To je 3.5% času na MySQL. Cool! Vertica (community edition) mi běží na m1.large serveru, což je největší popelnice, která v tomhle "testu" figuruje. Výsledek super!

Google BigQuery:

V BQ jsem vyrobil projekt R&D a nahrál do něj data přímo z CSV:

bq load --noallow_quoted_newlines --max_bad_records 500 --skip_leading_rows=1 rad.products /tmp/products.csv id,partner,start

Spuštění téhle query zabralo 1695 vteřin (!!) a zprocesovalo 2.63MB. Trochu zklamání, něco musím dělat blbě, nechce se mi tomu věřit. Verzi s nested selectem jsem nezkoušel.

AWS Redshift:

Nikdy jsem si s tím pořádně nehrál, četl jsem pár blogů (airbnb, hapyrus, botify) a dost jsem se na tuhle část těšil. Nahodit to je o stisknutí tlačítka, zvolil jsem režim "socky" a spustil single-node instanci dw.hs1.xlarge. 

Data se tam nahrávají nejlépe z S3...

COPY products (id, partner, start) FROM 's3://padak-share/products.csv' CREDENTIALS 'aws_access_key_id=xyz;aws_secret_access_key=abc' delimiter ',' REMOVEQUOTES DATEFORMAT AS 'YYYY-MM-DD';

Zkusil jsem query s joinem:

CREATE TABLE out AS SELECT p1.start, p1.partner, p1.id, MAX(p2.start) FROM products p1 LEFT JOIN products p2 ON p1.partner = p2.partner WHERE p1.start > p2.start GROUP BY p1.start, p1.partner, p1.id;

i nested selectem:

SELECT start, partner, id, (SELECT MAX(start) FROM products WHERE start < p1.start AND p1.partner = partner) AS prev FROM products p1;

Varianta přes JOIN trvá 167 vteřin a varianta s nested selectem trvá 17,4 vteřiny (!!).

Potřeboval bych to ještě stejně spustit ve Vertice, kde mi nested select vrací "ERROR 4160:  Non-equality correlated subquery expression is not supported", ale nevymyslel jsem kudy na to a víc času jsem tomu nechtěl věnovat. Dokud to nevymyslím, je Redshift největší powa, kterou jsem na tohle našel.


UPDATE 10.8.:

Dostal jsem tip od Kolese, že Vertica umí "INTERPOLATE JOIN". Podle semantiky popsané v dokumentaci to ale myslím nebude fungovat, protože to joinuje předchozí hodnoty pouze v situaci, kdy je z prava výsledek NULL. Já to potřebuju vždycky. Tuším ale, že to tam někde bude :-)

Upvote Upvoted 7
36 responses
Nice. Kterou instanci RedShiftu jsi zkoušel? XL nebo 8XL?
— Jakub Nešetřil
I don't understand why it should take more than few ms. Those databases are doing something really wrong.
— Hynek Vychodil
Jakub: XL
— Petr Šimeček
Hynek: I'd be happy if it take 500ms. If you are willing to help me w/ it, I'll provide you full src data. I'm happy to spend some bucks on it.
— Petr Šimeček
Z Oraclu jsem byl zvyklej na spoustu krasnejch analytickejch funkci, ktery delaj docela rychle a elegantne prave to co se v beznym SQL dela blbe a pomalu... A koukam, ze Vertica je ma taky. Zkousel jsi neco jako tohle? SELECT id, partner, start - LAG(start,1) over (partition by partner order by start) as from_prev_start http://www.vertica-forums.com/viewtopic.php?f=6...
— Michal Hauzírek
Musis dat Verticu taky na XL :o)... Jinak dobra poznamka od Michala s temi analytickymi funkcemi, na praci s time-frames a intervaly je jich tam nekolik a tohle by se dalo zvladnout na jeden pruchod bez joinu. U Redshiftu je super ten load z s3, to je velka uspora...
— Honza Cisar
Tenhle jednoduchý oneliner to udělá za 1,5s na mém starém a dýchavičném notebooku sort -t, -k3 test.data | perl -F',' -ane'chomp;print"$_,",$h{$F[1]}//"NULL\n";$h{$F[1]}=$F[2]' Vygeneroval jsem si test data perl -MPOSIX=strftime -E'$,=",";say $i++, int(rand(10000)), strftime("%Y-%m-%d", gmtime(time-86400*rand(1000))) for 1..106489' To jest 9999 různých partner id s jedním až 25 záznamy. Většinu času zabere IO a parsing. Ty databáze to mají už dávno naparsované v binární podobě, takže je ve výrazné nevýhodě tenhle skript. Trochu nefér využívám, že ty data máš v tak pěkném formátu, ale i tak, když jde o rychlost tak je všechno povoleno. Když tak řekni klukům z GoodData ROLAP týmu ať ti ukážou Event Store, pokud k tomu budou svolní. Jinak bych ti mohl ukázat jak tu samotnou operaci jde udělat pod 5ms, ale tam to stejně zabije problém jak ty data dostat dovnitř a pak zase ven.
— Hynek Vychodil
4MB dat se musi zpracovat pod sekundu. Pokud ne, delas to blbe ;) Jak bych na to sel ja (klidne v mysql, na db imho nezalezi) 1. Potrebujes slozeny index partner,start 2. Select * from products order by partner,start ---- timhle si dostanes ty dve radky co potrebujes k sobe, takze muzes starty rovnou odecist, shoduji-li se partners. Tohle musi byt tak rychle, jak jen to db zvladne cist z pameti, tedy zlomek sekundy.
— Michal illich
Tohle to udělá za 1,1s. LC_ALL=C sort -t, -k2n,2 -k3 test.data | perl -F',' -ane'chomp;print"$_,",$p eq$F[1]?$v:"NULL\n";($p,$v)=@F[1,2]'
— Hynek Vychodil
Aaaaaa. Udělal jsem úplně začátečnickou chybu. Měřil jsem to s výstupem do konsole. Jak nějakej n00b. Takže chtěl jsi to pod 500ms? Cca 350ms na mém dual CPU notebooku (Intel(R) Core(TM) i5 CPU M 520 @ 2.40GHz) LC_ALL=C sort -t, -k2n,2 -k3 test.data | perl -F',' -ane'chomp;print"$_,",$p eq$F[1]?$v:"NULL\n";($p,$v)=@F[1,2]' > result.data Cca 600ms když se to na závěr přeskládá podle Pk (sort -t, -n). Ta první verze to dělala asi za 700ms. Já to říkám pořád, stávající RDBMS sucks. Mimochodem pro desetkrát větší data je to prakticky desetkrát pomalejší. O(NlogN) vs O(N^2) rulez.
— Hynek Vychodil
Hynek> Za predpokladu, ze si pro kazdy dotaz chce clovek tvorit perlovy skript :o). Jinak je to pouha mentalni onanie... Petre, potrebujes Event Store, ten to zvladne bleskove :o))).
— Honza Cisar
Honza> Jistě, napsat jednu řádku v shellu, která má 128 znaků a z toho je 56 znaků perlový skript je mentální onanie. Napsat SQL dotaz který má 200 (join) nebo 135 (nested) mentální onanie není. Kolik zabere předpis pro Event Store jsem celkem zvědavý, nebo jste k tomu už konečně nadefinovali nějaké pořádné API? Mimochodem Event Store bude těžko rychlejší než ten můj skript, protože stále běháte na mém dva roky starém POC v čistém Perlu. Nebo jste to jádro už přepsali do C? Kromě toho vstupní a výstupní kód je docela složitější než to co dělá uvedený skript.
— Hynek Vychodil
Honza> Event Store nebude rozhodně nebude rychlejší. Má pomalejší IO, potřebuje data mít už seřazena podle času, nebo to musí udělat sám uvnitř. Uvnitř to funguje tak jako ten první skript používající hash, tedy pomalejší. No a výstupní IO bude taky pomalejší. Věř mi, já jsem ho vymyslel a napsal, jak samotné úložiště, tak i všechny low level algoritmy. Jediné jak může být rychlejší je, že to budete nejen plnit inkrementálně, ale i tahat ven inkrementálně. Pokud budete tahat ven full, budete tak +- nastejno. Tomu pak já teda říkám mentální onanie.
— Hynek Vychodil
Michal illich> "2. Select * from products order by partner,start ---- timhle si dostanes ty dve radky co potrebujes k sobe, takze muzes starty rovnou odecist, shoduji-li se partners." Té první části rozumím, to je přesně to co dělá sort -t, -k2n,2 -k3, ale co s tím v relační databázi udělám potom? Jak to dostanu na jeden řádek? To je peklo relační algebry, nezná pojem dva sousední řádky. Pokud bych byl pedant, tak relační algebra nezná ani pojem seřadit, relace (tabulka) je z definice neuspořádaná. Proto je to takový problém, že i Vertica to dělá 300x a AWS Redshift 50x pomaleji než jednoduchý Perl skript.
— Hynek Vychodil
Hynku, na stejnou radku si to date cimkoliv co umi if a odcitani a prirazeni. Python, php, perl, c, ruby nebo asi 2000 dalsich moznych jazyku ;) Asi to jde i v sql, ale to neumim natolik dobre, abych poradil, pouze v komentari nahore vidim jakesi lag() ktere to nejspis dela (ale nevim, v kolika narecich sql je to podporovane)
— Michal illich
Dobry den, poslal jsem odkaz na tento clanek kolegum v zahranici a pry mate zkusit toto: SELECT p1.start , p1.partner , p1.id , max(p2.start) as prev FROM products p1 JOIN products p2 ON p1.partner = p2.partner AND p2.start
— Jindrich kasal
Michal illich> Aha, a jak ty data dostanu do té databáze, pak do toho nějakého jazyku a pak ... ? Jen tak mimochodem mluvíme o 100k řádků. Například do MySQL to dostanu nejrychleji za 0.5s. Ano je to 200k řádků za sekundu. Teď jsem si to ověřil, že na tomhle se od dob před deseti lety, kdy jsme to v InsightStrategy měřili, vůbec nic nezměnilo. Takže to máme 0.5s dovnitř + sort + 0.5s ven + zpracování v nějakém jazyce. Nojo, ale já mám za 350ms hotovo. Asi jsem něco zcela zásadního minul. Jen podotýkám, že na tohle dovnitř a ven je shodou okolností MySQL šampion. V ostatních DB spláčete nad výdělkem. Ono to totiž není tak jednoduché jak to na první pohled vypadá.
— Hynek Vychodil
Jindrich kasal> Pěkné, ale má to jeden háček. Výsledkem toho nebude předchozí hodnota start ve sloupci prev, ale maximální hodnota. Bohužel zadání je poněkud jiné.
— Hynek Vychodil
Mimochodem když tam dám správný index: alter table products add index ps using btree (partner, start); a potom udělám CREATE TABLE out AS SELECT p1.start, p1.partner, p1.id, MAX(p2.start) FROM products p1 LEFT JOIN products p2 ON p1.partner = p2.partner WHERE p1.start > p2.start GROUP BY p1.start, p1.partner, p1.id; Je to hotové za 2s. To je včetně vytvoření tabulky (0.48s), načtení dat (0.5s) a vytvoření indexu (0.5s) jen 3.5s tedy jen desetkrát pomalejší než mentální onanie s perlovým skriptem, ale na RDBMS dobrý.
— Hynek Vychodil
Ultimátní test jakou cestou se dát je pro mě vyzkoušet danou technologii s desetkrát větším objemem dat, než potřebuji dnes. Když to vyzkouším tímhle: LC_ALL=C sort -t, -k2 test.data | perl -F',' -lane'print"$_,",$p eq$F[1]?$v:"NULL";($p,$v)=@F[1,2]' > result.data Mám výsledek za méně než 3.1s. Když použiji MySQL mysql> create table products ( id int not null primary key, partner int not null, start date ); Query OK, 0 rows affected (0.15 sec) mysql> load data infile '/tmp/test.data' into table products columns terminated by ','; Query OK, 1064890 rows affected (9.96 sec) Records: 1064890 Deleted: 0 Skipped: 0 Warnings: 0 mysql> alter table products add index ps using btree (partner, start); Query OK, 0 rows affected (5.78 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create table `out` as SELECT p1.id, p1.start, p1.partner, max(p2.start) as prev FROM products p1 left JOIN products p2 ON p1.partner = p2.partner AND p2.start > p1.start GROUP BY p1.start, p1.partner, p1.id; Query OK, 1064890 rows affected (1 min 6.00 sec) Records: 1064890 Duplicates: 0 Warnings: 0 Jsem na 82s. Z toho pro mě plyne, že Perl skript + sort škáluje prakticky lineárně, DB rozhodně ne.
— Hynek Vychodil
Tak jsem přišel na řešení v MySQL, které lineárně škáluje nejméně do 10M řádků a je jen nepatrně pomalejší než sort + perl skript (429ms vs 350ms pro 100k a 39s vs 35s pro 10M). To bych nevěřil co se dá z toho MySQL vymáčknout, když se ví jak na to. Nejvtipnější je, že na to není potřeba ani žádná velká mašina.
— Hynek Vychodil
Hynku, odpovidal jsem na clanek, kde Petr pise, ze to ma v databazi (dokonce ve ctyrech ruznych :) ). Proto jsem poradil databazove reseni. Pokud by byl vychozi stav .csv (o kterem ale clanek neni), tak bych taky pouzil sort a jednoduchy script. Samozrejme se da mezi jednim a druhym data prevadet za zlomek sekundy, takze je to trochu zbytecny spor :)
— Michal Illich
Michal Illich> "... a nahrál do něj data přímo z CSV. ... /tmp/products.csv ... Data se tam nahrávají nejlépe z S3 ... FROM 's3://padak-share/products.csv' ... " Mě to přišlo jako dobrá nápověda. Jinak MySQL nakonec umí číst i přes 2Mrows/s, ale má to háček. Docela dost záleží odkud, jak a kam. Docela mi vyrazilo dech, když jsem tuhle rychlost včera na svém nb vytáhl. U ostatních DB to může být docela problém. Z vlastní zkušenosti vím, že to je často hodně velký problém. Není žádná výjimka, když se to těmi "normálními" plazí neuvěřitelných 20krows/s.
— Hynek Vychodil
Odjel jsem na Vancouver Island a chvili jen postoval fotky z mobilu na facebook - koukam, ze se to tady rozjelo :) Ve čtvrtek jsme do Redshiftu stihli ještě nacpal 107M řádků, které potřebujeme transponovat (dojeli jsme na SLI hashe u (zbytečně) velkých tabulek, co mají eventy v key value pair podobě) a věci, co řešíme celý den v MySQL tam dáváme pod 30 minut (load z S3, SQL změny, export do S3). Objem na vstupu je 18GB. Dodám k tomu další data a podívám se na "script" style přístup. Každopádně RESPECT! Díky!
— Petr Šimeček
Petr Šimeček upvoted this post.
Hynek> Event Store nám kluci ukazovali už v loni. Všude jsem na to slyšel samou chválu, ale trochu jsme se toho báli, protože to bylo poměrně "raw" pro nás, jako 3rd partnera GD.
— Petr Šimeček
Ještě mi přijde dobré dodat, že my data transponujeme co nejvíc v SQL, protože to je ideální "lowest common denominator" pro popis "co s datama udělat". Díky tomu poměrně snadno předáváme zákazníkům kontrolu nad celou business logikou transformací a v praxi vidíme, že to sami vesele používají a vstup do takového prostředí je pro "levný".
— Petr Šimeček
Hynek Vychodil> Docela by mě zajímal ten dotaz, co škáluje lineárně do 10M, případně jiné aspekty toho řešení (jestli jsi třeba upravoval konfiguraci MySQL).
— Ondrej Hlavacek
Zkusil jsem v MySQL zopakovat přesně to co píše Hynek a trvá mi to hodinu. Tohle je náš my.cfg (https://s3.amazonaws.com/padak-share/my.cnf). Test jsem provedl v AWS na cr1.8xlarge.
— Petr Šimeček
Ondra> Nejrychlejší varianta vypadala takhle https://gist.github.com/pichi/6473329
— Hynek Vychodil
6 visitors upvoted this post.