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