În acest studiu, am vrut să văd ce îmbunătățiri de performanță ar putea fi obținute folosind o sursă de date ClickHouse, mai degrabă decât PostgreSQL. Cunosc beneficiile de productivitate pe care le obțin folosind ClickHouse. Vor continua aceste beneficii dacă accesez ClickHouse din PostgreSQL utilizând un Foreign Data Wrapper (FDW)?
Mediile de baze de date studiate sunt PostgreSQL v11, clickhousedb_fdw și baza de date ClickHouse. În cele din urmă, de la PostgreSQL v11 vom rula diverse interogări SQL direcționate prin clickhousedb_fdw către baza de date ClickHouse. Vom vedea apoi cum se compară performanța FDW cu aceleași interogări care rulează în PostgreSQL nativ și ClickHouse nativ.
Baza de date Clickhouse
ClickHouse este un sistem open source de gestionare a bazelor de date în coloană care poate atinge performanțe de 100-1000 de ori mai rapid decât abordările tradiționale de baze de date, capabil să proceseze peste un miliard de rânduri în mai puțin de o secundă.
Clickhousedb_fdw
clickhousedb_fdw - Învelișul extern de date pentru baza de date ClickHouse, sau FDW, este un proiect open source de la Percona. .
.
După cum veți vedea, acesta oferă un FDW pentru ClickHouse care permite SELECTARE și INSERARE în baza de date ClickHouse de pe serverul PostgreSQL v11.
FDW acceptă funcții avansate, cum ar fi agregare și unire. Acest lucru îmbunătățește semnificativ performanța prin utilizarea resurselor serverului de la distanță pentru aceste operațiuni care necesită mult resurse.
Mediu de referință
- Server Supermicro:
- CPU Intel® Xeon® E5-2683 v3 @ 2.00 GHz
- 2 prize / 28 miezuri / 56 fire
- Memorie: 256GB de RAM
- Stocare: Samsung SM863 1.9TB Enterprise SSD
- Sistem de fișiere: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: versiunea 11
Teste de referință
În loc să folosim un set de date generat de mașini pentru acest test, am folosit datele „Productivitate în funcție de timp raportat timp operator” din 1987 până în 2018. Puteți accesa datele .
Dimensiunea bazei de date este de 85 GB, oferind un tabel de 109 coloane.
Interogări de referință
Iată interogările pe care le-am folosit pentru a compara ClickHouse, clickhousedb_fdw și PostgreSQL.
Q#
Interogarea conține agregate și grupați după
Q1
SELECTAȚI Ziua Săptămânii, numărați(*) AS c FROM ontime WHERE Anul >= 2000 ȘI Anul <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2
SELECTează Ziua Săptămânii, numără(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q3
SELECT Origine, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;
Q4
SELECTARE Transportator, numărare() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() DESC;
Q5
SELECTAȚI a.Carrier, c, c2, c1000/c2 ca c3 FROM ( SELECTARE Transportator, 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 pe a.Carrier=b.Carrier ORDER PRIN c3 DESC;
Q6
SELECTAȚI a.Carrier, c, c2, c1000/c2 ca c3 FROM ( SELECTARE Transportator, count() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Transportator) a INNER JOIN ( SELECT Carrier, count(*) AS c2 FROM ontime WHERE Anul >= 2000 AND Year <= 2008 GROUP BY Transportator ) b pe a.Carrier=b.Carrier ORDER BY c3 DESC;
Q7
SELECT Transportator, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Anul >= 2000 AND Year <= 2008 GROUP BY Transportator;
Q8
SELECTARE Anul, avg(DepDelay) FROM ontime GROUP BY Year;
Q9
selectați Anul, numărați(*) ca c1 din grupul ontime după An;
Q10
SELECT AVg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;
Q11
selectați avg(c1) din (selectați Year,Month,count(*) as c1 din 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;
Interogarea conține îmbinări
Q14
SELECTAȚI a.Year, c1/c2 FROM (selectați Year, count()1000 ca c1 din ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (selectați Anul, numărați(*) ca c2 din ontime GROUP BY Year ) b pe a.Year=b.Year ORDER BY a.Year;
Q15
SELECTAȚI a.”Anul”, c1/c2 FROM (selectați „Anul”, count()1000 ca c1 FROM fonttime WHERE „DepDelay”>10 GROUP BY „Year”) a INNER JOIN (selectați „Year”, numărați(*) ca c2 FROM fonttime GROUP BY „Year” ) b pe a.”Year”=b. "An";
Tabelul-1: Interogări utilizate în benchmark
Execuții de interogare
Iată rezultatele fiecărei interogări atunci când sunt executate în diferite setări ale bazei de date: PostgreSQL cu și fără indexuri, ClickHouse nativ și clickhousedb_fdw. Timpul este afișat în milisecunde.
Q#
PostgreSQL
PostgreSQL (indexat)
Faceți clic pe Casă
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
Tabelul 1: Timpul necesar pentru a executa interogările utilizate în benchmark
Vezi rezultate
Graficul arată timpul de execuție a interogării în milisecunde, axa X arată numărul de interogare din tabelele de mai sus, iar axa Y arată timpul de execuție în milisecunde. Sunt afișate rezultatele ClickHouse și datele preluate de la postgres folosind clickhousedb_fdw. Din tabel puteți vedea că există o diferență uriașă între PostgreSQL și ClickHouse, dar o diferență minimă între ClickHouse și clickhousedb_fdw.
Acest grafic arată diferența dintre ClickhouseDB și clickhousedb_fdw. În majoritatea interogărilor, costul general FDW nu este atât de mare și nu este semnificativ, cu excepția Q12. Această interogare include îmbinări și o clauză ORDER BY. Din cauza clauzei ORDER BY GROUP/BY, ORDER BY nu apare în ClickHouse.
În tabelul 2 vedem saltul de timp în interogările Q12 și Q13. Din nou, acest lucru este cauzat de clauza ORDER BY. Pentru a confirma acest lucru, am rulat interogările Q-14 și Q-15 cu și fără clauza ORDER BY. Fără clauza ORDER BY timpul de finalizare este de 259ms, iar cu clauza ORDER BY este 1364212. Pentru a depana această interogare, explic ambele interogări și aici sunt rezultatele explicației.
Q15: Fără Clauza 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";Î15: Interogare fără clauză 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)Î14: Interogare cu clauza 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";Î14: Plan de interogare cu clauza 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)Producție
Rezultatele acestor experimente arată că ClickHouse oferă performanțe foarte bune, iar clickhousedb_fdw oferă beneficiile de performanță ale ClickHouse de la PostgreSQL. Deși există o suprasarcină la utilizarea clickhousedb_fdw, este neglijabilă și comparabilă cu performanța obținută prin rularea nativă în baza de date ClickHouse. Acest lucru confirmă, de asemenea, că fdw în PostgreSQL oferă rezultate excelente.
Chat Telegram prin Clickhouse
Chat Telegram folosind PostgreSQL
Sursa: www.habr.com