Sa pag-aaral na ito, gusto kong makita kung anong mga pagpapahusay sa pagganap ang maaaring makamit sa pamamagitan ng paggamit ng pinagmumulan ng data ng ClickHouse kaysa sa PostgreSQL. Alam ko ang mga benepisyo sa pagiging produktibo na nakukuha ko sa paggamit ng ClickHouse. Magpapatuloy ba ang mga benepisyong ito kung maa-access ko ang ClickHouse mula sa PostgreSQL gamit ang isang Foreign Data Wrapper (FDW)?
Ang database environment na pinag-aralan ay PostgreSQL v11, clickhousedb_fdw at ClickHouse database. Sa huli, mula sa PostgreSQL v11 ay magpapatakbo kami ng iba't ibang mga query sa SQL na iruruta sa aming clickhousedb_fdw patungo sa database ng ClickHouse. Pagkatapos ay makikita natin kung paano inihahambing ang pagganap ng FDW sa parehong mga query na tumatakbo sa native na PostgreSQL at native na ClickHouse.
Clickhouse Database
Ang ClickHouse ay isang open source columnar database management system na maaaring makamit ang pagganap ng 100-1000 beses na mas mabilis kaysa sa tradisyonal na mga diskarte sa database, na may kakayahang magproseso ng higit sa isang bilyong row sa mas mababa sa isang segundo.
Clickhousedb_fdw
clickhousedb_fdw - Ang panlabas na data wrapper para sa ClickHouse database, o FDW, ay isang open source na proyekto mula sa Percona. .
.
Tulad ng makikita mo, nagbibigay ito ng FDW para sa ClickHouse na nagpapahintulot sa PUMILI mula sa, at INSERT INTO, ang database ng ClickHouse mula sa PostgreSQL v11 server.
Sinusuportahan ng FDW ang mga advanced na feature tulad ng aggregate at join. Ito ay makabuluhang nagpapabuti sa pagganap sa pamamagitan ng paggamit ng mga mapagkukunan ng malayong server para sa mga resource-intensive na operasyong ito.
Benchmark na kapaligiran
- Supermicro server:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 socket / 28 core / 56 thread
- Memorya: 256GB ng RAM
- Imbakan: 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 pagsubok sa benchmark
Sa halip na gumamit ng ilang set ng data na binuo ng machine para sa pagsubok na ito, ginamit namin ang data na "Productivity by Time Reported Operator Time" mula 1987 hanggang 2018. Maaari mong ma-access ang data .
Ang laki ng database ay 85 GB, na nagbibigay ng isang talahanayan ng 109 na mga hanay.
Mga Tanong sa Benchmark
Narito ang mga query na ginamit ko upang ihambing ang ClickHouse, clickhousedb_fdw at PostgreSQL.
Q#
Ang Query ay Naglalaman ng Mga Pinagsasama-sama at Pangkat Ayon
Q1
PUMILI ng DayOfWeek, bilangin(*) BILANG c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2
PUMILI ng DayOfWeek, bilangin(*) BILANG c MULA sa oras KUNG SAAN DepDelay>10 AT Taon >= 2000 AT Taon <= 2008 GROUP NG DayOfWeek ORDER NG 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
PUMILI ng Carrier, bilangin() MULA sa ontime WHERE DepDelay>10 AT Year = 2007 GROUP BY Carrier ORDER BY count() DESC;
Q5
PUMILI a.Carrier, c, c2, c1000/c2 bilang c3 FROM ( PUMILI ng Carrier, bilangin() BILANG c FROM ontime WHERE DepDelay>10 AND 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 NG c3 DESC;
Q6
PUMILI a.Carrier, c, c2, c1000/c2 bilang c3 FROM ( PUMILI ng Carrier, bilangin() BILANG c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 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 NG c3 DESC;
Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier;
Q8
PUMILI Taon, avg(DepDelay) MULA sa ontime GROUP BY Year;
Q9
piliin ang Taon, bilangin (*) bilang c1 mula sa ontime na pangkat ayon sa Taon;
Q10
PUMILI ng avg(cnt) MULA SA (PUMILI Taon,Buwan,bilang(*) BILANG cnt MULA sa ontime KUNG SAAN DepDel15=1 GROUP BY Taon,Buwan) a;
Q11
piliin ang avg(c1) mula sa (piliin ang Taon,Buwan,bilang(*) bilang c1 mula sa ontime na pangkat ayon sa Taon,Buwan) a;
Q12
PUMILI OriginCityName, DestCityName, bilangin(*) BILANG c MULA sa ontime GROUP NG OriginCityName, DestCityName ORDER NG c DESC LIMIT 10;
Q13
PUMILI NG OriginCityName, bilangin(*) BILANG c MULA sa ontime GROUP NG OriginCityName ORDER NG c DESC LIMIT 10;
Ang Query ay Naglalaman ng Mga Pagsasama
Q14
SELECT a. Year, c1/c2 FROM ( piliin ang Taon, bilangin()1000 bilang c1 mula sa ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (piliin ang Taon, bilangin(*) bilang c2 mula sa ontime GROUP BY Year ) b sa a.Year=b.Year ORDER BY a.Year;
Q15
PUMILI ng a.”Taon”, c1/c2 MULA SA ( piliin ang “Taon”, bilang()1000 bilang c1 FROM fontime WHERE “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (piliin ang “Year”, count(*) as c2 FROM fontime GROUP BY “Year” ) b on a.”Year”=b. "Taon";
Talahanayan-1: Mga query na ginamit sa benchmark
Mga pagpapatupad ng query
Narito ang mga resulta ng bawat isa sa mga query kapag tumakbo sa iba't ibang mga setting ng database: PostgreSQL na may at walang mga index, native na ClickHouse at clickhousedb_fdw. Ang oras ay ipinapakita sa millisecond.
Q#
PostgreSQL
PostgreSQL (Naka-index)
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
Talahanayan-1: Oras na kinuha upang maisagawa ang mga query na ginamit sa benchmark
Tingnan ang Mga Resulta
Ipinapakita ng graph ang oras ng pagpapatupad ng query sa mga millisecond, ipinapakita ng X axis ang numero ng query mula sa mga talahanayan sa itaas, at ipinapakita ng Y axis ang oras ng pagpapatupad sa mga millisecond. Ang mga resulta ng ClickHouse at data na nakuha mula sa mga postgres gamit ang clickhousedb_fdw ay ipinapakita. Mula sa talahanayan makikita mo na may malaking pagkakaiba sa pagitan ng PostgreSQL at ClickHouse, ngunit kaunting pagkakaiba sa pagitan ng ClickHouse at clickhousedb_fdw.
Ipinapakita ng graph na ito ang pagkakaiba sa pagitan ng ClickhouseDB at clickhousedb_fdw. Sa karamihan ng mga query, hindi ganoon kataas ang overhead ng FDW at hindi gaanong mahalaga maliban sa Q12. Kasama sa query na ito ang mga pagsali at isang ORDER BY clause. Dahil sa ORDER BY GROUP/BY clause, ang ORDER BY ay hindi bumababa sa ClickHouse.
Sa Talahanayan 2 nakikita natin ang paglukso ng oras sa mga query Q12 at Q13. Muli, ito ay sanhi ng ORDER BY clause. Upang kumpirmahin ito, nagpatakbo ako ng mga query Q-14 at Q-15 na may at walang ORDER BY clause. Kung wala ang ORDER BY clause ang oras ng pagkumpleto ay 259ms at kasama ang ORDER BY clause ito ay 1364212. Upang i-debug ang query na ito ay ipinapaliwanag ko ang parehong mga query at narito ang mga resulta ng paliwanag.
Q15: Nang walang 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: Query Nang Walang 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: Query na may 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: Query Plan na may 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)Pagbubuhos
Ang mga resulta ng mga eksperimentong ito ay nagpapakita na ang ClickHouse ay nag-aalok ng talagang mahusay na pagganap, at ang clickhousedb_fdw ay nag-aalok ng mga benepisyo sa pagganap ng ClickHouse mula sa PostgreSQL. Bagama't mayroong ilang overhead kapag gumagamit ng clickhousedb_fdw, ito ay bale-wala at maihahambing sa pagganap na nakamit sa pamamagitan ng katutubong pagpapatakbo sa database ng ClickHouse. Kinukumpirma rin nito na ang fdw sa PostgreSQL ay nagbibigay ng mahusay na mga resulta.
Telegram chat sa pamamagitan ng Clickhouse
Telegram chat gamit ang PostgreSQL
Pinagmulan: www.habr.com