V tejto štúdii som chcel vidieť, aké vylepšenia výkonu možno dosiahnuť použitím zdroja údajov ClickHouse namiesto PostgreSQL. Poznám výhody produktivity, ktoré mám z používania ClickHouse. Budú tieto výhody pokračovať, ak pristúpim ku ClickHouse z PostgreSQL pomocou nástroja Foreign Data Wrapper (FDW)?
Študované databázové prostredia sú PostgreSQL v11, clickhousedb_fdw a databáza ClickHouse. Nakoniec od PostgreSQL v11 budeme spúšťať rôzne SQL dotazy smerované cez náš clickhousedb_fdw do databázy ClickHouse. Potom uvidíme, aký je výkon FDW v porovnaní s rovnakými dotazmi spustenými v natívnom PostgreSQL a natívnom ClickHouse.
Databáza Clickhouse
ClickHouse je open source stĺpcový databázový systém, ktorý dokáže dosiahnuť výkon 100-1000-krát rýchlejšie ako tradičné databázové prístupy a dokáže spracovať viac ako miliardu riadkov za menej ako sekundu.
Clickhousedb_fdw
clickhousedb_fdw – Externý obal údajov pre databázu ClickHouse alebo FDW je projekt s otvoreným zdrojom od spoločnosti Percona. .
.
Ako uvidíte, toto poskytuje FDW pre ClickHouse, ktorý umožňuje SELECT z databázy ClickHouse a INSERT INTO zo servera PostgreSQL v11.
FDW podporuje pokročilé funkcie, ako je agregácia a spojenie. To výrazne zlepšuje výkon využívaním prostriedkov vzdialeného servera na tieto operácie náročné na zdroje.
Benchmark prostredie
- Supermicro server:
- Procesor Intel® Xeon® E5-2683 v3 @ 2.00 GHz
- 2 zásuvky / 28 jadier / 56 závitov
- Pamäť: 256GB RAM
- Úložisko: Samsung SM863 1.9 TB Enterprise SSD
- Súborový systém: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: verzia 11
Benchmark testy
Namiesto použitia nejakého strojom generovaného súboru údajov pre tento test sme použili údaje „Produktivita podľa času hláseného času operátora“ od roku 1987 do roku 2018. K údajom máte prístup .
Veľkosť databázy je 85 GB, poskytuje jednu tabuľku so 109 stĺpcami.
Porovnávacie dopyty
Tu sú dotazy, ktoré som použil na porovnanie ClickHouse, clickhousedb_fdw a PostgreSQL.
Q#
Dotaz obsahuje agregáty a zoskupiť podľa
Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q3
SELECT Origin, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;
Q4
SELECT Carrier, count() OD včas, KDE DepDelay>10 A Rok = 2007 SKUPINA PODĽA OBJEDNÁVKY dopravcu PODĽA počtu() DESC;
Q5
SELECT a.Carrier, c, c2, c1000/c2 ako c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year=2007 GROUP BY Carrier ) a VNÚTORNÉ PRIPOJENIE ( SELECT Carrier,count(*) AS c2 FROM ONtime WHERE Year=2007 GROUP BY Carrier)b na a.Carrier=b.ORDER ORDER BY c3 DESC;
Q6
SELECT a.Carrier, c, c2, c1000/c2 ako c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Carrier) a INNER JOIN (SELECT Carrier, count(*) AS c2 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Nosič ) b na a.Nosič=b.Prepravca OBJEDNÁVKA PODĽA c3 POPIS;
Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier;
Q8
SELECT Year, avg(DepDelay) FROM ontime GROUP BY Year;
Q9
vyberte rok, počítajte (*) ako c1 z ontime skupiny podľa roku;
Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Mesiac) a;
Q11
vyberte avg(c1) z (vyberte rok,mesiac,počet(*) ako c1 z ontime skupiny podľa roka,mesiaca) a;
Q12
SELECT OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;
Q13
SELECT OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;
Dopyt obsahuje spojenia
Q14
SELECT a.Year, c1/c2 FROM ( vyberte Year, count()1000 ako c1 od ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (vyberte rok, počítajte(*) ako c2 od ontime GROUP BY Year ) b na a.Year=b.Year ORDER BY a.Year;
Q15
SELECT a.“Year“, c1/c2 FROM (vyberte „Year“, count()1000 1 ako c10 OD fonttime, KDE “DepDelay”>2 GROUP PODĽA “YEAR”) a VNÚTORNÉ PRIPOJENIE (vyberte “Year”, počítajte (*) ako cXNUMX FROM FRAME GROUP BY “Year” ) b na a.”Year”=b. "Rok";
Tabuľka 1: Dotazy použité v benchmarku
Vykonávanie dopytov
Tu sú výsledky každého z dotazov pri spustení v rôznych nastaveniach databázy: PostgreSQL s indexmi a bez nich, natívny ClickHouse a clickhousedb_fdw. Čas sa zobrazuje v milisekundách.
Q#
PostgreSQL
PostgreSQL (indexované)
clickhouse
clickhousedb_fdw
Q1
27920
19634
23
57
Q2
35124
17301
50
80
Q3
34046
15618
67
115
Q4
31632
7667
25
37
Q5
47220
8976
27
60
Q6
58233
24368
55
153
Q7
30566
13256
52
91
Q8
38309
60511
112
179
Q9
20674
37979
31
81
Q10
34990
20102
56
148
Q11
30489
51658
37
155
Q12
39357
33742
186
1333
Q13
29912
30709
101
384
Q14
54126
39913
124
1364212
Q15
97258
30211
245
259
Tabuľka 1: Čas potrebný na vykonanie dotazov použitých v benchmarku
Zobraziť výsledky
Graf zobrazuje čas vykonania dotazu v milisekundách, os X zobrazuje číslo dotazu z vyššie uvedených tabuliek a os Y zobrazuje čas vykonania v milisekundách. Zobrazia sa výsledky ClickHouse a údaje získané z postgres pomocou clickhousedb_fdw. Z tabuľky môžete vidieť, že medzi PostgreSQL a ClickHouse je obrovský rozdiel, ale medzi ClickHouse a clickhousedb_fdw je minimálny.
Tento graf ukazuje rozdiel medzi ClickhouseDB a clickhousedb_fdw. Vo väčšine dopytov nie je réžia FDW taká vysoká a je sotva významná, s výnimkou Q12. Tento dotaz obsahuje spojenia a klauzulu ORDER BY. Z dôvodu klauzuly ORDER BY GROUP/BY sa ORDER BY nerozbaľuje na ClickHouse.
V tabuľke 2 vidíme časový skok v dopytoch Q12 a Q13. Opäť je to spôsobené klauzulou ORDER BY. Aby som to potvrdil, spustil som dotazy Q-14 a Q-15 s a bez klauzuly ORDER BY. Bez klauzuly ORDER BY je čas dokončenia 259 ms a s klauzulou ORDER BY je to 1364212. Na odladenie tohto dotazu vysvetľujem oba dotazy a tu sú výsledky vysvetlenia.
Q15: Bez klauzuly ORDER BY
bm=# EXPLAIN VERBOSE SELECT a."Year", c1/c2
FROM (SELECT "Year", count(*)*1000 AS c1 FROM fontime WHERE "DepDelay" > 10 GROUP BY "Year") a
INNER JOIN(SELECT "Year", count(*) AS c2 FROM fontime GROUP BY "Year") b ON a."Year"=b."Year";Q15: Dotaz bez klauzuly ORDER BY
QUERY PLAN
Hash Join (cost=2250.00..128516.06 rows=50000000 width=12)
Output: fontime."Year", (((count(*) * 1000)) / b.c2)
Inner Unique: true Hash Cond: (fontime."Year" = b."Year")
-> Foreign Scan (cost=1.00..-1.00 rows=100000 width=12)
Output: fontime."Year", ((count(*) * 1000))
Relations: Aggregate on (fontime)
Remote SQL: SELECT "Year", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Year"
-> Hash (cost=999.00..999.00 rows=100000 width=12)
Output: b.c2, b."Year"
-> Subquery Scan on b (cost=1.00..999.00 rows=100000 width=12)
Output: b.c2, b."Year"
-> Foreign Scan (cost=1.00..-1.00 rows=100000 width=12)
Output: fontime_1."Year", (count(*))
Relations: Aggregate on (fontime)
Remote SQL: SELECT "Year", count(*) FROM "default".ontime GROUP BY "Year"(16 rows)Q14: Dotaz s klauzulou ORDER BY
bm=# EXPLAIN VERBOSE SELECT a."Year", c1/c2 FROM(SELECT "Year", count(*)*1000 AS c1 FROM fontime WHERE "DepDelay" > 10 GROUP BY "Year") a
INNER JOIN(SELECT "Year", count(*) as c2 FROM fontime GROUP BY "Year") b ON a."Year"= b."Year"
ORDER BY a."Year";Q14: Plán dotazov s klauzulou ORDER BY
QUERY PLAN
Merge Join (cost=2.00..628498.02 rows=50000000 width=12)
Output: fontime."Year", (((count(*) * 1000)) / (count(*)))
Inner Unique: true Merge Cond: (fontime."Year" = fontime_1."Year")
-> GroupAggregate (cost=1.00..499.01 rows=1 width=12)
Output: fontime."Year", (count(*) * 1000)
Group Key: fontime."Year"
-> Foreign Scan on public.fontime (cost=1.00..-1.00 rows=100000 width=4)
Remote SQL: SELECT "Year" FROM "default".ontime WHERE (("DepDelay" > 10))
ORDER BY "Year" ASC
-> GroupAggregate (cost=1.00..499.01 rows=1 width=12)
Output: fontime_1."Year", count(*) Group Key: fontime_1."Year"
-> Foreign Scan on public.fontime fontime_1 (cost=1.00..-1.00 rows=100000 width=4)
Remote SQL: SELECT "Year" FROM "default".ontime ORDER BY "Year" ASC(16 rows)Výkon
Výsledky týchto experimentov ukazujú, že ClickHouse ponúka naozaj dobrý výkon a clickhousedb_fdw ponúka výkonnostné výhody ClickHouse z PostgreSQL. Aj keď pri používaní clickhousedb_fdw existuje určitá réžia, je zanedbateľná a porovnateľná s výkonom dosiahnutým natívnym spustením v databáze ClickHouse. To tiež potvrdzuje, že fdw v PostgreSQL poskytuje vynikajúce výsledky.
Telegramový rozhovor cez Clickhouse
Telegramový chat pomocou PostgreSQL
Zdroj: hab.com