I roto i tenei rangahau, i hiahia ahau ki te kite he aha nga whakapainga mahi ka taea ma te whakamahi i te puna raraunga ClickHouse kaua ko PostgreSQL. E mohio ana ahau ki nga hua hua ka whiwhi ahau mai i te whakamahi ClickHouse. Ka haere tonu enei painga ki te uru ahau ki te ClickHouse mai i PostgreSQL ma te whakamahi i te Taapaki Raraunga Tawahi (FDW)?
Ko nga taiao papaa raraunga i akohia ko PostgreSQL v11, clickhousedb_fdw me ClickHouse pātengi raraunga. I te mutunga, mai i te PostgreSQL v11 ka whakahaerehia e matou nga momo patai SQL ka tukuna mai i to maatau clickhousedb_fdw ki te paataka korero ClickHouse. Ka kite tatou me pehea te whakataurite o nga mahi a FDW ki nga patai e rere ana i roto i te PostgreSQL taketake me te ClickHouse taketake.
Pātengi Raraunga Clickhouse
Ko te ClickHouse he punaha whakahaere papaaarangi puna tuwhera ka taea te whakatutuki i nga mahi 100-1000 nga wa tere atu i nga huarahi tuku iho, ka taea te tukatuka i runga i te piriona rarangi i roto i te iti iho i te rua.
Clickhousedb_fdw
clickhousedb_fdw - Ko te takai raraunga o waho mo te patengi raraunga ClickHouse, FDW ranei, he kaupapa puna tuwhera mai i Percona. .
.
Ka kite koe, ka whakaratohia he FDW mo ClickHouse e taea ai te SELECT mai, me te INSERT INTO, te paataka ClickHouse mai i te PostgreSQL v11 tūmau.
Ka tautokohia e FDW nga ahuatanga matatau penei i te whakahiato me te hono. Ka tino pai ake te mahi ma te whakamahi i nga rauemi o te tūmau mamao mo enei mahi whakakaha rawa.
Te taiao tohu
- tūmau Supermicro:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 turanga / 28 matua / 56 miro
- Mahinga: 256GB o RAM
- Rokiroki: Samsung SM863 1.9TB Enterprise SSD
- Pūnaha Kōnae: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: putanga 11
Nga whakamatautau tohu
Engari i te whakamahi i etahi huinga raraunga i hangaia e te miihini mo tenei whakamatautau, i whakamahia e matou nga raraunga "Productivity by Time Reported Operator Time" mai i te 1987 ki te 2018. Ka taea e koe te uru ki nga raraunga .
He 85 GB te rahinga putunga raraunga, e whakarato ana i te tepu kotahi o nga pou 109.
Uiui Paerewa
Anei nga patai i whakamahia e au ki te whakataurite i a ClickHouse, clickhousedb_fdw me PostgreSQL.
Q#
Uiui kei roto nga whakahiato me te whakaropu a
Q1
KŌWHIRINGA RāOfWeek, tatau(*) AS c FROM inawa WHERE Tau >= 2000 ME Tau <= 2008 Rōpū MA DayOfWeek ORDER NA c DESC;
Q2
KŌWHIWHIRIA RāOfWeek, tatau(*) AS c MAI i te wā WHERE DepDelay>10 ME Tau >= 2000 ME Tau <= 2008 Rōpū MA DayOfWeek ORDER NA c DESC;
Q3
SELECT Origin, tatau(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 Rōpū BY Origin ORDER BY c DESC LIMIT 10;
Q4
Tīpakohia Kaikawe, tatau() MAI i te waa WHERE DepDelay>10 ME te Tau = 2007 RUPAPA MA TE Kaikawe ORDER MA TE tatau() DESC;
Q5
PILIhia a.Kaikawe, c, c2, c1000/c2 hei c3 MAI ( TĪPAHI Kawe, tatau() AS c MAI i te wa WHERE DepDelay>10 ME Tau=2007 RUPAPA MA TE Kaikawe ) a ROTO KAUPAPA ( TIKA KAUPAPA, tatau(*) AS c2 MAI i te wa I WHERE Tau=2007 RUPAPA MA TE Kaikawe)b i runga i a.Kaikawe=b.Kaikawe OTA NA c3 DESC;
Q6
PILIhia a.Kaikawe, c, c2, c1000/c2 hei c3 MAI ( TĪPAHI Kawe, tatau() AS c MAI i te waa WHERE DepDelay>10 ME Te Tau >= 2000 ME Te Tau <= 2008 RUPAPA MA TE Kaikauwhau) he KAUPAPA KAUPAPA ( KORERO Kaikawe, tatau(*) AS c2 MAI i te waa WHERE Tau >= 2000 ME te Tau <= 2008 Rōpū MA Kaikawe ) b i runga i a.Kaikawe=b.Kaikawe ORDER BY c3 DESC;
Q7
SELECT Kaiwaea, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Tau >= 2000 AND Tau <= 2008 RUPAPA BY Kaikawe;
Q8
TŌTAHI Tau, taurite(DepDelay) MAI i te wā o te Rōpū ma te Tau;
Q9
tīpakohia te Tau, tatau(*) hei c1 mai i te roopu i runga i te tau;
Q10
KŌWHIWHI Avg(cnt) MAI (Kīwhiria te Tau,Marama, tatau(*) AS cnt MAI i te wā WHERE DepDel15=1 Rōpū ma te Tau,Marama) a;
Q11
tīpakohia te toharite(c1) mai i te (tīpakohia te Tau,Marama, tatau(*) hei c1 mai i te roopu o te waa ma te Tau,Marama) a;
Q12
KŌWHIWHI IA OriginCityName, DestCityName, tatau(*) AS c MAI i te wā RŌPUNA NA OriginCityName, DestCityName ORATE NA c DESC LIMIT 10;
Q13
Patohia te IngoaOriginCity, tatau(*) AS c mai i te wa o te Rōpū NA OriginCityName ORDER BY c DESC LIMIT 10;
He Hononga kei roto Uiui
Q14
Patohia a.Tau, c1/c2 MAI ( tohua te Tau, tatau()1000 as c1 from ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (tīpakohia te Tau, tatau(*) as c2 from ontime GROUP BY Year ) b on a.Year=b.Year ORDER BY a.Year;
Q15
Patohia a."Tau", c1/c2 MAI ( tohua "Tau", tatau()1000 as c1 FROM fontime WHERE “DepDelay”>10 ROUP BY “Year”) a INNER JOIN (tīpakohia “Year”, tatau(*) as c2 FROM fontime GROUP BY “Year” ) b on a.”Year”=b. "Tau";
Ripanga-1: Uiui e whakamahia ana hei tohu tohu
Nga mahi patai
Anei nga hua o ia patai i te wa e whakahaerea ana i roto i nga tautuhinga papaaarangi rereke: PostgreSQL me nga tohu tohu kore, ClickHouse taketake me clickhousedb_fdw. Ka whakaatuhia te wa i roto i nga manomano.
Q#
PostgreSQL
PostgreSQL (Kua Taupuhia)
PaateneToko
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
Ripanga-1: Te wa i pau ki te mahi i nga patai i whakamahia hei tohu tohu
Tirohia nga hua
Ko te kauwhata e whakaatu ana i te wa mahi uinga i roto i te mitamanono, ko te tuaka X e whakaatu ana i te tau patai mai i nga ripanga o runga ake nei, a ko te tuaka Y e whakaatu ana i te wa mahi i roto i te mirimiakona. Ko nga hua a ClickHouse me nga raraunga i tangohia mai i nga poupou ma te whakamahi clickhousedb_fdw ka whakaatuhia. Mai i te ripanga ka kite koe he nui te rereketanga i waenga i te PostgreSQL me te ClickHouse, engari he iti noa te rereketanga i waenga i te ClickHouse me te clickhousedb_fdw.
Ko tenei kauwhata e whakaatu ana i te rereketanga o ClickhouseDB me clickhousedb_fdw. I roto i te nuinga o nga patai, ko te FDW i runga ake karekau i te teitei, he iti noa te mea nui engari mo Q12. Kei roto i tenei patai nga hono me tetahi rara ORDER BY. Na te ORDER BY GROUP/BY rara, ORDER BY e kore e taka iho ki ClickHouse.
I te Ripanga 2 ka kite tatou i te peke wa i nga patai Q12 me Q13. Ano ano, na te rara ORDER BY. Hei whakaū i tenei, i whakahaere au i nga patai Q-14 me Q-15 me te kore o te ORDER BY rara. Ki te kore te ORDER BY clause ko te 259ms te wa whakaoti, me te ORDER BY clause ko te 1364212. Hei patuiro i tenei patai kei te whakamarama au i nga patai e rua, a koinei nga hua o te whakamarama.
Q15: Karekau he Ota MA te Rarangi
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";U15: Uiui Karekau he Ota MA te Rarangi
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)U14: Uiui me te ORDER BY Clause
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";U14: Mahere Uiui me te ORDER BY Clause
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)mutunga
Ko nga hua o enei whakamatautau e whakaatu ana ko ClickHouse e tuku ana i nga mahi tino pai, a ko clickhousedb_fdw e tuku ana i nga painga mahi a ClickHouse mai i PostgreSQL. Ahakoa he nui ake i te wa e whakamahi ana i te clickhousedb_fdw, he iti noa iho, he rite ki nga mahi i tutuki ma te whakahaere taketake i runga i te paataka korero ClickHouse. Ka whakau hoki tenei ko te fdw i PostgreSQL he pai nga hua.
Te korerorero a Telegram ma te Clickhouse
Te whakawhitiwhiti korero ma te whakamahi i te PostgreSQL
Source: will.com