In dit onderzoek wilde ik zien welke prestatieverbeteringen konden worden bereikt door een ClickHouse-gegevensbron te gebruiken in plaats van PostgreSQL. Ik ken de productiviteitsvoordelen die ik krijg als ik ClickHouse gebruik. Blijven deze voordelen bestaan als ik ClickHouse vanuit PostgreSQL open met behulp van een Foreign Data Wrapper (FDW)?
De bestudeerde databaseomgevingen zijn PostgreSQL v11, clickhousedb_fdw en ClickHouse database. Uiteindelijk zullen we vanaf PostgreSQL v11 verschillende SQL-query's uitvoeren die via onze clickhousedb_fdw naar de ClickHouse-database worden gerouteerd. We zullen dan zien hoe de prestaties van FDW zich verhouden tot dezelfde zoekopdrachten die worden uitgevoerd in native PostgreSQL en native ClickHouse.
Clickhouse-database
ClickHouse is een open source zuilvormig databasebeheersysteem dat 100-1000 keer sneller prestaties kan leveren dan traditionele databasebenaderingen, en in staat is om meer dan een miljard rijen in minder dan een seconde te verwerken.
Clickhousedb_fdw
clickhousedb_fdw - De externe datawrapper voor de ClickHouse-database, of FDW, is een open source-project van Percona. .
.
Zoals u zult zien, biedt dit een FDW voor ClickHouse waarmee u kunt SELECTEREN uit en INSERT INTO uit de ClickHouse-database vanaf de PostgreSQL v11-server.
FDW ondersteunt geavanceerde functies zoals aggregeren en samenvoegen. Dit verbetert de prestaties aanzienlijk door de bronnen van de externe server te gebruiken voor deze resource-intensieve bewerkingen.
Benchmark-omgeving
- Supermicro-server:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00 GHz
- 2 sockets / 28 kernen / 56 draden
- Geheugen: 256 GB RAM
- Opslag: Samsung SM863 1.9 TB Enterprise SSD
- Bestandssysteem: ext4/xfs
- Besturingssysteem: Linux smblade01 4.15.0-42-generiek #45~16.04.1-Ubuntu
- PostgreSQL: versie 11
Benchmarktests
In plaats van voor deze test een door een machine gegenereerde dataset te gebruiken, hebben we de gegevens 'Productiviteit per tijd gerapporteerde operatortijd' van 1987 tot 2018 gebruikt. U heeft toegang tot de gegevens .
De databasegrootte is 85 GB, wat één tabel van 109 kolommen oplevert.
Benchmark-query's
Hier zijn de zoekopdrachten die ik heb gebruikt om ClickHouse, clickhousedb_fdw en PostgreSQL te vergelijken.
Q#
Query bevat aggregaten en groeperen op
Q1
SELECTEER DagVanWeek, tel(*) AS c VAN ontime WAAR Jaar >= 2000 EN Jaar <= 2008 GROEPEREN OP DagVanWeek ORDER OP c OMSCHRIJVING;
Q2
SELECTEER DayOfWeek, tel(*) ALS c VAN ontime WAAR DepDelay>10 EN Jaar >= 2000 EN Jaar <= 2008 GROEPEREN OP DagVanWeek ORDER OP c OMSCHRIJVING;
Q3
SELECTEER Oorsprong, telling(*) AS c VAN ontime WAAR DepDelay>10 EN Jaar >= 2000 EN Jaar <= 2008 GROEPEREN OP Oorsprong ORDER OP c DESC LIMIET 10;
Q4
SELECTEER vervoerder, aantal() VAN op tijd WAAR DepDelay>10 EN Jaar = 2007 GROEPEREN OP Vervoerder ORDER OP aantal() BESCHRIJVING;
Q5
SELECTEER a.Vervoerder, c, c2, c1000/c2 als c3 VAN (SELECTIE 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 op a.Carrier=b.Carrier ORDER DOOR c3 DESC;
Q6
SELECTEER a.Vervoerder, c, c2, c1000/c2 als c3 VAN (SELECTIE 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 Vervoerder ) b op a.Vervoerder=b.Vervoerder BESTEL DOOR c3 DESC;
Q7
SELECTEER vervoerder, avg(DepDelay) * 1000 AS c3 FROM ontime WAAR Jaar >= 2000 EN Jaar <= 2008 GROEPEREN OP vervoerder;
Q8
SELECTEER Jaar, avg(DepDelay) VAN op tijd GROEP OP Jaar;
Q9
selecteer Jaar, tel(*) als c1 uit ontime-groep op Jaar;
Q10
SELECT avg(cnt) FROM (SELECTEER Jaar,Maand,aantal(*) AS cnt FROM ontime WAAR DepDel15=1 GROEPEREN OP Jaar,Maand) a;
Q11
selecteer avg(c1) from (selecteer Year,Month,count(*) als c1 uit ontime-groep op Year,Month) a;
Q12
SELECTEER OriginCityName, DestCityName, count(*) AS c FROM ontime GROEPEREN OP OriginCityName, DestCityName ORDER OP c DESC LIMIET 10;
Q13
SELECTEER OriginCityName, count(*) AS c FROM ontime GROEPEREN OP OriginCityName ORDER OP c DESC LIMIET 10;
Query bevat joins
Q14
SELECT a.Year, c1/c2 FROM ( selecteer Year, count()1000 als c1 vanaf ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (selecteer Jaar, tel(*) als c2 vanaf ontime GROUP BY Year ) b op a.Year=b.Year ORDER BY a.Year;
Q15
SELECT a.”Jaar”, c1/c2 FROM ( selecteer “Jaar”, count()1000 als c1 FROM fontime WAAR “DepDelay”>10 GROEP OP “Jaar”) a INNER JOIN (selecteer “Jaar”, tel(*) als c2 FROM fontime GROEP OP “Jaar” ) b op a.”Jaar”=b. "Jaar";
Tabel-1: Query's gebruikt in benchmark
Query-uitvoeringen
Hier zijn de resultaten van elk van de zoekopdrachten wanneer deze in verschillende database-instellingen worden uitgevoerd: PostgreSQL met en zonder indexen, native ClickHouse en clickhousedb_fdw. De tijd wordt weergegeven in milliseconden.
Q#
PostgreSQL
PostgreSQL (geïndexeerd)
Klik op Huis
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: Tijd die nodig is om de in de benchmark gebruikte queries uit te voeren
Bekijk resultaten
De grafiek toont de uitvoeringstijd van de query in milliseconden, de X-as toont het querynummer uit de bovenstaande tabellen en de Y-as toont de uitvoeringstijd in milliseconden. ClickHouse-resultaten en gegevens opgehaald uit postgres met behulp van clickhousedb_fdw worden weergegeven. Uit de tabel kun je zien dat er een enorm verschil is tussen PostgreSQL en ClickHouse, maar een minimaal verschil tussen ClickHouse en clickhousedb_fdw.
Deze grafiek toont het verschil tussen ClickhouseDB en clickhousedb_fdw. Bij de meeste zoekopdrachten is de FDW-overhead niet zo hoog en nauwelijks significant, behalve in Q12. Deze query bevat joins en een ORDER BY-clausule. Vanwege de ORDER BY GROUP/BY-clausule komt ORDER BY niet terecht in ClickHouse.
In Tabel 2 zien we de tijdsprong in de vragen Q12 en Q13. Nogmaals, dit wordt veroorzaakt door de ORDER BY-clausule. Om dit te bevestigen heb ik de query's Q-14 en Q-15 uitgevoerd met en zonder de ORDER BY-clausule. Zonder de ORDER BY-clausule is de voltooiingstijd 259 ms en met de ORDER BY-clausule is dit 1364212. Om deze query te debuggen, leg ik beide query's uit en hier zijn de resultaten van de uitleg.
Q15: Zonder ORDER OP clausule
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: Zoekopdracht zonder ORDER BY-clausule
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: Zoekopdracht met ORDER BY-clausule
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: Queryplan met ORDER BY-clausule
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)Uitgang
De resultaten van deze experimenten laten zien dat ClickHouse echt goede prestaties levert, en clickhousedb_fdw biedt de prestatievoordelen van ClickHouse van PostgreSQL. Hoewel er enige overhead is bij het gebruik van clickhousedb_fdw, is deze verwaarloosbaar en vergelijkbaar met de prestaties die worden bereikt door native op de ClickHouse-database te draaien. Dit bevestigt ook dat fdw in PostgreSQL uitstekende resultaten oplevert.
Telegramchat via Clickhouse
Telegram-chat met PostgreSQL
Bron: www.habr.com