Ytelsestesting av analytiske spørringer i PostgreSQL, ClickHouse og clickhousedb_fdw (PostgreSQL)

I denne studien ønsket jeg å se hvilke ytelsesforbedringer som kan oppnås ved å bruke datakilde ClickHouse i stedet for PostgreSQL. Jeg er klar over hvilke ytelsesfordeler jeg får ved å bruke ClickHouse. Vil disse fordelene bli bevart hvis jeg får tilgang til ClickHouse fra PostgreSQL via et eksternt datagrensesnitt (FDW)?

De undersøkte databasemiljøene er PostgreSQL v11, clickhousedb_fdw og ClickHouse-databasen. Til slutt vil vi kjøre forskjellige SQL-spørringer fra PostgreSQL v11, rutet gjennom vår clickhousedb_fdw til ClickHouse-databasen. Deretter vil vi se hvordan ytelsen til FDW sammenlignes med de samme spørringene utført i både nativ PostgreSQL og nativ ClickHouse.

Clickhouse-database

ClickHouse er et åpen kildekode kolonneorientert databashåndteringssystem som kan oppnå ytelse 100-1000 ganger raskere enn tradisjonelle databaser, og som kan behandle mer enn en milliard rader på mindre enn ett sekund.

Clickhousedb_fdw

clickhousedb_fdw er et eksternt datagrensesnitt for ClickHouse-databasen, eller FDW, utviklet som et open-source prosjekt av Percona. Her er lenken til prosjektets GitHub-repo.

I mars skrev jeg en blogg som gir deg mer informasjon om vår FDW.

Som du vil se, gir dette FDW for ClickHouse muligheten til å SELECT fra og INSERT INTO ClickHouse-databasen fra PostgreSQL v11-serveren.

FDW støtter avanserte funksjoner som aggregater og join. Dette øker ytelsen betydelig ved å bruke ressurser fra den eksterne serveren for disse ressurskrevende operasjonene.

Benchmark-miljø

  • Supermicro-server:
    • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
    • 2 sokler / 28 kjerner / 56 tråder
    • 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: versjon 11

Benchmark-tester

I stedet for å bruke et maskin-generert datasett til denne testen, brukte vi dataene "Ytelse over tid rapportert av operatørens kjøretid" fra 1987 til 2018. Du kan få tilgang til dataene ved hjelp av vårt skript, tilgjengelig her.

Databasen har en størrelse på 85 GB, og har én tabell med 109 kolonner.

Benchmark-spørringer

Her er spørringene jeg brukte for å sammenligne ClickHouse, clickhousedb_fdw og PostgreSQL.

Q#
Spørringen inneholder aggregater og Group By

Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q2
VELG Dag I Uke, telte(*) AS c FRA ontime HVOR DepDelay>10 OG ÅR >= 2000 OG ÅR <= 2008 GRUPPER ETTER Dag I Uke BESTILL c DESC;

Q3
VELG Opprinnelse, telte(*) AS c FRA ontime HVOR DepDelay>10 OG ÅR >= 2000 OG ÅR <= 2008 GRUPPER ETTER Opprinnelse BESTILL c DESC BEGRENSE 10;

Q4
VELG Fraktfører, telte() FRA ontime HVOR DepDelay>10 OG ÅR = 2007 GRUPPER ETTER Fraktfører BESTILL telte() DESC;

Q5
VELG a.Fraktfører, c, c2, c1000/c2 som c3 FRA ( VELG Fraktfører, telte() AS c FRA ontime HVOR DepDelay>10 OG ÅR=2007 GRUPPER ETTER Fraktfører ) a INNREDS MED ( VELG Fraktfører,tallet(*) AS c2 FRA ontime HVOR ÅR=2007 GRUPPER ETTER Fraktfører)b på a.Fraktfører=b.Fraktfører BESTILL c3 DESC;

Q6
VELG a.Fraktfører, c, c2, c1000/c2 som c3 FRA ( VELG Fraktfører, telte() AS c FRA ontime HVOR DepDelay>10 OG ÅR >= 2000 OG ÅR = 2000 OG ÅR <= 2008 GRUPPER ETTER Fraktfører ) b på a.Fraktfører=b.Fraktfører BESTILL c3 DESC;

Q7
VELG Fraktfører, gjennomsnitt(DepDelay) * 1000 AS c3 FRA ontime HVOR ÅR >= 2000 OG ÅR <= 2008 GRUPPER ETTER Fraktfører;

Q8
VELG ÅR, gjennomsnitt(DepDelay) FRA ontime GRUPPER ETTER ÅR;

Q9
velg ÅR, telte(*) som c1 fra ontime gruppert etter ÅR;

Q10
VELG gjennomsnitt(cnt) FRA (VELG ÅR,Måned,telte(*) AS cnt FRA ontime HVOR DepDel15=1 GRUPPER ETTER ÅR,Måned) a;

Q11
velg gjennomsnitt(c1) fra (velg ÅR,Måned,telte(*) som c1 fra ontime gruppert etter ÅR,Måned) a;

Q12
VELG OpprinnelsesbyNavn, DestbyNavn, telte(*) AS c FRA ontime GRUPPER ETTER OpprinnelsesbyNavn, DestbyNavn BESTILL c DESC BEGRENSE 10;

Q13
VELG OpprinnelsesbyNavn, telte(*) AS c FRA ontime GRUPPER ETTER OpprinnelsesbyNavn BESTILL c DESC BEGRENSE 10;

Spørring Inneholder Sammenføyninger

Q14
VELG a.ÅR, c1/c2 FRA ( velg ÅR, telte()1000 som c1 FRA ontime HVOR DepDelay>10 GRUPPERT ETTER ÅR) a INNREDS MED (velg ÅR, telte(*) som c2 FRA ontime GRUPPERT ETTER ÅR ) b på a.ÅR=b.ÅR BESTILL a.ÅR;

Q15
VELG a.”ÅR”, c1/c2 FRA ( velg “ÅR”, telte()1000 som c1 FRA fontime HVOR “DepDelay”>10 GRUPPERT ETTER “ÅR”) a INNREDS MED (velg “ÅR”, telte(*) som c2 FRA fontime GRUPPERT ETTER “ÅR” ) b på a.”ÅR”=b.”ÅR”;

Tabell-1: Spørringer brukt i benchmark

Spørringsutførelse

Her er resultatene av hver spørring når den ble utført med forskjellige databaseinnstillinger: PostgreSQL med indekser og uten, egen 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 brukt til å utføre spørringene brukt i benchmark

Vis resultater

Grafen viser tiden som ble brukt til å utføre spørringen i millisekunder, X-aksen viser spørringsnummeret fra tabellene ovenfor, mens Y-aksen viser tiden brukt i millisekunder. Resultatene fra ClickHouse og data hentet fra postgres med clickhousedb_fdw vises. Tabellen viser at det er en enorm forskjell mellom PostgreSQL og ClickHouse, men minimal forskjell mellom ClickHouse og clickhousedb_fdw.

Ytelsestesting av analytiske spørringer i PostgreSQL, ClickHouse og clickhousedb_fdw (PostgreSQL)

Denne grafen viser forskjellen mellom ClickhouseDB og clickhousedb_fdw. I de fleste spørringer er overheaden fra FDW ikke så stor og knapt merkbar, unntatt Q12. Denne spørringen inkluderer sammenføyninger og ORDER BY setning. På grunn av ORDER BY GROUP/BY og ORDER BY går ikke ned til ClickHouse.

I tabell 2 ser vi et sprang i tiden for forespørslene Q12 og Q13. Jeg gjentar, dette skyldes tilbudet ORDER BY. For å bekrefte dette, utførte jeg forespørslene Q-14 og Q-15 med og uten tilbudet ORDER BY. Uten tilbudet ORDER BY er fullføringstiden 259 ms, mens med tilbudet ORDER BY er tiden 1364212. For å feilsøke denne forespørselen forklarer jeg begge forespørslene, 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: Forespørsel uten ORDER BY-klausul

FORESPOØRSEL PLAN                                                      
Hash Join  (kostnad=2250.00..128516.06 rader=50000000 bredde=12)  
Utdata: fontime."Year", (((count(*) * 1000)) / b.c2)  
Indre Unik: sann   Hash Betingelse: (fontime."Year" = b."Year")  
->  Utenlandsk Skanning  (kostnad=1.00..-1.00 rader=100000 bredde=12)        
Utdata: fontime."Year", ((count(*) * 1000))        
Relasjoner: Aggregat på (fontime)        
Fjern SQL: SELECT "Year", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Year"  
->  Hash  (kostnad=999.00..999.00 rader=100000 bredde=12)        
Utdata: b.c2, b."Year"        
->  Underforespørsel Skanning på b  (kostnad=1.00..999.00 rader=100000 bredde=12)              
Utdata: b.c2, b."Year"              
->  Utenlandsk Skanning  (kostnad=1.00..-1.00 rader=100000 bredde=12)                    
Utdata: fontime_1."Year", (count(*))                    
Relasjoner: Aggregat på (fontime)                    
Fjern SQL: SELECT "Year", count(*) FROM "default".ontime GROUP BY "Year"(16 rader)

Q14: Forespørsel 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ørsel Plan med ORDER BY-klausul

FORESPOØRSEL PLAN 
Merge Join  (kostnad=2.00..628498.02 rader=50000000 bredde=12)   
Utdata: fontime."Year", (((count(*) * 1000)) / (count(*)))   
Indre Unik: sann   Merge Betingelse: (fontime."Year" = fontime_1."Year")   
->  GroupAggregate  (kostnad=1.00..499.01 rader=1 bredde=12)       
Utdata: fontime."Year", (count(*) * 1000)       
Gruppe Nøkkel: fontime."Year"       
->  Utenlandsk Skanning på public.fontime  (kostnad=1.00..-1.00 rader=100000 bredde=4)          
Fjern SQL: SELECT "Year" FROM "default".ontime WHERE (("DepDelay" > 10)) 
            ORDER BY "Year" ASC       
->  GroupAggregate  (kostnad=1.00..499.01 rader=1 bredde=12)       
Utdata: fontime_1."Year", count(*)       
Gruppe Nøkkel: fontime_1."Year"       
->  Utenlandsk Skanning på public.fontime fontime_1  (kostnad=1.00..-1.00 rader=100000 bredde=4)  
          
Fjern SQL: SELECT "Year" FROM "default".ontime ORDER BY "Year" ASC(16 rader)

Utdata

Resultata av desse eksperimenta viser at ClickHouse tilbyr verkeleg god ytelse, og clickhousedb_fdw gir fordelar ved ytelsen til ClickHouse frå PostgreSQL. Sjølv om det er nokre kostnader involvert i bruken av clickhousedb_fdw, er desse ubetydelige og samanliknbare med ytelsen oppnådd ved eit naturleg oppsett i ClickHouse-databasen. Dette bekrefter også at fdw i PostgreSQL gir imponerande resultat.

Telegram-chat om Clickhouse https://t.me/clickhouse_ru
Telegram-chat om PostgreSQL https://t.me/pgsql

Kilde: habr.com

Kjøp pålitelig hosting for nettsteder med DDoS-beskyttelse, VPS VDS-servere 🔥 Kjøp pålitelig hosting for nettsteder med DDoS-beskyttelse, VPS VDS-servere | ProHoster