ProHoster > Blog > Amministrazione > Test di performance delle query analitiche in PostgreSQL, ClickHouse e clickhousedb_fdw (PostgreSQL)

Test di performance delle query analitiche in PostgreSQL, ClickHouse e clickhousedb_fdw (PostgreSQL)

In questa ricerca volevo esaminare quali miglioramenti delle prestazioni si possono ottenere utilizzando la sorgente dati ClickHouse anziché PostgreSQL. So quali vantaggi in termini di prestazioni ho con ClickHouse. Questi vantaggi saranno mantenuti se accedo a ClickHouse da PostgreSQL tramite un collegamento ai dati esterni (FDW)?

Gli ambienti di database esaminati sono PostgreSQL v11, clickhousedb_fdw e il database ClickHouse. In definitiva, da PostgreSQL v11 eseguiremo diverse query SQL, instradate tramite il nostro clickhousedb_fdw al database ClickHouse. Poi vedremo come le prestazioni di FDW si confrontano con le stesse query eseguite in nativo su PostgreSQL e su ClickHouse nativo.

Database Clickhouse

ClickHouse è un sistema di gestione di database basato su colonne open source che può raggiungere prestazioni 100-1000 volte superiori rispetto agli approcci tradizionali ai database, in grado di gestire più di un miliardo di righe in meno di un secondo.

Clickhousedb_fdw

clickhousedb_fdw è una shell per dati esterni del database ClickHouse, o FDW, ed è un progetto open source di Percona. Ecco il link al repository del progetto GitHub.

In marzo ho scritto un blog che ti racconta di più sul nostro FDW.

Come vedrai, questo fornisce un FDW per ClickHouse che consente di SELECT from e INSERT INTO il database ClickHouse da un server PostgreSQL v11.

FDW supporta funzionalità avanzate come aggregate e join. Questo aumenta significativamente le prestazioni facendo uso delle risorse del server remoto per queste operazioni intensive.

Benchmark environment

  • Server Supermicro:
    • Intel® Xeon® CPU E5-2683 v3 @ 2,00GHz
    • 2 socket / 28 core / 56 thread
    • 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

Benchmark tests

Invece di utilizzare un insieme di dati generato dalla macchina per questo test, abbiamo utilizzato i dati 'Performance per tempo riportata sull'operatore' dal 1987 al 2018. Puoi accedere ai dati utilizzando il nostro script disponibile qui.

La dimensione del database è di 85 GB, fornendo una tabella con 109 colonne.

Benchmark Queries

Ecco le query che ho usato per confrontare ClickHouse, clickhousedb_fdw e PostgreSQL.

Q#
Query contiene aggregate e Group By

Q1
SELEZIONA GiornoDellaSettimana, count(*) AS c DA ontime DOVE Anno >= 2000 E Anno <= 2008 RAGGRUPPA PER GiornoDellaSettimana ORDINARE PER c DESC;

Q2
SELEZIONA GiornoDellaSettimana, count(*) AS c DA ontime DOVE DepDelay > 10 E Anno >= 2000 E Anno <= 2008 RAGGRUPPA PER GiornoDellaSettimana ORDINARE PER c DESC;

Q3
SELEZIONA Origine, count(*) AS c DA ontime DOVE DepDelay > 10 E Anno >= 2000 E Anno <= 2008 RAGGRUPPA PER Origine ORDINARE PER c DESC LIMIT 10;

Q4
SELEZIONA Vettore, count() DA ontime DOVE DepDelay > 10 E Anno = 2007 RAGGRUPPA PER Vettore ORDINARE PER count() DESC;

Q5
SELEZIONA a.Vettore, c, c2, c1000/c2 come c3 DA ( SELEZIONA Vettore, count() AS c DA ontime DOVE DepDelay > 10 E Anno = 2007 RAGGRUPPA PER Vettore ) a INNER JOIN ( SELEZIONA Vettore,count(*) AS c2 DA ontime DOVE Anno = 2007 RAGGRUPPA PER Vettore ) b ON a.Vettore = b.Vettore ORDINARE PER c3 DESC;

Q6
SELEZIONA a.Vettore, c, c2, c1000/c2 come c3 DA ( SELEZIONA Vettore, count() AS c DA ontime DOVE DepDelay > 10 E Anno >= 2000 E Anno = 2000 E Anno <= 2008 RAGGRUPPA PER Vettore ) b ON a.Vettore = b.Vettore ORDINARE PER c3 DESC;

Q7
SELEZIONA Vettore, avg(DepDelay) * 1000 AS c3 DA ontime DOVE Anno >= 2000 E Anno <= 2008 RAGGRUPPA PER Vettore;

Q8
SELEZIONA Anno, avg(DepDelay) DA ontime RAGGRUPPA PER Anno;

Q9
SELEZIONA Anno, count(*) AS c1 DA ontime RAGGRUPPA PER Anno;

Q10
SELEZIONA avg(cnt) DA (SELEZIONA Anno, Mese, count(*) AS cnt DA ontime DOVE DepDel15 = 1 RAGGRUPPA PER Anno, Mese) a;

Q11
SELEZIONA avg(c1) DA (SELEZIONA Anno, Mese, count(*) AS c1 DA ontime RAGGRUPPA PER Anno, Mese) a;

Q12
SELEZIONA NomeCittàOrigine, NomeCittàDestinazione, count(*) AS c DA ontime RAGGRUPPA PER NomeCittàOrigine, NomeCittàDestinazione ORDINARE PER c DESC LIMIT 10;

Q13
SELEZIONA NomeCittàOrigine, count(*) AS c DA ontime RAGGRUPPA PER NomeCittàOrigine ORDINARE PER c DESC LIMIT 10;

La query contiene join

Q14
SELEZIONA a.Anno, c1/c2 DA ( SELEZIONA Anno, count()1000 AS c1 DA ontime DOVE DepDelay > 10 RAGGRUPPA PER Anno) a INNER JOIN (SELEZIONA Anno, count(*) AS c2 DA ontime RAGGRUPPA PER Anno) b ON a.Anno = b.Anno ORDINARE PER a.Anno;

Q15
SELEZIONA a."Anno", c1/c2 DA ( SELEZIONA "Anno", count()1000 AS c1 DA fontime DOVE "DepDelay" > 10 RAGGRUPPA PER "Anno") a INNER JOIN (SELEZIONA "Anno", count(*) AS c2 DA fontime RAGGRUPPA PER "Anno") b ON a."Anno" = b."Anno";

Tabella-1: Query utilizzate nel benchmark

Esecuzioni delle query

Ecco i risultati di ciascuna delle query eseguite in diverse configurazioni del database: PostgreSQL con e senza indici, ClickHouse proprietario e clickhousedb_fdw. Il tempo è mostrato in millisecondi.

Q#
PostgreSQL
PostgreSQL (Indicizzato)
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

Tabella-1: Tempo impiegato per eseguire le query utilizzate nel benchmark

Visualizzazione dei risultati

Il grafico mostra il tempo di esecuzione della query in millisecondi, l'asse X mostra il numero della query dalle tabelle sopra, mentre l'asse Y mostra il tempo di esecuzione in millisecondi. I risultati di ClickHouse e i dati ottenuti da postgres tramite clickhousedb_fdw sono mostrati. Dalla tabella si può notare che esiste una grande differenza tra PostgreSQL e ClickHouse, ma una minima differenza tra ClickHouse e clickhousedb_fdw.

Test di performance delle query analitiche in PostgreSQL, ClickHouse e clickhousedb_fdw (PostgreSQL)

Questo grafico mostra la differenza tra ClickhouseDB e clickhousedb_fdw. Nella maggior parte delle query, i costi aggiuntivi di FDW non sono così elevati e sono appena significativi, tranne che per Q12. Questa query comporta join e una clausola ORDER BY. A causa della clausola ORDER BY GROUP/BY e di ORDINARE PER non vengono omessi per ClickHouse.

Nella tabella 2 vediamo un picco nei tempi delle query Q12 e Q13. Ripeto, questo è causato dalla clausola ORDER BY. Per confermare questo, 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 è di 259 ms, mentre con la clausola ORDER BY è di 1364212. Per il debug di questa query, spiego entrambe le query, e qui vengono mostrati i risultati dell'analisi.

Q15: Senza clausola ORDER BY

bm=# EXPLAIN VERBOSE SELECT a."Anno", c1/c2 
     FROM (SELECT "Anno", count(*)*1000 AS c1 FROM fontime WHERE "DepDelay" > 10 GROUP BY "Anno") a
     INNER JOIN(SELECT "Anno", count(*) AS c2 FROM fontime GROUP BY "Anno") b ON a."Anno"=b."Anno";

Q15: Query senza clausola ORDER BY

QUERY PLAN                                                      
Hash Join  (cost=2250.00..128516.06 rows=50000000 width=12)  
Output: fontime."Anno", (((count(*) * 1000)) / b.c2)  
Inner Unique: true   Hash Cond: (fontime."Anno" = b."Anno")  
->  Foreign Scan  (cost=1.00..-1.00 rows=100000 width=12)        
Output: fontime."Anno", ((count(*) * 1000))        
Relations: Aggregate on (fontime)        
Remote SQL: SELECT "Anno", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Anno"  
->  Hash  (cost=999.00..999.00 rows=100000 width=12)        
Output: b.c2, b."Anno"        
->  Subquery Scan on b  (cost=1.00..999.00 rows=100000 width=12)              
Output: b.c2, b."Anno"              
->  Foreign Scan  (cost=1.00..-1.00 rows=100000 width=12)                    
Output: fontime_1."Anno", (count(*))                    
Relations: Aggregate on (fontime)                    
Remote SQL: SELECT "Anno", count(*) FROM "default".ontime GROUP BY "Anno"(16 rows)

Q14: Query con clausola ORDER BY

bm=# EXPLAIN VERBOSE SELECT a."Anno", c1/c2 FROM(SELECT "Anno", count(*)*1000 AS c1 FROM fontime WHERE "DepDelay" > 10 GROUP BY "Anno") a 
     INNER JOIN(SELECT "Anno", count(*) as c2 FROM fontime GROUP BY "Anno") b  ON a."Anno"= b."Anno" 
     ORDER BY a."Anno";

Q14: Piano della query con clausola ORDER BY

QUERY PLAN 
Merge Join  (cost=2.00..628498.02 rows=50000000 width=12)   
Output: fontime."Anno", (((count(*) * 1000)) / (count(*)))   
Inner Unique: true   Merge Cond: (fontime."Anno" = fontime_1."Anno")   
->  GroupAggregate  (cost=1.00..499.01 rows=1 width=12)        
Output: fontime."Anno", (count(*) * 1000)         
Group Key: fontime."Anno"         
->  Foreign Scan on public.fontime  (cost=1.00..-1.00 rows=100000 width=4)               
Remote SQL: SELECT "Anno" FROM "default".ontime WHERE (("DepDelay" > 10)) 
            ORDER BY "Anno" ASC   
->  GroupAggregate  (cost=1.00..499.01 rows=1 width=12)         
Output: fontime_1."Anno", count(*)         Group Key: fontime_1."Anno"         
->  Foreign Scan on public.fontime fontime_1  (cost=1.00..-1.00 rows=100000 width=4) 
              
Remote SQL: SELECT "Anno" FROM "default".ontime ORDER BY "Anno" ASC(16 rows)

Conclusione

I risultati di questi esperimenti mostrano che ClickHouse offre davvero buone prestazioni e che clickhousedb_fdw offre i vantaggi delle prestazioni di ClickHouse a PostgreSQL. Sebbene vi siano alcune spese generali nell'uso di clickhousedb_fdw, sono trascurabili e comparabili alle prestazioni raggiunte quando si utilizza direttamente il database ClickHouse. Questo conferma anche che fdw in PostgreSQL fornisce risultati notevoli.

Chat Telegram su Clickhouse https://t.me/clickhouse_ru
Chat Telegram su PostgreSQL https://t.me/pgsql

Fonte: habr.com

Acquista hosting affidabile per siti web con protezione DDoS, VPS VDS server 🔥 Acquista hosting affidabile per siti web con protezione DDoS, VPS VDS server | ProHoster