V tej študiji sem želel videti, kakšne izboljšave zmogljivosti je mogoče doseči z uporabo vira podatkov ClickHouse namesto PostgreSQL. Vem, kakšne koristi pri produktivnosti imam z uporabo ClickHouse. Ali se bodo te ugodnosti nadaljevale, če bom dostopal do ClickHouse iz PostgreSQL z uporabo Foreign Data Wrapper (FDW)?
Preučevana okolja baz podatkov so PostgreSQL v11, clickhousedb_fdw in baza podatkov ClickHouse. Končno bomo od PostgreSQL v11 izvajali različne poizvedbe SQL, ki bodo prek našega clickhousedb_fdw usmerjene v bazo podatkov ClickHouse. Nato bomo videli, kakšna je zmogljivost FDW v primerjavi z istimi poizvedbami, ki se izvajajo v izvornem PostgreSQL in izvornem ClickHouse.
Baza podatkov Clickhouse
ClickHouse je odprtokodni stolpčni sistem za upravljanje baz podatkov, ki lahko doseže zmogljivost 100-1000-krat hitreje od tradicionalnih pristopov baz podatkov in lahko obdela več kot milijardo vrstic v manj kot sekundi.
Clickhousedb_fdw
clickhousedb_fdw – zunanji ovoj podatkov za zbirko podatkov ClickHouse ali FDW je odprtokodni projekt podjetja Percona. .
.
Kot boste videli, to zagotavlja FDW za ClickHouse, ki omogoča SELECT iz baze podatkov ClickHouse iz strežnika PostgreSQL v11 in INSERT INTO.
FDW podpira napredne funkcije, kot sta združevanje in združevanje. To bistveno izboljša zmogljivost z uporabo virov oddaljenega strežnika za te operacije, ki zahtevajo veliko virov.
Primerjalno okolje
- Supermicro strežnik:
- Intel® Xeon® CPE E5-2683 v3 @ 2.00 GHz
- 2 vtičnici / 28 jeder / 56 niti
- Pomnilnik: 256 GB RAM-a
- Shramba: Samsung SM863 1.9 TB Enterprise SSD
- Datotečni sistem: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generično #45~16.04.1-Ubuntu
- PostgreSQL: različica 11
Primerjalni testi
Namesto da bi za ta test uporabili nekakšen strojno ustvarjen nabor podatkov, smo uporabili podatke »Produktivnost po času, poročanem operaterskem času« od leta 1987 do 2018. Do podatkov lahko dostopate .
Velikost baze podatkov je 85 GB, kar zagotavlja eno tabelo s 109 stolpci.
Primerjalne poizvedbe
Tu 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
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 Izvor, štetje(*) 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
IZBERITE a.Carrier, c, c2, c1000/c2 kot c3 FROM ( IZBERI nosilec, štetje () 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 BY c3 DESC;
Q6
IZBERITE a.Carrier, c, c2, c1000/c2 kot c3 FROM ( IZBERI nosilec, štetje () 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 Nosilec ) 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
IZBERITE Leto, povprečje (DepDelay) FROM ontime GROUP BY Year;
Q9
izberite leto, štetje (*) kot c1 iz ontime skupine po letu;
Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;
Q11
izberite avg(c1) iz (izberite 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;
Poizvedba vsebuje spoje
Q14
IZBERITE a.Year, c1/c2 FROM (izberite Year, count()1000 kot c1 od ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (izberite leto, štetje(*) kot c2 od ontime GROUP BY Year ) b na a.Year=b.Year ORDER BY a.Year;
Q15
IZBERITE a.”Leto”, c1/c2 FROM ( izberite “Leto”, štetje()1000 kot c1 FROM fonttime WHERE “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (izberite “Year”, šteje (*) kot c2 FROM fontime GROUP BY “Year” ) b na a.”Year”=b. "Leto";
Tabela-1: Poizvedbe, uporabljene v primerjalnem preizkusu
Izvedbe poizvedb
Tukaj so rezultati vsake od poizvedb, ko se izvajajo v različnih nastavitvah baze podatkov: PostgreSQL z in brez indeksov, izvorni ClickHouse in clickhousedb_fdw. Čas je prikazan v milisekundah.
Q#
PostgreSQL
PostgreSQL (indeksirano)
KlikniteHouse
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 izvedbo poizvedb, uporabljenih v primerjalnem preizkusu
Oglejte si rezultate
Graf prikazuje čas izvedbe poizvedbe v milisekundah, os X prikazuje številko poizvedbe iz zgornjih tabel, os Y pa čas izvedbe v milisekundah. Prikazani so rezultati ClickHouse in podatki, pridobljeni iz postgres z uporabo clickhousedb_fdw. Iz tabele lahko vidite, da obstaja velika razlika med PostgreSQL in ClickHouse, a minimalna razlika med ClickHouse in clickhousedb_fdw.
Ta graf prikazuje razliko med ClickhouseDB in clickhousedb_fdw. V večini poizvedb režijski stroški FDW niso tako visoki in so komaj pomembni, razen pri Q12. Ta poizvedba vključuje združevanja in klavzulo ORDER BY. Zaradi klavzule ORDER BY GROUP/BY se ORDER BY ne spusti v ClickHouse.
V tabeli 2 vidimo časovni skok v poizvedbah Q12 in Q13. Ponovno je to posledica klavzule ORDER BY. Da bi to potrdil, sem zagnal poizvedbi Q-14 in Q-15 s klavzulo ORDER BY ali brez nje. Brez klavzule ORDER BY je čas dokončanja 259 ms, s klavzulo ORDER BY pa 1364212. Za odpravljanje napak v tej poizvedbi razlagam obe poizvedbi in tukaj so rezultati razlage.
V15: Brez klavzule 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";V15: Poizvedba brez klavzule 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)V14: Poizvedba s klavzulo 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";V14: Načrt poizvedbe s klavzulo 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)Izhod
Rezultati teh poskusov kažejo, da ClickHouse ponuja res dobro zmogljivost, clickhousedb_fdw pa ponuja prednosti zmogljivosti ClickHouse iz PostgreSQL. Čeprav je pri uporabi clickhousedb_fdw nekaj dodatnih stroškov, so zanemarljivi in primerljivi z zmogljivostjo, doseženo z izvirnim izvajanjem v bazi podatkov ClickHouse. To tudi potrjuje, da fdw v PostgreSQL zagotavlja odlične rezultate.
Telegram klepet prek Clickhouse
Telegramov klepet z uporabo PostgreSQL
Vir: www.habr.com