I den här studien ville jag se vilka prestandaförbättringar som kan uppnås genom att använda en ClickHouse-datakälla snarare än PostgreSQL. Jag vet vilka produktivitetsfördelar jag får av att använda ClickHouse. Kommer dessa fördelar att fortsätta om jag får åtkomst till ClickHouse från PostgreSQL med hjälp av en Foreign Data Wrapper (FDW)?
Databasmiljöerna som studeras är PostgreSQL v11, clickhousedb_fdw och ClickHouse databas. I slutändan kommer vi från PostgreSQL v11 att köra olika SQL-frågor som dirigeras genom vår clickhousedb_fdw till ClickHouse-databasen. Vi kommer sedan att se hur FDW:s prestanda jämförs med samma frågor som körs i inbyggt PostgreSQL och inbyggt ClickHouse.
Clickhouse Database
ClickHouse är ett kolumnformat databashanteringssystem med öppen källkod som kan uppnå prestanda 100-1000 gånger snabbare än traditionella databasmetoder, som kan bearbeta över en miljard rader på mindre än en sekund.
Clickhousedb_fdw
clickhousedb_fdw - Det externa dataomslaget för ClickHouse-databasen, eller FDW, är ett öppen källkodsprojekt från Percona. .
.
Som du kommer att se tillhandahåller detta en FDW för ClickHouse som tillåter SELECT från, och INSERT INTO, ClickHouse-databasen från PostgreSQL v11-servern.
FDW stöder avancerade funktioner som aggregat och join. Detta förbättrar prestandan avsevärt genom att använda fjärrserverns resurser för dessa resurskrävande operationer.
Benchmark miljö
- Supermicro server:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00 GHz
- 2 hylsor / 28 kärnor / 56 gängor
- Minne: 256 GB RAM
- Lagring: Samsung SM863 1.9TB Enterprise SSD
- Filsystem: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: version 11
Benchmark-tester
Istället för att använda någon maskingenererad datauppsättning för det här testet, använde vi data från "Produktivitet efter tidrapporterad operatörstid" från 1987 till 2018. Du kan komma åt data .
Databasstorleken är 85 GB, vilket ger en tabell med 109 kolumner.
Benchmark-frågor
Här är frågorna jag använde för att jämföra ClickHouse, clickhousedb_fdw och PostgreSQL.
Q#
Frågan innehåller aggregat och Gruppera efter
Q1
VÄLJ veckodag, räkna(*) SOM c FRÅN i tid WHERE År >= 2000 OCH År <= 2008 GRUPPER EFTER veckodag BESTÄLLNING AV c DESC;
Q2
VÄLJ veckodag, räkna(*) SOM c FRÅN i tid WHERE DepDelay>10 OCH år >= 2000 OCH år <= 2008 GRUPPER EFTER veckodag BESTÄLLNING AV c DESC;
Q3
VÄLJ Ursprung, räkning(*) 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() FRÅN i tid WHERE DepDelay>10 OCH År = 2007 GRUPPER EFTER operatör BESTÄLLNING EFTER antal() DESC;
Q5
VÄLJ a.Carrier, c, c2, c1000/c2 som c3 FRÅN ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 OCH Year=2007 GROUP BY Carrier ) a INNER JOIN (VÄLJ Carrier,count(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY Carrier)b på a.Carrier=b.Carrier ORDER AV c3 DESC;
Q6
VÄLJ a.Carrier, c, c2, c1000/c2 som c3 FRÅN ( SELECT Carrier, count() SOM c FRÅN i tid WHERE DepDelay>10 OCH År >= 2000 OCH År <= 2008 GRUPPER EFTER operatör) en INNER JOIN (VÄLJ operatör, räkna(*) SOM c2 FRÅN i tid VAR År >= 2000 OCH År <= 2008 GRUPPER EFTER Carrier ) b på a.Carrier=b.Carrier BESTÄLL MED c3 DESC;
Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier;
Q8
VÄLJ år, avg(DepDelay) FRÅN i tid GRUPPER FÖR år;
Q9
välj år, räkna(*) som c1 från ontime grupp 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älj avg(c1) från (välj År,Månad,räkning(*) som c1 från ontime-grupp efter År,Månad) 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;
Frågan innehåller kopplingar
Q14
SELECT a.Year, c1/c2 FROM ( välj Year, count()1000 som c1 från ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (välj År, räknas(*) som c2 från ontime GROUP BY Year ) b på a.Year=b.Year BESTÄLLNING PER a.Year;
Q15
VÄLJ a.”År”, c1/c2 FRÅN ( välj ”År”, räkna()1000 som c1 FRÅN fonttime DÄR “DepDelay”>10 GRUPPERAR EFTER “ÅR”) a INNER JOIN (välj “Year”, räknas(*) som c2 FRÅN fonttime GROUP BY “Year” ) b på a.”Year”=b. "År";
Tabell-1: Frågor som används i benchmark
Frågekörningar
Här är resultaten av var och en av frågorna när de körs i olika databasinställningar: PostgreSQL med och utan index, inbyggt ClickHouse och clickhousedb_fdw. Tiden visas i millisekunder.
Q#
PostgreSQL
PostgreSQL (indexerad)
klickhus
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
Tabell-1: Tid det tar att utföra de frågor som används i benchmark
Se Resultat
Grafen visar exekveringstiden i millisekunder, X-axeln visar sökningsnumret från tabellerna ovan och Y-axeln visar exekveringstiden i millisekunder. ClickHouse-resultat och data hämtade från postgres med clickhousedb_fdw visas. Från tabellen kan du se att det är en enorm skillnad mellan PostgreSQL och ClickHouse, men minimal skillnad mellan ClickHouse och clickhousedb_fdw.
Den här grafen visar skillnaden mellan ClickhouseDB och clickhousedb_fdw. I de flesta frågor är FDW-overheaden inte så hög och är knappast signifikant förutom Q12. Den här frågan innehåller joins och en ORDER BY-sats. På grund av ORDER BY GROUP/BY-klausulen faller ORDER BY inte ner till ClickHouse.
I tabell 2 ser vi tidshoppet i frågorna Q12 och Q13. Återigen, detta orsakas av ORDER BY-klausulen. För att bekräfta detta körde jag frågorna Q-14 och Q-15 med och utan ORDER BY-satsen. Utan ORDER BY-satsen är slutförandetiden 259 ms och med ORDER BY-satsen är den 1364212. För att felsöka den här frågan förklarar jag båda frågorna och här är resultatet av förklaringen.
F15: Utan 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";F15: Fråga utan 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)F14: Fråga 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";F14: Fråga efter plan 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)Utgång
Resultaten av dessa experiment visar att ClickHouse erbjuder riktigt bra prestanda, och clickhousedb_fdw erbjuder prestandafördelarna med ClickHouse från PostgreSQL. Även om det finns en viss overhead när du använder clickhousedb_fdw, är den försumbar och jämförbar med den prestanda som uppnås genom att köra inbyggt på ClickHouse-databasen. Detta bekräftar också att fdw i PostgreSQL ger utmärkta resultat.
Telegramchatt via Clickhouse
Telegramchatt med PostgreSQL
Källa: will.com