ProHoster > Blog > fitantanan-draharaha > Fitsapana ny fahombiazan'ny fangatahana famakafakana ao amin'ny PostgreSQL, ClickHouse ary clickhousedb_fdw (PostgreSQL)

Fitsapana ny fahombiazan'ny fangatahana famakafakana ao amin'ny PostgreSQL, ClickHouse ary clickhousedb_fdw (PostgreSQL)

Amin'ity fandalinana ity, te-hijery izay fanatsarana ny fampisehoana azo tanterahina amin'ny fampiasana loharano angona ClickHouse aho fa tsy PostgreSQL. Fantatro ny tombony azo amin'ny famokarana azoko amin'ny fampiasana ClickHouse. Hitohy ve ireo tombontsoa ireo raha miditra amin'ny ClickHouse avy amin'ny PostgreSQL mampiasa Foreign Data Wrapper (FDW) aho?

Ny tontolon'ny angon-drakitra nodinihina dia PostgreSQL v11, clickhousedb_fdw ary ClickHouse database. Amin'ny farany, avy amin'ny PostgreSQL v11 dia handefa fanontaniana SQL isan-karazany alefa amin'ny alalan'ny clickhousedb_fdw mankany amin'ny angon-drakitra ClickHouse. Ho hitantsika avy eo ny fampitahana ny fahombiazan'ny FDW amin'ireo fanontaniana mitovy amin'ny PostgreSQL teratany sy ClickHouse teratany.

Clickhouse Database

ClickHouse dia rafitra fitantanana angon-drakitra misokatra malalaka izay afaka mahatratra 100-1000 heny haingana kokoa noho ny fomba fiasa mahazatra mahazatra, afaka manodina andalana iray lavitrisa ao anatin'ny segondra vitsy.

Clickhousedb_fdw

clickhousedb_fdw - Ny fonosana data ivelany ho an'ny angon-drakitra ClickHouse, na FDW, dia tetikasa loharano misokatra avy amin'ny Percona. Ity misy rohy mankany amin'ny tahiry GitHub an'ny tetikasa.

Tamin'ny volana martsa dia nanoratra bilaogy iray aho izay milaza aminao bebe kokoa momba ny FDW.

Araka ny hitanao dia manome FDW ho an'ny ClickHouse izay mamela ny SELECT avy amin'ny, ary INSERT INTO, ny angona ClickHouse avy amin'ny mpizara PostgreSQL v11.

Ny FDW dia manohana ireo endri-javatra mandroso toy ny aggregate sy ny fidirana. Manatsara ny fampandehanana izany amin'ny alàlan'ny fampiasana ny loharanon'ny mpizara lavitra ho an'ireo asa mavesa-danja ireo.

Tontolo iainana benchmark

  • Mpizara Supermicro:
    • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
    • 2 sockets / 28 cores / 56 kofehy
    • Tsianjery: 256GB an'ny RAM
    • Fitehirizana: Samsung SM863 1.9TB Enterprise SSD
    • Rafitra fisie: ext4/xfs
  • OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
  • PostgreSQL: version 11

Fitsapana benchmark

Raha tokony hampiasa angon-drakitra novokarin'ny milina ho an'ity fitsapana ity izahay, dia nampiasa ny angon-drakitra "Productivity by Time Reported Operator Time" nanomboka tamin'ny 1987 ka hatramin'ny 2018. Afaka miditra amin'ny angona ianao mampiasa ny script misy antsika eto.

Ny haben'ny tahiry dia 85 GB, manome latabatra iray misy tsanganana 109.

Fanontaniana Benchmark

Ireto ny fanontaniana nampiasaiko mba hampitahana ny ClickHouse, clickhousedb_fdw ary PostgreSQL.

Q#
Ny fanontaniana dia misy fitambarana sy vondrona By

Q1
MIFIDY DayOfWeek, manisa (*) AS c avy amin'ny fotoana izay Taona >= 2000 SY Taona <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q2
MIFIDY DayOfWeek, manisa (*) AS c avy amin'ny fotoana izay DepDelay>10 SY Taona >= 2000 SY Taona <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q3
SELECT Origin, manisa(*) AS c FROM ontime WHERE DepDelay>10 SY Year >= 2000 SY Year <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;

Q4
SELECT Carrier, isa () AVY amin'ny fotoana izay DepDelay>10 SY Taona = 2007 GROUP BY Carrier ORDER BY count() DESC;

Q5
MIFIDY a.Mpitondra, c, c2, c1000/c2 toy ny c3 FROM ( SELECT Carrier, isa() AS c avy amin'ny fotoana izay DepDelay>10 SY Taona=2007 GROUP BY Carrier ) a INNER MIDIRANA ( SELECT Carrier, isa(*) AS c2 avy amin'ny fotoana WHERE Year=2007 GROUP BY Carrier)b amin'ny a.Carrier=b.Carrier ORDER BY c3 DESC;

Q6
MIFIDY a.Mpitondra, c, c2, c1000/c2 toy ny c3 FROM ( SELECT Carrier, isa() AS c FROM ontime WHERE DepDelay>10 SY Year >= 2000 SY Year <= 2008 GROUP BY Carrier) a INNER JOIN ( SELECT Carrier, isa(*) AS c2 FROM ontime WHERE Year >= 2000 SY Year <= 2008 GROUP BY Carrier ) b amin'ny a.Carrier=b.Carrier ORDER BY c3 DESC;

Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier;

Q8
SELECT Taona, avg(DepDelay) AVY amin'ny fotoana ara-potoana GROUP PAR-Taona;

Q9
safidio ny Taona, isao (*) ho c1 avy amin'ny vondrona ara-potoana isaky ny taona;

Q10
SELECT avg(cnt) FROM (FILIFINA Taona, Volana, isa(*) AS cnt avy amin'ny fotoana izay DepDel15=1 GROUP PAR TAONA,VINA) a;

Q11
safidio ny salan'isa(c1) avy amin'ny (safidio ny Taona, Volana, isa(*) ho c1 avy amin'ny vondrona ara-potoana araka ny Taona, Volana) a;

Q12
MIFIDY OriginCityName, DestCityName, manisa (*) AS c avy amin'ny fotoanan'ny GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;

Q13
MIFIDY OriginCityName, manisa (*) AS c avy amin'ny GROUP amin'ny fotoana ara-potoana AMIN'NY OriginCityName ORDER BY c DESC LIMIT 10;

Fanontaniana misy fiarahana

Q14
SELECT a.Year, c1/c2 FROM ( safidio ny Taona, isao()1000 toy ny c1 avy amin'ny fotoana izay AIZA DepDelay>10 GROUP PAR-Taona) a INNER MIDIRANA (fanteno ny Taona, isao (*) ho c2 avy amin'ny ara-potoana GROUP BY Year ) b amin'ny a.Year=b.Year ORDER BY a.Year;

Q15
FIFIDY a.”Taona”, c1/c2 AVY ( fidio “Taona”, isao()1000 toy ny c1 AVY AMIN'NY fontime IZAY “DepDelay”>10 GROUP PAR “Year”) a INTER JOIN (safidio ny “Year”, isa(*) ho c2 fontime GROUP PAR “Year” ) b on a.”Year”=b. "Taona";

Tabilao-1: Fanontaniana ampiasaina amin'ny benchmark

Fanontaniana famonoana

Ireto ny valin'ny fanontaniana tsirairay rehefa mandeha amin'ny sehatra angon-drakitra samihafa: PostgreSQL misy sy tsy misy index, ClickHouse teratany ary clickhousedb_fdw. Ny fotoana dia aseho amin'ny milisegondra.

Q#
PostgreSQL
PostgreSQL (Indroa)
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

Tabilao-1: Fotoana natokana hanatanterahana ireo fanontaniana ampiasaina amin'ny benchmark

Jereo ny valiny

Ny tabilao dia mampiseho ny fotoana fanatanterahana ny fangatahana amin'ny milisegondra, ny X axis dia mampiseho ny laharan'ny fangatahana avy amin'ireo tabilao etsy ambony, ary ny Y dia mampiseho ny fotoana famonoana amin'ny milisegondra. Ny valin'ny ClickHouse sy ny angona azo avy amin'ny postgres mampiasa clickhousedb_fdw dia aseho. Avy amin'ny tabilao dia hitanao fa misy fahasamihafana goavana eo amin'ny PostgreSQL sy ClickHouse, fa ny fahasamihafana kely eo amin'ny ClickHouse sy clickhousedb_fdw.

Fitsapana ny fahombiazan'ny fangatahana famakafakana ao amin'ny PostgreSQL, ClickHouse ary clickhousedb_fdw (PostgreSQL)

Ity tabilao ity dia mampiseho ny fahasamihafana misy eo amin'ny ClickhouseDB sy clickhousedb_fdw. Amin'ny ankamaroan'ny fanontaniana dia tsy dia avo loatra ny overhead FDW ary zara raha manan-danja afa-tsy amin'ny Q12. Ity fanontaniana ity dia ahitana ny fidirana sy ny fehezan-dalàna ORDER BY. Noho ny ORDER BY GROUP/BY clause dia tsy midina any amin'ny ClickHouse ny ORDER BY.

Ao amin'ny tabilao 2 dia hitantsika ny fitsangantsanganana amin'ny fanontaniana Q12 sy Q13. Averina indray, izany dia vokatry ny clause ORDER BY. Mba hanamafisana izany dia nametraka fanontaniana Q-14 sy Q-15 miaraka amin'ny clause ORDER BY aho. Raha tsy misy ny ORDER BY clause dia 259ms ny fotoana famitana ary miaraka amin'ny clause ORDER BY dia 1364212. Mba hanesorana an'io fanontaniana io dia samy manazava ny fanontaniana aho ary ireto ny valin'ny fanazavana.

Q15: Tsy misy 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: Fanontaniana tsy misy 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: Fanontaniana miaraka amin'ny 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: Drafi-panontaniana miaraka amin'ny 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)

famaranana

Ny valin'ireo andrana ireo dia mampiseho fa ny ClickHouse dia manolotra fampisehoana tena tsara, ary ny clickhousedb_fdw dia manolotra ny tombotsoan'ny ClickHouse avy amin'ny PostgreSQL. Na dia misy aza ny overhead rehefa mampiasa clickhousedb_fdw, dia tsinontsinona izany ary azo ampitahaina amin'ny zava-bita azo tamin'ny alàlan'ny fampandehanana an-tanindrazana ao amin'ny tahiry ClickHouse. Izany koa dia manamafy fa ny fdw ao amin'ny PostgreSQL dia manome valiny tsara.

Telegram chat amin'ny Clickhouse https://t.me/clickhouse_ru
Telegram chat mampiasa PostgreSQL https://t.me/pgsql

Source: www.habr.com

Add a comment