Magasin des requêtes dans Azure Database pour PostgreSQL - Serveur flexible

Le magasin de requêtes est une fonctionnalité d’un serveur flexible Azure Database pour PostgreSQL qui permet de suivre les performances des requêtes au fil du temps. Le magasin de requêtes simplifie la résolution des problèmes de performances en vous aidant à trouver rapidement les requêtes les plus longues en cours d’exécution et la plupart des requêtes gourmandes en 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, bases de données et requêtes sont stockées dans une base de données nommée azure_sys dans le serveur flexible Azure Database pour PostgreSQL.

Activer le magasin des requêtes

Query Store est disponible sans frais supplémentaires. Il s’agit d’une fonctionnalité d’opt-in. Elle n’est donc pas activée par défaut sur un serveur. Vous pouvez activer ou désactiver le magasin de requêtes globalement pour toutes les bases de données sur un serveur donné. Vous ne pouvez pas l’activer ou le désactiver par base de données.

Important

N’activez pas Query Store sur le niveau tarifaire Burstable, car cela entraîne des problèmes de performances.

Activer le magasin des requêtes sur le portail Azure

  1. Connectez-vous au portail Azure et sélectionnez votre serveur flexible Azure Database pour PostgreSQL.
  2. Sélectionnez Paramètres dans la section Paramètres du menu.
  3. Recherchez le paramètre pg_qs.query_capture_mode.
  4. Définissez la valeur top sur ou all, selon que vous souhaitez effectuer le suivi des requêtes de niveau supérieur ou également des requêtes imbriquées (celles qui s’exécutent à l’intérieur d’une fonction ou d’une procédure), puis sélectionnez Enregistrer. Patientez jusqu’à 20 minutes pour que le premier lot de données persiste dans la base de données azure_sys.

Activer l’échantillonnage d’attente du magasin des requêtes

  1. Recherchez le paramètre pgms_wait_sampling.query_capture_mode.
  2. Définissez la valeur sur all et 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 comprennent :

  • 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 plusieurs fenêtres de temps pour voir les variations importantes.
  • 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. Vous pouvez interroger les informations dans ces magasins à l’aide de vues.

Accéder aux informations du magasin des requêtes

Le serveur flexible Azure Database pour PostgreSQL stocke les données du magasin de requêtes dans la base de données azure_sys. La requête suivante retourne des informations sur les requêtes enregistrées par le magasin de requêtes :

SELECT * FROM  query_store.qs_view;

Cette requête retourne également des informations sur 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 regroupent différents événements d’attente en compartiments en fonction de la similarité. 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. Lorsque vous mettez en corrélation ces informations d’attente avec les statistiques du runtime de requête, vous obtenez une compréhension plus approfondie de ce qui contribue aux caractéristiques de performances des requêtes.

Voici quelques exemples de la façon dont vous pouvez obtenir plus d’informations sur votre charge de travail à l’aide des statistiques d’attente dans Magasin des requêtes :

Observation Action
Délais d’attente de verrouillage élevés Vérifiez les textes de requêtes pour les requêtes affectées et identifiez les entités cibles. Dans Magasin des requêtes, recherchez d’autres requêtes qui sont exécutées fréquemment, ont une durée d’exécution élevée et modifient la même entité. 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 qui présentent un grand nombre de lectures physiques dans Magasin des requêtes. Si elles correspondent à des requêtes présentant une attente d’E/S élevée, envisagez d’activer la fonctionnalité de réglage autonome pour voir si elle peut recommander la création d’index susceptibles de diminuer le nombre de lectures physiques pour ces requêtes.
Attentes de mémoire élevée 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.

Options de configuration

Lorsque vous activez le magasin de requêtes, il enregistre les données dans les fenêtres d’agrégation. La longueur de ces fenêtres est déterminée par le paramètre pg_qs.interval_length_minutes , qui est défini par défaut sur 15 minutes. Pour chaque fenêtre, le magasin de requêtes stocke jusqu’à 500 requêtes distinctes. Les attributs qui distinguent l’unicité de chaque requête sont user_id (identificateur de l’utilisateur qui exécute la requête), db_id (identificateur de la base de données dans laquelle la requête s’exécute) et query_id (valeur entière identifiant de manière unique la requête exécutée). Si le nombre de requêtes distinctes atteint 500 pendant l’intervalle configuré, le magasin de requêtes désalloue 5% des requêtes enregistrées pour rendre plus d’espace. Les requêtes désallouées en premier sont celles qui ont été exécutées le moins de fois.

Pour configurer Magasin des requêtes paramètres, utilisez les options suivantes :

Paramètre Description Par défaut Plage
pg_qs.interval_length_minutes Intervalle de capture du magasin des requêtes (en minutes). Définit la fréquence de persistance des données. 15 1 - 30
pg_qs.max_captured_queries Nombre maximal de requêtes que le magasin de requêtes conserve parmi toutes les requêtes enregistrées au cours de chaque intervalle de capture. 500 100 - 500
pg_qs.max_plan_size Nombre maximal d’octets que Query Store enregistre à partir du texte du plan de requête. Les plans plus longs sont tronqués. 7500 100 - 10000
pg_qs.max_query_text_length Longueur maximale de requête que le magasin de requêtes peut enregistrer. Les requêtes plus longues sont tronquées. 6000 100 - 10000
pg_qs.parameters_capture_mode Indique si et quand capturer les paramètres positionnels de requête. capture_parameterless_only capture_parameterless_only, capture_first_sample
pg_qs.query_capture_mode Instructions à suivre. none none, topall
pg_qs.retention_period_in_days Fenêtre de période de rétention en jours pour le magasin des requêtes. Les données plus anciennes sont automatiquement supprimées. 7 1 - 30
pg_qs.store_query_plans Si Query Store doit enregistrer les plans de requête. off on, off
pg_qs.track_utility Indique si le magasin des requêtes doit suivre les commandes utilitaires. on on, off

Note

Si vous modifiez la valeur du pg_qs.max_query_text_length paramètre, le texte de toutes les requêtes capturées par le magasin de requêtes avant d’apporter la modification continue d’utiliser les mêmes query_id et sql_query_text. Ce comportement peut donner l’impression que la nouvelle valeur n’a pas pris effet, mais, pour les requêtes que le magasin de requêtes n’a pas enregistrées auparavant, vous voyez que le texte de la requête utilise la longueur maximale nouvellement configurée. Ce comportement est par conception et est expliqué dans vues et fonctions. Si vous exécutez query_store.qs_reset, il supprime toutes les informations enregistrées par le magasin de requêtes jusqu’à présent, y compris le texte qu’il a capturé pour chaque ID de requête. Si l’une de ces requêtes est réexécutée, la longueur maximale nouvellement configurée est appliquée au texte capturé.

Les options suivantes s’appliquent spécifiquement aux statistiques d’attente :

Paramètre Description Par défaut Plage
pgms_wait_sampling.history_period Fréquence, en millisecondes, à laquelle les événements d’attente sont échantillonnés. 100 1 - 600000
pgms_wait_sampling.query_capture_mode Les instructions que l’extension pgms_wait_sampling doit suivre. none none, all

Note

pg_qs.query_capture_mode est prioritaire sur pgms_wait_sampling.query_capture_mode. Si pg_qs.query_capture_mode est none, le paramètre pgms_wait_sampling.query_capture_mode n’a pas d’effet.

Utilisez le portail Azure pour obtenir ou définir une valeur différente pour un paramètre.

Vues et fonctions

Vous pouvez interroger les informations enregistrées par le magasin de requêtes et les supprimer à l’aide de vues et de fonctions disponibles dans le query_store schéma de la azure_sys base de données. 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 les requêtes identifiées avec le même query_id, leur sql_query_text est celui 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 à l’exécution de la fonction query_store.qs_reset.

Fonctionnement de la normalisation des requêtes

Les exemples suivants illustrent le fonctionnement de la normalisation des requêtes :

Supposons que vous créez une table à l’aide de l’instruction suivante :

create table tableOne (columnOne int, columnTwo int);

Vous activez Magasin des requêtes collecte de données, et un ou plusieurs utilisateurs exécutent les requêtes suivantes dans cet 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 le même ID de requête. Magasin des requêtes conserve le texte de la première requête qui s’exécute après avoir activé la collecte de données. Par conséquent, le texte est 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 ensemble partagent le même identifiant de requête. Le texte qui les identifie tous est le texte 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, les requêtes suivantes ne correspondent pas à l’ID de requête des requêtes dans le lot précédent. La raison pour laquelle elles ne correspondent pas est expliquée dans la liste suivante :

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 est inversé. L’ordre passe 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 : l’ordre dans lequel les expressions de la clause WHERE sont évaluées est inversé. L’ordre passe de columnOne = ? and ColumnTwo = ? dans le lot précédent à 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.

Points de vue

query_store.qs_view

Cette vue renvoie toutes les données que le magasin des requêtes conserve dans ses tables sous-jacentes. Les données que le magasin de requêtes enregistre toujours en mémoire pour la fenêtre de temps actuellement active ne sont pas visibles tant que la fenêtre de temps ne se termine pas et que les 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 (user_id) et la requête (query_id).

Nom Type References Description
runtime_stats_entry_id bigint ID de la table runtime_stats_entries.
user_id oid pg_authid.oid OID de l’utilisateur 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 avec la même structure sont regroupées. 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 requête est de 6 000, et vous pouvez la modifier à l’aide du paramètre pg_qs.max_query_text_lengthde magasin de requêtes. Si le texte de la requête dépasse cette valeur maximale, il est tronqué après les pg_qs.max_query_text_length premiers octets.
plan_id bigint ID du plan correspondant à cette requête.
start_time horodatage Les requêtes sont agrégées par fenêtres de temps. Le paramètre pg_qs.interval_length_minutes définit l’intervalle de temps de ces fenêtres (la valeur par défaut est de 15 minutes). Cette colonne correspond à l’heure de début de la fenêtre dans laquelle cette entrée a été enregistrée.
end_time horodatage 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. Pour les requêtes parallèles, le nombre d’appels pour chaque exécution correspond à 1 pour le processus back-end qui pilote l’exécution de la requête, plus autant d’unités supplémentaires que de processus de travail back-end lancés 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. Pour les requêtes parallèles, le nombre de lignes pour chaque exécution correspond au nombre de lignes retournées au client par le processus principal qui pilote l’exécution de la requête, ainsi que la somme de toutes les lignes que chaque processus de travail principal, lancées pour collaborer à l’exécution des branches parallèles de l’arborescence d’exécution, retourne au processus principal qui pilote l’exécution de la requête.
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 boolean Détermine si le rôle avec user_id = 10 (azuresu) a exécuté la requête. Cet utilisateur dispose de privilèges de superutilisateur et est utilisé pour effectuer des opérations de plan de contrôle. Étant donné que ce service est un service PaaS managé, seule Microsoft fait partie du rôle de superutilisateur.
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.
search_path texte Valeur de search_path définie au moment de la capture de la requête.
query_parameters texte Représentation textuelle d’un objet JSON avec les valeurs passées aux paramètres positionnels d’une requête paramétrisée. Cette colonne remplit uniquement sa valeur dans deux cas : 1) pour les requêtes non paramétrisées. 2) Pour les requêtes paramétrisées, lorsque pg_qs.parameters_capture_mode est défini sur capture_first_sample, et si le magasin des requêtes peut extraire les valeurs des paramètres de la requête au moment de l’exécution.
parameters_capture_status texte Type d’opération représenté par la requête. Les valeurs possibles sont succeeded (soit la requête n’était pas paramétrée, soit il s’agissait d’une requête paramétrée et les valeurs ont été correctement capturées), disabled (la requête a été paramétrée, mais les paramètres n’ont pas été capturés, car pg_qs.parameters_capture_mode était défini sur capture_parameterless_only), too_long_to_capture (la requête a été paramétrée, mais les paramètres n’ont pas été capturés, car la longueur du JSON résultant qui serait exposé dans la colonne query_parameters de cette vue a été jugée excessivement longue pour être conservée par le Query Store), too_many_to_capture (la requête a été paramétrée, mais les paramètres n’ont pas été capturés, car le nombre total de paramètres a été jugé excessif pour être conservé par le Query Store), serialization_failed (la requête a été paramétrée, mais au moins l’une des valeurs transmises comme paramètre n’a pas pu être sérialisée sous forme de texte).

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 avec la même structure sont regroupées. 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 les versions de PostgreSQL <= 14, les valeurs possibles sont 0 (inconnues), (sélectionner), 1 (mettre à jour), 2 (insérer), 3 (supprimer), 45 (utilitaire), 6 (rien). Dans les versions de PostgreSQL >= 15, les valeurs possibles sont 0 (inconnues), (sélection), 1 (mise à jour), 2 (insertion), 3 (suppression), 4 (fusion), 56 (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 (user_id), requête (query_id) et événement (event).

Nom Type References Description
start_time horodatage Les requêtes sont agrégées par fenêtres de temps. Le paramètre pg_qs.interval_length_minutes définit l’intervalle de temps de ces fenêtres (la valeur par défaut est de 15 minutes). Cette colonne correspond à l’heure de début de la fenêtre dans laquelle cette entrée a été enregistrée.
end_time horodatage Heure de fin correspondant à la fenêtre de temps pour cette entrée.
user_id oid pg_authid.oid OID de l’utilisateur 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.
event 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é.

Note

Pour obtenir la liste des valeurs possibles dans les colonnes event_type et event de la vue query_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. Le magasin des requêtes enregistre uniquement les plans de requête pour les requêtes non utilitaires.

Nom Type References Description
plan_id bigint Valeur de hachage du plan de requête normalisé produit par EXPLAIN. Il est sous forme normalisée, 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, et format=false. Sortie identique à celle produite par EXPLAIN.

Functions

query_store.qs_reset

Cette fonction supprime toutes les statistiques que le Query Store recueille. Il ignore les statistiques pour les fenêtres de temps déjà fermées, qui sont déjà conservées sur des tables sur disque. Il ignore également les statistiques de la fenêtre de temps actuelle, qui existent uniquement en mémoire. Seuls les membres disposant du rôle administrateur de serveur (azure_pg_admin) peuvent exécuter cette fonction.

query_store.staging_data_reset

Cette fonction supprime toutes les statistiques collectées en mémoire par Query Store. Ces données ne sont pas encore écrites dans les tables stockées sur disque qui assurent la persistance des données collectées pour Query Store. Seuls les membres disposant du rôle administrateur de serveur (azure_pg_admin) peuvent exécuter cette fonction.

Mode Lecture seule

Lorsqu’un serveur flexible Azure Database pour PostgreSQL est en mode lecture seule, par exemple lorsque le paramètre est défini default_transaction_read_onlysur , ou si le mode lecture seule est on, le magasin de requêtes ne capture aucune donnée.

L’activation de Query Store sur un serveur qui possède des réplicas en lecture n’active pas automatiquement Query Store sur aucune des réplicas en lecture. Même si vous l’activez sur l’un des réplicas en lecture, Query Store n’enregistre pas les requêtes exécutées sur les réplicas en lecture. Les réplicas de lecture fonctionnent en mode lecture seule jusqu’à ce que vous les promouviez en instance principale.

Contenu connexe

  • Last updated on