Esta sección se centra en optimizar el rendimiento al leer e insertar datos desde S3 mediante las funciones de tabla de S3.
La lección descrita en esta guía también puede aplicarse a otras implementaciones de almacenamiento de objetos con sus propias funciones de tabla específicas, como GCS y Azure Blob storage. Mecanismo de inserción (nodo único)
Tamaño del bloque de inserción
INSERT INTO SELECT, ClickHouse recibe una porción de datos y ① crea, a partir de los datos recibidos, (al menos) un bloque de inserción en memoria (por clave de partición). Los datos del bloque se ordenan y se aplican optimizaciones específicas del motor de tabla. Después, los datos se comprimen y ② se escriben en el almacenamiento de la base de datos en forma de una nueva parte de datos.
El tamaño del bloque de inserción afecta tanto al uso de E/S de disco como al uso de memoria de un servidor ClickHouse. Los bloques de inserción más grandes consumen más memoria, pero generan partes iniciales más grandes y en menor número. Cuantas menos partes tenga que crear ClickHouse para cargar una gran cantidad de datos, menor será la E/S de disco y menos fusiones automáticas en segundo plano serán necesarias.
Al usar una consulta INSERT INTO SELECT en combinación con un motor de tabla de integración o una función de tabla, los datos son extraídos por el servidor ClickHouse:
Hasta que los datos se cargan por completo, el servidor ejecuta un bucle:
① Extraer y analizar la siguiente porción de datos y formar un bloque de datos en memoria (uno por clave de particionamiento) a partir de ella.
② Escribir el bloque en una nueva parte en el almacenamiento.
Ir a ①
min_insert_block_size_bytes indica el tamaño del bloque sin comprimir en memoria (y no el tamaño de la parte comprimida en disco). Además, ten en cuenta que los bloques y partes creados rara vez contienen exactamente la cantidad configurada de filas o bytes, porque ClickHouse transmite y procesa los datos por block de filas. Por lo tanto, estos ajustes especifican umbrales mínimos.
Tenga en cuenta las fusiones
El rendimiento de las consultas de ClickHouse se verá afectado negativamente si el número de partes supera los límites recomendados. ① Decidir qué partes fusionar a continuación y cargar estas partes como bloques en memoria.
② Fusionar los bloques cargados en memoria en un bloque más grande.
③ Escribir el bloque fusionado en una nueva parte en disco.
Ir a ①
MergeTree).
Un servidor de ClickHouse puede procesar e insertar datos en paralelo. El nivel de paralelismo de inserción afecta al rendimiento de ingestión y al uso de memoria de un servidor de ClickHouse. Cargar y procesar datos en paralelo requiere más memoria principal, pero aumenta el rendimiento de ingestión, ya que los datos se procesan más rápido.
Las funciones de tabla como s3 permiten especificar conjuntos de nombres de archivos que se van a cargar mediante patrones glob. Cuando un patrón glob coincide con varios archivos existentes, ClickHouse puede paralelizar las lecturas entre estos archivos y dentro de ellos, e insertar los datos en paralelo en una tabla utilizando hilos de inserción que se ejecutan en paralelo (por servidor):
Hasta que se procesen todos los datos de todos los archivos, cada hilo de inserción ejecuta un bucle:
① Obtener la siguiente porción de datos de archivo sin procesar (el tamaño de la porción se basa en el tamaño de bloque configurado) y crear un bloque de datos en memoria a partir de ella.
② Escribir el bloque en una nueva parte en el almacenamiento.
Ir a ①.
max_insert_threads. El valor predeterminado es 1 para ClickHouse de código abierto y 4 para ClickHouse Cloud.
Con una gran cantidad de archivos, el procesamiento en paralelo mediante varios hilos de inserción funciona bien. Puede saturar por completo tanto los núcleos de CPU disponibles como el ancho de banda de la red (para descargas paralelas de archivos). En escenarios en los que solo se van a cargar en una tabla unos pocos archivos grandes, ClickHouse establece automáticamente un alto grado de paralelismo en el procesamiento de datos y optimiza el uso del ancho de banda de red creando hilos de lectura adicionales por cada hilo de inserción para leer (descargar) en paralelo más rangos distintos dentro de archivos grandes.
Para la función s3 y la tabla, la descarga en paralelo de un archivo individual viene determinada por los valores max_download_threads y max_download_buffer_size. Los archivos solo se descargarán en paralelo si su tamaño es mayor que 2 * max_download_buffer_size. De forma predeterminada, el valor de max_download_buffer_size se establece en 10 MiB. En algunos casos, puede aumentar con seguridad este tamaño de búfer a 50 MB (max_download_buffer_size=52428800) para garantizar que cada archivo se descargue con un único hilo. Esto puede reducir el tiempo que cada hilo dedica a realizar llamadas a S3 y, por tanto, también disminuir el tiempo de espera en S3. Además, en el caso de los archivos demasiado pequeños para la lectura en paralelo, ClickHouse realiza automáticamente una precarga de datos leyendo previamente esos archivos de forma asíncrona para aumentar el rendimiento.
Optimizar el rendimiento de las consultas que usan las table functions de S3 es necesario tanto al ejecutar consultas directamente sobre los datos, es decir, consultas ad hoc en las que solo se usa la capacidad de cómputo de ClickHouse y los datos permanecen en S3 en su formato original, como al insertar datos desde S3 en un motor de tabla MergeTree de ClickHouse. Salvo que se indique lo contrario, las siguientes recomendaciones se aplican a ambos escenarios.
Impacto del tamaño del hardware
s3 y el motor S3. Si se leen archivos sin procesar, algunos de estos formatos ofrecen ventajas claras:
- Los formatos con nombres de columna codificados, como Native, Parquet, CSVWithNames y TabSeparatedWithNames, hacen que las consultas sean menos verbosas, ya que el usuario no tiene que especificar el nombre de la columna en la función
s3. Los nombres de columna permiten inferir esta información.
- Los formatos difieren en rendimiento en cuanto al throughput de lectura y escritura. Native y Parquet son los formatos más óptimos para el rendimiento de lectura, ya que ya están orientados a columnas y son más compactos. Además, el formato Native se beneficia de su alineación con la forma en que ClickHouse almacena los datos en memoria, lo que reduce la sobrecarga de procesamiento a medida que los datos se transfieren a ClickHouse.
- El tamaño del bloque suele afectar a la latencia de lectura en archivos grandes. Esto resulta muy evidente si solo se toma una muestra de los datos; por ejemplo, al devolver las primeras N filas. En el caso de formatos como CSV y TSV, los archivos deben analizarse para devolver un conjunto de filas. Como resultado, formatos como Native y Parquet permiten un muestreo más rápido.
- Cada formato de compresión tiene sus ventajas y desventajas, y a menudo equilibra el nivel de compresión con la velocidad, favoreciendo el rendimiento de la compresión o de la descompresión. Si se comprimen archivos sin procesar, como CSV o TSV, lz4 ofrece el mejor rendimiento de descompresión, a costa del nivel de compresión. Gzip suele comprimir mejor, a cambio de velocidades de lectura ligeramente más lentas. Xz lleva esto más lejos, ya que por lo general ofrece la mejor compresión, pero con el peor rendimiento tanto en compresión como en descompresión. Si se exporta, Gz y lz4 ofrecen velocidades de compresión comparables. Sopesa esto frente a la velocidad de tu conexión. Cualquier mejora derivada de una compresión o descompresión más rápida puede verse anulada fácilmente por una conexión más lenta a tus buckets de S3.
- Formatos como Native o Parquet normalmente no justifican la sobrecarga de la compresión. Cualquier ahorro en tamaño de datos probablemente será mínimo, ya que estos formatos son inherentemente compactos. El tiempo dedicado a comprimir y descomprimir rara vez compensará los tiempos de transferencia de red, especialmente porque S3 está disponible globalmente con un mayor ancho de banda de red.
Conjunto de datos de ejemplo
-- Principales nombres de usuario
SELECT
OwnerDisplayName,
count() AS num_posts
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/by_month/*.parquet')
WHERE OwnerDisplayName NOT IN ('', 'anon')
GROUP BY OwnerDisplayName
ORDER BY num_posts DESC
LIMIT 5
┌─OwnerDisplayName─┬─num_posts─┐
│ user330315 │ 10344 │
│ user4039065 │ 5316 │
│ user149341 │ 4102 │
│ user529758 │ 3700 │
│ user3559349 │ 3068 │
└──────────────────┴───────────┘
5 rows in set. Elapsed: 3.013 sec. Processed 59.82 million rows, 24.03 GB (19.86 million rows/s., 7.98 GB/s.)
Peak memory usage: 603.64 MiB.
-- Cargar en la tabla posts
INSERT INTO posts SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/by_month/*.parquet')
0 rows in set. Elapsed: 191.692 sec. Processed 59.82 million rows, 24.03 GB (312.06 thousand rows/s., 125.37 MB/s.)
SELECT, en las que se devuelven grandes volúmenes de datos al cliente, utilice el formato Null para las consultas o dirija los resultados al motor Null. Esto debería evitar que el cliente se vea desbordado por los datos y que la red se sature.
Al ejecutar consultas de lectura, la consulta inicial a menudo puede parecer más lenta que si se repite la misma consulta. Esto puede atribuirse tanto al propio almacenamiento en caché de S3 como a la ClickHouse Schema Inference Cache. Esta almacena el esquema inferido de los archivos, lo que permite omitir el paso de inferencia en accesos posteriores y, por tanto, reducir el tiempo de consulta. Uso de hilos para lecturas
- Normalmente, el valor predeterminado de
max_threads es suficiente, es decir, el número de núcleos. Si la cantidad de memoria utilizada por una consulta es alta y necesita reducirse, o si el LIMIT de los resultados es bajo, este valor puede configurarse en uno menor. Los usuarios con memoria abundante quizá quieran probar a aumentar este valor para obtener un mayor rendimiento de lectura desde S3. Por lo general, esto solo resulta beneficioso en máquinas con pocos núcleos, es decir, < 10. El beneficio de seguir paralelizando suele disminuir cuando otros recursos se convierten en cuellos de botella, por ejemplo, la red y la contención de CPU.
- Las versiones de ClickHouse anteriores a la 22.3.1 solo paralelizaban las lecturas entre varios archivos al usar la función
s3 o el motor de tabla S3. Esto obligaba al usuario a asegurarse de que los archivos estuvieran divididos en fragmentos en S3 y de leerlos mediante un patrón glob para lograr un rendimiento de lectura óptimo. Las versiones posteriores ya paralelizan las descargas dentro de un mismo archivo.
- En escenarios con pocos hilos, puede resultar útil establecer
remote_filesystem_read_method en “read” para forzar la lectura síncrona de archivos desde S3.
- Para la función
s3 y la tabla, la descarga en paralelo de un archivo individual viene determinada por los valores de max_download_threads y max_download_buffer_size. Aunque max_download_threads controla el número de hilos utilizados, los archivos solo se descargarán en paralelo si su tamaño es mayor que 2 * max_download_buffer_size. De forma predeterminada, max_download_buffer_size está configurado en 10MiB. En algunos casos, puede aumentar con seguridad este tamaño de búfer a 50 MB (max_download_buffer_size=52428800) para asegurarse de que los archivos más pequeños se descarguen con un solo hilo. Esto puede reducir el tiempo que cada hilo dedica a realizar llamadas a S3 y, por tanto, también disminuir el tiempo de espera en S3. Consulte esta entrada del blog para ver un ejemplo.
Antes de realizar cualquier cambio para mejorar el rendimiento, asegúrese de medirlo adecuadamente. Dado que las llamadas a la API de S3 son sensibles a la latencia y pueden afectar a los tiempos del cliente, use el log de consultas para obtener métricas de rendimiento, es decir, system.query_log.
Considere nuestra consulta anterior: duplicar max_threads a 16 (el valor predeterminado de max_thread es el número de núcleos de un nodo) mejora 2x el rendimiento de nuestra consulta de lectura, a costa de un mayor uso de memoria. Aumentar aún más max_threads ofrece beneficios cada vez menores, como se muestra.
SELECT
OwnerDisplayName,
count() AS num_posts
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/by_month/*.parquet')
WHERE OwnerDisplayName NOT IN ('', 'anon')
GROUP BY OwnerDisplayName
ORDER BY num_posts DESC
LIMIT 5
SETTINGS max_threads = 16
┌─OwnerDisplayName─┬─num_posts─┐
│ user330315 │ 10344 │
│ user4039065 │ 5316 │
│ user149341 │ 4102 │
│ user529758 │ 3700 │
│ user3559349 │ 3068 │
└──────────────────┴───────────┘
5 rows in set. Elapsed: 1.505 sec. Processed 59.82 million rows, 24.03 GB (39.76 million rows/s., 15.97 GB/s.)
Peak memory usage: 178.58 MiB.
SETTINGS max_threads = 32
5 rows in set. Elapsed: 0.779 sec. Processed 59.82 million rows, 24.03 GB (76.81 million rows/s., 30.86 GB/s.)
Peak memory usage: 369.20 MiB.
SETTINGS max_threads = 64
5 rows in set. Elapsed: 0.674 sec. Processed 59.82 million rows, 24.03 GB (88.81 million rows/s., 35.68 GB/s.)
Peak memory usage: 639.99 MiB.
Ajuste de los hilos y del tamaño de bloque para las inserciones
• max_insert_threads: elegir ~ la mitad de los núcleos de CPU disponibles para los hilos de inserción (para dejar suficientes núcleos dedicados a las fusiones en segundo plano)
• peak_memory_usage_in_bytes: elegir el uso máximo de memoria deseado; ya sea toda la RAM disponible (si se trata de una ingesta aislada) o la mitad o menos (para dejar margen a otras tareas concurrentes)
Luego:
min_insert_block_size_bytes = peak_memory_usage_in_bytes / (~3 * max_insert_threads)
min_insert_block_size_rows en 0 (para desactivar el umbral basado en filas), fijar max_insert_threads en el valor elegido y min_insert_block_size_bytes en el resultado calculado con la fórmula anterior.
Uso de esta fórmula con nuestro ejemplo anterior de Stack Overflow.
max_insert_threads=4 (8 núcleos por nodo)peak_memory_usage_in_bytes - 32 GiB (100 % de los recursos del nodo) o 34359738368 bytes.min_insert_block_size_bytes = 34359738368/(3*4) = 2863311530
INSERT INTO posts SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/by_month/*.parquet') SETTINGS min_insert_block_size_rows=0, max_insert_threads=4, min_insert_block_size_bytes=2863311530
0 rows in set. Elapsed: 128.566 sec. Processed 59.82 million rows, 24.03 GB (465.28 thousand rows/s., 186.92 MB/s.)
33%. Dejamos al lector comprobar si puede mejorar aún más el rendimiento de un solo nodo.
Escalado con recursos y nodos
INSERT INTO posts SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/by_month/*.parquet') SETTINGS min_insert_block_size_rows=0, max_insert_threads=8, min_insert_block_size_bytes=2863311530
0 rows in set. Elapsed: 67.294 sec. Processed 59.82 million rows, 24.03 GB (888.93 thousand rows/s., 357.12 MB/s.)
SELECT
OwnerDisplayName,
count() AS num_posts
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/by_month/*.parquet')
WHERE OwnerDisplayName NOT IN ('', 'anon')
GROUP BY OwnerDisplayName
ORDER BY num_posts DESC
LIMIT 5
SETTINGS max_threads = 92
5 rows in set. Elapsed: 0.421 sec. Processed 59.82 million rows, 24.03 GB (142.08 million rows/s., 57.08 GB/s.)
Los nodos individuales también pueden verse limitados por la red y por las solicitudes GET a S3, lo que impide que el rendimiento escale linealmente al ampliar verticalmente los recursos.
s3Cluster, como se describe en Utilizing Clusters. Esto permite distribuir las lecturas entre los nodos.
El servidor que recibe inicialmente la consulta de inserción primero resuelve el patrón glob y luego distribuye dinámicamente el procesamiento de cada archivo que coincida entre sí mismo y los demás servidores.
Repetimos la consulta de lectura anterior distribuyendo la carga de trabajo entre 3 nodos, ajustando la consulta para usar s3Cluster. Esto se realiza automáticamente en ClickHouse Cloud, haciendo referencia al clúster default.
Como se indica en Utilizing Clusters, este trabajo se distribuye a nivel de archivo. Para beneficiarse de esta función, necesitará una cantidad suficiente de archivos; es decir, un número de archivos superior al de nodos.
SELECT
OwnerDisplayName,
count() AS num_posts
FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/by_month/*.parquet')
WHERE OwnerDisplayName NOT IN ('', 'anon')
GROUP BY OwnerDisplayName
ORDER BY num_posts DESC
LIMIT 5
SETTINGS max_threads = 16
┌─OwnerDisplayName─┬─num_posts─┐
│ user330315 │ 10344 │
│ user4039065 │ 5316 │
│ user149341 │ 4102 │
│ user529758 │ 3700 │
│ user3559349 │ 3068 │
└──────────────────┴───────────┘
5 rows in set. Elapsed: 0.622 sec. Processed 59.82 million rows, 24.03 GB (96.13 million rows/s., 38.62 GB/s.)
Peak memory usage: 176.74 MiB.
INSERT INTO posts SELECT *
FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/by_month/*.parquet') SETTINGS min_insert_block_size_rows=0, max_insert_threads=4, min_insert_block_size_bytes=2863311530
0 rows in set. Elapsed: 171.202 sec. Processed 59.82 million rows, 24.03 GB (349.41 thousand rows/s., 140.37 MB/s.)
s3Cluster, las inserciones se realizarán en el nodo iniciador. Esto significa que, aunque las lecturas se ejecuten en cada nodo, las filas resultantes se enviarán al iniciador para su distribución. En escenarios de alto rendimiento, esto puede convertirse en un cuello de botella. Para solucionarlo, establezca el parámetro parallel_distributed_insert_select para la función s3cluster.
Si se establece en parallel_distributed_insert_select=2, se garantiza que SELECT e INSERT se ejecutarán en cada segmento desde/hacia la tabla subyacente del motor Distributed en cada nodo.
INSERT INTO posts
SELECT *
FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/by_month/*.parquet')
SETTINGS parallel_distributed_insert_select = 2, min_insert_block_size_rows=0, max_insert_threads=4, min_insert_block_size_bytes=2863311530
0 rows in set. Elapsed: 54.571 sec. Processed 59.82 million rows, 24.03 GB (1.10 million rows/s., 440.38 MB/s.)
Peak memory usage: 11.75 GiB.
Desactivar la deduplicación
INSERT INTO SELECT desde almacenamiento de objetos. Si se desactiva esta funcionalidad en el momento de la inserción, se puede mejorar el rendimiento, como se muestra a continuación:
INSERT INTO posts
SETTINGS parallel_distributed_insert_select = 2, min_insert_block_size_rows = 0, max_insert_threads = 4, min_insert_block_size_bytes = 2863311530, insert_deduplicate = 0
SELECT *
FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/by_month/*.parquet')
SETTINGS parallel_distributed_insert_select = 2, min_insert_block_size_rows = 0, max_insert_threads = 4, min_insert_block_size_bytes = 2863311530, insert_deduplicate = 0
0 rows in set. Elapsed: 52.992 sec. Processed 59.82 million rows, 24.03 GB (1.13 million rows/s., 453.50 MB/s.)
Peak memory usage: 26.57 GiB.
Optimización durante la inserción
optimize_on_insert controla si las partes de datos se fusionan durante el proceso de inserción. Cuando está habilitada (optimize_on_insert = 1 de forma predeterminada), las partes pequeñas se fusionan en otras más grandes a medida que se insertan, lo que mejora el rendimiento de las consultas al reducir la cantidad de partes que deben leerse. Sin embargo, esta fusión añade sobrecarga al proceso de inserción, por lo que puede ralentizar las inserciones de alto rendimiento.
Deshabilitar esta configuración (optimize_on_insert = 0) omite la fusión durante las inserciones, lo que permite escribir los datos más rápidamente, especialmente cuando se realizan inserciones pequeñas frecuentes. El proceso de fusión se aplaza para ejecutarse en segundo plano, lo que mejora el rendimiento de inserción, pero aumenta temporalmente la cantidad de partes pequeñas, lo que puede ralentizar las consultas hasta que se complete la fusión en segundo plano. Esta configuración es ideal cuando el rendimiento de inserción es prioritario y el proceso de fusión en segundo plano puede encargarse de la optimización de forma eficiente más adelante. Como se muestra a continuación, deshabilitar esta configuración puede mejorar el rendimiento de inserción:
SELECT *
FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/by_month/*.parquet')
SETTINGS parallel_distributed_insert_select = 2, min_insert_block_size_rows = 0, max_insert_threads = 4, min_insert_block_size_bytes = 2863311530, insert_deduplicate = 0, optimize_on_insert = 0
0 rows in set. Elapsed: 49.688 sec. Processed 59.82 million rows, 24.03 GB (1.20 million rows/s., 483.66 MB/s.)
- En escenarios de poca memoria, considere reducir
max_insert_delayed_streams_for_parallel_write si inserta en S3.
