Vous devez avoir un compte Developpez.com et être connecté pour pouvoir participer aux discussions.

Identifiez-vous
Identifiant
Mot de passe
Mot de passe oublié ?
Créer un compte

Vous n'avez pas encore de compte Developpez.com ? L'inscription est gratuite et ne vous prendra que quelques instants !

Je m'inscris !

Developpez.com

PostgreSQL

Choisissez la catégorie, puis la rubrique :

F.28. pg_stat_statements

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.

F.28.1. La vue pg_stat_statements

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.

F.28.2. Fonctions

pg_stat_statements_reset() returns void

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.

F.28.3. Paramètres de configuration

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 1000. 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 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
  

F.28.4. Exemple de sortie

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

F.28.5. Auteurs

Takahiro Itagaki . La normalisation des requêtes a été ajoutée par Peter Geoghegan .

Contacter le responsable de la rubrique PostgreSQL

Partenaire : Hébergement Web