In questo studio, volevo vedere quali miglioramenti delle prestazioni si potevano ottenere utilizzando un'origine dati ClickHouse anziché PostgreSQL. Conosco i vantaggi in termini di produttività che ottengo dall'utilizzo di ClickHouse. Questi vantaggi continueranno se accedo a ClickHouse da PostgreSQL utilizzando un Foreign Data Wrapper (FDW)?
Gli ambienti database studiati sono PostgreSQL v11, clickhousedb_fdw e il database ClickHouse. In definitiva, da PostgreSQL v11 eseguiremo varie query SQL instradate attraverso il nostro clickhousedb_fdw al database ClickHouse. Vedremo quindi come le prestazioni di FDW si confrontano con le stesse query eseguite in PostgreSQL nativo e ClickHouse nativo.
Banca dati ClickHouse
ClickHouse è un sistema di gestione di database colonnare open source in grado di raggiungere prestazioni 100-1000 volte più veloci rispetto agli approcci tradizionali ai database, in grado di elaborare oltre un miliardo di righe in meno di un secondo.
Clickhousedb_fdw
clickhousedb_fdw - Il wrapper di dati esterno per il database ClickHouse, o FDW, è un progetto open source di Percona. .
.
Come vedrai, questo fornisce un FDW per ClickHouse che consente di SELEZIONARE e INSERIRE nel database ClickHouse dal server PostgreSQL v11.
FDW supporta funzionalità avanzate come aggregazione e unione. Ciò migliora significativamente le prestazioni utilizzando le risorse del server remoto per queste operazioni ad uso intensivo di risorse.
Ambiente di riferimento
- Server supermicro:
- CPU Intel® Xeon® E5-2683 v3 a 2.00 GHz
- 2 socket / 28 core / 56 thread
- Memoria: 256GB di RAM
- Memoria: SSD aziendale Samsung SM863 da 1.9 TB
- File system: ext4/xfs
- Sistema operativo: Linux smblade01 4.15.0-42-generico #45~16.04.1-Ubuntu
- PostgreSQL: versione 11
Test di riferimento
Invece di utilizzare alcuni set di dati generati dalla macchina per questo test, abbiamo utilizzato i dati "Produttività per tempo riferito al tempo dell'operatore" dal 1987 al 2018. È possibile accedere ai dati .
La dimensione del database è 85 GB e fornisce una tabella di 109 colonne.
Query di riferimento
Ecco le query che ho utilizzato per confrontare ClickHouse, clickhousedb_fdw e PostgreSQL.
Q#
La query contiene aggregati e raggruppa per
Q1
SELECT DayOfWeek, count (*) AS c FROM ontime WHERE Anno >= 2000 AND Anno <= 2008 GRUPPO PER DayOfWeek ORDER BY c DESC;
Q2
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Anno >= 2000 AND Anno <= 2008 GRUPPO PER DayOfWeek ORDER BY c DESC;
Q3
SELECT Origine, conteggio (*) AS c FROM ontime WHERE DepDelay>10 AND Anno >= 2000 AND Anno <= 2008 GRUPPO PER Origine ORDER BY c DESC LIMIT 10;
Q4
SELEZIONA Corriere, conta() FROM ontime WHERE DepDelay>10 AND Anno = 2007 GRUPPO PER Operatore ORDER BY conteggio() DESC;
Q5
SELEZIONARE a.Portante, c, c2, c1000/c2 come c3 FROM ( SELECT Portante, conta() AS c FROM ontime WHERE DepDelay>10 AND Anno=2007 GRUPPO BY Operatore ) a INNER JOIN ( SELECT Operatore,count(*) AS c2 FROM ontime WHERE Anno=2007 GRUPPO BY Operatore)b on a.Operatore=b.Operatore ORDER PER c3 DESC;
Q6
SELEZIONARE a.Portante, c, c2, c1000/c2 come c3 FROM ( SELECT Portante, conta() AS c FROM ontime WHERE DepDelay>10 AND Anno >= 2000 AND Anno <= 2008 GROUP BY Operatore) a INNER JOIN ( SELECT Operatore, count(*) AS c2 FROM ontime WHERE Anno >= 2000 AND Anno <= 2008 GROUP BY Vettore ) b su a.Vettore=b.Vettore ORDER BY c3 DESC;
Q7
SELECT Operatore, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Anno >= 2000 AND Anno <= 2008 GROUP BY Operatore;
Q8
SELECT Anno, avg(DepDelay) FROM ontime GRUPPO PER Anno;
Q9
selezionare Anno, contare (*) come c1 dal gruppo ontime per Anno;
Q10
SELECT avg(cnt) FROM (SELECT Anno,Mese,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Anno,Mese) a;
Q11
select avg(c1) from (select Anno,Mese,count(*) as c1 from ontime group by Anno,Mese) a;
Q12
SELEZIONA NomeCittàOrigine, NomeCittàDest, conteggio(*) AS c FROM ontime GRUPPO PER NomeCittàOrigine, NomeCittàDest ORDER BY c DESC LIMIT 10;
Q13
SELECT NomeCittàOrigine, count(*) AS c FROM ontime GRUPPO PER NomeCittàOrigine ORDER BY c DESC LIMIT 10;
La query contiene join
Q14
SELECT a.Anno, c1/c2 FROM ( seleziona Anno, conta()1000 come c1 da ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (seleziona Anno, conta (*) come c2 da ontime GROUP BY Year ) b su a.Year=b.Year ORDER BY a.Year;
Q15
SELEZIONARE a."Anno", c1/c2 DA ( selezionare "Anno", contare()1000 come c1 DA fontime WHERE “DepDelay”>10 GRUPPO PER “Anno”) a INNER JOIN (seleziona “Anno”, conta (*) come c2 DA fontime GRUPPO PER “Anno” ) b su a.”Anno”=b. "Anno";
Tabella 1: query utilizzate nel benchmark
Esecuzioni di query
Ecco i risultati di ciascuna query eseguita in diverse impostazioni del database: PostgreSQL con e senza indici, ClickHouse nativa e clickhousedb_fdw. Il tempo è mostrato in millisecondi.
Q#
PostgreSQL
PostgreSQL (indicizzato)
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
Tabella 1: tempo impiegato per eseguire le query utilizzate nel benchmark
Vedi i risultati
Il grafico mostra il tempo di esecuzione della query in millisecondi, l'asse X mostra il numero di query dalle tabelle sopra e l'asse Y mostra il tempo di esecuzione in millisecondi. Vengono visualizzati i risultati di ClickHouse e i dati recuperati da Postgres utilizzando clickhousedb_fdw. Dalla tabella puoi vedere che c'è un'enorme differenza tra PostgreSQL e ClickHouse, ma una differenza minima tra ClickHouse e clickhousedb_fdw.
Questo grafico mostra la differenza tra ClickhouseDB e clickhousedb_fdw. Nella maggior parte delle query, l'overhead dell'FDW non è così elevato e difficilmente significativo, ad eccezione del trimestre 12. Questa query include join e una clausola ORDER BY. A causa della clausola ORDER BY GROUP/BY, ORDER BY non scende in ClickHouse.
Nella tabella 2 vediamo il salto temporale nelle query Q12 e Q13. Ancora una volta, ciò è causato dalla clausola ORDER BY. Per confermarlo, ho eseguito le query Q-14 e Q-15 con e senza la clausola ORDER BY. Senza la clausola ORDER BY il tempo di completamento è 259 ms e con la clausola ORDER BY è 1364212. Per eseguire il debug di questa query, spiego entrambe le query ed ecco i risultati della spiegazione.
D15: Senza clausola 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: query senza clausola 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 con clausola 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: Piano di query con clausola 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)conclusione
I risultati di questi esperimenti mostrano che ClickHouse offre prestazioni davvero buone e clickhousedb_fdw offre i vantaggi prestazionali di ClickHouse di PostgreSQL. Sebbene vi sia un certo sovraccarico quando si utilizza clickhousedb_fdw, è trascurabile e paragonabile alle prestazioni ottenute eseguendo in modo nativo il database ClickHouse. Ciò conferma anche che fdw in PostgreSQL fornisce risultati eccellenti.
Chatta su Telegram tramite Clickhouse
Chatta su Telegram utilizzando PostgreSQL
Fonte: habr.com