Traqueur : introduction, installation, utilisation basique

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

Téléchargement du traqueur pour PostgreSQL 9.3, 9.4, 9.5, 9.6, 10 et 11
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.

Prérequis, installation

      J’utilise principalement le traqueur avec le backport de PostgreSQL 9.6 sous Debian 8 "Jessie" ainsi que PostgreSQL 9.6 et 10 sous Debian 9 "Stretch" .
      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 Solaris 11.3, AIX 7.1 et 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 moyenne 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 9 "Strech", qui est la version stable de Debian depuis juin 2017, tout cela s’installe en moins de 5 minutes. Je suppose que PostgreSQL 9.6 est déjà installé et un cluster créé.

-- connecte root root@srvdeb96:~# cat /etc/*lease PRETTY_NAME="Debian GNU/Linux 9 (stretch)" ... root@srvdeb96:~# cat /etc/debian_version 9.1 root@srvdeb96:~# apt-get install postgresql-plpython ... root@srvdeb96:~# apt-get install python-psutil ... root@srvdeb96:~# apt-get install gcc ... root@srvdeb96:~# apt-get install bonnie++ ... -- connecte postgres postgres@srvdeb96:~$ psql psql (9.6.4) Saisissez " help " pour l'aide. postgres=# select version(); version --------------------------------------------------------------------------------------------------- PostgreSQL 9.6.4 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit (1 ligne) -- 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@srvdeb96:~$ chmod 700 traqueur.sh postgres@srvde96:~$ ./traqueur.sh -h traqueur 2.01.00 - outil de diagnostic performance pour PostgreSQL version 9.3 => 11 usage: traqueur.sh [-h] [ -v niveau ] [ -a ] [ -l ] [-c parametres de connexion] [ -r ] [ -m fenetre ] [ -z ] [ -s delai ] [ -t ] [ -b L|U|F ] [ -e L|U ] [ -j ] [-d duree ] [ -w pids ] [ -n ] [ -f ] [ -g "commentaire" ] [ -p ] [ -i profondeur ] [ -o colonnes ] [ -x ] [ -C ] [ -D ] [ -F informations ] [ -A role ] [ -R role ] [ -S schema ] [ -N ] -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) -a : effectue un test CPU/RAM et sort, necessite gcc et une execution du traqueur depuis le serveur postgresql. Utilise 600M dans $TRAQUEUR_W (/tmp par defaut si la variable $TRAQUEUR_W n'est pas positionnee) et beaucoup de ressources CPU (1 thread) -l : effectue un test de latence I/O et sort, necessite bonnie++ v1.97+ et une execution du traqueur depuis le serveur PostgreSQL. Utilise 600M dans $PGDATA/base (/var/lib/postgresql/9.6/main/base par defaut si la variable $PGDATA n'est pas positionnee) et beaucoup de ressources I/O -c parametres : parametres de connexion passes au programme psql -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 -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 -t : active le chronometrage par requete de psql pendant la phase de collecte et la presentation des resultats -b L|U|F : 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 cluster PostgreSQL en version 9.5 ou superieure et un repository cree avec l'option -C dans la meme version majeure de PostgreSQL -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 et necessite un cluster PostgreSQL en version 9.5 ou superieure -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. Necessite un cluster PostgreSQL en version 10 ou superieure -d duree : valeur entiere, duree minimale 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 inferieure a la duree minimale -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'" -n : normalise les requetes lors de la collecte, remplacement des valeurs numeriques par 0, des valeurs chaines par '' et des listes par (...) -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 -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), "iquery" (valeur de hachage de la requete), "tquery" (requete normalisee), "itquery" (valeur de hachage de la requete normalisee). Ce choix se substitue au choix par defaut qui peut par ailleurs etre demande explicitement avec -o "defaut". L'option -o peut etre renseignee plusieurs fois afin d'agreger de differentes manieres les resultats de la collecte -x : active l'affichage psql etendu, prise en compte lors de l'affichage des resultats -C : cree un repository, un schema pouvant etre utilise pour stocker les donnees collectes en mode batch, cree un utilisateur avec les privileges select et insert sur les tables du repository et sort. Necessite un cluster en version 9.5 ou superieure -D : supprime le schema du repository, supprime l'utilisateur ayant les privileges select et insert sur les tables du repository 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 priorite : l'ordre de priorite des options speciales, entrainant une sortie du script sans collecte d'informations sur l'activite de postgresql, est -a -l -C -D -r -e -m -z

Sécurité

      Le traqueur est un outil d’administration. Son utilisation doit être encadrée.
      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. La recommandation est donc de créer une base traqueur dans le cluster à analyser et de révoquer tous les droits publics sur cette base.
      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.

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

Traduction

      Originellement uniquement disponible en français, le traqueur est traduit en anglais depuis la 0.12.02.
      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@srvdeb10:~$ ./traqueur.sh traqueur 2.01.00 - outil de diagnostic performance pour PostgreSQL 9.3 => 11 INFORMATION, pas d'options de connexions indiquees, utilisation de la base traqueur detectee ... INFORMATION, connecte a la base traqueur INFORMATION, version de PostgreSQL detectee : 100000 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 : 25/11/2017