U ovom istraživanju želio sam ispitati koja poboljšanja performansi mogu postići koristeći ClickHouse kao izvor podataka umjesto PostgreSQL-a. Znam koje performanse dobijam korištenjem ClickHouse-a. Hoće li te prednosti biti očuvane 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 ćemo pokretati razne SQL upite, usmjerene kroz naš clickhousedb_fdw u bazu podataka ClickHouse. Zatim ćemo vidjeti kako se performanse FDW upoređuju sa istim upitima izvršenim u nativnom PostgreSQL-u i nativnom ClickHouse-u.
Baza podataka Clickhouse
ClickHouse je sistem upravljanja bazama podataka zasnovan na kolonama koji može postići performanse 100-1000 puta brže od tradicionalnih pristupa bazama podataka, sposoban obraditi više od milijardu redova u manje od sekunde.
Clickhousedb_fdw
clickhousedb_fdw je vanjski omotač podataka za bazu podataka ClickHouse, ili FDW, otvoreni je izvorni projekt od Percone. .
.
Kao što ćete vidjeti, ovo pruža FDW za ClickHouse koji omogućava SELECT iz, i INSERT u, bazu podataka ClickHouse sa servera PostgreSQL v11.
FDW podržava napredne funkcije kao što su agregat i join. To značajno poboljšava performanse koristeći resurse udaljenog servera za ove operacije koje zahtevaju mnogo resursa.
Okruženje benchmarka
- Supermicro server:
- Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
- 2 soketa / 28 jezgri / 56 niti
- Memorija: 256GB RAM-a
- Skladište: 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: verzija 11
Benchmark testovi
Umjesto da koristimo neki set podataka koji generiše mašina, za ovaj test koristili smo podatke "Performanse po vremenu, izveštavanje o vremenu rada operatera" od 1987. do 2018. godine. Možete pristupiti podacima .
Veličina baze podataka je 85 GB, što osigurava jednu tabelu od 109 kolona.
Benchmark upiti
Evo upita koje sam koristio za upoređivanje ClickHouse-a, clickhousedb_fdw i PostgreSQL-a.
Q#
Upit sadrži agregate i grupisanje
Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2
ODABERITE DanUWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GRUPIŠITE PO DanUWeek NARUČITE PO c DESC;
Q3
ODABERITE Origin, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GRUPIŠITE PO Origin NARUČITE PO c DESC LIMIT 10;
Q4
ODABERITE Carrier, count() FROM ontime WHERE DepDelay>10 AND Year = 2007 GRUPIŠITE PO Carrier NARUČITE PO count() DESC;
Q5
ODABERITE a.Carrier, c, c2, c1000/c2 kao c3 FROM ( ODABERITE Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year=2007 GRUPIŠITE PO Carrier ) a UNUTARNJE SPREMA ( ODABERITE Carrier,count(*) AS c2 FROM ontime WHERE Year=2007 GRUPIŠITE PO Carrier)b ON a.Carrier=b.Carrier NARUČITE PO c3 DESC;
Q6
ODABERITE a.Carrier, c, c2, c1000/c2 kao c3 FROM ( ODABERITE Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year = 2000 AND Year <= 2008 GRUPIŠITE PO Carrier ) b ON a.Carrier=b.Carrier NARUČITE PO c3 DESC;
Q7
ODABERITE Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GRUPIŠITE PO Carrier;
Q8
ODABERITE Year, avg(DepDelay) FROM ontime GRUPIŠITE PO Year;
Q9
ODABERITE Year, count(*) AS c1 FROM ontime GRUPIŠITE PO Year;
Q10
ODABERITE avg(cnt) FROM (ODABERITE Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GRUPIŠITE PO Year,Month) a;
Q11
ODABERITE avg(c1) FROM (ODABERITE Year,Month,count(*) AS c1 FROM ontime GRUPIŠITE PO Year,Month) a;
Q12
ODABERITE OriginCityName, DestCityName, count(*) AS c FROM ontime GRUPIŠITE PO OriginCityName, DestCityName NARUČITE PO c DESC LIMIT 10;
Q13
ODABERITE OriginCityName, count(*) AS c FROM ontime GRUPIŠITE PO OriginCityName NARUČITE PO c DESC LIMIT 10;
Upit sadrži pridruživanja
Q14
ODABERITE a.Year, c1/c2 FROM (ODABERITE Year, count()1000 kao c1 FROM ontime WHERE DepDelay>10 GRUPIŠITE PO Year) a UNUTARNJE SPREMA (ODABERITE Year, count(*) AS c2 FROM ontime GRUPIŠITE PO Year) b ON a.Year=b.Year NARUČITE PO a.Year;
Q15
ODABERITE a."Year", c1/c2 FROM (ODABERITE "Year", count()1000 kao c1 FROM fontime WHERE "DepDelay">10 GRUPIŠITE PO "Year") a UNUTARNJE SPREMA (ODABERITE "Year", count(*) AS c2 FROM fontime GRUPIŠITE PO "Year") b ON a."Year"=b."Year";
Tabela-1: Upiti korišćeni u merenju
Izvršenja upita
Evo rezultata svakog upita kada su izvršeni u različitim postavkama baze podataka: PostgreSQL s indeksima i bez njih, vlastiti ClickHouse i clickhousedb_fdw. Vreme je izraženo 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: Vreme potrebno za izvršavanje upita korišćenih u merenju
Pogledajte rezultate
Grafikon prikazuje vreme izvršenja upita u milisekundama, osi X prikazuje broj upita iz gornjih tabela, a osi Y prikazuje vreme izvršenja u milisekundama. Rezultati ClickHousea i podaci dobijeni iz postgres-a putem clickhousedb_fdw su prikazani. Iz tabele se može videti da postoji ogromna razlika između PostgreSQL-a i ClickHouse-a, ali minimalna razlika između ClickHouse-a i clickhousedb_fdw.

Ovaj grafikon prikazuje razliku između ClickhouseDB-a i clickhousedb_fdw. U većini upita troškovi FDW nisu tako veliki i jedva su značajni, osim Q12. Ovaj upit uključuje pridruživanja i klauzulu ORDER BY. Zbog klauzule ORDER BY GRUPIŠI / BY i ORDER BY ne padaju do ClickHouse-a.
U tabeli 2 vidimo porast vremena u upitima Q12 i Q13. Ponovićemo, to je uzrokovano klauzulom ORDER BY. Da bismo to potvrdili, izvršio sam upite Q-14 i Q-15 sa klauzulom ORDER BY i bez nje. Bez klauzule ORDER BY vreme izvršenja iznosi 259 ms, dok je sa klauzulom ORDER BY — 1364212. Za debagovanje ovog upita objašnjavao sam oba upita, a ovde 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
PLAN UPITA
Hash Join (cost=2250.00..128516.06 rows=50000000 width=12)
Izlaz: fontime."Year", (((count(*) * 1000)) / b.c2)
Unutrašnja jedinstvenost: true Hash Cond: (fontime."Year" = b."Year")
-> Strani skeniranje (cost=1.00..-1.00 rows=100000 width=12)
Izlaz: fontime."Year", ((count(*) * 1000))
Odnosi: Agregacija na (fontime)
Dalji SQL: SELECT "Year", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Year"
-> Hash (cost=999.00..999.00 rows=100000 width=12)
Izlaz: b.c2, b."Year"
-> Skeniranje podupita na b (cost=1.00..999.00 rows=100000 width=12)
Izlaz: b.c2, b."Year"
-> Strano skeniranje (cost=1.00..-1.00 rows=100000 width=12)
Izlaz: fontime_1."Year", (count(*))
Odnosi: Agregacija na (fontime)
Dalji SQL: SELECT "Year", count(*) FROM "default".ontime GROUP BY "Year"(16 rows)Q14: 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";Q14: Plan upita sa klauzulom ORDER BY
PLAN UPITA
Merge Join (cost=2.00..628498.02 rows=50000000 width=12)
Izlaz: fontime."Year", (((count(*) * 1000)) / (count(*)))
Unutrašnja jedinstvenost: true Merge Cond: (fontime."Year" = fontime_1."Year")
-> Grupna agregacija (cost=1.00..499.01 rows=1 width=12)
Izlaz: fontime."Year", (count(*) * 1000)
Grupni ključ: fontime."Year"
-> Strano skeniranje na public.fontime (cost=1.00..-1.00 rows=100000 width=4)
Dalji SQL: SELECT "Year" FROM "default".ontime WHERE (("DepDelay" > 10))
ORDER BY "Year" ASC
-> Grupna agregacija (cost=1.00..499.01 rows=1 width=12)
Izlaz: fontime_1."Year", count(*) Grupni ključ: fontime_1."Year"
-> Strano skeniranje na public.fontime fontime_1 (cost=1.00..-1.00 rows=100000 width=4)
Dalji SQL: SELECT "Year" FROM "default".ontime ORDER BY "Year" ASC(16 rows)Zaključak
Rezultati ovih eksperimenata pokazuju da ClickHouse nudi zaista dobru performansu, a clickhousedb_fdw pruža prednosti performanse ClickHouse-a unutar PostgreSQL-a. Iako postoje neki troškovi prilikom korišćenja clickhousedb_fdw, oni su neznatni i usporedivi sa performansom postignutom direktnim pokretanjem u bazi podataka ClickHouse. To takođe potvrđuje da fdw u PostgreSQL-u pruža izvanredne rezultate.
Telegram chat za ClickHouse
Telegram chat za PostgreSQL
Izvor: habr.com
