V této studii jsem chtěl vidět, jakého zlepšení výkonu lze dosáhnout použitím zdroje dat ClickHouse spíše než PostgreSQL. Znám výhody produktivity, které získávám z používání ClickHouse. Budou tyto výhody pokračovat, pokud přistupuji ke ClickHouse z PostgreSQL pomocí Foreign Data Wrapper (FDW)?
Studovaná databázová prostředí jsou PostgreSQL v11, clickhousedb_fdw a databáze ClickHouse. Nakonec z PostgreSQL v11 budeme spouštět různé SQL dotazy směrované přes náš clickhousedb_fdw do databáze ClickHouse. Poté uvidíme, jak se výkon FDW porovná se stejnými dotazy spuštěnými v nativním PostgreSQL a nativním ClickHouse.
Databáze Clickhouse
ClickHouse je open source systém pro správu sloupcových databází, který dokáže dosáhnout výkonu 100-1000krát rychleji než tradiční databázové přístupy a dokáže zpracovat více než miliardu řádků za méně než sekundu.
Clickhousedb_fdw
clickhousedb_fdw – externí datový obal pro databázi ClickHouse neboli FDW je projekt s otevřeným zdrojovým kódem od společnosti Percona. .
.
Jak uvidíte, toto poskytuje FDW pro ClickHouse, který umožňuje SELECT z a INSERT INTO databáze ClickHouse ze serveru PostgreSQL v11.
FDW podporuje pokročilé funkce, jako je agregace a spojení. To výrazně zlepšuje výkon používáním prostředků vzdáleného serveru pro tyto operace náročné na zdroje.
Benchmark prostředí
- Supermicro server:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00 GHz
- 2 patice / 28 jader / 56 závitů
- Paměť: 256GB RAM
- Úložiště: Samsung SM863 1.9TB Enterprise SSD
- Souborový systém: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: verze 11
Srovnávací testy
Namísto použití nějaké strojově generované datové sady pro tento test jsme použili data „Produktivita podle času hlášeného operátora“ od roku 1987 do roku 2018. K datům máte přístup .
Velikost databáze je 85 GB, poskytuje jednu tabulku se 109 sloupci.
Srovnávací dotazy
Zde jsou dotazy, které jsem použil k porovnání ClickHouse, clickhousedb_fdw a PostgreSQL.
Q#
Dotaz obsahuje agregáty a seskupit podle
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() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() DESC;
Q5
SELECT a.Carrier, c, c2, c1000/c2 jako c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year=2007 GROUP BY Carrier ) a INNER JOIN ( SELECT Carrier,count(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY Carrier)b na a.Carrier=b.Carrier ORDER BY c3 DESC;
Q6
SELECT a.Carrier, c, c2, c1000/c2 jako 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.Nosič OBJEDNAT PODLE 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čítejte (*) jako c1 z ontime skupiny podle roku;
Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;
Q11
select avg(c1) from (vyberte Year,Month,count(*) as c1 from ontime group by Year,Month) 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;
Dotaz obsahuje spojení
Q14
SELECT a.Year, c1/c2 FROM ( vyberte Year, count()1000 jako c1 od ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (vyberte rok, počítejte(*) jako c2 od ontime GROUP BY Year ) b v a.Year=b.Year ORDER BY a.Year;
Q15
VYBERTE a.”Rok”, c1/c2 FROM ( vyberte “Rok”, počet()1000 jako c1 OD fontime KDE “DepDelay”>10 GROUP BY “Year”) a VNITŘNÍ JOIN (vyberte “Year”, počítejte (*) jako c2 FROM fontime GROUP BY “Year” ) b na a.”Year”=b. "Rok";
Tabulka-1: Dotazy používané v benchmarku
Provádění dotazů
Zde jsou výsledky každého z dotazů při spuštění v různých nastaveních databáze: PostgreSQL s indexy a bez nich, nativní ClickHouse a clickhousedb_fdw. Čas se zobrazuje v milisekundách.
Q#
PostgreSQL
PostgreSQL (indexováno)
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
Tabulka-1: Doba potřebná k provedení dotazů použitých v benchmarku
Zobrazit výsledky
Graf ukazuje dobu provedení dotazu v milisekundách, osa X ukazuje číslo dotazu z výše uvedených tabulek a osa Y ukazuje dobu provedení v milisekundách. Jsou zobrazeny výsledky ClickHouse a data získaná z postgres pomocí clickhousedb_fdw. Z tabulky můžete vidět, že je obrovský rozdíl mezi PostgreSQL a ClickHouse, ale minimální rozdíl mezi ClickHouse a clickhousedb_fdw.
Tento graf ukazuje rozdíl mezi ClickhouseDB a clickhousedb_fdw. Ve většině dotazů není režie FDW tak vysoká a s výjimkou Q12 je sotva významná. Tento dotaz obsahuje spojení a klauzuli ORDER BY. Kvůli klauzuli ORDER BY GROUP/BY se ORDER BY nerozbaluje na ClickHouse.
V tabulce 2 vidíme časový skok v dotazech Q12 a Q13. Opět je to způsobeno klauzulí ORDER BY. Abych to potvrdil, spustil jsem dotazy Q-14 a Q-15 s a bez klauzule ORDER BY. Bez klauzule ORDER BY je čas dokončení 259 ms as klauzulí ORDER BY je to 1364212. Pro odladění tohoto dotazu vysvětluji oba dotazy a zde jsou výsledky vysvětlení.
Q15: Bez klauzule 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 klauzule 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 klauzulí 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 dotazů s klauzulí 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 experimentů ukazují, že ClickHouse nabízí opravdu dobrý výkon a clickhousedb_fdw nabízí výkonnostní výhody ClickHouse z PostgreSQL. I když při použití clickhousedb_fdw existuje určitá režie, je zanedbatelná a srovnatelná s výkonem dosaženým nativním spuštěním v databázi ClickHouse. To také potvrzuje, že fdw v PostgreSQL poskytuje vynikající výsledky.
Telegramový chat přes Clickhouse
Telegramový chat pomocí PostgreSQL
Zdroj: www.habr.com