Mu phunziro ili, ndimafuna kuwona zomwe kusintha kwa magwiridwe antchito kungatheke pogwiritsa ntchito gwero la data la ClickHouse osati PostgreSQL. Ndikudziwa zopindulitsa zomwe ndimapeza pogwiritsa ntchito ClickHouse. Kodi mapinduwa adzapitilira ndikapeza ClickHouse kuchokera ku PostgreSQL pogwiritsa ntchito Foreign Data Wrapper (FDW)?
Malo a database omwe amawerengedwa ndi PostgreSQL v11, clickhousedb_fdw ndi ClickHouse database. Pamapeto pake, kuchokera ku PostgreSQL v11 tikhala tikufunsa mafunso osiyanasiyana a SQL oyendetsedwa kudzera pa clickhousedb_fdw yathu kupita ku database ya ClickHouse. Kenako tiwona momwe magwiridwe antchito a FDW akufananizira ndi mafunso omwewo omwe akuyenda mu PostgreSQL wamba ndi ClickHouse wamba.
Clickhouse Database
ClickHouse ndi gwero lotseguka la database loyang'anira database lomwe limatha kukwaniritsa magwiridwe antchito 100-1000 mwachangu kuposa njira zachikhalidwe zama database, zomwe zimatha kukonza mizere yopitilira biliyoni pasanathe sekondi imodzi.
Clickhousedb_fdw
clickhousedb_fdw - Chovala chakunja cha data cha ClickHouse database, kapena FDW, ndi pulojekiti yotseguka yochokera ku Percona. .
.
Monga momwe muwonera, izi zimapereka FDW ya ClickHouse yomwe imalola SELECT kuchokera, ndi INSERT INTO, database ya ClickHouse kuchokera pa seva ya PostgreSQL v11.
FDW imathandizira zida zapamwamba monga kuphatikiza ndi kujowina. Izi zimathandizira kwambiri magwiridwe antchito pogwiritsa ntchito zida za seva yakutali pazogwiritsa ntchito izi.
Benchmark chilengedwe
- Seva ya Supermicro:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 sockets / 28 cores / 56 ulusi
- Memory: 256GB ya RAM
- Kusungirako: Samsung SM863 1.9TB Enterprise SSD
- Mafayilo: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: mtundu 11
Mayeso a benchmark
M'malo mogwiritsa ntchito data yopangidwa ndi makina pamayesowa, tidagwiritsa ntchito data ya "Productivity by Time Reported Operator Time" kuyambira 1987 mpaka 2018. Mukhoza kupeza deta .
Kukula kwa database ndi 85 GB, kupereka tebulo limodzi la mizati 109.
Mafunso a Benchmark
Nawa mafunso omwe ndimakonda kufananiza ClickHouse, clickhousedb_fdw ndi PostgreSQL.
Q#
Funso Lili ndi Ma Aggregates ndi Gulu Lolemba
Q1
SANKHANI DayOfWeek, count(*) AS c KUCHOKERA nthawi yomweyo KULI Chaka >= 2000 NDI Chaka <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2
SANKHANI DayOfWeek, count(*) AS c KUCHOKERA nthawi yomweyo KULI DepDelay>10 NDI Chaka >= 2000 NDI Chaka <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q3
SANKANI Chiyambi, kuwerengera(*) AS c KUCHOKERA nthawi yomweyo KULI DepDelay>10 NDI Chaka>= 2000 NDI Chaka <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;
Q4
SINANI Wonyamula, werengera () KUCHOKERA nthawi yomweyo KUMENE DepDelay>10 NDI Chaka = 2007 GROUP BY Carrier ORDER BY count(DESC;
Q5
SANKHANI a.Carrier, c, c2, c1000/c2 monga c3 KUCHOKERA (SANKHANI Chonyamulira, kuwerengera() AS c KUCHOKERA nthawi yake KULI DepDelay>10 NDI Chaka=2007 GROUP BY Carrier ) JOIN WAMKATI (SAKHANI Wonyamula,kuwerengera(*) AS c2 KUCHOKERA pa nthawi yomwe Year=2007 GROUP BY Carrier)b pa.Carrier=b. NDI c3 DESC;
Q6
SANKHANI a.Carrier, c, c2, c1000/c2 monga c3 KUCHOKERA (SANKHANI Chonyamulira, kuwerengera() AS c KUCHOKERA nthawi yomweyo KOMWE DepDelay>10 NDI Chaka >= 2000 NDI Chaka <= 2008 GROUP BY Carrier) JOIN INNER (SAKHANI Onyamula, owerengera(*) AS c2 KUCHOKERA nthawi POMWE Year >= 2000 NDI Chaka <= 2008 GROUP BY Wonyamulira ) b pa a.Carrier=b.UTHENGA WACHINYAMATA NDI c3 DESC;
Q7
SANKANI Carrier, avg(DepDelay) * 1000 AS c3 KUCHOKERA panthawi yomwe Chaka>= 2000 NDI Chaka <= 2008 GROUP BY Carrier;
Q8
SANKANI Chaka, avg(DepDelay) KUCHOKERA pa nthawi GROUP BY Year;
Q9
sankhani Chaka, count(*) monga c1 kuchokera pagulu la nthawi ndi Chaka;
Q10
SANKANI avg(cnt) KUCHOKERA (SANKHA Chaka, Mwezi,kuwerengera(*) AS cnt KUCHOKERA nthawi yomweyo KODI DepDel15=1 GULU NDI Chaka,Mwezi) a;
Q11
sankhani avg(c1) kuchokera ku (sankhani Chaka,Mwezi,kuwerengera(*) monga c1 kuchokera pagulu la nthawi ndi Chaka,Mwezi) a;
Q12
SANKHANI OriginCityName, DestCityName, count(*) AS c KUCHOKERA pa nthawi yake GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;
Q13
SANKHANI OriginCityName, werengerani(*) AS c KUCHOKERA pa nthawi yake GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;
Funso Lili ndi Zolumikizana
Q14
SANKANI a.Year, c1/c2 KUCHOKERA ( sankhani Chaka, count()1000 monga c1 kuchokera nthawi yomweyo KULI DepDelay>10 GROUP BY Year) JOIN YA MKATI (sankhani Chaka, werengerani(*) monga c2 kuchokera pa nthawi GROUP BY Year ) b pa.Year=b.Year ORDER BY a.Year;
Q15
SANKHANI a.”Chaka”, c1/c2 KUCHOKERA ( sankhani “Chaka”, count()1000 monga c1 KUCHOKERA ku fontime KULI “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (sankhani “Year”, count(*) monga c2 KUCHOKERA KU fontime GROUP BY “Year” ) b pa.”Year”=b. "Chaka";
Gulu-1: Mafunso omwe amagwiritsidwa ntchito pa benchmark
Zochita zamafunso
Nazi zotsatira zafunso lililonse likamayendetsedwa m'malo osiyanasiyana a database: PostgreSQL yokhala ndi ma indexes, ClickHouse wamba ndi clickhousedb_fdw. Nthawi ikuwonetsedwa mu milliseconds.
Q#
PostgreSQL
PostgreSQL (yolembedwa)
Dinani Nyumba
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
Gulu-1: Nthawi yotengedwa kuti ayankhe mafunso omwe amagwiritsidwa ntchito pa benchmark
Onani zotsatira
Grafu ikuwonetsa nthawi yofunsa mafunso mu milliseconds, X axis ikuwonetsa nambala yafunso kuchokera pamatebulo pamwambapa, ndipo Y axis ikuwonetsa nthawi yochitira mu ma milliseconds. Zotsatira za ClickHouse ndi zomwe zatengedwa kuchokera ku ma postgres pogwiritsa ntchito clickhousedb_fdw zikuwonetsedwa. Kuchokera patebulo mutha kuwona kuti pali kusiyana kwakukulu pakati pa PostgreSQL ndi ClickHouse, koma kusiyana kochepa pakati pa ClickHouse ndi clickhousedb_fdw.
Chithunzichi chikuwonetsa kusiyana pakati pa ClickhouseDB ndi clickhousedb_fdw. Pamafunso ambiri, FDW pamwamba siili yokwera kwambiri ndipo ndiyofunikira kupatula Q12. Funso ili likuphatikizapo maulumikizano ndi ORDER BY clause. Chifukwa cha ORDER BY GROUP/BY clause, ORDER BY sichitsikira ku ClickHouse.
Mu Table 2 tikuwona nthawi kudumpha mu mafunso Q12 ndi Q13. Apanso, izi zimayambitsidwa ndi ORDER BY clause. Kuti nditsimikizire izi, ndidafunsa mafunso Q-14 ndi Q-15 popanda ndime ya ORDER BY clause. Popanda ORDER BY clause nthawi yomaliza ndi 259ms ndipo ndi ORDER BY clause ndi 1364212. Kuthetsa funsoli ndikufotokozera mafunso onse ndipo apa pali zotsatira za kufotokozera.
Q15: Popanda KUYAMBIRA NDI Chiganizo
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: Kufunsa Popanda KUYANG'ANIRA NDI Ndime
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: Funso NDI 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: Funso Lokonzekera ndi 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)Pomaliza
Zotsatira zakuyesaku zikuwonetsa kuti ClickHouse imapereka magwiridwe antchito abwino kwambiri, ndipo clickhousedb_fdw imapereka maubwino a ClickHouse kuchokera ku PostgreSQL. Ngakhale pali mitu ina mukamagwiritsa ntchito clickhousedb_fdw, ndizosavomerezeka komanso zofananira ndi magwiridwe antchito omwe apezeka pothamanga mwachilengedwe patsamba la ClickHouse. Izi zimatsimikiziranso kuti fdw mu PostgreSQL imapereka zotsatira zabwino kwambiri.
Macheza a telegraph kudzera pa Clickhouse
Macheza a Telegraph pogwiritsa ntchito PostgreSQL
Source: www.habr.com