ProHoster > Blogi > Haldamine > AnalĂŒĂŒtiliste pĂ€ringute jĂ”udluse testimine PostgreSQL, ClickHouse ja clickhousedb_fdw (PostgreSQL) puhul

AnalĂŒĂŒtiliste pĂ€ringute jĂ”udluse testimine PostgreSQL, ClickHouse ja clickhousedb_fdw (PostgreSQL) puhul

Selles uuringus soovisin uurida, milliseid jĂ”udluse parandusi on vĂ”imalik saavutada, kasutades andmeallikaks ClickHouse'i, mitte PostgreSQL-i. Ma tean, millised jĂ”udluse eelised kaasnevad ClickHouse'i kasutamisega. Kas need eelised pĂŒsivad, kui pÀÀsen ClickHouse'ile PostgreSQL'ist vĂ€liste andmekestade (FDW) abil?

Uuritavad andmebaasikeskkonnad on PostgreSQL v11, clickhousedb_fdw ja ClickHouse andmebaas. LÔppkokkuvÔttes kÀivitame PostgreSQL v11-st erinevaid SQL-pÀringuid, mis suunatakse meie clickhousedb_fdw kaudu ClickHouse andmebaasi. SeejÀrel nÀeme, kuidas FDW jÔudlus vÔrreldes sama pÀringuga, mis on tÀidetud kohalikus PostgreSQL-is ja kui ka kohalikus ClickHouse'is.

Clickhouse andmebaas

ClickHouse on avatud lĂ€htekoodiga veergude andmebaasisĂŒsteem, mis suudab saavutada tulemusi 100–1000 korda kiiremini kui traditsioonilised andmebaasi lĂ€henemisviisid, olles vĂ”imeline töötlema ĂŒle miljardi rida vĂ€hem kui sekundiga.

Clickhousedb_fdw

clickhousedb_fdw on ClickHouse'i vÀliste andmete kesta, vÔi FDW, avatud lÀhtekoodiga projekt, mille on loonud Percona. Siin on link projekti GitHub'i hoidlasse.

MÀrtsis kirjutasin blogiposti, mis rÀÀgib rohkem meie FDW-st.

Nagu nÀete, pakub see FDW voor ClickHouse'ile, mis vÔimaldab SELECT from ning INSERT INTO ClickHouse andmebaasi PostgreSQL v11 serverist.

FDW toetab keerukaid funktsioone, nagu agregaat ja ĂŒhine. See suurendab jĂ”udlust, kasutades kaugarvuti ressursse nende ressursimahukate toimingute jaoks.

Benchmark keskkond

  • Supermicro server:
    • IntelÂź XeonÂź CPU E5-2683 v3 @ 2.00GHz
    • 2 pesa / 28 tuuma / 56 lĂ”ime
    • MĂ€lu: 256GB RAM-i
    • Salvestus: Samsung SM863 1.9TB Enterprise SSD
    • FailisĂŒsteem: ext4/xfs
  • OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
  • PostgreSQL: versioon 11

Benchmark testid

Selle testi jaoks, selle asemel et kasutada masinaga genereeritud andmebaasi, kasutasime andmeid 'Aja tĂ€itmise tegevuse pĂ”hjal mÀÀratud jĂ”udluse kohta' 1987–2018. Te saate ligipÀÀsu andmetele meie siin saadaval oleva skripti kaudu.

Andmebaasi suurus on 85 GB, pakkudes ĂŒhte tabelit, kus on 109 veergu.

Benchmark pÀringud

Siin on pÀringud, mida kasutasin ClickHouse'i, clickhousedb_fdw ja PostgreSQL'i vÔrdlemiseks.

K#
PÀring sisaldab agregaatfunktsioone ja grupi jÀrgi

K1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

K2
VALI DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;

Q3
VALI 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
VALI Carrier, count() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() DESC;

Q5
VALI a.Carrier, c, c2, c1000/c2 as c3 FROM ( SELECT Carrier, count() AS 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 BY c3 DESC;

Q6
VALI a.Carrier, c, c2, c1000/c2 as c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year = 2000 AND Year <= 2008 GROUP BY Carrier ) b on a.Carrier=b.Carrier ORDER BY c3 DESC;

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

Q8
VALI Year, avg(DepDelay) FROM ontime GROUP BY Year;

Q9
valida Year, count(*) as c1 from ontime group by Year;

Q10
VALI avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;

Q11
valida avg(c1) from (select Year,Month,count(*) as c1 from ontime group by Year,Month) a;

Q12
VALI OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;

Q13
VALI OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;

KĂŒsimus sisaldab liite

Q14
VALI a.Year, c1/c2 FROM ( select Year, count()1000 as c1 from ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (select Year, count(*) as c2 from ontime GROUP BY Year ) b on a.Year=b.Year ORDER BY a.Year;

Q15
VALI 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”;

Table-1: KĂŒsimused, mida kasutati vĂ”rdluses

KĂŒsimuste tĂ€itmine

Siin on iga pÀringu tulemused, kui neid tÀideti erinevates andmebaasi seadetes: PostgreSQL indeksitega ja ilma, enda ClickHouse ja clickhousedb_fdw. Aeg on nÀidatud millisekundites.

K#
PostgreSQL
PostgreSQL (indekseeritud)
ClickHouse
clickhousedb_fdw

K1
27920
19634
23
57

K2
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

Table-1: Aeg, mis kulus pÀringute tÀitmiseks, mida kasutati vÔrdluses

Vaata tulemusi

Graafik nĂ€itab pĂ€ringu tĂ€itmise aega millisekundites, X-telg nĂ€itab pĂ€ringu numbrit ĂŒlaltoodud tabelites ning Y-telg nĂ€itab tĂ€itmisaja millisekundites. Tulemused ClickHouse'i ja andmed, saadud Postgres'ist, kasutades clickhousedb_fdw, on nĂ€idatud. Tabelis on nĂ€ha, et PostgreSQL ja ClickHouse'i vahel on tohutu erinevus, kuid ClickHouse ja clickhousedb_fdw vahel on minimaalne erinevus.

AnalĂŒĂŒtiliste pĂ€ringute jĂ”udluse testimine PostgreSQL, ClickHouse ja clickhousedb_fdw (PostgreSQL) puhul

See graafik nÀitab erinevust ClickhouseDB ja clickhousedb_fdw vahel. Enamikus pÀringutes ei ole FDW kulud eriti suured ja on vaevu mÀrgatavad, vÀlja arvatud Q12. See pÀring sisaldab liite ja ORDER BY klauslit. GROUP/BY ja ORDER BY tÔttu ei jÀeta ClickHouse'i.

Tabelis 2 nĂ€eme hĂŒpet Q12 ja Q13 pĂ€ringute ajades. Kordan, et see on pĂ”hjustatud ORDER BY lausetest. Kinnitamiseks kĂ€isin lĂ€bi pĂ€ringud Q-14 ja Q-15 nii ORDER BY lause kui ka ilma selleta. Ilma ORDER BY lauseta on lĂ”puaeg 259 ms, ja ORDER BY lauseta on see 1364212. Selle pĂ€ringu tĂ”rkeotsingu jaoks selgitan mĂ”lemat pĂ€ringut, siin on esitatud seletuse tulemused.

Q15: Ilma ORDER BY lauseteta

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: PĂ€ring ilma ORDER BY lauseteta

PÄRINGU PLaan                                                      
Hash Join  (cost=2250.00..128516.06 rows=50000000 width=12)  
VĂ€ljund: fontime."Year", (((count(*) * 1000)) / b.c2)  
Sisemine unikaalne: tÔene   Hash Cond: (fontime."Year" = b."Year")  
->  VÔÔrskane  (cost=1.00..-1.00 rows=100000 width=12)        
VĂ€ljund: fontime."Year", ((count(*) * 1000))        
Suhted: Agressiivne (fontime)        
Kaugsuhtlus SQL: SELECT "Year", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Year"  
->  Hash  (cost=999.00..999.00 rows=100000 width=12)        
VĂ€ljund: b.c2, b."Year"        
->  AlampÀringu skaneerimine b  (cost=1.00..999.00 rows=100000 width=12)              
VĂ€ljund: b.c2, b."Year"              
->  VÔÔrskane  (cost=1.00..-1.00 rows=100000 width=12)                    
VĂ€ljund: fontime_1."Year", (count(*))                    
Suhted: Agressiivne (fontime)                    
Kaugsuhtlus SQL: SELECT "Year", count(*) FROM "default".ontime GROUP BY "Year"(16 rows)

Q14: PĂ€ring ORDER BY lausega

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: PĂ€ringu plaan ORDER BY lausega

PÄRINGU PLaan 
Merge Join  (cost=2.00..628498.02 rows=50000000 width=12)   
VÀljund: fontime."Year", (((count(*) * 1000)) / (count(*)))   
Sisemine unikaalne: tÔene   Merge Cond: (fontime."Year" = fontime_1."Year")   
->  Grupiagregaat  (cost=1.00..499.01 rows=1 width=12)        
VÀljund: fontime."Year", (count(*) * 1000)         
Grupi nÔue: fontime."Year"         
->  VÔÔrskane avalikus.fontimes  (cost=1.00..-1.00 rows=100000 width=4)               
Kaugsuhtlus SQL: SELECT "Year" FROM "default".ontime WHERE (("DepDelay" > 10)) 
            ORDER BY "Year" ASC   
->  Grupiagregaat  (cost=1.00..499.01 rows=1 width=12)         
VÀljund: fontime_1."Year", count(*)         GrupinÔue: fontime_1."Year"         
->  VÔÔrskane avalikus.fontimes fontime_1  (cost=1.00..-1.00 rows=100000 width=4) 
              
Kaugsuhtlus SQL: SELECT "Year" FROM "default".ontime ORDER BY "Year" ASC(16 rows)

KokkuvÔte

Needuste katsetused nĂ€itavad, et ClickHouse pakub tĂ”epoolest head jĂ”udlust, samas kui clickhousedb_fdw toob PostgreSQL-i ClickHouse'i jĂ”udluse eelised. Kuigi clickhousedb_fdw kasutamisel on teatud ĂŒlevaated, on need ebaolulised ja vĂ”rreldavad jĂ”udlusega, mida saavutatakse loomuliku kĂ€ivitamisega ClickHouse andmebaasis. See kinnitab ka, et fdw PostgreSQL-is pakub suurepĂ€raseid tulemusi.

Clickhouse'i Telegrami vestlus https://t.me/clickhouse_ru
PostgreSQL-i Telegrami vestlus https://t.me/pgsql

Allikas: habr.com

Osta usaldusvÀÀrne hostimine veebilehtede jaoks DDoS-i kaitsega, VPS VDS serverid đŸ”„ Osta usaldusvÀÀrne hostimine veebilehtede jaoks DDoS-i kaitsega, VPS VDS serverid | ProHoster