Extrait d'une série consacrée à l'optimisation des coûts et des performances BigQuery, cet article explique comment repérer les requêtes qui génèrent le plus de coûts dans votre environnement.
Extrait d'une série sur l'optimisation des coûts et des performances BigQuery
La première partie de cette série exposait les prérequis pour exécuter des requêtes dans BigQuery, et la deuxième partie couvrait les erreurs de requêtage les plus fréquentes rencontrées lorsqu'on optimise son utilisation de BigQuery. Dans cet article, nous voyons comment repérer les requêtes les plus coûteuses de votre environnement. L'ensemble du code utilisé dans la série est référencé dans ce dépôt GitHub.
Les requêtes les plus coûteuses au global
La première requête à examiner se trouve dans le fichier top_costly_queries.sql. Sans doute la plus importante en matière de coût, elle additionne le coût total qu'une requête engendre sur l'intervalle considéré, sur l'ensemble de ses itérations. Son objectif est double : déterminer la requête globalement la plus coûteuse de votre environnement et identifier si une requête onéreuse s'exécute plus souvent que nécessaire.
Les requêtes individuelles les plus coûteuses
La deuxième requête utilisée dans cette section figure dans le fichier top_billed_queries.sql. Elle liste les requêtes les plus coûteuses de votre environnement, triées par ordre décroissant. Contrairement à la première, elle ne tient pas compte du nombre d'exécutions ; elle affiche simplement le coût par exécution.
L'exécution de cette requête fait immédiatement remonter les requêtes les plus coûteuses en haut du jeu de résultats. Repérez la première et regardez d'abord la quantité de données qu'elle a traitée (les colonnes total*Billed). Reprenez ce chiffre et examinez la requête elle-même pour comprendre ce qui en fait grimper le coût.
Remarque sur les requêtes en doublon
Si les résultats renvoient plusieurs fois la même requête, faussant l'analyse ou rendant la lecture difficile, consultez plutôt le fichier top_billed_queries_deduplicated.sql. Il s'agit de la même requête, mais elle déduplique la requête exacte exécutée afin qu'un seul résultat apparaisse. Elle prend plus de temps à s'exécuter et scanne davantage de données, mais renvoie des résultats dédupliqués.
BigQuery ne propose pas pour l'instant d'agrégat natif de similarité de chaînes, comme une distance de Hamming ou de Levenshtein, ce qui rend assez difficile le filtrage de requêtes similaires ne différant que par de petits éléments tels qu'une date. Au besoin, vous trouverez quelques solutions UDF qui implémentent certains algorithmes de similarité de chaînes pour BigQuery, mais il vaut mieux concevoir une solution en dehors de BigQuery, car les implémentations UDF d'algorithmes aussi gourmands en calcul restent assez lentes.
Les utilisateurs les plus coûteux
Une troisième requête utile pour analyser les coûts est le fichier top_cost_users.sql. Elle liste les requêtes les plus coûteuses de votre environnement, mais triées par utilisateur, puis par requête la plus coûteuse.
L'objectif est de mettre en évidence quels utilisateurs ou comptes de service dépensent le plus, et sur quelles requêtes. Cette liste révèle souvent des processus exécutant des requêtes inefficaces dont vous n'avez pas connaissance. Dans certains cas extrêmes, ces processus se trouvent dans d'autres projets ou clouds. Ajouter des filtres supplémentaires pour les exclure peut alors s'avérer utile.
Autres requêtes du dépôt
Le dépôt GitHub contient également d'autres requêtes pour des usages plus spécifiques : identification des requêtes émises par Looker, comptage du nombre d'exécutions d'une requête, coût des requêtes portant un label particulier, etc. Elles restent assez ciblées, mais nous les utilisons chez DoiT et avons choisi de les partager avec la communauté.
Repérer les requêtes qui posent des problèmes de performance
Le sujet majeur suivant : repérer les requêtes qui consomment plus de ressources que nécessaire et ne s'exécutent pas comme prévu. Elles coïncident souvent avec les plus coûteuses, il peut donc y avoir un certain recoupement.
Dans cette section, nous définissons la complexité comme le nombre de slots qu'une requête utilise pendant son exécution. Cette valeur correspond au temps total passé par les slots à effectuer le travail, divisé par la durée totale d'exécution de la requête. Voici un exemple appliqué à la vue JOBS_BY_PROJECT :
SELECTSAFE_DIVIDE(total_slot_ms, TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS slotCountFROM`<project-name>`.`<dataset-region>`.INFORMATION_SCHEMA.JOBS_BY_PROJECTIl s'agit d'une approximation car, malheureusement, BigQuery ne renvoie pas les valeurs exactes ; ce calcul correspond également à ce qu'affiche l'interface de BigQuery.
Complexité et nombre de slots
Dans tout système de base de données, l'augmentation de la complexité logique d'une requête se traduit généralement par une exécution interne plus complexe. BigQuery ne fait pas exception. Cette complexité est par ailleurs directement corrélée au coût de la requête. En règle générale, réduire la complexité revient donc aussi à réduire les coûts.
Cela étant, la principale requête du dépôt GitHub à utiliser pour évaluer la complexité est top_complex_queries.sql. Elle renvoie les requêtes qui consomment le plus de slots sur leur durée d'exécution ; en récupérer les premières aide à identifier celles susceptibles de poser des problèmes de performance. Ce n'est pas une science exacte, car certaines requêtes complexes doivent rester telles quelles pour fonctionner correctement, mais c'est une manière de repérer des candidates problématiques.
Requêtes à exécution longue
Une autre piste pour identifier des problèmes de performance consiste à examiner les requêtes les plus longues. Cela dit, une requête met souvent du temps simplement parce qu'elle le doit.
La requête longest_running_queries.sql renvoie l'ensemble des requêtes sur la période, classées de la plus longue à la plus courte. Elle fait remonter en tête de liste les requêtes les plus longues, à examiner en priorité. La colonne runtimeToBytesBilledRatio donne une bonne idée du nombre d'octets traités par milliseconde. En général, si ce chiffre est supérieur à 1, il vaut la peine de vérifier s'il est possible d'optimiser la requête pour réduire le volume de données traitées.
Une remarque sur les problèmes de performance
Dans une prochaine partie de cette série, j'aborderai plus en détail le tuning de performance, en présentant certains des pièges les plus courants et méconnus de BigQuery, ainsi que les méthodes pour les surmonter.
Requêtes à usage général issues du dépôt
Le dernier sujet abordé dans cette partie de la série regroupe des requêtes du dépôt GitHub qui exposent des informations ou des métadonnées plus générales que celles vues jusqu'ici.
Requêtes par type de job
Parmi les différents types de jobs existants (requêtes, chargements, extractions et copies de tables), les requêtes et les chargements sont de loin les plus fréquents. Les fichiers load_job_information.sql et query_job_information.sql du dépôt renvoient justement ces types de jobs ainsi que des métadonnées utiles à leur sujet. Une version plus générique, general_job_information.sql, retourne tous les types de jobs et leurs métadonnées génériques.
Requêtes concurrentes
En décembre 2022, Google a modifié le comportement de BigQuery concernant les requêtes concurrentes. En prévision des évolutions à venir et du déploiement des files d'attente de requêtes, la limite stricte de 100 requêtes concurrentes par projet a été relevée pour évoluer avec le nombre de slots disponibles. Au moment de la rédaction, ce point n'était pas entièrement documenté, mais nous avons recueilli ces informations auprès du support et de la documentation existante.
Par défaut, cette valeur est fixée à zéro, ce qui signifie que BigQuery détermine dynamiquement le niveau de concurrence en fonction des ressources disponibles pour le projet. Lors du passage à la tarification forfaitaire, un paramètre de la réservation appelé maximum concurrency permet de demander un plafond de concurrence. Google ne peut pas garantir qu'il sera respecté, mais s'efforce de l'atteindre. La documentation officielle se trouve sur la page des files d'attente de requêtes ici. Cette mise au point faite, passons aux problèmes liés aux requêtes concurrentes.
Si vous exécutez trop de requêtes en parallèle, vous obtiendrez le redoutable message 503 "Service Unavailable" ou le plus récent "Query was not admitted as the maximum concurrency has been reached." Cela signifie que votre seuil de concurrence a été dépassé et que les requêtes ne seront pas planifiées. Pour des raisons évidentes, c'est une situation à éviter.
Dès que les nouvelles modifications introduites par Google pourront être pleinement analysées, nous présenterons des stratégies pour atténuer ce problème ou concevoir vos workloads de façon à l'éviter. En attendant, utilisez les requêtes concurrent_queries_by_minute.sql et concurrent_queries_by_seconds.sql pour calculer vos moyennes de concurrence à la minute et à la seconde. Elles renvoient le nombre moyen de requêtes concurrentes en cours d'exécution sur chaque minute (ou seconde) de l'intervalle défini.
Comptage des requêtes
Les requêtes sont souvent exécutées plusieurs fois, par un utilisateur ou via un processus automatisé. La requête query_counts.sql affiche le nombre d'exécutions d'une requête sur l'intervalle de temps. C'est très utile pour déterminer si une requête donnée s'exécute plus souvent qu'elle ne le devrait, et gaspille ainsi de l'argent. Elle inclut également le coût total de la requête sur l'intervalle spécifié.
Et ensuite ?
Cet article est une version condensée de ma série d'articles sur l'optimisation de vos requêtes BigQuery, qui sera enrichie au fil des évolutions de BigQuery. Si vous êtes déjà client DoiT, vous pouvez réaliser toutes ces étapes via la fonctionnalité BQ Lens de DoiT Cloud Intelligence™. En attendant, n'hésitez pas à nous contacter chez DoiT pour bénéficier de notre expertise approfondie sur BigQuery, ainsi que dans les domaines du machine learning et de la business intelligence.