V sodobnih CPU-ijih je veliko jedr. Leta so aplikacije pošiljale poizvedbe v baze podatkov hkrati. Če je to poročilo, ki vključuje veliko število vrstic v tabeli, se hitreje izvede, ko uporabljate več CPU-ijev, kar je v PostgreSQL mogoče od različice 9.6 naprej.
Ugodno je bilo potrebno 3 leta, da se je realizirala funkcija paralelnih poizvedb — kod je bilo potrebno ponovno napisati v različnih fazah izvajanja poizvedb. V PostgreSQL 9.6 je bila uvedena infrastruktura za nadaljnje izboljšanje kode. V kasnejših različicah so bile paralelno izvedene tudi druge vrste poizvedb.
Omejitve
- Ne vklanjajte paralelnega izvajanja, če so vse jedra že zasedena, sicer bodo druge poizvedbe počasne.
- Najpomembneje je, da paralelno obdelovanje z visokimi vrednostmi WORK_MEM porabi veliko pomnilnika — vsaka povezava ali sortiranje s funkcijo hash uporablja pomnilnik v obsegu work_mem.
- OLTP poizvedbe z nizko zakasnitvijo ni mogoče pospešiti s paralelnim izvajanjem. Če poizvedba vrne eno vrstico, jo paralelno obdelovanje samo upočasni.
- Razvijalci radi uporabljajo benchmark TPC-H. Morda imate podobne poizvedbe za idealno paralelno izvajanje.
- Samo poizvedbe SELECT brez predikatske zaklenitve se izvajajo paralelno.
- Včasih je pravilna indeksacija boljša od zaporednega skeniranja tabele v paralelnem načinu.
- Zaustavitve poizvedb in kurzorji niso podprti.
- Okenske funkcije in agregatne funkcije urejenih nizov niso paralelne.
- Ne pridobivate ničesar v delovni obremenitvi vhod-izhod.
- Paralelni algoritmi za sortiranje ne obstajajo. Toda poizvedbe s sortiranjem se lahko izvajajo paralelno v nekaterih vidikih.
- Zamenjajte CTE (WITH …) z vnešenim SELECT, da omogočite paralelno obdelovanje.
- Ovije podatkov tretjih oseb zaenkrat ne podpirajo paralelne obdelave (kar bi lahko!).
- FULL OUTER JOIN ni podprt.
- max_rows onemogoča paralelno obdelovanje.
- Če je v poizvedbi funkcija, ki ni označena kot PARALLEL SAFE, bo enojna nit.
- Nivo izolacije transakcij SERIALIZABLE onemogoča paralelno obdelovanje.
Testno okolje
Razvijalci PostgreSQL so poskušali zmanjšati odzivni čas poizvedb benchmarka TPC-H. Prenesite benchmark in . To je neformalna uporaba benchmarka TPC-H — ni za primerjavo baz podatkov ali opreme.
- Prenesite TPC-H_Tools_v2.17.3.zip (ali novejšo različico) .
- Preimenujte makefile.suite v Makefile in spremenite, kot je opisano tukaj: . Kodo skompajlirajte z ukazom make.
- Generirajte podatke:
.\/dbgen -s 10ustvari bazo podatkov velikosti 23 GB. To je dovolj, da opazite razliko v zmogljivosti med paralelnimi in nepovedenimi poizvedbami. - Pretvorite datoteke
tblucsv s forinsed. - Klonirajte repozitorij
pg_tpchin kopirajte datotekecsvupg_tpch\/dss\/data. - Ustvarite poizvedbe z ukazom
qgen. - Naložite podatke v bazo z ukazom
.\/tpch.sh.
Paralelno zaporedno skeniranje
Morda je hitrejše ne zaradi paralelnega branja, temveč zato, ker so podatki razpršeni po več jedrih CPU. V sodobnih operacijskih sistemih se datoteke podatkov PostgreSQL dobro predpomnijo. S predhodnim branjem lahko pridobite iz shrambi blok, ki je večji, kot ga zahteva PG demon. Torej zmogljivost poizvedbe ni omejena z vhodno-izhodnimi operacijami diska. Porablja cikle CPU za:
- branje vrstic eno po eno s strani tabele;
- primerjanje vrednosti vrstic in pogojev
WHERE.
Izvedimo preprosto poizvedbo 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 msZaporedno skeniranje daje preveč vrstic brez agregacije, tako da se poizvedba izvede z enim jedrom CPU.
Če dodamo SUM(), vidimo, da dva delovna procesa pomagata pospešiti poizvedbo:
explain analyze select sum(l_quantity) as sum_qty from lineitem where l_shipdate 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)
-> Paralelno zaporedno skeniranje na 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 msParalelna agregacija
Noda 'Paralelno zaporedno skeniranje' proizvaja vrstice za delno agregacijo. Noda 'Delna agregacija' omeji te vrstice s pomočjo SUM(). Na koncu število SUM iz vsakega delovnega procesa zbere noda 'Gather'.
Končni rezultat se izračuna z vozliščem „Finalize Aggregate“. Če imate svoje funkcije aggregracije, jih ne pozabite označiti kot „parallel safe“.
Število delovnih procesov
Število delovnih procesov lahko povečate brez ponovnega zagonu strežnika:
explain analyze select sum(l_quantity) as sum_qty from lineitem where l_shipdate 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)
-> Paralelno zaporedno skeniranje na 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 msKaj se dogaja tukaj? Število delovnih procesov se je povečalo na 2-krat več, medtem ko je bil zahtevek le 1,6599-krat hitrejši. Izračuni so zanimivi. Imeli smo 2 delovna procesa in 1 vodilni. Po spremembi pa je bilo 4+1.
Naša največja pospešitev zaradi paralelnega procesiranja: 5/3 = 1,66(6)-krat.
Kako deluje?
Procesi
Izvajanje zahteve se vedno začne z vodilnim procesom. Vodja opravi vse nepodvojeno in del dela paralelne obdelave. Ostali procesi, ki izvajajo enake zahteve, se imenujejo delovni procesi. Paralelno procesiranje uporablja infrastrukturo (od različice 9.4). Ker druge komponente PostgreSQL uporabljajo procese in ne niti, lahko je zahteva s 3 delovnimi procesi do 4-krat hitrejša od tradicionalne obdelave.
Interakcija
Delovni procesi komunicirajo z vodjo preko čakalne vrste sporočil (na osnovi skupne pomnilnika). Vsak proces ima 2 čakalni vrsti: za napake in za tuplje.
Koliko delovnih procesov potrebujemo?
Minimalno omejitev določa parameter . Nato izvrševalec zahtevkov vzame delovne procese iz bazena, omejenega z параметром . Zadnja omejitev je , to je skupno število ozadnih procesov.
Če ne uspe dodeliti delovnega procesa, bo obdelava enoprocesna.
Sistem za načrtovanje zahtevkov lahko zmanjša število delovnih procesov glede na velikost tabele ali indeksa. Za to obstajajo parametri in .
set min_parallel_table_scan_size='8MB'
8MB tabela => 1 delovni proces
24MB tabela => 2 delovna procesa
72MB tabela => 3 delovne procese
x => log(x / min_parallel_table_scan_size) / log(3) + 1 delovni procesVsakič, ko je tabela 3-krat večja od min_parallel_(index|table)_scan_size, Postgres doda delovni proces. Število delovnih procesov ni temeljeno na stroških. Ciklična odvisnost otežuje zapletene izvedbe. Namesto tega načrtovalec uporablja preprosta pravila.
V praksi ta pravila niso vedno primerna za proizvodnjo, zato lahko spremenite število delovnih procesov za določeno tabelo: ALTER TABLE … SET (parallel_workers = N).
Zakaj paralelno procesiranje ni uporabljeno?
Poleg dolgega seznama omejitev so še preverbe stroškov:
— da se izognemo paralelnemu obdelovanju kratkih poizvedb. Ta parameter ocenjuje čas, potreben za pripravo pomnilnika, zagon procesa in začetno izmenjavo podatkov.
: komunikacija vodje z delavci se lahko podaljša sorazmerno s številom tupljev delovnih procesov. Ta parameter izračunava stroške izmenjave podatkov.
Povezave vgrajenih krogov — Nested Loop Join
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)Zbiranje poteka v zadnji fazi, tako da je Nested Loop Left Join — to je paralelna operacija. Parallel Index Only Scan se je pojavil šele v različici 10. Deluje podobno kot paralelno sequential scanning. Pogoji c_custkey = o_custkey prebere en naročilo za vsako vrstico stranke. Tako da ni paralelno.
Hash Join
Vsak delovni proces ustvari svojo hash tabelo do PostgreSQL 11. Če pa je teh procesov več kot štiri, zmogljivost ne bo boljša. V novi različici je hash tabela skupna. Vsak delovni proces lahko uporablja WORK_MEM za ustvarjanje hash tabele.
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 = date '1996-01-01'
and l_receiptdate 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 = '1996-01-01'::date) AND (l_receiptdate 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 msPojačanje 12 iz TPC-H jasno prikazuje paralelno haš spajanje. Svaki radni proces učestvuje u kreiranju zajedničke haš tabele.
Spojitev s fuzijo — Merge Join
Spojitev s fuzijo je po svoji naravi nepovsem povezana. Ne skrbite, če je to zadnja faza poizvedbe, še vedno se lahko izvaja vzporedno.
-- Poizvedba 2 iz TPC-H
razloži (stroške izklopi) izbira s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
iz del, dobavitelj, partsupp, narod, regija
kjer
p_partkey = ps_partkey
in s_suppkey = ps_suppkey
in p_size = 36
in p_type kot '%BRASS'
in s_nationkey = n_nationkey
in n_regionkey = r_regionkey
in r_name = 'AMERIKA'
in ps_supplycost = (
izbira
min(ps_supplycost)
iz partsupp, dobavitelj, narod, regija
kjer
p_partkey = ps_partkey
in s_suppkey = ps_suppkey
in s_nationkey = n_nationkey
in n_regionkey = r_regionkey
in r_name = 'AMERIKA'
)
uredi po s_acctbal desc, n_name, s_name, p_partkey
LIMIT 100;
NAČRT POIZVEDBE
----------------------------------------------------------------------------------------------------------
Limit
-> Razvrsti
Ključ za razvrščanje: dobavitelj.s_acctbal DESC, narod.n_name, dobavitelj.s_name, del.p_partkey
-> Združi Merge
Pogoj združitve: (del.p_partkey = partsupp.ps_partkey)
Filtriranje pri združevanju: (partsupp.ps_supplycost = (Podnačrt 1))
-> Zbiranje za merge
Načrtovani delavci: 4
-> Paralelno iskanje po indeksu z uporabo <strong>del_pkey</strong> na delu
Filtriranje: (((p_type)::text ~~ '%BRASS'::text) IN (p_size = 36))
-> Materializiraj
-> Razvrsti
Ključ za razvrščanje: partsupp.ps_partkey
-> Gnezdena zanka
-> Gnezdena zanka
Filtriranje pri združevanju: (narod.n_regionkey = regija.r_regionkey)
-> Sekvencijsko iskanje po regiji
Filtriranje: (r_name = 'AMERIKA'::bpchar)
-> Hash Join
Hash Pogoj: (dobavitelj.s_nationkey = narod.n_nationkey)
-> Sekvencijsko iskanje po dobavitelju
-> Hash
-> Sekvencijsko iskanje po narodu
-> Iskanje po indeksu z uporabo idx_partsupp_suppkey na partsupp
Indeksni pogoj: (ps_suppkey = dobavitelj.s_suppkey)
Podnačrt 1
-> Agregat
-> Gnezdena zanka
Filtriranje pri združevanju: (narod_1.n_regionkey = regija_1.r_regionkey)
-> Sekvencijsko iskanje po regiji regija_1
Filtriranje: (r_name = 'AMERIKA'::bpchar)
-> Gnezdena zanka
-> Gnezdena zanka
-> Iskanje po indeksu z uporabo idx_partsupp_partkey na partsupp partsupp_1
Indeksni pogoj: (del.p_partkey = ps_partkey)
-> Iskanje po indeksu z uporabo supplier_pkey na dobavitelju dobavitelj_1
Indeksni pogoj: (s_suppkey = partsupp_1.ps_suppkey)
-> Iskanje po indeksu z uporabo nation_pkey na narodu narod_1
Indeksni pogoj: (n_nationkey = dobavitelj_1.s_nationkey)Vožnja 'Merge Join' je nad 'Gather Merge'. Tako fuzija ne uporablja vzporedne obdelave. Vendar pa voznik 'Parallel Index Scan' še vedno pomaga s segmentom. del_pkey.
Spojitev po sekcijah
V PostgreSQL 11 privzeto je izklopljeno: ima zelo drago načrtovanje. Tabele s podobnim sekcioniranjem je mogoče povezati sekcija za sekcijo. Tako bo Postgres uporabil manjše hashe. Vsaka spojitev sekcij je lahko vzporedna.
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)Najpomembneje je, da je spojitev po sekcijah lahko vzporedna, le če so te sekcije dovolj velike.
Vzporedno dopolnilo — Parallel Append
se lahko uporablja namesto različnih blokov v različnih delovnih procesih. Običajno se to zgodi pri poizvedbah UNION ALL. Pomanjkljivost je manjša vzporednost, saj vsak delovni proces obravnava le 1 poizvedbo.
Tukaj sta zagnana 2 delovna procesa, čeprav je vklopljenih 4.
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)Najpomembnejše spremenljivke
- WORK_MEM omejuje količino pomnilnika za vsak proces, ne le za poizvedbe: work_mem procesi povezave = zelo veliko pomnilnika.
- — koliko delovnih procesov bo izvajajoči program uporabil za paralelno obdelavo po načrtu.
- — prilagaja skupno število delovnih procesov številu jedrskih CPU na strežniku.
- — enakega, vendar za paralelne delovne procese.
Zaključki
Začnite z različico 9.6, paralelno obdelavo lahko znatno izboljša uspešnost zapletenih poizvedb, ki skenirajo veliko vrstic ali indeksov. V PostgreSQL 10 je paralelna obdelava privzeto omogočena. Ne pozabite jo onemogočiti na strežnikih z velikim OLTP delovnim obremenitvami. Zaporedni skeni ali skeni indeksov porabijo zelo veliko virov. Če ne izvajate poročila za celoten niz podatkov, lahko poizvedbe postanejo bolj učinkovite, tako da dodate manjkajoče indekse ali uporabite pravilno delitev.
Povezave
Izvor: habr.com
