LOXODATA

Profilage de fonctions PL/PgSQL

2017-01-26   709 mots, 4 minutes de lecture   Sébastien Lardière

Profilage de fonctions PL/PgSQL

Contexte

Le langage PL/PgSQL est présent depuis près de 20 ans dans PostgreSQL.

Il ajoute des instructions procédurales au langage SQL. On peut ainsi écrire des procédures dans une base PostgreSQL.

Ses structures de contrôle sont très communes pour un développeur. Il intègre parfaitement bien le langage SQL, ce qui en fait un outil idéal lorsqu’un développeur veut implémenter ses algorithmes au plus proche des données.

Lorsqu’on rencontre des problèmes de performances avec une requête SQL, on peut utiliser la commande EXPLAIN afin d’identifier les éléments les plus lents.

Mais des problèmes de performance peuvent survenir avec une fonction PL/PgSQL, et EXPLAIN ne permet pas d’entrer dans le code de la fonction.

Le créateur du langage PL/PgSQL, Jan Wieck, a publié très récemment un profiler, qui permet d’obtenir le détail de l’exécution de fonctions PL/pgSQL.

Cet outil est composé de deux parties :

  • un module chargé dans l’instance PostgreSQL, ainsi que l’extension correspondante ;
  • un programme client chargé de lancer les fonctions et de produire un rapport de profilage.

Suivant les indications de l’auteur même, cette extension n’est pas utilisable sur un système de production : il est préférable de n’utiliser le profiler que sur un environnement de développement dédié.

Le profiler permet de comprendre dans le détail l’exécution d’une fonction, avec des indicateurs précis, pour chaque ligne de code :

  • nombre de passages par la ligne ;
  • temps total passé (incluant le code imbriqué) sur cette ligne ;
  • temps maximal unitaire sur la ligne.

Rapport plprofiler

Dans cette capture d'écran, on aperçoit le nombre d’exécutions du code IF ..., et on comprend, avec le nombre d’appels aux instructions imbriquées, combien de fois les conditions du IF ont été vraies.

Installation du profiler

L’outil étant récent, il n’existe pas encore de paquet binaire, il est donc nécessaire de procéder à l’installation depuis le code source de l’outil. Pour cela, plusieurs outils, compilateurs et bibliothèques sont nécessaires.

Une fois téléchargé le code source de l’extension, à partir de https://bitbucket.org/openscg/plprofiler/downloads?tab=tags, les procédures suivantes permettent la compilation de l’extension et du programme client :

Mise à jour du 14 février 2020 : le projet est désormais accessible à l’adresse suivante : https://github.com/bigsql/plprofiler/

Pour un système RedHat ou Centos :

sudo yum install postgresql96-devel gcc python-setuptools python-devel perl
unzip openscg-plprofiler-fd05b0005ec2.zip
mv openscg-plprofiler-fd05b0005ec2 openscg-plprofiler
cd openscg-plprofiler
sudo PATH=/usr/pgsql-9.6/bin/:$PATH make USE_PGXS=1 install
cd python-plprofiler/
sudo PATH=/usr/pgsql-9.6/bin/:$PATH python setup.py install

Pour un système Debian ou Ubuntu

sudo apt-get install postgresql-server-dev-9.6 gcc python-setuptools python-dev perl
unzip openscg-plprofiler-fd05b0005ec2.zip
mv openscg-plprofiler-fd05b0005ec2 openscg-plprofiler
cd openscg-plprofiler
sudo PATH=/usr/lib/postgresql/9.6/bin/:$PATH make USE_PGXS=1 install
cd python-plprofiler/
sudo PATH=/usr/lib/postgresql/9.6/bin/:$PATH python setup.py install

Utilisation

Une fois les fichiers installés, il est nécessaire de créer l’extension dans la base de données :

psql -Upostgres -d <dbname> -c 'create extension plprofiler'

Pour illustrer le profilage d’une fonction, l’exemple suivant crée des boucles de code en fonction des paramètres en entrée :

create or replace function looploop( in i integer, in n integer )
returns integer
language plpgsql
as $$
declare
        y int;
        z int;
begin

        for y in select x from generate_series( 0 , i ) x
        loop
                for z in select w from generate_series( 0, n ) w
                loop
                        perform y * z ;
                        if ( (y * z) % 10 = 5 )
                        then
                                perform pg_sleep ( 1 );
                        end if;
                end loop;
        end loop;
        return i ;
end;
$$;

Une fois la fonction créée dans la base de données, il est possible de l’appeler, via l’ordre SQL select looploop(2,2) en utilisant le programme client plprofiler :

psql -Upostgres -d test -f looploop.sql
plprofiler run -U postgres -d test -c "select looploop(2,2)" --output=looploop.html \
                  --name=LoopLoop --title=LoopLoop --desc="Loop Loop function call"

Le fichier looploop.html doit alors être ouvert dans un navigateur Web.

Rapport plprofiler

Le rapport permet d’accéder au détail de la fonction, où l’on distingue chaque ligne de code, avec, à chaque fois que c’est pertinent, des informations chiffrées :

  • exec_count : Nombre d’exécution du code ;
  • total_time : temps total passé à cet endroit du code ;
  • longest_time : temps unitaire le plus long.

Profilage d’une instance PostgreSQL

Un prochain article abordera la possibilité de suivre une instance entière, au lieu d’une simple commande. Ce mode est particulièrement intéressant lorsqu’on souhaite réaliser des benchmarks d’applications, et ainsi obtenir des métriques réalistes sur nos fonctions PL/pgSQL.