CPU modèn gen yon anpil nan nwayo. Pandan plizyè ane, aplikasyon yo te voye demann nan baz done an paralèl. Si se yon rechèch rapò sou plizyè ranje nan yon tablo, li kouri pi vit lè w ap itilize plizyè CPU, epi PostgreSQL te kapab fè sa depi vèsyon 9.6.
Li te pran 3 ane pou aplike karakteristik rechèch paralèl la - nou te oblije reekri kòd la nan diferan etap nan ekzekisyon demann lan. PostgreSQL 9.6 prezante enfrastrikti pou amelyore plis kòd la. Nan vèsyon ki vin apre yo, lòt kalite demann yo egzekite an paralèl.
Restriksyon yo
- Pa pèmèt ekzekisyon paralèl si tout nwayo yo deja okipe, otreman lòt demann yo pral ralanti.
- Sa ki pi enpòtan, pwosesis paralèl ak gwo valè WORK_MEM sèvi ak anpil memwa - chak rantre oswa sòt hash pran memwa work_mem.
- Rekèt OLTP latansi ki ba pa ka akselere pa ekzekisyon paralèl. Men, si rechèch la retounen yon sèl ranje, pwosesis paralèl pral sèlman ralanti li.
- Devlopè renmen sèvi ak referans TPC-H la. Petèt ou gen demann menm jan an pou ekzekisyon paralèl pafè.
- Sèlman rechèch SELECT san yo pa bloke predikate yo egzekite an paralèl.
- Pafwa bon endèks pi bon pase optik tab sekans nan mòd paralèl.
- Poz demann ak kurseur yo pa sipòte.
- Fonksyon fenèt yo ak fonksyon total ansanm òdone yo pa paralèl.
- Ou pa jwenn anyen nan kantite travay I/O.
- Pa gen okenn algoritm klasman paralèl. Men, demann ak kalite yo ka egzekite nan paralèl nan kèk aspè.
- Ranplase CTE (AK ...) ak yon SELECT enbrike pou pèmèt pwosesis paralèl.
- Anbalaj done twazyèm pati poko sipòte pwosesis paralèl (men yo te kapab!)
- FULL OUTTER JOIN pa sipòte.
- max_rows enfim pwosesis paralèl.
- Si yon rechèch gen yon fonksyon ki pa make PARALLEL SAFE, li pral yon sèl fil.
- Nivo izolasyon tranzaksyon SERIALIZABLE enfim pwosesis paralèl.
Anviwònman tès la
Devlopè PostgreSQL yo te eseye diminye tan repons demann referans TPC-H yo. Telechaje referans lan ak . Sa a se yon itilizasyon ofisyèl TPC-H referans - pa pou baz done oswa konparezon pyès ki nan konpitè.
- Telechaje TPC-H_Tools_v2.17.3.zip (oswa nouvo vèsyon) .
- Chanje non makefile.suite pou Makefile epi chanje jan sa dekri isit la: . Konpile kòd la ak lòd make la.
- Jenere done:
./dbgen -s 10kreye yon baz done 23 GB. Sa a se ase yo wè diferans lan nan pèfòmans nan demann paralèl ak ki pa paralèl. - Konvèti fichye yo
tblвcsv с forиsed. - Klone repozitwa a
pg_tpchepi kopye fichye yocsvвpg_tpch/dss/data. - Kreye demann ak yon lòd
qgen. - Chaje done nan baz done a ak lòd la
./tpch.sh.
Paralèl analiz sekans
Li ka pi vit pa paske nan lekti paralèl, men paske done yo gaye nan anpil nwayo CPU. Nan sistèm opere modèn yo, dosye done PostgreSQL yo kach byen. Avèk li davans, li posib pou jwenn yon pi gwo blòk nan depo pase demann demon PG yo. Se poutèt sa, pèfòmans rechèch pa limite pa disk I/O. Li konsome sik CPU pou:
- li ranje youn pa youn nan paj tab la;
- konpare valè fisèl ak kondisyon yo
WHERE.
Ann kouri yon rechèch senp select:
tpch=# explain analyze select l_quantity as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Seq Scan on lineitem (cost=0.00..1964772.00 rows=58856235 width=5) (actual time=0.014..16951.669 rows=58839715 loops=1)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 1146337
Planning Time: 0.203 ms
Execution Time: 19035.100 msEskanè sekans lan pwodui twòp ranje san yo pa agrégasyon, kidonk rechèch la egzekite pa yon sèl nwayo CPU.
Si ou ajoute SUM(), ou ka wè ke de workflows pral ede akselere rechèch la:
explain analyze select sum(l_quantity) as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=1589702.14..1589702.15 rows=1 width=32) (actual time=8553.365..8553.365 rows=1 loops=1)
-> Gather (cost=1589701.91..1589702.12 rows=2 width=32) (actual time=8553.241..8555.067 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=1588701.91..1588701.92 rows=1 width=32) (actual time=8547.546..8547.546 rows=1 loops=3)
-> Parallel Seq Scan on lineitem (cost=0.00..1527393.33 rows=24523431 width=5) (actual time=0.038..5998.417 rows=19613238 loops=3)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 382112
Planning Time: 0.241 ms
Execution Time: 8555.131 msAgregasyon paralèl
Ne paralèl Seq Scan la pwodui ranje pou agrégation pasyèl. Nœuds "Pasyèl Total" taye liy sa yo lè l sèvi avèk SUM(). Nan fen a, kontwa SUM la nan chak pwosesis travayè yo kolekte pa noeud "Rasanble".
Rezilta final la kalkile pa ne "Finalize total". Si ou gen pwòp fonksyon agrégation ou, pa bliye make yo kòm "paralèl san danje".
Kantite pwosesis travayè yo
Nimewo a nan pwosesis travayè yo ka ogmante san yo pa rekòmanse sèvè a:
explain analyze select sum(l_quantity) as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=1589702.14..1589702.15 rows=1 width=32) (actual time=8553.365..8553.365 rows=1 loops=1)
-> Gather (cost=1589701.91..1589702.12 rows=2 width=32) (actual time=8553.241..8555.067 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=1588701.91..1588701.92 rows=1 width=32) (actual time=8547.546..8547.546 rows=1 loops=3)
-> Parallel Seq Scan on lineitem (cost=0.00..1527393.33 rows=24523431 width=5) (actual time=0.038..5998.417 rows=19613238 loops=3)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 382112
Planning Time: 0.241 ms
Execution Time: 8555.131 msKisa k ap pase la a? Te gen 2 fwa plis pwosesis travay, ak demann lan te vin sèlman 1,6599 fwa pi vit. Kalkil yo enteresan. Nou te gen 2 travayè pwosesis ak 1 lidè. Apre chanjman an li te vin 4+1.
Akselere maksimòm nou an nan pwosesis paralèl: 5/3 = 1,66(6) fwa.
Kouman li travay?
Pwosesis yo
Egzekisyon demann toujou kòmanse ak pwosesis dirijan an. Lidè a fè tout bagay ki pa paralèl ak kèk pwosesis paralèl. Lòt pwosesis ki fè menm demann yo rele pwosesis travayè yo. Pwosesis paralèl itilize enfrastrikti (ki soti nan vèsyon 9.4). Depi lòt pati nan PostgreSQL itilize pwosesis olye ke fil, yon rechèch ak 3 pwosesis travayè ta ka 4 fwa pi vit pase pwosesis tradisyonèl yo.
Entèraksyon
Pwosesis travayè yo kominike ak lidè a atravè yon keu mesaj (ki baze sou memwa pataje). Chak pwosesis gen 2 moun kap kriye: pou erè ak pou tuple.
Konbyen workflows ki nesesè?
Limit minimòm lan espesifye pa paramèt la . Lè sa a, moun kap kouri demann lan pran pwosesis travayè nan pisin lan limite pa paramèt la . Dènye limit la se , se sa ki, kantite total pwosesis background.
Si li pa t posib pou asiyen yon pwosesis travayè, pwosesis la pral yon sèl-pwosesis.
Planifikatè rechèch la ka diminye workflows selon gwosè tab la oswa endèks la. Gen paramèt pou sa и .
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 workerChak fwa tab la se 3 fwa pi gwo pase min_parallel_(index|table)_scan_size, Postgres ajoute yon pwosesis travayè. Kantite workflows pa baze sou depans yo. Depandans sikilè fè aplikasyon konplèks difisil. Olye de sa, planifikatè a sèvi ak règ senp.
Nan pratik, règ sa yo pa toujou apwopriye pou pwodiksyon, kidonk ou ka chanje kantite pwosesis travayè pou yon tab espesifik: ALTER TABLE ... SET (parallel_workers = N).
Poukisa yo pa itilize pwosesis paralèl?
Anplis de lis long restriksyon yo, gen chèk pri tou:
- pou evite pwosesis paralèl nan demann kout. Paramèt sa a estime tan pou prepare memwa, kòmanse pwosesis la, ak premye echanj done.
: kominikasyon ant lidè a ak travayè yo ka retade nan pwopòsyon ak kantite tuple ki soti nan pwosesis travay yo. Paramèt sa a kalkile pri a nan echanj done.
Answit bouk yo
PostgreSQL 9.6+ может выполнять вложенные циклы параллельно — это простая операция.
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 '%special%deposits%'
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 !~~ '%special%deposits%'::text)Koleksyon an fèt nan dènye etap la, kidonk Ned Loop Left Join se yon operasyon paralèl. Paralèl Endèks Sèlman Scan te prezante sèlman nan vèsyon 10. Li travay menm jan ak optik seri paralèl. Kondisyon c_custkey = o_custkey li yon lòd pou chak fisèl kliyan. Se konsa, li pa paralèl.
Hash Join
Chak pwosesis travayè kreye pwòp tab hash li jiska PostgreSQL 11. Men, si gen plis pase kat nan pwosesis sa yo, pèfòmans pa pral amelyore. Nan nouvo vèsyon an, tab hash la pataje. Chak pwosesis travayè ka itilize WORK_MEM pou kreye yon tab hash.
select
l_shipmode,
sum(case
when o_orderpriority = '1-URGENT'
or o_orderpriority = '2-HIGH'
then 1
else 0
end) as high_line_count,
sum(case
when o_orderpriority <> '1-URGENT'
and o_orderpriority <> '2-HIGH'
then 1
else 0
end) as low_line_count
from
orders,
lineitem
where
o_orderkey = l_orderkey
and l_shipmode in ('MAIL', 'AIR')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '1996-01-01'
and l_receiptdate < date '1996-01-01' + interval '1' year
group by
l_shipmode
order by
l_shipmode
LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1964755.66..1964961.44 rows=1 width=27) (actual time=7579.592..7922.997 rows=1 loops=1)
-> Finalize GroupAggregate (cost=1964755.66..1966196.11 rows=7 width=27) (actual time=7579.590..7579.591 rows=1 loops=1)
Group Key: lineitem.l_shipmode
-> Gather Merge (cost=1964755.66..1966195.83 rows=28 width=27) (actual time=7559.593..7922.319 rows=6 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=1963755.61..1965192.44 rows=7 width=27) (actual time=7548.103..7564.592 rows=2 loops=5)
Group Key: lineitem.l_shipmode
-> Sort (cost=1963755.61..1963935.20 rows=71838 width=27) (actual time=7530.280..7539.688 rows=62519 loops=5)
Sort Key: lineitem.l_shipmode
Sort Method: external merge Disk: 2304kB
Worker 0: Sort Method: external merge Disk: 2064kB
Worker 1: Sort Method: external merge Disk: 2384kB
Worker 2: Sort Method: external merge Disk: 2264kB
Worker 3: Sort Method: external merge Disk: 2336kB
-> Parallel Hash Join (cost=382571.01..1957960.99 rows=71838 width=27) (actual time=7036.917..7499.692 rows=62519 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..1552386.40 rows=71838 width=19) (actual time=0.583..4901.063 rows=62519 loops=5)
Filter: ((l_shipmode = ANY ('{MAIL,AIR}'::bpchar[])) AND (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND (l_receiptdate >= '1996-01-01'::date) AND (l_receiptdate < '1997-01-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 11934691
-> Parallel Hash (cost=313722.45..313722.45 rows=3750045 width=20) (actual time=2011.518..2011.518 rows=3000000 loops=5)
Buckets: 65536 Batches: 256 Memory Usage: 3840kB
-> Parallel Seq Scan on orders (cost=0.00..313722.45 rows=3750045 width=20) (actual time=0.029..995.948 rows=3000000 loops=5)
Planning Time: 0.977 ms
Execution Time: 7923.770 msRekèt 12 ki soti nan TPC-H montre klèman yon koneksyon hash paralèl. Chak pwosesis travayè kontribye nan kreyasyon yon tab hash komen.
Rantre Join
Yon rantre nan fizyon se ki pa paralèl nan lanati. Pa enkyete w si sa a se dènye etap la nan rechèch la - li ka toujou kouri nan paralèl.
-- Query 2 from TPC-H
explain (costs off) select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
from part, supplier, partsupp, nation, region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 36
and p_type like '%BRASS'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'AMERICA'
and ps_supplycost = (
select
min(ps_supplycost)
from partsupp, supplier, nation, region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'AMERICA'
)
order by s_acctbal desc, n_name, s_name, p_partkey
LIMIT 100;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Limit
-> Sort
Sort Key: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
-> Merge Join
Merge Cond: (part.p_partkey = partsupp.ps_partkey)
Join Filter: (partsupp.ps_supplycost = (SubPlan 1))
-> Gather Merge
Workers Planned: 4
-> Parallel Index Scan using <strong>part_pkey</strong> on part
Filter: (((p_type)::text ~~ '%BRASS'::text) AND (p_size = 36))
-> Materialize
-> Sort
Sort Key: partsupp.ps_partkey
-> Nested Loop
-> Nested Loop
Join Filter: (nation.n_regionkey = region.r_regionkey)
-> Seq Scan on region
Filter: (r_name = 'AMERICA'::bpchar)
-> Hash Join
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Seq Scan on supplier
-> Hash
-> Seq Scan on nation
-> Index Scan using idx_partsupp_suppkey on partsupp
Index Cond: (ps_suppkey = supplier.s_suppkey)
SubPlan 1
-> Aggregate
-> Nested Loop
Join Filter: (nation_1.n_regionkey = region_1.r_regionkey)
-> Seq Scan on region region_1
Filter: (r_name = 'AMERICA'::bpchar)
-> Nested Loop
-> Nested Loop
-> Index Scan using idx_partsupp_partkey on partsupp partsupp_1
Index Cond: (part.p_partkey = ps_partkey)
-> Index Scan using supplier_pkey on supplier supplier_1
Index Cond: (s_suppkey = partsupp_1.ps_suppkey)
-> Index Scan using nation_pkey on nation nation_1
Index Cond: (n_nationkey = supplier_1.s_nationkey)Nœud "Merge Join" la sitiye pi wo a "Gather Merge". Se konsa, fusion pa sèvi ak pwosesis paralèl. Men, ne "Paralèl Index Scan" toujou ede ak segman an part_pkey.
Koneksyon pa seksyon
Nan PostgreSQL 11 andikape pa default: li gen orè trè chè. Tablo ki gen menm patisyon yo ka mete patisyon pa patisyon. Fason sa a Postgres pral sèvi ak pi piti tab hash. Chak koneksyon nan seksyon yo ka paralèl.
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;
QUERY PLAN
---------------------------------------------------
Append
-> Hash Join
Hash Cond: (t2.b = t1.a)
-> Seq Scan on prt2_p1 t2
Filter: ((b >= 0) AND (b <= 10000))
-> Hash
-> Seq Scan on prt1_p1 t1
Filter: (b = 0)
-> Hash Join
Hash Cond: (t2_1.b = t1_1.a)
-> Seq Scan on prt2_p2 t2_1
Filter: ((b >= 0) AND (b <= 10000))
-> Hash
-> Seq Scan on prt1_p2 t1_1
Filter: (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;
QUERY PLAN
-----------------------------------------------------------
Gather
Workers Planned: 4
-> Parallel Append
-> Parallel Hash Join
Hash Cond: (t2_1.b = t1_1.a)
-> Parallel Seq Scan on prt2_p2 t2_1
Filter: ((b >= 0) AND (b <= 10000))
-> Parallel Hash
-> Parallel Seq Scan on prt1_p2 t1_1
Filter: (b = 0)
-> Parallel Hash Join
Hash Cond: (t2.b = t1.a)
-> Parallel Seq Scan on prt2_p1 t2
Filter: ((b >= 0) AND (b <= 10000))
-> Parallel Hash
-> Parallel Seq Scan on prt1_p1 t1
Filter: (b = 0)Bagay pwensipal lan se ke koneksyon an nan seksyon se paralèl sèlman si seksyon sa yo gwo ase.
Paralèl Append
ka itilize olye pou yo blòk diferan nan workflows diferan. Sa rive anjeneral ak UNION ALL requêtes. Dezavantaj la se mwens paralelis, paske chak pwosesis travayè sèlman trete 1 demann.
Gen 2 pwosesis travayè k ap kouri isit la, byenke 4 yo aktive.
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 <= date '2000-12-01' - interval '105' day;
QUERY PLAN
------------------------------------------------------------------------------------------------
Gather
Workers Planned: 2
-> Parallel Append
-> Aggregate
-> Seq Scan on lineitem
Filter: (l_shipdate <= '2000-08-18 00:00:00'::timestamp without time zone)
-> Aggregate
-> Seq Scan on lineitem lineitem_1
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)Varyab ki pi enpòtan yo
- WORK_MEM limite memwa pou chak pwosesis, pa sèlman demann: work_mem pwosesis koneksyon = anpil memwa.
- — konbyen travayè travayè pwogram egzekisyon an pral itilize pou pwosesis paralèl nan plan an.
- — ajiste kantite total pwosesis travayè yo ak kantite nwayo CPU sou sèvè a.
- - menm bagay la tou, men pou pwosesis travay paralèl.
Rezilta
Apati vèsyon 9.6, pwosesis paralèl ka amelyore anpil pèfòmans demann konplèks ki analize anpil ranje oswa endèks. Nan PostgreSQL 10, pwosesis paralèl aktive pa default. Sonje enfim li sou serveurs ak yon gwo kantite travay OLTP. Analiz sekans oswa analiz endèks konsome anpil resous. Si w pa fè yon rapò sou tout seri done a, ou ka amelyore pèfòmans rechèch lè w tou senpleman ajoute endèks ki manke yo oswa lè w itilize bon patisyon.
Referans
Sous: www.habr.com