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)

V tej študiji sem želel raziskati, kakšne izboljšave zmogljivosti lahko dosežem z uporabo podatkovnega vira ClickHouse namesto PostgreSQL. Zavedam se prednosti zmogljivosti, ki jih ponuja ClickHouse. Ali bodo te prednosti ohranjene, če dostopam do ClickHouse iz PostgreSQL preko zunanje podatkovne obloge (FDW)?

Raziskovane baze podatkov so PostgreSQL v11, clickhousedb_fdw in baza podatkov ClickHouse. Na koncu bomo iz PostgreSQL v11 izvajali različne SQL poizvedbe, ki bodo usmerjene preko našega clickhousedb_fdw v bazo podatkov ClickHouse. Nato bomo videli, kako se zmogljivost FDW primerja z enakimi poizvedbami, ki jih izvajamo v nativnem PostgreSQL in nativnem ClickHouse.

Baza podatkov Clickhouse

ClickHouse je sistem za upravljanje baz podatkov na osnovi kolon, s odprto kodo, ki lahko doseže zmogljivost od 100 do 1000-krat hitreje kot tradicionalni pristopi k bazam podatkov, sposoben obdelovati več kot milijardo vrstic v manj kot sekundi.

Clickhousedb_fdw

clickhousedb_fdw je zunanja podatkovna obloga za bazo podatkov ClickHouse, oziroma FDW, in je projekt z odprto kodo, ki ga podpira Percona. Tukaj je povezava do repozitorija projekta na GitHubu.

Marca sem napisal blog, ki vam pove več o našem FDW.

Kot boste videli, to omogoča FDW za ClickHouse, ki omogoča SELECT from in INSERT INTO baz podatkov ClickHouse s strežnika PostgreSQL v11.

FDW podpira napredne funkcije, kot so agregati in združevanje. To znatno poveča zmogljivost z uporabo virov oddaljenega strežnika za te virov potrošne operacije.

Benchmark okolje

  • Supermicro strežnik:
    • Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
    • 2 vtičnice / 28 jeder / 56 niti
    • Pomnilnik: 256GB RAM
    • Shranjevanje: Samsung SM863 1.9TB Enterprise SSD
    • Datotečni sistem: ext4/xfs
  • OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
  • PostgreSQL: različica 11

Benchmark testi

Namesto da bi uporabili neko podatkovno množico, generirano z napravo, smo za ta test uporabili podatke 'Zmogljivost po času, obveščeni o času dela operaterja' od leta 1987 do 2018. Do podatkov lahko dostopate z našim skriptom, ki je tukaj na voljo.

Velikost baze podatkov je 85 GB, ki vsebuje eno tabelo z 109 stolpci.

Benchmark poizvedbe

Tukaj so poizvedbe, ki sem jih uporabil za primerjavo ClickHouse, clickhousedb_fdw in PostgreSQL.

Q#
Poizvedba vsebuje agregate in Group By

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

Q2
IZBERI DanTedna, število(*) AS c IZ ontime KJER DepDelay>10 IN Leto >= 2000 IN Leto <= 2008 SKUPAJ PO DanTedna NAROČI PO c DESC;

Q3
IZBERI Izvor, število(*) AS c IZ ontime KJER DepDelay>10 IN Leto >= 2000 IN Leto <= 2008 SKUPAJ PO Izvor NAROČI PO c DESC OMEJITI 10;

Q4
IZBERI Prevoznik, število() IZ ontime KJER DepDelay>10 IN Leto = 2007 SKUPAJ PO Prevoznik NAROČI PO število() DESC;

Q5
IZBERI a.Prevoznik, c, c2, c1000/c2 kot c3 IZ ( IZBERI Prevoznik, število() AS c IZ ontime KJER DepDelay>10 IN Leto=2007 SKUPAJ PO Prevoznik ) a NOTRANJI JOIN ( IZBERI Prevoznik,število(*) AS c2 IZ ontime KJER Leto=2007 SKUPAJ PO Prevoznik)b ON a.Prevoznik=b.Prevoznik NAROČI PO c3 DESC;

Q6
IZBERI a.Prevoznik, c, c2, c1000/c2 kot c3 IZ ( IZBERI Prevoznik, število() AS c IZ ontime KJER DepDelay>10 IN Leto >= 2000 IN Leto <= 2008 SKUPAJ PO Prevoznik) a NOTRANJI JOIN ( IZBERI Prevoznik, število(*) AS c2 IZ ontime KJER Leto >= 2000 IN Leto <= 2008 SKUPAJ PO Prevoznik ) b ON a.Prevoznik=b.Prevoznik NAROČI PO c3 DESC;

Q7
IZBERI Prevoznik, povprečje(DepDelay) * 1000 AS c3 IZ ontime KJER Leto >= 2000 IN Leto <= 2008 SKUPAJ PO Prevoznik;

Q8
IZBERI Leto, povprečje(DepDelay) IZ ontime SKUPAJ PO Leto;

Q9
izberi Leto, število(*) kot c1 IZ ontime SKUPAJ PO Leto;

Q10
IZBERI povprečje(cnt) IZ (IZBERI Leto,Mesec, število(*) AS cnt IZ ontime KJER DepDel15=1 SKUPAJ PO Leto,Mesec) a;

Q11
izberi povprečje(c1) IZ (izberi Leto,Mesec, število(*) kot c1 IZ ontime SKUPAJ PO Leto,Mesec) a;

Q12
IZBERI ImeIzvirnegaMesta, ImeDestinacijskegaMesta, število(*) AS c IZ ontime SKUPAJ PO ImeIzvirnegaMesta, ImeDestinacijskegaMesta NAROČI PO c DESC OMEJITI 10;

Q13
IZBERI ImeIzvirnegaMesta, število(*) AS c IZ ontime SKUPAJ PO ImeIzvirnegaMesta NAROČI PO c DESC OMEJITI 10;

Poizvedba vsebuje združitve

Q14
IZBERI a.Leto, c1/c2 IZ ( izberi Leto, število()1000 kot c1 IZ ontime KJER DepDelay>10 SKUPAJ PO Leto) a NOTRANJI JOIN (izberi Leto, število(*) kot c2 IZ ontime SKUPAJ PO Leto ) b ON a.Leto=b.Leto NAROČI PO a.Leto;

Q15
IZBERI a."Leto", c1/c2 IZ ( izberi "Leto", število()1000 kot c1 IZ fontime KJER "DepDelay">10 SKUPAJ PO "Leto") a NOTRANJI JOIN (izberi "Leto", število(*) kot c2 IZ fontime SKUPAJ PO "Leto" ) b ON a."Leto"=b."Leto";

Tabela-1: Poizvedbe uporabljene v benchmarku

Izvršitve poizvedb

Tukaj so rezultati posameznih poizvedb, izvedenih v različnih nastavitvah baze podatkov: PostgreSQL z indeksi in brez njih, lastni ClickHouse in clickhousedb_fdw. Čas je prikazan v milisekundah.

Q#
PostgreSQL
PostgreSQL (Indeksirani)
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: Čas, potreben za izvršitev poizvedb uporabljene v benchmarku

Ogled rezultatov

Graf prikazuje čas izvršitve poizvedbe v milisekundah, os X prikazuje številko poizvedbe iz zgornjih tabel, os Y pa prikazuje čas izvršitve v milisekundah. Rezultati ClickHouse in podatki, pridobljeni iz postgres z uporabo clickhousedb_fdw, so prikazani. Iz tabele je razvidno, da obstaja ogromna razlika med PostgreSQL in ClickHouse, a minimalna razlika med ClickHouse in clickhousedb_fdw.

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

Ta graf prikazuje razliko med ClickhouseDB in clickhousedb_fdw. Pri večini poizvedb niso tako visoke stroške FDW in so komaj opazne, razen pri Q12. Ta poizvedba vključuje združitve in klavzulo ORDER BY. Zaradi klavzule ORDER BY SKUPINA/BY in ORDER BY se do ClickHouse ne opusti.

V tabeli 2 vidimo skok časa pri poizvedbah Q12 in Q13. Ponovno, to je posledica uporabe ukaza ORDER BY. Da bi to potrdil, sem izvedel poizvedbe Q-14 in Q-15 z ukazom ORDER BY in brez njega. Brez ukaza ORDER BY je čas zaključka 259 ms, medtem ko je s tem ukazom 1364212. Za odpravljanje napak pri tej poizvedbi sem razložil oboje, tukaj so rezultati razlage.

Q15: Brez klavzule ORDER BY

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

Q15: Poizvedba brez klavzule ORDER BY

POIZVEDBNI NAČRT 
Hash Join  (cost=2250.00..128516.06 rows=50000000 width=12)  
Output: fontime."Leto", (((count(*) * 1000)) / b.c2)  
Inner Unique: true   Hash Cond: (fontime."Leto" = b."Leto")  
->  Foreign Scan  (cost=1.00..-1.00 rows=100000 width=12)        
Output: fontime."Leto", ((count(*) * 1000))        
Relations: Aggregate on (fontime)        
Remote SQL: SELECT "Leto", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Leto"  
->  Hash  (cost=999.00..999.00 rows=100000 width=12)        
Output: b.c2, b."Leto"        
->  Subquery Scan on b  (cost=1.00..999.00 rows=100000 width=12)              
Output: b.c2, b."Leto"              
->  Foreign Scan  (cost=1.00..-1.00 rows=100000 width=12)                    
Output: fontime_1."Leto", (count(*))                    
Relations: Aggregate on (fontime)                    
Remote SQL: SELECT "Leto", count(*) FROM "default".ontime GROUP BY "Leto"(16 rows)

Q14: Poizvedba z ukazom ORDER BY

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

Q14: Poizvedbeni načrt z ukazom ORDER BY

POIZVEDBNI NAČRT 
Merge Join  (cost=2.00..628498.02 rows=50000000 width=12)   
Output: fontime."Leto", (((count(*) * 1000)) / (count(*)))   
Inner Unique: true   Merge Cond: (fontime."Leto" = fontime_1."Leto")   
->  GroupAggregate  (cost=1.00..499.01 rows=1 width=12)        
Output: fontime."Leto", (count(*) * 1000)         
Group Key: fontime."Leto"         
->  Foreign Scan on public.fontime  (cost=1.00..-1.00 rows=100000 width=4)               
Remote SQL: SELECT "Leto" FROM "default".ontime WHERE (("DepDelay" > 10)) 
            ORDER BY "Leto" ASC   
->  GroupAggregate  (cost=1.00..499.01 rows=1 width=12)         
Output: fontime_1."Leto", count(*)         Group Key: fontime_1."Leto"         
->  Foreign Scan on public.fontime fontime_1  (cost=1.00..-1.00 rows=100000 width=4) 
              
Remote SQL: SELECT "Leto" FROM "default".ontime ORDER BY "Leto" ASC(16 rows)

Izlaz

Rezultati teh eksperimentov kažejo, da ClickHouse ponuja resnično dobro zmogljivost, medtem ko clickhousedb_fdw prinaša prednosti zmogljivosti ClickHouse iz PostgreSQL. Čeprav pri uporabi clickhousedb_fdw obstajajo določeni stroški, so ti zanemarljivi in primerljivi z zmogljivostjo, doseženo pri naravnem zagonu v bazi podatkov ClickHouse. To tudi potrjuje, da fdw v PostgreSQL zagotavlja izjemne rezultate.

Telegram skupina za Clickhouse https://t.me/clickhouse_ru
Telegram skupina za PostgreSQL https://t.me/pgsql

Izvor: habr.com

Kupite zanesljivo gostovanje za spletne strani z zaščito pred DDoS, VPS VDS strežniki 🔥 Kupite zanesljivo gostovanje za spletne strani z zaščito pred DDoS, VPS VDS strežniki | ProHoster