Traqueur : introduction, installation, utilisation basique

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

Téléchargement du traqueur pour PostgreSQL 9.6 et 10
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.0 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.3) Saisissez " help " pour l'aide. postgres=# select version(); version --------------------------------------------------------------------------------------------------- PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit -- optionnel mais recommandé postgres=# CREATE DATABASE traqueur; CREATE DATABASE postgres=# \q -- transfert de traqueur.sh sur le serveur puis : postgres@srvdeb96:~$ chmod 700 traqueur.sh postgres@srvde96:~$ ./traqueur.sh -h traqueur 0.10.02beta - outil de diagnostic performance pour PostgreSQL 9.6, 10 usage: traqueur.sh [-h] [ -v niveau ] [ -a ] [ -l ] [-c parametres de connexion] [ -r ] [ -m fenetre ] [ -z ] [ -s delai ] [ -t ] [ -b L|U ] [ -e L|U ] [-d duree ] [ -w pids ] [ -n ] [ -f ] [ -p ] [ -o colonnes ] [ -x ] -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 -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 de connexion, arguments 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 supprime toutes les donnees) et sort -z : supprime en base les tables creees en mode batch et sort, les extensions ne sont pas supprimees -s delai : delai minimal en centiemes de secondes entre 2 interrogations de pg_stat_activity lors de la collecte (par defaut 10, minimum 1). Avec -p le delai effectif sera toujours > 0.1s -t : active le chronometrage par requete de psql pendant la phase de collecte et la presentation des resultats -b L|U : 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 -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 -d duree : entier positif, duree minimale de la partie analyse du script en secondes (jours en mode batch) -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 -p : collecte d'informations sur l'activite CPU et la consommation memoire, necessite le module python psutil (version >= 4 pour les infos memoire) -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), "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 -x : active l'affichage psql etendu, prise en compte lors de l'affichage des resultats 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 -r -e -m -z

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.

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 par défaut, le cluster PostgreSQL est ici en version 10 beta :

postgres@srvdeb10:~$ ./traqueur.sh traqueur 0.10.02beta - outil de diagnostic performance pour PostgreSQL 9.6, 10 INFORMATION, pas d'options de connexions indiquees, utilisation de la base traqueur detectee ... 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 : 24/07/2017