Neste estudo, eu queria ver quais melhorias de desempenho poderiam ser alcançadas usando uma fonte de dados ClickHouse em vez de PostgreSQL. Conheço os benefícios de produtividade que obtenho ao usar o ClickHouse. Esses benefícios continuarão se eu acessar o ClickHouse do PostgreSQL usando um Foreign Data Wrapper (FDW)?
Os ambientes de banco de dados estudados são PostgreSQL v11, clickhousedb_fdw e banco de dados ClickHouse. Por fim, a partir do PostgreSQL v11, executaremos várias consultas SQL roteadas através de nosso clickhousedb_fdw para o banco de dados ClickHouse. Veremos então como o desempenho do FDW se compara às mesmas consultas executadas no PostgreSQL nativo e no ClickHouse nativo.
Banco de dados Clickhouse
ClickHouse é um sistema de gerenciamento de banco de dados colunar de código aberto que pode atingir desempenho 100-1000 vezes mais rápido do que as abordagens de banco de dados tradicionais, capaz de processar mais de um bilhão de linhas em menos de um segundo.
Clickhousedb_fdw
clickhousedb_fdw - O wrapper de dados externo para o banco de dados ClickHouse, ou FDW, é um projeto de código aberto da Percona. .
.
Como você verá, isso fornece um FDW para ClickHouse que permite SELECT from, e INSERT INTO, o banco de dados ClickHouse do servidor PostgreSQL v11.
O FDW oferece suporte a recursos avançados, como agregação e junção. Isso melhora significativamente o desempenho ao usar os recursos do servidor remoto para essas operações que exigem muitos recursos.
Ambiente de referência
- Servidor Supermicro:
- CPU Intel® Xeon® E5-2683 v3 a 2.00 GHz
- 2 soquetes / 28 núcleos / 56 threads
- Memória: 256 GB de RAM
- Armazenamento: SSD empresarial Samsung SM863 de 1.9 TB
- Sistema de arquivos: ext4/xfs
- SO: Linux smblade01 4.15.0-42-genérico #45~16.04.1-Ubuntu
- PostgreSQL: versão 11
Testes de referência
Em vez de usar algum conjunto de dados gerados por máquina para este teste, usamos os dados de “Produtividade por Tempo Reportado do Operador” de 1987 a 2018. Você pode acessar os dados .
O tamanho do banco de dados é de 85 GB, fornecendo uma tabela de 109 colunas.
Consultas de referência
Aqui estão as consultas que usei para comparar ClickHouse, clickhousedb_fdw e PostgreSQL.
Q#
Consulta contém agregados e grupo por
Q1
SELECIONE DayOfWeek, conte(*) AS c FROM ontime WHERE Ano >= 2000 AND Ano <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2
SELECT DayOfWeek, conte(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q3
SELECT Origem, contagem(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;
Q4
SELECIONE Operadora, conte() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY contagem()DESC;
Q5
SELECIONE a.Transportadora, 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 em a.Carrier=b.Carrier ORDER POR c3 DESC;
Q6
SELECIONE a.Transportadora, 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 Carrier) a INNER JOIN ( SELECT Carrier, count(*) AS c2 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Transportadora ) b em a.Carrier=b.Carrier ORDER BY c3 DESC;
Q7
SELECT Operadora, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Ano >= 2000 AND Ano <= 2008 GROUP BY Operadora;
Q8
SELECIONE Ano, avg(DepDelay) FROM ontime GROUP BY Year;
Q9
selecione Ano, conte (*) como c1 do grupo ontime por Ano;
Q10
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;
Q11
selecione avg(c1) de (selecione Ano, Mês, contagem(*) como c1 do grupo de horário por Ano, Mês) a;
Q12
SELECT OriginCityName, DestCityName, contagem(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;
Q13
SELECT OriginCityName, contagem(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;
A consulta contém junções
Q14
SELECIONE a.Ano, c1/c2 FROM (selecione Ano, conte()1000 como c1 do horário de início WHERE DepDelay>10 GROUP BY Year) a INNER JOIN (selecione Ano, conte (*) como c2 do horário de início GROUP BY Year ) b em a.Year=b.Year ORDER BY a.Year;
Q15
SELECIONE a.”Ano”, c1/c2 FROM ( selecione “Ano”, contagem()1000 como c1 FROM fontime WHERE “DepDelay”>10 GROUP BY “Year”) a INNER JOIN (selecione “Year”, conte(*) como c2 FROM fontime GROUP BY “Year”) b em a.”Year”=b. "Ano";
Tabela 1: Consultas usadas no benchmark
Execuções de consulta
Aqui estão os resultados de cada uma das consultas quando executadas em diferentes configurações de banco de dados: PostgreSQL com e sem índices, ClickHouse nativo e clickhousedb_fdw. O tempo é mostrado em milissegundos.
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
Tabela 1: Tempo necessário para executar as consultas usadas no benchmark
Ver resultados
O gráfico mostra o tempo de execução da consulta em milissegundos, o eixo X mostra o número da consulta das tabelas acima e o eixo Y mostra o tempo de execução em milissegundos. Os resultados do ClickHouse e os dados recuperados do postgres usando clickhousedb_fdw são mostrados. Na tabela você pode ver que há uma grande diferença entre PostgreSQL e ClickHouse, mas uma diferença mínima entre ClickHouse e clickhousedb_fdw.
Este gráfico mostra a diferença entre ClickhouseDB e clickhousedb_fdw. Na maioria das consultas, a sobrecarga do FDW não é tão alta e dificilmente é significativa, exceto no Q12. Esta consulta inclui junções e uma cláusula ORDER BY. Devido à cláusula ORDER BY GROUP/BY, ORDER BY não cai para ClickHouse.
Na Tabela 2 vemos o salto no tempo nas consultas Q12 e Q13. Novamente, isso é causado pela cláusula ORDER BY. Para confirmar isso, executei as consultas Q-14 e Q-15 com e sem a cláusula ORDER BY. Sem a cláusula ORDER BY o tempo de conclusão é 259ms e com a cláusula ORDER BY é 1364212. Para depurar esta consulta estou explicando ambas as consultas e aqui estão os resultados da explicação.
Q15: Sem 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";Q15: Consulta sem 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)Q14: Consulta com 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";Q14: Plano de consulta com 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)Jogar aviator online grátis: hack aviator funciona
Os resultados desses experimentos mostram que ClickHouse oferece um desempenho realmente bom e clickhousedb_fdw oferece os benefícios de desempenho do ClickHouse do PostgreSQL. Embora haja alguma sobrecarga ao usar clickhousedb_fdw, ela é insignificante e comparável ao desempenho alcançado pela execução nativa no banco de dados ClickHouse. Isso também confirma que o fdw no PostgreSQL oferece excelentes resultados.
Bate-papo por telegrama via Clickhouse
Bate-papo por telegrama usando PostgreSQL
Fonte: habr.com