Yn yr astudiaeth hon, roeddwn i eisiau gweld pa welliannau perfformiad y gellid eu cyflawni trwy ddefnyddio ffynhonnell ddata ClickHouse yn hytrach na PostgreSQL. Rwy'n gwybod y buddion cynhyrchiant a gaf o ddefnyddio ClickHouse. A fydd y buddion hyn yn parhau os byddaf yn cyrchu ClickHouse o PostgreSQL gan ddefnyddio Lapiwr Data Tramor (FDW)?
Yr amgylcheddau cronfa ddata a astudiwyd yw PostgreSQL v11, clickhousedb_fdw a chronfa ddata ClickHouse. Yn y pen draw, o PostgreSQL v11 byddwn yn rhedeg amrywiol ymholiadau SQL wedi'u cyfeirio trwy ein clickhousedb_fdw i gronfa ddata ClickHouse. Yna byddwn yn gweld sut mae perfformiad FDW yn cymharu â'r un ymholiadau sy'n rhedeg yn PostgreSQL brodorol a ClickHouse brodorol.
Cronfa Ddata Clickhouse
Mae ClickHouse yn system rheoli cronfa ddata golofnog ffynhonnell agored a all gyflawni perfformiad 100-1000 gwaith yn gyflymach na dulliau cronfa ddata traddodiadol, sy'n gallu prosesu dros biliwn o resi mewn llai nag eiliad.
Cliciwchhousedb_fdw
clickhousedb_fdw - Mae'r deunydd lapio data allanol ar gyfer cronfa ddata ClickHouse, neu FDW, yn brosiect ffynhonnell agored gan Percona. .
.
Fel y gwelwch, mae hyn yn darparu FDW ar gyfer ClickHouse sy'n caniatáu SELECT o gronfa ddata ClickHouse o'r gweinydd PostgreSQL v11, a INSERT INTO.
Mae FDW yn cefnogi nodweddion uwch fel agreg ac ymuno. Mae hyn yn gwella perfformiad yn sylweddol trwy ddefnyddio adnoddau'r gweinydd pell 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 edafedd
- Cof: 256GB o RAM
- Storio: Samsung SM863 1.9TB Enterprise SSD
- System ffeiliau: ext4/xfs
- OS: Linux sblade01 4.15.0-42-generig #45~16.04.1-Ubuntu
- PostgreSQL: fersiwn 11
Profion meincnod
Yn lle defnyddio rhywfaint o set ddata a gynhyrchir gan beiriannau ar gyfer y prawf hwn, gwnaethom ddefnyddio'r data "Cynhyrchedd yn ôl Amser Gweithredwr a Adroddir gan Amser" rhwng 1987 a 2018. Gallwch gael mynediad at y data .
Maint y gronfa ddata yw 85 GB, gan ddarparu un tabl o 109 o golofnau.
Ymholiadau Meincnodi
Dyma'r ymholiadau a ddefnyddiais i gymharu ClickHouse, clickhousedb_fdw a PostgreSQL.
Q#
Ymholiad Yn Cynnwys Agregau a Grŵp Gan
Q1
DEWIS DiwrnodOfWythnos, cyfrif(*) AS c O ontime LLE Blwyddyn >= 2000 A Blwyddyn <= GRŴP 2008 GAN Ddiwrnod O'r Wythnos GORCHYMYN GAN c DESC;
Q2
DEWIS DiwrnodOfWythnos, cyfrwch(*) AS c O ontime LLE Oedi Oedi>10 A Blwyddyn>= 2000 A Blwyddyn <= 2008 GRŴP FEL DiwrnodOfWythnos GORCHYMYN GAN c DESC;
Q3
DEWIS Tarddiad, cyfrif(*) UG c O onamser LLE Oedi Oedi>10 A Blwyddyn>= 2000 A Blwyddyn <= 2008 GRŴP YN ÔL GORCHYMYN Tarddiad GAN c CYFYNGIAD DESC 10;
Q4
SELECT Carrier, cyfrif () O onamser LLE Oedi Deli>10 A Blwyddyn = 2007 GRŴP TRWY ORCHYMYN Cludydd TRWY gyfrif() DESC;
Q5
SELECT a.Carrier, c, c2, c1000/c2 fel c3 O ( SELECT Carrier, cyfrif () AS c O ARamser LLE Oedi>10 A Blwyddyn=2007 GRŴP GAN Gludwr ) YMUNIAD FEWNOL ( DEWIS Cludwr, cyfrif (*) UG c2 O ARamser LLE Blwyddyn=2007 GRŴP GAN Gludiwr)b ar a.Carrier=b.Carrier ORDER GAN c3 DESC;
Q6
SELECT a.Carrier, c, c2, c1000/c2 fel c3 O ( SELECT Carrier, cyfrif () AS c O AR Amser LLE Oedi>10 A Blwyddyn >= 2000 A Blwyddyn <= 2008 GRŴP GAN cludwr) YMUNIAD MEWNOL ( DEWIS Cludwr, cyfrif(*) AS c2 O ARamser LLE Blwyddyn >= 2000 A Blwyddyn <= 2008 GRŴP GAN Cludydd ) b ar a.Carrier=b.Carrier GORCHYMYN GAN c3 DESC;
Q7
SELECT Carrier, cyf(DepDelay) * 1000 UG c3 O ontime LLE Blwyddyn >= 2000 A Blwyddyn <= 2008 GRWP GAN Carrier;
Q8
DEWIS Blwyddyn, cyf(Oedi Oedi) O GRŴP onamser FIS Blwyddyn;
Q9
dewis Blwyddyn, cyfrif(*) fel c1 o grŵp onamser fesul Blwyddyn;
Q10
DEWISWCH cyf(cnt) O (DEWIS Blwyddyn,Mis,cyfrif(*) UG cnt O ontime LLE DepDel15=1 GRŴP YN ÔL Blwyddyn,Mis) a;
Q11
dewiswch cyf(c1) o (dewiswch Flwyddyn,Mis,cyfrif(*) fel c1 o grŵp ontime fesul Blwyddyn,Mis) a;
Q12
DEWISWCH OriginCityName, DestCityName, cyfrif(*) UG c O GRŴP ontime GAN OriginCityName, DestCityName GORCHYMYN GAN c DESC TERFYN 10;
Q13
DEWISWCH OriginCityName, cyfrif(*) FEL C O'R GRŴP onamser GAN ORiginCityName GORCHYMYN GAN c TERFYN DESC 10;
Ymholiad Yn Cynnwys Ymuno
Q14
DEWISWCH Flwyddyn, c1/c2 GAN ( dewiswch Blwyddyn , cyfrif ()1000 fel c1 o aramser LLE Oedi Oedi>10 GRŴP FLYNYDDOL) YMUNIAD MEWNOL (dewiswch Flwyddyn, cyfrwch(*) fel c2 o ontime GRŴP YN ÔL Blwyddyn ) b ar a.Blwyddyn=b.Blwyddyn ORCHYMYN a.Blwyddyn;
Q15
DEWISWCH a.”Blwyddyn”, c1/c2 GAN (dewiswch “Blwyddyn”, cyfrif()1000 fel c1 O ffontime LLE “Oedi Oedi”>10 GRŴP ERBYN “Blwyddyn”) YMUNIAD MEWNOL (dewiswch “Blwyddyn”, cyfrif(*) fel c2 O GRŴP ffontime ERBYN “Blwyddyn”) b ar a.” Blwyddyn” = b. "Blwyddyn";
Tabl-1: Ymholiadau a ddefnyddir yn y meincnod
Ymholiad am ddienyddiadau
Dyma ganlyniadau pob un o'r ymholiadau wrth eu rhedeg mewn gwahanol leoliadau cronfa ddata: PostgreSQL gyda mynegeion a hebddynt, 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: Yr amser a gymerwyd i weithredu'r ymholiadau a ddefnyddiwyd yn y meincnod
Gweld canlyniadau
Mae'r graff yn dangos yr amser cyflawni ymholiad mewn milieiliadau, mae'r echelin X yn dangos rhif yr ymholiad o'r tablau uchod, ac mae'r echel Y yn dangos yr amser gweithredu mewn milieiliadau. Dangosir canlyniadau ClickHouse a data a gafwyd o postgres gan ddefnyddio clickhousedb_fdw. O'r tabl gallwch weld bod gwahaniaeth enfawr rhwng PostgreSQL a ClickHouse, ond ychydig iawn o wahaniaeth rhwng ClickHouse a clickhousedb_fdw.
Mae'r graff hwn yn dangos y gwahaniaeth rhwng ClickhouseDB a clickhousedb_fdw. Yn y rhan fwyaf o ymholiadau, nid yw gorbenion FDW mor uchel â hynny a phrin ei fod yn arwyddocaol ac eithrio C12. Mae'r ymholiad hwn yn cynnwys uno a chymal GORCHYMYN GAN. Oherwydd y cymal GORCHYMYN GAN GRŴP / GAN, nid yw ORDER BY yn disgyn i ClickHouse.
Yn Nhabl 2 gwelwn y naid amser mewn ymholiadau C12 a C13. Eto, mae hyn yn cael ei achosi gan y cymal GORCHYMYN GAN. I gadarnhau hyn, rhedais ymholiadau Q-14 a Q-15 gyda'r cymal GORCHYMYN GAN a hebddo. Heb y cymal GORCHYMYN TRWY yr amser cwblhau yw 259ms a gyda'r cymal GORCHYMYN WRTH 1364212. I ddadfygio'r ymholiad hwn rwy'n egluro'r ddau ymholiad a dyma ganlyniadau'r esboniad.
C15: Heb Orchymyn GAN Gymal
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 Orchymyn GAN Gymal
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 GORCHYMYN GAN Gymal
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 GORCHYMYN GAN Gymal
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 da iawn, ac mae clickhousedb_fdw yn cynnig buddion perfformiad ClickHouse o PostgreSQL. Er bod rhywfaint o orbenion wrth ddefnyddio clickhousedb_fdw, mae'n ddibwys ac yn debyg i'r perfformiad a gyflawnwyd trwy redeg yn frodorol ar gronfa ddata ClickHouse. Mae hyn hefyd yn cadarnhau bod fdw yn PostgreSQL yn darparu canlyniadau rhagorol.
Sgwrs Telegram trwy Clickhouse
Sgwrs Telegram gan ddefnyddio PostgreSQL
Ffynhonnell: hab.com