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

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

Selles uuringus soovisin uurida, milliseid jĂ”udlusparandusi on vĂ”imalik saavutada andmeallika ClickHouse'i kasutamisel PostgreSQL-i asemel. Ma tean, milliseid jĂ”udlusvĂ”lusid saan ClickHouse'i kasutamisel. Kas need eelised pĂŒsivad, kui pÀÀsen ClickHouse'ile PostgreSQL'i kaudu vĂ€lise andmekĂ€ivituse (FDW) abil?

Uuritavad andmebaasid 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 vaatame, kuidas FDW jÔudlus vÔrreldes sama pÀringuga, mida teostatakse natiivses PostgreSQL-is ja natiivses ClickHouse'is.

ClickHouse andmebaas

ClickHouse on avatud lĂ€htekoodiga veergude pĂ”hine andmebaasi haldamise sĂŒsteem, mis vĂ”ib saavutada jĂ”udluse, mis on 100–1000 korda kiirem kui traditsioonilised andmebaasi lĂ€henemised, suudab töödelda ĂŒle miljardi rida vĂ€hem kui sekundiga.

Clickhousedb_fdw

clickhousedb_fdw on ClickHouse andmebaasi vÀline andmekÀivituse (FDW) jahutustooted, avatud lÀhtekoodiga projekt, mille on loonud Percona. Siin on link projekti GitHubi hoidlatele.

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

Nagu nÀete, pakub see FDW ClickHouse'ile, mis vÔimaldab SELECT andmeid ja INSERT andmeid PostgreSQL v11-serverist ClickHouse andmebaasi.

FDW toetab tĂ€iustatud funktsioone, nagu aggregate ja join. See suurendab mĂ€rkimisvÀÀrselt jĂ”udlust, kasutades kaugsĂŒsteemi ressursse nende ressursimahukate operatsioonide 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 ettevĂ”tte SSD
    • FailisĂŒsteem: ext4/xfs
  • OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
  • PostgreSQL: versioon 11

Benchmark testid

Kuna ei kasutanud mingit masinaga genereeritud andmestikku, kasutasime ĂŒhte andmestikku „Aja jĂ”udlus, mida operaatori tööaja raportid” aastatel 1987-2018. Andmetele pÀÀsete ligi meie skripti kaudu, mis on saadaval siin.

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

Benchmark pÀringud

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

Q#
PĂ€ring sisaldab aggregaatfunktsioone ja rĂŒhmitamisi

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

Q2
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
SELECT 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
SELECT 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 liitumisi

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
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;

Table-1: Queries used in benchmark

Query executions

Siin on iga pÀringu tulemused erinevates andmebaasi seadistustes: PostgreSQL koos ja ilma indeksitega, eraldi ClickHouse ning clickhousedb_fdw. Aeg on nÀidatud millisekundites.

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

Table-1: Time taken to execute the queries used in benchmark

Tulemuste vaatamine

Graafik nĂ€itab pĂ€ringu tĂ€itmise aega millisekundites, X-teljel on pĂ€ringu number ĂŒlaltoodud tabelitest, Y-teljel aga tĂ€itmise aeg millisekundites. Tulemused ClickHouse'ist ja andmed, mis saadud postgres'ist clickhousedb_fdw abil, on nĂ€idatud. Tabelist on nĂ€ha, et PostgreSQL ja ClickHouse'i vahel on suur vahe, kuid ClickHouse'i ja clickhousedb_fdw vahel minimaalne vahe.

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

See graafik nĂ€itab, kuidas ClickhouseDB ja clickhousedb_fdw erinevad. Enamikus pĂ€ringutes ei ole FDW kulud nii suured ja vaevumĂ€rgatavad, vĂ€lja arvatud Q12. See pĂ€ring sisaldab ĂŒhendusi ja ORDER BY lauset. ORDER BY GROUP/BY ja ORDER BY ei ole teist ferrulede alla ClickHouse'i.

Tabelis 2 nĂ€eme ajahĂŒpet pĂ€ringutes Q12 ja Q13. Kordan, et see on pĂ”hjustatud ORDER BY lausest. Selle kinnitamiseks tegin pĂ€ringud Q-14 ja Q-15 koos ja ilma ORDER BY lauseta. Ilma ORDER BY lauseta on lĂ”ppemise aeg 259 ms ja ORDER BY lausaga 1364212. Selle pĂ€ringu tĂ”rkeotsingu jaoks selgitan mĂ”lemat pĂ€ringut, ja siin on antud selgituse tulemused.

Q15: Ilma ORDER BY lauseta

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 lauseta

KÜSIMUSE PLaan                                                      
Hash Join  (kulu=2250.00..128516.06 ridades=50000000 laius=12)  
VĂ€ljund: fontime."Aasta", (((count(*) * 1000)) / b.c2)  
Sisemine ainulaadne: true   Hash Cond: (fontime."Aasta" = b."Aasta")  
->  VĂ€line skaneerimine  (kulu=1.00..-1.00 ridades=100000 laius=12)        
VĂ€ljund: fontime."Aasta", ((count(*) * 1000))        
Seosed: Aadress kogumisel (fontime)        
Kaugsena SQL: SELECT "Aasta", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Aasta"  
->  Hash  (kulu=999.00..999.00 ridades=100000 laius=12)        
VĂ€ljund: b.c2, b."Aasta"        
->  AlamkĂŒsimuse skaneerimine b  (kulu=1.00..999.00 ridades=100000 laius=12)              
VĂ€ljund: b.c2, b."Aasta"              
->  VĂ€line skaneerimine  (kulu=1.00..-1.00 ridades=100000 laius=12)                    
VĂ€ljund: fontime_1."Aasta", (count(*))                    
Seosed: Aadress kogumisel (fontime)                    
Kaugsena SQL: SELECT "Aasta", count(*) FROM "default".ontime GROUP BY "Aasta"(16 rida)

Q14: KĂŒsige koos ORDER BY klausliga

bm=# EXPLAIN VERBOSE SELECT a."Aasta", c1/c2 FROM(SELECT "Aasta", count(*)*1000 AS c1 FROM fontime WHERE "DepDelay" > 10 GROUP BY "Aasta") a 
     INNER JOIN(SELECT "Aasta", count(*) as c2 FROM fontime GROUP BY "Aasta") b  ON a."Aasta"= b."Aasta" 
     ORDER BY a."Aasta";

Q14: KĂŒsige plaan koos ORDER BY klausliga

KÜSIMUSE PLANEERIMINE 
Sulandumine  (kulu=2.00..628498.02 read=50000000 laius=12)   
VĂ€ljund: fontime."Aasta", (((count(*) * 1000)) / (count(*)))   
Sisemine unikaalsus: tÔene   Sulandumise tingimus: (fontime."Aasta" = fontime_1."Aasta")   
->  GrupimÀÀramine  (kulu=1.00..499.01 read=1 laius=12)       
VĂ€ljund: fontime."Aasta", (count(*) * 1000)       
Grupi vÔti: fontime."Aasta"       
->  VÔÔrsil skaneerimine public.fontime  (kulu=1.00..-1.00 read=100000 laius=4)             
Kaugarvutus: SELECT "Aasta" FROM "default".ontime WHERE (("DepDelay" > 10)) 
            TELLIMUS KOHA JÄRGI "Aasta" ASC       
->  GrupimÀÀramine  (kulu=1.00..499.01 read=1 laius=12)       
VÀljund: fontime_1."Aasta", count(*)       Grupi vÔti: fontime_1."Aasta"       
->  VÔÔrsil skaneerimine public.fontime fontime_1  (kulu=1.00..-1.00 read=100000 laius=4)  
              
Kaugarvutus: SELECT "Aasta" FROM "default".ontime TELLIMUS KOHA JÄRGI "Aasta" ASC(16 read)

KokkuvÔte

Need eksperimendid nÀitavad, et ClickHouse pakub tÔeliselt head jÔudlust ja clickhousedb_fdw toob PostgreSQL-se kaasa ClickHouse'i jÔudluse eelised. Kuigi clickhousedb_fdw kasutamisel on teatud overhead, on need ebaolulised ja vÔrreldavad jÔudlusega, mis saavutatakse ClickHouse'i andmebaasis loomulikus kÀivitamises. See tÔendab ka, et fdw PostgreSQL-s tagab suurepÀraseid tulemusi.

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

Allikas: habr.com

Osta usaldusvÀÀrne veebihosting DDoS kaitsega, VPS VDS serverid đŸ”„ Osta usaldusvÀÀrne veebihosting DDoS kaitsega, VPS VDS serverid | ProHoster