DSC_2457, CC By-SA 2.0

Je parallélise, tu parallélises, ils parallélisent

Avant de rentrer plus en détail dans la parallélisation, il est nécessaire tout d’abord de comprendre comment est structuré le serveur PostgreSQL. Le serveur exécute les requêtes qui lui sont envoyées par les clients. Pour ce faire, il démarre des processus (nommés « workers »), 8 par défaut. Ils peuvent déjà exécuter en parallèle des requêtes envoyées par les clients. Ces requêtes sont exécutées de façon concurrente. Elles se partagent les ressources physiques du serveur: CPU, Mémoire, accès disque. Nous nous intéressons ici non pas à l’exécution en parallèle de requête concurrentes, mais à l’exécution en parallèle de sous-parties d’une même requête.

PostgreSQL contient un planificateur de requêtes. Il permet de déterminer une décomposition de la requête en une séquence d’instructions et de les ordonner en ce qui semble être le plus rapide à exécuter : c’est-à-dire dans quel ordre réaliser la récupération de données, le filtrage, par quelle table commencer lors d’une jointure, quel index utiliser… Ce planificateur peut choisir de découper le plan d’exécution en parties à traiter en parallèle lorsque cela est possible, tant que cela va permettre de produire un résultat plus rapidement et que les ressources du serveur le permettent.

Paralléliser des sous traitements n’est pas toujours plus rapide. Le découpage implique plus de traitements à exécuter : justement pour effectuer le découpage lui-même et rassembler les résultats des sous parties. Le nombre de traitements parallèles a également une influence. Si l’on découpe en trop de petites tâches, le travail pour les rassembler peut s’avérer trop long. Le traitement total serait alors moins rapide qu’un traitement sans parallélisation, ou plus consommateur de ressources.

Chaque partie parallélisée va entrer en concurrence pour les ressources physiques du serveur. Chaque sous partie ayant notamment besoin de mémoire pour travailler, la consommation totale de mémoire va être démultipliée et peut devenir un facteur limitant.

Workers disponibles pour les traitements

Les workers exécutent les requêtes SQL, mais aussi les sous parties de celles découpées pour être traitées en parallèles. Par défaut le serveur PostgreSQL vient avec un pool de 8 workers (mise à disposition de 8 processus pouvant exécuter des tâches quand elles se présentent). Ce nombre de workers est le paramètre de configuration max_worker_processes.

Parmi ces workers, on peut définir combien peuvent être utilisées pour traiter des requêtes en exécutions parallèles : max_parallel_workers. Ce ne sont pas des workers supplémentaires, ils sont pris dans le pool déjà défini. On permet juste d’utiliser plusieurs workers pour traiter une seule requête. Par défaut la valeur est également de 8, soit tous les workers.

Le paramètre le plus intéressant dans notre cas est max_parallel_workers_per_gather. Il définit le nombre maximum de workers qui peuvent être utilisés en parallèle pour diviser un traitement. Par défaut il n’est que de 2. Cette limite paraît raisonnable pour une exécution de requêtes envoyées par des clients dans le cadre d’une application en production. Toutefois si on est dans le cas d’exécution d’un traitement lourd d’un script SQL et sans concurrence avec d’autres utilisateurs, ce paramètre devient limitant. Il a l’avantage de pouvoir être modifié par le client directement en cours de sessions.

SET max_parallel_workers_per_gather TO 8;

Parcourir une table en parallèle

Par défaut le parcours d’une table se fait par accès séquentiel sur l’ensemble des enregistrements de la table. Ce parcours peut naturellement être découpé pour être traité par des workers en parallèle.

Pour décider s’il vaut mieux découper ou non la requête le planificateur va utiliser la configuration, notamment celle vue plus haut :

SHOW max_worker_processes;
SHOW max_parallel_workers;
SHOW max_parallel_workers_per_gather;

Il va également évaluer le coût du découpage du traitement à l’aide de deux variables. Leurs valeurs peuvent être changées en cours de session pour modifier le comportement du planificateur :

• parallel_setup_cost : représente le coût de mise en place d’un traitement parallèle supplémentaire,
• parallel_tuple_cost : représente le coût pour ramener un enregistrement vers le worker principal.

Si le coût d’exécution de la requête découpée est plus important que celui de l’exécution séquentielle, ou limité par la configuration du nombre de workers, ou du nombre de workers disponibles au moment de l’exécution, la requête ne va pas être parallélisée.

EXPLAIN SELECT id FROM osm_building_polygon;

 Gather  (cost=1000.00..5462168.55 rows=175225332 width=4)
   Workers Planned: 2
   ->  Parallel Seq Scan on osm_building_polygon  (cost=0.00..5461168.55 rows=73010555 width=4)

Deux scans en parallèle de la table.

UNION parallèle

La différence entre UNION et UNION ALL réside dans le fait que la variante ALL retourne l’ensemble des résultats des deux sous requêtes, alors que celle sans s’assure de l’unicité des résultats (par exemple en les triant). Pour rendre unique les éléments, il les faut tous. La variante avec ALL est donc plus rapide et plus simple. Elle se prête donc mieux à la parallélisation.

EXPLAIN
(SELECT id FROM osm_building_polygon)
UNION ALL
(SELECT osm_id FROM osm_building_polygon);

 Gather  (cost=1000.00..16618160.39 rows=350450664 width=8)
   Workers Planned: 2
   ->  Parallel Append  (cost=0.00..16617160.39 rows=350450664 width=8)
         ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..7651485.20 rows=175225332 width=8)
               ->  Parallel Seq Scan on osm_building_polygon  (cost=0.00..5461168.55 rows=73010555 width=4)
         ->  Parallel Seq Scan on osm_building_polygon osm_building_polygon_1  (cost=0.00..5461168.55 rows=73010555 width=8)

« Parallel Append » sur deux scans de tables.

Jointure parallèle

Les jointures en parallèles sont comme les parcours de tables. C’est le parcours de la première table qui est parallélisé, et non la boucle interne de parcours de la seconde table.

EXPLAIN
SELECT p.osm_id
FROM osm_building_polygon AS p
  JOIN osm_building_relation AS r ON
    p.geometry = r.geometry;

 Gather  (cost=1000.00..446312904880.54 rows=21535018077 width=8)
   Workers Planned: 2
   ->  Nested Loop  (cost=0.00..444159402072.84 rows=8972924199 width=8)
         Join Filter: (p.geometry = r.geometry)
         ->  Parallel Seq Scan on osm_building_relation r  (cost=0.00..4945.08 rows=51208 width=199)
         ->  Seq Scan on osm_building_polygon p  (cost=0.00..6483316.32 rows=175225332 width=160)

Découpage d’une jointure pour exécution en parallèle.

Requête impliquant une fonction

L’utilisation d’une fonction peut facilement empêcher l’exécution en parallèle. Les fonctions utilisateur doivent être explicitement déclarées compatibles avec l’exécution parallèle. Par défaut elles ne le sont pas.

CREATE OR REPLACE FUNCTION increment(i bigint) RETURNS bigint AS $$
    SELECT i + 1;
$$ LANGUAGE sql;

EXPLAIN SELECT increment(osm_id) FROM osm_building_polygon;

 Seq Scan on osm_building_polygon  (cost=0.00..133339.74 rows=3354619 width=8)

CREATE OR REPLACE FUNCTION increment(i bigint) RETURNS bigint AS $$
    SELECT i + 1;
$$ LANGUAGE sql
PARALLEL SAFE;

EXPLAIN SELECT increment(osm_id) FROM osm_building_polygon;

 Gather  (cost=0.00..108878.97 rows=3354619 width=8)
   Workers Planned: 2
   ->  Parallel Seq Scan on osm_building_polygon  (cost=0.00..108878.97 rows=1397758 width=8)

Notamment lors de l’utilisation de code SQL ancien il est pertinent de vérifier si les fonctions peuvent être passées en parallèle. C’est souvent le cas pour les fonctions simples et sans effet de bord. Il faut alors rajouter PARALLEL SAFE dans la signature pour bénéficier de la parallélisation.

Michael Trolove, CC By 2.0

Agréger en parallèle

Les fonctions utilisées peuvent également être des fonctions d’agrégats. Une agrégation consiste à calculer une valeur unique depuis un ensemble de valeurs issues de multiples enregistrements : par exemple les fonctions sum (somme), max, min, avg (moyenne) ou encore en non numérique string_agg ou array_agg (qui retourne un tableau d’éléments collectés depuis un ensemble d’enregistrements).

En interne un agrégat est composé de plusieurs choses. Par exemple pour la fonction d’agrégat max() :

• une variable d’accumulation, avec pour valeur initiale moins l’infini ;
• une fonction d’accumulation appelée pour chaque enregistrement à agréger : elle affecte à la variable d’accumulation la valeur la plus grande entre la variable d’accumulation et la nouvelle valeur à agréger ;
• une fonction finale : la valeur retournée par la fonction d’agrégation est la valeur de la variable d’accumulation.

Cependant lors d’exécutions en parallèles, chaque worker va calculer un agrégat partiel sur les données qu’il traite. Il va donc être nécessaire de rassembler ces valeurs d’agrégats partiels pour obtenir l’agrégat global.

Si dans le cas de la fonction max() la valeur de l’agrégat global est, de façon évidente, le maximum de l’ensemble des agrégats partiels, et donc en fait la même fonction, ce n’est pas systématiquement le cas. Pour la fonction max() le type valeur agrégé est le même que le type de la valeur retournée (des nombres).

Il est donc nécessaire d’avoir une fonction d’accumulation (SFUNC) et une fonction de combinaisons des agrégats partiels (COMBINEFUNC). La fonction de combinaison prend en entrée deux résultats d’agrégats partiels et les combines.

Cette méthode de parallélisation peut rappeler le processus mapReduce utilisé dans les bases de données NoSQL pour effectuer également des calculs en parallèle.

CREATE FUNCTION array_agg_append(accu int[], id int) RETURNS int[] AS $$
    SELECT array_append(accu, id);
$$
LANGUAGE SQL PARALLEL SAFE;

CREATE FUNCTION array_agg_combine(accu1 int[], accu2 int[]) RETURNS int[] AS $$
    SELECT accu1 || accu2; -- Concaténation de tableaux
$$
LANGUAGE SQL PARALLEL SAFE;

CREATE OR REPLACE AGGREGATE combinable_array_agg (int) (
    SFUNC = array_agg_append,
    COMBINEFUNC = array_agg_combine,
    STYPE = int[],
    PARALLEL = SAFE
);

Exemple d’une version combinable de la fonction d’agrégation array_agg().

Là encore il est nécessaire de vérifier que les agrégats utilisés supportent bien l’exécution en parallèle. C’est à dire qu’ils sont définis avec PARALLEL = SAFE et qu’ils ont bien une fonction de combinaison COMBINEFUNC = *.

À noter qu’un certain nombre d’agrégateurs PostGIS sont définis comme PARALLEL = SAFE mais sans COMBINEFUNC (ST_Collect() non combinable et ST_MemCollect() combinable). La combinaison des géométries en cascade rendrait les agrégateurs moins intéressant.

Il peut parfois être pertinent de changer la stratégie de la requête pour utiliser des agrégateurs combinables ou de définir ses propres agrégateurs avec une représentation interne adaptée pour pouvoir profiter de l’exécution en parallèle.

Tables temporaires

PostgreSQL ne supporte pas l’exécution parallèle avec les tables temporaires. Une alternative à la table temporaire peut être la table classique (assurée d’être stockée sur disque) mais avec l’option UNLOGGED. Comme ce n’est pas une table temporaire, il faudra explicitement la supprimer.

UNLOGGED désactive le log des modifications de la table. C’est ce qui permet de ne pas perdre de données en cas d’arrêt brutal ou de crash du serveur. Mais c’était déjà le cas des tables temporaires qui ne survivent pas à la fin de la session.

Une table UNLOGGED à également l’avantage d’être beaucoup plus rapide en écriture qu’une table classique. À noter toutefois, et en particulier pour les utilisateurs de PostGIS, qu’il n’est pas possible de créer un index GIST dessus (utilisé pour indexer des géométries).

CREATE UNLOGGED TABLE foobar(
    …
) ;

Conclusion

Rendre un traitement SQL avec PostgreSQL parallélisable n’est pas une tâche évidente. Cela nécessite des modifications assez faciles pour déclarer les fonctions parallélisables, mais ce n’est que la première étape. Cela requiert aussi de retoucher le code SQL pour lever les blocages en étudiant les plans d’exécution.

Toutefois des gains importants peuvent être obtenus pour des tâches SQL lourdes.

Vous souhaitez en apprendre plus sur PostgreSQL ou PostGIS par la pratique ? Nous proposons des formations PostgreSQL et PostGIS.