Supervision de PostgreSQL

Révision 21.09 (16 septembre 2021)

Dalibo SCOP

Creative Commons BY-NC-SA

Supervision

PostgreSQL


Introduction

  • Deux types de supervision
    • occasionnelle
    • automatique
  • Superviser PostgreSQL et le système
  • Pour PostgreSQL, statistiques et traces

Superviser un serveur de bases de données consiste à superviser le SGBD lui-même, mais aussi le système d’exploitation et le matériel. Ces deux derniers sont importants pour connaître la charge système, l’utilisation des disques ou du réseau, qui pourraient expliquer des lenteurs au niveau du SGBD. PostgreSQL propose lui aussi des informations qu’il est important de surveiller pour détecter des problèmes au niveau de l’utilisation du SGBD ou de sa configuration.

Ce module a pour but de montrer comment effectuer une supervision occasionnelle (au cas où un problème survient, savoir comment interpréter les informations fournies par le système et par PostgreSQL) et comment mettre en place une supervision automatique (pour des alertes ou la supervision à long terme).

  • Politique de supervision
  • Supervision de PostgreSQL
  • Traces : configuration & analyses
  • Statistiques d’activité

Politique de supervision

  • Pour quoi ?
  • Pour qui ?
  • Quels critères ?
  • Quels outils

Il n’existe pas qu’une seule supervision. Suivant la personne concernée par la supervision et son objectif, les critères de la supervision seront différents.

Lors de la mise en place de la supervision, il est important de se demander l’objectif de cette supervision, à qui elle va servir, les critères qui importent à cette personne.

Répondre à ces questions permettra de mieux choisir l’outil de supervision à mettre en place, ainsi que sa configuration.


Objectifs de la supervision

  • Améliorer/mesurer les performances
  • Améliorer l’applicatif
  • Anticiper/prévenir les incidents
  • Réagir vite en cas de crash

Généralement, les administrateurs mettant en place la supervision veulent pouvoir anticiper les problèmes, qu’ils soient matériels, de performance, de qualité de service, etc.

Améliorer les performances du SGBD sans connaître les performances globales du système est très difficile. Si un utilisateur se plaint d’une perte de performance, pouvoir corroborer ses dires avec des informations provenant du système de supervision aide à s’assurer qu’il y a bien un problème de performances et peut fréquemment aider à résoudre ce problème. De plus, il est important de pouvoir mesurer les gains de performances.

Une supervision des traces de PostgreSQL permet aussi d’améliorer les applications qui utilisent une base de données. Toute requête en erreur est tracée dans les journaux applicatifs, ce qui permet de trouver rapidement les problèmes que les utilisateurs rencontrent.

Un suivi régulier de la volumétrie ou du nombre de connexions permet de prévoir les évolutions nécessaires du matériel ou de la configuration : achat de matériel, création d’index, amélioration de la configuration.

Prévenir les incidents peut se faire en ayant une sonde de supervision des erreurs disques par exemple. La supervision permet aussi d’anticiper les problèmes de configuration. Par exemple, surveiller le nombre de sessions ouvertes sur PostgreSQL permet de s’assurer que ce nombre n’approche pas trop du nombre maximum de sessions configuré avec le paramètre max_connections dans le fichier postgresql.conf.

Enfin, une bonne configuration de la supervision implique d’avoir configuré finement la gestion des traces de PostgreSQL. Avoir un bon niveau de trace (autrement dit : ni trop, ni pas assez) permet de réagir rapidement après un crash.


Acteurs concernés

  • Développeur
    • correction et optimisation de requêtes
  • Administrateur de bases de données
    • surveillance, performance
    • mise à jour
  • Administrateur système
    • surveillance, qualité de service

Il y a trois types d’acteurs concernés par la supervision.

Le développeur doit pouvoir visualiser l’activité de la base de données. Il peut ainsi comprendre l’impact du code applicatif sur la base. De plus, le développeur est intéressé par la qualité des requêtes que son code exécute. Donc des traces qui ramènent les requêtes en erreur et celles qui ne sont pas performantes sont essentielles pour ce profil.

L’administrateur de bases de données a besoin de surveiller les bases pour s’assurer de la qualité de service, pour garantir les performances et pour réagir rapidement en cas de problème. Il doit aussi faire les mises à jours mineures dès qu’elles sont disponibles.

Enfin, l’administrateur système doit s’assurer de la présence du service. Il doit aussi s’assurer que le service dispose des ressources nécessaires, en terme de processeur (donc de puissance de calcul), de mémoire et de disque (notamment pour la place disponible).


Exemples d’indicateurs - système d’exploitation

  • Charge CPU
  • Entrées/sorties disque
  • Espace disque
  • Sur-activité et non-activité du serveur
  • Temps de réponse
  • Outils Unix habituels :
    • top, atop, free, df, vmstat, sar, iotop

Voici quelques exemples d’indicateurs intéressants à superviser pour la partie du système d’exploitation.

La charge CPU (processeur) est importante. Elle peut expliquer pourquoi des requêtes, auparavant rapides, deviennent lentes. Cependant, la suractivité comme la non-activité sont un problème. En fait, si le service est tombé, le serveur sera en sous-activité, ce qui est un excellent indice.

Les entrées/sorties disque permettent de montrer un souci au niveau du système disque. Par exemple, PostgreSQL peut écrire trop à cause d’une mauvaise configuration des journaux de transactions, ou des fichiers temporaires issus de requêtes lourdes ou mal écrites ; ou il peut lire trop de données par manque de cache en RAM, ou de requêtes mal écrites.

L’espace disque est essentiel à surveiller. PostgreSQL ne propose rien pour cela, il faut donc le faire au niveau système. L’espace disque peut poser problème s’il manque, surtout si cela concerne la partition des journaux de transactions.

Unix possède de nombreux outils pour surveiller les différents éléments du système. Les grands classiques sont top et ses innombrables clones comme atop pour le CPU, free pour la RAM, df pour l’espace disque, vmstat pour la mémoire virtuelle, iotop pour les entrées/sorties, ou sar (généraliste). Sous Windows, les premiers outils sont bien sûr le Gestionnaire des tâches, et Process Monitor des outils Sysinternals.

Il est conseillé d’avoir une requête étalon dont la durée d’exécution sera testée de temps à autre pour détecter les moments problématiques sur le serveur.


Exemples d’indicateurs - base de données

  • Nombre de connexions
  • Requêtes lentes et/ou fréquentes
  • Ratio d’utilisation du cache
  • Verrous
  • Volumétries

Il existe de nombreux indicateurs intéressant sur les bases : nombre de connexions (en faisant par exemple la différence entre connexions inactives, actives, en attente de verrous), nombre de requêtes lentes et/ou fréquentes, volumétrie (en taille, en nombre de lignes), des ratios (utilisation du cache par exemple)…


Supervision de PostgreSQL

  • Supervision occasionnelle
    • sur incident…
  • Supervision automatique
    • remonter des informations rapidement
    • archiver, suivre les tendances

La supervision occasionnelle est la conséquence d’une plainte d’un utilisateur : on se contente de réagir à un problème. C’est généralement insuffisant.

Il est important de mettre en place une solution de supervision automatique. Le but est de récupérer périodiquement des données statistiques sur les objets et sur l’utilisation du serveur pour avoir des graphes de tendance, et recevoir des alertes quand des seuils sont dépassés.


Informations internes

  • PostgreSQL propose :
    • statistiques d’activité
    • traces
  • …mais rien pour les historiser

PostgreSQL propose deux canaux d’informations : les statistiques d’activité (à ne pas confondre avec les statistiques sur les données, à destination de l’optimiseur de requêtes) et les traces applicatives (ou « logs »), souvent dans un fichier comme postgresql.log (le nom exact varie avec la distribution et l’installation).

PostgreSQL stocke un ensemble d’informations (métadonnées des schémas, informations sur les tables et les colonnes, données de suivi interne, etc.) dans des tables systèmes qui peuvent être consultées par les administrateurs. PostgreSQL fournit également des vues combinant des informations puisées dans différentes tables systèmes. Ces vues simplifient le suivi de l’activité de la base.

PostgreSQL est aussi capable de tracer un grand nombre d’informations qui peuvent être exploitées pour surveiller l’activité de la base de données.

Pour pouvoir mettre en place un système de supervision automatique, il est essentiel de s’assurer que les statistiques d’activité et les traces applicatives sont bien configurées et il faut aussi leur associer un outil permettant de sauvegarder les données, les alertes et de les historiser.


Outils externes

  • Pour conserver les informations
  • …et exécuter automatiquement des actions
    • graphiques (Munin, Zabbix…)
    • envoi d’alertes (Nagios, tail_n_mail)

Pour récupérer et enregistrer les informations statistiques, les historiser, envoyer des alertes, il faut faire appel à un outil externe. Cela peut être un outil très simple comme munin ou un outil très complexe comme Nagios ou Zabbix.


check_pgactivity

  • Script de monitoring PostgreSQL pour Nagios
    • nombreuses sondes spécifiques à PostgreSQL
    • nombreuses métriques remontées
  • Développé au départ par Dalibo
    • utilisable indépendamment
  • https://github.com/OPMDG/check_pgactivity

Le script de monitoring check_pgactivity permet d’intégrer la supervision de bases de données PostgreSQL dans un système de supervision piloté par Nagios (entre autres).

Au départ, Dalibo utilisait une sonde nommmée check_postgres et participait activement à son développement, avec même un commiter dans le projet. Cependant, rapidement, nous nous sommes aperçus que nous ne pouvions pas aller aussi loin que nous le souhaitions. C’est à ce moment que, dans le cadre de sa R&D, Dalibo a conçu check_pgactivity.

La plupart des sondes de check_postgres y ont été réimplémentées. Le script corrige certaines sondes existantes et en fournit de nouvelles répondant mieux aux besoins de notre supervision, avec notamment un nombre plus important de métriques de performances. Il renvoie directement des ratios par rapport à la valeur précédente plutôt que des valeurs fixes. Pour les besoins les plus simples, le script peut être utilisé de façon autonome, sans nécessité d’installer toute l’infrastructure d’un outil comme Nagios, Icinga ou Grafana.

La supervision d’un serveur PostgreSQL passe par la surveillance de sa disponibilité, des indicateurs sur son activité, l’identification des besoins de maintenance, et le suivi de la réplication le cas échéant. Ci-dessous figurent les sondes check_pgactivity à mettre en place sur ces différents aspects. Le site du projet contient toute la documentation de chaque sonde.

Disponibilité :

  • connection : réalise un test de connexion pour vérifier que le serveur est accessible ;
  • backends : compte le nombre de connexions au serveur comparé au paramètre max_connections ;
  • backend_status : permet d’obtenir des statistiques plus précises sur l’état des connexions clientes et d’être alerté lorsqu’un certain nombre de connexions clientes sont dans un état donné (waiting, idle in transaction…) ;
  • uptime : détecte un redémarrage du serveur ou du rechargement de la configuration.

Vacuum :

  • autovacuum : suit le fonctionnement de l’autovacuum et des tâches en cours (VACUUM, ANALYZE, FREEZE…) ;
  • table_bloat : vérifie le volume de données « mortes » et la fragmentation des tables ;
  • btree_bloat : vérifie le volume de données « mortes » et la fragmentation des index - par rapport à check_postgres, le calcul est séparé entre tables et index ;
  • last_analyze : vérifie si le dernier analyze (relevé des statistiques relatives aux calculs des plans d’exécution) est trop ancien ;
  • last_vacuum : vérifie si le dernier vacuum (relevé des espaces réutilisables dans les tables) est trop ancien.

Activité :

  • locks : permet d’obtenir des statistiques plus détaillées sur les verrous obtenus et tient notamment compte des spécificités des predicate locks du niveau d’isolation SERIALIZABLE ;
  • wal_files : compte le nombre de segments du journal de transaction présents dans le répertoire pg_wal ;
  • longest_query : permet d’être alerté si une requête est en cours d’exécution depuis plus d’un certain temps ;
  • oldest_xact : permet d’être alerté si une transaction est ouverte depuis un certain temps sans être utilisée ;
  • oldest_2pc : calcule l’âge de la plus ancienne transaction préparée (two-phase commit transaction) ;
  • oldest_xmin : repère la plus ancienne transaction de chaque base, et ce à quoi elle est liée (requête, slot…) ;
  • bgwriter : permet de collecter des données de performance des différents processus d’écritures de PostgreSQL ;
  • hit_ratio : calcule le hit ratio (utilisation du cache de PostgreSQL) ;
  • commit_ratio : calcule la proportion de COMMIT et ROLLBACK ;
  • checksum_errors : détecte l’apparition d’erreurs de sommes de contrôle (à partir de PostgreSQL 12) ;
  • database_size : suit la volumétrie des bases et leurs variations ;
  • max_freeze_age : calcule l’âge des plus vieilles lignes stockées dans chaque base pour suivre le bon passage des VACUUM FREEZE ;
  • stat_snapshot_age : calcule l’âge des statistiques d’activité pour repérer un blocage du collecteur ;
  • temp_files : suivi des fichiers temporaires.

Configuration :

  • configuration : permet de vérifier que les principaux paramètres mémoire n’ont pas leur valeur par défaut ;
  • minor_version : détecte les instances n’ayant pas la dernière version mineure ;
  • settings : repère un changement des paramètres ;
  • invalid_indexes : repérer tout index invalide ;
  • pgdata_permission : vérifie les droits sur PGDATA pour éviter un blocage au redémarrage ;
  • table_unlogged : remonte le nombre de tables unlogged ;
  • extensions_versions : détecte les extensions à mettre à jour.

Réplication & archivage :

  • archiver : compte le nombre de segments du journal de transaction en attente d’archivage ;
  • archive_folder : vérifie qu’il n’y a pas de journal manquant dans les archives de sauvegarde PITR ;
  • hot_standby_delta : calcule le délai de réplication entre un serveur primaire et un serveur secondaire ;
  • is_master / is_hot_standby : vérifie que l’instance est bien démarrée en lecture/écriture, ou une instance secondaire ;
  • is_replay_paused : vérifie si la réplication est en pause ;
  • replication_slots : calcule la volumétrie conservée pour chaque slot de réplication.

Sauvegarde physique et logique :

  • backup_label_age : calcule l’âge du fichier backup_label (sauvegardes PITR exclusives) ;
  • pg_dump_backup : contrôle l’âge et la variation de taille des sauvegardes logiques.

check_postgres

Le script de monitoring check_postgres est la première sonde à avoir été écrite pour PostgreSQL. Comme vu précédemment, check_pgactivity est un remplaçant plus fonctionnel, donnant plus de métriques, sur un nombre plus limité de sondes.

check_postgres évolue cependant toujours et est intéressant dans certains cas : alerte pour les triggers désactivés, taille des relations, estimation du retard en réplication logique (native et Slony), comparaison de schémas, détection de proximité du wraparound. Elle intègre aussi beaucoup de sondes pour l’outil de pooling pgBouncer.


Traces

  • Configuration
    • traces peu fournies par défaut
  • Récupération
    • des problèmes importants
    • des requêtes lentes/fréquentes
  • Outils externes de classement

La première information que fournit PostgreSQL sur son activité sort dans les traces. Chaque requête en erreur génère une trace indiquant la requête erronée et l’erreur. Chaque problème de lecture ou d’écriture de fichier génère une trace. En fait, tout problème détecté par PostgreSQL fait l’objet d’un message dans les traces. PostgreSQL peut aussi y envoyer d’autres messages suivant certains événements, comme les connexions, l’activité de processus système en tâche de fond, etc.

Nous allons donc aborder la configuration des traces (où tracer, quoi tracer, quel niveau d’informations). Nous verrons au passage nombre d’informations intéressantes à récupérer. Enfin, nous verrons quelques outils permettant de traiter automatiquement les fichiers de trace.


Configuration des traces : principes

  • Où tracer ?
  • Quel niveau de traces ?
  • Tracer les requêtes 
    • durée, fichiers temporaires…
  • Tracer certains comportements
    • erreurs

Il est essentiel de bien configurer PostgreSQL pour que les traces ne soient pas à la fois trop lourdes (pour ne pas être submergé par les informations) et incomplètes (il manque des informations). Un bon dosage du niveau des traces est important. Savoir où envoyer les traces est tout aussi important.

Suivant la configuration réalisée, les journaux applicatifs peuvent contenir quantité d’informations importantes. La plus fréquemment recherchée est la durée d’exécution des requêtes. L’intérêt principal est de récupérer les requêtes les plus lentes. L’autre information importante concerne les messages d’erreur, de niveau PANIC en premier lieu. Ces messages indiquent un état anormal du serveur qui s’est soldé par un arrêt brutal. Ce genre de problème est anormal et doit être surveillé.


Événements exceptionnels tracés

  • Crash de PostgreSQL :
PANIC:  could not write to file "pg_wal/xlogtemp.9109":
        No space left on device
  • Rechargement de la configuration :
LOG:  received SIGHUP, reloading configuration files
  • Envoi immédiat d’une alerte
  • Outil : tail_n_mail

Les messages PANIC sont très importants. Généralement, vous ne les verrez pas au moment où ils se produisent. Un crash va survenir et vous allez chercher à comprendre ce qui s’est passé. Il est possible à ce moment-là que vous trouviez dans les traces des messages PANIC, comme celui-ci :

PANIC:  could not write to file "pg_wal/xlogtemp.9109":
        No space left on device

Là, le problème est très simple : PostgreSQL n’arrive pas à créer un journal de transactions à cause d’un manque d’espace sur le disque. Du coup, le système ne peut plus fonctionner, il panique et s’arrête.

Un outil comme tail_n_mail peut aider à détecter automatiquement ce genre de problème et à envoyer un mail à la personne d’astreinte. Il n’est d’ailleurs pas si rare que PostgreSQL, après un problème grave, redémarre si vite qu’il n’y a aucune conséquence visible sérieuse, et que ce genre de détection automatique soit le seul symptôme d’un problème.

Un autre événement à suivre est le changement de la configuration du serveur, et surtout la valeur des paramètres modifiés. PostgreSQL envoie un message niveau LOG lorsque la configuration est relue. Il indique aussi les nouvelles valeurs des paramètres, ainsi que les paramètres modifiés qu’il n’a pas pu prendre en compte (cela peut arriver pour tous les paramètres exigeant un redémarrage du serveur).

Là-aussi, tail_n_mail est l’outil adapté pour être prévenu dès que la configuration du serveur est relue.


Où tracer ?

Gestion des traces


Configuration de la destination des traces

  • log_destination : stderr / syslog / csvlog / eventlog
  • logging_collector : géré par PostgreSQL (Red Hat)
    • log_directory, log_filename, log_file_mode
    • log_rotation_age, log_rotation_size, log_truncate_on_rotation
  • Sinon : si off, penser à logrotate (Debian)
  • syslog (Unix)
    • syslog_facility, syslog_ident
    • syslog_sequence_numbers, syslog_split_messages
  • eventlog (Windows) : event_source

PostgreSQL peut envoyer les traces sur plusieurs destinations selon la valeur de log_destination :

stderr et csvlog

stderr (valeur par défaut, y compris sur Debian & Red Hat) et csvlog, disponibles sur toutes les plateformes, correspondent à la sortie des erreurs.

La différence entre les deux réside dans le format des traces (texte simple ou CSV). (Noter qu’il existe une extension jsonlog, par Michaël Paquier, qui offre en plus le format JSON).

Les paramètres suivants sont spécifiques à stderr et csvlog.

logging_collector indique ensuite si PostgreSQL doit s’occuper lui-même de la gestion des fichiers de logs.

S’il est à on (défaut sous Red Hat/CentOS) :

  • log_directory désigne l’emplacement des journaux applicatifs. Par défaut, il est dans le répertoire de l’instance (sous-répertoire log, ou pg_log jusqu’en version 9.6 comprise) ;

  • log_filename indique le nom des fichiers. Sa valeur varie suivant la distribution :

    • postgresql-%Y-%m-%d_%H%M%S.log par défaut (avec rotation quotidienne) ;
    • postgresql-%a.log par défaut sur Red Hat/CentOS : on a ainsi une rotation quotidienne sur une semaine (postgresql-Mon.log, postgresql-Tue.log, etc.) ;
  • log_file_mode précise les droits sur les fichiers (0600 par défaut, les réservant à l’utilisateur sous lequel l’instance tourne). Une rotation est configurable suivant la taille (log_rotation_size) et la durée de vie (log_rotation_age, souvent 1d, à garder en cohérence avec log_filename) ;

  • log_truncate_on_rotation à on entraîne l’effacement de tout fichier dont le nom serait réutilisé, ce qui est en général une bonne idée si les mêmes noms de fichiers sont réutilisés.

Par contre, sous Debian, logging_collector est par défaut à off, les paramètres ci-dessus sont ignorés et la gestion des logs est gérée par le système d’exploitation :

  • les traces vont dans /var/log/postgresql/, donc hors du PGDATA ;
  • elles sont nommées en fonction de l’instance, de son nom et du nom fourni lors de la création avec l’outil pg_ctlcluster (pour l’instance installée par défaut, en version 13, on aura donc postgresql-13-main.log), sauf à modifier le pg_ctl.conf de l’instance ;
  • la rotation des fichiers est gérée par logrotate (comme les autres fichiers de log), et paramétrée dans /etc/logrotate.d/postgresql-common avec par défaut une rotation sur 10 jours.

Une instance compilée n’utilise pas non plus le logging collector.

syslog

syslog fonctionne uniquement sur un serveur Unix et est intéressant pour centraliser la configuration des traces.

Dans cette configuration, il reste à définir le niveau avec syslog_facility, et l’identification du programme avec syslog_ident. Les valeurs par défaut de ces deux paramètres sont généralement bonnes. Il est intéressant de modifier ces valeurs surtout si plusieurs instances de PostgreSQL sont installées sur le même serveur car cela permet de différencier leur traces.

syslog_sequence_numbers préfixe chaque message d’un numéro de séquence incrémenté automatiquement, pour éviter le message --- last message repeated N times ---, utilisé par un grand nombre d’implémentations de syslog. Ce comportement est activé par défaut. Quant à syslog_split_messages, s’il est activé, les messages envoyés à syslog sont divisés par lignes, elles-mêmes divisées pour tenir sur 1024 octets. Attention à ce que syslog soit configuré pour accepter des débits élevés quand on tient à tout tracer.

eventlog

eventlog est disponible uniquement sur Windows et alimente le journal des événements. Pour identifier les messages de PostgreSQL, il faut aussi renseigner event_source, qui par défaut est à « PostgreSQL ».


Niveau des traces

  • log_min_messages
    • défaut : panic / fatal / log / error / warning
  • log_min_error_statement
    • défaut : error (ou warning)
  • log_error_verbosity
    • default / terse / verbose

log_min_messages est le paramètre à configurer pour avoir plus ou moins de traces. Par défaut, PostgreSQL enregistre tous les messages de niveau panic, fatal, log, error et warning. Cela peut sembler beaucoup mais, dans les faits, c’est assez discret. Cependant, il est possible de descendre le niveau ou de l’augmenter.

log_min_error_statement indique à partir de quel niveau la requête est elle-aussi tracée. Par défaut, la requête n’est tracée que si une erreur est détectée. Généralement, ce paramètre n’est pas modifié, sauf dans un cas précis. Les messages d’avertissement (niveau warning) n’indiquent pas la requête qui a généré l’affichage du message. Cela est assez important, notamment dans le cadre de l’utilisation d’antislash dans les chaînes de caractères. On verra donc parfois un abaissement au niveau warning pour cette raison.

log_error_verbosity vaut default, qui convient généralement. Si une requête est tracée, plusieurs lignes peuvent apparaître, chacune de niveau DETAIL, HINT, QUERY ou CONTEXT. La valeur terse les masque. À l’inverse, verbose rajoute encore d’autres informations sur le code source d’origine.


Tracer les requêtes et leur durée

  • Toutes les requêtes :
    • log_min_duration_statement (ex : 1s)
    • ou log_statement + log_duration
  • Extrait aléatoire :
    • log_transaction_sample_rate
    • log_statement_sample_rate + log_min_duration_sample

Pour répérer les problèmes de performances, il est intéressant de pouvoir tracer les requêtes et leur durée d’exécution. PostgreSQL propose deux solutions à cela.

log_statement & log_duration :

La première solution disponible concerne les paramètres log_statement et log_duration. Le premier permet de tracer toute requête exécutée si la requête correspond au filtre indiqué par le paramètre :

  • none : aucune requête n’est tracée ;
  • ddl : seules les requêtes DDL (autrement dit de changement de structure) sont tracées ;
  • mod : seules les requêtes de changement de structure et de données sont tracées ;
  • all : toutes les requêtes sont tracées.

Le paramètre log_duration est un simple booléen. S’il vaut true ou on, chaque requête exécutée envoie en plus un message dans les traces indiquant la durée d’exécution de la requête. Évidemment, il vaut mieux alors configurer log_statement à all, ou il sera impossible de dire à quelles requêtes les temps correspondent.

Donc pour tracer toutes les requêtes et leur durée d’exécution, une solution serait de réaliser la configuration suivante :

log_statement = 'all'
log_duration = on

Une requête générera deux entrées dans les traces, de cette façon :

2019-01-28 15:43:27.993 CET [25575] LOG:  statement: SELECT * FROM pg_stat_activity;
2019-01-28 15:43:27.999 CET [25575] LOG:  duration: 7.093 ms

log_min_duration_statement :

Il est préférable de désactiver ces deux paramètres et de préférer log_min_duration_statement. Son but est d’abord de cibler les requêtes lentes, par exemple celles qui prennent plus de deux secondes à s’exécuter :

log_min_duration_statement = '2s'

La requête et la durée d’exécution seront alors tracées dans le même message :

2019-01-28 15:49:56.193 CET [32067] LOG:
  duration: 2906.270 ms
  statement: insert into t1 select i, i from generate_series(1, 200000) as i;

En plus de la trace par log_min_duration_statement, rien n’interdit de tracer des requêtes sensibles, notamment le DDL :

log_statement = 'ddl'

Échantillonnage :

Quelle que soit la méthode, tracer toutes les requêtes peut poser problème pour de simples raisons de volumétrie du fichier de traces. Même s’il est possible de configurer finement la durée à partir de laquelle une requête est tracée, il faut bien comprendre que plus la durée minimale est importante, plus la vision des performances est partielle. Passeront ainsi « sous le radar » des requêtes relativement rapides mais très nombreuses qui, ensemble, peuvent représenter l’essentiel de la charge.

Cela étant dit, laisser 0 en permanence n’est pas recommandé. Il est préférable de configurer ce paramètre à une valeur plus importante en temps normal pour détecter seulement les requêtes longues et, lorsqu’un audit de la plateforme est nécessaire, passer temporairement ce paramètre à une valeur très basse (0 étant le mieux).

Une nouvelle fonctionnalité a donc été ajoutée : tracer une certaine proportion des requêtes ou des transactions.

log_transaction_sample_rate, à partir de PostgreSQL 12, indique une proportion de transactions à tracer. Par exemple, en le configurant à 0.01, toutes les requêtes d’un centième des transactions, choisies au hasard, seront tracées.

De manière similaire, à partir de PostgreSQL 13, log_statement_sample_rate indique la proportion de requêtes à tracer, parmi celles durant plus d’une certaine durée, à indiquer dans log_min_duration_sample :

log_min_duration_sample = '10ms'
log_statement_sample_rate = 0.01

Évidemment, une requête dépassant la durée de log_min_duration_statement sera toujours tracée.


Configuration : tracer certains comportements

  • log_connections, log_disconnections
  • log_autovacuum_min_duration
  • log_checkpoints
  • log_lock_waits (mini 1s)

En dehors des erreurs et des durées des requêtes, il est aussi possible de tracer certaines activités ou comportements. Le paramétrage par défaut est peu bavard, et il est généralement conseillé d’activer tous les paramètres qui suivent.

log_connections et son pendant log_disconnections, à on, permettent de suivre qui se (dé)connecte, depuis où, et durant combien de temps :

2019-01-28 13:34:32 CEST LOG:  connection received: host=[local]
2019-01-28 13:34:32 CEST LOG:  connection authorized: user=u1 database=b1
…
2016-09-01 13:34:35 CEST LOG:  disconnection: session time: 0:01:04.634
                               user=u1 database=b1 host=[local]

Il est possible de récupérer cette durée de session pour calculer leur durée moyenne. Cette information est importante pour savoir si un outil de pooling de connexions a un intérêt.

log_autovacuum_min_duration équivaut à log_min_duration_statement, mais pour l’autovacuum. Le but est de tracer son activité, au-delà d’une certaine durée, de vérifier qu’il passe suffisamment fréquemment et rapidement.

log_checkpoints à on ajoute un message dans les traces pour indiquer qu’un checkpoint commence ou se termine, auquel cas s’ajoutent des statistiques :

2019-01-28 13:34:17 CEST LOG: checkpoint starting: xlog
2019-01-28 13:34:20 CEST LOG: checkpoint complete:
  wrote 13115 buffers (80.0%);
  0 WAL file(s) added, 0 removed, 0 recycled;
  write=3.007 s, sync=0.324 s, total=3.400 s;
  sync files=16, longest=0.285 s, average=0.020 s;
  distance=404207 kB, estimate=404207 kB

Le message indique donc en plus le nombre de blocs écrits sur disque, le nombre de journaux de transactions ajoutés, supprimés et recyclés. Il est rare que des journaux soient ajoutés, ils sont plutôt recyclés. Des journaux sont supprimés quand il y a eu une très grosse activité qui a généré plus de journaux que d’habitude. Les statistiques incluent aussi la durée des écritures, de la synchronisation sur disque, la durée totale, etc. Le plus important est de pouvoir vérifier que l’écriture des checkpoints est bien régulière (essentiellement périodique).

log_lock_waits à on permet de tracer les attentes de verrous (par exemple, un UPDATE bloqué par un autre UPDATE, un SELECT bloqué par TRUNCATE ou un VACUUM FULL, etc…) Lorsque l’attente dépasse la durée indiquée par le paramètre deadlock_timeout (1 seconde par défaut), un message est enregistré, comme dans cet exemple :

2019-01-28 13:38:40 CEST LOG:  process 15976 still waiting for
                               AccessExclusiveLock on relation 26160 of
                               database 16384 after 1000.123 ms
2019-01-28 13:38:40 CEST STATEMENT:  DROP TABLE t1;

Ici, un DROP TABLE attend depuis 1 seconde de pouvoir poser un verrou exclusif sur une relation.

Plus ce type de message apparaît dans les traces, plus des contentions ont lieu sur certains objets, ce qui peut diminuer fortement les performances. Ces messages peuvent permettre d’analyser la cause première d’une accumulation de verrous, à condition que les requêtes soient tracées.


Repérer les fichiers temporaires

  • Exemple :
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp9894.0",
      size 26927104
  • log_temp_files à activer !
  • Alerte : problème potentiel de performances

Quand PostgreSQL ne peut effectuer un tri en mémoire, il le fait sur disque dans un fichier temporaire, ce qui est beaucoup plus lent qu’en mémoire, même avec un SSD. Typiquement, cela concerne le tri de données et le hachage, quand la valeur du paramètre work_mem ne permet pas de tout faire en mémoire. Cela ne sera pas forcément gênant pour une grosse requête ponctuelle, mais, répétés, ces fichiers peuvent avoir un impact sur la performance du système. Ils sont parfois inévitables quand on brasse beaucoup de données.

Être averti lors de la création de ce type de fichiers peut être intéressant, mais ils sont parfois trop fréquents pour que ce soit réaliste. Il est préférable de faire analyser après coup un fichier de traces pour savoir combien de fichiers temporaires ont été créés, et de quelles tailles. Cela peut mener à vérifier les requêtes exécutées, les optimiser, vérifier la configuration, réviser la valeur de work_mem

Le paramètre log_temp_files à 0 permet de tracer toutes les créations de fichiers temporaires, comme ici :

2019-01-28 13:41:11 CEST LOG:  temporary file: path
                               "base/pgsql_tmp/pgsql_tmp15617.1",
                               size 59645952

Pour le même tri, il peut y avoir de nombreux fichiers temporaires. De plus, la requête est aussi tracée, et si elle est longue et fréquente, le volume de traces peut être conséquent.


Configuration : divers

  • log_line_prefix
    • Conseillé : %t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h
  • lc_messages = C
  • log_timezone = 'Europe/Paris'

Le paramètre log_line_prefix permet d’ajouter un préfixe à une trace. Le défaut ('%m [%p] ', soit horodatage et numéro de processus), est insuffisant :

2021-02-05 14:12:12.343 UTC [2917] LOG:  duration: 3.276 ms
                            statement: SELECT count(*) FROM pgbench_branches ;

Il est conseillé de rajouter le nom de l’application cliente, le nom de l’utilisateur, le nom de la base, etc. Une valeur habituellement conseillée pour pgBadger, pour une sortie vers stderr, est :

log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '

ce qui nous donnera ce genre de traces :

2021-02-05 14:30:01 UTC [3006]: user=durand,db=bench,app=test,client=[local]
    LOG:  duration: 0.184 ms  statement: SELECT count(*) FROM pgbench_branches ;

Pour une sortie vers syslog, l’horodatage est inutile :

log_line_prefix = 'user=%u,db=%d,app=%a,client=%h '

Par défaut, les traces sont enregistrées dans la locale par défaut du serveur. Des traces en français peuvent présenter certains intérêts pour des débutants, mais ont plusieurs gros inconvénients : un moteur de recherche renverra beaucoup moins de résultats avec des traces en français qu’en anglais, et les outils d’analyse automatique des traces se basent principalement sur des traces en anglais. Donc, il vaut mieux préciser systématiquement :

lc_messages = 'C'

Quant à log_timezone, il permet de choisir le fuseau horaire pour l’horodatage des traces. C’est inestimable quand on administre différents serveurs dispersés sur la planète.

log_timezone = 'UTC'
log_timezone = 'Europe/Paris'

Outils d’analyse des traces

  • Beaucoup d’outils existent
    • en temps réel / rétro-analyse
    • généralistes / spécifiques PostgreSQL
  • Exemples :
    • pgBadger
    • logwatch
    • tail_n_mail

Il existe de nombreux programmes qui analysent les traces. On peut distinguer deux catégories :

  • ceux qui le font en temps réel ;
  • ceux qui le font après coup (de la rétro-analyse en fait).

Mais également :

  • ceux généralistes, connaissant plus ou moins bien beaucoup d’outils et logiciels ;
  • ceux dédiés à PostgreSQL.

L’analyse en temps réel des traces permet de réagir rapidement à certains messages. Par exemple, il est important d’avoir une réaction rapide à l’archivage échoué d’un journal de transactions, ainsi qu’en cas de manque d’espace disque. Dans cette catégorie, il existe des outils généralistes comme logwatch, et des outils spécifiques pour PostgreSQL comme tail_n_mail.

L’analyse après coup permet une analyse plus fine, se terminant généralement par un rapport en HTML, parfois avec des graphes. Cette analyse plus fine nécessite des outils spécialisés. Il en a existé plusieurs qui ne sont plus maintenus. La référence dans le domaine est pgBadger.


pgBadger

  • Site officiel : https://pgbadger.darold.net/
  • Licence : PostgreSQL
  • Analyse des traces de durée d’exécution des requêtes
  • Analyse des traces du VACUUM, des connexions, des checkpoints
  • Compatible syslog, stderr, csvlog

Gilles Darold a créé pgBadger, un analyseur des journaux applicatifs de PostgreSQL. Il permet de générer des rapports détaillés depuis ceux-ci. pgBadger est très souvent utilisé pour déterminer les requêtes à améliorer en priorité pour accélérer son application basée sur PostgreSQL. C’est certainement le meilleur outil actuel de rétro-analyse d’un fichier de traces PostgreSQL, au point qu’il est cité dans le manuel de PostgreSQL.

pgBadger est écrit en Perl et est facilement extensible si vous avez besoin de rapports spécifiques.

Il est conçu pour traiter rapidement de gros fichiers de traces avec une mémoire réduite, mais permet d’exploiter plusieurs CPU pour accélérer considérablement l’analyse.


pgBadger : exemple de rapport