Niini nga pagtuon, gusto nakong makita kung unsa nga mga pag-uswag sa pasundayag ang mahimong makab-ot pinaagi sa paggamit sa tinubdan sa datos sa ClickHouse kaysa sa PostgreSQL. Nahibal-an ko ang mga benepisyo sa pagka-produktibo nga akong makuha gikan sa paggamit sa ClickHouse. Magpadayon ba kini nga mga benepisyo kung ma-access nako ang ClickHouse gikan sa PostgreSQL gamit ang Foreign Data Wrapper (FDW)?
Ang database environment nga gitun-an mao ang PostgreSQL v11, clickhousedb_fdw ug ClickHouse database. Sa katapusan, gikan sa PostgreSQL v11 magpadagan kami sa lainlaing mga pangutana sa SQL nga gipaagi sa among clickhousedb_fdw hangtod sa database sa ClickHouse. Atong tan-awon kung giunsa pagtandi ang pasundayag sa FDW sa parehas nga mga pangutana nga nagdagan sa lumad nga PostgreSQL ug lumad nga ClickHouse.
Database sa Clickhouse
Ang ClickHouse usa ka open source columnar database management system nga makakab-ot sa performance nga 100-1000 ka beses nga mas paspas kay sa tradisyonal nga database approaches, nga makahimo sa pagproseso sa kapin sa usa ka bilyon nga mga laray sa ubos sa usa ka segundo.
Clickhousedb_fdw
clickhousedb_fdw - Ang external data wrapper para sa ClickHouse database, o FDW, usa ka open source nga proyekto gikan sa Percona. .
.
Sama sa imong makita, naghatag kini og FDW para sa ClickHouse nga nagtugot sa PILI gikan sa, ug INSERT INTO, ang ClickHouse database gikan sa PostgreSQL v11 server.
Gisuportahan sa FDW ang mga advanced nga bahin sama sa aggregate ug pag-apil. Mahinungdanon nga gipauswag niini ang pasundayag pinaagi sa paggamit sa mga kahinguhaan sa hilit nga server alang sa kini nga mga operasyon nga kusog sa kapanguhaan.
Benchmark nga palibot
- Supermicro server:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 sockets / 28 cores / 56 threads
- Pahinumdom: 256GB nga RAM
- Pagtipig: Samsung SM863 1.9TB Enterprise SSD
- Filesystem: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: bersyon 11
Mga pagsulay sa benchmark
Imbis nga gamiton ang pipila ka set sa datos nga hinimo sa makina para sa kini nga pagsulay, gigamit namon ang datos nga "Produktibidad sa Oras nga Gi-report sa Oras sa Operator" gikan sa 1987 hangtod 2018. Mahimo nimong ma-access ang datos .
Ang gidak-on sa database mao ang 85 GB, nga naghatag usa ka lamesa nga adunay 109 nga mga kolum.
Mga Pangutana sa Benchmark
Ania ang mga pangutana nga akong gigamit sa pagtandi sa ClickHouse, clickhousedb_fdw ug PostgreSQL.
Q#
Ang Pangutana Naglangkob sa mga Aggregate ug Group By
Q1
PILI DayOfWeek, ihap(*) AS c GIKAN sa ontime DIIN Tuig >= 2000 UG Tuig <= 2008 GROUP NI DayOfWeek ORDER NI c DESC;
Q2
PILI DayOfWeek, ihap(*) AS c GIKAN sa ontime DIIN DepDelay>10 UG Tuig >= 2000 UG Tuig <= 2008 GROUP BY DayOfWeek ORDER NI c DESC;
Q3
SELECT Origin, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;
Q4
SELECT Carrier, ihap() GIKAN sa ontime DIIN DepDelay>10 UG Tuig = 2007 GROUP BY Carrier ORDER BY count() DESC;
Q5
PILI a.Carrier, c, c2, c1000/c2 isip c3 GIKAN ( SELECT Carrier, ihap() AS c GIKAN sa ontime DIIN DepDelay>10 UG Year=2007 GROUP BY Carrier ) a INNER JOIN ( SELECT Carrier,count(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY Carrier)b on a.Carrier=b.Carrier ORDER NI c3 DESC;
Q6
PILI a.Carrier, c, c2, c1000/c2 isip c3 GIKAN ( SELECT Carrier, ihap() AS c GIKAN sa ontime DIIN DepDelay>10 UG Tuig >= 2000 UG Tuig <= 2008 GROUP BY Carrier) a INNER JOIN ( SELECT Carrier, count(*) AS c2 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier ) b on a.Carrier=b.Carrier ORDER PINAAGI sa c3 DESC;
Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 GIKAN sa ontime DIIN Tuig >= 2000 UG Tuig <= 2008 GROUP BY Carrier;
Q8
PILI Tuig, avg(DepDelay) GIKAN sa ontime GROUP BY Year;
Q9
pilia ang Tuig, ihap(*) isip c1 gikan sa ontime nga grupo sa Tuig;
Q10
PILI ang avg(cnt) GIKAN (PILI nga Tuig,Bulan,ihap(*) AS cnt GIKAN sa ontime DIIN DepDel15=1 GROUP BY Tuig,Bulan) a;
Q11
pilia ang avg(c1) gikan sa (pilia ang Tuig,Bulan,ihap(*) isip c1 gikan sa ontime nga grupo sa Tuig,Bulan) a;
Q12
PILI OriginCityName, DestCityName, ihap(*) AS c GIKAN sa ontime GROUP NI OriginCityName, DestCityName ORDER NI c DESC LIMIT 10;
Q13
PILI OriginCityName, ihap(*) AS c GIKAN sa ontime GROUP PINAAGI OriginCityName ORDER NI c DESC LIMIT 10;
Ang Pangutana Naglangkob sa mga Apil
Q14
PILI a.Tuig, c1/c2 GIKAN ( pilia ang Tuig, ihap()1000 as c1 from ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (pilia ang Year, count(*) as c2 from ontime GROUP BY Year ) b on a.Year=b.Year ORDER BY a.Year;
Q15
PILI a.”Tuig”, c1/c2 GIKAN ( pilia ang “Year”, ihap()1000 as c1 FROM fontime DIIN “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (pilia “Year”, count(*) as c2 GIKAN fontime GROUP BY “Year” ) b on a.”Year”=b. "Tuig";
Table-1: Mga pangutana nga gigamit sa benchmark
Mga pagpatay sa pangutana
Ania ang mga resulta sa matag usa sa mga pangutana kung modagan sa lainlaing mga setting sa database: PostgreSQL nga adunay ug walay mga indeks, lumad nga ClickHouse ug clickhousedb_fdw. Gipakita ang oras sa milliseconds.
Q#
PostgreSQL
PostgreSQL (Naka-indeks)
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
Talaan-1: Panahon nga gikuha aron mapatuman ang mga pangutana nga gigamit sa benchmark
Tan-awa ang mga resulta
Gipakita sa graph ang oras sa pagpatuman sa pangutana sa millisecond, ang X axis nagpakita sa numero sa pangutana gikan sa mga lamesa sa ibabaw, ug ang Y axis nagpakita sa oras sa pagpatuman sa milliseconds. Ang mga resulta sa ClickHouse ug datos nga nakuha gikan sa mga postgres gamit ang clickhousedb_fdw gipakita. Gikan sa lamesa imong makita nga adunay dako nga kalainan tali sa PostgreSQL ug ClickHouse, apan gamay nga kalainan tali sa ClickHouse ug clickhousedb_fdw.
Kini nga graph nagpakita sa kalainan tali sa ClickhouseDB ug clickhousedb_fdw. Sa kadaghanang mga pangutana, ang overhead sa FDW dili kaayo taas ug dili kaayo importante gawas sa Q12. Kini nga pangutana naglakip sa pag-apil ug usa ka ORDER BY clause. Tungod sa ORDER BY GROUP/BY clause, ang ORDER BY dili ihulog sa ClickHouse.
Sa Talaan 2 atong makita ang paglukso sa oras sa mga pangutana Q12 ug Q13. Pag-usab, kini tungod sa ORDER BY clause. Aron makumpirma kini, gipadagan nako ang mga pangutana Q-14 ug Q-15 nga adunay ug wala ang ORDER BY clause. Kung wala ang ORDER BY clause ang oras sa pagkompleto mao ang 259ms ug uban ang ORDER BY clause kini 1364212. Aron ma-debug kini nga pangutana akong gipatin-aw ang mga pangutana ug ania ang mga resulta sa pagpatin-aw.
Q15: Kung walay 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";Q15: Pangutana nga Walay ORDER BY Clause
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: Pangutana Uban sa 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";Q14: Plano sa Pangutana nga adunay 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)konklusyon
Ang mga resulta niini nga mga eksperimento nagpakita nga ang ClickHouse nagtanyag og maayo kaayo nga performance, ug ang clickhousedb_fdw nagtanyag sa mga benepisyo sa performance sa ClickHouse gikan sa PostgreSQL. Samtang adunay pipila ka mga overhead sa diha nga ang paggamit sa clickhousedb_fdw, kini gipasagdan ug ikatandi sa pasundayag nga nakab-ot pinaagi sa pagdagan nga lumad sa ClickHouse database. Gipamatud-an usab niini nga ang fdw sa PostgreSQL naghatag og maayo nga mga resulta.
Telegram chat pinaagi sa Clickhouse
Telegram chat gamit ang PostgreSQL
Source: www.habr.com