23.2. Le récupérateur de statistiques

Le récupérateur de statistiques de PostgreSQL est un sous-système qui supporte la récupération et les rapports d'informations sur l'activité du serveur. Actuellement, le récupérateur peut compter les accès aux tables et index à la fois en terme de blocs disque et de lignes individuelles. Il supporte aussi la détermination de la commande exacte en cours d'exécution par les autres processus serveur.

23.2.1. Configuration de la récupération de statistiques

Comme la récupération de statistiques ajoute un temps supplémentaire à l'exécution de la requête, le système peut être configuré pour récupérer ou non des informations. Ceci est contrôlé par les paramètres de configuration qui sont normalement initialisés dans postgresql.conf. (Voir Section 16.4 pour plus de détails sur leur initialisation.)

Le paramètre stats_start_collector doit valoir true pour que le récupérateur de statistiques soit seulement lancé. C'est la valeur par défaut et la configuration recommandée mais elle peut être désactivée si vous n'êtes pas intéressé dans les statistiques et que vous souhaitez supprimer toute activité en trop. (Néanmoins, ce que vous sauverez sera assez petit.) Notez que cette option ne peut pas être changée alors que le serveur est en cours d'exécution.

Les paramètres stats_command_string, stats_block_level et stats_row_level contrôlent la quantité d'informations réellement envoyée au récupérateur et détermine du coup le temps supplémentaire réclamé. Ils déterminent respectivement si un processus serveur envoie sa chaîne de commande actuelle, les statistiques d'accès au niveau du bloc disque et celles au niveau de la ligne vers le récupérateur. Normalement, ces paramètres sont configurés dans postgresql.conf de façon à ce qu'ils s'appliquent à tous les processus serveur mais il est possible de les activer/désactiver sur des sessions individuelles en utilisant la commande SET. (Pour empêcher les utilisateurs ordinaires de cacher leur activité à l'administrateur, seuls les superutilisateurs sont autorisés à modifier ces paramètres avec SET.)

Note : Comme les paramètres stats_command_string, stats_block_level et stats_row_level valent par défaut false, très peu de statistiques sont récupérées dans la configuration par défaut. Activer une ou plus des variables de configuration améliorera significativement le nombre de données utiles produit par le récupérateur de statistiques au prix d'une surcharge supplémentaire à l'exécution.

23.2.2. Visualiser les statistiques récupérées

Plusieurs vues prédéfinies sont disponibles pour afficher les résultats de la récupération de statistiques, listés dans Tableau 23-1. Autrement, vous pouvez construire des vues personnalisées en utilisant les fonctions statistiques existantes.

En utilisant les statistiques pour surveiller l'activité en cours, il est important de réaliser que l'information n'est pas mise à jour instantanément. Chaque processus serveur individuel transmet le nouveau nombre d'accès au récupérateur juste avant l'attente d'une nouvelle commande du client ; donc une requête toujours en cours n'affecte pas les totaux affichés. De plus, le récupérateur lui-même émet les nouveaux totaux une fois par pgstat_stat_interval millisecondes (500 par défaut). Donc, les totaux affichés sont bien derrière l'activité réelle.

Un autre point important est que, lorsqu'un processus serveur se voit demander d'afficher une des statistiques, il récupère tout d'abord les totaux les plus récents émis pas le processus de récupération, puis continue d'utiliser cette image de toutes les vues et fonctions statistiques jusqu'à la fin de sa transaction en cours. Donc, les statistiques ne sembleront pas changer tant que vous restez dans la même transaction. Ceci est une fonctionnalité, et non pas un bogue, car il vous permet de traiter plusieurs requêtes sur les statistiques et de corréler les résultats sans vous inquiéter que les nombres aient pu changer. Mais si vous voulez voir les nouveaux résultats pour chaque requête, assurez-vous de lancer les requêtes en dehors de tout bloc de transaction.

Tableau 23-1. Vues statistiques standards

Nom de la vueDescription
pg_stat_activityUne ligne par processus serveur, affichant l'ID du processus, la base de données, l'utilisateur, la requête en cours et le temps où a commencé l'exécution de la requête. Les colonnes renvoyant des données sur la requête en cours sont seulement disponibles si le paramètre stats_command_string a été activé. De plus, ces colonnes se lisent comme NULL sauf si l'utilisateur examinant cette vue est un superutilisateur ou est le propriétaire du processus en cours de rapport. (Notez qu'à cause des délais du récupérateur, la requête en cours ne sera mise à jour que pour les requêtes de longue durée.)
pg_stat_databaseUne ligne par base de données, affichant le nombre de processus serveur actifs, le nombre total de transactions validées et le nombre de celles qui ont été annulées, le nombre total de blocs disque lus et le nombre total de succès du tampon (c'est-à-dire le nombre de lectures de blocs évitées en trouvant déjà le bloc dans le cache du tampon).
pg_stat_all_tablesPour chaque table dans la base de données en cours, les nombres totaux de parcours séquentiels et indexés, le nombre total de lignes renvoyés par chaque type de parcours et le nombre total d'insertions, de mises à jour et de suppressions de lignes.
pg_stat_sys_tablesIdentique à pg_stat_all_tables, sauf que seules les tables systèmes sont affichées.
pg_stat_user_tablesIdentique à pg_stat_all_tables, sauf que seules les tables utilisateurs sont affichées.
pg_stat_all_indexesPour chaque index dans la base de données en cours, le nombre total de parcours d'index qui ont utilisé cet index, le nombre de lignes d'index lus et le nombre de lignes d'en-tête récupérées avec succès. (Ceci pourrait être moindre quand les entrées d'index pointent vers des lignes supprimées.)
pg_stat_sys_indexesIdentique à pg_stat_all_indexes, sauf que seules les tables systèmes sont affichées.
pg_stat_user_indexesIdentique à pg_stat_all_indexes, sauf que seules les tables utilisateurs sont affichées.
pg_statio_all_tablesPour chaque table de la base de données en cours, le nombre total de lecture de blocs disques à partir de cette table, le nombre de récupérations valides par les tampons, le nombre de lectures de blocs disque et le nombre de récupérations valides par les tampons dans tous les index de cette table, le nombre de lectures de blocs disque et le nombre de récupérations valides par les tampons dans tous les index à partir de la table auxiliaire TOAST (si elle existe) pour cette table, le nombre de lectures de blocs disque et le nombre de récupérations valides par les tampons pour l'index de la table TOAST.
pg_statio_sys_tablesIdentique à pg_statio_all_tables, sauf que seules les tables systèmes sont affichées.
pg_statio_user_tablesIdentique à pg_statio_all_tables, sauf que seules les tables utilisateur sont affichées.
pg_statio_all_indexesPour chaque index de la base de données en cours, les nombres de lectures de blocs disque et de récupérations valides dans cet index.
pg_statio_sys_indexesIdentique à pg_statio_all_indexes, sauf que seuls les index systèmes sont affichés.
pg_statio_user_indexesIdentique à pg_statio_all_indexes, sauf que seuls les index utilisateur sont affichés.
pg_statio_all_sequencesPour chaque objet séquence de la base de données en cours, le nombres de lectures de blocs disque et de récupérations valides de tampons dans cette séquence.
pg_statio_sys_sequencesIdentique à pg_statio_all_sequences, sauf que seules les séquences système sont affichées (actuellement, aucune séquence système n'est définie, donc cette vue est toujours vide)
pg_statio_user_sequencesIdentique à pg_statio_all_sequences, sauf que seules les séquences utilisateur sont affichées.

Les statistiques par index sont particulièrement utiles pour déterminer les index utilisés et leur efficacité.

Les vues pg_statio_ sont principalement utiles pour déterminer l'efficacité du cache tampon. Quand le nombre de lectures disques réelles est plus petit que le nombre de récupérations valides par le tampon, alors le cache satisfait la plupart des demandes de lecture sans faire appel au noyau. Néanmoins, ces statistiques ne nous donnent pas l'histoire complète : à cause de la façon dont PostgreSQL gère les entrées/sorties disque, les données qui ne sont pas dans le tampon de PostgreSQL pourraient toujours résider dans le tampon d'entrées/sorties du noyau et pourraient, du coup, être toujours récupérées sans nécessiter une lecture physique. Les utilisateurs intéressés pour obtenir des informations plus détaillées sur le comportement des entrées/sorties dans PostgreSQL sont invités à utiliser le récupérateur de statistiques de PostgreSQL avec les outils du système d'exploitation permettant une vue de la gestion des entrées/sorties par le noyau.

Il existe d'autres façons de regarder les statistiques. Cela se fait en écrivant des requêtes qui utilisent les mêmes fonctions d'accès aux statistiques que les vues standards. Ces fonctions sont listées dans le Tableau 23-2. Les fonctions d'accès par base de données prennent un OID de la base de données comme argument pour identifier la base de données du rapport. Les fonctions par table et par index prennent l'OID de la table ou de l'index (notez que seuls les tables et les index de la base de données en cours peuvent être vus par ces fonctions). Les fonctions d'accès au processus prennent le numéro d'identifiant du processus.

Tableau 23-2. Fonctions d'accès aux statistiques

FonctionCode de retourDescription
pg_stat_get_db_numbackends(oid)integer Nombre de processus actifs pour la base de données
pg_stat_get_db_xact_commit(oid)bigint Transactions validées dans la base de données
pg_stat_get_db_xact_rollback(oid)bigint Transactions annulées dans la base de données
pg_stat_get_db_blocks_fetched(oid)bigint Nombre de demandes de récupérations de blocs disque pour la base de données
pg_stat_get_db_blocks_hit(oid)bigint Nombre de demandes de récupérations de blocs disque trouvés dans le tampon pour la base de données
pg_stat_get_numscans(oid)bigint Nombre de parcours séquentiels réalisés lorsque l'argument est une table, ou nombre de parcours d'index lorsque l'argument est un index
pg_stat_get_tuples_returned(oid)bigint Nombre de lignes lues par les parcours séquentiels lorsque l'argument est une table, ou nombre de lignes d'index lues lorsque l'argument est un index
pg_stat_get_tuples_fetched(oid)bigint Nombre de lignes récupérées valides (non expirées) récupérées par des parcours séquentiels quand l'argument est une table, ou récupérées par des parcours d'index en utilisant cet index lorsque l'argument est un index
pg_stat_get_tuples_inserted(oid)bigint Nombre de lignes insérées dans la table
pg_stat_get_tuples_updated(oid)bigint Nombre de lignes mises à jour dans la table
pg_stat_get_tuples_deleted(oid)bigint Nombre de lignes supprimées dans la table
pg_stat_get_blocks_fetched(oid)bigint Nombre de demandes de récupération de blocs disques pour la table ou l'index
pg_stat_get_blocks_hit(oid)bigint Nombre de demandes de blocs disque récupérés dans le tampon pour la table ou l'index
pg_stat_get_backend_idset()set of integer Ensemble d'identifiants de processus actifs (de 1 au nombre de processus actifs). Voir l'exemple d'utilisation dans le texte.
pg_backend_pid()integer ID du processus pour le processus serveur attaché à la session en cours
pg_stat_get_backend_pid(integer)integer ID du processus pour le processus serveur donné
pg_stat_get_backend_dbid(integer)oid ID de la base de données pour le processus serveur en cours
pg_stat_get_backend_userid(integer)oid ID de l'utilisateur pour le processus serveur en cours
pg_stat_get_backend_activity(integer)text Commande active du processus serveur en cours (NULL si l'utilisateur courant n'est pas un superutilisateur ni le même utilisateur que celui de la session en cours de requêtage, ou si stats_command_string n'est pas activée)
pg_stat_get_backend_activity_start(integer)timestamp with time zone Le moment où la requête en cours de traitement a été lancée (NULL si l'utilisateur courant n'est pas un superutilisateur ou s'il ne s'agit pas du même utilisateur que celui de la session qui a lancé la requête ou si stats_command_string n'est pas actif)
pg_stat_reset()boolean Réinitialise toutes les statistiques en cours

Note : pg_stat_get_db_blocks_fetched moins pg_stat_get_db_blocks_hit donne le nombre d'appels lancés pour la table, l'index ou la base de données ; mais le nombre réel de lectures physiques est habituellement moindre à cause des tampons du noyau.

La fonction pg_stat_get_backend_idset fournit un moyen agréable de générer une ligne pour chaque processus serveur actif. Par exemple, pour afficher les PID et les requêtes en cours pour tous les processus serveur :

SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
       pg_stat_get_backend_activity(s.backendid) AS current_query
    FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;