Aislar cuellos de botella en consultas

La optimización de consultas resulta más sencilla si se modifica una parte de la consulta a la vez y se comparan los resultados con una referencia estable. Esta guía explica cómo simplificar progresivamente una consulta y usar las diferencias entre ejecuciones para identificar qué operaciones contribuyen más a su duración. A continuación, puede validar el posible cuello de botella antes de elegir una optimización.

Parta de un patrón recurrente de consultas lentas que desee investigar. Si todavía no ha identificado ninguno, consulte Diagnosticar consultas lentas, donde se explica el proceso.

Para ejecutar los ejemplos de esta guía tal como se muestran, cree y cargue la tabla nyc_taxi.trips_small_inferred si aún no lo ha hecho:

CREATE DATABASE IF NOT EXISTS nyc_taxi;
USE nyc_taxi;

CREATE TABLE nyc_taxi.trips_small_inferred
ORDER BY () EMPTY
AS SELECT *
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet',
    NOSIGN,
    Parquet
);

INSERT INTO nyc_taxi.trips_small_inferred
SELECT *
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet',
    NOSIGN,
    Parquet
);

La tabla de ejemplo usa ORDER BY (), por lo que su filtro de fecha no puede usar una clave de ordenación para descartar datos durante la lectura. Use el ejemplo para practicar el método de comparación, no como objetivo de rendimiento.

Simplificar una consulta de forma progresiva permite comparar su duración antes y después de eliminar una etapa de trabajo. Las diferencias ayudan a determinar si conviene investigar el escaneo y filtrado, la agrupación, los cálculos de agregación o las operaciones posteriores, como la ordenación y el formato de salida:

1. Ejecute la consulta original para establecer las mediciones de referencia.
2. Mantenga GROUP BY, sustituya los cálculos de agregación de la consulta por count y elimine las operaciones posteriores, como la ordenación y el formato de salida.
3. Elimine la agrupación y ejecute un count sin agrupar para aproximar el trabajo correspondiente al escaneo, filtrado y cualquier join.

Estas etapas se aplican directamente a las consultas de agregación convencionales con agrupación. Para consultas más complejas, aplique el mismo principio a un bloque SELECT a la vez: conserve fuentes de datos y filtros equivalentes, elimine una operación a la vez y verifique el plan de ejecución después de cada cambio.

Aplica las siguientes prácticas para que las mediciones sean comparables:

• Mantén sin cambios las cláusulas FROM, JOIN, PREWHERE y WHERE para que cada comparación use los mismos datos y el mismo intervalo de tiempo.
• Ejecuta cada versión de la consulta varias veces con una carga del sistema similar.
• Mantén condiciones de caché coherentes. Ejecuta cada versión de la consulta antes de registrar las mediciones o desactiva las cachés indicadas a continuación. No compares ejecuciones en caché con ejecuciones sin caché.
• Registra una duración representativa, como la mediana de las ejecuciones repetidas tras las ejecuciones de calentamiento, en lugar de basarte en el resultado más rápido o más lento.
• Cambia una variable a la vez para poder asociar una diferencia de rendimiento a un cambio específico.

Para una comparación de diagnóstico sin caché, desactiva la caché del sistema de archivos de ClickHouse para datos remotos, la caché de consultas y la caché de condiciones de consulta. Desactiva también las proyecciones implícitas para que el count de la ejecución C no use un plan de ejecución optimizado que omita el escaneo que pretendes comparar.

SET enable_filesystem_cache = 0;
SET use_query_cache = 0;
SET use_query_condition_cache = 0;
SET optimize_use_implicit_projections = 0;

El flujo de trabajo combina ejecuciones controladas de consultas con mediciones del registro de consultas:

Recopile las mediciones de cada ejecución de la siguiente manera:

1. Asigne un ID de consulta único a cada ejecución o registre el ID generado por su interfaz de consultas. Por ejemplo, identifique las ejecuciones repetidas como bottleneck-a-1, bottleneck-a-2 y bottleneck-a-3. Con clickhouse-client, pase --query_id your-query-id al ejecutar una consulta.

2. Ejecute cada consulta comparativa varias veces en las mismas condiciones. Mantenga las ejecuciones de calentamiento separadas de las ejecuciones medidas.

3. Vacíe el registro de consultas antes de buscar consultas completadas recientemente:

   SYSTEM FLUSH LOGS;

   Si no puede ejecutar SYSTEM FLUSH LOGS, espere a que el registro de consultas se vacíe automáticamente y vuelva a intentar la búsqueda. Si el registro nunca aparece, verifique que el registro de consultas esté habilitado, que pueda leer system.query_log y que esté consultando el nodo que ejecutó la consulta.

4. Busque el registro completado correspondiente a cada ID de consulta. system.query_log registra eventos QueryStart y QueryFinish para las consultas completadas. Filtre por QueryFinish, que contiene la duración final, las filas y los bytes leídos, y el pico de memoria:

   SELECT
       query_id,
       query_duration_ms,
       read_rows,
       read_bytes,
       memory_usage
   FROM system.query_log
   WHERE type = 'QueryFinish'
     AND query_id = 'your-query-id'
   ORDER BY event_time_microseconds DESC
   LIMIT 1;

5. Para cada versión de la consulta, use la duración mediana de las ejecuciones medidas. Registre read_rows, read_bytes y el pico de memoria de la ejecución más cercana a esa mediana para que las mediciones sigan vinculadas a una ejecución real.

Use una tabla como la siguiente para organizar las mediciones representativas. Consulte system.query_log para obtener más información sobre sus campos y configuración.

Run,Query version,Representative duration,read_rows,read_bytes,Peak memory
A,Original query,,,,
B,Grouped count,,,,
C,Ungrouped count,,,,

Para mostrar las tres comparaciones, el ejemplo utiliza la carga de trabajo agrupada por intervalo de fechas. Puede aplicar el método a otra consulta sin seguir el ejemplo paso a paso. Si la consulta no contiene GROUP BY, omita la ejecución B como se describe a continuación.

SELECT
    payment_type,
    count() AS trip_count,
    formatReadableQuantity(sum(trip_distance)) AS total_distance,
    avg(total_amount) AS total_amount_avg,
    avg(tip_amount) AS tip_amount_avg
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type
ORDER BY trip_count DESC;

SELECT
    payment_type,
    count() AS trip_count
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type;

SELECT count()
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01';

Compare duraciones representativas de ejecuciones repetidas, en lugar de restar dos mediciones individuales. Las diferencias grandes y constantes indican qué investigar a continuación:

Compare el valor de read_rows de la ejecución C con el valor devuelto por count. Por ejemplo, si read_rows es de 100 millones y count devuelve 1 millón, ClickHouse examinó aproximadamente 100 filas de origen por cada fila contabilizada. Esto indica que el filtro descartó la mayoría de las filas leídas de la tabla, pero no permite determinar el motivo. Esta proporción está pensada para análisis sencillos de una sola tabla. Para consultas con múltiples fuentes de datos o proyecciones, interprete read_rows mediante el plan de ejecución.

En ClickHouse 25.9 y versiones posteriores, desactive la caché de condiciones de consulta y la aplicación dinámica de índices de omisión de datos antes de inspeccionar el uso de los índices:

SET use_query_condition_cache = 0;
SET use_skip_indexes_on_data_read = 0;

A continuación, use EXPLAIN indexes = 1 para ver qué índices utilizó ClickHouse y cuántas partes y gránulos descartó cada índice. Si ClickHouse seleccionó más gránulos de los esperados, compruebe si los filtros se ajustan a la clave de ordenación de la tabla y si la poda de particiones o un índice de omisión de datos podrían descartar más gránulos. Si el plan no incluye una sección Indexes, EXPLAIN no informó de la poda de índices para esa consulta. Por el contrario, es esperable que una consulta analítica sobre toda la tabla lea la mayor parte de ella.

Una vez que la comparación indique un posible cuello de botella, valídelo antes de modificar el esquema o la consulta. Use pruebas adecuadas para la fuente de latencia sospechada:

• Para un cuello de botella de escaneo o filtrado, use EXPLAIN indexes = 1 con la configuración descrita anteriormente para ver qué índices utiliza ClickHouse y cuántas partes y gránulos elimina cada índice. Compruebe si el plan utiliza una proyección implícita en lugar del escaneo previsto.
• Para un cuello de botella de agrupación o agregación, inspeccione los eventos de perfil de consulta pertinentes y el uso máximo de memoria.
• Si la ejecución C sigue siendo lenta e incluye joins, compárela con una consulta de diagnóstico que elimine un join cada vez. Una reducción considerable de la duración sugiere que el join eliminado supone una carga significativa. Dado que eliminar un join cambia el significado de la consulta, utilice esta comparación solo para aislar los tiempos e interprete por separado los cambios en el número de filas.
• Para un cuello de botella en otra operación que conserva la ejecución C, inspeccione el plan de ejecución y los eventos de perfil de consulta pertinentes.

Consulte la guía de diagnóstico de consultas lentas para obtener información detallada sobre los índices que devuelve EXPLAIN. Aplique un cambio específico y repita las ejecuciones A, B y C en las mismas condiciones. Confirme que el cambio redujo el trabajo previsto y no desplazó el cuello de botella a otro punto.

Continúe con Enfoques de optimización para asociar el posible cuello de botella con uno o varios cambios específicos.