Mettre à jour PostgreSQL pour améliorer les performances

Chaque version majeure apporte des avancées...majeures

      Tout changement doit faire l’objet de tests de non régression. Mais, avec PostgreSQL, le risque est souvent payant au niveau des performances. Nous allons ici nous intéresser au cas d’une requête impliquant une jointure par hachage (HASH JOIN) avec 3 versions majeures de PostgreSQL (9.6, 10 et 11 devel)
      Les tables considérées sont des classiques du site, GEANTS et LANCERS. Elles sont ici de taille similaire. Un filtrage sur GEANTS est effectué mais la sélectivité est bien moins bonne que prévue car les colonnes utilisées sont fonctionnellement dépendantes.
      Un cadre commun est fixé pour l’ensemble des tests :

cat /etc/debian_version 9.3 cat /proc/cpuinfo | grep "model name" model name : Intel(R) Xeon(R) CPU E5-2603 v2 @ 1.80GHz model name : Intel(R) Xeon(R) CPU E5-2603 v2 @ 1.80GHz model name : Intel(R) Xeon(R) CPU E5-2603 v2 @ 1.80GHz model name : Intel(R) Xeon(R) CPU E5-2603 v2 @ 1.80GHz cat /proc/meminfo | grep MemTotal MemTotal: 4100128 kB show max_parallel_workers_per_gather; max_parallel_workers_per_gather --------------------------------- 2 (1 ligne) show shared_buffers; shared_buffers ---------------- 128MB (1 ligne) show effective_cache_size; effective_cache_size ---------------------- 4GB (1 ligne) show work_mem; work_mem ---------- 4MB (1 ligne) create table geants( idg serial primary key, genre char(1), taille smallint, masse smallint, actif boolean, devise varchar(128), pw smallint, heureux boolean, couleur varchar(8), veteran boolean, clan smallint, gabarit varchar(8), revenu integer, pm smallint, berserk boolean, tutelaire smallint, ere varchar(10), cyclope boolean); CREATE TABLE WITH serie(i, r1) AS (SELECT generate_series(1,10000000,1), random()) insert into geants(genre, taille, masse, actif, devise, pw, heureux, couleur, veteran, clan, gabarit, revenu, pm, berserk, tutelaire, ere, cyclope) select case when random() < 0.45 then 'M' else 'F' end, 200 + (trunc(random() * 200 + 1)), 300 + (trunc(random() * 200 + 1)), case when random() < 0.01 then false when random() > 0.5 and random() < 0.99 then false else true end, upper(md5(random()::text)), (trunc(random()*100 + 1)), case when random() < 0.1 then false else true end, case when r1 <= 0.29998 then 'GRIS' when r1 <= 0.59998 then 'NOIR' else 'BLEU' end, case when random() < 0.9 then false else true end, (trunc(random()*1000 + 1)), case when r1 <= 0.29999 then 'PETIT' when r1 <= 0.59999 then 'MOYEN' else 'GRAND' end, (trunc(random()*1000000 + 1)), (trunc(random()*10 + 1)), case when r1 <= 0.3 then true when r1 <= 0.6 then false else null end, (trunc(random()*10 + 1)), case when r1 <= 0.30001 then 'TAUREAU' when r1 <= 0.60001 then 'LICORNE' else 'DRAGON' end, case when r1 <= 0.30002 then true when r1 <= 0.60002 then false else null end from serie; INSERT 0 10000000 create table lancers(dtl timestamp, idg integer, perf integer); CREATE TABLE WITH serie(i) AS (SELECT generate_series(25000000,1,-1)) insert into lancers(dtl, idg, perf) select current_timestamp - (i || ' minutes')::interval, trunc(random() * 10000000 + 1), case when random() <= 0.0001 then 50000 + trunc(random() * 50000 + 1) else trunc(random() * 50000 + 1) end from serie; INSERT 0 25000000 analyze geants; ANALYZE analyze lancers; ANALYZE \d+ Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+----------------+----------+--------------+------------+------------- public | geants | table | postgres | 1116 MB | public | geants_idg_seq | séquence | postgres | 8192 bytes | public | lancers | table | postgres | 1056 MB | (3 lignes)


      RAS c’est du classique. Utilisons cet environnement pour tester une charge comprenant une seule requête avec PostgreSQL 9.6 et effectuer son analyse avec le traqueur :

select version(); version --------------------------------------------------------------------------------------------------- PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit (1 ligne) explain select g.idg, g.devise, avg(l.perf) from geants g join lancers l on (g.idg = l.idg) where g.couleur = 'GRIS' and g.gabarit = 'PETIT' and g.ere = 'TAUREAU' and berserk = true and cyclope = true group by g.idg, g.devise order by 3 desc fetch first 3 rows only; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=706888.80..706888.81 rows=3 width=69) -> Sort (cost=706888.80..706950.14 rows=24536 width=69) Sort Key: (avg(l.perf)) DESC -> GroupAggregate (cost=705804.93..706571.68 rows=24536 width=69) Group Key: g.idg -> Sort (cost=705804.93..705958.28 rows=61340 width=41) Sort Key: g.idg -> Hash Join (cost=219535.40..699037.00 rows=61340 width=41) Hash Cond: (l.idg = g.idg) -> Seq Scan on lancers l (cost=0.00..385137.60 rows=25000160 width=8) -> Hash (cost=219228.70..219228.70 rows=24536 width=37) -> Gather (cost=1000.00..219228.70 rows=24536 width=37) Workers Planned: 2 -> Parallel Seq Scan on geants g (cost=0.00..215775.10 rows=10223 width=37) Filter: (berserk AND cyclope AND ((couleur)::text = 'GRIS'::text) AND ((gabarit)::text = 'PETIT'::text) AND ((ere)::text = 'TAUREAU'::text)) (15 lignes) -- demo01.sql set application_name=demo01; select g.idg, g.devise, avg(l.perf) from geants g join lancers l on (g.idg = l.idg) where g.couleur = 'GRIS' and g.gabarit = 'PETIT' and g.ere = 'TAUREAU' and berserk = true and cyclope = true group by g.idg, g.devise order by 3 desc fetch first 3 rows only; -- demo01.sh time ./traqueur.sh -d 60 -p -o "pid, substr(query, 1, 64)" -o "wait_event_type" -o "wait_event_type, wait_event" -w "select pid from pg_stat_activity where application_name = 'demo01'" & time psql -f "demo01.sql" & wait -- demo01.sh a ete executee plusieurs fois avant cette execution pour mettre les donnees en cache ./demo01.sh traqueur 2.03.00 - outil de diagnostic performance pour PostgreSQL 9.3 => 11 SET INFORMATION, pas d'options de connexions indiquees, utilisation de la base traqueur detectee ... INFORMATION, connecte a la base traqueur INFORMATION, version de PostgreSQL detectee : 90606 INFORMATION, preparation de la collecte ... INFORMATION, execution de la collecte et presentation des resultats ... idg | devise | avg ---------+----------------------------------+-------------------- 5312509 | 5BF343B0868129279CCD20FDD2B0F99A | 99598.000000000000 6162793 | A70EB0C56731E6D109EE6B9497E1A8F6 | 97807.000000000000 3098880 | 915FE00B25A6D296D6F3D1433758538F | 96939.000000000000 (3 lignes) real 0m40,438s user 0m0,088s sys 0m0,036s busy_pc | distinct_exe | pid | substr | non_cpu_pc | mem | swapped ---------+--------------+------+------------------------------------------------------------------+------------+---------+--------- 64 | 1 / 382 | 2662 | select g.idg, g.devise, avg(l.perf) from geants g join lancers l | 1 | 5772 kB | 0 bytes (1 ligne) busy_pc | distinct_exe | wait_event_type | non_cpu_pc | mem | swapped ---------+--------------+-----------------+------------+---------+--------- 64 | 1 / 382 | | 1 | 5772 kB | 0 bytes (1 ligne) busy_pc | distinct_exe | wait_event_type | wait_event | non_cpu_pc | mem | swapped ---------+--------------+-----------------+------------+------------+---------+--------- 64 | 1 / 382 | | | 1 | 5772 kB | 0 bytes (1 ligne) Charge CPU moyennne globale (hors traqueur) --------------------------------------------- 18 (1 ligne) Temps_CPU_User_en_s | Temps_CPU_Systeme_en_s | Temps_CPU_Idle_en_s | Temps_IO_en_s ---------------------+------------------------+---------------------+--------------- 38 | 9 | 189 | 11 (1 ligne) real 1m2,943s user 0m0,540s sys 0m0,308s


      Quels enseignements ? Nous avons le HASH JOIN attendu entre GEANTS et LANCERS. Le planner choisit d’effectuer une exécution en parallèle du balayage de la table GEANTS et le reste est effectué en série.
      Le traqueur donne ensuite des informations suffisantes pour cerner ce qui se passe mais parfois de manière indirecte. Nous avons bien sûr le texte de la requête même si je n’ai affiché que le début pour la lisibilité. Nous savons qu’une seule exécution de cette requête a été détectée (distinct_exe 1/XXX). Nous pouvons déduire la durée effective de l’exécution de la requête, environ 40 secondes (0.64 X 63).
      Mais nous n’avons pas d’informations dans cette version de postgres sur les travailleurs parallèles au niveau de pg_stat_activity et donc au niveau du traqueur.
      Le croisement avec les informations système donne toutefois quelques informations supplémentaires car l’activité de demo01.sh était la seule sur la machine virtuelle. En considérant les temps cumulés CPU user (38s), système (8s) et IO (12s), il est possible de déduire que le travail en parallèle n’a concerné qu’une toute petite partie du travail. Si 3 process avaient effectivement travaillé en parallèle pendant un temps elapsed de 40s alors il y aurait autour de 120s de temps pour ce travail et c’est loin d’être le cas.

      Que donne PostgreSQL 10 à présent ? Démonstration :

select version(); version --------------------------------------------------------------------------------------------------------------------------- PostgreSQL 10.1 on x86_64-pc-linux-gnu (Debian 10.1-2.pgdg90+1), compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit (1 ligne) create statistics geants_couleur_gabarit_ere_berserk_cyclope(dependencies, ndistinct) on couleur, gabarit, ere, berserk, cyclope from geants; CREATE STATISTICS analyze geants; ANALYZE analyze lancers; ANALYZE \d+ Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+----------------+----------+--------------+------------+------------- public | geants | table | postgres | 1116 MB | public | geants_idg_seq | séquence | postgres | 8192 bytes | public | lancers | table | postgres | 1056 MB | (3 lignes) \x Affichage étendu activé. -[ RECORD 1 ]- stxrelid | 1426067 stxname | geants_couleur_gabarit_ere_berserk_cyclope stxnamespace | 2200 stxowner | 10 stxkeys | 9 12 15 17 18 stxkind | {d,f} stxndistinct | {"9, 12": 4, "9, 15": 4, "9, 17": 4, "9, 18": 4, "12, 15": 4, "12, 17": 4, "12, 18": 4, "15, 17": 3, "15, 18": 3, "17, 18": 3, "9, 12, 15": 5, "9, 12, 17": 5, "9, 12, 18": 5, "9, 15, 17": 4, "9, 15, 18": 4, "9, 17, 18": 4, "12, 15, 17": 4, "12, 15, 18": 4, "12, 17, 18": 4, "15, 17, 18": 3, "9, 12, 15, 17": 5, "9, 12, 15, 18": 5, "9, 12, 17, 18": 5, "9, 15, 17, 18": 4, "12, 15, 17, 18": 4, "9, 12, 15, 17, 18": 5} stxdependencies | {"9 => 12": 0.601100, "9 => 15": 0.601100, "9 => 17": 0.601100, "9 => 18": 0.601100, "12 => 9": 0.700300, "12 => 15": 0.601133, "12 => 17": 0.601133, "12 => 18": 0.601133, "15 => 9": 0.700267, "15 => 12": 0.700267, "15 => 17": 1.000000, "15 => 18": 1.000000, "17 => 9": 0.700267, "17 => 12": 0.700267, "17 => 15": 1.000000, "17 => 18": 1.000000, "18 => 9": 0.700267, "18 => 12": 0.700267, "18 => 15": 1.000000, "18 => 17": 1.000000, "9, 12 => 15": 0.601133, "9, 12 => 17": 0.601133, "9, 12 => 18": 0.601133, "9, 15 => 12": 0.999933, "9, 15 => 17": 1.000000, "9, 15 => 18": 1.000000, "9, 17 => 12": 0.999933, "9, 17 => 15": 1.000000, "9, 17 => 18": 1.000000, "9, 18 => 12": 0.999933, "9, 18 => 15": 1.000000, "9, 18 => 17": 1.000000, "12, 15 => 9": 0.700300, "12, 15 => 17": 1.000000, "12, 15 => 18": 1.000000, "12, 17 => 9": 0.700300, "12, 17 => 15": 1.000000, "12, 17 => 18": 1.000000, "12, 18 => 9": 0.700300, "12, 18 => 15": 1.000000, "12, 18 => 17": 1.000000, "15, 17 => 9": 0.700267, "15, 17 => 12": 0.700267, "15, 17 => 18": 1.000000, "15, 18 => 9": 0.700267, "15, 18 => 12": 0.700267, "15, 18 => 17": 1.000000, "17, 18 => 9": 0.700267, "17, 18 => 12": 0.700267, "17, 18 => 15": 1.000000, "9, 12, 15 => 17": 1.000000, "9, 12, 15 => 18": 1.000000, "9, 12, 17 => 15": 1.000000, "9, 12, 17 => 18": 1.000000, "9, 12, 18 => 15": 1.000000, "9, 12, 18 => 17": 1.000000, "9, 15, 17 => 12": 0.999933, "9, 15, 17 => 18": 1.000000, "9, 15, 18 => 12": 0.999933, "9, 15, 18 => 17": 1.000000, "9, 17, 18 => 12": 0.999933, "9, 17, 18 => 15": 1.000000, "12, 15, 17 => 9": 0.700300, "12, 15, 17 => 18": 1.000000, "12, 15, 18 => 9": 0.700300, "12, 15, 18 => 17": 1.000000, "12, 17, 18 => 9": 0.700300, "12, 17, 18 => 15": 1.000000, "15, 17, 18 => 9": 0.700267, "15, 17, 18 => 12": 0.700267, "9, 12, 15, 17 => 18": 1.000000, "9, 12, 15, 18 => 17": 1.000000, "9, 12, 17, 18 => 15": 1.000000, "9, 15, 17, 18 => 12": 0.999933, "12, 15, 17, 18 => 9": 0.700300} \x Affichage étendu désactivé. explain select g.idg, g.devise, avg(l.perf) from geants g join lancers l on (g.idg = l.idg) where g.couleur = 'GRIS' and g.gabarit = 'PETIT' and g.ere = 'TAUREAU' and berserk = true and cyclope = true group by g.idg, g.devise order by 3 desc fetch first 3 rows only; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=876384.98..876384.99 rows=3 width=69) -> Sort (cost=876384.98..876901.51 rows=206610 width=69) Sort Key: (avg(l.perf)) DESC -> Finalize GroupAggregate (cost=817689.78..873714.59 rows=206610 width=69) Group Key: g.idg -> Gather Merge (cost=817689.78..869065.86 rows=413220 width=69) Workers Planned: 2 -> Partial GroupAggregate (cost=816689.76..820370.00 rows=206610 width=69) Group Key: g.idg -> Sort (cost=816689.76..817227.80 rows=215219 width=41) Sort Key: g.idg -> Hash Join (cost=322056.68..775868.25 rows=215219 width=41) Hash Cond: (l.idg = g.idg) -> Parallel Seq Scan on lancers l (cost=0.00..239303.33 rows=10416733 width=8) -> Hash (cost=317859.05..317859.05 rows=206610 width=37) -> Seq Scan on geants g (cost=0.00..317859.05 rows=206610 width=37) Filter: (berserk AND cyclope AND ((couleur)::text = 'GRIS'::text) AND ((gabarit)::text = 'PETIT'::text) AND ((ere)::text = 'TAUREAU'::text)) (17 lignes) -- demo01.sh, ajout de backend_type avec PostgreSQL 10 time ./traqueur.sh -d 60 -p -o "pid, backend_type, substr(query, 1, 64)" -o "wait_event_type" -o "wait_event_type, wait_event" -w "select pid from pg_stat_activity where application_name = 'demo01'" & time psql -f "demo01.sql" & wait ./demo01.sh traqueur 2.03.00 - outil de diagnostic performance pour PostgreSQL 9.3 => 11 SET INFORMATION, pas d'options de connexions indiquees, utilisation de la base traqueur detectee ... INFORMATION, connecte a la base traqueur INFORMATION, version de PostgreSQL detectee : 100001 INFORMATION, preparation de la collecte ... INFORMATION, execution de la collecte et presentation des resultats ... idg | devise | avg ---------+----------------------------------+-------------------- 4499073 | B46FCED8CA98516A46A56ACBB9425C4A | 99928.000000000000 2696199 | C20CDF8F985C4DDE8D0A4B373F631D0F | 99758.000000000000 8301498 | 05E61B95D5688A823410C5A2A64950EF | 99456.000000000000 (3 lignes) real 0m27,811s user 0m0,100s sys 0m0,028s busy_pc | distinct_exe | pid | backend_type | substr | non_cpu_pc | mem | swapped ---------+--------------+------+-------------------+------------------------------------------------------------------+------------+---------+--------- 42 | 1 / 254 | 1477 | client backend | select g.idg, g.devise, avg(l.perf) from geants g join lancers l | 3 | 5992 kB | 0 bytes 42 | 1 / 253 | 1479 | background worker | select g.idg, g.devise, avg(l.perf) from geants g join lancers l | 14 | 5272 kB | 0 bytes 42 | 1 / 253 | 1480 | background worker | select g.idg, g.devise, avg(l.perf) from geants g join lancers l | 14 | 5284 kB | 0 bytes (3 lignes) busy_pc | distinct_exe | wait_event_type | non_cpu_pc | mem | swapped ---------+--------------+-----------------+------------+---------+--------- 92 | 3 / 549 | | 6 | 5572 kB | 0 bytes 22 | 3 / 131 | IPC | 28 | 5283 kB | 0 bytes 11 | 3 / 68 | IO | 14 | 5519 kB | 0 bytes 2 | 3 / 12 | LWLock | 3 | 5517 kB | 0 bytes (4 lignes) busy_pc | distinct_exe | wait_event_type | wait_event | non_cpu_pc | mem | swapped ---------+--------------+-----------------+---------------------+------------+---------+--------- 92 | 3 / 549 | | | 6 | 5572 kB | 0 bytes 22 | 2 / 130 | IPC | MessageQueueSend | 28 | 5278 kB | 0 bytes 8 | 3 / 47 | IO | BufFileWrite | 18 | 5536 kB | 0 bytes 2 | 3 / 10 | IO | DataFileRead | 4 | 5562 kB | 0 bytes 2 | 3 / 11 | IO | BufFileRead | 3 | 5407 kB | 0 bytes 2 | 3 / 12 | LWLock | buffer_io | 3 | 5517 kB | 0 bytes 0 | 1 / 1 | IPC | MessageQueueReceive | 61 | 5992 kB | 0 bytes (7 lignes) Charge CPU moyennne globale (hors traqueur) --------------------------------------------- 29 (1 ligne) Temps_CPU_User_en_s | Temps_CPU_Systeme_en_s | Temps_CPU_Idle_en_s | Temps_IO_en_s ---------------------+------------------------+---------------------+--------------- 54 | 17 | 165 | 10 (1 ligne) real 1m3,469s user 0m0,604s sys 0m0,200s


      La version 10 a introduit les statistiques étendues donc nous pouvons informer le planner de la dépendance fonctionnelle entre les colonnes utilisées dans la clause de filtrage. La présence de colonnes de type booléen dans la liste entraîne un bug corrigé en 10.2 et 11 mais ce bug ne peut pas entraîner une régression par rapport à la 9.6, au pire une absence d’amélioration. Le plan d’exécution obtenu est en tous cas différent de celui que nous avions obtenu précédemment. C’est la table LANCERS qui est cette fois l’objet du parallélisme. La durée d’exécution passe de 40s en 9.6 à 27s en 10.
      Que dit le traqueur sur cette exécution ? Il donne toujours la durée horloge du travail, à noter qu’il faut bien sûr considérer uniquement le client principal désigné par client_backend pour évaluer le temps horloge donc cela donne autour de 27 secondes (0.42 X 63). Le traqueur donne toujours aussi le texte de la requête et le fait qu’une seule exécution a entraîné toutes les détections.
      La première vrai différence est que nous avons cette fois bien plus d’informations sur le travail en parallèle. Les travailleurs supplémentaires sont identifiables via une colonne de pg_stat_activty, backend_type. Nous pouvons constater que chacun des 2 travailleurs a été détecté quasi autant que le process principal (253 fois contre 254). Le travail en parallèle est donc cette fois très conséquent. Ce travail est confirmé par les chiffres système : temps CPU user 54s, temps CPU système 17s et temps IO 10s. Le gain en temps d’exécution de la requête est donc ici conditionné par la présence de ressources système disponibles.
      Autre point très important : la nature de l’activité. La 9.6 a apporté à pg_stat_activity les types d’événements d’attente (wait_event_type) et les événements d’attente détaillés (wait_event). Mais dans cette version les colonnes restaient souvent vides. En 10 ce n’est plus le cas. Nous pouvons constater la part de la communication entre les process (IPC), des I/O (lectures de fichiers de données, lecture et écritures de données dans le buffer de travail). Lorsque wait_event_type est à NULL, il est à présent de plus en plus possible d’en déduire que l’exécution est sur le CPU et n’attend pas.

      Qu’apporte en plus la version 11 à présent ? Démonstration :

select version(); version ----------------------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 11devel on x86_64-pc-linux-gnu (Debian 11~~devel~20171225.1713-1~276.gitff963b3.pgdg+1), compiled by gcc (Debian 7.2.0-18) 7.2.0, 64-bit (1 ligne) explain select g.idg, g.devise, avg(l.perf) from geants g join lancers l on (g.idg = l.idg) where g.couleur = 'GRIS' and g.gabarit = 'PETIT' and g.ere = 'TAUREAU' and berserk = true and cyclope = true group by g.idg, g.devise order by 3 desc fetch first 3 rows only; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=2094891.99..2094891.99 rows=3 width=69) -> Sort (cost=2094891.99..2102439.53 rows=3019018 width=69) Sort Key: (avg(l.perf)) DESC -> Finalize GroupAggregate (cost=1237228.40..2055871.74 rows=3019018 width=69) Group Key: g.idg -> Gather Merge (cost=1237228.40..1987943.84 rows=6038036 width=69) Workers Planned: 2 -> Partial GroupAggregate (cost=1236228.37..1290004.64 rows=3019018 width=69) Group Key: g.idg -> Sort (cost=1236228.37..1244090.40 rows=3144812 width=41) Sort Key: g.idg -> Parallel Hash Join (cost=241327.15..703351.73 rows=3144812 width=41) Hash Cond: (l.idg = g.idg) -> Parallel Seq Scan on lancers l (cost=0.00..239303.33 rows=10416733 width=8) -> Parallel Hash (cost=215775.10..215775.10 rows=1257924 width=37) -> Parallel Seq Scan on geants g (cost=0.00..215775.10 rows=1257924 width=37) Filter: (berserk AND cyclope AND ((couleur)::text = 'GRIS'::text) AND ((gabarit)::text = 'PETIT'::text) AND ((ere)::text = 'TAUREAU'::text)) ./demo01.sh traqueur 2.03.00 - outil de diagnostic performance pour PostgreSQL 9.3 => 11 SET INFORMATION, pas d'options de connexions indiquees, utilisation de la base traqueur detectee ... INFORMATION, connecte a la base traqueur INFORMATION, version de PostgreSQL detectee : 110000 INFORMATION, preparation de la collecte ... INFORMATION, execution de la collecte et presentation des resultats ... idg | devise | avg ---------+----------------------------------+-------------------- 654492 | B19BB5BAC5EBAF0F1F73DF2FCC7D2DDA | 98358.000000000000 2897338 | CE14A8B8279010C9E77D6B2DC969074D | 97200.000000000000 2403172 | 7AED796E8FEF856B2AA91FBFE4DD4CEE | 96971.000000000000 (3 lignes) real 0m20,558s user 0m0,088s sys 0m0,036s busy_pc | distinct_exe | pid | backend_type | substr | non_cpu_pc | mem | swapped ---------+--------------+------+-----------------+------------------------------------------------------------------+------------+---------+--------- 31 | 1 / 184 | 1769 | client backend | select g.idg, g.devise, avg(l.perf) from geants g join lancers l | 1 | 4820 kB | 0 bytes 30 | 1 / 181 | 1770 | parallel worker | select g.idg, g.devise, avg(l.perf) from geants g join lancers l | 20 | 4044 kB | 0 bytes 30 | 1 / 181 | 1771 | parallel worker | select g.idg, g.devise, avg(l.perf) from geants g join lancers l | 21 | 4216 kB | 0 bytes (3 lignes) busy_pc | distinct_exe | wait_event_type | non_cpu_pc | mem | swapped ---------+--------------+-----------------+------------+---------+--------- 65 | 3 / 390 | | 7 | 4431 kB | 0 bytes 17 | 2 / 104 | IPC | 45 | 4128 kB | 0 bytes 9 | 3 / 52 | IO | 3 | 4316 kB | 0 bytes (3 lignes) busy_pc | distinct_exe | wait_event_type | wait_event | non_cpu_pc | mem | swapped ---------+--------------+-----------------+------------------+------------+---------+--------- 65 | 3 / 390 | | | 7 | 4431 kB | 0 bytes 17 | 2 / 104 | IPC | MessageQueueSend | 45 | 4128 kB | 0 bytes 7 | 3 / 40 | IO | BufFileWrite | -0 | 4361 kB | 0 bytes 1 | 2 / 6 | IO | BufFileRead | 23 | 4101 kB | 0 bytes 1 | 3 / 6 | IO | DataFileRead | 3 | 4231 kB | 0 bytes (5 lignes) Charge CPU moyennne globale (hors traqueur) --------------------------------------------- 20 (1 ligne) Temps_CPU_User_en_s | Temps_CPU_Systeme_en_s | Temps_CPU_Idle_en_s | Temps_IO_en_s ---------------------+------------------------+---------------------+--------------- 42 | 10 | 195 | 3 (1 ligne) real 1m4,157s user 0m0,528s sys 0m0,380s


      Le plan est différent de celui obtenu en 10. Un commit (modification du code source PostgreSQL) d’Andres Freund du 21/12/2017 apporte le parallélisme intégral au niveau du HASH JOIN, depuis la constitution de la table de hachage jusqu’à la jointure en elle-même. Le résultat est probant dans cet exemple : l’exécution prend un peu plus de 20s en 11 contre 27 en 10 et 40 en 9.6.
      Que donne l’analyse avec le traqueur ? Les background workers de la 10 sont nommés parallel workers en 11. Sinon l’outil donne toujours les mêmes informations et permet de constater que les gains sont globaux sur ce cas favorable : moins de consommation mémoire par process, moins d’IO, moins de charge CPU etc. La 11 est encore en développement au 28/12/2017 et ne doit pas être utilisée en production mais elle présentera bien, elle aussi, des avancées majeures.

Conclusion

      Pour obtenir les meilleures performances et faciliter l’analyse de la charge, vous avez tout intérêt à utiliser les dernières versions de PostgreSQL. Ce SGBD évolue vite et bien et cette page n’a présenté que quelques évolutions récentes. Chaque version introduit des fonctionnalités majeures ET ces fonctionnalités évoluent ensuite au fil des versions. Ces changements sont quasi toujours positifs mais il convient cependant de comprendre leur nature et de les tester afin de les utiliser au mieux.

Mise à jour : 28/12/2017