Le module auto_explain fournit un moyen de tracer les
plans d'exécution des requêtes lentes automatiquement, sans qu'il soit
nécessaire de lancer EXPLAIN manuellement. C'est
particulièrement utile pour repérer les requêtes non optimisées sur de grosses
applications.
Le module ne fournit pas de fonctions accessibles par SQL. Pour l'utiliser, il suffit de le charger sur le serveur. Il peut être chargé dans une session individuelle :
LOAD 'auto_explain';
(Seul le super-utilisateur peut le faire.) Une utilisation plus commune est de
le précharger dans certaines ou toutes les sessions, en incluant
auto_explain dans
session_preload_libraries ou dans
shared_preload_libraries dans le fichier
postgresql.conf. Il est alors possible de récupérer les
requêtes lentes non prévues, quel que soit le moment où elles se produisent.
Évidemment, il y a un prix à payer pour cela.
Plusieurs paramètres de configuration contrôlent le comportement
d'auto_explain. Le comportement par défaut est de ne rien
faire. Il est donc nécessaire de préciser au minimum
auto_explain.log_min_duration pour obtenir un résultat.
auto_explain.log_min_duration (integer)
auto_explain.log_min_duration est la durée minimale
d'exécution de requête à partir de laquelle le plan d'exécution sera tracé.
Son unité est la milliseconde. La positionner à zéro trace tous les plans.
-1 (la valeur par défaut) désactive l'écriture des plans. Positionnée à
250ms, tous les ordres qui durent 250 ms ou plus
seront tracés. Seuls les super-utilisateurs peuvent modifier ce paramétrage.
auto_explain.log_analyze (boolean)
auto_explain.log_analyze entraîne l'écriture du résultat
de EXPLAIN ANALYZE, à la place du résultat de
EXPLAIN, lorsqu'un plan d'exécution est tracé. Ce
paramètre est désactivé par défaut. Seuls les super-utilisateurs peuvent
modifier ce paramètre.
Lorsque ce paramètre est activé, un chronométrage par nœud du plan
est calculé pour tous les ordres exécutés, qu'ils durent suffisamment
longtemps pour être réellement tracés, ou non. Ceci peut avoir des
conséquences très négatives sur les performances. Désactiver
auto_explain.log_timing améliore les performances au
prix d'une diminution des informations.
auto_explain.log_buffers (boolean)
auto_explain.log_buffers contrôle l'affichage des
statistiques d'utilisation du cache disque de PostgreSQL dans la trace
d'un plan d'exécution ; il s'agit de l'équivalent de l'option
BUFFERS de la commande EXPLAIN.
Ce paramètre n'a pas d'effet tant que
auto_explain.log_analyze n'est pas activé. Il est
désactivé par défaut.
auto_explain.log_timing (boolean)
auto_explain.log_timing contrôle l'affichage du
chronométrage de chaque nœud lorsqu'un plan d'exécution est tracé ;
il s'agit de l'équivalent de l'option TIMING pour la
commande EXPLAIN. La surcharge occasionnée par la
lecture répétée de l'horloge système peut ralentir significativement
l'exécution des requêtes sur certains systèmes. De ce fait, il peut être
utile de désactiver ce paramètre quand seul le nombre de lignes exacts
importe. Ce paramètre n'a pas d'effet tant que
auto_explain.log_analyze n'est pas activé. Il est
désactivé par défaut. Seuls les superutilisateurs peuvent modifier la
valeur de ce paramètre.
auto_explain.log_triggers (boolean)
auto_explain.log_triggers entraîne la prise en compte
des statistiques d'exécution des triggers quand un plan d'exécution
est tracé. Ce paramètre n'a pas d'effet tant que
auto_explain.log_analyze n'est pas activé. Il est
désactivé par défaut. Seuls les superutilisateurs peuvent modifier la
valeur de ce paramètre.
auto_explain.log_verbose (enum)
auto_explain.log_verbose contrôle l'affichage des
détails quand un plan d'exécution est tracé ; il s'agit de
l'équivalent de l'option VERBOSE pour la commande
EXPLAIN. Ce paramètre est désactivé par défaut.
auto_explain.log_format (boolean)
auto_explain.log_format sélectionne le format de sortie
utilisé par la commande EXPLAIN. Les valeurs autorisées
sont text, xml,
json et yaml. Le format par défaut
est le texte brut.
auto_explain.log_nested_statements (boolean)
auto_explain.log_nested_statements entraîne la prise en
compte des ordres imbriqués (les requêtes exécutées dans une fonction)
dans la trace. Quand il est désactivé, seuls les plans d'exécution de plus
haut niveau sont tracés. Ce paramètre est désactivé par défaut. Seuls les
super-utilisateurs peuvent modifier ce paramètre.
auto_explain.sample_rate (real)
auto_explain.sample_rate force auto_explain à tracer le
plan d'exécution que d'une fraction des requêtes de chaque session. La
valeur par défaut est de 1, autrement dit toutes les requêtes. Dans le
cas de requêtes imbriquées, soit toutes se voient tracées leur plan, soit
aucune. Seuls les super-utilisateurs peuvent modifier ce paramètre.
D'ordinaire, ces paramètres sont configurés dans le fichier
postgresql.conf mais les superutilisateurs peuvent les
modifier en ligne pour leur propres sessions. Voici un exemple typique
d'utilisation :
# postgresql.conf session_preload_libraries = 'auto_explain' auto_explain.log_min_duration = '3s'
postgres=# LOAD 'auto_explain';
postgres=# SET auto_explain.log_min_duration = 0;
postgres=# SET auto_explain.log_analyze = true;
postgres=# SELECT count(*)
FROM pg_class, pg_index
WHERE oid = indrelid AND indisunique;
Ceci devrait produire un résultat de ce style dans les journaux applicatifs :
LOG: duration: 3.651 ms plan:
Query Text: SELECT count(*)
FROM pg_class, pg_index
WHERE oid = indrelid AND indisunique;
Aggregate (cost=16.79..16.80 rows=1 width=0) (actual time=3.626..3.627 rows=1 loops=1)
-> Hash Join (cost=4.17..16.55 rows=92 width=0) (actual time=3.349..3.594 rows=92 loops=1)
Hash Cond: (pg_class.oid = pg_index.indrelid)
-> Seq Scan on pg_class (cost=0.00..9.55 rows=255 width=4) (actual time=0.016..0.140 rows=255 loops=1)
-> Hash (cost=3.02..3.02 rows=92 width=4) (actual time=3.238..3.238 rows=92 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 4kB
-> Seq Scan on pg_index (cost=0.00..3.02 rows=92 width=4) (actual time=0.008..3.187 rows=92 loops=1)
Filter: indisunique
Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp>