Neste estudo, quería ver que melloras de rendemento se poderían conseguir usando unha fonte de datos ClickHouse en lugar de PostgreSQL. Coñezo os beneficios de produtividade que teño ao usar ClickHouse. Continuarán estes beneficios se accedo a ClickHouse desde PostgreSQL usando un Foreign Data Wrapper (FDW)?
Os entornos de bases de datos estudados son PostgreSQL v11, clickhousedb_fdw e ClickHouse. En definitiva, desde PostgreSQL v11 executaremos varias consultas SQL enviadas a través do noso clickhousedb_fdw á base de datos ClickHouse. Despois veremos como se compara o rendemento de FDW coas mesmas consultas que se executan en PostgreSQL nativo e ClickHouse nativo.
Base de datos Clickhouse
ClickHouse é un sistema de xestión de bases de datos en columnas de código aberto que pode acadar un rendemento 100-1000 veces máis rápido que os enfoques tradicionais de bases de datos, capaz de procesar máis de mil millóns de filas en menos dun segundo.
Clickhousedb_fdw
clickhousedb_fdw - O envoltorio de datos externo para a base de datos ClickHouse, ou FDW, é un proxecto de código aberto de Percona. .
.
Como verás, isto proporciona un FDW para ClickHouse que permite SELECCIONAR e INSERTAR a base de datos ClickHouse desde o servidor PostgreSQL v11.
FDW admite funcións avanzadas como agregar e unir. Isto mellora significativamente o rendemento mediante o uso dos recursos do servidor remoto para estas operacións de uso intensivo de recursos.
Entorno de referencia
- Servidor Supermicro:
- CPU Intel® Xeon® E5-2683 v3 @ 2.00GHz
- 2 tomas / 28 núcleos / 56 roscas
- Memoria: 256 GB de memoria RAM
- Almacenamento: Samsung SM863 1.9 TB Enterprise SSD
- Sistema de ficheiros: ext4/xfs
- OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: versión 11
Probas de referencia
En lugar de utilizar algúns conxuntos de datos xerados pola máquina para esta proba, utilizamos os datos "Produtividade por tempo do tempo informado do operador" de 1987 a 2018. Podes acceder aos datos .
O tamaño da base de datos é de 85 GB, proporcionando unha táboa de 109 columnas.
Consultas de referencia
Aquí están as consultas que usei para comparar ClickHouse, clickhousedb_fdw e PostgreSQL.
Q#
A consulta contén agregados e agrupa por
Q1
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Ano >= 2000 E Ano <= 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
SELECCIONAR Orixe, contar(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;
Q4
SELECCIONAR transportista, contar() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() DESC;
Q5
SELECCIONAR a.Portador, c, c2, c1000/c2 como 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 en a.Carrier=b.Carrier ORDER POR c3 DESC;
Q6
SELECCIONAR a.Portador, c, c2, c1000/c2 como c3 FROM ( SELECT Carrier, count() AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Transportista) a INNER JOIN ( SELECT Carrier, count(*) AS c2 FROM ontime WHERE Ano >= 2000 AND Year <= 2008 GROUP BY Transportista ) b en a.Carrier=b.Carrier ORDEN POR c3 DESC;
Q7
SELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Ano >= 2000 E Ano <= 2008 GROUP BY Transportista;
Q8
SELECT Year, avg(DepDelay) FROM ontime GRUPO POR Ano;
Q9
seleccione Ano, conta (*) como c1 do grupo de tempo por Ano;
Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;
Q11
seleccione avg(c1) from (seleccione Year,Month,count(*) as c1 from ontime group by Year,Month) a;
Q12
SELECCIONE OriginCityName, DestCityName, count(*) AS c FROM ontime GRUPO POR OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;
Q13
SELECT OriginCityName, count(*) AS c FROM ontime GRUPO POR OriginCityName ORDER BY c DESC LIMIT 10;
A consulta contén unións
Q14
SELECCIONE a.Ano, c1/c2 DE (seleccione Ano, conta()1000 como c1 desde o momento WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (seleccione o ano, conta(*) como c2 desde ontime GROUP BY Year ) b en a.Year=b.Year ORDENA POR a.Year;
Q15
SELECCIONE a."Ano", c1/c2 FROM (seleccione "Ano", count()1000 como c1 FROM fonttime WHERE “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (seleccione “Year”, conta (*)) como c2 FROM fonttime GROUP BY “Year” ) b en a.”Year”=b. "Ano";
Táboa-1: Consultas utilizadas no benchmark
Execucións de consulta
Aquí están os resultados de cada unha das consultas cando se executan en diferentes configuracións de base de datos: PostgreSQL con e sen índices, ClickHouse nativo e clickhousedb_fdw. O tempo móstrase en milisegundos.
Q#
PostgreSQL
PostgreSQL (indexado)
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
Táboa-1: Tempo necesario para executar as consultas utilizadas no benchmark
Ver resultados
O gráfico mostra o tempo de execución da consulta en milisegundos, o eixe X mostra o número de consulta das táboas anteriores e o eixe Y mostra o tempo de execución en milisegundos. Amósanse os resultados de ClickHouse e os datos recuperados de postgres mediante clickhousedb_fdw. Na táboa podes ver que hai unha gran diferenza entre PostgreSQL e ClickHouse, pero unha diferenza mínima entre ClickHouse e clickhousedb_fdw.
Este gráfico mostra a diferenza entre ClickhouseDB e clickhousedb_fdw. Na maioría das consultas, a sobrecarga de FDW non é tan alta e apenas é significativa, excepto para Q12. Esta consulta inclúe combinacións e unha cláusula ORDER BY. Debido á cláusula ORDER BY GROUP/BY, ORDER BY non aparece en ClickHouse.
Na táboa 2 vemos o salto de tempo nas consultas Q12 e Q13. De novo, isto é causado pola cláusula ORDER BY. Para confirmalo, executei as consultas Q-14 e Q-15 con e sen a cláusula ORDER BY. Sen a cláusula ORDER BY o tempo de realización é de 259 ms e coa cláusula ORDER BY é 1364212. Para depurar esta consulta estou explicando ambas as consultas e aquí están os resultados da explicación.
P15: Sen 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 sen 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 coa 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: Plan de consulta coa 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)Saída
Os resultados destes experimentos mostran que ClickHouse ofrece un rendemento realmente bo e clickhousedb_fdw ofrece os beneficios de rendemento de ClickHouse de PostgreSQL. Aínda que hai algo de sobrecarga ao usar clickhousedb_fdw, é insignificante e comparable ao rendemento acadado ao executarse de forma nativa na base de datos ClickHouse. Isto tamén confirma que fdw en PostgreSQL proporciona excelentes resultados.
Chat de Telegram a través de Clickhouse
Chat de Telegram usando PostgreSQL
Fonte: www.habr.com