CPUs modern boga loba cores. Mangtaun-taun, aplikasi parantos ngirim patarosan ka pangkalan data paralel. Upami éta mangrupikeun pamundut laporan dina sababaraha baris dina méja, éta ngajalankeun langkung gancang nalika nganggo sababaraha CPU, sareng PostgreSQL tiasa ngalakukeun ieu ti saprak versi 9.6.
Butuh waktu 3 taun pikeun nerapkeun fitur query paralel - urang kudu nulis balik kode dina tahap béda tina palaksanaan query. PostgreSQL 9.6 ngenalkeun infrastruktur pikeun ningkatkeun kodeu. Dina versi saterusna, tipe séjén queries dieksekusi paralel.
larangan
- Ulah ngaktipkeun palaksanaan paralel lamun sakabeh cores geus sibuk, disebutkeun requests séjén bakal ngalambatkeun turun.
- Anu paling penting, pamrosésan paralel kalayan nilai WORK_MEM anu luhur ngagunakeun seueur mémori - unggal gabung atanapi diurutkeun hash nyandak mémori work_mem.
- Patarosan OLTP latency low teu bisa gancangan ku palaksanaan paralel. Tur upami query mulih hiji baris, processing paralel ngan bakal ngalambatkeun eta turun.
- Pamekar resep ngagunakeun patokan TPC-H. Panginten anjeun gaduh patarosan anu sami pikeun palaksanaan paralel anu sampurna.
- Ngan patarosan PILIH tanpa ngonci predikat anu dieksekusi paralel.
- Kadang-kadang indexing ditangtoskeun leuwih hade tinimbang scanning tabel sequential dina modeu paralel.
- Ngareureuhkeun patarosan sareng kursor henteu dirojong.
- Fungsi jandela jeung maréntahkeun set fungsi agrégat teu paralel.
- Anjeun teu meunang nanaon dina I / O workload.
- Henteu aya algoritma asihan paralel. Tapi queries kalawan sorts bisa dieksekusi dina paralel dina sababaraha aspék.
- Ganti CTE (WITH ...) ku SELECT nested pikeun ngaktipkeun processing paralel.
- Bungkus data pihak katilu henteu acan ngadukung pamrosesan paralel (tapi tiasa!)
- FULL OUTER JOIN teu dirojong.
- max_rows nganonaktipkeun pamrosesan paralel.
- Upami pamundut ngagaduhan fungsi anu henteu ditandaan PARALLEL SAFE, éta bakal janten benang tunggal.
- Tingkat isolasi transaksi SERIALIZABLE nganonaktipkeun pamrosésan paralel.
Lingkungan tés
Pamekar PostgreSQL nyobian ngirangan waktos réspon patarosan patokan TPC-H. Ngundeur patokan jeung . Ieu mangrupikeun pamakean henteu resmi tina patokan TPC-H - sanés pikeun pangkalan data atanapi perbandingan hardware.
- Unduh TPC-H_Tools_v2.17.3.zip (atanapi versi anu langkung énggal) .
- Ganti ngaran makefile.suite ka Makefile sareng robih sakumaha anu dijelaskeun di dieu: . Kompilkeun kode sareng paréntah make.
- Ngahasilkeun data:
./dbgen -s 10nyiptakeun 23 database GB. Ieu cukup pikeun ningali bédana kinerja queries paralel jeung non-paralel. - Ngarobih file
tblвcsv с forиsed. - Kloning gudang
pg_tpchsareng nyalin filecsvвpg_tpch/dss/data. - Jieun queries kalawan paréntah
qgen. - Muat data kana pangkalan data nganggo paréntah
./tpch.sh.
Paralel sequential scanning
Éta tiasa langkung gancang sanés kusabab bacaan paralel, tapi kusabab datana sumebar ka seueur inti CPU. Dina sistem operasi modéren, file data PostgreSQL di-cache ogé. Kalayan maca sateuacanna, anjeun tiasa nampi blok anu langkung ageung tina panyimpenan tibatan pamundut daemon PG. Ku alatan éta, kinerja query teu diwatesan ku disk I/O. Éta meakeun siklus CPU pikeun:
- baca baris hiji-hiji tina kaca tabel;
- ngabandingkeun nilai string jeung kaayaan
WHERE.
Hayu urang ngajalankeun hiji query basajan 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 msScan sequential ngahasilkeun loba teuing baris tanpa aggregation, jadi query dieksekusi ku inti CPU tunggal.
Upami anjeun nambihan SUM(), anjeun tiasa ningali yén dua alur kerja bakal ngabantosan nyepetkeun pamundut:
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 msPangumpulan paralel
Titik Parallel Seq Scan ngahasilkeun baris pikeun agrégasi parsial. Titik "Agrégat Parsial" motong garis ieu nganggo SUM(). Dina tungtungna, counter SUM ti unggal prosés worker dikumpulkeun ku titik "Kumpul".
Hasil ahir diitung ku titik "Finalize Agrégat". Upami Anjeun gaduh fungsi aggregation sorangan, ulah poho pikeun nandaan aranjeunna salaku "paralel aman".
Jumlah prosés pagawe
Jumlah prosés pagawé tiasa dironjatkeun tanpa ngamimitian deui 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 msAya naon di dieu? Aya 2 kali leuwih prosés gawé, sarta pamundut janten ngan 1,6599 kali leuwih gancang. Itungan anu metot. Kami ngagaduhan 2 prosés pagawé sareng 1 pamimpin. Sanggeus robah jadi 4+1.
speedup maksimum kami ti processing paralel: 5/3 = 1,66 (6) kali.
Kumaha carana sangkan eta pagawean?
Proses dina prosés
Palaksanaan pamundut salawasna dimimitian ku prosés ngarah. pamimpin ngalakukeun sagalana non-paralel jeung sababaraha processing paralel. Prosés séjén anu ngalaksanakeun pamundut anu sami disebut prosés pagawé. Ngolah paralel ngagunakeun infrastruktur (tina versi 9.4). Kusabab bagian séjén PostgreSQL ngagunakeun prosés tinimbang threads, query kalawan 3 prosés worker bisa jadi 4 kali leuwih gancang ti processing tradisional.
Interaksi
Prosés worker komunikasi sareng pamimpin ngaliwatan antrian pesen (dumasar kana memori dibagikeun). Unggal prosés ngagaduhan 2 antrian: pikeun kasalahan sareng tuple.
Sabaraha alur kerja anu diperyogikeun?
Watesan minimum ditangtukeun ku parameter . The runner pamundut lajeng nyandak prosés worker ti kolam renang diwatesan ku parameter . The watesan panungtungan nyaéta , nyaeta, jumlah total prosés tukang.
Lamun teu mungkin pikeun allocate hiji prosés worker, processing bakal single-prosés.
Nu Ngarencana query bisa ngurangan workflows gumantung kana ukuran tabel atawa indéks. Aya parameter pikeun ieu и .
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 workerUnggal waktos tabel nyaéta 3 kali leuwih badag batan min_parallel_(index|table)_scan_size, Postgres nambihan prosés padamel. Jumlah alur kerja henteu dumasar kana biaya. kagumantungan sirkular ngajadikeun palaksanaan kompléks hésé. Gantina, Nu Ngarencana ngagunakeun aturan basajan.
Dina prakték, aturan ieu teu salawasna cocog pikeun produksi, jadi Anjeun bisa ngarobah jumlah prosés worker pikeun tabel husus: ALTER TABLE ... SET (parallel_workers = N).
Naha ngolah paralel henteu dianggo?
Salian daptar panjang larangan, aya ogé cek biaya:
- pikeun nyegah ngolah paralel tina pamundut pondok. Parameter ieu ngira-ngira waktos nyiapkeun mémori, ngamimitian prosés, sareng bursa data awal.
: komunikasi antara pamimpin jeung pagawe bisa nyangsang saimbang jeung jumlah tuple tina prosés gawé. Parameter ieu ngitung biaya bursa data.
Nested Loop Gabung
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)Koléksi lumangsung dina tahap anu terakhir, janten Nested Loop Left Join mangrupikeun operasi paralel. Indéks Paralel Ngan Scan diwanohkeun ngan dina versi 10. Gawéna sami sareng scanning serial paralel. Kaayaan c_custkey = o_custkey maca hiji urutan per string klien. Jadi teu paralel.
Hash Gabung
Unggal prosés worker nyiptakeun tabel hash sorangan nepi ka PostgreSQL 11. Jeung lamun aya leuwih ti opat prosés ieu, kinerja moal ningkatkeun. Dina versi anyar, tabel hash dibagikeun. Unggal prosés pagawe tiasa nganggo WORK_MEM pikeun nyiptakeun méja 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 msPaménta 12 ti TPC-H jelas nunjukkeun sambungan Hash paralel. Unggal prosés pagawe nyumbang kana kreasi tabel hash umum.
Gabung Gabung
A gabung ngagabung téh non-paralel di alam. Tong hariwang upami ieu mangrupikeun léngkah terakhir tina pamundut - éta masih tiasa dijalankeun paralel.
-- 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)Titik "Gabung Gabung" ayana di luhur "Kumpulkeun Gabung". Jadi ngahiji teu make processing paralel. Tapi titik "Scan Indéks Paralel" masih ngabantosan ruas éta part_pkey.
Sambungan ku bagian
Dina PostgreSQL 11 ditumpurkeun sacara standar: boga scheduling pisan mahal. Tabél sareng partisi anu sami tiasa ngagabung partisi ku partisi. Ku cara ieu Postgres bakal nganggo tabel hash anu langkung alit. Unggal sambungan bagian tiasa paralel.
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)Hal utama nyaéta sambungan dina bagian sajajar ngan lamun bagian ieu cukup badag.
Paralel Append
bisa dipaké gaganti blok béda dina workflows béda. Ieu biasana lumangsung kalawan UNION ALL queries. disadvantage nyaeta kirang parallelism, sabab unggal prosés worker ngan prosés 1 pamundut.
Aya 2 prosés padamel dijalankeun di dieu, sanaos 4 diaktipkeun.
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)Variabel pangpentingna
- WORK_MEM ngawatesan mémori per prosés, sanés ngan ukur patarosan: work_mem prosés sambungan = loba memori.
- - sabaraha worker prosés program executing bakal dipaké pikeun ngolah paralel tina rencana.
- - nyaluyukeun jumlah total prosés worker kana jumlah cores CPU dina server.
- - sarua, tapi pikeun prosés gawé paralel.
hasil
Dina versi 9.6, pamrosésan paralel tiasa pisan ningkatkeun kinerja queries kompléks nu nyeken loba baris atawa indéks. Dina PostgreSQL 10, pamrosésan paralel diaktipkeun sacara standar. Inget pikeun mareuman éta dina server sareng beban kerja OLTP anu ageung. Scan sequential atanapi scan indéks meakeun seueur sumber. Lamun anjeun teu ngajalankeun laporan dina sakabéh dataset, anjeun tiasa ningkatkeun kinerja query ku saukur nambahkeun indexes leungit atawa ngagunakeun partitioning ditangtoskeun.
rujukan
sumber: www.habr.com