ProHoster > Blog > Administracija > Testiranje performansi analitičkih upita u PostgreSQL, ClickHouse i clickhousedb_fdw (PostgreSQL)

Testiranje performansi analitičkih upita u PostgreSQL, ClickHouse i clickhousedb_fdw (PostgreSQL)

U ovoj studiji želio sam istražiti kakve se poboljšanja u performansama mogu postići korištenjem izvora podataka ClickHouse umjesto PostgreSQL. Znam koja su poboljšanja performansi koja dobivam koristeći ClickHouse. Hoće li te prednosti ostati ako pristupim ClickHouse-u iz PostgreSQL-a putem vanjskog omotača podataka (FDW)?

Istraživane baze podataka su PostgreSQL v11, clickhousedb_fdw i baza podataka ClickHouse. Na kraju, iz PostgreSQL v11 pokrećemo različite SQL upite, usmjerene kroz naš clickhousedb_fdw u bazu podataka ClickHouse. Tada ćemo vidjeti kako se performanse FDW uspoređuju s istim upitima koji se izvršavaju u nativnom PostgreSQL-u i nativnom ClickHouse-u.

Baza podataka Clickhouse

ClickHouse je sustav za upravljanje bazama podataka temeljen na kolonama s otvorenim izvorom, koji može postići performanse 100-1000 puta brže od tradicionalnih pristupa bazama podataka, sposoban obraditi više od milijardu redaka u manje od sekunde.

Clickhousedb_fdw

clickhousedb_fdw je vanjski omotač podataka za bazu podataka ClickHouse, ili FDW, projekt s otvorenim izvorom od Percone. Evo linka na GitHub repozitorij projekta.

U ožujku sam napisao blog koji vas detaljnije informira o našem FDW-u.

Kao što ćete vidjeti, ovo pruža FDW za ClickHouse koji omogućuje SELECT iz i INSERT u bazu podataka ClickHouse sa servera PostgreSQL v11.

FDW podržava napredne funkcije, kao što su agregati i join. To značajno poboljšava performanse koristeći resurse udaljenog servera za ove resursno intenzivne operacije.

Benchmark okruženje

  • Supermicro server:
    • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
    • 2 utičnice / 28 jezgri / 56 niti
    • Memorija: 256GB RAM-a
    • Pohrana: Samsung SM863 1.9TB Enterprise SSD
    • Datotečni sustav: ext4/xfs
  • OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
  • PostgreSQL: verzija 11

Benchmark testovi

Umjesto korištenja nekog skupa podataka generiranog računalom, za ovaj test koristili smo podatke 'Performanse po vremenu, izvještavanje o vremenu rada operatera' od 1987. do 2018. godine. Možete pristupiti podacima putem našeg skripta, dostupnog ovdje.

Veličina baze podataka iznosi 85 GB, osiguravajući jednu tablicu s 109 stupaca.

Benchmark upiti

Ovo su upiti koje sam koristio za usporedbu ClickHouse-a, clickhousedb_fdw-a i PostgreSQL-a.

Q#
Upit sadrži agregate i Group By

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

Q2
ODABERITE DanTjedna, COUNT(*) AS c IZ ontime GDJE DepDelay>10 I Godina >= 2000 I Godina <= 2008 GRUPIRAJ PO DanTjedna NAREDI PO c DESC;

Q3
ODABERITE Izvor, COUNT(*) AS c IZ ontime GDJE DepDelay>10 I Godina >= 2000 I Godina <= 2008 GRUPIRAJ PO Izvor NAREDI PO c DESC LIMIT 10;

Q4
ODABERITE Prijevoznik, COUNT() IZ ontime GDJE DepDelay>10 I Godina = 2007 GRUPIRAJ PO Prijevoznik NAREDI PO COUNT() DESC;

Q5
ODABERITE a.Prijevoznik, c, c2, c1000/c2 kao c3 IZ ( ODABERITE Prijevoznik, COUNT() AS c IZ ontime GDJE DepDelay>10 I Godina=2007 GRUPIRAJ PO Prijevoznik ) a UNUTAR SPOJA ( ODABERITE Prijevoznik, COUNT(*) AS c2 IZ ontime GDJE Godina=2007 GRUPIRAJ PO Prijevoznik) b ON a.Prijevoznik=b.Prijevoznik NAREDI PO c3 DESC;

Q6
ODABERITE a.Prijevoznik, c, c2, c1000/c2 kao c3 IZ ( ODABERITE Prijevoznik, COUNT() AS c IZ ontime GDJE DepDelay>10 I Godina >= 2000 I Godina = 2000 I Godina <= 2008 GRUPIRAJ PO Prijevoznik ) b ON a.Prijevoznik=b.Prijevoznik NAREDI PO c3 DESC;

Q7
ODABERITE Prijevoznik, AVG(DepDelay) * 1000 AS c3 IZ ontime GDJE Godina >= 2000 I Godina <= 2008 GRUPIRAJ PO Prijevoznik;

Q8
ODABERITE Godina, AVG(DepDelay) IZ ontime GRUPIRAJ PO Godina;

Q9
ODABERITE Godina, COUNT(*) AS c1 IZ ontime GRUPIRAJ PO Godina;

Q10
ODABERITE AVG(cnt) IZ (ODABERITE Godina, Mjesec, COUNT(*) AS cnt IZ ontime GDJE DepDel15=1 GRUPIRAJ PO Godina, Mjesec) a;

Q11
ODABERITE AVG(c1) IZ (ODABERITE Godina, Mjesec, COUNT(*) AS c1 IZ ontime GRUPIRAJ PO Godina, Mjesec) a;

Q12
ODABERITE ImeIzvorCity, ImeDestCity, COUNT(*) AS c IZ ontime GRUPIRAJ PO ImeIzvorCity, ImeDestCity NAREDI PO c DESC LIMIT 10;

Q13
ODABERITE ImeIzvorCity, COUNT(*) AS c IZ ontime GRUPIRAJ PO ImeIzvorCity NAREDI PO c DESC LIMIT 10;

Upit sadrži spoj

Q14
ODABERITE a.Godina, c1/c2 IZ ( ODABERITE Godina, COUNT()1000 AS c1 IZ ontime GDJE DepDelay>10 GRUPIRAJ PO Godina) a UNUTAR SPOJA (ODABERITE Godina, COUNT(*) AS c2 IZ ontime GRUPIRAJ PO Godina) b ON a.Godina=b.Godina NAREDI PO a.Godina;

Q15
ODABERITE a."Godina", c1/c2 IZ ( ODABERITE "Godina", COUNT()1000 AS c1 IZ fontime GDJE "DepDelay">10 GRUPIRAJ PO "Godina") a UNUTAR SPOJA (ODABERITE "Godina", COUNT(*) AS c2 IZ fontime GRUPIRAJ PO "Godina") b ON a."Godina"=b."Godina";

Tabela-1: Upiti korišteni u benchmarku

Izvršenja upita

Evo rezultata svakog od upita prilikom izvršenja u različitim postavkama baze podataka: PostgreSQL s indeksima i bez njih, vlastiti ClickHouse i clickhousedb_fdw. Vrijeme je prikazano u milisekundama.

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

Tabela-1: Vrijeme potrebno za izvršenje upita korištenih u benchmarku

Pogledajte rezultate

Grafikon prikazuje vrijeme izvršenja upita u milisekundama, os X prikazuje broj upita iz gornjih tablica, a os Y prikazuje vrijeme izvršenja u milisekundama. Rezultati ClickHouse-a i podaci dobiveni iz postgres-a putem clickhousedb_fdw su prikazani. Iz tablice se vidi da postoji ogromna razlika između PostgreSQL i ClickHouse, ali minimalna razlika između ClickHouse i clickhousedb_fdw.

Testiranje performansi analitičkih upita u PostgreSQL, ClickHouse i clickhousedb_fdw (PostgreSQL)

Ovaj grafikon pokazuje razliku između ClickhouseDB i clickhousedb_fdw. U većini upita, troškovi FDW nisu značajni osim u Q12. Ovaj upit uključuje spajanja i klauzulu ORDER BY. Zbog klauzule ORDER BY, GROUP BY i ORDER BY ne preskoče do ClickHouse.

U tablici 2 vidimo skok vremena u upitima Q12 i Q13. Ponovno ću reći, to je uzrokovano klauzulom ORDER BY. Da to potvrdim, izvršio sam upite Q-14 i Q-15 s klauzulom ORDER BY i bez nje. Bez klauzule ORDER BY vrijeme završetka iznosi 259 ms, a s klauzulom ORDER BY — 1364212. Za otklanjanje problema s ovim upitom objašnjavam oba upita, a ovdje su rezultati objašnjenja.

Q15: Bez klauzule ORDER BY

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: Upit bez klauzule ORDER BY

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: Upit s klauzulom ORDER BY

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: Plan upita s klauzulom ORDER BY

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)

Izvještaj

Rezultati ovih eksperimenata pokazuju da ClickHouse nudi zaista dobru performansu, dok clickhousedb_fdw donosi prednosti performansi ClickHouse-a iz PostgreSQL-a. Iako postoje neki troškovi korišćenja clickhousedb_fdw, oni su mali i uporedivi sa performansom postignutom prilikom izvorne upotrebe ClickHouse baze podataka. Ovo takođe potvrđuje da fdw u PostgreSQL-u pruža izvanredne rezultate.

Telegram chat o Clickhouse-u https://t.me/clickhouse_ru
Telegram chat o PostgreSQL-u https://t.me/pgsql

Izvor: habr.com

Kupite pouzdan hosting za web stranice s DDoS zaštitom, VPS VDS servere 🔥 Kupite pouzdan hosting za web stranice s DDoS zaštitom, VPS VDS servere | ProHoster