Í þessari rannsókn vildi ég sjá hvaða frammistöðubætum væri hægt að ná með því að nota ClickHouse gagnagjafa frekar en PostgreSQL. Ég þekki framleiðniávinninginn sem ég fæ af því að nota ClickHouse. Mun þessi ávinningur halda áfram ef ég opna ClickHouse frá PostgreSQL með því að nota erlend gagnaumbúðir (FDW)?
Gagnagrunnsumhverfið sem rannsakað er eru PostgreSQL v11, clickhousedb_fdw og ClickHouse gagnagrunnur. Að lokum, frá PostgreSQL v11, munum við keyra ýmsar SQL fyrirspurnir sem fluttar eru í gegnum clickhousedb_fdw okkar í ClickHouse gagnagrunninn. Við munum síðan sjá hvernig árangur FDW er í samanburði við sömu fyrirspurnir sem keyra í innfæddum PostgreSQL og innfæddum ClickHouse.
Clickhouse gagnagrunnur
ClickHouse er opinn uppspretta dálkagagnagrunnsstjórnunarkerfi sem getur náð frammistöðu 100-1000 sinnum hraðar en hefðbundnar gagnagrunnsaðferðir, sem geta unnið úr yfir milljarði raða á innan við sekúndu.
Clickhousedb_fdw
clickhousedb_fdw - Ytri gagnaumbúðir fyrir ClickHouse gagnagrunninn, eða FDW, er opinn uppspretta verkefni frá Percona. .
.
Eins og þú munt sjá gefur þetta FDW fyrir ClickHouse sem gerir kleift að VELJA frá og SETJA INN Í ClickHouse gagnagrunninn frá PostgreSQL v11 þjóninum.
FDW styður háþróaða eiginleika eins og samansafn og sameiningu. Þetta bætir verulega afköst með því að nota auðlindir ytra netþjónsins fyrir þessar auðlindafrekar aðgerðir.
Viðmið umhverfi
- Supermicro þjónn:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 innstungur / 28 kjarna / 56 þræðir
- Minni: 256GB af vinnsluminni
- Geymsla: Samsung SM863 1.9TB Enterprise SSD
- Skráarkerfi: ext4/xfs
- Stýrikerfi: Linux smblade01 4.15.0-42-almennt #45~16.04.1-Ubuntu
- PostgreSQL: útgáfa 11
Viðmiðunarpróf
Í stað þess að nota eitthvað vélrænt gagnasett fyrir þessa prófun, notuðum við gögnin „Framleiðni eftir tíma sem tilkynnt er um rekstraraðila“ frá 1987 til 2018. Þú getur nálgast gögnin .
Stærð gagnagrunnsins er 85 GB, sem gefur eina töflu með 109 dálkum.
Viðmiðunarfyrirspurnir
Hér eru fyrirspurnirnar sem ég notaði til að bera saman ClickHouse, clickhousedb_fdw og PostgreSQL.
Q#
Fyrirspurn inniheldur samantekt og hóp eftir
Q1
VELDU DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2
VELDU DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 OG Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q3
VELJU Uppruni, telja(*) AS c FROM á tíma WHERE DepDelay>10 OG Ár >= 2000 OG Ár <= 2008 RÓPAN EFTIR uppruna RÖÐUN EFTIR c DESC LIMIT 10;
Q4
VELJA flutningsaðila, telja() FRÁ á réttum tíma WHERE DepDelay>10 OG Ár = 2007 GRÓPUR EFTIR flutningsaðila PÖNNUN EFTIR fjölda() DESC;
Q5
SELECT a.Carrier, c, c2, c1000/c2 sem c3 FRÁ (VELJA flutningsaðila, telja() AS c FROM ontime WHERE DepDelay>10 OG Year=2007 GROUP BY Carrier ) a INNER JOIN ( SELECT FROM á tíma(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY Carrier)b on a.Carrier=b.Carrier ORDER BY c3 DESC;
Q6
SELECT a.Carrier, c, c2, c1000/c2 sem c3 FRÁ (VELJA flutningsaðila, telja() AS c FROM á tíma WHERE DepDelay>10 OG Ár >= 2000 OG Ár <= 2008 HÓPUR EFTIR flutningsaðila) a INNER JOIN (VELDU flutningsaðila, teldu(*) AS c2 FROM á tíma HVAR Ár >= 2000 OG Ár <= 2008 HÓPUR EFTA Flytjandi ) b á a.Carrier=b.Carrier ORDER BY c3 DESC;
Q7
SELECT flutningsaðili, avg(DepDelay) * 1000 AS c3 FROM á tíma HVAR Ár >= 2000 OG Ár <= 2008 HÓPUR EFTIR flutningsaðila;
Q8
VELJA ár, meðaltal(DepDelay) FRÁ tímanlega HÓPUR EFTI ár;
Q9
veldu ár, teldu(*) sem c1 úr tímahópi eftir ár;
Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,month) a;
Q11
velja meðaltal(c1) frá (velja ár, mánuð, telja(*) sem c1 úr tímahópi eftir ár, mánuð) a;
Q12
SELECT 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;
Fyrirspurn inniheldur tengingar
Q14
SELECT a.Year, c1/c2 FROM ( veldu Year, count()1000 sem c1 frá tímanlega HVAR DepDelay>10 Hópur eftir ári) a INNER JOIN (veljið ár, teljið(*) sem c2 frá tímanlega GROUP BY Year ) b á a.Year=b.Year ORDER BY a.Year;
Q15
SELECT a."Year", c1/c2 FROM ( veldu "Year", teldu()1000 sem c1 FRÁ fonttime WHERE “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (veljið “Year”, teldu(*) sem c2 FRÁ fonttime GROUP BY “Year” ) b á a.”Year”=b. "Ár";
Tafla-1: Fyrirspurnir notaðar í viðmiðun
Fyrirspurnaraftökur
Hér eru niðurstöður hverrar fyrirspurna þegar þær eru keyrðar í mismunandi gagnagrunnsstillingum: PostgreSQL með og án vísitölu, innfæddur ClickHouse og clickhousedb_fdw. Tími er sýndur í millisekúndum.
Q#
PostgreSQL
PostgreSQL (verðtryggt)
smellahús
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
Tafla-1: Tími sem það tekur að framkvæma fyrirspurnirnar sem notaðar eru í viðmiðun
Skoða niðurstöður
Línuritið sýnir framkvæmdartíma fyrirspurnarinnar í millisekúndum, X-ásinn sýnir fyrirspurnarnúmerið úr töflunum hér að ofan og Y-ásinn sýnir framkvæmdartímann í millisekúndum. ClickHouse niðurstöður og gögn sótt frá postgres með því að nota clickhousedb_fdw eru sýndar. Af töflunni má sjá að það er mikill munur á PostgreSQL og ClickHouse, en lágmarks munur á ClickHouse og clickhousedb_fdw.
Þetta línurit sýnir muninn á ClickhouseDB og clickhousedb_fdw. Í flestum fyrirspurnum er FDW kostnaðurinn ekki svo hár og er varla marktækur nema fyrir Q12. Þessi fyrirspurn inniheldur joins og ORDER BY ákvæði. Vegna ORDER BY GROUP/BY ákvæðisins fellur ORDER BY ekki niður í ClickHouse.
Í töflu 2 sjáum við tímastökkið í fyrirspurnum Q12 og Q13. Aftur, þetta stafar af ORDER BY ákvæðinu. Til að staðfesta þetta rak ég fyrirspurnir Q-14 og Q-15 með og án ORDER BY ákvæðisins. Án ORDER BY klausunnar er útfyllingartíminn 259 ms og með ORDER BY klausunni er það 1364212. Til að kemba þessa fyrirspurn er ég að útskýra báðar fyrirspurnirnar og hér eru niðurstöður skýringarinnar.
Q15: Án ORDER BY ákvæði
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: Fyrirspurn án ORDER BY ákvæði
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: Fyrirspurn með ORDER BY ákvæði
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: Fyrirspurnaráætlun með ORDER BY-ákvæði
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)Output
Niðurstöður þessara tilrauna sýna að ClickHouse býður upp á mjög góðan árangur og clickhousedb_fdw býður upp á frammistöðuávinninginn af ClickHouse frá PostgreSQL. Þó að það sé nokkur kostnaður þegar þú notar clickhousedb_fdw, þá er hann hverfandi og sambærilegur við frammistöðu sem næst með því að keyra innbyggt á ClickHouse gagnagrunninum. Þetta staðfestir líka að fdw í PostgreSQL gefur framúrskarandi árangur.
Telegram spjall í gegnum Clickhouse
Telegram spjall með PostgreSQL
Heimild: www.habr.com