Prova del rendiment de les consultes analítiques a PostgreSQL, ClickHouse i clickhousedb_fdw (PostgreSQL)

En aquest estudi, volia veure quines millores de rendiment es podrien aconseguir utilitzant una font de dades ClickHouse en lloc de PostgreSQL. Conec els avantatges de productivitat que obtinc d'utilitzar ClickHouse. Continuaran aquests avantatges si accedeixo a ClickHouse des de PostgreSQL mitjançant un embolcall de dades estrangeres (FDW)?

Els entorns de bases de dades estudiats són PostgreSQL v11, clickhousedb_fdw i la base de dades ClickHouse. En última instància, des de PostgreSQL v11 executarem diverses consultes SQL dirigides a través del nostre clickhousedb_fdw a la base de dades de ClickHouse. A continuació, veurem com es compara el rendiment de FDW amb les mateixes consultes que s'executen a PostgreSQL natiu i ClickHouse natiu.

Base de dades Clickhouse

ClickHouse és un sistema de gestió de bases de dades de codi obert que pot assolir un rendiment 100-1000 vegades més ràpid que els enfocaments tradicionals de bases de dades, capaç de processar més de mil milions de files en menys d'un segon.

Clickhousedb_fdw

clickhousedb_fdw: l'embolcall de dades extern per a la base de dades ClickHouse, o FDW, és un projecte de codi obert de Percona. Aquí teniu un enllaç al repositori GitHub del projecte.

Al març vaig escriure un blog que t'explica més sobre la nostra FDW.

Com veureu, això proporciona un FDW per a ClickHouse que permet SELECT i INSERT INTO de la base de dades ClickHouse del servidor PostgreSQL v11.

FDW admet funcions avançades com ara agregar i unir. Això millora significativament el rendiment mitjançant l'ús dels recursos del servidor remot per a aquestes operacions que consumeixen molts recursos.

Entorn de referència

• Servidor Supermicro:
  • CPU Intel® Xeon® E5-2683 v3 @ 2.00 GHz
  • 2 preses / 28 nuclis / 56 fils
  • Memòria: 256 GB de RAM
  • Emmagatzematge: Samsung SM863 1.9 TB Enterprise SSD
  • Sistema de fitxers: ext4/xfs
• Sistema operatiu: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
• PostgreSQL: versió 11

Proves de referència

En lloc d'utilitzar alguns conjunts de dades generades per màquina per a aquesta prova, hem utilitzat les dades de "Productivitat per temps del temps de l'operador informat" del 1987 al 2018. Podeu accedir a les dades utilitzant el nostre script disponible aquí.

La mida de la base de dades és de 85 GB, proporcionant una taula de 109 columnes.

Consultes de referència

Aquestes són les consultes que vaig utilitzar per comparar ClickHouse, clickhousedb_fdw i PostgreSQL.

Q#
La consulta conté agregats i agrupa per

Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Any >= 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 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, count() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() DESC;

Q5
SELECCIONA a.Portador, c, c2, c1000/c2 com 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 Any=2007 GROUP BY Carrier)b on a.Carrier=b.Carrier ORDER PER c3 DESC;

Q6
SELECCIONA a.Portador, c, c2, c1000/c2 com 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 Any >= 2000 AND Year <= 2008 GROUP BY Transportista ) b en a.Carrier=b.Carrier ORDER BY c3 DESC;

Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Any >= 2000 AND Year <= 2008 GROUP BY Transportista;

Q8
SELECT Year, avg(DepDelay) FROM ontime GRUP PER Any;

Q9
seleccionar Any, comptar (*) com a c1 del grup ontime per Any;

Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;

Q11
seleccioneu avg(c1) de (seleccioneu Any,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 GRUP PER OriginCityName ORDER BY c DESC LIMIT 10;

La consulta conté unions

Q14
SELECT a.Year, c1/c2 FROM (seleccioneu Any, count()1000 com a c1 des de ontime WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (seleccioneu Any, compta(*) com a c2 de ontime GROUP BY Year ) b en a.Year=b.Year ORDER BY a.Year;

Q15
SELECCIONA a."Any", c1/c2 DE (selecciona "Any", compta ()1000 com c1 FROM fonttime WHERE “DepDelay”>10 GROUP PER “Year”) a INNER JOIN (seleccioneu “Year”, compta(*) com a c2 FROM fonttime GROUP BY “Year” ) b en a.”Year”=b. "Curs";

Taula-1: consultes utilitzades en benchmark

Execucions de consulta

Aquests són els resultats de cadascuna de les consultes quan s'executen en diferents configuracions de base de dades: PostgreSQL amb i sense índexs, ClickHouse natiu i clickhousedb_fdw. El temps es mostra en mil·lisegons.

Q#
PostgreSQL
PostgreSQL (indexat)
Feu clic a Casa
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

Taula-1: Temps necessari per executar les consultes utilitzades al benchmark

Veure resultats

El gràfic mostra el temps d'execució de la consulta en mil·lisegons, l'eix X mostra el número de consulta de les taules anteriors i l'eix Y mostra el temps d'execució en mil·lisegons. Es mostren els resultats de ClickHouse i les dades recuperades de Postgres mitjançant clickhousedb_fdw. A la taula podeu veure que hi ha una gran diferència entre PostgreSQL i ClickHouse, però una diferència mínima entre ClickHouse i clickhousedb_fdw.

Aquest gràfic mostra la diferència entre ClickhouseDB i clickhousedb_fdw. En la majoria de consultes, la sobrecàrrega FDW no és tan alta i gairebé no és significativa, excepte per a la Q12. Aquesta consulta inclou unions i una clàusula ORDER BY. A causa de la clàusula ORDER BY GROUP/BY, ORDER BY no es desplega a ClickHouse.

A la taula 2 veiem el salt de temps a les consultes Q12 i Q13. De nou, això és causat per la clàusula ORDER BY. Per confirmar-ho, vaig executar les consultes Q-14 i Q-15 amb i sense la clàusula ORDER BY. Sense la clàusula ORDER BY el temps de finalització és de 259 ms i amb la clàusula ORDER BY és 1364212. Per depurar aquesta consulta explico les dues consultes i aquí teniu els resultats de l'explicació.

P15: Sense clàusula 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";

P15: Consulta sense clàusula 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)

P14: Consulta amb la clàusula 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";

P14: Pla de consulta amb clàusula 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)

Sortida

Els resultats d'aquests experiments mostren que ClickHouse ofereix un rendiment molt bo i clickhousedb_fdw ofereix els avantatges de rendiment de ClickHouse de PostgreSQL. Tot i que hi ha una mica de sobrecàrrega quan s'utilitza clickhousedb_fdw, és insignificant i comparable al rendiment assolit executant-se de manera nativa a la base de dades ClickHouse. Això també confirma que fdw a PostgreSQL proporciona resultats excel·lents.

Xat de Telegram a través de Clickhouse https://t.me/clickhouse_ru
Xat de Telegram amb PostgreSQL https://t.me/pgsql

Font: www.habr.com