ProHoster > Blogg > Administration > Prestandatest av analytiska frÄgor i PostgreSQL, ClickHouse och clickhousedb_fdw (PostgreSQL)

Prestandatest av analytiska frÄgor i PostgreSQL, ClickHouse och clickhousedb_fdw (PostgreSQL)

I denna studie ville jag titta pÄ vilka prestandaförbÀttringar som kan uppnÄs genom att anvÀnda datakÀlla ClickHouse istÀllet för PostgreSQL. Jag Àr medveten om de prestandafördelar jag fÄr med ClickHouse. Kommer dessa fördelar att bevaras om jag fÄr Ätkomst till ClickHouse frÄn PostgreSQL via en externa dataskal (FDW)?

De databasmiljöer som utforskas Àr PostgreSQL v11, clickhousedb_fdw och ClickHouse-databasen. I slutÀndan kommer vi att köra olika SQL-frÄgor frÄn PostgreSQL v11 som dirigeras genom vÄr clickhousedb_fdw till ClickHouse-databasen. Sedan kommer vi att se hur FDW:s prestanda jÀmförs med samma frÄgor som körs i ren PostgreSQL och ren ClickHouse.

Databasen Clickhouse

ClickHouse Àr ett kolumnbaserat databashanteringssystem med öppen kÀllkod som kan nÄ prestanda 100-1000 gÄnger snabbare Àn traditionella databaser, kapabel att hantera mer Àn en miljard rader pÄ mindre Àn en sekund.

Clickhousedb_fdw

clickhousedb_fdw Àr en extern dataskal för ClickHouse-databasen, eller FDW, som Àr ett projekt med öppen kÀllkod frÄn Percona. HÀr Àr en lÀnk till projektets GitHub-repo.

I mars skrev jag en blogg som berÀttar mer om vÄr FDW.

Som du kommer att se ger det FDW för ClickHouse, vilket tillÄter SELECT from och INSERT INTO ClickHouse-databasen frÄn PostgreSQL v11-servern.

FDW stöder avancerade funktioner som aggregate och join. Detta ökar avsevÀrt prestandan genom att anvÀnda resurser frÄn den fjÀrrservern för dessa resurskrÀvande operationer.

Benchmark-miljö

  • Supermicro-server:
    • IntelÂź XeonÂź CPU E5-2683 v3 @ 2.00GHz
    • 2 socklar / 28 kĂ€rnor / 56 trĂ„dar
    • Minne: 256GB 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 en maskin-skapad dataset för detta test anvÀnde vi data "Prestanda över tid, rapporterad om operatörens drifttid" frÄn 1987 till 2018. Du kan fÄ tillgÄng till datan med vÄrt skript som finns hÀr.

Databasens storlek À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 Group By

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

Q2
VÄLJ Dag i veckan, count(*) AS c FRÅN ontime DÄR DepDelay>10 OCH År >= 2000 OCH År <= 2008 GRUPPERA EFTER Dag i veckan BESTÄLL EFTER c DESC;

Q3
VÄLJ Ursprung, count(*) AS c FRÅN ontime DÄR DepDelay>10 OCH År >= 2000 OCH År <= 2008 GRUPPERA EFTER Ursprung BESTÄLL EFTER c DESC BEGRÄNSA TILL 10;

Q4
VÄLJ Frakt, count() FRÅN ontime DÄR DepDelay>10 OCH År = 2007 GRUPPERA EFTER Frakt BESTÄLL EFTER count() DESC;

Q5
VÄLJ a.Frakt, c, c2, c1000/c2 som c3 FRÅN ( VÄLJ Frakt, count() AS c FRÅN ontime DÄR DepDelay>10 OCH År=2007 GRUPPERA EFTER Frakt ) a INRE FÖRENING ( VÄLJ Frakt,count(*) AS c2 FRÅN ontime DÄR År=2007 GRUPPERA EFTER Frakt) b pĂ„ a.Frakt=b.Frakt BESTÄLL EFTER c3 DESC;

Q6
VÄLJ a.Frakt, c, c2, c1000/c2 som c3 FRÅN ( VÄLJ Frakt, count() AS c FRÅN ontime DÄR DepDelay>10 OCH År >= 2000 OCH År = 2000 OCH År <= 2008 GRUPPERA EFTER Frakt ) b pĂ„ a.Frakt=b.Frakt BESTÄLL EFTER c3 DESC;

Q7
VÄLJ Frakt, avg(DepDelay) * 1000 AS c3 FRÅN ontime DÄR År >= 2000 OCH År <= 2008 GRUPPERA EFTER Frakt;

Q8
VÄLJ År, avg(DepDelay) FRÅN ontime GRUPPERA EFTER År;

Q9
vĂ€lj År, count(*) som c1 FRÅN ontime GRUPPERA EFTER År;

Q10
VÄLJ avg(cnt) FRÅN (VÄLJ År,MĂ„nad,count(*) AS cnt FRÅN ontime DÄR DepDel15=1 GRUPPERA EFTER År,MĂ„nad) a;

Q11
vĂ€lj avg(c1) frĂ„n (vĂ€lj År,MĂ„nad,count(*) som c1 FRÅN ontime GRUPPERA EFTER År,MĂ„nad) a;

Q12
VÄLJ Ursprung stadsnamn, Dest stadsnamn, count(*) AS c FRÅN ontime GRUPPERA EFTER Ursprung stadsnamn, Dest stadsnamn BESTÄLL EFTER c DESC BEGRÄNSA TILL 10;

Q13
VÄLJ Ursprung stadsnamn, count(*) AS c FRÅN ontime GRUPPERA EFTER Ursprung stadsnamn BESTÄLL EFTER c DESC BEGRÄNSA TILL 10;

FrÄgan InnehÄller Föreningar

Q14
VÄLJ a.År, c1/c2 FRÅN ( vĂ€lj År, count()1000 som c1 FRÅN ontime DÄR DepDelay>10 GRUPPERA EFTER År) a INRE FÖRENING (vĂ€lj År, count(*) som c2 FRÅN ontime GRUPPERA EFTER År ) b pĂ„ a.År=b.År BESTÄLL EFTER a.År;

Q15
VÄLJ a.Ӂr”, c1/c2 FRÅN ( vĂ€lj “År”, count()1000 som c1 FRÅN fontime DÄR “DepDelay”>10 GRUPPERA EFTER “År”) a INRE FÖRENING (vĂ€lj “År”, count(*) som c2 FRÅN fontime GRUPPERA EFTER “År” ) b pĂ„ a.Ӂr”=b.Ӂr”;

Tabell-1: FrÄgor som anvÀnds i benchmark

FrÄgeexekveringar

HÀr Àr resultaten för varje frÄga nÀr den kördes under olika instÀllningar av databasen: PostgreSQL med och utan index, egen ClickHouse och clickhousedb_fdw. Tiden visas i millisekunder.

Q#
PostgreSQL
PostgreSQL (Indexerad)
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 som krÀvs för att köra de frÄgor som anvÀnds i benchmark

Visa resultat

Grafen visar tiden för att köra frÄgan i millisekunder, X-axeln visar nummer pÄ frÄgan frÄn tabellerna ovan, och Y-axeln visar tiden för att köra i millisekunder. Resultaten frÄn ClickHouse och data som erhÄllits frÄn postgres med hjÀlp av clickhousedb_fdw visas. Tabellen visar att det finns en enorm skillnad mellan PostgreSQL och ClickHouse, men en minimal skillnad mellan ClickHouse och clickhousedb_fdw.

Prestandatest av analytiska frÄgor i PostgreSQL, ClickHouse och clickhousedb_fdw (PostgreSQL)

Denna graf visar skillnaden mellan ClickhouseDB och clickhousedb_fdw. För de flesta frÄgor Àr overheadkostnaderna för FDW inte sÄ stora och knappt mÀrkbara, förutom för Q12. Denna frÄga inkluderar föreningar och ORDER BY-klausulen. PÄ grund av ORDER BY GROUP/BY och ORDER BY hoppar inte ner till ClickHouse.

I tabell 2 ser vi en ökning av tiden för förfrÄgningarna Q12 och Q13. Jag upprepar, detta orsakas av ORDER BY-satsen. För att bekrÀfta detta har jag utfört förfrÄgningarna Q-14 och Q-15 med och utan ORDER BY-sats. Utan ORDER BY-satsen tar det 259 ms, medan det med ORDER BY tar 1364212 ms. För att felsöka denna förfrÄgan förklarar jag bÄda förfrÄgningarna, och hÀr Àr resultaten av förklaringen.

Q15: Utan ORDER BY-sats

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: FörfrÄgan utan ORDER BY-sats

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: FörfrÄgan med ORDER BY-sats

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: FörfrÄgningsplan med ORDER BY-sats

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)

Utdata

Resultaten av dessa experiment visar att ClickHouse verkligen erbjuder god prestanda, och att clickhousedb_fdw ger prestandafördelar frĂ„n ClickHouse till PostgreSQL. Även om det finns vissa overheadkostnader med att anvĂ€nda clickhousedb_fdw, Ă€r dessa marginella och jĂ€mförbara med den prestanda som uppnĂ„s vid direkt anvĂ€ndning av ClickHouse-databasen. Detta bekrĂ€ftar ocksĂ„ att fdw i PostgreSQL ger imponerande resultat.

Telegramchatt för Clickhouse https://t.me/clickhouse_ru
Telegramchatt för PostgreSQL https://t.me/pgsql

KĂ€lla: habr.com

Köp pĂ„litlig webbhotell med DDoS-skydd, VPS VDS-servrar đŸ”„ Köp pĂ„litlig webbhotell med DDoS-skydd, VPS VDS-servrar | ProHoster