I denne studien ønsket jeg å se hvilke ytelsesforbedringer som kan oppnås ved å bruke en ClickHouse-datakilde i stedet for PostgreSQL. Jeg kjenner produktivitetsfordelene jeg får ved å bruke ClickHouse. Vil disse fordelene fortsette hvis jeg får tilgang til ClickHouse fra PostgreSQL ved å bruke en Foreign Data Wrapper (FDW)?
Databasemiljøene som er studert er PostgreSQL v11, clickhousedb_fdw og ClickHouse database. Til syvende og sist, fra PostgreSQL v11 vil vi kjøre forskjellige SQL-spørringer rutet gjennom clickhousedb_fdw til ClickHouse-databasen. Vi vil da se hvordan FDWs ytelse sammenlignes med de samme spørringene som kjører i native PostgreSQL og native ClickHouse.
Clickhouse-database
ClickHouse er et kolonnebasert databasestyringssystem med åpen kildekode som kan oppnå ytelse 100-1000 ganger raskere enn tradisjonelle databasetilnærminger, i stand til å behandle over en milliard rader på mindre enn ett sekund.
Clickhousedb_fdw
clickhousedb_fdw - Den eksterne datainnpakningen for ClickHouse-databasen, eller FDW, er et åpen kildekode-prosjekt fra Percona. .
.
Som du vil se, gir dette en FDW for ClickHouse som tillater SELECT fra, og INSERT INTO, ClickHouse-databasen fra PostgreSQL v11-serveren.
FDW støtter avanserte funksjoner som aggregat og join. Dette forbedrer ytelsen betydelig ved å bruke ressursene til den eksterne serveren til disse ressurskrevende operasjonene.
Benchmark miljø
- Supermicro server:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00 GHz
- 2 stikkontakter / 28 kjerner / 56 gjenger
- Minne: 256 GB RAM
- Lagring: Samsung SM863 1.9TB Enterprise SSD
- Filsystem: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generisk #45~16.04.1-Ubuntu
- PostgreSQL: versjon 11
Benchmark-tester
I stedet for å bruke noe maskingenerert datasett for denne testen, brukte vi dataene "Produktivitet etter tid rapportert operatørtid" fra 1987 til 2018. Du kan få tilgang til dataene .
Databasestørrelsen er 85 GB, og gir én tabell med 109 kolonner.
Benchmark-spørringer
Her er spørringene jeg brukte til å sammenligne ClickHouse, clickhousedb_fdw og PostgreSQL.
Q#
Spørsmålet inneholder aggregater og grupper etter
Q1
VELG Ukedag, telling(*) SOM c FRA på tid HVOR År >= 2000 OG År <= 2008 GRUPPE ETTER Ukedag BESTILLE ETTER c DESC;
Q2
VELG Ukedag, telling(*) SOM c FRA på tid WHERE DepDelay>10 OG År >= 2000 OG År <= 2008 GRUPPER ETTER Ukedag BESTILLE ETTER c DESC;
Q3
VELG 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 på tide HVOR DepDelay>10 OG År = 2007 GRUPPER ETTER operatør BESTILLE ETTER telling() DESC;
Q5
VELG a.Carrier, c, c2, c1000/c2 som c3 FRA ( VELG operatør, telle() AS c FRA på tid WHERE DepDelay>10 OG År=2007 GRUPPE ETTER operatør ) a INNER JOIN ( VELG operatør, teller(*) AS c2 FRA på tid HVOR År=2007 GRUPPER AV operatør)b på a.Carrier=b. Carrier ORDRE AV c3 DESC;
Q6
VELG a.Carrier, c, c2, c1000/c2 som c3 FRA ( VELG operatør, telle() SOM c FRA på tid WHERE DepDelay>10 OG År >= 2000 OG År <= 2008 GRUPPE ETTER Transportør) en INNER JOIN (VELG operatør, teller(*) SOM c2 FRA på tid HVOR År >= 2000 OG År <= 2008 GRUPPER ETTER Carrier ) b på a.Carrier=b.Carrier BESTILL PÅ c3 DESC;
Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier;
Q8
VELG år, avg(DepDelay) FRA på tid GRUPPE FOR år;
Q9
velg år, tell(*) som c1 fra tid-gruppe etter år;
Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;
Q11
velg avg(c1) fra (velg Year,Month,count(*) som c1 fra ontime-gruppe etter år,måned) a;
Q12
VELG 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;
Spørsmålet inneholder sammenføyninger
Q14
SELECT a.Year, c1/c2 FROM ( velg Year, count()1000 som c1 fra på tide HVOR DepDelay>10 GRUPPE FOR ÅR) a INNER JOIN (velg år, teller(*) som c2 fra på tide GRUPPE FOR ÅR ) b på a.Year=b.Year BESTILLING ETTER a.Year;
Q15
VELG a."År", c1/c2 FRA ( velg "År", tell()1000 som c1 FRA fonttime HVOR “DepDelay”>10 GRUPPER ETTER “ÅR”) a INNER JOIN (velg “År”, teller(*) som c2 FRA fonttime GRUPPE ETTER “År” ) b på a.”År”=b. "År";
Tabell-1: Spørringer brukt i benchmark
Forespørselsutførelser
Her er resultatene av hver av spørringene når de kjøres i forskjellige databaseinnstillinger: PostgreSQL med og uten indekser, native ClickHouse og clickhousedb_fdw. Tiden vises i millisekunder.
Q#
PostgreSQL
PostgreSQL (indeksert)
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
Tabell-1: Tid det tar å utføre spørringene brukt i benchmark
Se resultater
Grafen viser utførelsestiden for spørringen i millisekunder, X-aksen viser spørringsnummeret fra tabellene ovenfor, og Y-aksen viser utførelsestiden i millisekunder. ClickHouse-resultater og data hentet fra postgres ved hjelp av clickhousedb_fdw vises. Fra tabellen kan du se at det er en enorm forskjell mellom PostgreSQL og ClickHouse, men minimal forskjell mellom ClickHouse og clickhousedb_fdw.
Denne grafen viser forskjellen mellom ClickhouseDB og clickhousedb_fdw. I de fleste søk er FDW-overheaden ikke så høy og er neppe signifikant bortsett fra Q12. Denne spørringen inkluderer sammenføyninger og en ORDER BY-klausul. På grunn av ORDER BY GROUP/BY-klausulen faller ikke ORDER BY ned til ClickHouse.
I tabell 2 ser vi tidshoppet i spørringene Q12 og Q13. Igjen, dette er forårsaket av ORDER BY-klausulen. For å bekrefte dette, kjørte jeg spørringer Q-14 og Q-15 med og uten ORDER BY-klausulen. Uten ORDER BY-klausulen er fullføringstiden 259 ms og med ORDER BY-klausulen er den 1364212. For å feilsøke denne spørringen forklarer jeg både spørringene og her er resultatene av forklaringen.
Q15: Uten 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: Spørring uten 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: Spørring 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: Spørreplan 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)Utgang
Resultatene av disse eksperimentene viser at ClickHouse tilbyr virkelig god ytelse, og clickhousedb_fdw tilbyr ytelsesfordelene til ClickHouse fra PostgreSQL. Selv om det er noen overhead når du bruker clickhousedb_fdw, er det ubetydelig og kan sammenlignes med ytelsen som oppnås ved å kjøre naturlig på ClickHouse-databasen. Dette bekrefter også at fdw i PostgreSQL gir utmerkede resultater.
Telegramchat via Clickhouse
Telegramchat ved hjelp av PostgreSQL
Kilde: www.habr.com