F'dan l-istudju, ridt nara x'titjib fil-prestazzjoni jista 'jinkiseb billi tuża sors ta' dejta ClickHouse aktar milli PostgreSQL. Naf il-benefiċċji tal-produttività li nikseb billi nuża ClickHouse. Dawn il-benefiċċji se jkomplu jekk naċċessa ClickHouse minn PostgreSQL billi tuża Foreign Data Wrapper (FDW)?
L-ambjenti tad-database studjati huma PostgreSQL v11, clickhousedb_fdw u ClickHouse database. Fl-aħħar mill-aħħar, minn PostgreSQL v11 se nkunu qed inħaddmu diversi mistoqsijiet SQL mibgħuta permezz tal-clickhousedb_fdw tagħna għad-database ClickHouse. Imbagħad naraw kif il-prestazzjoni tal-FDW titqabbel mal-istess mistoqsijiet li jaħdmu f'PostgreSQL nattiv u ClickHouse nattiv.
Clickhouse Database
ClickHouse hija sistema ta 'ġestjoni tad-database kolonni ta' sors miftuħ li tista 'tikseb prestazzjoni 100-1000 darba aktar malajr minn approċċi ta' database tradizzjonali, kapaċi tipproċessa aktar minn biljun ringiela f'inqas minn sekonda.
Clickhousedb_fdw
clickhousedb_fdw - It-tgeżwir tad-dejta esterna għad-database ClickHouse, jew FDW, huwa proġett ta' sors miftuħ minn Percona. .
.
Kif se tara, dan jipprovdi FDW għal ClickHouse li jippermetti AGĦŻEL minn, u Daħħal Ġewwa, id-database ClickHouse mis-server PostgreSQL v11.
FDW jappoġġja karatteristiċi avvanzati bħal aggregate u join. Dan itejjeb b'mod sinifikanti l-prestazzjoni billi juża r-riżorsi tas-server remot għal dawn l-operazzjonijiet li jużaw ħafna riżorsi.
Ambjent ta' referenza
- Supermicro server:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 sokits / 28 qalba / 56 ħajt
- Memorja: 256GB RAM
- Ħażna: Samsung SM863 1.9TB Enterprise SSD
- Sistema tal-fajls: ext4/xfs
- OS: Linux smblade01 4.15.0-42-ġeneriku #45~16.04.1-Ubuntu
- PostgreSQL: verżjoni 11
Testijiet ta' referenza
Minflok ma użajna xi sett ta' dejta ġġenerat mill-magni għal dan it-test, użajna d-dejta tal-"Produttività skont il-Ħin Rapportat tal-Operatur tal-Ħin" mill-1987 sal-2018. Tista' taċċessa d-dejta .
Id-daqs tad-database huwa ta' 85 GB, li jipprovdi tabella waħda ta' 109 kolonna.
Mistoqsijiet ta' Benchmark
Hawn huma l-mistoqsijiet li użajt biex inqabbel ClickHouse, clickhousedb_fdw u PostgreSQL.
Q#
Mistoqsija Fih Aggregati u Group By
Q1
AGĦŻEL DayOfWeek, għadd(*) KIF ċ MILL-ħin FEJN Sena >= 2000 U Sena <= 2008 GRUPP MILL-JumOfWeek ORDNI MINN c DESC;
Q2
GĦAŻEL DayOfWeek, għadd(*) KIF ċ MILL-ħin FEJN DepDelay>10 U Sena >= 2000 U Sena <= 2008 GRUPP BY DayOfWeek ORDNI MINN c DESC;
Q3
GĦAŻEL Oriġini, għadd(*) KIF ċ MILL-ħin FEJN DepDelay>10 U Sena >= 2000 U Sena <= 2008 GRUPP SKOND ORNI ORDNI MINN c DESC LIMIT 10;
Q4
AGĦŻEL Trasportatur, għadd() MINN fil-ħin FEJN DepDelay>10 U Sena = 2007 GRUPP BIL-Trasportatur ORDER BY għadd() DESC;
Q5
AGĦŻEL a.Carrier, c, c2, c1000/c2 bħala c3 MINN (AGĦŻEL Trasportatur, għadd() KIF c FROM fil-ħin WHERE DepDelay>10 AND Year=2007 GROUP BY Carrier ) a INNER JOIN ( SELECT Carrier,count (*)) KIF c2 FROM ontime WHERE Sena=2007 GROUP BY Carrier)b fuq a.Carrier=b.Carrier ORDER MINN c3 DESC;
Q6
AGĦŻEL a.Carrier, c, c2, c1000/c2 bħala c3 MINN (AGĦŻEL Trasportatur, għadd() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Carrier) a INNER JOIN ( SELECT Carrier, għadd(*) AS c2 FROM ontime WHERE Sena >= 2000 AND Year <= 2008 GROUP BY Carrier ) b fuq a.Carrier=b.Carrier ORDNI MINN c3 DESC;
Q7
AGĦŻEL Trasportatur, avg(DepDelay) * 1000 AS c3 FROM ontime FEJN Sena >= 2000 U Sena <= 2008 GRUPP MINN Trasportatur;
Q8
AGĦŻEL s-Sena, avg(DepDelay) FROM ontime GRUPP BY Year;
Q9
agħżel Sena, għadd(*) bħala c1 minn grupp ontime b'Sena;
Q10
GĦAŻEL avg(cnt) FROM (AGĦŻEL Sena,Xahar,għadd(*) AS cnt FROM ontime WHERE DepDel15=1 GRUPP BY Year,Month) a;
Q11
agħżel avg(c1) minn (agħżel Sena,Xahar,għadd(*) bħala c1 minn grupp ontime b'Sena,Xahar) a;
Q12
AGĦŻEL OriginCityName, DestCityName, għadd(*) KIF c MILL-ħin GRUPP MILL-OriginCityName, DestCityName ORDNI MINN c DESC LIMIT 10;
Q13
AGĦŻEL OriginCityName, għadd(*) KIF ċ MILL-ħin GRUPP MINN OriginCityName ORDNI MINN c DESC LIMIT 10;
Mistoqsija Fih Joins
Q14
AGĦŻEL a.Sena, c1/c2 MINN (agħżel Sena, għadd()1000 as c1 from ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (agħżel Sena, għodd(*) bħala c2 minn ontime GRUPP BY Year ) b fuq a.Year=b.Year ORDER BY a.Year;
Q15
AGĦŻEL a."Sena", c1/c2 MINN (agħżel "Sena", għadd()1000 bħala c1 MINN fonttime FEJN “DepDelay”>10 GRUPP MINN “Sena”) a INNER JOIN (agħżel “Sena”, għodd(*) bħala c2 MINN fonttime GRUPP MINN “Sena”) b fuq a.”Sena”=b. "Sena";
Tabella-1: Mistoqsijiet użati fil-benchmark
Mistoqsija eżekuzzjonijiet
Hawn huma r-riżultati ta' kull mistoqsija meta titħaddem f'settings ta' database differenti: PostgreSQL bi u mingħajr indiċi, ClickHouse indiġeni u clickhousedb_fdw. Il-ħin jidher f'millisekondi.
Q#
PostgreSQL
PostgreSQL (Indiċjat)
IkklikkjaHouse
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
Tabella-1: Ħin meħud biex jitwettqu l-mistoqsijiet użati fil-benchmark
Ara r-riżultati
Il-graff turi l-ħin tal-eżekuzzjoni tal-mistoqsija f'millisekondi, l-assi X juri n-numru tal-mistoqsija mit-tabelli ta 'hawn fuq, u l-assi Y juri l-ħin tal-eżekuzzjoni f'millisekondi. Ir-riżultati ta' ClickHouse u d-dejta rkuprata minn postgres bl-użu ta' clickhousedb_fdw jintwerew. Mit-tabella tista' tara li hemm differenza kbira bejn PostgreSQL u ClickHouse, iżda differenza minima bejn ClickHouse u clickhousedb_fdw.
Din il-grafika turi d-differenza bejn ClickhouseDB u clickhousedb_fdw. Fil-biċċa l-kbira tal-mistoqsijiet, l-overhead FDW mhuwiex daqshekk għoli u bilkemm huwa sinifikanti ħlief għal Q12. Din il-mistoqsija tinkludi joins u klawżola ORDER BY. Minħabba l-klawżola ORDER BY GROUP/BY, ORDER BY ma tinżelx għal ClickHouse.
Fit-Tabella 2 naraw il-qabża fil-ħin fil-mistoqsijiet Q12 u Q13. Għal darb'oħra, dan huwa kkawżat mill-klawżola ORDER BY. Biex nikkonferma dan, għamilt mistoqsijiet Q-14 u Q-15 bi u mingħajr il-klawżola ORDER BY. Mingħajr il-klawżola ORDER BY il-ħin tat-tlestija huwa 259ms u bil-klawżola ORDER BY huwa 1364212. Biex niddebug din il-mistoqsija qed nispjega kemm il-mistoqsijiet u hawn huma r-riżultati tal-ispjegazzjoni.
Q15: Mingħajr Klawżola ORDER BY
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: Mistoqsija Mingħajr Klawżola ORDER BY
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: Mistoqsija Bi Klawżola ORDER BY
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: Pjan ta' Mistoqsija bi Klawżola ORDER BY
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
Ir-riżultati ta 'dawn l-esperimenti juru li ClickHouse joffri prestazzjoni tassew tajba, u clickhousedb_fdw joffri l-benefiċċji tal-prestazzjoni ta' ClickHouse minn PostgreSQL. Filwaqt li hemm xi overhead meta tuża clickhousedb_fdw, hija negliġibbli u komparabbli mal-prestazzjoni miksuba billi taħdem b'mod nattiv fuq id-database ClickHouse. Dan jikkonferma wkoll li fdw f'PostgreSQL jipprovdi riżultati eċċellenti.
Chat tat-telegramma permezz ta' Clickhouse
Telegram chat bl-użu ta 'PostgreSQL
Sors: www.habr.com