Traqueur : introduction, installation, utilisation basique

Outil de résolution systématique des problèmes de performance

Téléchargement du traqueur pour PostgreSQL 12, 13, 14, 15, 16
Téléchargement du traqueur pour PostgreSQL 11
Téléchargement du traqueur pour PostgreSQL 10
Téléchargement du traqueur pour PostgreSQL 9.6
Téléchargement du traqueur pour PostgreSQL 9.5
Téléchargement du traqueur pour PostgreSQL 9.4
Téléchargement du traqueur pour PostgreSQL 9.3
Journal des changements
Signalement de bugs via le forum
Licence identique à celle de PostgreSQL, open source type BSD
Dossier des versions

Analyse de performance, quel point d’entrée ?

      Imaginez que vous êtes le DBA responsable d’un environnement de production. Un utilisateur se plaint que c’est "lent". Vous soupirez. Vous vous connectez au serveur avec un outil comme putty. Vous exécutez "topv5", "topas" ou autre "top". Vous constatez que le serveur "n’est pas chargé"...et c’est tout. Vous vous arrêtez là en concluant que l’utilisateur a rêvé et vous lui dites que "pour vous tout va bien". Cela risque de ne pas suffire comme réponse non ?

      Bien sûr, c’est caricatural. Mais disposer d’un outil pour le DBA aussi rapide à utiliser que "top" et donnant plus de pistes peut être intéressant. Le traqueur peut être cet outil.
      Le traqueur est basé sur le même principe simple que le snapper de Tanel Poder, un script gratuit disponible pour toutes les éditions d’Oracle Database. Les utilisateurs d’une application interrogent une base de données par l’intermédiaire de sessions. L’idée est de demander aux sessions si elles sont actives et, si elles le sont, pourquoi elles attendent en réalisant un sondage sur une période de notre choix. Si une session n’est pas active, le DBA ne peut pas faire grand chose mais c’est malgré tout une information importante !
      Supposons en effet qu’un utilisateur a attendu 100 secondes la réponse de son application. Le traqueur dit que la session de l’utilisateur a été active à 3% donc le travail sur la base est de l’ordre de 3 secondes. Si vous divisez par 2 les 3 secondes alors l’utilisateur attendra 98,5s. Il risque de ne pas être emballé. La cause des lenteurs se situe ailleurs : réseau, serveur applicatif, poste de travail...
      Le cas inverse peut se présenter. Une part non négligeable de l’attente se situe parfois au niveau de la base. Le traqueur vous permettra alors de déterminer quelles sont les requêtes les plus significatives dans l’activité donc celles qui sont à optimiser. Ce ne sont pas forcément les plus lentes ! Imaginez que le traqueur lancé sur la session de l’utilisateur remonte une "grosse" requête prenant à chaque exécution 1 seconde mais représentant 5% de l’activité et une autre prenant 0,1s à chaque exécution mais représentant 80% de l’activité. À laquelle devez-vous vous intéresser en priorité ?
      Le traqueur permet aussi de déterminer si le temps d’activité est plutôt passé sur le CPU (processeur) ou ailleurs. Si une sessions active n’est pas occupée sur le CPU, c’est peut-être que le serveur manque de ressources à ce niveau. Sinon le temps peut aussi être passé en attente de verrous et le traqueur vous dira quelle(s) sessions(s) ont provoqué ces attentes. Enfin, il peut aussi s’agir d’attentes I/O (lectures/écriture).
      Le but est donc de cerner rapidement le ou les goulets d’étranglement, le traqueur est un outil de "troubleshooting". Je vous conseille de l’exécuter aussi périodiquement quand les performances sont jugées satisfaisantes afin de pouvoir travailler par comparaison.
      Même s’il est au départ prévu pour être utilisé en interactif, le traqueur peut être utilisé en mode batch afin de rendre persistentes les données de troubleshooting et d’enquêter sur des problèmes passés. Les données persistentes peuvent par ailleurs être stockées directement dans un cluster dédié, distinct du cluster à analyser.

Prérequis, installation

      J’utilise principalement le traqueur avec PostgreSQL 11 sous Debian 10 "Buster" , PostgreSQL 13 sous Debian 11 "Bullseye", PostgreSQL 15 sous Debian 11 "Bookworm".
      Toutes les options n’ont pas été testées mais le script a été reporté comme fonctionnel pour une utilisation basique avec PostgreSQL 9.6 sous Oracle Linux 7.3 (distribution Redhat-like).
      Pour disposer de l’ensemble des fonctionnalités, il faut dans tous les cas :

      Si vous n’avez pas les prérequis python, vous disposerez déjà de beaucoup d’éléments mais vous n’aurez pas les informations CPU ainsi que les informations concernant la consommation mémoire par process.
      Si vous n’avez pas gcc, vous n’aurez pas accès au test CPU/RAM.
      Si vous n’avez pas bonnie++, vous n’aurez pas accès au test de latence I/O.
      Sur un système Debian 12 "Bookworm", qui est la version stable de Debian depuis juin 2023, tout cela s’installe en moins de 5 minutes. Je suppose que PostgreSQL 15 est déjà installé et un cluster créé.

-- connecte postgres postgres@srvdeb12:~$ psql -- optionnel mais recommandé (securité) postgres=# CREATE DATABASE traqueur; CREATE DATABASE postgres=# REVOKE ALL PRIVILEGES ON DATABASE traqueur FROM public; REVOKE postgres=# \q -- transfert de traqueur.sh sur le serveur puis : postgres@srvdeb12:~$ chmod 700 traqueur.sh postgres@srvdeb12:~$ ./traqueur.sh -h traqueur 7.01.02 - https://pgphil.ovh - outil de diagnostic performance pour PostgreSQL version 12 => 16 usage: traqueur.sh [-h] [ -v niveau ] [ -t 1|2|3|4|5 ] [-c parametres de connexion] [ -q role ] [ -r ] [ -m fenetre ] [ -z ] [ -s delai ] [ -b L|U|F|H ] [ -e L|U ] [ -j ] [-d duree ] [ -w pids ] [ -n ] [ -y ] [ -f ] [ -g "commentaire" ] [ -p ] [ -i profondeur ] [ -o colonnes ] [ -k iterations ] [ -l ] [ -a t|x|tx ] [ -C L|U ] [ -D ] [ -F informations ] [ -A role ] [ -R role ] [ -S schema ] [ -N ] [ -L ] [ -H dossier ] [ -K ] -h : affiche ce message et sort -v niveau : niveau de verbosite du script apres la lecture des arguments, 0 aucun message, 1 erreurs, 2 erreurs/warnings, 3 erreurs/warnings/informations (par defaut 3) -t 1|2|3|4|5 : effectue un test de performance et sort. Entre 1 et 4, test CPU/RAM (calcul d'un grand nombre premier avec l'argument 1, calcul de decimales de pi avec les argument 2, 3 et 4). 5 effectue un test de latence I/O. L'execution du traqueur doit toujours etre faite sur le serveur postgresql. Les tests 1, 2, 3, 4 necessitent gcc. Le test 1 utilise 600M dans $TRAQUEUR_W (/tmp par defaut si la variable $TRAQUEUR_W n'est pas positionnee). Le test 5 necessite bonnie++ v1.97+ et utilise 1Go dans $PGDATA/base (/var/lib/postgresql/13/main/base par defaut si la variable $PGDATA n'est pas positionnee) et consomme beaucoup de ressources I/O -c parametres : parametres de connexion passes au programme psql -q role : cree un role permettant d'eviter les connexions en tant que superuser en interactif (une premiere utilisation du traqueur avec un superuser est toujours necessaire pour creer les extensions dans la base de travail) et sort. -r : produit un rapport en interrogeant les donnees obtenues en mode batch et sort, l'option -p ajoute les infos sur les temps CPU pendant la periode, l'option -l ajoute les informations concernant la latence I/O avec PostgreSQL 16 et versions superieures. -m fenetre : supprime en base les donnees collectees en mode batch en fonction d'une fenetre de conservation exprimee en jours (fenetre est un entier, une fenetre de 0 tronque les tables) et sort. Lorsque les donnees sont collectes avec -b F et donc stockees dans un repository distant, le traqueur avec l'option -m doit etre directement execute sur le cluster du repository -z : supprime en base le schema du traqueur designe par $TRAQUEUR_LOCAL_SCHEMA (par defaut traqueur) -s delai : delai minimal en centiemes de secondes entre 2 interrogations de pg_stat_activity lors de la collecte (par defaut 10, minimum 0). Avec -p le delai effectif sera toujours > 0.1s. -s 0 supprime les temps de sommeil du script : cette option ne peut pas etre combinee avec l'option -b et la duree reelle du script peut varier enormement -a t|x|tx : avec l'argument t, active le chronometrage par requete de psql pendant la phase de collecte et la presentation des resultats. Avec l'argument x, active l'affichage psql etendu, prise en compte lors de l'affichage des resultats. L'argument tx combine les 2 options. -b L|U|F|H : mode batch, multiplie par 100 le delai fixe par -s. Au lieu d'etre stockees dans des tables temporaires afin de produire un rapport immediat, les donnees collectees sont conservees en base dans des tables non journalisees (unlogged) avec l'argument U, journalisees (logged) avec l'argument L, distantes (foreign) avec l'argument F. -b F necessite un repository cree avec l'option -C dans la meme version majeure de PostgreSQL. Avec l'argument H, un fichier plat est utilise au lieu de tables et les options -n, -f, -g, -p ne sont pas prises en compte. -b H peut etre utilise sur des clusters en lecture seule (hot standby). Les donnees de performance concernant les I/O sont automatiquement incluses toutes les 10 iterations si track_io_timing=on avec PostgreSQL 16 et versions superieures. -e L|U : utilisee avec l'argument L, l'option -e passe les tables creees par l'option -b en mode logged, et sort. Avec l'argument U, l'option -e passe les tables en mode unlogged, et sort. Cette option provoque une erreur sur les tables distantes creees avec l'option -b F -j : tire parti du partitionnement natif des tables en mode batch (option -b). L'option -j peut egalement etre utilisee en combinaison avec l'option -m pour realiser des suppressions de partitions au lieu de suppressions de lignes et avec l'option -C pour utiliser le partionnement dans un repository du traqueur. -d duree : valeur entiere, duree approximative de la partie analyse du script en secondes (jours en mode batch) (par defaut 5, minimum 0). -d 0 realise une seule interrogation de pg_stat_activity et pg_locks. avec -s 0, la duree peut etre tres eloignee de la duree prevue -w pids : filtrage de la collecte sur une liste de pids separes par , ou toute requete retournant des pids (exemple "7232,4281" ou "select pid from pg_stat_activity where application_name = 'appli'", par defaut "select pid from pg_stat_activity where coalesce(wait_event_type,'') not in ('Timeout', 'Activity')" -n : normalise les requetes lors de la collecte, remplacement des valeurs numeriques par 0, des valeurs chaines par '' et des listes par (...) -y : lors de la collecte, renseigne la colonne de type jsonb application_info (cles : program, module, action, client_info, sofar, totalwork) depuis la colonne application_name de pg_stat_activity. Le format d'application_name doit etre programme,module,action,informations_clientes,travail_effectue,travail_restant. -f : lors de la collecte, renseigne la colonne de type tsvector dquery permettant d'appliquer des filtres texte sur les donnees de query lors de la production de rapports, option non recommandee sauf cas tres particulier -g commentaire : en mode batch (option -b), collecte d'informations generales et de statistiques sur le cluster (version de PostgreSQL, taille etc.) pouvant etre utilisees en reporting. Le commentaire est utilise pour renseigner une colonne dediee. L'option -g cree par ailleurs une table utilisable pour renseigner manuellement les resultats de benchmarks -u : en mode batch (option -b), collecte d'informations detaillees sur les bloqueurs et transactions preparees -p : collecte d'informations sur l'activite CPU et la consommation memoire, necessite le module python psutil (version >= 4 pour les infos memoire) -i profondeur : niveau de profondeur pour l'analyse recursive des bloqueurs finaux (par defaut 2) -o colonnes : choix de colonnes lors de l'affichage des resultats parmi celles de pg_stat_activity separees par "," avec en plus "blockers" (liste de processus bloquants finaux, 0 designant une ou plusieurs transactions preparees), "to_hex(iquery)" (valeur de hachage de la requete), "tquery" (requete normalisee), "to_hex(itquery)" (valeur de hachage de la requete normalisee), application_info (informations clientes). Ce choix se substitue au choix par defaut qui peut par ailleurs etre demande explicitement avec -o "defaut". Utiliser -o avec "" ou avec une constante permet de considerer toutes les sessions actives sans agregation. L'option -o peut etre renseignee plusieurs fois afin d'agreger de differentes manieres les resultats de la collecte -k iterations : arrete prematurement la collecte si elle ne ramene aucune information "iterations" fois consecutivement (minimum 1). Cette option n'est pas effective sur un cluster en lecture seule (-b H). -l : en mode interactif, affiche les informations de latence I/O (en millisecondes) et les evictions du cache avec PostgreSQL 16 et versions superieures -C L|U : cree un repository, un schema pouvant etre utilise pour stocker les donnees collectes en mode batch (les tables sont logged avec l'argument L, unlogged avec l'argument U), cree un utilisateur avec les privileges select et insert et un utilisateur avec les privileges select sur les tables du repository puis sort. -D : supprime le schema du repository, supprime les utilisateur crees par l'option -C et sort -F informations : cette option peut etre combinee avec l'option -b F option afin de fournir les informations de connexion vers un repository (par defaut "host 'traqueur', dbname 'traqueur', port '5432'") -A role : cette option peut etre combinee avec les options -C, -D , -b F et -F afin d'indiquer un role ayant les privileges insert et select sur les tables du repository (par defaut traqueur_agent) -R role : cette option peut etre combinee avec les options -C et -D afin d'indiquer un role ayant les privileges select sur les tables du repository (par defaut traqueur_dashboard) -S schema : cette option peut etre combinee avec les options -C, -D , -b F et -F afin d'indiquer le schema du repository (par defaut traqueur) -N : cette option peut etre combinee avec l'option -b F afin de ne pas renseigner de mot de passe dans la correspondance entre l'utilisateur PostgreSQL executant le traqueur et l'utilisateur PostgreSQL du repository -L : charge un fichier plat produit sur une hot standby dans un repository cree par l'option -C, supprime du fichier les informations chargees et sort. Le cluster sur lequel le fichier a ete produit et le cluster cible doivent etre dans la meme version majeure de PostgreSQL -H dossier : cette option peut etre combinee avec l'option -b H, -r H, -L pour indiquer le dossier du fichiers plat traqueur_sessions_actives.txt (dossier par defaut /traqueur) -K : supprime les fichiers de travail traqueur.pid obsoletes et stoppe les processus du traqueur tournant en mode batch, cette option peut etre combinee avec l'option -c priorite : l'ordre de priorite des options speciales, entrainant une sortie du script sans collecte d'informations sur l'activite de postgresql, est -t -K -q -L -C -D -r -e -z

Sécurité

      Le traqueur est un outil d’administration. Son utilisation doit être encadrée. Il peut être utilisé avec l'utilisateur postgres ou un autre superuser (appelé par exemple traqueur).
      Il n’est pas recommandé de le connecter directement à vos bases de production, spécialement si vous utilisez l’option -p entraînant la création de l’extension plpythonu dans pg_catalog. La recommandation est donc de créer une base dédiée dans le cluster à analyser et de révoquer tous les droits publics sur cette base. Cette base est par défaut appelée traqueur. Si vous choisissez un autre nom, cela doit par la suite être indiqué au traqueur via la variable d'environnement $TRAQUEUR_DATABASE. En dehors de plpythonu, les extensions éventuellement utilisées par le traqueur (bloom, postgres_fdw) sont créées dans un schéma appelé par défaut traqueur (cela peut être changé via la variable d'environnement $TRAQUEUR_EXTENSIONS_SCHEMA)
      Si vous utilisez le traqueur en mode batch (option -b) et que vous comptez effectuer du reporting sur les données d’analyse, il est recommandé de créer un utilisateur dédié. Cet utilisateur doit uniquement avoir le droit de se connecter à la base du traqueur et de sélectionner sur les tables du schéma traqueur.
      La colonne query de traqueur_sessions_actives contient potentiellement des informations applicatives. S’il n’est pas acceptable qu’elles soient visualisées, il est recommandé de se servir de l’option -n de normalisation des requêtes, de restreindre l’accès à la colonne query et de se servir de la colonne tquery contenant les requêtes normalisées.
      Si vous souhaitez davantage d'informatons, consultez cette page complémentaire sur la sécurité.

Overhead et performance

      En mode interactif, le traqueur a été testé pour donner rapidement des informations pertinentes avec plusieurs centaines de sessions ACTIVES simultanément, même sur un problème de verrouillage. Le mot ACTIVES est important, des dizaines de milliers de sessions pourraient être connectées, le traqueur ne s’intéresse de toute façon qu’à celles qui travaillent ou attendent.
      Quelle que soit la version de PostgreSQL, le traqueur utilise par défaut des tables temporaires en mode interactif et unlogged en mode batch. Il n’y a donc pas de suractivité de journalisation à craindre. Par ailleurs, afin de minimiser les écritures, une indexation légère est mise en place : BRIN sur les colonnes temporelles, BLOOM sur les colonnes entières et textes, GIST sur le document éventuellement créé pour la recherche plein texte. Il est aussi possible de stocker les informations de traque dans un cluster différent du cluster à analyser.
      Les index BRIN sont apparus en 9.5 et les index BLOOM en 9.6. Si vous utilisez le traqueur en mode batch avec une version de PostgreSQL 9.3, 9.4 et dans une moindre mesure 9.5, que vous conservez un volume de données de traque assez important et que vous faites du reporting direct sur ces données, vous serez probablement amenés à créer manuellement des index B-Tree sur diverses colonnes, à commencer par les colonnes dtcol des différentes tables.

Mise à jour

      La mise à jour consiste essentiellement à écraser le fichier du traqueur avec un fichier de version plus récente.
      Si vous utilisez le mode batch (option -b), il peut être nécessaire après mise à jour d’exécuter une première fois le script avec l’option -z avant de le refaire tourner en tâche de fond (nohup) avec vos options habituelles.

Feuille de route

      Sur le long terme, le but sera toujours de maintenir la compatibilité avec toutes les versions supportées de PostgreSQL.
      Une version majeure de PostgreSQL est supportée 5 ans, une nouvelle version sort tous les ans donc le traqueur devrait toujours au minimum couvrir 5 versions.
      Lors de l'arrêt du support d'une version de PostgreSQL, la dernière version du traqueur compatible avec elle est laissée en ligne mais n'est plus maintenue.
      La traqueur a initialement été développé pour PostgreSQL 9.6. Il a ensuite été adapté pour PostgreSQL 9.3, 9.4 et 9.5 mais reste plus performant et pertinent avec PostgreSQL 9.6 et versions supérieures. La version minimale supportée est à présent la 12.

Traduction

      Si la variable LANG est positionnée sur fr* (exemple fr_FR.UTF-8), alors les messages et sections des rapports sont en français, dans tous les autres cas en anglais.

Premier exemple d’utilisation

      Une utilisatrice se plaint, elle n’arrive pas à travailler sur l’application REUNION. Elle a le "sablier" dès la connexion ! Vous êtes surpris, personne ne travaille sur cette application qui n’est pas encore en production.
      Vous faites tourner le traqueur sans option particulière :

postgres@srvdeb12:~$ ./traqueur.sh traqueur 7.01.02 - https://pgphil.ovh - outil de diagnostic performance pour PostgreSQL version 12 => 16 INFORMATION, pas de base de connexion indiquee, utilisation de la base dediee detectee ... INFORMATION, connecte a la base traqueur INFORMATION, version de PostgreSQL detectee : 130009 INFORMATION, preparation de la collecte ... INFORMATION, execution de la collecte et presentation des resultats ... busy_pc | distinct_exe | db | pid | pg_user | client_ip | application | query | wait_event_type | blocker ---------+--------------+----------+-------+----------+--------------+--------------+------------------------------------------------------------------+-----------------+--------- 100 | 1 / 50 | reunion | 3791 | recette | XXX.XXX.XX.X | REUNION | INSERT INTO logging VALUES(current_timestamp, 'CONNEXION', 100); | Lock | {3794} 58 | 1 / 29 | postgres | 3794 | postgres | | psql | vacuum full verbose logging; | | 32 | 1 / 16 | postgres | 3794 | postgres | | psql | vacuum full verbose logging; | LWLock | 10 | 1 / 5 | postgres | 3794 | postgres | | psql | vacuum full verbose logging; | IO | (4 lignes)

      Environ 5 secondes plus tard vous obtenez ce résultat. Comment l’interpréter ? La session de l’utilisatrice connectée depuis le programme REUNION est active à 100% donc le temps d’attente se situe bien "niveau base". Elle est en fait bloquée par une autre session portant le numéro de process 3794. Cette session provient du serveur lui-même, elle réalise un VACUUM FULL sur la table logging. Il ne peut s’agir que d’un de vos collègues du service informatique car PostgreSQL ne lance pas de VACUUM FULL mais des VACUUM classiques via AUTOVACUUM.
      Le diagnostic est posé, place à l’action. Vous allez voir dans le bureau d’à côté et, effectivement, Francis a lancé cette opération. Il voulait gagner de la place après un gros DELETE sans savoir que cette action pouvait avoir des conséquences. Mauvaise pioche de sa part mais il interrompt l’opération et tout rentre dans l’ordre.
      Bien sûr, le traqueur ne se limite pas au diagnostic de problèmes aussi simples : découvrez des cas d’utilisation avancés.

Mise à jour : 21/12/2023