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 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.
Les statistiques collectées par le module sont rendues disponibles par une vue système nommée pg_stat_statements. Cette vue contient une ligne pour chaque texte de requête, identifiant de base de données et identifiant utilisateur distincts (jusqu'au nombre maximum d'ordres distincts que le module peut surveiller). Les colonnes de la vue sont affichées dans Tableau F.20, « Colonnes de pg_stat_statements ».
Tableau F.20. 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é |
| query | text | Texte de l'ordre SQL représentatif (jusqu'à track_activity_query_size octets) | |
| calls | bigint | Nombre d'exécutions | |
| total_time | double precision | 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). |
Cette vue, et la fonction pg_stat_statements_reset, sont disponibles seulement dans les bases de données dans lesquelles elles ont été installées spécifiquement via l'installation de l'extension pg_stat_statements. Cependant, si le module pg_stat_statements est chargé sur le serveur, les statistiques sont pistées à travers toutes les bases de données du serveur, sans tenir compte de la présence de la vue.
Pour des raisons de sécurité, les utilisateurs qui ne sont pas super-utilisateurs ne sont pas autorisés à voir le texte des requêtes exécutées par les autres utilisateurs. Ils peuvent cependant voir les statistiques si la vue a été installée sur 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.
Concernant le rendu de la requête SQL fournie par pg_stat_statements, il est composé du texte de la première requête dont la valeur de hachage correspond à l'entrée en question. Toutefois, lorsqu'une valeur constante a été ignorée de manière à faire correspondre une requête à cette première requête, la constante est remplacée par ?.
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).
Parce que le hachage est calculé à partir de la représentation après analyse de la requête, le résultat inverse est aussi possible : des requêtes avec un texte identique peuvent apparaître dans des entrées différentes, notamment si elles peuvent être interprétées de différentes façons par l'intermédiaire de facteurs tels que le paramètre search_path.
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.
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 1000. 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 d'environ pg_stat_statements.max * track_activity_query_size octets. 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 + ? WHERE bid = ?;
calls | 3000
total_time | 9609.00100000002
rows | 2836
hit_percent | 99.9778970000200936
-[ RECORD 2 ]---------------------------------------------------------------------
query | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?;
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 + ? WHERE aid = ?;
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
