ProHoster > blog > Gweinyddiaeth > Profi perfformiad ymholiadau dadansoddol yn PostgreSQL, ClickHouse a clickhousedb_fdw (PostgreSQL)

Profi perfformiad ymholiadau dadansoddol yn PostgreSQL, ClickHouse a clickhousedb_fdw (PostgreSQL)

Yn yr astudiaeth hon, roeddwn i eisiau gweld pa welliannau perfformiad y gallwn i eu cyflawni trwy ddefnyddio ffynhonnell ddata ClickHouse yn hytrach na PostgreSQL. Rwy'n gwybod y manteision perfformiad rwy'n eu cael o ClickHouse. A fydd y manteision hyn yn cael eu cadw os byddaf yn cyrchu ClickHouse o PostgreSQL gan ddefnyddio lapio data allanol (FDW)?

Yr amgylcheddau cronfa ddata sy'n cael eu hastudio yw PostgreSQL v11, clickhousedb_fdw, a chronfa ddata ClickHouse. Yn y pen draw, byddwn yn rhedeg amrywiol ymholiadau SQL o PostgreSQL v11, wedi'u llwybro trwy ein clickhousedb_fdw, i gronfa ddata ClickHouse. Yna byddwn yn gweld sut mae perfformiad yr FDW yn cymharu â'r un ymholiadau a weithredir yn PostgreSQL brodorol a ClickHouse brodorol.

Cronfa Ddata Clickhouse

Mae ClickHouse yn system rheoli cronfa ddata ffynhonnell agored, sy'n seiliedig ar golofnau, a all gyflawni perfformiad 100-1000 gwaith yn gyflymach na dulliau cronfa ddata traddodiadol, ac sy'n gallu prosesu dros biliwn o resi mewn llai nag eiliad.

Clickhousedb_fdw

clickhousedb_fdw — Mae Lapio Data Allanol Cronfa Ddata ClickHouse, neu FDW, yn brosiect ffynhonnell agored gan Percona. Dyma ddolen i storfa GitHub y prosiect.

Ym mis Mawrth ysgrifennais flog sy'n dweud mwy wrthych chi am ein FDW.

Fel y gwelwch, mae hyn yn darparu FDW ar gyfer ClickHouse sy'n eich galluogi i DDEWIS o, a MEWNOSOD I, gronfa ddata ClickHouse o weinydd PostgreSQL v11.

Mae FDW yn cefnogi nodweddion uwch fel crynhoadau ac ymuno. Mae hyn yn gwella perfformiad yn sylweddol trwy ddefnyddio adnoddau gweinydd o bell ar gyfer y gweithrediadau hyn sy'n defnyddio llawer o adnoddau.

Amgylchedd meincnod

  • Gweinydd Supermicro:
    • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
    • 2 soced / 28 craidd / 56 edau
    • Cof: 256GB o RAM
    • Storio: Samsung SM863 1.9TB Enterprise SSD
    • System ffeiliau: ext4/xfs
  • OS: Linux smblade01 4.15.0-42-generig #45~16.04.1-Ubuntu
  • PostgreSQL: fersiwn 11

Profion meincnod

Yn lle defnyddio set ddata a gynhyrchwyd gan beiriant ar gyfer y prawf hwn, fe wnaethom ddefnyddio'r data "Cynhyrchiant dros Amser a Adroddwyd gan Amser Gweithredwr" o 1987 i 2018. Gallwch gael mynediad at y data gan ddefnyddio ein sgript, sydd ar gael yma.

Maint y gronfa ddata yw 85GB, gan ddarparu un tabl gyda 109 o golofnau.

Ymholiadau Meincnod

Dyma'r ymholiadau a ddefnyddiais i gymharu ClickHouse, clickhousedb_fdw a PostgreSQL.

Q#
Mae'r ymholiad yn cynnwys crynodebau ac yn grwpio yn ôl

Q1
SELECT DayOfWeek, count(*) AS c O ontime WHERE Blwyddyn >= 2000 A Blwyddyn <= 2008 GRŴPIWCH YN ÔL DayOfWeek GORCHYMYN YN ÔL c DISGRIFIAD;

Q2
DEWIS DiwrnodO'rWythnos, cyfrif(*) FEL c O amser WHERE DepDelay>10 A Blwyddyn >= 2000 A Blwyddyn <= 2008 GRŴPIWCH YN ÔL DiwrnodO'rWythnos GORCHYMYN YN ÔL c DISGRIFIAD;

Q3
SELECT Origin, count(*) AS c O ontime WHERE DepDelay>10 A Blwyddyn >= 2000 A Blwyddyn <= 2008 GRWP YN ÔL Origin GORCHYMYN GAN c DESC TERFYN 10;

Q4
DEWIS Cludwr, cyfrif() O amser ar amser LLE Mae oedi dadlwytho>10 A Blwyddyn = 2007 GRŴPIWCH GAN Gludwr GORCHYMYN GAN cyfrif() DISGRIFIAD;

Q5
DEWIS a.Cludwr, c, c2, c1000/c2 fel c3 O (DEWIS Cludwr, cyfrif() FEL c O amser WHERE DepDelay>10 A Blwyddyn=2007 GRŴP GAN Gludwr ) a YMUNIANT MEWNOL ( SELECT Carrier,count(*) FEL c2 O amser WHERE Blwyddyn=2007 GRŴP GAN Gludwr)b ar a.Cludwr=b.Cludwr GORCHYMYN GAN c3 DISGRIFIAD;

Q6
DEWIS a.Cludwr, c, c2, c1000/c2 fel c3 O (DEWIS Cludwr, cyfrif() AS c O amser WHERE DepDelay>10 A Blwyddyn >= 2000 A Blwyddyn <= 2008 GROUP BY Carrier) a INNER JOIN ( SELECT Carrier, count(*) AS c2 O amser WHERE Blwyddyn >= 2000 A Blwyddyn <= 2008 GROUP BY Carrier ) b ar a.Carrier=b.Carrier ORDER BY c3 DESC;

Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 O ontime WHERE Blwyddyn >= 2000 A Blwyddyn <= 2008 GRŴP GAN Carrier;

Q8
DEWIS Blwyddyn, cyf(Oedi Gwahardd) O amser GRŴP GAN Flwyddyn;

Q9
dewis Blwyddyn, cyfrif(*) fel c1 o'r grŵp amser-amser yn ôl Blwyddyn;

Q10
SELECT avg(cnt) O (SELECT Blwyddyn,Mis,cyfrif(*) AS cnt O amser WHERE DepDel15=1 GRŴP YN ÔL Blwyddyn,Mis) a;

Q11
dewiswch avg(c1) o (dewiswch Flwyddyn,Mis,cyfrif(*) fel c1 o'r grŵp amser yn ôl Blwyddyn,Mis) a;

Q12
SELECT OriginCityName, DestCityName, count(*) AS c O ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;

Q13
SELECT OriginCityName, count(*) AS c O ontime GROUP BY OriginCityName ORCHYMYN GAN c DESC TERFYN 10;

Mae'r ymholiad yn cynnwys ymuno

Q14
DEWISWCH a.Blwyddyn, c1/c2 O (dewiswch Flwyddyn, cyfrif()1000 fel c1 o amser LLE Mae Oedi Ymlaen > 10 GROUP BY Year) a INNER JOIN (dewiswch Blwyddyn, cyfrif(*) fel c2 o amser GROUP BY Year) b ar a.Year=b.Year ORDER BY a.Year;

Q15
DEWISWCH a.”Blwyddyn”, c1/c2 O ( dewiswch “Blwyddyn”, cyfrif()1000 fel c1 O fontime WHERE “DepDelay”>10 GROUP BY “Blwyddyn”) a INNER JOIN (dewiswch “Blwyddyn”, count(*) fel c2 O fontime GROUP BY “Blwyddyn” ) b ar a.”Blwyddyn”=b.”Blwyddyn”;

Tabl-1: Ymholiadau a ddefnyddiwyd yn y meincnod

Gweithrediadau ymholiadau

Dyma'r canlyniadau ar gyfer pob ymholiad pan gaiff ei redeg mewn gwahanol osodiadau cronfa ddata: PostgreSQL gyda a heb fynegeion, ClickHouse brodorol, a clickhousedb_fdw. Dangosir amser mewn milieiliadau.

Q#
PostgreSQL
PostgreSQL (Mynegai)
CliciwchHouse
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

Tabl-1: Amser a gymerwyd i gyflawni'r ymholiadau a ddefnyddiwyd yn y meincnod

Gweld canlyniadau

Mae'r graff yn dangos amser gweithredu'r ymholiad mewn milieiliadau. Mae'r echelin-x yn dangos rhif yr ymholiad o'r tablau uchod, ac mae'r echelin-y yn dangos amser gweithredu mewn milieiliadau. Dangosir canlyniadau ClickHouse a data a gafwyd o Postgres gan ddefnyddio clickhousedb_fdw. Mae'r tabl yn dangos gwahaniaeth sylweddol rhwng PostgreSQL a ClickHouse, ond gwahaniaeth lleiaf posibl rhwng ClickHouse a clickhousedb_fdw.

Profi perfformiad ymholiadau dadansoddol yn PostgreSQL, ClickHouse a clickhousedb_fdw (PostgreSQL)

Mae'r graff hwn yn dangos y gwahaniaeth rhwng ClickhouseDB a clickhousedb_fdw. Ar gyfer y rhan fwyaf o ymholiadau, nid yw gorbenion FDW mor uchel ac nid yw'n arwyddocaol o gwbl, ac eithrio ar gyfer C12. Mae'r ymholiad hwn yn cynnwys uniadau a chymal ORDER BY. Oherwydd y cymal ORDER BY, nid yw GROUP/BY ac ORDER BY yn cael eu gwthio i lawr i ClickHouse.

Yn Nhabl 2, gwelwn naid mewn amser yn ymholiadau Q12 a Q13. Unwaith eto, mae hyn yn cael ei achosi gan y cymal ORDER BY. I gadarnhau hyn, rhedais ymholiadau Q-14 a Q-15 gyda a heb y cymal ORDER BY. Heb y cymal ORDER BY, yr amser cwblhau yw 259 ms, tra gyda'r cymal ORDER BY, mae'n 1364212 ms. I ddadfygio'r ymholiad hwn, rwy'n egluro'r ddau ymholiad, a dangosir y canlyniadau yma.

C15: Heb y Cymal GORCHYMYN GAN

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";

C15: Ymholiad Heb Gymal ORDER BY

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)

C14: Ymholiad Gyda Chymal ORDER BY

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";

C14: Cynllun Ymholiad gyda Chymal ORDER BY

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)

Allbwn

Mae canlyniadau'r arbrofion hyn yn dangos bod ClickHouse yn cynnig perfformiad gwirioneddol dda, ac mae clickhousedb_fdw yn dod â manteision perfformiad ClickHouse i PostgreSQL. Er bod rhywfaint o orbenion wrth ddefnyddio clickhousedb_fdw, mae'n ddibwys ac yn gymharol â'r perfformiad a gyflawnir wrth redeg yn frodorol ar gronfa ddata ClickHouse. Mae hyn hefyd yn cadarnhau bod fdw yn PostgreSQL yn darparu canlyniadau rhagorol.

Sgwrs Telegram ar Clickhouse https://t.me/clickhouse_ru
Sgwrs Telegram PostgreSQL https://t.me/pgsql

Ffynhonnell: hab.com

Prynu gwesteio dibynadwy ar gyfer gwefannau sydd â diogelwch DDoS, gweinyddwyr VPS VDS 🔥 Prynu cynnal gwefannau dibynadwy gyda diogelwch DDoS, gweinyddion VPS VDS | ProHoster