Blog

le 11/09/2017
Matthieu
Données / Sécurité, PostgreSQL

PostgreSQL 9.6 : Introduction au parallélisme

Avec sa version 9.6, PostgreSQL introduit pour la première fois la notion de parallélisme au sein de son moteur de base de données.
Bien qu’assez limité comparativement aux fonctionnalités similaires de ses concurrents (Oracle et SQL Server notamment), cette nouveauté apporte tout de même son lot de fonctionnalités qu’il va être intéressant de surveiller.
La documentation officielle complète est à retrouver ici (https://www.postgresql.org/docs/9.6/static/parallel-query.html) et dans le cadre de cet article, nous verrons comment mettre en place le parallélisme et de quelle façon il se traduit sur nos plans d’exécution.

Présentation et configuration

L’utilisation de plusieurs process en parallèle est aujourd’hui limitée aux requêtes de lecture (SELECT) et ne fonctionne pas pour les requêtes imbriquées dans une requête déjà parallélisée.
Autre remarque, l’optimiseur décide de ne pas paralléliser une requête lorsque celle-ci n’est pas assez sélective (un full seq scan ne sera pas parallélisé).
Au niveau de la configuration, voici les nouveaux paramètres principaux à définir :

max_worker_processes : Le nombre maximum de « worker » disponibles
max_parallel_workers_per_gather : Le nombre maximum de « worker » utilisables par une requête unitaire
min_parallel_relation_size : Taille minimum pour qu’une table soit utilisable pour une requête parallélisée

Benchmark et plans d’exécution

Pour les tests, nous avons créé une table de 100.000.000 d’enregistrements (3 Go) et nous allons modifier le paramètre max_parallel_workers_per_gather au fil des exécutions d’une même requête : « explain«
La machine sur laquelle se trouve mon instance possède 4 CPU.

Test n°1 : 0 max_parallel_workers_per_gather

postgres=# show max_parallel_workers_per_gather ;
max_parallel_workers_per_gather
---------------------------------
(1 row)
postgres=# EXPLAIN ANALYZE SELECT * FROM easyteam WHERE test=1000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on easyteam (cost=0.00..1673536.78 rows=1 width=4) (actual time=37.194..8772.130 rows=2 loops=1)
Filter: (test = 1000)
Rows Removed by Filter: 100000000
Planning time: 0.085 ms
Execution time: 8772.159 ms
(5 rows)

Test n°2 : 2 max_parallel_workers_per_gather

postgres=# set max_parallel_workers_per_gather to 2 ;
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM easyteam WHERE test=1000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..981309.77 rows=1 width=4) (actual time=43.468..4878.854 rows=2 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on easyteam (cost=0.00..980309.67 rows=1 width=4) (actual time=3232.021..4841.484 rows=1 loops=3)
Filter: (test = 1000)
Rows Removed by Filter: 33333333
Planning time: 0.148 ms
Execution time: 4931.510 ms
(8 rows)

On constate déjà une diminution de 43% (je précise que la table était déjà en cache, c’est bien le parallélisme qui améliore la rapidité).

Test n°3 : 4 max_parallel_workers_per_gather

postgres=# set max_parallel_workers_per_gather to 4 ;
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM easyteam WHERE test=1000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..782176.70 rows=1 width=4) (actual time=43.636..4860.099 rows=2 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Seq Scan on easyteam (cost=0.00..781176.60 rows=1 width=4) (actual time=3000.023..4827.714 rows=0 loops=5)
Filter: (test = 1000)
Rows Removed by Filter: 20000000
Planning time: 0.056 ms
Execution time: 3076.281 ms
(8 rows)

On constate encore une amélioration des temps de réponse avec un degré de parallélisme supérieur.
Evidemment, lorsque l’on tente d’augmenter le paramètre max_parallel_workers_per_gather à une valeur supérieure au nombre de CPU de la machine, on constate que les temps de réponse atteignent un plateau.

Jointures et fonctions d’agrégat

Que se passe-t-il lorsque nos requêtes sont un peu plus complexes qu’un vulgaire SELECT sur une unique table ?
Et bien PostgreSQL s’adapte.
Dans l’exemple ci-dessous, on réalise une jointure entre notre première table et une copie de celle-ci.
Le premier plan d’exécution montre l’exécution de la requête sans parallélisme, et le deuxième avec un degré égal à 2.

postgres=# EXPLAIN ANALYZE SELECT e.test, o.test FROM easyteam e LEFT JOIN oracle o ON e.test = o.test where e.test=1000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..3537204.01 rows=564159 width=8) (actual time=171.153..33883.639 rows=4 loops=1)
Join Filter: (e.test = o.test)
-> Seq Scan on easyteam e (cost=0.00..1677275.40 rows=1 width=4) (actual time=170.894..9872.009 rows=2 loops=1)
Filter: (test = 1000)
Rows Removed by Filter: 100000000
-> Seq Scan on oracle o (cost=0.00..1852876.62 rows=564159 width=4) (actual time=0.693..12005.801 rows=2 loops=2)
Filter: (test = 1000)
Rows Removed by Filter: 100000000
Planning time: 0.294 ms
Execution time: 33883.830 ms
(10 rows)
postgres=#
postgres=# set max_parallel_workers_per_gather to 2;
SET
postgres=# EXPLAIN ANALYZE SELECT e.test, o.test FROM easyteam e LEFT JOIN oracle o ON e.test = o.test where e.test=1000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=2000.00..1945621.38 rows=1 width=8) (actual time=4937.288..14759.074 rows=4 loops=1)
Join Filter: (e.test = o.test)
-> Gather (cost=1000.00..981309.77 rows=1 width=4) (actual time=4930.598..4930.607 rows=2 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on easyteam e (cost=0.00..980309.67 rows=1 width=4) (actual time=3291.690..4895.823 rows=1 loops=3)
Filter: (test = 1000)
Rows Removed by Filter: 33333333
-> Gather (cost=1000.00..964311.60 rows=1 width=4) (actual time=3.413..4914.222 rows=2 loops=2)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on oracle o (cost=0.00..963311.50 rows=1 width=4) (actual time=2574.693..4920.620 rows=1 loops=8)
Filter: (test = 1000)
Rows Removed by Filter: 33523703
Planning time: 0.242 ms
Execution time: 14761.482 ms
(16 rows)

Plusieurs constats : les temps de réponses sont à nouveau plus rapides (plus de 50% de gain), et on constate que des process parallèles sont aussi lancés pour la lecture des tables jointes.
Enfin, pour conclure ce tour d’horizon rapide, coup d’oeil sur les fonctions d’agrégat :

postgres=# set max_parallel_workers_per_gather to 0;
SET
postgres=# EXPLAIN ANALYZE SELECT max(test) FROM easyteam;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1677275.40..1677275.41 rows=1 width=4) (actual time=17319.720..17319.720 rows=1 loops=1)
-> Seq Scan on easyteam (cost=0.00..1438315.72 rows=95583872 width=4) (actual time=99.241..8643.459 rows=100000002 loops=1)
Planning time: 0.134 ms
Execution time: 17319.830 ms
(4 rows)
postgres=# set max_parallel_workers_per_gather to 2;
SET
postgres=# EXPLAIN ANALYZE SELECT max(test) FROM easyteam;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=981309.88..981309.89 rows=1 width=4) (actual time=8810.498..8810.498 rows=1 loops=1)
-> Gather (cost=981309.67..981309.88 rows=2 width=4) (actual time=8809.016..8810.492 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=980309.67..980309.68 rows=1 width=4) (actual time=8806.247..8806.247 rows=1 loops=3)
-> Parallel Seq Scan on easyteam (cost=0.00..880743.13 rows=39826613 width=4) (actual time=54.912..4399.931 rows=33333334 loops=3)
Planning time: 0.101 ms
Execution time: 8811.511 ms
(8 rows)

Même constat tout aussi intéressant : des performances nettement meilleures sur des fonctions de groupe (MAX, AVG, etc…)

En bref, le parallélisme sous PostgreSQL…

… c’est à suivre de près ! Même s’il n’arrive évidemment pas encore à la cheville de ses concurrents (rien sur les requêtes d’écriture, sur les création d’index, …) PosgreSQL prend le bon chemin et annonce d’ores et déjà que tout cela n’est qu’un début.

Matthieu

Voir tous ses articles

Blog

PostgreSQL 9.6 : Introduction au parallélisme

Présentation et configuration

Benchmark et plans d’exécution

Test n°1 : 0 max_parallel_workers_per_gather

Test n°2 : 2 max_parallel_workers_per_gather

Test n°3 : 4 max_parallel_workers_per_gather

Jointures et fonctions d’agrégat

En bref, le parallélisme sous PostgreSQL…

Articles récents