U ovoj studiji, želio sam vidjeti kakva poboljšanja performansi mogu biti postignuta korištenjem ClickHouse izvora podataka umjesto PostgreSQL-a. Znam prednosti produktivnosti koje imam od korištenja ClickHousea. Hoće li se ove pogodnosti nastaviti ako pristupim ClickHouse-u iz PostgreSQL-a koristeći Foreign Data Wrapper (FDW)?
Proučavana okruženja baze podataka su PostgreSQL v11, clickhousedb_fdw i ClickHouse baza podataka. Konačno, od PostgreSQL v11 ćemo pokretati različite SQL upite koji se usmjeravaju kroz naš clickhousedb_fdw do ClickHouse baze podataka. Zatim ćemo vidjeti kakve su performanse FDW-a u poređenju sa istim upitima koji se pokreću u izvornom PostgreSQL-u i izvornom ClickHouse-u.
Clickhouse Database
ClickHouse je sistem za upravljanje bazama podataka otvorenog koda koji može postići performanse 100-1000 puta brže od tradicionalnih pristupa bazi podataka, sposoban da obradi više od milijardu redova za manje od jedne sekunde.
Clickhousedb_fdw
clickhousedb_fdw - Eksterni omotač podataka za ClickHouse bazu podataka, ili FDW, je projekat otvorenog koda iz Percone. .
.
Kao što ćete vidjeti, ovo pruža FDW za ClickHouse koji omogućava SELECT iz i INSERT INTO, ClickHouse bazu podataka sa PostgreSQL v11 servera.
FDW podržava napredne funkcije kao što su združivanje i spajanje. Ovo značajno poboljšava performanse korištenjem resursa udaljenog poslužitelja za ove resursno intenzivne operacije.
Benchmark okruženje
- Supermicro server:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 utičnice / 28 jezgri / 56 navoja
- Memorija: 256 GB RAM-a
- Skladištenje: Samsung SM863 1.9TB Enterprise SSD
- Sistem datoteka: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generički #45~16.04.1-Ubuntu
- PostgreSQL: verzija 11
Benchmark testovi
Umjesto da za ovaj test koristimo neke mašinski generirane podatke, koristili smo podatke "Produktivnost prema vremenu prijavljenom operaterskom vremenu" od 1987. do 2018. godine. Možete pristupiti podacima .
Veličina baze podataka je 85 GB, pružajući jednu tabelu od 109 kolona.
Benchmark Queries
Evo upita koje sam koristio da uporedim ClickHouse, clickhousedb_fdw i PostgreSQL.
Q#
Upit sadrži agregate i grupe po
Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Godina >= 2000 I Godina <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q3
SELECT 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
ODABIR prijevoznika, broj () OD ontime WHERE DepDelay>10 I Godina = 2007 GRUPA PO Prevozniku ORDER BY count() DESC;
Q5
SELECT a.Nosilac, c, c2, c1000/c2 kao c3 OD ( ODABIR Nosioca, broj () KAO c OD pravovremenog GDJE DepDelay>10 I Godina=2007 GRUPA PO Prevozniku ) a UNUTRAŠNJE PRIDRUŽENJE (ODABIR Prevoznika,broj(*) KAO c2 OD pravovremenog GDE Godina=2007 GRUPA PO Prevozniku)b na a.Prevoznik=b.Narudžba prevoznika BY c3 DESC;
Q6
SELECT a.Nosilac, c, c2, c1000/c2 kao c3 OD ( ODABIR Nosioca, broj () KAO c OD pravovremenog GDJE DepDelay>10 I Godina >= 2000 I Godina <= 2008 GRUPA PO Prevozniku) a UNUTRAŠNJE PRIDRUŽENJE ( ODABIR Nosioca, računaj(*) KAO c2 OD pravovremenog GDJE Godina >= 2000 I Godina <= 2008 GRUPA PO Nositelj ) b na a.Nosilac=b.Nosilac ORDER BY c3 DESC;
Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Godina >= 2000 I Godina <= 2008 GRUPA PO Prevozniku;
Q8
SELECT Year, avg(DepDelay) FROM ontime GROUP BY Year;
Q9
izaberite Godina, računajte(*) kao c1 iz vremenske grupe po godini;
Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GRUPA PO Godini,Mjesecu) a;
Q11
izaberite avg(c1) iz (odaberite Godinu,Mjesec,broj(*) kao c1 iz ontime grupe po Godini,Mjesecu) a;
Q12
SELECT OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;
Q13
SELECT OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;
Upit sadrži pridruživanja
Q14
ODABERITE a.Godinu, c1/c2 OD ( odaberite godinu, broj ()1000 kao c1 od ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (odaberite godinu, računajte(*) kao c2 od ontime GROUP BY Year ) b na a.Godina=b.Godina ORDER BY a.Year;
Q15
ODABERITE a.”Godina”, c1/c2 IZ (odaberite “Godina”, count()1000 kao c1 FROM fonttime WHERE “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (odaberite “Year”, brojite(*) kao c2 FROM fonttime GROUP PO “Year” ) b na a.”Godina”=b. "Godina";
Tabela-1: Upiti korišteni u benčmarku
Izvršenja upita
Evo rezultata svakog od upita kada se pokreću u različitim postavkama baze podataka: PostgreSQL sa i bez indeksa, izvorni ClickHouse i clickhousedb_fdw. Vrijeme je prikazano u milisekundama.
Q#
PostgreSQL
PostgreSQL (indeksirano)
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šavanje upita korištenih u benčmarku
Pogledaj rezultate
Grafikon prikazuje vrijeme izvršenja upita u milisekundama, X osa prikazuje broj upita iz gornjih tabela, a Y osa prikazuje vrijeme izvršenja u milisekundama. Prikazani su rezultati ClickHouse i podaci preuzeti iz postgresa korištenjem clickhousedb_fdw. Iz tabele možete vidjeti da postoji ogromna razlika između PostgreSQL i ClickHouse, ali minimalna razlika između ClickHouse i clickhousedb_fdw.
Ovaj grafikon pokazuje razliku između ClickhouseDB i clickhousedb_fdw. U većini upita, troškovi FDW-a nisu tako visoki i jedva da su značajni osim za Q12. Ovaj upit uključuje spojeve i klauzulu ORDER BY. Zbog ORDER BY GROUP/BY klauzule, ORDER BY ne pada na ClickHouse.
U tabeli 2 vidimo vremenski skok u upitima Q12 i Q13. Opet, ovo je uzrokovano ORDER BY klauzulom. Da to potvrdim, pokrenuo sam upite Q-14 i Q-15 sa i bez klauzule ORDER BY. Bez ORDER BY klauzule vrijeme završetka je 259 ms, a sa ORDER BY klauzulom je 1364212. Da bih otklonio greške u ovom upitu, objašnjavam oba upita i evo rezultata objašnjenja.
P15: 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";P15: 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)P14: Upit sa 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";P14: 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)zaključak
Rezultati ovih eksperimenata pokazuju da ClickHouse nudi zaista dobre performanse, a clickhousedb_fdw nudi prednosti performansi ClickHouse-a iz PostgreSQL-a. Iako postoje određeni troškovi pri korištenju clickhousedb_fdw, oni su zanemarljivi i uporedivi s performansama postignutim izvornim pokretanjem na ClickHouse bazi podataka. Ovo takođe potvrđuje da fdw u PostgreSQL-u daje odlične rezultate.
Telegram chat putem Clickhousea
Telegram chat koristeći PostgreSQL
izvor: www.habr.com