Superviser les performances avec le Magasin des requêtes
S’APPLIQUE À :
Azure Database pour PostgreSQL – Serveur flexible
La fonctionnalité du magasin des requêtes d’Azure Database pour le serveur flexible PostgreSQL permet de suivre le niveau de performance des requêtes au fil du temps. Le Magasin des requêtes simplifie la résolution des problèmes de performances en vous aidant à identifier rapidement les requêtes dont l’exécution est la plus longue et qui consomment le plus de ressources. Le Magasin des requêtes capture automatiquement un historique des requêtes et des statistiques d’exécution, et les conserve pour que vous les passiez en revue. Il découpe les données par heure de façon à ce que vous puissiez voir des modèles d’utilisation temporelle. Les données de tous les utilisateurs, des bases de données et des requêtes sont stockées dans une base de données nommée azure_sys dans l’instance du serveur flexible Azure Database pour PostgreSQL.
Important
Ne modifiez pas la base de données azure_sys ou ses schémas. Si vous le faites, le Magasin des requêtes et les fonctionnalités de performances associées ne fonctionneront pas correctement.
Activer le magasin des requêtes
Le Magasin des requêtes est disponible dans toutes les régions sans frais supplémentaires. C’est une fonctionnalité avec option d’adhésion, elle n’est pas activée par défaut sur un serveur. Le Magasin des requêtes peut être activé ou désactivé de façon globale pour toutes les bases de données se trouvant sur un serveur donné, et ne peut pas être activé ou désactivé par base de données.
Important
N’activez pas le Magasin des requêtes sur le niveau tarifaire Burstable, car cela a un impact sur les performances.
Activer le Magasin des requêtes dans le portail Azure
- Connectez-vous au portail Azure et sélectionnez votre instance de serveur flexible Azure Database pour PostgreSQL.
- Sélectionnez Paramètres du serveur dans la section Paramètres du menu.
- Recherchez le paramètre
pg_qs.query_capture_mode. - Définissez la valeur sur
TOPouALL, selon que vous souhaitez effectuer le suivi des requêtes de niveau supérieur ou des requêtes imbriquées (celles exécutées à l’intérieur d’une fonction ou d’une procédure), puis cliquez sur Enregistrer. Autoriser jusqu’à 20 minutes de conservation pour le premier lot de données dans la base de données azure_sys.
query_store_wait_sampling_frequency
- Recherchez le paramètre
pgms_wait_sampling.query_capture_mode. - Définissez la valeur sur
ALLet cliquez sur Enregistrer.
Informations dans le Magasin des requêtes
Le Magasin des requêtes se compose de deux magasins :
- Un magasin des statistiques d’exécution pour conserver les informations sur les statistiques d’exécution des requêtes.
- Un magasin des statistiques d’attente pour conserver les informations sur les statistiques d’attente.
Les scénarios courants pour l’utilisation du Magasin des requêtes sont notamment les suivants :
- Détermination du nombre de fois où une requête a été exécutée dans une fenêtre de temps donnée.
- Comparaison de la durée d’exécution moyenne d’une requête sur des fenêtres de temps pour voir les deltas importants.
- Identification des requêtes durables au cours des dernières heures.
- Identification des N premières requêtes en attente de ressources.
- Comprendre la nature des attentes pour une requête particulière.
Pour réduire l’utilisation de l’espace, les statistiques d’exécution du runtime dans le magasin des statistiques d’exécution sont agrégées pendant une fenêtre de temps configurable fixe. Les informations contenues dans ces magasins peuvent être interrogées à l’aide de vues.
Accéder aux informations du Magasin des requêtes
Les données du Magasin des requêtes sont stockées dans la base de données azure_sys sur votre instance de serveur flexible Azure Database pour PostgreSQL. La requête suivante retourne des informations sur les requêtes du Magasin des requêtes :
SELECT * FROM query_store.qs_view;
Ou cette requête pour les statistiques d’attente :
SELECT * FROM query_store.pgms_wait_sampling_view;
Rechercher des requêtes d’attente
Les types d’événements d’attente combinent différents événements d’attente dans des compartiments par similarité. Le Magasin des requêtes fournit le type d’événement d’attente, le nom d’événement d’attente spécifique et la requête en question. Pouvoir mettre en corrélation ces informations d’attente avec les statistiques d’exécution de requête vous permet de mieux comprendre ce qui contribue aux caractéristiques de performances des requêtes.
Voici quelques exemples illustrant la façon d’obtenir plus d’insights dans votre charge de travail à l’aide des statistiques d’attente dans le Magasin des requêtes :
| Observation | Action |
|---|---|
| Attentes de verrous élevés | Vérifiez les textes de requêtes pour les requêtes affectées et identifiez les entités cibles. Recherchez dans le Magasin des requêtes d’autres requêtes modifiant la même entité, qui est fréquemment exécutée et/ou dont la durée d’exécution est longue. Après avoir identifié ces requêtes, envisagez de changer la logique d’application pour améliorer l’accès concurrentiel, ou utilisez un niveau d’isolation moins restrictif. |
| Attentes d’E/S de mémoire tampon élevées | Recherchez les requêtes comportant un grand nombre de lectures physiques dans le Magasin des requêtes. Si elles correspondent aux requêtes ayant des attentes d’E/S élevées, envisagez d’introduire un index sur l’entité sous-jacente, afin de faire des recherches plutôt que des analyses. Cela réduit la surcharge d’E/S des requêtes. Consultez les Recommandations en matière de performances pour votre serveur dans le portail afin de voir s’il existe des recommandations relatives aux index adaptées à ce serveur qui optimiseraient les requêtes. |
| Attentes de mémoire élevées | Recherchez les principales requêtes consommatrices de mémoire dans le Magasin des requêtes. Ces requêtes retardent probablement davantage la progression des requêtes affectées. Consultez les Recommandations en matière de performances pour votre serveur dans le portail afin de voir s’il existe des recommandations relatives aux index qui optimiseraient ces requêtes. |
Options de configuration
Lorsque le Magasin des requêtes est activé, il enregistre les données dans les fenêtres d’agrégation de longueur déterminée par le paramètre de serveur pg_qs.interval_length_minutes (par défaut à 15 minutes). Pour chaque fenêtre, elle stocke les 500 requêtes distinctes par fenêtre.
Les options suivantes sont disponibles pour la configuration des paramètres du Magasin des requêtes :
| Paramètre | Description | Par défaut | Plage |
|---|---|---|---|
| pg_qs.query_capture_mode | Définit les instructions qui sont suivies. | Aucun | none, top, all |
| pg_qs.interval_length_minutes (*) | Définit l’intervalle de capture query_store en minutes pendant pg_qs : il s’agit de la fréquence de persistance des données. | 15 | 1 - 30 |
| pg_qs.store_query_plans | Active ou désactive l’enregistrement des plans de requête pour pg_qs. | arrêt | on, off |
| pg_qs.max_plan_size | Définit le nombre maximal d’octets qui seront enregistrés pour le texte du plan de requête pour pg_qs ; les plans plus longs seront tronqués. | 7500 | 100 - 10 000 |
| pg_qs.max_query_text_length | Définit la longueur maximale de requête qui peut être enregistrée; les requêtes plus longues seront tronquées. | 6000 | 100 - 10 000 |
| pg_qs.retention_period_in_days | Définit la fenêtre de période de rétention en jours pour pg_qs : après cette suppression des données. | 7 | 1 - 30 |
| pg_qs.index_generation_interval (*) | Définit l’intervalle de génération automatique d’index query_store en minutes pour pg_qs. | 720 | 15 – 10 080 |
| pg_qs.index_recommendations | Active ou désactive les recommandations d’index. pg_qs.query_capture_mode doit également être « TOP » ou « ALL ». | arrêt | désactivé, recommandé |
| pg_qs.track_utility | Définit si les commandes utilitaires sont suivies par pg_qs. | actif | on, off |
(*) Paramètre statique du serveur qui nécessite un redémarrage du serveur pour qu’une modification de sa valeur prenne effet.
Les options suivantes s’appliquent spécifiquement aux statistiques d’attente :
| Paramètre | Description | Par défaut | Plage |
|---|---|---|---|
| pgms_wait_sampling.query_capture_mode | Sélectionne les instructions suivies par l’extension pgms_wait_sampling. | Aucune | none, all |
| Pgms_wait_sampling.history_period | Définit la fréquence, en millisecondes, à laquelle les événements d’attente sont échantillonnés. | 100 | 1 - 600 000 |
Notes
pg_qs.query_capture_mode remplace pgms_wait_sampling.query_capture_mode. Si pg_qs.query_capture_mode a la valeur NONE, le paramètre pgms_wait_sampling.query_capture_mode n’a aucun effet.
Utilisez le portail Azure pour obtenir ou définir une valeur différente pour un paramètre.
Vues et fonctions
Affichez et gérez le Magasin des requêtes à l’aide des fonctions et vues suivantes. Quiconque dans le rôle public PostgreSQL peut utiliser ces vues pour afficher les données du Magasin des requêtes. Ces vues sont disponibles uniquement dans la base de données azure_sys.
Les requêtes sont normalisées en examinant leur structure et en ignorant tout ce qui n’est pas sémantiquement significatif, comme les littéraux, les constantes, les alias ou les différences de casse.
Si deux requêtes sont sémantiquement identiques, même si elles utilisent des alias différents pour les mêmes colonnes et tables référencées, elles sont identifiées par le même query_id. Si deux requêtes diffèrent uniquement dans les valeurs littérales utilisées, elles sont également identifiées avec la même query_id. Pour toutes les requêtes identifiées avec le même query_id, leur sql_query_text sera celle de la requête exécutée en premier depuis le démarrage de l’activité d’enregistrement du Magasin des requêtes, ou depuis la dernière fois que les données persistantes ont été ignorées, car la fonction query_store.qs_reset a été exécutée.
Fonctionnement de la normalisation des requêtes
Voici quelques exemples pour essayer d’illustrer le fonctionnement de cette normalisation :
Supposons que vous créiez une table avec l’instruction suivante :
create table tableOne (columnOne int, columnTwo int);
Vous activez la collecte de données du Magasin des requêtes et un ou plusieurs utilisateurs exécutent les requêtes suivantes, dans l’ordre exact :
select * from tableOne;
select columnOne, columnTwo from tableOne;
select columnOne as c1, columnTwo as c2 from tableOne as t1;
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one";
Toutes les requêtes précédentes partagent les mêmes query_id. Et le texte conservé par le Magasin des requêtes est celui de la première requête exécutée après l’activation de la collecte de données. Par conséquent, il serait select * from tableOne;.
L’ensemble de requêtes suivant, une fois normalisé, ne correspond pas au jeu de requêtes précédent, car la clause WHERE les rend sémantiquement différentes :
select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
select * from tableOne where columnOne = -3 and columnTwo = -3;
select columnOne, columnTwo from tableOne where columnOne = '5' and columnTwo = '5';
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = 7 and columnTwo = 7;
Toutefois, toutes les requêtes de ce dernier jeu partagent les mêmes query_id et le texte utilisé pour les identifier tous est celui de la première requête dans le lot select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;.
Enfin, recherchez ci-dessous certaines requêtes qui ne correspondent pas aux query_id de celles du lot précédent, et la raison pour laquelle elles ne correspondent pas :
Requête :
select columnTwo as c2, columnOne as c1 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
Raison de ne pas correspondre : la liste des colonnes fait référence aux deux mêmes colonnes (columnOne et ColumnTwo), mais l’ordre dans lequel ils sont référencés est inversé, de columnOne, ColumnTwo dans le lot précédent à ColumnTwo, columnOne dans cette requête.
Requête :
select * from tableOne where columnTwo = 25 and columnOne = 25;
Raison de ne pas correspondre : Ordre dans lequel les expressions évaluées dans la clause WHERE sont référencées sont inversées de columnOne = ? and ColumnTwo = ? dans le lot précédent pour ColumnTwo = ? and columnOne = ? dans cette requête.
Requête :
select abs(columnOne), columnTwo from tableOne where columnOne = 12 and columnTwo = 21;
Raison de ne pas correspondre : la première expression de la liste de colonnes n’est plus columnOne, mais la fonction abs évaluée sur columnOne (abs(columnOne)), qui n’est pas sémantiquement équivalente.
Requête :
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = ceiling(16) and columnTwo = 16;
Raison de ne pas correspondre : la première expression de la clause WHERE n’évalue plus l’égalité de columnOne avec un littéral, mais avec le résultat de la fonction ceiling évaluée sur un littéral, ce qui n’est pas sémantiquement équivalent.
Vues
query_store.qs_view
Cette vue retourne toutes les données qui ont déjà été conservées dans les tables de prise en charge du Magasin des requêtes. Les données enregistrées en mémoire pour la fenêtre de temps actuellement active ne sont pas visibles tant que la fenêtre de temps n’est pas terminée et que ses données volatiles en mémoire sont collectées et conservées sur des tables stockées sur le disque. Cette vue retourne une ligne différente pour chaque base de données distincte (db_id), l’utilisateur(-trice) (user_id) et la requête (query_id).
| Nom | Type | Informations de référence | Description |
|---|---|---|---|
| runtime_stats_entry_id | bigint | ID de la table runtime_stats_entries. | |
| user_id | oid | pg_authid.oid | OID de l’utilisateur(-trice) qui a exécuté l’instruction. |
| db_id | oid | pg_database.oid | OID de la base de données dans laquelle l’instruction a été exécutée. |
| query_id | bigint | Code de hachage interne, calculé à partir de l’arborescence d’analyse de l’instruction. | |
| query_sql_text | varchar(10000) | Texte d’une instruction représentative. Différentes requêtes ayant la même structure sont regroupées en clusters ; ce texte est le texte de la première des requêtes du cluster. La valeur par défaut de la longueur maximale du texte de la requête est de 6000, et peut être modifiée à l’aide du paramètre du Magasin des requêtes pg_qs.max_query_text_length. Si le texte de la requête dépasse cette valeur maximale, il est tronqué au premier pg_qs.max_query_text_length caractères. |
|
| plan_id | bigint | ID du plan correspondant à cette requête. | |
| start_time | timestamp | Les requêtes sont agrégées par fenêtres de temps, dont l’intervalle de temps est défini par le paramètre de serveur pg_qs.interval_length_minutes (la valeur par défaut est de 15 minutes). Il s’agit de l’heure de début correspondant à la fenêtre temporelle de cette entrée. |
|
| end_time | timestamp | Heure de fin correspondant à la fenêtre de temps pour cette entrée. | |
| calls | bigint | Nombre de fois où la requête a été exécutée dans cette fenêtre temporelle. Notez que pour les requêtes parallèles, le nombre d’appels pour chaque exécution correspond à 1 pour le processus backend pilotant l’exécution de la requête, plus autant d’autres unités pour chaque processus Worker backend, lancé pour collaborer à l’exécution des branches parallèles de l’arborescence d’exécution. | |
| total_time | double précision | Durée totale d’exécution de la requête, en millisecondes. | |
| min_time | double précision | Durée minimale d’exécution de la requête, en millisecondes. | |
| max_time | double précision | Durée maximale d’exécution de la requête, en millisecondes. | |
| mean_time | double précision | Durée moyenne d’exécution de la requête, en millisecondes. | |
| stddev_time | double précision | Écart type de la durée d’exécution de la requête, en millisecondes. | |
| rows | bigint | Nombre total de lignes récupérées ou affectées par l’instruction. Notez que pour les requêtes parallèles, le nombre de lignes pour chaque exécution correspond au nombre de lignes renvoyées au client par le processus backend pilotant l’exécution de la requête, plus la somme de toutes les lignes que chaque processus Worker backend, lancé pour collaborer à l’exécution des branches parallèles de l’arborescence, renvoie au processus backend pilotant l’exécution. | |
| shared_blks_hit | bigint | Nombre total d’accès au cache de blocs partagés par l’instruction. | |
| shared_blks_read | bigint | Nombre total de blocs partagés lus par l’instruction. | |
| shared_blks_dirtied | bigint | Nombre total de blocs partagés modifiés par l’instruction. | |
| shared_blks_written | bigint | Nombre total de blocs partagés écrits par l’instruction. | |
| local_blks_hit | bigint | Nombre total d’accès au cache de blocs locaux par l’instruction. | |
| local_blks_read | bigint | Nombre total de blocs locaux lus par l’instruction. | |
| local_blks_dirtied | bigint | Nombre total de blocs locaux modifiés par l’instruction. | |
| local_blks_written | bigint | Nombre total de blocs locaux écrits par l’instruction. | |
| temp_blks_read | bigint | Nombre total de blocs temporaires lus par l’instruction. | |
| temp_blks_written | bigint | Nombre total de blocs temporaires écrits par l’instruction. | |
| blk_read_time | double précision | Durée totale passée par l’instruction à lire des blocs, en millisecondes (si track_io_timing est activé ; sinon, zéro). | |
| blk_write_time | double précision | Durée totale passée par l’instruction à écrire des blocs, en millisecondes (si track_io_timing est activé ; sinon, zéro). | |
| is_system_query | booléen | Détermine si la requête a été exécutée par rôle avec user_id = 10 (azuresu), qui a des privilèges de superutilisateur(-trice) et est utilisée pour effectuer des opérations de volet de contrôle. Étant donné que ce service est un service PaaS managé, seule Microsoft fait partie du rôle de super-utilisateur(-trice). | |
| query_type | texte | Type d’opération représenté par la requête. Les valeurs possibles sont les suivantes : unknown, select, update, insert, delete, merge, utility, nothing, undefined. |
query_store.query_texts_view
Cette vue retourne les données du texte des requêtes du Magasin des requêtes. Il y a une ligne pour chaque query_sql_text distinct.
| Nom | Type | Description |
|---|---|---|
| query_text_id | bigint | ID de la table query_texts |
| query_sql_text | varchar(10000) | Texte d’une instruction représentative. Différentes requêtes ayant la même structure sont regroupées en clusters ; ce texte est le texte de la première des requêtes du cluster. |
| query_type | smallint | Type d’opération représenté par la requête. Dans la version de PostgreSQL <= 14, les valeurs possibles sont 0 (inconnu), 1 (sélectionner), 2 (mise à jour), 3 (insérer), 4 (supprimer), 5 (utilitaire), 6 (rien). Dans la version de PostgreSQL >= 15, les valeurs possibles sont 0 (inconnu), 1 (sélectionner), 2 (mise à jour), 3 (insérer), 4 (supprimer), 5 (fusionner), 6 (utilitaire), 7 (rien). |
query_store.pgms_wait_sampling_view
Cette vue retourne les données des événements d’attente du Magasin des requêtes. Cette vue renvoie une ligne différente pour chaque base de données (db_id), utilisateur(-trice) (user_id), requête (query_id) et événement (event).
| Nom | Type | Informations de référence | Description |
|---|---|---|---|
| start_time | timestamp | Les requêtes sont agrégées par fenêtres de temps, dont l’intervalle de temps est défini par le paramètre de serveur pg_qs.interval_length_minutes (la valeur par défaut est de 15 minutes). Il s’agit de l’heure de début correspondant à la fenêtre temporelle de cette entrée. |
|
| end_time | timestamp | Heure de fin correspondant à la fenêtre de temps pour cette entrée. | |
| user_id | oid | pg_authid.oid | OID de l’utilisateur(-trice) qui a exécuté l’instruction. |
| db_id | oid | pg_database.oid | OID de la base de données dans laquelle l’instruction a été exécutée. |
| query_id | bigint | Code de hachage interne, calculé à partir de l’arborescence d’analyse de l’instruction. | |
| event_type | texte | Type d’événement pour lequel le backend est en attente. | |
| événement | texte | Nom de l’événement d’attente si le backend est actuellement en attente. | |
| calls | entier | Nombre de fois où le même événement a été capturé. |
Remarque
Pour obtenir la liste des valeurs possibles dans l’événement event_type et colonnes de l’événementquery_store.pgms_wait_sampling_view , reportez-vous à la documentation officielle de pg_stat_activity et recherchez les informations faisant référence aux colonnes portant les mêmes noms.
query_store.query_plans_view
Cette vue retourne le plan de requête utilisé pour exécuter une requête. Il y a une ligne par ID de base de données distinct, ID d’utilisateur et ID de requête. Cela permet de stocker uniquement les plans de requête pour les requêtes sans utilitaire.
| plan_id | db_id | query_id | plan_text |
|---|---|---|---|
| plan_id | bigint | Valeur de hachage du plan de requête normalisé produit par EXPLAIN. Il est considéré comme normalisé, car il exclut les coûts estimés des nœuds de plan et l’utilisation des mémoires tampons. | |
| db_id | oid | pg_database.oid | OID de la base de données dans laquelle l’instruction a été exécutée. |
| query_id | bigint | Code de hachage interne, calculé à partir de l’arborescence d’analyse de l’instruction. | |
| plan_text | varchar(10000) | Plan d’exécution de l’instruction donnée costs=false, buffers=false, and format=false. Il s’agit de la même sortie fournie par EXPLAIN. |
Functions
query_store.qs_reset
Cette fonction ignore toutes les statistiques collectées jusqu’à présent par le Magasin des requêtes. Il ignore à la fois les statistiques des fenêtres temporelles déjà fermées, qui ont été conservées dans des tables sur disque, et celles de la fenêtre temporelle en cours, qui sont toujours conservées en mémoire. Cette fonction ne peut être exécutée que par le rôle d’administrateur du serveur (azure_pg_admin).
query_store.staging_data_reset
Cette fonction supprime toutes les statistiques collectées en mémoire par Magasin des requêtes (c’est-à-dire les données en mémoire qui n’ont pas encore été transférées dans les tables sur disque assurant la persistance des données collectées pour Magasin des requêtes). Cette fonction ne peut être exécutée que par le rôle d’administrateur du serveur (azure_pg_admin).
Limitations et problèmes connus
Compatibilité entre Stockage Azure et Magasin des requêtes
En raison de problèmes de compatibilité, les extensions Magasin des requêtes et Stockage Azure ne peuvent pas être activées simultanément. Pour garantir un bon fonctionnement et éviter d’éventuels conflits, n’activez qu’une seule de ces extensions à la fois.
Pour utiliser Stockage Azure :
- Désactivez Magasin des requêtes en définissant le paramètre
pg_qs.query_capture_modesurNONE. Ce paramètre est dynamique, aucun redémarrage n’est nécessaire.
Pour utiliser Magasin des requêtes :
- Désactivez l’extension Stockage Azure en émettant
DROP EXTENSION azure_storage;. - Supprimez Stockage Azure de
shared_preload_libraries. - Redémarrez votre serveur de base de données.
Ces étapes sont nécessaires pour éviter les conflits et garantir le bon fonctionnement de votre système. Nous nous efforçons de résoudre ces problèmes de compatibilité. Nous allons vous tenir informés des futures mises à jour.
Mode Lecture seule
Lorsqu’une instance de serveur flexible Azure Database pour PostgreSQL est en mode lecture seule, par exemple lorsque le paramètre default_transaction_read_only est défini sur on, ou si le mode lecture seule est automatiquement activé en raison d’atteindre la capacité de stockage, le Magasin des requêtes ne capture aucune donnée.