Les processeurs modernes ont de nombreux cĆurs. Pendant des annĂ©es, les applications ont envoyĂ© des requĂȘtes aux bases de donnĂ©es en parallĂšle. Si c'est une requĂȘte de rapport sur de nombreuses lignes dans une table, elle s'exĂ©cute plus rapidement lorsqu'elle utilise plusieurs cĆurs, et c'est possible dans PostgreSQL Ă partir de la version 9.6.
Il a fallu 3 ans pour mettre en Ćuvre la fonction de requĂȘtes parallĂšles : il a fallu rĂ©Ă©crire le code Ă diffĂ©rentes Ă©tapes de l'exĂ©cution des requĂȘtes. PostgreSQL 9.6 a introduit l'infrastructure pour amĂ©liorer davantage le code. Dans les versions suivantes, d'autres types de requĂȘtes sont Ă©galement exĂ©cutĂ©s en parallĂšle.
Restrictions
- N'activez pas l'exĂ©cution parallĂšle si tous les cĆurs sont dĂ©jĂ occupĂ©s, sinon d'autres requĂȘtes seront ralenties.
- Surtout, le traitement parallÚle avec des valeurs WORK_MEM élevées utilise beaucoup de mémoire : chaque jointure de hachage ou chaque tri nécessite de la mémoire équivalente à work_mem.
- Les requĂȘtes OLTP Ă faible latence ne peuvent pas ĂȘtre accĂ©lĂ©rĂ©es par l'exĂ©cution parallĂšle. Et si une requĂȘte retourne une seule ligne, le traitement parallĂšle ne fait que la ralentir.
- Les dĂ©veloppeurs aiment utiliser le benchmark TPC-H. Peut-ĂȘtre avez-vous des requĂȘtes similaires pour un exĂ©cution parallĂšle optimale.
- Seules les requĂȘtes SELECT sans verrouillage prĂ©dictif s'exĂ©cutent en parallĂšle.
- Parfois, un bon indexage est préférable à un scan séquentiel en mode parallÚle sur la table.
- Les suspensions de requĂȘtes et les curseurs ne sont pas pris en charge.
- Les fonctions de fenĂȘtre et les fonctions d'agrĂ©gation sur des ensembles ordonnĂ©s ne sont pas parallĂšles.
- Vous ne gagnez rien dans les charges de travail d'entrée-sortie.
- Il n'existe pas d'algorithmes de tri parallĂšles. Mais les requĂȘtes avec tris peuvent ĂȘtre exĂ©cutĂ©es en parallĂšle sous certains aspects.
- Remplacez CTE (WITH âŠ) par un SELECT imbriquĂ© pour inclure le traitement parallĂšle.
- Les wrappers de données tierces ne prennent pas encore en charge le traitement parallÚle (mais pourraient le faire !)
- FULL OUTER JOIN n'est pas pris en charge.
- max_rows désactive le traitement parallÚle.
- Si une fonction dans la requĂȘte n'est pas marquĂ©e comme PARALLEL SAFE, elle sera exĂ©cutĂ©e en mode mono-fil.
- Le niveau d'isolation de transaction SERIALIZABLE désactive le traitement parallÚle.
Environnement de test
Les dĂ©veloppeurs de PostgreSQL ont essayĂ© de rĂ©duire le temps de rĂ©ponse des requĂȘtes du benchmark TPC-H. TĂ©lĂ©chargez le benchmark et Ceci n'est pas une utilisation officielle du benchmark TPC-H - pas pour comparer des bases de donnĂ©es ou du matĂ©riel.
- Téléchargez TPC-H_Tools_v2.17.3.zip (ou une version plus récente) .
- Renommez makefile.suite en Makefile et modifiez comme décrit ici : Compilez le code avec la commande make.
- Générez des données :
./dbgen -s 10crĂ©e une base de donnĂ©es de 23 Go. Cela suffira pour voir la diffĂ©rence de performance entre les requĂȘtes parallĂšles et non parallĂšles. - Convertissez les fichiers
tbldanscsv avec foretsed. - Clonez le référentiel
pg_tpchet copiez les fichierscsvdanspg_tpch/dss/data. - CrĂ©ez des requĂȘtes avec la commande
qgen. - Chargez les données dans la base avec la commande
./tpch.sh.
Scan séquentiel parallÚle
Cela peut ĂȘtre plus rapide non pas Ă cause de la lecture parallĂšle, mais parce que les donnĂ©es sont dispersĂ©es sur plusieurs cĆurs de CPU. Dans les systĂšmes d'exploitation modernes, les fichiers de donnĂ©es PostgreSQL sont bien mis en cache. Avec la lecture anticipative, il est possible de rĂ©cupĂ©rer de l'entrepĂŽt un bloc plus grand que celui demandĂ© par le dĂ©mon PG. Par consĂ©quent, la performance de la requĂȘte n'est pas limitĂ©e par les entrĂ©es-sorties du disque. Elle utilise des cycles CPU pour :
- lire les lignes une par une Ă partir des pages de la table ;
- comparer les valeurs des lignes et les conditions
OĂ.
Effectuons une requĂȘte simple select:
tpch=# explain analyze select l_quantity as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day;
PLAN DE LA REQUĂTE
--------------------------------------------------------------------------------------------------------------------------
Scan Séquentiel sur lineitem (coût=0.00..1964772.00 lignes=58856235 largeur=5) (temps réel=0.014..16951.669 lignes=58839715 boucles=1)
Filtre : (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Lignes supprimées par le filtre : 1146337
Temps de planification : 0.203 ms
Temps d'exĂ©cution : 19035.100 msLe scan sĂ©quentiel donne trop de lignes sans agrĂ©gation, si bien que la requĂȘte s'exĂ©cute sur un seul cĆur de CPU.
Si l'on ajoute SUM(), on voit que deux processus de travail peuvent aider Ă accĂ©lĂ©rer la requĂȘte :
explain analyze select sum(l_quantity) as sum_qty from lineitem where l_shipdate Rassembler (coût=1589701.91..1589702.12 lignes=2 largeur=32) (temps réel=8553.241..8555.067 lignes=3 boucles=1)
Travailleurs prévus : 2
Travailleurs lancés : 2
-> Agrégation partielle (coût=1588701.91..1588701.92 lignes=1 largeur=32) (temps réel=8547.546..8547.546 lignes=1 boucles=3)
-> Scan séquentiel parallÚle sur lineitem (coût=0.00..1527393.33 lignes=24523431 largeur=5) (temps réel=0.038..5998.417 lignes=19613238 boucles=3)
Filtre : (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Lignes supprimées par le filtre : 382112
Temps de planification : 0.241 ms
Temps d'exécution : 8555.131 msAgrégation parallÚle
Le nĆud « Parallel Seq Scan » produit des lignes pour une agrĂ©gation partielle. Le nĆud « Partial Aggregate » rĂ©duit ces lignes Ă l'aide de SUM(). Ă la fin, le compteur SUM de chaque processus de travail est collectĂ© par le nĆud « Gather ».
Le rĂ©sultat final est calculĂ© par le nĆud « Finalize Aggregate ». Si vous avez vos propres fonctions d'agrĂ©gation, n'oubliez pas de les marquer comme « parallel safe ».
Nombre de processus de travail
Le nombre de processus de travail peut ĂȘtre augmentĂ© sans redĂ©marrer le serveur :
explain analyze select sum(l_quantity) as sum_qty from lineitem where l_shipdate Rassembler (coût=1589701.91..1589702.12 lignes=2 largeur=32) (temps réel=8553.241..8555.067 lignes=3 boucles=1)
Travailleurs prévus : 2
Travailleurs lancés : 2
-> Agrégation partielle (coût=1588701.91..1588701.92 lignes=1 largeur=32) (temps réel=8547.546..8547.546 lignes=1 boucles=3)
-> Scan séquentiel parallÚle sur lineitem (coût=0.00..1527393.33 lignes=24523431 largeur=5) (temps réel=0.038..5998.417 lignes=19613238 boucles=3)
Filtre : (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Lignes supprimées par le filtre : 382112
Temps de planification : 0.241 ms
Temps d'exĂ©cution : 8555.131 msQue se passe-t-il ici ? Le nombre de processus de travail a doublĂ©, et la requĂȘte est devenue seulement 1,6599 fois plus rapide. Les calculs sont intĂ©ressants. Nous avions 2 processus de travail et 1 leader. AprĂšs modification, il y en a 4+1.
Notre maximum d'accélération grùce au traitement en parallÚle : 5/3 = 1,66(6) fois.
Comment cela fonctionne ?
Processus
L'exĂ©cution de la requĂȘte commence toujours par le processus leader. Le leader effectue tout le travail non parallĂšle et une partie du traitement parallĂšle. Les autres processus, effectuant les mĂȘmes requĂȘtes, sont appelĂ©s processus de travail. Le traitement parallĂšle utilise l'infrastructure (Ă partir de la version 9.4). Comme d'autres parties de PostgreSQL utilisent des processus plutĂŽt que des threads, une requĂȘte avec 3 processus de travail pouvait ĂȘtre 4 fois plus rapide que le traitement traditionnel.
Interaction
Les processus de travail communiquent avec le leader via une file d'attente de messages (basée sur la mémoire partagée). Chaque processus a 2 files d'attente : pour les erreurs et pour les tuples.
Combien de processus de travail sont nécessaires ?
La limite minimale est dĂ©finie par le paramĂštre . Ensuite, l'exĂ©cuteur de requĂȘtes prend des processus de travail Ă partir d'un pool, limitĂ© par le paramĂštre . La derniĂšre limite est , c'est-Ă -dire le nombre total de processus de fond.
Si un processus de travail ne peut pas ĂȘtre allouĂ©, le traitement sera uniprocessus.
Le planificateur de requĂȘtes peut rĂ©duire le nombre de processus de travail en fonction de la taille de la table ou de l'index. Pour cela, il y a des paramĂštres et .
set min_parallel_table_scan_size='8MB'
8MB table => 1 worker
24MB table => 2 workers
72MB table => 3 workers
x => log(x / min_parallel_table_scan_size) / log(3) + 1 workerChaque fois que la table est 3 fois plus grande que min_parallel_(index|table)_scan_size, Postgres ajoute un processus de travail. Le nombre de processus de travail n'est pas basé sur les coûts. La dépendance circulaire complique les implémentations complexes. Au lieu de cela, le planificateur utilise des rÚgles simples.
En pratique, ces rÚgles ne s'appliquent pas toujours à la production, il est donc possible de modifier le nombre de processus de travail pour une table spécifique : ALTER TABLE ⊠SET (parallel_workers = N).
Pourquoi le traitement parallÚle n'est-il pas utilisé ?
En plus de la longue liste de limitations, il y a aussi des vérifications de coût :
â pour Ă©viter le traitement parallĂšle des requĂȘtes courtes. Ce paramĂštre Ă©value le temps nĂ©cessaire Ă la prĂ©paration de la mĂ©moire, au lancement du processus et Ă l'Ă©change initial de donnĂ©es.
: la communication entre le leader et les travailleurs peut prendre plus de temps proportionnellement au nombre de tuples des processus de travail. Ce paramÚtre calcule les coûts d'échange de données.
Jointures en boucle imbriquĂ©es â Nested Loop Join
PostgreSQL 9.6+ peut exĂ©cuter des boucles imbriquĂ©es en parallĂšle â c'est une opĂ©ration simple.
explain (costs off) select c_custkey, count(o_orderkey)
from customer left outer join orders on
c_custkey = o_custkey and o_comment not like '%specialposits%'
group by c_custkey;
QUERY PLAN
--------------------------------------------------------------------------------------
Finalize GroupAggregate
Group Key: customer.c_custkey
-> Gather Merge
Workers Planned: 4
-> Partial GroupAggregate
Group Key: customer.c_custkey
-> Nested Loop Left Join
-> Parallel Index Only Scan using customer_pkey on customer
-> Index Scan using idx_orders_custkey on orders
Index Cond: (customer.c_custkey = o_custkey)
Filter: ((o_comment)::text !~~ '%specialposits%'::text)La collecte se fait Ă la derniĂšre Ă©tape, donc la jointure en boucle imbriquĂ©e â Nested Loop Left Join â est une opĂ©ration parallĂšle. Le Parallel Index Only Scan n'est apparu qu'Ă partir de la version 10. Il fonctionne de maniĂšre similaire Ă un scan sĂ©quentiel parallĂšle. La condition c_custkey = o_custkey lit un ordre pour chaque ligne client. Donc, ce n'est pas parallĂšle.
Jointure par hachage â Hash Join
Chaque processus de travail crée sa propre table de hachage jusqu'à PostgreSQL 11. Et si le nombre de ces processus dépasse quatre, la performance ne s'améliorera pas. Dans la nouvelle version, la table de hachage est partagée. Chaque processus de travail peut utiliser WORK_MEM pour créer une table de hachage.
sélectionner
l_shipmode,
somme(cas
quand o_orderpriority = '1-URGENT'
ou o_orderpriority = '2-HIGH'
alors 1
sinon 0
fin) comme high_line_count,
somme(cas
quand o_orderpriority <> '1-URGENT'
et o_orderpriority <> '2-HIGH'
alors 1
sinon 0
fin) comme low_line_count
Ă partir de
commandes,
lignearticle
oĂč
o_orderkey = l_orderkey
et l_shipmode dans ('MAIL', 'AIR')
et l_commitdate < l_receiptdate
et l_shipdate < l_commitdate
et l_receiptdate >= date '1996-01-01'
et l_receiptdate < date '1996-01-01' + intervalle '1' an
grouper par
l_shipmode
ordre par
l_shipmode
LIMIT 1;
PLAN DE CONSULTATION
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limite (coût=1964755.66..1964961.44 lignes=1 largeur=27) (temps réel=7579.592..7922.997 lignes=1 boucles=1)
-> Finaliser GroupAggregate (coût=1964755.66..1966196.11 lignes=7 largeur=27) (temps réel=7579.590..7579.591 lignes=1 boucles=1)
Clé de groupe: lignearticle.l_shipmode
-> Rassembler Fusionner (coût=1964755.66..1966195.83 lignes=28 largeur=27) (temps réel=7559.593..7922.319 lignes=6 boucles=1)
Travailleurs prévus: 4
Travailleurs lancés: 4
-> GroupAggregate partiel (coût=1963755.61..1965192.44 lignes=7 largeur=27) (temps réel=7548.103..7564.592 lignes=2 boucles=5)
Clé de groupe: lignearticle.l_shipmode
-> Trier (coût=1963755.61..1963935.20 lignes=71838 largeur=27) (temps réel=7530.280..7539.688 lignes=62519 boucles=5)
Clé de tri: lignearticle.l_shipmode
MĂ©thode de tri: fusion externe Disque: 2304kB
Ouvrier 0: MĂ©thode de tri: fusion externe Disque: 2064kB
Ouvrier 1: MĂ©thode de tri: fusion externe Disque: 2384kB
Ouvrier 2: MĂ©thode de tri: fusion externe Disque: 2264kB
Ouvrier 3: MĂ©thode de tri: fusion externe Disque: 2336kB
-> Jointure de hachage parallÚle (coût=382571.01..1957960.99 lignes=71838 largeur=27) (temps réel=7036.917..7499.692 lignes=62519 boucles=5)
Condition de hachage: (lignearticle.l_orderkey = commandes.o_orderkey)
-> Analyse séquentielle parallÚle sur lignearticle (coût=0.00..1552386.40 lignes=71838 largeur=19) (temps réel=0.583..4901.063 lignes=62519 boucles=5)
Filtre: ((l_shipmode = TOUT ('{MAIL,AIR}'::bpchar[])) ET (l_commitdate < l_receiptdate) ET (l_shipdate < l_commitdate) ET (l_receiptdate >= '1996-01-01'::date) ET (l_receiptdate < '1997-01-01 00:00:00'::timestamp sans fuseau horaire))
Lignes supprimées par le filtre: 11934691
-> Hachage parallÚle (coût=313722.45..313722.45 lignes=3750045 largeur=20) (temps réel=2011.518..2011.518 lignes=3000000 boucles=5)
Seaux: 65536 Lots: 256 Utilisation de la mémoire: 3840kB
-> Analyse séquentielle parallÚle sur commandes (coût=0.00..313722.45 lignes=3750045 largeur=20) (temps réel=0.029..995.948 lignes=3000000 boucles=5)
Temps de planification: 0.977 ms
Temps d'exĂ©cution: 7923.770 msLa requĂȘte 12 de TPC-H montre clairement une jointure de hachage parallĂšle. Chaque processus de travail participe Ă la crĂ©ation d'une table de hachage commune.
Joint de fusion â Merge Join
Le joint de fusion est fondamentalement non parallĂšle. Ne vous inquiĂ©tez pas si c'est la derniĂšre Ă©tape de la requĂȘte, elle peut quand mĂȘme ĂȘtre exĂ©cutĂ©e en parallĂšle.
-- Requête 2 de TPC-H
expliquer (coûts off) sélectionner s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
à partir de part, fournisseur, partsupp, nation, région
où
p_partkey = ps_partkey
et s_suppkey = ps_suppkey
et p_size = 36
et p_type comme '%BRASS'
et s_nationkey = n_nationkey
et n_regionkey = r_regionkey
et r_name = 'AMÉRIQUE'
et ps_supplycost = (
sélectionner
min(ps_supplycost)
à partir de partsupp, fournisseur, nation, région
où
p_partkey = ps_partkey
et s_suppkey = ps_suppkey
et s_nationkey = n_nationkey
et n_regionkey = r_regionkey
et r_name = 'AMÉRIQUE'
)
classer par s_acctbal desc, n_name, s_name, p_partkey
LIMIT 100;
PLAN DE REQUÊTE
----------------------------------------------------------------------------------------------------------
Limite
-> Trier
Clé de tri : supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
-> Jonction Fusion
Condition de fusion : (part.p_partkey = partsupp.ps_partkey)
Filtre de jointure : (partsupp.ps_supplycost = (Sous-plan 1))
-> Rassembler la fusion
Travailleurs prévus : 4
-> Scan d'index parallèle en utilisant <strong>part_pkey</strong> sur part
Filtre : (((p_type)::text ~~ '%BRASS'::text) ET (p_size = 36))
-> Matérialiser
-> Trier
Clé de tri : partsupp.ps_partkey
-> Boucle imbriquée
-> Boucle imbriquée
Filtre de jointure : (nation.n_regionkey = region.r_regionkey)
-> Scan séquentiel sur région
Filtre : (r_name = 'AMÉRIQUE'::bpchar)
-> Jonction par hachage
Condition de hachage : (supplier.s_nationkey = nation.n_nationkey)
-> Scan séquentiel sur fournisseur
-> Hachage
-> Scan séquentiel sur nation
-> Scan d'index utilisant idx_partsupp_suppkey sur partsupp
Condition d'index : (ps_suppkey = supplier.s_suppkey)
Sous-plan 1
-> Agrégat
-> Boucle imbriquée
Filtre de jointure : (nation_1.n_regionkey = region_1.r_regionkey)
-> Scan séquentiel sur région region_1
Filtre : (r_name = 'AMÉRIQUE'::bpchar)
-> Boucle imbriquée
-> Boucle imbriquée
-> Scan d'index utilisant idx_partsupp_partkey sur partsupp partsupp_1
Condition d'index : (part.p_partkey = ps_partkey)
-> Scan d'index utilisant supplier_pkey sur fournisseur fournisseur_1
Condition d'index : (s_suppkey = partsupp_1.ps_suppkey)
-> Scan d'index utilisant nation_pkey sur nation nation_1
Condition d'index : (n_nationkey = supplier_1.s_nationkey)Le nĆud « Merge Join » se situe au-dessus de « Gather Merge ». Par consĂ©quent, la fusion n'utilise pas de traitement parallĂšle. Mais le nĆud « Parallel Index Scan » aide toujours avec le segment part_pkey.
Joint par partitions
Dans PostgreSQL 11 dĂ©sactivĂ© par dĂ©faut : il nĂ©cessite une planification trĂšs coĂ»teuse. Les tables avec un partitionnement similaire peuvent ĂȘtre jointes partition par partition. Ainsi, Postgres utilisera de plus petites tables de hachage. Chaque jointure de partitions peut ĂȘtre parallĂšle.
tpch=# set enable_partitionwise_join=t;
tpch=# explain (costs off) select * from prt1 t1, prt2 t2
where t1.a = t2.b and t1.b = 0 and t2.b between 0 and 10000;
PLAN DE QUĂTE
---------------------------------------------------
Append
-> Hash Join
Cond de hachage : (t2.b = t1.a)
-> Seq Scan sur prt2_p1 t2
Filtre : ((b >= 0) ET (b <= 10000))
-> Hash
-> Seq Scan sur prt1_p1 t1
Filtre : (b = 0)
-> Hash Join
Cond de hachage : (t2_1.b = t1_1.a)
-> Seq Scan sur prt2_p2 t2_1
Filtre : ((b >= 0) ET (b <= 10000))
-> Hash
-> Seq Scan sur prt1_p2 t1_1
Filtre : (b = 0)
tpch=# set parallel_setup_cost = 1;
tpch=# set parallel_tuple_cost = 0.01;
tpch=# explain (costs off) select * from prt1 t1, prt2 t2
where t1.a = t2.b and t1.b = 0 and t2.b between 0 and 10000;
PLAN DE QUĂTE
-----------------------------------------------------------
Gather
Travailleurs prévus : 4
-> Append parallĂšle
-> Hash Join parallĂšle
Cond de hachage : (t2_1.b = t1_1.a)
-> Seq Scan parallĂšle sur prt2_p2 t2_1
Filtre : ((b >= 0) ET (b <= 10000))
-> Hash
-> Seq Scan parallĂšle sur prt1_p2 t1_1
Filtre : (b = 0)
-> Hash Join parallĂšle
Cond de hachage : (t2.b = t1.a)
-> Seq Scan parallĂšle sur prt2_p1 t2
Filtre : ((b >= 0) ET (b <= 10000))
-> Hash
-> Seq Scan parallĂšle sur prt1_p1 t1
Filtre : (b = 0)Principale, une jointure par sections peut ĂȘtre parallĂšle, seulement si ces sections sont suffisamment grandes.
Append parallĂšle â Parallel Append
peut ĂȘtre utilisĂ© Ă la place de diffĂ©rents blocs dans diffĂ©rents processus de travail. Cela se produit gĂ©nĂ©ralement avec des requĂȘtes UNION ALL. Le dĂ©savantage est un parallĂ©lisme rĂ©duit, car chaque processus de travail traite seulement 1 requĂȘte.
Ici, 2 processus de travail sont lancés, bien que 4 soient inclus.
tpch=# explain (costs off) select sum(l_quantity) as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day union all select sum(l_quantity) as sum_qty from lineitem where l_shipdate Parallel Append
-> Aggregate
-> Seq Scan on lineitem
Filter: (l_shipdate Aggregate
-> Seq Scan on lineitem lineitem_1
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)Les variables les plus importantes
- WORK_MEM limite la quantitĂ© de mĂ©moire pour chaque processus, pas seulement pour les requĂȘtes : work_mem processus connexions = beaucoup de mĂ©moire.
- â combien de processus de travail le programme exĂ©cutant utilisera pour le traitement parallĂšle du plan.
- â ajuste le nombre total de processus de travail en fonction du nombre de cĆurs CPU sur le serveur.
- â mĂȘme chose, mais pour les processus de travail parallĂšles.
RĂ©sultats
Depuis la version 9.6, le traitement parallĂšle peut considĂ©rablement amĂ©liorer les performances des requĂȘtes complexes qui scannent de nombreuses lignes ou index. Dans PostgreSQL 10, le traitement parallĂšle est activĂ© par dĂ©faut. N'oubliez pas de le dĂ©sactiver sur les serveurs avec une charge de travail OLTP importante. Les scans sĂ©quentiels ou les scans d'index consomment beaucoup de ressources. Si vous n'exĂ©cutez pas un rapport sur l'ensemble du jeu de donnĂ©es, vous pouvez rendre les requĂȘtes plus performantes simplement en ajoutant des index manquants ou en utilisant un bon partitionnement.
Liens
Source : habr.com
