Šiuolaikiniai procesoriai turi daug branduolių. Daugelį metų programos lygiagrečiai siunčia užklausas į duomenų bazes. Jei tai ataskaitos užklausa keliose lentelės eilutėse, ji veikia greičiau, kai naudojami keli centriniai procesoriai, o PostgreSQL galėjo tai padaryti nuo 9.6 versijos.
Lygiagrečios užklausos funkcijai įdiegti prireikė 3 metų – turėjome perrašyti kodą skirtinguose užklausos vykdymo etapuose. PostgreSQL 9.6 įdiegė infrastruktūrą, kad būtų galima toliau tobulinti kodą. Vėlesnėse versijose lygiagrečiai vykdomos kitų tipų užklausos.
Apribojimai
- Neįjunkite lygiagretaus vykdymo, jei visi branduoliai jau užimti, kitaip kitos užklausos sulėtės.
- Svarbiausia, kad lygiagretus apdorojimas su didelėmis WORK_MEM reikšmėmis naudoja daug atminties – kiekvienas maišos prisijungimas arba rūšiavimas užima work_mem atmintį.
- Mažos delsos OLTP užklausos negali būti paspartintos lygiagrečiai vykdant. Ir jei užklausa pateikia vieną eilutę, lygiagretus apdorojimas jį tik sulėtins.
- Kūrėjai mėgsta naudoti TPC-H etaloną. Galbūt turite panašių užklausų dėl tobulo lygiagretaus vykdymo.
- Lygiagrečiai vykdomos tik SELECT užklausos be predikato užrakinimo.
- Kartais tinkamas indeksavimas yra geresnis nei nuoseklus lentelės nuskaitymas lygiagrečiu režimu.
- Užklausų ir žymeklių pristabdymas nepalaikomas.
- Langų funkcijos ir užsakytų rinkinių suvestinės funkcijos nėra lygiagrečios.
- Įvesties / išvesties darbo krūvyje jūs nieko negaunate.
- Lygiagrečių rūšiavimo algoritmų nėra. Tačiau kai kuriais aspektais užklausos su rūšimis gali būti vykdomos lygiagrečiai.
- Pakeiskite CTE (WITH ...) įdėtu SELECT, kad įgalintumėte lygiagretų apdorojimą.
- Trečiųjų šalių duomenų paketai dar nepalaiko lygiagretaus apdorojimo (bet galėtų!)
- FULL OUTTER JOIN nepalaikomas.
- max_rows išjungia lygiagretų apdorojimą.
- Jei užklausoje yra funkcija, kuri nėra pažymėta LYGIALELIAI SAUGI, ji bus vienos gijos.
- SERIALIZABLE operacijų izoliavimo lygis išjungia lygiagretų apdorojimą.
Bandymo aplinka
PostgreSQL kūrėjai bandė sumažinti TPC-H etaloninių užklausų atsako laiką. Atsisiųskite etaloną ir . Tai neoficialus TPC-H etalono naudojimas – ne duomenų bazės ar aparatinės įrangos palyginimui.
- Atsisiųskite TPC-H_Tools_v2.17.3.zip (arba naujesnę versiją) .
- Pervardykite makefile.suite į Makefile ir pakeiskite, kaip aprašyta čia: . Sukompiliuokite kodą naudodami make komandą.
- Generuoti duomenis:
./dbgen -s 10sukuria 23 GB duomenų bazę. To pakanka, kad pamatytumėte lygiagrečių ir nelygiagrečių užklausų našumo skirtumą. - Konvertuoti failus
tblвcsv с forиsed. - Klonuoti saugyklą
pg_tpchir nukopijuokite failuscsvвpg_tpch/dss/data. - Sukurkite užklausas naudodami komandą
qgen. - Įkelkite duomenis į duomenų bazę naudodami komandą
./tpch.sh.
Lygiagretus nuoseklus nuskaitymas
Tai gali būti greitesnė ne dėl lygiagretaus skaitymo, o dėl to, kad duomenys yra paskirstyti daugelyje procesoriaus branduolių. Šiuolaikinėse operacinėse sistemose PostgreSQL duomenų failai yra gerai saugomi talpykloje. Skaitant iš anksto, iš saugyklos galima gauti didesnį bloką, nei reikalauja PG demonas. Todėl užklausos našumo neriboja disko I/O. Jis sunaudoja procesoriaus ciklus, kad:
- skaityti eilutes po vieną iš lentelės puslapių;
- palyginkite eilučių vertes ir sąlygas
WHERE.
Vykdykime paprastą užklausą 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 msNuoseklus nuskaitymas sukuria per daug eilučių be apibendrinimo, todėl užklausą vykdo vienas procesoriaus branduolys.
Jei pridėsite SUM(), matote, kad dvi darbo eigos padės pagreitinti užklausą:
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 msLygiagretus agregavimas
Lygiagrečios sekos nuskaitymo mazgas sukuria eilutes daliniam agregavimui. Mazgas „Dalinis sujungimas“ apkarpo šias eilutes naudodamas SUM(). Pabaigoje SUM skaitiklis iš kiekvieno darbuotojo proceso surenkamas mazgu „Surinkti“.
Galutinį rezultatą apskaičiuoja mazgas „Finalize Aggregate“. Jei turite savo agregavimo funkcijas, nepamirškite jų pažymėti kaip „lygiagrečiai saugus“.
Darbuotojų procesų skaičius
Darbuotojų procesų skaičių galima padidinti neperkraunant serverio:
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 msKas čia vyksta? Darbo procesų buvo 2 kartus daugiau, o prašymas tapo tik 1,6599 karto greitesnis. Skaičiavimai įdomūs. Turėjome 2 darbuotojų procesus ir 1 vadovą. Po pakeitimo tapo 4+1.
Didžiausias lygiagretaus apdorojimo greitis: 5/3 = 1,66 (6) karto.
Kaip tai veikia?
Procesai
Užklausos vykdymas visada prasideda nuo pagrindinio proceso. Vadovas daro viską nelygiagrečiai ir tam tikrą lygiagretų apdorojimą. Kiti procesai, kurie atlieka tas pačias užklausas, vadinami darbuotojo procesais. Lygiagretus apdorojimas naudoja infrastruktūrą (nuo 9.4 versijos). Kadangi kitose PostgreSQL dalyse naudojami procesai, o ne gijos, užklausa su 3 darbuotojų procesais gali būti 4 kartus greitesnė nei tradicinis apdorojimas.
Sąveika
Darbuotojų procesai bendrauja su lyderiu per pranešimų eilę (pagal bendrą atmintį). Kiekvienas procesas turi 2 eiles: klaidoms ir eilėms.
Kiek darbo eigų reikia?
Minimali riba nurodoma parametru . Tada užklausų vykdytojas paima darbuotojų procesus iš parametro apriboto telkinio . Paskutinis apribojimas yra , tai yra bendras foninių procesų skaičius.
Jei nebuvo įmanoma paskirstyti darbuotojo proceso, apdorojimas bus vienas procesas.
Užklausų planavimo priemonė gali sumažinti darbo eigą, priklausomai nuo lentelės arba indekso dydžio. Tam yra parametrai и .
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 workerKiekvieną kartą lentelė yra 3 kartus didesnė nei min_parallel_(index|table)_scan_size, Postgres prideda darbuotojo procesą. Darbo eigų skaičius nepriklauso nuo išlaidų. Dėl žiedinės priklausomybės sudėtingas įgyvendinimas yra sudėtingas. Vietoj to, planuotojas naudoja paprastas taisykles.
Praktiškai šios taisyklės ne visada tinka gamybai, todėl galite pakeisti konkrečios lentelės darbuotojų procesų skaičių: ALTER TABLE ... SET (parallel_workers = N).
Kodėl lygiagretus apdorojimas nenaudojamas?
Be ilgo apribojimų sąrašo, taip pat yra išlaidų patikrinimai:
- vengti lygiagretaus trumpų užklausų apdorojimo. Šis parametras įvertina laiką, per kurį reikia paruošti atmintį, pradėti procesą ir pradinį apsikeitimą duomenimis.
: komunikacija tarp vadovo ir darbuotojų gali vėluoti proporcingai eilučių iš darbo procesų skaičiui. Šis parametras apskaičiuoja duomenų mainų kainą.
Įdėtos kilpos prisijungimai
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)Surinkimas vyksta paskutiniame etape, todėl įdėtos kilpos kairysis sujungimas yra lygiagreti operacija. Parallel Index Only Scan buvo pristatytas tik 10 versijoje. Jis veikia panašiai kaip lygiagretusis nuoseklus nuskaitymas. Būklė c_custkey = o_custkey nuskaito vieną užsakymą kiekvienoje kliento eilutėje. Taigi tai nėra lygiagreti.
Prisijunkite prie maišos
Kiekvienas darbuotojo procesas sukuria savo maišos lentelę iki PostgreSQL 11. Ir jei tokių procesų yra daugiau nei keturi, našumas nepagerės. Naujoje versijoje maišos lentelė yra bendrinama. Kiekvienas darbuotojo procesas gali naudoti WORK_MEM maišos lentelei sukurti.
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 ms12 užklausa iš TPC-H aiškiai rodo lygiagretų maišos ryšį. Kiekvienas darbuotojo procesas prisideda prie bendros maišos lentelės kūrimo.
Sujungti prisijungti
Sujungimas yra nelygiagretus pobūdis. Nesijaudinkite, jei tai paskutinis užklausos veiksmas – ji vis tiek gali būti vykdoma lygiagrečiai.
-- 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)„Sujungti sujungimo“ mazgas yra virš „Sujungti sujungimą“. Taigi sujungimas nenaudoja lygiagretaus apdorojimo. Tačiau „lygiagretaus indekso nuskaitymo“ mazgas vis tiek padeda segmentui part_pkey.
Sujungimas sekcijomis
„PostgreSQL 11“. pagal numatytuosius nustatymus išjungtas: jis turi labai brangų tvarkaraštį. Lenteles su panašiu skaidymu galima sujungti skaidiniu po skaidinio. Tokiu būdu Postgres naudos mažesnes maišos lenteles. Kiekvienas sekcijų sujungimas gali būti lygiagretus.
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)Svarbiausia, kad sekcijų jungtis būtų lygiagreti tik tuo atveju, jei šios sekcijos yra pakankamai didelės.
Lygiagretus priedas
gali būti naudojamas vietoj skirtingų blokų skirtingose darbo eigose. Paprastai tai atsitinka su UNION ALL užklausomis. Trūkumas yra mažesnis lygiagretumas, nes kiekvienas darbuotojas apdoroja tik 1 užklausą.
Čia veikia 2 darbuotojų procesai, nors 4 yra įjungti.
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)Svarbiausi kintamieji
- WORK_MEM riboja atmintį kiekvienam procesui, o ne tik užklausoms: work_mem procesus jungtys = daug atminties.
- — kiek darbuotojų procesų vykdanti programa naudos lygiagrečiam apdorojimui iš plano.
- — pritaiko bendrą darbininkų procesų skaičių prie procesoriaus branduolių skaičiaus serveryje.
- - tas pats, bet lygiagrečiai darbo procesams.
rezultatai
Nuo 9.6 versijos lygiagretus apdorojimas gali labai pagerinti sudėtingų užklausų, kurios nuskaito daug eilučių arba indeksų, našumą. PostgreSQL 10 lygiagretusis apdorojimas įgalintas pagal numatytuosius nustatymus. Nepamirškite jo išjungti serveriuose su dideliu OLTP darbo krūviu. Nuoseklus arba indeksinis nuskaitymas sunaudoja daug išteklių. Jei nevykdote viso duomenų rinkinio ataskaitos, galite pagerinti užklausos našumą tiesiog pridėdami trūkstamus indeksus arba naudodami tinkamą skaidymą.
Nuorodos
Šaltinis: www.habr.com