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 conserve aussi la trace du nombre total de lignes dans chaque table ainsi que des informations sur les VACUUM et les ANALYZE pour chaque table. Il peut aussi compter le nombre d'appels aux fonctions définies par l'utilisateur ainsi que le temps total dépensé par chacune.
PostgreSQL™ supporte aussi la détermination de la commande exacte en cours d'exécution par les autres processus serveur. Cette fonctionnalité indépendante ne dépend pas du récupérateur 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 Chapitre 18, Configuration du serveur pour plus de détails sur leur initialisation).
Le paramètre track_activities active la collecte d'informations sur la commande en cours d'exécution pour n'importe quel processus serveur.
Le paramètre track_counts contrôle si les statistiques sont récupérées pour les accès aux tables et index.
Le paramètre track_functions active le calcul de statistiques sur l'utilisation des fonctions définies par l'utilisateur.
Le paramètre track_io_timing active la collecte des temps de lecture et d'écriture de blocs.
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(7) (pour empêcher les utilisateurs ordinaires de cacher leur activité à l'administrateur, seuls les superutilisateurs sont autorisés à modifier ces paramètres avec SET).
Le collecteur de statistiques transmets l'information récupérée aux autres processus PostgreSQL™ à travers des fichiers temporaires. Ces fichiers sont stockés dans le répertoire défini par le paramètre stats_temp_directory, par défaut pg_stat_tmp. Pour de meilleures performances, stats_temp_directory peut pointer vers un disque en RAM, diminuant ainsi les besoins en entrées/sorties physique. Quand le serveur s'arrête, une copie permanente des données statistiques est stockée dans le sous-répertoire global, pour que les statistiques puissent être conservées après un arrêt du serveur.
Une transaction peut aussi voir des statistiques propres à son activité (qui ne sont pas encore transmises au collecteur) dans les vues pg_stat_xact_all_tables, pg_stat_xact_sys_tables, pg_stat_xact_user_tables et pg_stat_xact_user_functions. Ces informations se mettent à jour en continue pendant l'exécution de la transaction.
Plusieurs vues prédéfinies, listées dans le Tableau 27.1, « Vues statistiques standards », sont disponibles pour afficher les résultats de la récupération de statistiques. Autrement, vous pouvez construire des vues personnalisées en utilisant les fonctions statistiques existantes, comme discuté dans Section 27.1, « Fonctions Statistiques ».
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 les nouvelles statistiques 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 un nouveau rapport une fois par PGSTAT_STAT_INTERVAL millisecondes (500 millisecondes, sauf si cette valeur a été modifiée lors de la construction du serveur). Donc, les totaux affichés sont bien derrière l'activité réelle. Néanmoins, l'information sur la requête en cours récupérée par track_activities est toujours à jour.
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 le rapport le plus récent émis par 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. De façon similaire, les informations sur les requêtes en cours, quelque soit le processus, sont récupérées quand une telle information est demandée dans une transaction, et cette même information sera affichée lors de la transaction. Donc, les statistiques afficheront des informations statiques 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. Autrement, vous pouvez appeler pg_stat_clear_snapshot(), qui annulera l'image statistique de la transaction en cours. L'utilisation suivante des informations statistiques causera la récupération d'une nouvelle image.
Tableau 27.1. Vues statistiques standards
| Nom de la vue | Description |
|---|---|
| pg_stat_activity | Une ligne par processus serveur, montrant les informations liées à l'activité courante du processus, comme l'état et la requête en cours. Voir pg_stat_activity pour plus de détails. |
| pg_stat_bgwriter | Une seule ligne, montrant les statistiques d'activité du processus d'écriture d'arrière plan. Voir pg_stat_bgwriter pour plus de détails. |
| pg_stat_database | Une ligne par base de données, montrant les statistiques globales des bases. Voir pg_stat_database pour plus de détails. |
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.
Tableau 27.2. Vue pg_stat_activity
| Colonne | Type | Description |
|---|---|---|
| datid | oid | OID de la base de données auquelle ce processus serveur est connecté |
| datname | name | Nom de la base de données auquelle ce processus serveur est connecté |
| pid | integer | Identifiant du processus serveur |
| usesysid | oid | OID de l'utilisateur connecté à ce processus serveur |
| usename | name | Nom de l'utilisateur connecté à ce processus serveur |
| application_name | text | Nom de l'application connectée à ce processus serveurs |
| client_addr | inet | Adresse IP du client pour ce processus serveur. Si ce champ est vide, cela indique soit que le client est connecté via un socket Unix sur la machine serveur soit qu'il s'agit d'un processus interne tel qu'autovacuum. |
| client_hostname | text | Nom d'hôte du client connecté, comme reporté par une recherche DNS inverse sur client_addr. Ce champ ne sera rempli que pour les connexions IP, et seulement quand log_hostname est activé. |
| client_port | integer | Numéro de port TCP que le client utilise pour communiquer avec le processus serveur, ou -1 si un socket Unix est utilisé. |
| backend_start | timestamp with time zone | Heure de démarrage du processus, c'est-à-dire quand le client s'est connecté au serveur. |
| xact_start | timestamp with time zone | Heure de démarrage de la transaction courante du processus, ou NULL si aucune transaction n'est active. Si la requête courante est la première de sa transaction, cette colonne a la même valeur que la colonne query_start. |
| query_start | timestamp with time zone | Heure à laquelle la requête active a été démarrée, ou si state ne vaut pas active, quand la dernière requête a été lancée. |
| state_change | timestamp with time zone | Heure à laquelle l'état (state) a été modifié en dernier |
| waiting | boolean | Vrai si le processus serveur est actuellement en attente d'un verrou |
| state | text |
État général du processus serveur. Les valeurs possibles
sont:
|
| query | text | Texte de la requête la plus récente pour ce processus serveur. Si state vaut active, alors ce champ affiche la requête en cours d'exécution. Dans tous les autres cas, il affichera la dernière requête à avoir été exécutée. |
La vue pg_stat_activity aura une ligne par processus serveur, montrant des informations liées à l'activité courante de ce processus.
![[Note]](images/note.png)
Les colonnes waiting et state sont indépendantes. Si un processus serveur est dans l'état active, le champ waiting peut être vrai ou faux. Si l'état vaut active et que waiting est vrai, cela signifie que la requête est exécutée, mais qu'elle est bloquée par un verrou quelque part sur le système.
Tableau 27.3. Vue pg_stat_bgwriter
| Colonne | Type | Description |
|---|---|---|
| checkpoints_timed | bigint | Nombre de checkpoints planifiés ayant été effectués |
| checkpoints_req | bigint | Nombre de checkpoints demandés ayant été effectués |
| checkpoint_write_time | double precision | Temps total passé dans la partie des checkpoints où les fichiers sont écrits sur disque, en millisecondes. |
| checkpoint_sync_time | double precision | Temps total passé dans la partie des checkpoints où les fichiers sont synchronisés sur le disque, en millisecondes. |
| buffers_checkpoint | bigint | Nombre de tampons écrits durant des checkpoints |
| buffers_clean | bigint | Nombre de tampons écrits par le processus background writer (processus d'écriture en tâche de fond) |
| maxwritten_clean | bigint | Nombre de fois que le processus background writer a arrêté son parcours de nettoyage pour avoir écrit trop de tampons |
| buffers_backend | bigint | Nombre de tampons écrits directement par un processus serveur |
| buffers_backend_fsync | bigint | Nombre de fois qu'un processus serveur a du exécuter son propre appel à fsync (normalement le processus background writer gère ces appels même quand le processus serveur effectue sa propre écriture) |
| buffers_alloc | bigint | Nombre de tampons alloués |
| stats_reset | timestamp with time zone | Dernière fois que ces statistiques ont été réinitialisées |
La vue pg_stat_bgwriter aura toujours une ligne unique, contenant les données globales de l'instance.
Tableau 27.4. Vue pg_stat_database
| Colonne | Type | Description |
|---|---|---|
| datid | oid | OID d'une base de données |
| datname | name | Nom de cette base de données |
| numbackends | integer | Nombre de processus serveurs actuellement connectés à cette base de données. C'est la seule colonne de cette vue qui renvoie une valeur reflétant l'état actuel ; toutes les autres colonnes renvoient les valeurs accumulées depuis la dernière réinitialisation |
| xact_commit | bigint | Nombre de transactions de cette base de données qui ont été validées |
| xact_rollback | bigint | Nombre de transactions de cette base de données qui ont été annulées |
| blks_read | bigint | Nombre de blocs disques lus dans cette base de données |
| blks_hit | bigint | Nombre de fois que des blocs disques étaient déjà dans le cache tampon, et qu'il n'a donc pas été nécessaire de les lire sur disque (cela n'inclut que les accès dans le cache tampon de PostgreSQL, pas dans le cache de fichiers du système d'exploitation). |
| tup_returned | bigint | Nombre de lignes retournées par des requêtes dans cette base de données |
| tup_fetched | bigint | Nombre de lignes rapportées par des requêtes dans cette base de données |
| tup_inserted | bigint | Nombre de lignes insérées par des requêtes dans cette base de données |
| tup_updated | bigint | Nombre de lignes mises à jour par des requêtes dans cette base de données |
| tup_deleted | bigint | Nombre de lignes supprimées par des requêtes dans cette base de données |
| conflicts | bigint | Nombre de requêtes annulées à cause de conflits avec la restauration dans cette base de données. (Les conflits n'arrivent que sur des serveurs de standby ; voir pg_stat_database_conflicts pour plus de détails.) |
| temp_files | bigint | Nombre de fichiers temporaires créés par des requêtes dans cette base de données. Tous les fichiers temporaires sont comptabilisés, quel que soit la raison de la création du fichier temporaire (par exemple, un tri ou un hachage) et quel que soit la valeur du paramètre log_temp_files. |
| temp_bytes | bigint | Quantité totale de données écrites dans des fichiers temporaires par des requêtes dans cette base de données. Tous les fichiers temporaires sont comptabilisés, quel que soit la raison de la création de ce fichier temporaire, et quel que soit la valeur du paramètre log_temp_files. |
| deadlocks | bigint | Nombre de verrous mortels détectés dans cette base de données |
| blk_read_time | double precision | Temps passé à lire des blocs de donnée dans des fichiers par des processsus serveur dans cette base de données, en millisecondes |
| blk_write_time | double precision | Temps passé à écrire des blocs de données dans des fichiers par les processus serveurs dans cette base de données, en millisecondes |
| stats_reset | timestamp with time zone | Dernière fois que ces statistiques ont été réinitialisées |
La vue pg_stat_database ne contiendra qu'une ligne pour chaque base de données dans l'instance, montrant ses statistiques globales.
Tableau 27.5. Vue pg_stat_all_tables
| Colonne | Type | Description |
|---|---|---|
| relid | oid | OID d'une table |
| schemaname | name | Nom du schéma dans lequel se trouve cette table |
| relname | name | Nom de cette table |
| seq_scan | bigint | Nombre de parcours séquentiels initiés sur cette table |
| seq_tup_read | bigint | Nombre de lignes vivantes rapportées par des parcours séquentiels |
| idx_scan | bigint | Nombre de parcours d'index initiés sur cette table |
| idx_tup_fetch | bigint | Nombre de lignes vivantes rapportées par des parcours d'index |
| n_tup_ins | bigint | Nombre de lignes insérées |
| n_tup_upd | bigint | Nombre de lignes mises à jour |
| n_tup_del | bigint | Nombre de lignes supprimées |
| n_tup_hot_upd | bigint | Nombre de lignes mises à jour par HOT (i.e., sans mises à jour d'index nécessaire) |
| n_live_tup | bigint | Nombre estimé de lignes vivantes |
| n_dead_tup | bigint | Nombre estimé de lignes mortes |
| last_vacuum | timestamp with time zone | Dernière fois qu'une opération VACUUM manuelle a été faite sur cette table (sans compter VACUUM FULL) |
| last_autovacuum | timestamp with time zone | Dernière fois que le démon autovacuum a exécuté une opération VACUUM sur cette table |
| last_analyze | timestamp with time zone | Dernière fois qu'une opération ANALYZE a été lancée manuellement sur cette table |
| last_autoanalyze | timestamp with time zone | Dernière fois que le démon autovacuum a exécuté une opération ANALYZE sur cette table |
| vacuum_count | bigint | Nombre de fois qu'une opération VACUUM manuelle a été lancée sur cette table (sans compter VACUUM FULL) |
| autovacuum_count | bigint | Nombre de fois que le démon autovacuum a exécuté une opération VACUUM manuelle |
| analyze_count | bigint | Nombre de fois qu'une opération ANALYZE manuelle a été lancée sur cette table |
| autoanalyze_count | bigint | Nombre de fois que le démon autovacuum a exécuté une opération ANALYZE sur cette table |
La vue pg_stat_all_tables contiendra une ligne par table dans la base de données courante (incluant les tables TOAST), montrant les statistiques d'accès pour cette table spécifiquement. Les vues pg_stat_user_tables et pg_stat_sys_tables contiennent les mêmes informations, mais filtrent respectivement les tables utilisateurs et les tables systèmes.
Tableau 27.6. Vue pg_stat_all_indexes
| Colonne | Type | Description |
|---|---|---|
| relid | oid | OID de la table pour cet index |
| indexrelid | oid | OID de cet index |
| schemaname | name | Nom du schéma dans lequel se trouve cet index |
| relname | name | Nom de la table pour cet index |
| indexrelname | name | Nom de cet index |
| idx_scan | bigint | Nombre de parcours d'index initiés par cet index |
| idx_tup_read | bigint | Nombre d'entrées d'index retournées par des parcours sur cet index |
| idx_tup_fetch | bigint | Nombre de lignes vivantes de la table rapportées par des simples parcours d'index utilisant cet index |
La vue pg_stat_all_indexes contiendra une ligne pour chaque index dans la base de données courante, montrant les statistiques d'accès sur cet index spécifiquement. Les vues pg_stat_user_indexes et pg_stat_sys_indexes contiennent la même information, mais sont filtrées pour ne montrer respectivement que les index utilisateurs et les index systèmes.
Les index peuvent être utilisés avec un simple parcours d'index ou un parcours d'index « bitmap ». Dans un parcours de bitmap, les sorties de plusieurs index peuvent être combinées avec des règles AND ou OR, c'est pourquoi il est difficile d'associer des lecture de lignes individuelles de la table avec des index spécifiques quand un parcours de bitmap est utilisé. Ainsi donc, un parcours de bitmap incrémente le(s) valeur(s) de pg_stat_all_indexes. idx_tup_read pour le(s) index qu'il utilise, et incrémente la valeur de pg_stat_all_tables. idx_tup_fetch pour la table, mais il n'affecte pas pg_stat_all_indexes.idx_tup_fetch.
Les valeurs de idx_tup_read et idx_tup_fetch peuvent être différentes même sans aucune utilisation de parcours de bitmap, car idx_tup_read comptabilise les entrées d'index récupérées de cet index alors que idx_tup_fetch comptabilise le nombre de lignes vivantes rapportées de la table. Le second sera moindre si des lignes mortes ou pas encore validées sont rapportées en utilisant l'index, ou si des lectures de lignes de la table sont évitées grâce a un parcours d'index seul.
Tableau 27.7. Vue pg_statio_all_tables
| Colonne | Type | Description |
|---|---|---|
| relid | oid | OID d'une table |
| schemaname | name | Nom du schéma dans lequel se trouve cette table |
| relname | name | Nom de cette table |
| heap_blks_read | bigint | Nombre de blocs disque lus pour cette table |
| heap_blks_hit | bigint | Nombre de tampons récupérés pour cette table |
| idx_blks_read | bigint | Nombre de blocs disque lus par tous les index de cette table |
| idx_blks_hit | bigint | Nombre de tampons récupérés sur tous les index de cette table |
| toast_blks_read | bigint | Nombre de blocs disque lus sur la partie TOAST de cette table (si présente) |
| toast_blks_hit | bigint | Nombre de tampons récupérés sur la partie TOAST de cette table (si présente) |
| tidx_blks_read | bigint | Nombre de blocs disque lus sur les index de la partie TOAST de cette table (si présente) |
| tidx_blks_hit | bigint | Nombre de tampons récupérés sur les index de la partie TOAST de cette table (si présente) |
La vue pg_statio_all_tables contiendra une ligne pour chaque table dans la base de données courante (en incluant les tables TOAST), montrant les statistiques d'entrées/sorties de chaque table spécifiquement. Les vues pg_statio_user_tables et pg_statio_sys_tables contiennent la même information, mais sont filtrées pour ne montrer respectivement que les tables utilisateurs et les tables systèmes.
Tableau 27.8. Vue pg_statio_all_indexes
| Colonne | Type | Description |
|---|---|---|
| relid | oid | OID de la table pour cet index |
| indexrelid | oid | OID de cet index |
| schemaname | name | Nom du schéma dans lequel se trouve cet index |
| relname | name | Nom de la table pour cet index |
| indexrelname | name | Nom de cet index |
| idx_blks_read | bigint | Nombre de blocs disque lus pour cet index |
| idx_blks_hit | bigint | Nombre de tampons récupérés sur cet index |
La vue pg_statio_all_indexes contiendra une ligne pour chaque index dans la base de données courante, montrant les statistiques d'entrées/sorties sur chaque index spécifiquement. Les vues pg_statio_user_indexes et pg_statio_sys_indexes contiennent la même information, mais sont filtrées pour ne montrer respectivement que les tables utilisateur et tables système.
Tableau 27.9. Vue pg_statio_all_sequences
| Colonne | Type | Description |
|---|---|---|
| relid | oid | OID de cette séquence |
| schemaname | name | Nom du schéma dans lequel se trouve cette séquence |
| relname | name | Nom de cette séquence |
| blks_read | bigint | Nombre de blocs disque lus pour cette séquence |
| blks_hit | bigint | Nombre de tampons récupérés pour cette séquence |
La vue pg_statio_all_sequences contiendra une ligne pour chaque séquence dans la base de données courante, montrant les statistiques d'entrées/sorties pour chaque séquence spécifiquement.
Tableau 27.10. Vue pg_stat_user_functions
| Colonne | Type | Description |
|---|---|---|
| funcid | oid | OID de cette fonction |
| schemaname | name | Nom du schéma dans lequel se trouve cette fonction |
| funcname | name | Nom de cette fonction |
| calls | bigint | Nombre de fois que cette fonction a été appelée |
| total_time | double precision | Temps total passé dans cette fonction ainsi que dans toutes les autres fonctions appelées par elle, en millisecondes |
| self_time | double precision | Temps total passé dans cette fonction seule, sans inclure les autres fonctions appelées par elle, en millisecondes |
La vue pg_stat_user_functions contiendra une ligne pour chaque fonction suivie, montrant les statistiques d'exécution de cette fonction. Le paramètre track_functions contrôle exactement quelles fonctions sont suivies.
Tableau 27.11. Vue pg_stat_replication
| Colonne | Type | Description |
|---|---|---|
| pid | integer | Identifiant du processus walsender |
| usesysid | oid | OID de l'utilisateur connecté à ce processus walsender |
| usename | name | Nom de l'utilisateur connecté à ce processus walsender |
| application_name | text | Nom de l'application connectée à ce processus walsender |
| client_addr | inet | Adresse IP du client connecté à ce processus walsender. Si ce champ est vide, cela indique que ce client est connecté par un socket Unix sur la machine serveur |
| client_hostname | text | Nom d'hôte du client connecté, comme reporté par une recherche DNS inverse sur client_addr. Ce champ ne sera renseigné que pour les connexions IP, et seulement quand le paramètre log_hostname est activé. |
| client_port | integer | Numéro de port TCP que le client utilise pour la communication avec le processus walsender, ou -1 si un socket Unix est utilisé. |
| backend_start | timestamp with time zone | Heure à laquelle ce processus a été démarré, c'est-à-dire quand le client s'est connecté à ce processus walsender. |
| state | text | État courant du processus walsender |
| sent_location | text | Dernière position du journal de transactions envoyée sur cette connexion |
| write_location | text | Dernière position du journal de transactions écrite sur disque par ce serveur de standby |
| flush_location | text | Dernière position du journal de transactions vidée sur disque par ce serveur de standby |
| replay_location | text | Dernière position du journal de transactions rejouée sur la base de données sur ce serveur de standby |
| sync_priority | integer | Priorité de ce serveur de standby pour être choisi comme le serveur de standby synchrone |
| sync_state | text | État de synchronisation de ce serveur de standby |
La vue pg_stat_replication contiendra une ligne par processus walsender, montrant les statistiques de réplication du serveur de standby sur lequel il est connecté. Seuls les serveurs de standby directement connecté sont listés ; il n'y a pas d'informations disponibles sur les serveurs de standby en aval.
Tableau 27.12. Vue pg_stat_database_conflicts
| Colonne | Type | Description |
|---|---|---|
| datid | oid | OID d'une base de données |
| datname | name | Nom de cette base de données |
| confl_tablespace | bigint | Nombre de requêtes qui ont été annulées sur cette base de données pour cause de suppression de tablespaces |
| confl_lock | bigint | Nombre de requêtes qui ont été annulées sur cette base de données pour cause de délais d'attente sur des verrous |
| confl_snapshot | bigint | Nombre de requêtes qui ont été annulées sur cette base de données pour cause d'image de la base trop vieille |
| confl_bufferpin | bigint | Nombre de requêtes qui ont été annulées sur cette base de données pour cause de tampons bloqués |
| confl_deadlock | bigint | Nombre de requêtes qui ont été annulées sur cette base de données pour cause de verrous mortels |
La vue pg_stat_database_conflicts contiendra une ligne par base de données, montrant les statistiques globales sur les annulations de requêtes se produisant à cause de conflits avec la restauration sur les serveurs de standby. Cette vue ne contiendra que des informations sur les serveurs de standby, puisque les conflits n'arrivent pas sur les serveurs maître.
Surveiller l'activité de la
base de données
