I denne undersøgelse ønskede jeg at se, hvilke præstationsforbedringer der kunne opnås ved at bruge en ClickHouse-datakilde frem for PostgreSQL. Jeg kender de produktivitetsfordele, jeg får ved at bruge ClickHouse. Vil disse fordele fortsætte, hvis jeg får adgang til ClickHouse fra PostgreSQL ved hjælp af en Foreign Data Wrapper (FDW)?
De undersøgte databasemiljøer er PostgreSQL v11, clickhousedb_fdw og ClickHouse database. I sidste ende vil vi fra PostgreSQL v11 køre forskellige SQL-forespørgsler dirigeret gennem vores clickhousedb_fdw til ClickHouse-databasen. Vi vil derefter se, hvordan FDW's ydeevne sammenlignes med de samme forespørgsler, der kører i native PostgreSQL og native ClickHouse.
Clickhouse-database
ClickHouse er et open source kolonnebaseret databasestyringssystem, der kan opnå ydeevne 100-1000 gange hurtigere end traditionelle databasetilgange, i stand til at behandle over en milliard rækker på mindre end et sekund.
Clickhousedb_fdw
clickhousedb_fdw - Den eksterne dataindpakning til ClickHouse-databasen, eller FDW, er et open source-projekt fra Percona. .
.
Som du vil se, giver dette en FDW for ClickHouse, der tillader SELECT fra, og INSERT INTO, ClickHouse-databasen fra PostgreSQL v11-serveren.
FDW understøtter avancerede funktioner såsom aggregat og join. Dette forbedrer ydeevnen markant ved at bruge ressourcerne på den eksterne server til disse ressourcekrævende operationer.
Benchmark miljø
- Supermicro server:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00 GHz
- 2 fatninger / 28 kerner / 56 gevind
- Hukommelse: 256 GB RAM
- Opbevaring: Samsung SM863 1.9TB Enterprise SSD
- Filsystem: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generisk #45~16.04.1-Ubuntu
- PostgreSQL: version 11
Benchmark test
I stedet for at bruge nogle maskingenererede datasæt til denne test, brugte vi dataene "Produktivitet efter tidsrapporteret operatørtid" fra 1987 til 2018. Du kan få adgang til dataene .
Databasestørrelsen er 85 GB, hvilket giver én tabel med 109 kolonner.
Benchmark-forespørgsler
Her er de forespørgsler, jeg brugte til at sammenligne ClickHouse, clickhousedb_fdw og PostgreSQL.
Q#
Forespørgsel indeholder aggregater og grupper efter
Q1
VÆLG Ugedag, tæl(*) SOM c FRA til tiden HVOR År >= 2000 OG År <= 2008 GRUPPER EFTER Ugedag BESTILLING AF c DESC;
Q2
VÆLG Ugedag, tæll(*) SOM c FRA til tiden WHERE DepDelay>10 OG År >= 2000 OG År <= 2008 GRUPPER EFTER Ugedag BESTILLING AF 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() FRA til tiden WHERE DepDelay>10 OG År = 2007 GRUPPER EFTER operatør BESTIL EFTER antal() DESC;
Q5
SELECT a. Carrier, c, c2, c1000/c2 som c3 FRA ( SELECT Carrier, count() SOM c FRA til tiden WHERE DepDelay>10 OG År=2007 GRUPPER EFTER operatør ) a INNER JOIN (VÆLG operatør, tæll(*) SOM c2 FRA på tid WHERE År=2007 GRUPPER AF operatør)b på a.Carrier=b. Carrier ORDRE AF c3 DESC;
Q6
SELECT a. Carrier, c, c2, c1000/c2 som c3 FRA ( SELECT Carrier, count() SOM c FRA til tiden WHERE DepDelay>10 OG År >= 2000 OG År <= 2008 GRUPPER EFTER operatør) en INNER JOIN (VÆLG operatør, tæller(*) SOM c2 FRA til tiden HVOR År >= 2000 OG År <= 2008 GRUPPER EFTER Carrier ) b på a.Carrier=b.Carrier BESTIL AF c3 DESC;
Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE År >= 2000 OG År <= 2008 GRUPPER EFTER Carrier;
Q8
VÆLG år, gns.(DepDelay) FRA til tiden GRUPPE FOR år;
Q9
vælg År, tæl(*) som c1 fra til tiden gruppe efter år;
Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;
Q11
vælg avg(c1) fra (vælg år, måned, tæller(*) som c1 fra til tiden gruppe efter år, måned) 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;
Forespørgsel indeholder tilslutninger
Q14
SELECT a.Year, c1/c2 FROM ( vælg Year, count()1000 som c1 fra til tiden HVOR DepDelay>10 GRUPPER FOR ÅR) a INNER JOIN (vælg år, tæller(*) som c2 fra til tider GRUPPE FOR ÅR ) b på a.År=b.ÅR BESTILLING EFTER a.År;
Q15
VÆLG a."År", c1/c2 FRA ( vælg "År", tæl()1000 som c1 FRA fonttime, HVOR “DepDelay”>10 GRUPPER EFTER “ÅR”) a INNER JOIN (vælg “År”, tæller(*) som c2 FRA font-gruppe efter “År” ) b på a.”År”=b. "År";
Tabel-1: Forespørgsler brugt i benchmark
Forespørgselsudførelser
Her er resultaterne af hver af forespørgslerne, når de køres i forskellige databaseindstillinger: PostgreSQL med og uden indekser, native ClickHouse og clickhousedb_fdw. Tiden vises i millisekunder.
Q#
PostgreSQL
PostgreSQL (indekseret)
klikhus
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
Tabel-1: Tid det tager at udføre de forespørgsler, der er brugt i benchmark
Se resultater
Grafen viser forespørgselsudførelsestiden i millisekunder, X-aksen viser forespørgselsnummeret fra tabellerne ovenfor, og Y-aksen viser udførelsestiden i millisekunder. ClickHouse-resultater og data hentet fra postgres ved hjælp af clickhousedb_fdw vises. Fra tabellen kan du se, at der er en kæmpe forskel på PostgreSQL og ClickHouse, men minimal forskel på ClickHouse og clickhousedb_fdw.
Denne graf viser forskellen mellem ClickhouseDB og clickhousedb_fdw. I de fleste forespørgsler er FDW-overheaden ikke så høj og er næppe signifikant bortset fra Q12. Denne forespørgsel inkluderer joins og en ORDER BY-klausul. På grund af ORDER BY GROUP/BY-klausulen falder ORDER BY ikke ned til ClickHouse.
I tabel 2 ser vi tidsspringet i forespørgsler Q12 og Q13. Igen er dette forårsaget af ORDER BY-klausulen. For at bekræfte dette kørte jeg forespørgsler Q-14 og Q-15 med og uden ORDER BY-klausulen. Uden ORDER BY-klausulen er færdiggørelsestiden 259 ms, og med ORDER BY-klausulen er den 1364212. For at fejlsøge denne forespørgsel forklarer jeg begge forespørgslerne, og her er resultaterne af forklaringen.
Q15: Uden ORDER BY klausul
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: Forespørgsel uden ORDER BY-klausul
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: Forespørgsel med ORDER BY-klausul
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: Forespørgselsplan med ORDER BY-klausul
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)Output
Resultaterne af disse eksperimenter viser, at ClickHouse tilbyder rigtig god ydeevne, og clickhousedb_fdw tilbyder ydeevnefordelene ved ClickHouse fra PostgreSQL. Selvom der er nogle overhead, når du bruger clickhousedb_fdw, er det ubetydeligt og kan sammenlignes med ydeevnen opnået ved at køre indbygget på ClickHouse-databasen. Dette bekræfter også, at fdw i PostgreSQL giver fremragende resultater.
Telegramchat via Clickhouse
Telegramchat ved hjælp af PostgreSQL
Kilde: www.habr.com