Le module pg_stat_statements fournit un moyen de surveiller les statistiques d'exécution de tous les ordres SQL exécutés par un serveur.
Le module doit être chargé par l'ajout de pg_stat_statements à shared_preload_libraries dans le fichier de configuration postgresql.conf parce qu'il a besoin de mémoire partagée supplémentaire. Ceci signifie qu'il faut redémarrer le serveur pour ajouter ou supprimer le module.
Quand pg_stat_statements est chargé, il récupère des statistiques sur toutes les bases de données du serveur. Pour y accéder et les manipuler, le module fournit une vue, pg_stat_statements, et les fonctions pg_stat_statements_reset et pg_stat_statements. Elles ne sont pas disponibles globalement mais peuvent être activées pour une base de données spécifique avec l'instruction CREATE EXTENSION pg_stat_statements.
Les statistiques collectées par le module sont rendues disponibles par une vue nommée pg_stat_statements. Cette vue contient une ligne pour chaque identifiant de base de données, identifiant utilisateur et identifiant de requête distincts (jusqu'au nombre maximum d'ordres distincts que le module peut surveiller). Les colonnes de la vue sont affichées dans Tableau F.22, « Colonnes de pg_stat_statements ».
Tableau F.22. Colonnes de pg_stat_statements
| Nom | Type | Référence | Description |
|---|---|---|---|
| userid | oid | pg_authid.oid | OID de l'utilisateur qui a exécuté l'ordre SQL |
| dbid | oid | pg_database.oid | OID de la base de données dans laquelle l'ordre SQL a été exécuté |
| queryid | bigint | Code de hachage interne, calculé à partir de l'arbre d'analyse de la requête. | |
| query | text | Texte de l'ordre SQL représentatif | |
| calls | bigint | Nombre d'exécutions | |
| total_time | double precision | Durée d'exécution de l'instruction SQL, en millisecondes | |
| min_time | double precision | Durée minimum d'exécution de l'instruction SQL, en millisecondes | |
| max_time | double precision | Durée maximum d'exécution de l'instruction SQL, en millisecondes | |
| mean_time | double precision | Durée moyenne d'exécution de l'instruction SQL, en millisecondes | |
| stddev_time | double precision | Déviation standard de la durée d'exécution de l'instruction SQL, en millisecondes | |
| rows | bigint | Nombre total de lignes renvoyées ou affectées par l'ordre SQL | |
| shared_blks_hit | bigint | Nombre total de blocs partagés lus dans le cache par l'ordre SQL | |
| shared_blks_read | bigint | Nombre total de blocs partagés lus sur disque par l'ordre SQL | |
| shared_blks_dirtied | bigint | Nombre total de blocs partagés mis à jour par l'ordre SQL | |
| shared_blks_written | bigint | Nombre total de blocs partagés écrits sur disque par l'ordre SQL | |
| local_blks_hit | bigint | Nombre total de blocs locaux lus dans le cache par l'ordre SQL | |
| local_blks_read | bigint | Nombre total de blocs locaux lus sur disque par l'ordre SQL | |
| local_blks_dirtied | bigint | Nombre total de blocs locaux mis à jour par l'ordre SQL. | |
| local_blks_written | bigint | Nombre total de blocs locaux écrits sur disque par l'ordre SQL | |
| temp_blks_read | bigint | Nombre total de blocs temporaires lus par l'ordre SQL | |
| temp_blks_written | bigint | Nombre total de blocs temporaires écrits par l'ordre SQL | |
| blk_read_time | double precision | Durée totale du temps passé par l'ordre SQL à lire des blocs, en millisecondes (si track_io_timing est activé, sinon zéro) | |
| blk_write_time | double precision | Durée totale du temps passé par l'ordre SQL à écrire des blocs sur disque, en millisecondes (si track_io_timing est activé, sinon zéro) |
Pour raisons de sécurité, seuls les super utilisateurs et les membres du rôle pg_read_all_stats sont autorisé à voir le texte SQL ainsi que le champ queryid des requêtes exécutées par d'autres utilisateurs. Les autres utilisateurs peuvent cependant voir les statistiques, si la vue a été installée dans leur base de données.
Les requêtes qui disposent d'un plan d'exécution (c'est-à-dire SELECT, INSERT, UPDATE, et DELETE) sont combinées en une entrée unique dans pg_stat_statements lorsqu'elles ont un plan d'exécution similaire (d'après leur hachage). En substance, cela signifie que deux requêtes seront considérées comme équivalentes si elles sont sémantiquement les mêmes mais disposent de valeurs littérales différentes dans la requête. Les requêtes utilitaires (c'est-à-dire toutes les autres) ne sont considérées comme unique que lorsqu'elles sont égales au caractère près.
Quand la valeur d'une constante a été ignorée pour pouvoir comparer la requête à d'autres requêtes, la constante est remplacée par un symbole de paramètre, tel que $1, dans l'affichage de pg_stat_statements. Le reste du texte de la requête est tel qu'était la première requête ayant la valeur de hashage queryid spécifique assoiée à l'entrée dans pg_stat_statements.
Dans certains cas, les requêtes SQL avec des textes différents peuvent être fusionnés en une seule entrée pg_stat_statements. Normalement, cela n'arrive que pour les requêtes dont la sémantique est équivalente, mais il y a une petite chance que des collisions de l'algorithme de hachage aient pour conséquence la fusion de requêtes sans rapport en une entrée. (Cela ne peut cependant pas arriver pour des requêtes appartenant à des utilisateurs différents ou des bases de données différentes).
Puisque la valeur de hachage queryid est calculée sur la représentation de la requête après analyse, l'inverse est également possible : des requêtes avec un texte identique peuvent apparaître comme des entrées séparées, si elles ont des significations différentes en fonction de facteurs externes, comme des réglages de search_path différents.
Les programmes utilisant pg_stat_statements pourraient préférer utiliser queryid (peut-être en association avec dbid et userid) pour disposer d'un identifiant plus stable et plus sûr pour chaque entrée plutôt que le texte de la requête. Cependant, il est important de comprendre qu'il n'y a qu'une garantie limitée sur la stabilité de la valeur de hachage de queryid. Puisque l'identifiant est dérivé de l'arbre après analyse, sa valeur est une fonction, entre autres choses, des identifiants d'objet interne apparaissant dans cette représentation. Cela a des implications paradoxales. Par exemple, pg_stat_statements considérera deux requêtes apparemment identiques comme distinctes, si elles référencent une table qui a été supprimée et recréée entre l'exécution de ces deux requêtes. Le processus de hachage est également sensible aux différences d'architecture des machines ainsi que d'autres facettes de la plateforme. De plus, il n'est pas sûr de partir du principe que queryid restera stable entre des versions majeures de PostgreSQL™.
De manière générale, on peut supposer que les valeurs de queryid sont stables et comparables tant que la version de serveur sous-jacente et que les informations de métadonnées du catalogue restent exactement les même. Deux serveurs en réplication à base de rejeu de journaux de transactions physique devraient avoir des valeurs de queryid identiques pour une même requête. Toutefois, les mécanismes de réplication logique ne garantissent pas de conserver des réplicats identiques pour tous les détails entrant en jeu, par conséquent queryid ne sera pas un identifiant utile pour accumuler des coûts sur un ensemble de réplicats logiques. En cas de doute, il est recommandé de tester directement.
Le symbole de paramètre utilité pour remplacer les constantes dans le texte représentatif de la requête démarre après le plus grand paramètre $n dans le texte de la requête originale, ou $1 s'il n'y en avait pas. Il est intéressant de noter que dans cerains cas il pourrait y avoir un symbole de paramètre caché qui affecte cette numérotation. Par exemple, PL/pgSQL utilise des symbole de paramètre cachés pour insérer des valeurs de variables locales à la fonction dans les requêtes, ainsi un ordre PL/pgSQL comme SELECT i + 1 INTO j aurait un texte représentatif tel que SELECT i + $2.
Les textes des requêtes sont conservées dans un fichier texte externe et ne consomment pas de mémoire partagée. De ce fait, même lees textes très longs de requêtes peuvent être enregistrés avec succès. Néanmoins, si beaucoup de textes très longs de requêtes sont accumulées, le fichier externe peut devenir suffisamment gros pour ne plus être gérable. Si cela survient, comme méthode de restauration, pg_stat_statements peut choisir d'ignorer les textes de requêtes. Dans ce cas, le champ query apparaitra vide sur les lignes de la vue pg_stat_statements mais les statistiques associées seront préservées. Si cela arrive, réfléchissez à réduire la valeur du paramètre pg_stat_statements.max pour empêcher que cela ne recommence.
pg_stat_statements_reset ignore toutes les statistiques collectées jusque-là par pg_stat_statements. Par défaut, cette fonction peut uniquement être exécutée par les super-utilisateurs.
La vue pg_stat_statements est basée sur une fonction également nommée pg_stat_statements. Les clients peuvent appeler la fonction pg_stat_statements directement, et peuvent en spécifiant showtext := false ne pas récupérer le texte de la requête (ce qui veut dire que l'argument OUT qui correspond à la colonne query de la vue retournera des NULL). Cette fonctionnalité est prévue pour le support d'outils externes qui pourraient vouloir éviter le surcoût de récupérer de manière répétée les textes des requêtes de longueur indeterminées. De tels outils peuvent à la place eux-même mettre le premier texte de requête récupéré pour chaque entrée, puisque c'est déjà ce que fait pg_stat_statements lui-même, et ensuite récupérer les textes de requêtes uniquement si nécessaire. Puisque le serveur stocke les textes de requête dans un fichier, cette approche pourrait réduire les entrée/sorties physiques pour des vérifications répétées des données de pg_stat_statements.
pg_stat_statements.max est le nombre maximum d'ordres tracés par le module (c'est-à-dire le nombre maximum de lignes dans la vue pg_stat_statements). Si un nombre supérieur d'ordres SQL distincts a été observé, c'est l'information sur les ordres les moins exécutés qui est ignorée. La valeur par défaut est 5000. Ce paramètre peut uniquement être positionné au démarrage du serveur.
pg_stat_statements.track contrôle quels sont les ordres comptabilisés par le module. Spécifiez top pour suivre les ordres de plus haut niveau (ceux qui sont soumis directement par les clients), all pour suivre également les ordres imbriqués (tels que les ordres invoqués dans les fonctions) ou none pour désactiver la récupération des statistiques sur les requêtes. La valeur par défaut est top. Seuls les super-utilisateurs peuvent changer ce paramétrage.
pg_stat_statements.track_utility contrôle si les commandes utilitaires sont tracées par le module. Les commandes utilitaires sont toutes les commandes SQL sauf SELECT, INSERT, UPDATE et DELETE. La valeur par défaut est on. Seuls les superutilisateurs peuvent modifier cette configuration.
pg_stat_statements.save précise s'il faut sauvegarder les statistiques lors des arrêts du serveur. S'il est off, alors les statistiques ne sont pas sauvegardées lors de l'arrêt ni rechargées au démarrage du serveur. La valeur par défaut est on. Ce paramètre peut uniquement être positionné dans le fichier postgresql.conf ou sur la ligne de commande du serveur.
Le module a besoin de mémoire partagée supplémentaire proportionnelle à pg_stat_statements.max. Notez que cette mémoire est consommée quand le module est chargé, même si pg_stat_statements.track est positionné à none.
Ces paramètres doivent être définis dans postgresql.conf. Un usage courant pourrait être :
# postgresql.conf shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all
bench=# SELECT pg_stat_statements_reset();
$ pgbench -i bench
$ pgbench -c10 -t300 bench
bench=# \x
bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
-[ RECORD 1 ]---------------------------------------------------------------------
query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
calls | 3000
total_time | 9609.00100000002
rows | 2836
hit_percent | 99.9778970000200936
-[ RECORD 2 ]---------------------------------------------------------------------
query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;
calls | 3000
total_time | 8015.156
rows | 2990
hit_percent | 99.9731126579631345
-[ RECORD 3 ]---------------------------------------------------------------------
query | copy pgbench_accounts from stdin
calls | 1
total_time | 310.624
rows | 100000
hit_percent | 0.30395136778115501520
-[ RECORD 4 ]---------------------------------------------------------------------
query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2;
calls | 3000
total_time | 271.741999999997
rows | 3000
hit_percent | 93.7968855088209426
-[ RECORD 5 ]---------------------------------------------------------------------
query | alter table pgbench_accounts add primary key (aid)
calls | 1
total_time | 81.42
rows | 0
hit_percent | 34.4947735191637631
Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp>. La normalisation des requêtes a été ajoutée par Peter Geoghegan <peter@2ndquadrant.com>.
pgrowlocks
