U ovoj studiji želio sam vidjeti koja se poboljšanja performansi mogu postići korištenjem izvora podataka ClickHouse umjesto PostgreSQL-a. Znam koje prednosti u produktivnosti dobivam korištenjem ClickHousea. Hoće li se ove pogodnosti nastaviti ako pristupim ClickHouseu iz PostgreSQL-a koristeći Foreign Data Wrapper (FDW)?
Proučavana okruženja baze podataka su PostgreSQL v11, clickhousedb_fdw i ClickHouse baza podataka. U konačnici, od PostgreSQL v11 pokretat ćemo razne SQL upite koji se usmjeravaju preko našeg clickhousedb_fdw do baze podataka ClickHouse. Zatim ćemo vidjeti kakva je izvedba FDW-a u usporedbi s istim upitima koji se izvode u izvornom PostgreSQL-u i izvornom ClickHouseu.
Clickhouse baza podataka
ClickHouse je sustav za upravljanje stupčastim bazama podataka otvorenog koda koji može postići performanse 100-1000 puta brže od tradicionalnih pristupa bazi podataka, sposoban obraditi više od milijardu redaka u manje od sekunde.
Clickhousedb_fdw
clickhousedb_fdw - vanjski omotač podataka za bazu podataka ClickHouse ili FDW je projekt otvorenog koda tvrtke Percona. .
.
Kao što ćete vidjeti, ovo daje FDW za ClickHouse koji omogućuje SELECT iz baze podataka ClickHouse i INSERT INTO s poslužitelja PostgreSQL v11.
FDW podržava napredne značajke kao što su agregat i pridruživanje. To značajno poboljšava performanse korištenjem resursa udaljenog poslužitelja za ove resursno intenzivne operacije.
Referentno okruženje
- Supermicro poslužitelj:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00 GHz
- 2 utičnice / 28 jezgri / 56 niti
- Memorija: 256 GB RAM-a
- Pohrana: Samsung SM863 1.9TB Enterprise SSD
- Datotečni sustav: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generički #45~16.04.1-Ubuntu
- PostgreSQL: verzija 11
Benchmark testovi
Umjesto da koristimo neki strojno generirani skup podataka za ovaj test, koristili smo podatke "Produktivnost prema vremenu prijavljenom vremenu operatera" od 1987. do 2018. Možete pristupiti podacima .
Veličina baze podataka je 85 GB, pruža jednu tablicu od 109 stupaca.
Usporedni upiti
Ovo su upiti koje sam koristio za usporedbu ClickHousea, clickhousedb_fdw i PostgreSQLa.
Q#
Upit sadrži agregate i grupiranje prema
Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 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
SELECT Carrier, count() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() DESC;
Q5
SELECT a.Carrier, c, c2, c1000/c2 kao 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 OD c3 DESC;
Q6
SELECT a.Carrier, c, c2, c1000/c2 kao c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Carrier) a INNER JOIN ( SELECT Carrier, count(*) AS c2 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Nosač ) b na a.Carrier=b.Carrier ORDER BY c3 DESC;
Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier;
Q8
SELECT Year, avg(DepDelay) FROM ontime GROUP BY Year;
Q9
odaberite godinu, brojite (*) kao c1 iz ontime grupe po godini;
Q10
SELECT 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
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 spajanja
Q14
SELECT a.Year, c1/c2 FROM ( odaberite Year, count()1000 kao c1 od ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (odaberite godinu, broj(*) kao c2 od ontime GROUP BY Year ) b na a.Year=b.Year ORDER BY a.Year;
Q15
SELECT a.”Year”, c1/c2 FROM ( odaberite “Year”, count()1000 kao c1 FROM fonttime WHERE “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (odaberite “Year”, brojite(*) kao c2 FROM fontime GROUP BY “Year” ) b na a.”Year”=b. "Godina";
Tablica-1: Upiti korišteni u mjerilu
Izvršenja upita
Ovdje su rezultati svakog od upita kada se pokrenu 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)
klikanica
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
Tablica-1: Vrijeme potrebno za izvršavanje upita korištenih u usporedbi
Pogledaj rezultate
Grafikon prikazuje vrijeme izvršenja upita u milisekundama, X os prikazuje broj upita iz gornjih tablica, a Y os prikazuje vrijeme izvršenja u milisekundama. Prikazani su rezultati ClickHousea i podaci dohvaćeni iz postgresa pomoću clickhousedb_fdw. Iz tablice možete vidjeti da postoji velika razlika između PostgreSQL-a i ClickHousea, ali minimalna razlika između ClickHousea i clickhousedb_fdw.
Ovaj grafikon prikazuje razliku između ClickhouseDB i clickhousedb_fdw. U većini upita FDW režijski troškovi nisu toliko visoki i jedva da su značajni osim za Q12. Ovaj upit uključuje spojeve i klauzulu ORDER BY. Zbog klauzule ORDER BY GROUP/BY, ORDER BY ne pada u ClickHouse.
U tablici 2 vidimo vremenski skok u upitima Q12 i Q13. Opet, to je uzrokovano klauzulom ORDER BY. Kako bih to potvrdio, pokrenuo sam upite Q-14 i Q-15 sa i bez klauzule ORDER BY. Bez klauzule ORDER BY vrijeme završetka je 259 ms, a s klauzulom ORDER BY je 1364212. Za otklanjanje pogrešaka 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 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";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)Izlaz
Rezultati ovih eksperimenata pokazuju da ClickHouse nudi stvarno dobre performanse, a clickhousedb_fdw nudi prednosti performansi ClickHousea iz PostgreSQL-a. Iako postoje određeni troškovi pri korištenju clickhousedb_fdw, oni su zanemarivi i usporedivi s performansama postignutim nativnim radom na bazi podataka ClickHouse. Ovo također potvrđuje da fdw u PostgreSQL daje izvrsne rezultate.
Telegram chat putem Clickhousea
Telegram chat koristeći PostgreSQL
Izvor: www.habr.com