In questu studiu, aghju vulsutu vede ciò chì migliure di u rendiment puderia esse ottenuta usendu una fonte di dati ClickHouse invece di PostgreSQL. Cunnoscu i benefici di produtividade chì aghju da aduprà ClickHouse. Sti benefici continuanu se accede à ClickHouse da PostgreSQL utilizendu un Wrapper di Dati Esteri (FDW)?
L'ambienti di basa di dati studiati sò PostgreSQL v11, clickhousedb_fdw è a basa di dati ClickHouse. In ultimamente, da PostgreSQL v11, eseguiremu diverse dumande SQL dirette attraversu u nostru clickhousedb_fdw à a basa di dati ClickHouse. Allora videremu cumu a prestazione di FDW si compara cù e stesse dumande in esecuzione in PostgreSQL nativu è ClickHouse nativu.
Clickhouse Database
ClickHouse hè un sistema di gestione di basa di dati colonnari open source chì pò ottene un rendimentu 100-1000 volte più veloce di l'approcciu tradiziunale di basa di dati, capace di processà più di un miliardo di fila in menu di un secondu.
Clickhousedb_fdw
clickhousedb_fdw - U wrapper di dati esterni per a basa di dati ClickHouse, o FDW, hè un prughjettu open source da Percona. .
.
Comu vi vede, questu furnisce un FDW per ClickHouse chì permette SELECT da, è INSERT INTO, a basa di dati ClickHouse da u servitore PostgreSQL v11.
FDW supporta funzioni avanzate cum'è aggregate è join. Questu migliurà significativamente u rendiment utilizendu e risorse di u servitore remoto per queste operazioni intensive di risorse.
Ambiente benchmark
- Supermicro server:
- CPU Intel® Xeon® E5-2683 v3 @ 2.00 GHz
- 2 prese / 28 core / 56 fili
- Memoria: 256GB di RAM
- Storage: Samsung SM863 1.9TB Enterprise SSD
- Filesystem: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: versione 11
Test di benchmark
Invece di utilizà qualchì set di dati generati da a macchina per questa prova, avemu usatu i dati "Produttività per Time Reported Operator Time" da 1987 à 2018. Pudete accede à i dati .
A dimensione di a basa di dati hè 85 GB, chì furnisce una tavola di 109 colonne.
Queries di Benchmark
Eccu e dumande chì aghju utilizatu per paragunà ClickHouse, clickhousedb_fdw è PostgreSQL.
Q#
Query cuntene Aggregati è Group By
Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year>= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDINE BY c DESC;
Q2
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year>= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDINE 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, cunta () FROM ontime WHERE DepDelay> 10 AND Year = 2007 GROUP BY Carrier ORDER BY count () DESC;
Q5
SELECT a.Carrier, c, c2, c1000/c2 cum'è 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 BY c3 DESC;
Q6
SELECT a.Carrier, c, c2, c1000/c2 cum'è 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 Carrier ) b on 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
selezziunà l'annu, cuntà (*) cum'è c1 da u gruppu ontime per annu;
Q10
SELECT avg (cnt) FROM (SELECT Year, Month, count (*) AS cnt FROM ontime WHERE DepDel15 = 1 GROUP BY Year, Month) a;
Q11
selezziunà avg (c1) da (selezziunà 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;
A ricerca cuntene unizzioni
Q14
SELECT a.Year, c1/c2 FROM (selezziunà Year, count()1000 cum'è c1 da ontime WHERE DepDelay> 10 GROUP BY Year) a INNER JOIN (select Year, count(*) as c2 from ontime GROUP BY Year ) b on a.Year=b.Year ORDINE BY a.Year;
Q15
SELEZIONA a "Annu", c1/c2 FROM (selezziunà "Annu", cunta ()1000 as c1 FROM fonttime WHERE "DepDelay"> 10 GROUP BY "Year") a INNER JOIN (selezziunà "Year", count(*) as c2 FROM fonttime GROUP BY "Year") b on a."Year"=b. "Annu";
Table-1: Queries utilizati in benchmark
Esecuzioni di dumanda
Eccu i risultati di ognuna di e dumande quandu eseguite in diverse paràmetri di basa di dati: PostgreSQL cù è senza indici, ClickHouse nativu è clickhousedb_fdw. U tempu hè indicatu in millisecondi.
Q#
PostgreSQL
PostgreSQL (indexatu)
CliccaCasa
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
Table-1: Tempu impiegatu per eseguisce e dumande utilizzate in benchmark
Vede i risultati
U graficu mostra u tempu d'esekzione di a dumanda in millisecondi, l'assi X mostra u numeru di dumanda da e tavule sopra, è l'assi Y mostra u tempu d'esekzione in millisecondi. I risultati di ClickHouse è i dati recuperati da postgres cù clickhousedb_fdw sò mostrati. Da a tavula pudete vede chì ci hè una gran diferenza trà PostgreSQL è ClickHouse, ma minima differenza trà ClickHouse è clickhousedb_fdw.
Stu graficu mostra a diffarenza trà ClickhouseDB è clickhousedb_fdw. In a maiò parte di e dumande, u FDW overhead ùn hè micca cusì altu è hè pocu significativu eccettu per Q12. Questa dumanda include joins è una clause ORDER BY. A causa di a clausola ORDER BY GROUP/BY, ORDER BY ùn scende micca à ClickHouse.
In a Tabella 2 vedemu u saltu di tempu in e dumande Q12 è Q13. In novu, questu hè causatu da a clause ORDER BY. Per cunfirmà questu, aghju eseguitu e dumande Q-14 è Q-15 cù e senza clause ORDER BY. Senza a clausula ORDER BY u tempu di cumpiimentu hè 259ms è cù a clausula ORDER BY hè 1364212. Per debug sta query, spieghemu e duie dumande è quì sò i risultati di a spiegazione.
Q15: Senza Clause 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: Interrogazione Senza Clause 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)Q14: Query cù ORDER BY Clause
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: Pianu di Query cù ORDER BY Clause
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)cunchiusioni
I risultati di sti esperimenti mostranu chì ClickHouse offre un rendimentu veramente bonu, è clickhousedb_fdw offre i benefici di rendiment di ClickHouse da PostgreSQL. Mentre chì ci hè un pocu di overhead quandu si usa clickhousedb_fdw, hè insignificante è paragunabile à u rendiment ottenutu da eseguisce nativamente nantu à a basa di dati ClickHouse. Questu cunfirma ancu chì fdw in PostgreSQL furnisce risultati eccellenti.
Chat di Telegram via Clickhouse
Chat di Telegram cù PostgreSQL
Source: www.habr.com