Obtenir des insights sur les performances des requêtes

Ce document explique comment utiliser le graphique d'exécution de requêtes pour diagnostiquer les problèmes de performances des requêtes et afficher les insights correspondants.

BigQuery offre des performances de requête élevées, mais il s'agit d'un système distribué complexe doté de nombreux facteurs internes et externes qui peuvent affecter la vitesse des requêtes. La nature déclarative du langage SQL peut également masquer la complexité de l'exécution des requêtes. Par conséquent, il peut être difficile de comprendre ce qui s'est passé lorsque les requêtes s'exécutent plus lentement que prévu ou que lors d'exécutions précédentes.

Le graphique d'exécution de requête fournit une interface intuitive permettant d'inspecter les détails des performances des requêtes. Elle vous permet d'examiner les informations du plan de requête sous forme de graphique pour n'importe quelle requête, qu'elle soit en cours d'exécution ou terminée.

Vous pouvez également utiliser le graphique d'exécution de requête pour obtenir des informations sur les performances des requêtes. Les insights sur les performances fournissent des suggestions d'optimisation pour vous aider à améliorer les performances des requêtes. Étant donné que les performances des requêtes possèdent plusieurs attributs, les insights sur les performances peuvent ne fournir qu'une idée partielle des performances globales des requêtes.

Remarque : Il est possible que les insights sur les performances ne soient pas générés pour les jobs de requête exécutés avant le 1er octobre 2022.

Autorisations requises

Pour utiliser le graphique d'exécution de requête, vous devez disposer des autorisations suivantes :

• bigquery.jobs.get
• bigquery.jobs.listAll

Ces autorisations sont disponibles via les rôles IAM (Identity and Access Management) prédéfinis suivants :

• roles/bigquery.admin
• roles/bigquery.resourceAdmin
• roles/bigquery.resourceEditor
• roles/bigquery.resourceViewer

Afficher les informations sur les performances des requêtes

Console

Pour afficher des insights sur les performances des requêtes, procédez comme suit :

1. Ouvrez la page BigQuery dans la console Google Cloud.

   Accéder à la page "BigQuery"

2. Dans l'éditeur, cliquez sur Historique personnel ou Historique du projet.

3. Dans la liste des jobs, identifiez celui qui vous intéresse. Cliquez sur  Actions, puis sélectionnez Ouvrir la requête dans l'éditeur.

4. Sélectionnez l'onglet Execution Graph (Graphique d'exécution) pour afficher une représentation graphique de chaque stade de la requête :

   

   Pour déterminer si une phase de requête comporte des insights sur les performances, consultez l'icône qui s'affiche. Les phases comportant une icône d'information  disposent d'insights sur les performances. Les phases comportant une icône représentant une coche  n'en disposent pas pas.

5. Cliquez sur une étape pour ouvrir son volet, dans lequel vous pouvez voir les informations suivantes :

   • Informations sur le plan de requête pour la phase.
   • Les étapes exécutées dans l'étape.
   • Tous les insights sur les performances applicables.

   

6. Facultatif : Si vous inspectez une requête en cours d'exécution, cliquez sur  Sync (Synchroniser) pour mettre à jour le graphique d'exécution afin qu'il reflète l'état actuel de la requête.

   

7. Facultatif : Pour mettre en évidence les principales phases sur le graphique en fonction de leur durée, cliquez sur Highlight top stages by duration (Mettre en évidence les principales phases par durée).

   

8. Facultatif : Pour mettre en évidence les principales phases sur le graphique en fonction de leur durée d'utilisation des emplacements, cliquez sur Highlight top stages by processing (Mettre en évidence les principales phases par traitement).

   

9. Facultatif : pour inclure les phases de redistribution du brassage sur le graphique, cliquez sur Show shuffle redistribution stages (Afficher les phases de redistribution du brassage).

   

   Cette option permet d'afficher les phases de répartition et de réduction masquées dans le graphique d'exécution par défaut.

   Des phases de répartition et de réduction sont introduites pendant l'exécution de la requête et permettent d'améliorer la distribution des données entre les nœuds de calcul qui traitent la requête. Étant donné que ces phases ne sont pas liées au texte de votre requête, elles sont masquées pour simplifier le plan de requête affiché.

Pour toute requête présentant des problèmes de régression des performances, les insights sur les performances sont également affichés dans l'onglet Job information (Informations sur le job) de la requête :

SQL

1. Dans la console Google Cloud, accédez à la page BigQuery.

   Accéder à BigQuery

2. Dans l'éditeur de requête, saisissez l'instruction suivante :

   
   SELECT
     `bigquery-public-data`.persistent_udfs.job_url(
       project_id || ':us.' || job_id) AS job_url,
     query_info.performance_insights
   FROM
     `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
   WHERE
     DATE(creation_time) >= CURRENT_DATE - 30 -- scan 30 days of query history
     AND job_type = 'QUERY'
     AND state = 'DONE'
     AND error_result IS NULL
     AND statement_type != 'SCRIPT'
     AND EXISTS ( -- Only include queries which had performance insights
       SELECT 1
       FROM UNNEST(
         query_info.performance_insights.stage_performance_standalone_insights
       )
       WHERE slot_contention OR insufficient_shuffle_quota
       UNION ALL
       SELECT 1
       FROM UNNEST(
         query_info.performance_insights.stage_performance_change_insights
       )
       WHERE input_data_change.records_read_diff_percentage IS NOT NULL
     );
   

3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

API

Vous pouvez obtenir des informations sur les performances des requêtes dans un format non graphique en appelant la méthode d'API jobs.list et en inspectant les informations JobStatistics2 renvoyées.

Interpréter les informations sur les performances des requêtes

Utilisez cette section pour en savoir plus sur la signification des insights de performances et sur la façon de les gérer.

Les insights sur les performances sont destinés à deux audiences :

• Analystes : vous exécutez des requêtes dans un projet. Vous souhaitez savoir pourquoi une requête que vous avez exécutée précédemment est plus lente de manière inattendue, et obtenir des conseils pour améliorer ses performances. Vous disposez des autorisations décrites dans la section Autorisations requises.

• Administrateurs de lacs de données ou d'entrepôts de données : vous gérez les ressources et les réservations BigQuery de votre organisation. Vous disposez des autorisations associées au rôle d'administrateur BigQuery.

Chacune des sections suivantes fournit des conseils sur la manière de traiter les insights sur les performances que vous recevez, en fonction de votre rôle.

Conflit d'emplacements

Lorsque vous exécutez une requête, BigQuery tente de diviser le travail nécessaire à votre requête en tâches. Une tâche est une seule tranche de données d'entrée et de sortie d'une étape. Un seul emplacement récupère une tâche et exécute cette tranche de données pour l'étape. Dans l'idéal, les emplacements BigQuery exécutent ces tâches en parallèle afin d'obtenir des performances élevées. Un conflit d'emplacements se produit lorsque votre requête dispose de nombreuses tâches prêtes à être exécutées, mais que BigQuery ne peut pas obtenir suffisamment d'emplacements disponibles pour les exécuter.

Que faire si vous êtes analyste ?

Réduisez les données que vous traitez dans votre requête en suivant les instructions de la section Réduire les données traitées dans les requêtes.

Que faire si vous êtes administrateur ?

Augmentez la disponibilité des emplacements ou réduisez leur utilisation en effectuant les actions suivantes :

• Si vous appliquez la tarification à la demande de BigQuery, vos requêtes utilisent un pool d'emplacements partagé. Envisagez de passer à la tarification de l'analyse basée sur la capacité en achetant à la place des réservations. Les réservations vous permettent de réserver des emplacements dédiés pour les requêtes de votre organisation.

• Si vous utilisez des réservations BigQuery, assurez-vous que le nombre d'emplacements est suffisant dans la réservation attribuée au projet qui exécute la requête. La réservation peut ne pas avoir suffisamment d'emplacements dans les scénarios suivants :

  • D'autres jobs consomment des emplacements de réservation. Vous pouvez utiliser les graphiques de ressources d'administrateur pour voir comment votre organisation utilise la réservation.
  • La réservation ne dispose pas de suffisamment d'emplacements attribués pour exécuter les requêtes suffisamment rapidement. L'estimateur d'emplacements vous permet d'obtenir une estimation de la taille de vos réservations afin de traiter efficacement les tâches de vos requêtes.

  Pour résoudre ce problème, vous pouvez essayer l'une des solutions suivantes :

  • Ajoutez des emplacements à cette réservation.
  • Créez une réservation supplémentaire et attribuez-la au projet qui exécute la requête.
  • Répartissez les requêtes nécessitant beaucoup de ressources, au fil du temps dans une réservation ou sur différentes réservations.

• Assurez-vous que les tables que vous interrogez sont mises en cluster. Le clustering permet de garantir que BigQuery peut lire rapidement les colonnes avec des données corrélées.

• Assurez-vous que les tables que vous interrogez sont partitionnées. Pour les tables non partitionnées, BigQuery lit l'intégralité de la table. Le partitionnement de vos tables vous permet de n'interroger que le sous-ensemble de tables qui vous intéresse.

Quota de brassage insuffisant

Avant d'exécuter votre requête, BigQuery décompose la logique de votre requête en étapes. Les emplacements BigQuery exécutent les tâches pour chaque étape. Lorsqu'un emplacement termine l'exécution des tâches d'une phase, il stocke les résultats intermédiaires en brassage. Les étapes suivantes de la requête lisent les données du brassage pour poursuivre l'exécution de la requête. Le quota de brassage est insuffisant lorsque vous avez plus de données à écrire que de capacité de brassage.

Que faire si vous êtes analyste ?

Comme pour le conflit d'emplacements, la réduction de la quantité de données traitées par vos requêtes peut réduire l'utilisation du brassage. Pour ce faire, suivez les instructions de la section Réduire les données traitées dans les requêtes.

Certaines opérations en SQL utilisent généralement une plus le brassage, en particulier les opérations JOIN et les clauses GROUP BY. Dans la mesure du possible, la réduction de la quantité de données dans ces opérations peut réduire l'utilisation du brassage.

Que faire si vous êtes administrateur ?

Réduisez les conflits de quota de brassage en procédant comme suit :

• Comme pour les conflits d'emplacements, si vous utilisez la tarification à la demande de BigQuery, vos requêtes utilisent un pool partagé d'emplacements. Envisagez de passer à la tarification de l'analyse basée sur la capacité en achetant à la place des réservations. Les réservations vous offrent des emplacements dédiés et une capacité de brassage pour les requêtes de vos projets.

• Si vous utilisez des réservations BigQuery, les emplacements disposent d'une capacité de brassage dédiée. Si votre réservation exécute des requêtes qui utilisent le brassage de manière intensive, cela peut entraîner une capacité de brassage insuffisante pour les autres requêtes exécutées en parallèle. Vous pouvez identifier quelles tâches utilisent la capacité de brassage de manière intensive en interrogeant la colonne period_shuffle_ram_usage_ratio de la vue INFORMATION_SCHEMA.JOBS_TIMELINE.

  Pour résoudre ce problème, vous pouvez essayer l'une ou plusieurs des solutions suivantes :

  • Ajoutez des emplacements à cette réservation.
  • Créez une réservation supplémentaire et attribuez-la au projet qui exécute la requête.
  • Répartir les requêtes utilisant le brassage de manière intensive, au fil du temps dans une réservation ou sur des réservations différentes.

Modification de l'échelle de saisie des données

Cet insight sur les performances indique que votre requête lit au moins 50 % de données en plus pour une table d'entrée donnée qu'à la dernière exécution de la requête. Vous pouvez utiliser l'historique des modifications de table pour voir si la taille de l'une des tables utilisées dans la requête a récemment augmenté.

Que faire si vous êtes analyste ?

Réduisez les données que vous traitez dans votre requête en suivant les instructions de la section Réduire les données traitées dans les requêtes.

Jointure à cardinalité élevée

Lorsqu'une requête contient une jointure avec des clés non uniques des deux côtés de la jointure, la taille de la table de sortie peut être considérablement plus grande que celle des tables d'entrée. Cet insight indique que le ratio entre les lignes de sortie et les lignes d'entrée est élevé et fournit des informations sur ces nombres de lignes.

Que faire si vous êtes analyste ?

Vérifiez les conditions de jointure pour vérifier que l'augmentation de la taille de la table de sortie est attendue. Évitez d'utiliser des jointures croisées. Si vous devez utiliser une jointure croisée, essayez d'utiliser une clause GROUP BY pour pré-agréger les résultats ou utilisez un fenêtrage. Pour en savoir plus, consultez la section Réduire les données avant d'utiliser une jointure (JOIN).

Décalage de partition

Preview

Cette fonctionnalité est soumise aux "Conditions des offres de pré-DG" dans la section "Conditions d'utilisation générales" des Conditions spécifiques au service. Les fonctionnalités pré-DG sont disponibles "en l'état" et peuvent avoir une assistance limitée. Pour en savoir plus, consultez les descriptions des étapes de lancement.

Pour envoyer des commentaires ou demander de l'aide concernant cette fonctionnalité, envoyez un e-mail à l'adresse [e-mail].

Une distribution asymétrique des données peut ralentir l'exécution des requêtes. Lorsqu'une requête est exécutée, BigQuery divise les données en petites partitions. Vous ne pouvez pas partager les partitions entre plusieurs emplacements. Par conséquent, si les données sont réparties de manière inégale, certaines partitions deviennent très volumineuses, ce qui entraîne le plantage de l'emplacement qui traite la partition surdimensionnée.

Le décalage se produit au cours des étapes JOIN. Lorsque vous exécutez une opération JOIN, BigQuery divise en partitions les données situées du côté droit et du côté gauche de l'opération JOIN. Si une partition est trop volumineuse, les données sont rééquilibrées par des étapes de repartitionnement. Si les performances du décalage sont trop mauvaises et que BigQuery ne peut pas procéder à un nouveau rééquilibrage, un insight de décalage de partition est ajouté à l'étape "JOIN". Ce processus correspond aux étapes de repartitionnement. Si BigQuery détecte des partitions volumineuses qui ne peuvent pas être divisées, un insight de décalage de partition est ajouté à l'étape JOIN.

Que faire si vous êtes analyste ?

Pour éviter le décalage de partition, filtrez vos données le plus tôt possible. Pour plus d'informations sur les façons d'éviter un décalage de partition, consultez la page Filtrer les données asymétriques.

Interpréter les informations sur la phase d'une requête

En plus d'utiliser les insights sur les performances des requêtes, vous pouvez également appliquer les instructions suivantes lorsque vous consultez les détails de l'étape de requête pour déterminer s'il y a un problème avec une requête :

• Si la valeur Wait ms (temps d'attente en ms) pour une ou plusieurs phases est élevée par rapport aux exécutions précédentes de la requête :
  • Vérifiez si vous disposez de suffisamment d'emplacements pour répondre à votre charge de travail. Si ce n'est pas le cas, équilibrez la charge lorsque vous exécutez des requêtes nécessitant beaucoup de ressources afin qu'elles ne se concurrencent pas.
  • Si la valeur Wait ms (temps d'attente en ms) est supérieure à celle attendue pour une seule phase, examinez la phase antérieure pour vérifier la présence éventuelle d'un goulot d'étranglement. Des éléments tels que des modifications importantes sur les données ou le schéma des tables impliquées dans la requête peuvent affecter les performances de la requête.
• Si la valeur Shuffle output bytes (Octets de sortie de brassage) d'une étape est élevée par rapport aux exécutions précédentes de la requête ou par rapport à une phase précédente, évaluez les étapes traitées dans cette phase pour savoir si l'une d'entre elles crée de grandes quantités de données de manière inattendue. Ce problème se produit généralement lorsqu'une étape traite une jointure INNER JOIN qui présente des clés en double de chaque côté de la jointure. Cela peut renvoyer une quantité de données inattendue.
• Le graphique d'exécution permet d'afficher les principales phases par durée et par traitement. Déterminez la quantité de données qu'elles produisent et si elles sont comparables à la taille des tables référencées dans la requête. Si ce n'est pas le cas, examinez les étapes dans ces phases pour savoir si l'une d'entre elles peut produire une quantité inattendue de données intermédiaires.

Étapes suivantes

• Consultez les conseils d'optimisation des requêtes pour savoir comment améliorer les performances des requêtes.