Isoler les goulots d’étranglement des requêtes

L’optimisation des requêtes est plus facile lorsque vous modifiez un seul élément d’une requête à la fois et comparez les résultats à une référence stable. Ce guide explique comment simplifier progressivement une requête et utiliser les différences entre les exécutions pour identifier les opérations qui contribuent le plus à sa durée. Vous pouvez ensuite confirmer le goulot d’étranglement suspecté avant de choisir une optimisation.

Avant de commencer

Commencez par un schéma récurrent de requêtes lentes que vous souhaitez analyser. Si vous n’en avez pas encore identifié, Diagnostiquer les requêtes lentes vous guide tout au long du processus. Pour exécuter les exemples de ce guide tels quels, créez et chargez la table nyc_taxi.trips_small_inferred si ce n’est pas déjà fait :

Configurer l'ensemble de données d'exemple

Le fichier Parquet source fait environ 5,8 Go. Son chargement peut prendre plusieurs minutes, selon votre réseau et les ressources disponibles.

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 table d’exemple utilise ORDER BY () ; son filtre de date ne peut donc pas s’appuyer sur une clé de tri pour éliminer des données lors de la lecture. Utilisez cet exemple pour vous exercer à la méthode de comparaison, et non comme référence de performance.

Fonctionnement

Simplifier progressivement une requête permet de comparer sa durée avant et après la suppression d’une étape de traitement. Les différences permettent de déterminer s’il convient d’examiner le parcours et le filtrage, le regroupement, les calculs d’agrégation ou les opérations ultérieures, telles que le tri et le formatage de sortie :

Exécutez la requête d’origine afin d’établir les mesures de référence.
Conservez GROUP BY, remplacez les calculs d’agrégation de la requête par count, puis supprimez les opérations ultérieures, telles que le tri et le formatage de sortie.
Supprimez le regroupement et exécutez un count sans regroupement afin d’estimer le traitement restant lié au parcours, au filtrage et aux éventuelles jointures.

Ces étapes s’appliquent directement aux requêtes d’agrégation classiques avec regroupement. Pour les requêtes plus complexes, appliquez le même principe à un bloc SELECT à la fois : conservez des sources de données et des filtres équivalents, supprimez une opération à la fois et vérifiez le plan d’exécution après chaque modification.

Ces différences constituent des estimations à des fins de diagnostic, et non des mesures exactes des étapes d’exécution de ClickHouse. La modification de la requête peut altérer son plan d’exécution, les colonnes lues et les données transmises entre les étapes. Utilisez les résultats pour formuler une hypothèse, puis validez-la à l’aide des journaux de requêtes et de EXPLAIN.

Établissez une référence reproductible

Appliquez les pratiques suivantes pour rendre les mesures comparables :

Conservez les clauses FROM, JOIN, PREWHERE et WHERE inchangées afin que chaque comparaison porte sur les mêmes données et le même intervalle de temps.
Exécutez chaque version de la requête plusieurs fois dans des conditions de charge système similaires.
Maintenez des conditions de cache cohérentes. Exécutez chaque version de la requête avant d’enregistrer les mesures ou désactivez les caches répertoriés ci-dessous. Ne comparez pas des exécutions avec et sans cache.
Enregistrez une durée représentative, par exemple la médiane des exécutions répétées après les éventuelles exécutions de préchauffage, plutôt que de vous fier au résultat le plus rapide ou le plus lent.
Modifiez une variable à la fois afin de pouvoir attribuer une différence de performances à une modification précise.

Pour une comparaison de diagnostic sans cache, désactivez le cache du système de fichiers ClickHouse pour les données distantes, le cache de requêtes et le cache des conditions de requête. Désactivez également les projections implicites afin que le count de l’exécution C n’utilise pas un plan d’exécution optimisé qui évite le parcours que vous souhaitez comparer.

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

Ces instructions SET s’appliquent uniquement à la session en cours. Exécutez toutes les requêtes de comparaison dans cette session ou appliquez les mêmes paramètres à chaque exécution. Le paramètre du cache du système de fichiers ne désactive ni le cache de pages du système d’exploitation ni l’ensemble des caches ClickHouse. Lorsque vous avez terminé, fermez la session dédiée ou restaurez chaque paramètre à sa valeur précédente.

Le workflow combine des exécutions de requêtes contrôlées et des mesures issues du journal des requêtes : Collectez les mesures pour chaque exécution comme suit :

Affectez un ID de requête unique à chaque exécution ou enregistrez l’ID généré par votre interface de requêtes. Par exemple, identifiez les exécutions répétées par bottleneck-a-1, bottleneck-a-2 et bottleneck-a-3. Avec clickhouse-client, transmettez --query_id your-query-id lors de l’exécution d’une requête.
Exécutez chaque requête de comparaison plusieurs fois dans les mêmes conditions. Distinguez les exécutions de préchauffage des exécutions mesurées.
Videz le journal des requêtes avant de rechercher les requêtes récemment terminées :
SYSTEM FLUSH LOGS;
Si vous ne pouvez pas exécuter SYSTEM FLUSH LOGS, attendez que le journal des requêtes soit vidé automatiquement, puis relancez la recherche. Si l’enregistrement n’apparaît jamais, vérifiez que la journalisation des requêtes est activée, que vous pouvez lire system.query_log et que vous interrogez le nœud ayant exécuté la requête.
Recherchez l’enregistrement terminé pour chaque ID de requête. system.query_log enregistre les événements QueryStart et QueryFinish d’une requête terminée. Filtrez sur QueryFinish, qui contient la durée finale, le nombre de lignes et d’octets lus, ainsi que le pic de mémoire :
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;
Pour chaque version de la requête, utilisez la durée médiane des exécutions mesurées. Relevez read_rows, read_bytes et le pic de mémoire de l’exécution la plus proche de cette médiane afin que les mesures correspondent à une exécution réelle.

Pour les requêtes distribuées, memory_usage dans l’enregistrement QueryFinish de la requête initiatrice ne représente pas le pic à l’échelle du cluster. Utilisez initial_query_id pour examiner les enregistrements QueryFinish enfants sur les nœuds participants.

Utilisez un tableau comme celui-ci pour organiser les mesures représentatives. Consultez system.query_log pour plus d’informations sur ses champs et sa configuration.

Tableau
CSV

Exécution	Version de la requête	Durée représentative	`read_rows`	`read_bytes`	Pic de mémoire
A	Requête d’origine
B	`count` groupé
C	`count` non groupé

query-comparison.csv

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

Exécuter des requêtes de plus en plus simples

Pour illustrer les trois comparaisons, l’exemple utilise la charge de travail agrégée par plage de dates. Vous pouvez appliquer cette méthode à une autre requête sans suivre l’exemple pas à pas. Si la requête ne contient pas de GROUP BY, ignorez l’exécution B comme décrit ci-dessous.

Exécution A : mesurer la requête d'origine

Exécutez la requête complète sans modifier ses filtres, son regroupement, ses expressions d’agrégation, son tri ni sa sortie. Cela établit une référence pour la durée, le nombre de lignes et d’octets lus, ainsi que le pic d’utilisation de la mémoire.Cette requête regroupe les trajets par type de paiement et calcule plusieurs valeurs agrégées :

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;

Enregistrez les mesures de la requête sous l’exécution A.

Exécution B : conserver le regroupement avec count

Conservez les clauses FROM, JOIN, PREWHERE et WHERE, ainsi que les clés de regroupement de la requête. Remplacez ses expressions d’agrégation par un count regroupé. Supprimez les opérations effectuées après l’agrégation, notamment le tri d’origine et les expressions de sortie.

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;

L’exécution B continue de parcourir et de filtrer les données, d’effectuer les jointures éventuelles et de constituer les groupes. Comparez sa durée à celle de l’exécution A pour estimer la contribution des expressions d’agrégation d’origine et des opérations effectuées après l’agrégation. Comparez également read_bytes, car la suppression d’expressions d’agrégation peut réduire le nombre de colonnes lues.Si la requête d’origine ne contient pas de GROUP BY, il n’y a pas d’étape de regroupement à isoler. Ignorez l’exécution B et comparez directement la requête d’origine à l’exécution C.

Exécution C : supprimer le regroupement

Supprimez GROUP BY et renvoyez un unique count. Conservez les clauses FROM, JOIN, PREWHERE et WHERE inchangées afin que les opérations restantes soient comparables.

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

L’exécution C fournit une référence pour les opérations conservées par son plan, et non une mesure isolée du parcours ou du filtrage. Comparez-la à l’exécution B pour estimer la contribution du regroupement. Comparez également read_bytes, car la suppression de la clé de regroupement peut réduire le nombre de colonnes lues. Le count renvoyé indique le nombre de lignes qui atteignent l’agrégation après application des filtres et jointures conservés.Avant d’interpréter l’exécution C, vérifiez que son plan d’exécution lit la source de données prévue et applique les filtres conservés. Une projection ou un comptage basé sur les métadonnées peut modifier les opérations effectuées. Pour obtenir une référence basée sur le parcours, désactivez, pour les trois exécutions, l’optimisation indiquée dans le plan : utilisez optimize_use_implicit_projections = 0 pour une projection implicite, optimize_use_projections = 0 pour une projection explicite ou optimize_trivial_count_query = 0 pour un comptage non filtré fourni à partir des métadonnées de la table.Si l’exécution C reste lente, examinez les opérations qu’elle conserve, en commençant par le parcours et le filtrage. Utilisez les journaux de requêtes et EXPLAIN pour valider le goulot d’étranglement suspecté avant de modifier la requête.

Interpréter les différences

Comparez des durées représentatives issues d’exécutions répétées plutôt que de soustraire deux mesures individuelles. Des écarts importants et constants indiquent la suite de l’investigation :

Observation	Goulots d’étranglement potentiels	Investigation suivante
L’exécution A est beaucoup plus lente que l’exécution B	Expressions d’agrégation, tri, autres opérations après l’agrégation ou lecture de colonnes supplémentaires	Examinez les fonctions d’agrégation coûteuses, les expressions, `ORDER BY`, `read_bytes` et le pic d’utilisation de la mémoire
L’exécution B est beaucoup plus lente que l’exécution C	Regroupement, cardinalité des groupes ou lecture des clés de regroupement	Examinez les clés de regroupement, le nombre de groupes, `read_bytes` et le pic d’utilisation de la mémoire
L’exécution C reste lente	Parcours, filtrage, jointures ou autre opération conservée dans l’exécution C	Examinez les lignes et les octets lus, l’utilisation de la clé primaire, les index de saut de données et le plan d’exécution ; validez ensuite le goulot d’étranglement suspecté
Les trois exécutions ont des durées similaires	La source de latence peut être commune aux trois versions, ou la simplification peut avoir modifié le plan d’exécution	Comparez `read_rows`, `read_bytes` et le pic de mémoire entre les exécutions. S’ils sont également similaires, examinez les opérations conservées dans l’exécution C. Sinon, comparez les plans d’exécution pour identifier les différences

Comparer les lignes lues au résultat de count

Comparez read_rows pour l’exécution C à la valeur renvoyée par son count. Par exemple, si read_rows est de 100 millions et que count renvoie 1 million, ClickHouse a parcouru environ 100 lignes sources pour chaque ligne comptée. Cela montre que le filtre a écarté la plupart des lignes lues dans la table, mais n’indique pas pourquoi. Ce ratio est destiné aux parcours simples sur une seule table. Pour les requêtes comportant plusieurs sources de données ou projections, interprétez plutôt read_rows à l’aide du plan d’exécution. Pour ClickHouse 25.9 et versions ultérieures, désactivez le cache des conditions de requête et l’application dynamique des index de saut de données avant d’examiner l’utilisation des index :

SET use_query_condition_cache = 0;
SET use_skip_indexes_on_data_read = 0;

Utilisez ensuite EXPLAIN indexes = 1 pour voir quels index ClickHouse a utilisés et combien de parties et de granules chaque index a éliminés. Si ClickHouse a sélectionné plus de granules que prévu, vérifiez si les filtres sont alignés sur la clé de tri de la table et si l’élagage des partitions ou un index de saut de données pourrait éliminer davantage de granules. Si le plan ne comporte pas de section Indexes, EXPLAIN n’a pas signalé d’élagage d’index pour cette requête. À l’inverse, une requête analytique portant sur l’ensemble de la table lira vraisemblablement la majeure partie de celle-ci.

Valider le goulot d’étranglement suspecté

Une fois qu’un goulot d’étranglement probable a été identifié par la comparaison, validez-le avant de modifier le schéma ou la requête. Utilisez des éléments probants adaptés à la source de latence suspectée :

Pour un goulot d’étranglement lié au parcours ou au filtrage, utilisez EXPLAIN indexes = 1 avec les paramètres décrits ci-dessus afin de voir quels index ClickHouse utilise et combien de parts et de granules chaque index élimine. Vérifiez si le plan utilise une projection implicite au lieu du parcours attendu.
Pour un goulot d’étranglement lié au regroupement ou à l’agrégation, examinez les événements de profil de requête pertinents et le pic d’utilisation de la mémoire.
Si l’exécution C reste lente et contient des jointures, comparez-la à une requête de diagnostic qui supprime une jointure à la fois. Une forte diminution de la durée indique que la jointure supprimée représente une charge de travail importante. Comme la suppression d’une jointure modifie le sens de la requête, utilisez cette comparaison uniquement pour isoler les temps d’exécution et interprétez séparément les variations du nombre de lignes.
Pour un goulot d’étranglement dans une autre opération conservée par l’exécution C, examinez le plan d’exécution et les événements de profil de requête pertinents.

Consultez le guide de diagnostic des requêtes lentes pour en savoir plus sur les informations d’index renvoyées par EXPLAIN. Appliquez une modification ciblée, puis répétez les exécutions A, B et C dans les mêmes conditions. Vérifiez que la modification a réduit le travail visé et n’a pas déplacé le goulot d’étranglement ailleurs.

Étapes suivantes

Consultez les Approches d’optimisation pour faire correspondre le goulot d’étranglement suspecté à une ou plusieurs modifications ciblées.

ClickHouse guides

Use case guides

Isoler les goulots d’étranglement des requêtes

Avant de commencer

Fonctionnement

Établissez une référence reproductible

Exécuter des requêtes de plus en plus simples

Interpréter les différences

Comparer les lignes lues au résultat de count

Valider le goulot d’étranglement suspecté

Étapes suivantes

​Avant de commencer

​Fonctionnement

​Établissez une référence reproductible

​Exécuter des requêtes de plus en plus simples

​Interpréter les différences

​Comparer les lignes lues au résultat de count

​Valider le goulot d’étranglement suspecté

​Étapes suivantes

Avant de commencer

Fonctionnement

Établissez une référence reproductible

Exécuter des requêtes de plus en plus simples

Interpréter les différences

Comparer les lignes lues au résultat de count

Valider le goulot d’étranglement suspecté

Étapes suivantes