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.

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 :

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.

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 :

1. Exécutez la requête d'origine afin d'établir les mesures de référence.
2. 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.
3. 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.

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;

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 :

1. 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.

2. 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.

3. 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.

4. 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;

5. 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.

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.

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

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.

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';

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 :

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.

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.

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