Is-CPUs moderni għandhom ħafna qlub. Għal snin sħaħ, l-applikazzjonijiet ilhom jibagħtu mistoqsijiet lil databases b'mod parallel. Jekk hija mistoqsija ta 'rapport fuq ringieli multipli f'tabella, taħdem aktar malajr meta tuża CPUs multipli, u PostgreSQL seta' jagħmel dan mill-verżjoni 9.6.
Kienu ħadet 3 snin biex timplimenta l-karatteristika tal-mistoqsija parallela - kellna nerġgħu nikteb il-kodiċi fi stadji differenti tal-eżekuzzjoni tal-mistoqsija. PostgreSQL 9.6 introduċa infrastruttura biex ikompli jtejjeb il-kodiċi. Fil-verżjonijiet sussegwenti, tipi oħra ta 'mistoqsijiet huma eżegwiti b'mod parallel.
Restrizzjonijiet
Tippermettix l-eżekuzzjoni parallela jekk il-qlub kollha jkunu diġà okkupati, inkella talbiet oħra jonqsu.
L-iktar importanti, l-ipproċessar parallel b'valuri WORK_MEM għoljin juża ħafna memorja - kull hash join jew sort jieħu memorja work_mem.
Mistoqsijiet OLTP b'latenza baxxa ma jistgħux jiġu aċċellerati b'eżekuzzjoni parallela. U jekk il-mistoqsija tirritorna ringiela waħda, l-ipproċessar parallel se jrattab biss.
L-iżviluppaturi jħobbu jużaw il-benchmark TPC-H. Forsi għandek mistoqsijiet simili għal eżekuzzjoni parallela perfetta.
Mistoqsijiet SELECT biss mingħajr qfil tal-predikat huma esegwiti b'mod parallel.
Xi drabi l-indiċjar xieraq huwa aħjar mill-iskannjar tal-mejda sekwenzjali fil-modalità parallela.
Il-waqfien tal-mistoqsijiet u l-cursors mhumiex appoġġjati.
Il-funzjonijiet tat-tieqa u l-funzjonijiet aggregati tas-sett ordnati mhumiex paralleli.
Inti ma takkwista xejn fil-volum tax-xogħol I/O.
M'hemm l-ebda algoritmi ta' għażla paralleli. Iżda mistoqsijiet b'tipi jistgħu jiġu esegwiti b'mod parallel f'xi aspetti.
Ibdel CTE (WITH ...) bi SELECT ibitat biex tippermetti l-ipproċessar parallel.
It-tgeżwir tad-dejta ta’ partijiet terzi għadhom ma jappoġġjawx l-ipproċessar parallel (iżda jistgħu!)
FULL OUTER JOIN mhix appoġġjata.
max_rows tiddiżattiva l-ipproċessar parallel.
Jekk mistoqsija għandha funzjoni li mhix immarkata PARALLEL SAFE, tkun b'kamin wieħed.
Il-livell ta' iżolament tat-tranżazzjoni SERIALIZABLE jiddiżattiva l-ipproċessar parallel.
Ambjent tat-Test
L-iżviluppaturi ta 'PostgreSQL ppruvaw inaqqsu l-ħin tar-rispons tal-mistoqsijiet ta' referenza TPC-H. Niżżel il-benchmark u tadattah għal PostgreSQL. Dan huwa użu mhux uffiċjali tal-benchmark TPC-H - mhux għal paragun tad-database jew tal-ħardwer.
Semmi mill-ġdid makefile.suite għal Makefile u ibdel kif deskritt hawn: https://github.com/tvondra/pg_tpch . Iġbor il-kodiċi bil-kmand make.
Iġġenera data: ./dbgen -s 10 joħloq database ta’ 23 GB. Dan huwa biżżejjed biex tara d-differenza fil-prestazzjoni ta 'mistoqsijiet paralleli u mhux paralleli.
Ikkonverti fajls tbl в csv с for и sed.
Ikklonja r-repożitorju pg_tpch u kopja l-fajls csv в pg_tpch/dss/data.
Oħloq mistoqsijiet bi kmand qgen.
Tagħbija data fid-database bil-kmand ./tpch.sh.
Skennjar sekwenzjali parallel
Jista 'jkun aktar mgħaġġel mhux minħabba qari parallel, iżda minħabba li d-dejta hija mifruxa fuq bosta cores tas-CPU. Fis-sistemi operattivi moderni, il-fajls tad-dejta PostgreSQL huma miżmuma fil-cache tajjeb. Bil-qari bil-quddiem, huwa possibbli li tinkiseb blokka akbar mill-ħażna milli titlob id-daemon PG. Għalhekk, il-prestazzjoni tal-mistoqsija mhix limitata mill-I/O tad-disk. Jikkonsma ċikli tas-CPU biex:
aqra ringieli waħda waħda mill-paġni tat-tabella;
qabbel il-valuri u l-kundizzjonijiet tas-sekwenza WHERE.
Ejja nagħmel mistoqsija sempliċi 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 ms
L-iskannjar sekwenzjali jipproduċi wisq ringieli mingħajr aggregazzjoni, għalhekk il-mistoqsija tiġi esegwita minn qalba waħda tas-CPU.
Jekk iżżid SUM(), tista' tara li żewġ flussi tax-xogħol se jgħinu biex titħaffef il-mistoqsija:
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 ms
Aggregazzjoni parallela
In-node Parallel Seq Scan jipproduċi ringieli għal aggregazzjoni parzjali. Il-"Parzjali Aggregat" node trims dawn il-linji bl-użu SUM(). Fl-aħħar, il-counter SUM minn kull proċess tal-ħaddiem jinġabar min-node "Gather".
Ir-riżultat finali huwa kkalkulat min-node "Finalize Aggregate". Jekk għandek il-funzjonijiet ta 'aggregazzjoni tiegħek stess, tinsiex timmarkahom bħala "parallel safe".
Numru ta' proċessi tal-ħaddiema
In-numru ta' proċessi tal-ħaddiema jista' jiżdied mingħajr ma jerġa' jibda s-server:
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 ms
X'qed jiġri hawn? Kien hemm 2 darbiet aktar proċessi ta 'xogħol, u t-talba saret biss 1,6599 darbiet aktar mgħaġġla. Il-kalkoli huma interessanti. Kellna 2 proċessi tal-ħaddiema u mexxej 1. Wara l-bidla saret 4+1.
L-eżekuzzjoni tat-talba dejjem tibda bil-proċess ewlieni. Il-mexxej jagħmel dak kollu mhux parallel u xi proċessar parallel. Proċessi oħra li jwettqu l-istess talbiet jissejħu proċessi tal-ħaddiema. L-ipproċessar parallel juża l-infrastruttura proċessi dinamiċi tal-ħaddiema fl-isfond (mill-verżjoni 9.4). Peress li partijiet oħra ta 'PostgreSQL jużaw proċessi aktar milli ħjut, mistoqsija bi proċessi tal-ħaddiema 3 tista' tkun 4 darbiet aktar mgħaġġla mill-ipproċessar tradizzjonali.
Interazzjoni
Il-proċessi tal-ħaddiema jikkomunikaw mal-mexxej permezz ta 'kju ta' messaġġi (ibbażat fuq memorja kondiviża). Kull proċess għandu 2 kjuwijiet: għall-iżbalji u għat-tuples.
Kull darba li t-tabella hija 3 darbiet akbar minn min_parallel_(index|table)_scan_size, Postgres iżid proċess tal-ħaddiem. In-numru ta' flussi tax-xogħol mhuwiex ibbażat fuq l-ispejjeż. Id-dipendenza ċirkolari tagħmel l-implimentazzjonijiet kumplessi diffiċli. Minflok, il-pjanifikatur juża regoli sempliċi.
Fil-prattika, dawn ir-regoli mhux dejjem huma adattati għall-produzzjoni, għalhekk tista 'tbiddel in-numru ta' proċessi tal-ħaddiema għal tabella speċifika: ALTER TABLE ... SET (parallel_workers = N).
Għaliex ma jintużax l-ipproċessar parallel?
Minbarra l-lista twila ta’ restrizzjonijiet, hemm ukoll kontrolli tal-ispejjeż:
parallel_setup_cost - biex jiġi evitat l-ipproċessar parallel ta' talbiet qosra. Dan il-parametru jistma l-ħin biex tipprepara l-memorja, tibda l-proċess, u l-iskambju inizjali tad-dejta.
parallel_tuple_cost: il-komunikazzjoni bejn il-mexxej u l-ħaddiema tista 'tittardja fi proporzjon għan-numru ta' tuples mill-proċessi tax-xogħol. Dan il-parametru jikkalkula l-ispiża tal-iskambju tad-dejta.
Jingħaqad Loop nested
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)
Il-ġbir iseħħ fl-aħħar stadju, għalhekk Nested Loop Left Join hija operazzjoni parallela. Parallel Index Only Scan ġie introdott biss fil-verżjoni 10. Jaħdem simili għall-iskannjar serjali parallel. Kundizzjoni c_custkey = o_custkey jaqra ordni waħda għal kull string tal-klijent. Allura mhuwiex parallel.
Hash Ingħaqad
Kull proċess ta 'ħaddiem joħloq it-tabella hash tiegħu stess sa PostgreSQL 11. U jekk ikun hemm aktar minn erbgħa minn dawn il-proċessi, il-prestazzjoni mhux se titjieb. Fil-verżjoni l-ġdida, it-tabella tal-hash hija kondiviża. Kull proċess ta' ħaddiem jista' juża WORK_MEM biex joħloq tabella 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 ms
Mistoqsija 12 minn TPC-H turi b'mod ċar konnessjoni hash parallela. Kull proċess ta 'ħaddiem jikkontribwixxi għall-ħolqien ta' tabella hash komuni.
Merge Ingħaqad
A merge join hija ta' natura mhux parallela. Tinkwetax jekk dan huwa l-aħħar pass tal-mistoqsija - xorta tista 'taħdem b'mod parallel.
-- 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)
In-node "Merge Join" jinsab fuq il-"Gather Merge". Allura l-għaqda ma tużax ipproċessar parallel. Iżda n-node "Parallel Index Scan" għadu jgħin fis-segment part_pkey.
Konnessjoni b'taqsimiet
F'PostgreSQL 11 konnessjoni minn taqsimiet diżattivat awtomatikament: għandu skedar għali ħafna. Tabelli b'qsim simili jistgħu jingħaqdu diviżorju b'partizzjoni. Dan il-mod Postgres se juża tabelli hash iżgħar. Kull konnessjoni ta 'sezzjonijiet tista' tkun parallela.
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)
Il-ħaġa prinċipali hija li l-konnessjoni f'sezzjonijiet hija parallela biss jekk dawn it-taqsimiet huma kbar biżżejjed.
Appendi Parallel
Appendi Parallel jistgħu jintużaw minflok blokki differenti fi flussi tax-xogħol differenti. Dan normalment jiġri bil-mistoqsijiet UNION ALL. L-iżvantaġġ huwa inqas paralleliżmu, minħabba li kull proċess ta 'ħaddiem jipproċessa talba waħda biss.
Hemm 2 proċessi tal-ħaddiema għaddejjin hawn, għalkemm 4 huma attivati.
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)
Il-varjabbli l-aktar importanti
WORK_MEM jillimita l-memorja għal kull proċess, mhux biss mistoqsijiet: work_mem proċessi konnessjonijiet = ħafna memorja.
max_parallel_workers_per_gather — kemm-il ħaddiem jipproċessa l-programm ta’ eżekuzzjoni se juża għall-ipproċessar parallel mill-pjan.
max_worker_processes — taġġusta n-numru totali ta 'proċessi tal-ħaddiema għan-numru ta' cores CPU fuq is-server.
Mill-verżjoni 9.6, l-ipproċessar parallel jista 'jtejjeb ħafna l-prestazzjoni ta' mistoqsijiet kumplessi li jiskennjaw ħafna ringieli jew indiċi. F'PostgreSQL 10, l-ipproċessar parallel huwa attivat awtomatikament. Ftakar li tiddiżattivaha fuq servers b'ammont kbir ta' xogħol OLTP. Skans sekwenzjali jew skans tal-indiċi jikkunsmaw ħafna riżorsi. Jekk m'intix qed tmexxi rapport fuq is-sett tad-dejta kollu, tista' ttejjeb il-prestazzjoni tal-mistoqsija billi sempliċement iżżid indiċijiet neqsin jew tuża qsim xieraq.