Optimisations BigQuery (Partie 3)

Partie 1

Partie 2

Les deux sections précédentes ont abordé de nombreux sujets : prérequis, bonnes connaissances générales à avoir, et mise en place d'une infrastructure pour analyser les données.

L'ensemble du code utilisé tout au long de cette série se trouve dans le dépôt GitHub suivant : https://github.com/doitintl/bigquery-optimization-queries

Comme les deux méthodes de monitoring génèrent des schémas radicalement différents, j'ai séparé les fichiers SQL en deux dossiers (audit_log et information_schema) dans le dépôt.

Chaque répertoire contient un ensemble de fichiers SQL dédiés à un usage précis, et un README.md y explique le rôle de chaque script.

À la racine se trouve un script Python nommé generate_sql_files.py qui génère une copie des fichiers SQL avec les informations de votre projet et de votre dataset.

Le dépôt GitHub évoluant en permanence, j'ai rédigé un article qui répertorie chaque requête avec sa description ici.

Il sera mis à jour au fur et à mesure que de nouvelles requêtes seront ajoutées.

À partir de la prochaine section et tout au long de cette série, je ferai référence à de nombreuses requêtes et examinerai un grand volume de données. L'interface de BigQuery gère correctement de nombreuses requêtes et analyses, mais je recommande VIVEMENT d'exporter ces données vers un tableur.

Google Sheets est l'option idéale, car BigQuery peut y exporter les résultats directement (avec quelques limitations de taille). De plus, exporter les données une fois vers un tableur revient moins cher que de relancer plusieurs fois la même requête pour vos analyses.

Vous devez également garder un œil sur les coûts. Certaines de ces requêtes peuvent être très onéreuses, je recommande donc systématiquement de vérifier le coût d'une requête avant de la lancer. J'ai ajouté à chaque requête une variable nommée interval_in_days qui contrôle la profondeur d'analyse temporelle ; vous pouvez la modifier pour réduire le volume de données scannées. Certains de nos clients ont simplement adapté les clauses WHERE pour cibler les plages qui les intéressent : les possibilités sont infinies avec ces requêtes.

Toutes les étapes de cette partie peuvent être réalisées dans la section BQ Lens du CMP. Le panneau Explorer permet de les effectuer visuellement ; en pratique, si vous utilisez les requêtes du sink des audit logs, beaucoup d'entre elles sont quasi identiques à celles que le CMP exécute pour afficher les données.

La première étape de l'optimisation des coûts BigQuery consiste à identifier les requêtes les plus coûteuses de votre environnement. Bien souvent, leur existence même est ignorée — comme nous l'enseignait GI Joe dans les dessins animés du samedi matin : Savoir, c'est déjà gagner la moitié de la bataille.

Une fois ces requêtes identifiées, place à l'optimisation : repérer les processus ou utilisateurs problématiques qui les exécutent, puis corriger le tir.

Je ferai référence aux fichiers SQL du dépôt GitHub mentionné plus haut (lien). Vous pouvez utiliser indifféremment les fichiers audit log ou information_schema, le résultat sera le même.

La première requête utilisée dans notre analyse se trouve dans le fichier top_costly_queries.sql ; c'est sans doute la plus importante de toutes en matière de coûts. Elle additionne le coût total qu'une requête engendre sur l'intervalle, en cumulant toutes ses exécutions. Elle révèle donc les requêtes réellement les plus coûteuses, qui peuvent tourner plusieurs fois et coûter bien plus que ce qu'elles laissent paraître au premier abord.

Cette requête a deux objectifs : 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. Bien souvent, un cron job, une tâche dans un DAG d'une instance Airflow, une Cloud Function, etc. exécute une requête de façon répétée — souvent plus que de raison. Et plus souvent encore, le créateur, le mainteneur ou la personne qui paie la facture cloud n'a aucune idée du coût de ces requêtes par exécution ou par mois. C'est là que cette requête s'avère précieuse !

La deuxième requête utilisée dans cette section se trouve 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 de coût. Notez que, contrairement à la première, elle ne tient pas compte du fait qu'une requête s'exécute plusieurs fois : elle affiche simplement le coût par exécution.

Lancer cette requête fait immédiatement remonter les requêtes les plus coûteuses en haut du jeu de résultats. Une bonne pratique consiste à repérer la requête en tête et à examiner d'abord la quantité de données traitées (les colonnes total*Billed). Confrontez ensuite ce chiffre à la requête elle-même pour comprendre pourquoi elle coûte autant.

Bien souvent, les coupables sont les problèmes listés plus haut, mais d'autres facteurs peuvent entrer en jeu. Je recommande donc d'examiner les requêtes pour repérer ce qui saute aux yeux.

Si les résultats renvoient plusieurs fois exactement la même requête et faussent l'analyse ou rendent la lecture difficile, utilisez plutôt le fichier top_billed_queries_deduplicated.sql. Il s'agit de la même requête, mais qui déduplique la requête exacte exécutée pour qu'un seul résultat apparaisse. Elle prend plus de temps et scanne davantage de données, mais elle renvoie des résultats dédupliqués.

Notez qu'à l'heure où j'écris ces lignes, BigQuery ne fournit pas d'agrégat natif de similarité de chaînes tel que la distance de Hamming ou la distance de Levenshtein, ce qui rend relativement difficile le filtrage de requêtes similaires comportant de petites variations comme une date. Au besoin, il existe quelques solutions UDF qui implémentent certains algorithmes de similarité de chaînes pour BigQuery, mais je suggère plutôt de bâtir une solution en dehors de BigQuery, car les implémentations UDF d'algorithmes aussi gourmands en calculs sont assez lentes.

La troisième et dernière requête pour analyser les coûts est le fichier top_cost_users.sql. Elle liste les requêtes les plus coûteuses de votre environnement, triées par utilisateur, suivies de la requête la plus onéreuse.

L'objectif est de mettre en évidence les utilisateurs ou comptes de service qui dépensent le plus, et sur quelles requêtes. Cette liste fait souvent apparaître des processus exécutant des requêtes inefficaces dont vous ignoriez l'existence. Dans certains cas extrêmes, il peut s'agir de processus situés dans d'autres projets ou d'autres clouds. Ajouter des filtres supplémentaires pour les écarter peut s'avérer utile.

Voilà les trois requêtes principales, plutôt généralistes, pour aider à découvrir les requêtes coûtant plus cher que prévu. Le dépôt GitHub inclut quelques requêtes supplémentaires à des fins plus spécifiques : repérer les requêtes provenant de Looker, savoir combien de fois une requête est exécutée, connaître le coût des requêtes portant un label précis, etc. Elles sont assez ciblées, mais ce sont des requêtes que mes collègues de DoiT et moi-même avons utilisées par le passé, et que nous partageons avec la communauté.

Le sujet majeur suivant : repérer les requêtes qui consomment plus de ressources que nécessaire et n'offrent pas les performances attendues. Dans bien des cas, les requêtes identifiées ici recouperont les plus coûteuses : il y aura donc probablement des points communs.

Dans cette section, le terme complexité reviendra souvent. Le définir est, eh bien, complexe, mais par souci de simplicité je le définis ici comme le nombre de slots qu'une requête utilise pendant son exécution. Cette valeur correspond à la durée totale passée par les slots à effectuer du travail, divisée par le temps total d'exécution de la requête. Voici un exemple appliqué à la vue JOBS_BY_PROJECT :

SELECT
 SAFE_DIVIDE(total_slot_ms, TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS slotCount
FROM
 `<project-name>`.`<dataset-region>`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

Pour répondre à une question fréquemment posée : il s'agit d'une approximation, car BigQuery ne renvoie malheureusement pas les valeurs exactes, et c'est ce calcul qu'affiche également l'interface BigQuery.

Dans tout système de base de données, lorsqu'une requête est plus complexe sur le plan logique, il existe généralement une corrélation directe avec la complexité de son exécution interne. BigQuery ne fait pas exception, et il y a en plus une corrélation directe entre cette complexité et le coût de la requête. De manière générale, réduire la complexité réduit donc aussi les coûts.

Cela dit, la requête principale du dépôt GitHub à utiliser pour évaluer la complexité d'une requête est top_complex_queries.sql. Elle renvoie les requêtes qui consomment le plus de slots pendant leur exécution ; en récupérer les premières aide à identifier celles qui pourraient présenter des problèmes de performances.

Ce n'est pas une science exacte, car les requêtes complexes doivent souvent le rester pour fonctionner correctement, mais c'est un bon moyen de trouver des candidates susceptibles de poser problème.

Une autre piste pour identifier les problèmes de performances consiste à examiner les requêtes qui s'exécutent le plus longtemps. Comme indiqué plus haut, ce n'est pas non plus une science exacte, car bien souvent une requête prend du temps tout simplement parce qu'elle ne peut pas faire autrement.

La requête longest_running_queries.sql renvoie toutes les requêtes sur la période, classées de la plus longue à la plus courte. Les requêtes prenant le plus de temps remontent ainsi en haut de la liste pour examen. La colonne runtimeToBytesBilledRatio donne une bonne indication du nombre d'octets traités par milliseconde. En général, si ce nombre est supérieur à 1, il vaut la peine d'analyser la requête pour voir si quelque chose peut être optimisé afin de réduire le volume de données traitées.

Une prochaine partie de cette série couvrira beaucoup plus en détail le tuning des performances, en présentant certains des pièges les plus méconnus de BigQuery et les méthodes pour les surmonter.

Le dernier sujet abordé dans cette partie de la série regroupe un ensemble de requêtes du dépôt GitHub qui présentent des informations ou des métadonnées plus génériques que celles vues jusqu'ici.

Il existe plusieurs types de jobs : queries, loads, extracts et table copies. Les plus courants sont de loin les queries et les loads ; le dépôt contient donc les fichiers load_job_information.sql et query_job_information.sql qui renvoient ces types de jobs ainsi que des métadonnées utiles à leur sujet.

Il existe également une version plus générique, general_job_information.sql, qui retourne tous les types de jobs et des métadonnées génériques associées.

Précisons en préambule que Google a modifié, en décembre 2022, le comportement de BigQuery concernant les requêtes concurrentes. Auparavant, il existait une limite stricte de 100 requêtes concurrentes par projet ; en prévision des évolutions à venir et du déploiement des files de requêtes, cette valeur évolue désormais en fonction du nombre de slots disponibles.

Notez que ce point n'est pas entièrement documenté à l'heure où j'écris, mais j'ai recoupé ces informations auprès du support et dans la documentation existante.

Par défaut, cette valeur est fixée à zéro, ce qui signifie que BigQuery détermine dynamiquement la concurrence en fonction des ressources disponibles pouvant être allouées au projet. En passant à une tarification flat-rate, un paramètre appelé maximum concurrency sur la réservation peut être défini pour demander que ce nombre serve de plafond. Google ne peut pas garantir que la valeur sera bien atteinte, mais s'efforce d'y parvenir. La documentation officielle se trouve sur la page des files de requêtes ici.

Cet avertissement posé, plongeons dans les problèmes liés aux requêtes concurrentes et la manière de les diagnostiquer.

Lors de l'exécution de requêtes, il arrive d'en lancer trop en parallèle et de recevoir 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. Inutile d'expliquer pourquoi c'est problématique.

Même si votre organisation n'a pas atteint cette limite sur certains projets, mieux vaut en avoir conscience pour l'éviter de manière préventive. J'aborderai dans une prochaine partie de cette série certaines stratégies pour atténuer ce problème ou concevoir vos workloads pour qu'il ne se produise pas, une fois que les nouveaux changements mis en place par Google pourront être pleinement disséqués et compris.

En attendant, pour calculer vos moyennes de concurrence à la minute et à la seconde, utilisez les requêtes concurrent_queries_by_minute.sql et concurrent_queries_by_seconds.sql. Elles renvoient le nombre moyen de requêtes en cours d'exécution simultanée par minute (ou par seconde) sur l'intervalle défini.

Bien souvent, une requête est exécutée plusieurs fois, soit par un utilisateur, soit via un processus automatisé. Comme chaque requête a un coût, il est utile de savoir à quelle fréquence elle s'exécute pour déterminer si elle tourne trop souvent et engendre des dépenses superflues.

La requête query_counts.sql affiche le nombre de fois où une requête a été exécutée sur l'intervalle de temps. C'est très utile pour repérer une requête qui s'exécute plus souvent qu'elle ne le devrait, et coûte donc plus que nécessaire. Elle inclut aussi le coût total de la requête sur l'intervalle spécifié, ce qui aide à juger si elle tourne et coûte trop, ou non.

Ceci conclut les trois premières sections consacrées à l'optimisation BigQuery. Dans la prochaine, j'ai placé un tableau présentant le contenu du dépôt GitHub, qui sera mis à jour à mesure que de nouvelles requêtes seront ajoutées et documentera la fonctionnalité de chacune.

D'autres sections de cette série suivront sous peu : BigQuery traverse une phase d'évolutions, et je serai là pour explorer comment garder votre utilisation optimisée à mesure qu'elles seront déployées.