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.
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.21.
Tableau F.21. Colonnes de pg_stat_statements
| Nom | Type | Référence | Description |
|---|---|---|---|
userid | oid | | OID de l'utilisateur qui a exécuté l'ordre SQL |
dbid | 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 associé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.
Deux serveurs participant à une réplication basée sur le rejeu physique des
journaux de transactions devraient avoir des valeurs de
queryid identiques pour la même requête.
Cependant, les systèmes de réplication logique ne promettent pas de conserver
des réplicats identiques surtout les détails intéressants, donc
queryid ne sera pas un identifiant utile pour
accumuler les coûts dans un ensemble de réplicats logiques. En cas de doute,
un test direct est recommandé.
En règle général, il peut être supposé que les valeurs
queryid sont stables entre des versions mineures
de PostgreSQL, en supposant que les instances sont
exécutées sur la même architecture matérielle et que les détails de
métadonnées du catalogue correspondent. La compatibilité entre versions
mineures ne sera cassée qu'en cas de dernière extrémité.
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 les 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(userid Oid, dbid Oid, queryid bigint) returns void
pg_stat_statements_reset annule les statistiques
récupérées jusqu'à maintenant par
pg_stat_statements correspondant aux
userid, dbid et
queryid indiqués. Si un de ces paramètres
n'est pas spécifié, la valeur par défaut 0 (invalide)
est utilisée pour chacun d'entre eux et les statistiques qui
correspondent avec les autres paramètres seront réinitialisées. Si aucun
paramètre n'est spécifié ou si tous les paramètres spécifiés sont à
0 (invalide), elle annulera toutes les statistiques.
Par défaut, cette fonction peut seulement être exécutée par les
superutilisateurs. L'accès peut être donné à d'autres utilisateurs en
utilisant la commande GRANT.
pg_stat_statements(showtext boolean) returns setof record
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 (integer)
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 (enum)
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 (boolean)
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 (boolean)
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;
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 | 25565.855387
rows | 3000
hit_percent | 100.0000000000000000
-[ RECORD 2 ]--------------------------------------------------------------------
query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
calls | 3000
total_time | 20756.669379
rows | 3000
hit_percent | 100.0000000000000000
-[ RECORD 3 ]--------------------------------------------------------------------
query | copy pgbench_accounts from stdin
calls | 1
total_time | 291.865911
rows | 100000
hit_percent | 100.0000000000000000
-[ RECORD 4 ]--------------------------------------------------------------------
query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls | 3000
total_time | 271.232977
rows | 3000
hit_percent | 98.5723926698852723
-[ RECORD 5 ]--------------------------------------------------------------------
query | alter table pgbench_accounts add primary key (aid)
calls | 1
total_time | 160.588563
rows | 0
hit_percent | 100.0000000000000000
bench=# SELECT pg_stat_statements_reset(0,0,s.queryid) FROM pg_stat_statements AS s
WHERE s.query = 'UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2';
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_tellers SET tbalance = tbalance + $1 WHERE tid = $2
calls | 3000
total_time | 20756.669379
rows | 3000
hit_percent | 100.0000000000000000
-[ RECORD 2 ]--------------------------------------------------------------------
query | copy pgbench_accounts from stdin
calls | 1
total_time | 291.865911
rows | 100000
hit_percent | 100.0000000000000000
-[ RECORD 3 ]--------------------------------------------------------------------
query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls | 3000
total_time | 271.232977
rows | 3000
hit_percent | 98.5723926698852723
-[ RECORD 4 ]--------------------------------------------------------------------
query | alter table pgbench_accounts add primary key (aid)
calls | 1
total_time | 160.588563
rows | 0
hit_percent | 100.0000000000000000
-[ RECORD 5 ]--------------------------------------------------------------------
query | vacuum analyze pgbench_accounts
calls | 1
total_time | 136.448116
rows | 0
hit_percent | 99.9201915403032721
bench=# SELECT pg_stat_statements_reset(0,0,0);
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 | SELECT pg_stat_statements_reset(0,0,0)
calls | 1
total_time | 0.189497
rows | 1
hit_percent |
Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp>.
La normalisation des requêtes a été ajoutée par Peter Geoghegan <peter@2ndquadrant.com>.