En este estudio, quería ver qué mejoras de rendimiento se podrían lograr utilizando una fuente de datos de ClickHouse en lugar de PostgreSQL. Conozco los beneficios de productividad que obtengo al usar ClickHouse. ¿Continuarán estos beneficios si accedo a ClickHouse desde PostgreSQL utilizando un contenedor de datos externo (FDW)?
Los entornos de bases de datos estudiados son PostgreSQL v11, clickhousedb_fdw y base de datos ClickHouse. En última instancia, desde PostgreSQL v11 ejecutaremos varias consultas SQL enrutadas a través de nuestro clickhousedb_fdw a la base de datos de ClickHouse. Luego veremos cómo se compara el rendimiento de FDW con las mismas consultas que se ejecutan en PostgreSQL nativo y ClickHouse nativo.
Base de datos de Clickhouse
ClickHouse es un sistema de gestión de bases de datos en columnas de código abierto que puede alcanzar un rendimiento entre 100 y 1000 veces más rápido que los enfoques de bases de datos tradicionales, capaz de procesar más de mil millones de filas en menos de un segundo.
Clickhousedb_fdw
clickhousedb_fdw: el contenedor de datos externo para la base de datos ClickHouse, o FDW, es un proyecto de código abierto de Percona. .
.
Como verá, esto proporciona un FDW para ClickHouse que permite SELECCIONAR e INSERTAR EN la base de datos de ClickHouse desde el servidor PostgreSQL v11.
FDW admite funciones avanzadas como agregación y unión. Esto mejora significativamente el rendimiento al utilizar los recursos del servidor remoto para estas operaciones que consumen muchos recursos.
Entorno de referencia
- Servidor Supermicro:
- CPU Intel® Xeon® E5-2683 v3 a 2.00 GHz
- 2 enchufes / 28 núcleos / 56 hilos
- Memoria: 256GB de RAM
- Almacenamiento: SSD empresarial Samsung SM863 de 1.9 TB
- Sistema de archivos: ext4/xfs
- Sistema operativo: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu
- PostgreSQL: versión 11
Pruebas comparativas
En lugar de utilizar algún conjunto de datos generados por máquina para esta prueba, utilizamos los datos de "Productividad por tiempo reportado del operador" de 1987 a 2018. Puedes acceder a los datos .
El tamaño de la base de datos es de 85 GB y proporciona una tabla de 109 columnas.
Consultas de referencia
Estas son las consultas que utilicé para comparar ClickHouse, clickhousedb_fdw y PostgreSQL.
Q#
La consulta contiene agregados y grupo por
Q1
SELECCIONE Día de la semana, cuente (*) COMO c DESDE ontime DONDE Año >= 2000 Y Año <= 2008 GRUPO POR Día de la semana ORDEN POR c DESC;
Q2
SELECCIONE DíaDeLa Semana, cuente(*) COMO c DESDE ontime DONDE DepDelay>10 Y Año >= 2000 Y Año <= 2008 GRUPO POR DíaDeLa Semana ORDEN POR c DESC;
Q3
SELECCIONE Origen, cuente(*) COMO c DESDE ontime DONDE DepDelay>10 AND Año >= 2000 AND Año <= 2008 GRUPO POR Origen ORDEN POR c DESC LIMIT 10;
Q4
SELECCIONAR Transportista, contar() DESDE ontime DONDE DepDelay>10 Y Año = 2007 GRUPO POR Transportista ORDEN POR recuento() DESC;
Q5
SELECCIONAR a.Transportista, c, c2, c1000/c2 como c3 DESDE (SELECCIONE Transportista, cuente() COMO c DESDE ontime DONDE DepDelay>10 AND Year=2007 GROUP BY Carrier ) a INNER JOIN ( SELECCIONE Carrier,count(*) COMO c2 FROM ontime DONDE Año=2007 GROUP BY Carrier)b en a.Carrier=b.Carrier ORDEN POR c3 DESC;
Q6
SELECCIONAR a.Transportista, c, c2, c1000/c2 como c3 DESDE (SELECCIONE Transportista, cuente() COMO c DESDE ontime DONDE DepDelay>10 Y Año >= 2000 Y Año <= 2008 GRUPO POR Operador) a INNER JOIN ( SELECCIONE Operador, cuenta (*) COMO c2 DESDE ontime DONDE Año >= 2000 Y Año <= 2008 GRUPO POR Transportista ) b en a.Transportista=b.Transportista ORDEN POR c3 DESC;
Q7
SELECCIONE Operador, promedio (DepDelay) * 1000 COMO c3 DESDE ontime DONDE Año >= 2000 Y Año <= 2008 GRUPO POR Operador;
Q8
SELECCIONE Año, promedio (DepDelay) FROM ontime GROUP BY Año;
Q9
seleccione Año, cuente (*) como c1 del grupo ontime por Año;
P10
SELECCIONE promedio(cnt) DESDE (SELECCIONE Año,Mes,cuenta(*) COMO cnt DESDE ontime DONDE DepDel15=1 GRUPO POR Año,Mes) a;
P11
seleccione promedio (c1) de (seleccione Año, Mes, cuente (*) como c1 del grupo ontime por Año, Mes) a;
P12
SELECCIONE OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDEN POR c DESC LIMIT 10;
P13
SELECCIONE OriginCityName, cuente(*) COMO c FROM ontime GROUP BY OriginCityName ORDEN POR c DESC LIMIT 10;
La consulta contiene uniones
P14
SELECCIONE a.Año, c1/c2 DESDE (seleccione Año, cuente()1000 como c1 desde ontime DONDE DepDelay>10 GROUP BY Year) a INNER JOIN (seleccione Año, cuente (*) como c2 desde ontime GROUP BY Year) b en a.Year=b.Year ORDER BY a.Year;
P15
SELECCIONE a.”Año”, c1/c2 DESDE (seleccione “Año”, cuente()1000 como c1 DESDE fontime DONDE “DepDelay”>10 GRUPO POR “Año”) a INNER JOIN (seleccione “Año”, cuente(*) como c2 DESDE fontime GRUPO POR “Año” ) b en a.”Año”=b. "Año";
Tabla-1: Consultas utilizadas en el punto de referencia
Ejecuciones de consultas
Aquí están los resultados de cada una de las consultas cuando se ejecutan en diferentes configuraciones de base de datos: PostgreSQL con y sin índices, ClickHouse nativo y clickhousedb_fdw. El tiempo se muestra en milisegundos.
Q#
PostgreSQL
PostgreSQL (indexado)
casa de clics
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
P10
34990
20102
56
148
P11
30489
51658
37
155
P12
39357
33742
186
1333
P13
29912
30709
101
384
P14
54126
39913
124
1364212
P15
97258
30211
245
259
Tabla-1: Tiempo necesario para ejecutar las consultas utilizadas en el punto de referencia
Ver resultados
El gráfico muestra el tiempo de ejecución de la consulta en milisegundos, el eje X muestra el número de consulta de las tablas anteriores y el eje Y muestra el tiempo de ejecución en milisegundos. Se muestran los resultados de ClickHouse y los datos recuperados de postgres usando clickhousedb_fdw. En la tabla puede ver que existe una gran diferencia entre PostgreSQL y ClickHouse, pero una diferencia mínima entre ClickHouse y clickhousedb_fdw.
Este gráfico muestra la diferencia entre ClickhouseDB y clickhousedb_fdw. En la mayoría de las consultas, los gastos generales de FDW no son tan elevados y apenas son significativos, excepto en el caso del P12. Esta consulta incluye combinaciones y una cláusula ORDER BY. Debido a la cláusula ORDER BY GROUP/BY, ORDER BY no desciende a ClickHouse.
En la Tabla 2 vemos el salto de tiempo en las consultas Q12 y Q13. Nuevamente, esto se debe a la cláusula ORDER BY. Para confirmar esto, ejecuté las consultas Q-14 y Q-15 con y sin la cláusula ORDER BY. Sin la cláusula ORDER BY el tiempo de finalización es 259ms y con la cláusula ORDER BY es 1364212. Para depurar esta consulta estoy explicando ambas consultas y aquí están los resultados de la explicación.
P15: Sin 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 sin 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 con 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 con 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)conclusión
Los resultados de estos experimentos muestran que ClickHouse ofrece un rendimiento realmente bueno y clickhousedb_fdw ofrece los beneficios de rendimiento de ClickHouse de PostgreSQL. Si bien hay cierta sobrecarga al usar clickhousedb_fdw, es insignificante y comparable al rendimiento logrado al ejecutarlo de forma nativa en la base de datos de ClickHouse. Esto también confirma que fdw en PostgreSQL proporciona excelentes resultados.
Chat de Telegram a través de Clickhouse
Chat de Telegram usando PostgreSQL
Fuente: habr.com