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 (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 secondes | |
| 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_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_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 |
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.
Notez que les ordres SQL sont considérés identiques s'ils ont le même texte, quelles que soient les valeurs des variables de substitution utilisées dans les ordres SQL. L'utilisation des variables liées va aider à regrouper les ordres SQL et rendre les statistiques plus utiles.
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 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.
Afin de positionner ces paramètres dans votre fichier postgresql.conf, vous devez ajouter pg_stat_statements à custom_variable_classes. Un usage courant pourrait être :
# postgresql.conf shared_preload_libraries = 'pg_stat_statements' custom_variable_classes = '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 -M prepared 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 | 9.60900100000002
rows | 2836
hit_percent | 99.9778970000200936
-[ RECORD 2 ]---------------------------------------------------------------------
query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;
calls | 3000
total_time | 8.015156
rows | 2990
hit_percent | 99.9731126579631345
-[ RECORD 3 ]---------------------------------------------------------------------
query | copy pgbench_accounts from stdin
calls | 1
total_time | 0.310624
rows | 100000
hit_percent | 0.30395136778115501520
-[ RECORD 4 ]---------------------------------------------------------------------
query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2;
calls | 3000
total_time | 0.271741999999997
rows | 3000
hit_percent | 93.7968855088209426
-[ RECORD 5 ]---------------------------------------------------------------------
query | alter table pgbench_accounts add primary key (aid)
calls | 1
total_time | 0.08142
rows | 0
hit_percent | 34.4947735191637631
Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp>
pg_standby
