U moderne procesore se ugrađuje mnogo jezgri. Godinama su aplikacije slale upite u baze podataka paralelno. Ako je to upit za izvještavanje o velikom broju redaka u tablici, izvodi se brže kada se koristi više procesora, a u PostgreSQL-u je to moguće od verzije 9.6.
Bilo je potrebno 3 godine da se implementira funkcija paralelnih upita — kod je morao biti prepravljen u raznim fazama izvršavanja upita. U PostgreSQL 9.6 uvedena je infrastruktura za daljnje poboljšanje koda. U kasnijim verzijama, i drugi tipovi upita se izvode paralelno.
Ograničenja
- Ne uključujte paralelno izvršavanje ako su sve jezgre već zauzete, inače će drugi upiti biti usporeni.
- Najvažnije, paralelna obrada s visokim vrijednostima WORK_MEM koristi puno memorije — svaka hash veza ili sortiranje zauzimaju memoriju u obujmu work_mem.
- OLTP upiti s niskim kašnjenjem ne mogu se ubrzati paralelnim izvršavanjem. Ako upit vraća jedan redak, paralelna obrada će ga samo usporiti.
- Programeri vole koristiti TPC-H benchmark. Možda imate slične upite za idealno paralelno izvršavanje.
- Samo SELECT upiti bez predikativne blokade se izvode paralelno.
- Ponekad je pravilna indeksacija bolja od sekvencijalnog skeniranja tablice u paralelnom načinu.
- Suspended queries and cursors are not supported.
- Window functions and aggregate functions of ordered sets are not parallel.
- You gain nothing in I/O workload.
- There are no parallel sorting algorithms. However, queries with sorting can be executed in parallel in some aspects.
- Replace CTE (WITH ...) with a nested SELECT to enable parallel processing.
- Third-party data wrappers do not currently support parallel processing (though they could!)
- FULL OUTER JOIN is not supported.
- max_rows disables parallel processing.
- If there is a function in the query that is not marked as PARALLEL SAFE, it will be single-threaded.
- The SERIALIZABLE transaction isolation level disables parallel processing.
Test environment
PostgreSQL developers aimed to cut the response times of TPC-H benchmark queries. Download the benchmark and . Ovo je neformalna upotreba TPC-H benchmarka - ne za usporedbu baza podataka ili hardvera.
- Preuzmite TPC-H_Tools_v2.17.3.zip (ili noviju verziju) .
- Preimenujte makefile.suite u Makefile i izmijenite ga kao što je opisano ovdje: . Kompajlirajte kod naredbom make.
- Generirajte podatke:
.\/dbgen -s 10stvara bazu podataka od 23 GB. To je dovoljno da se vidi razlika u performansama paralelnih i neparalelnih upita. - Konvertirajte datoteke
tblucsv s forised. - Kloni repozitorij
pg_tpchi kopirajte datotekecsvupg_tpch\/dss\/data. - Kreirajte upite naredbom
qgen. - Učitajte podatke u bazu naredbom
.\/tpch.sh.
Paralelno sekvencijsko skeniranje
Ono može biti brže ne zbog paralelnog čitanja, već zato što su podaci raspršeni po mnogim CPU jezgrama. U modernim OS, datoteke podataka PostgreSQL se dobro keširaju. Sa pred-čitanjem možete dobiti iz spremišta blok veći od onoga što traži PG demon. Stoga performanse upita nisu ograničene na ulazno-izlazne operacije diska. Troši CPU cikluse za:
- čitati redove jedan po jedan sa stranica tablice;
- uspoređivati vrijednosti redova i uvjete
GDJE.
Izvršimo jednostavan upit 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 msSekvencijsko skeniranje daje previše redova bez agregacije, tako da se upit izvršava jednim CPU jezgrom.
Ako dodate SUM(), vidljivo je da će dva radna procesa pomoći ubrzati upit:
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)
-> Paralelno 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 msParalelna agregacija
Noda „Parallel Seq Scan“ proizvodi rezultate za djelomičnu agregaciju. Noda „Partial Aggregate“ skraćuje te rezultate pomoću SUM(). Na kraju, brojač SUM iz svake radne jedinice prikuplja se pomoću noda „Gather“.
Konačni rezultat izračunava se pomoću node „Finalize Aggregate“. Ako imate vlastite funkcije agregacije, ne zaboravite ih označiti kao „parallel safe“.
Broj radnih procesa
Broj radnih procesa može se povećati bez ponovnog pokretanja servera:
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)
-> Paralelno 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Što se ovdje događa? Radnih procesa je dvostruko više, a upit je postao samo 1,6599 puta brži. Izračuni su zanimljivi. Imali smo 2 radna procesa i 1 vođu. Nakon promjene sad imamo 4+1.
Naše maksimalno ubrzanje od paralelnog obrade: 5/3 = 1,66(6) puta.
Kako to radi?
Procesi
Izvršenje upita uvijek počinje s vodećim procesom. Vođa radi sve neparalelno i dio paralelnog obrade. Ostali procesi koji izvršavaju iste upite nazivaju se radnim procesima. Paralelna obrada koristi infrastrukturu (od verzije 9.4). Kako druge komponente PostgreSQL koriste procese, a ne niti, upit s 3 radna procesa mogao bi biti 4 puta brži od tradicionalne obrade.
Interakcija
Radni procesi komuniciraju s vođom putem reda poruka (na bazi zajedničke memorije). Svaki proces ima 2 reda: za greške i za tuple.
Koliko radnih procesa je potrebno?
Minimalno ograničenje postavlja parametar . Nakon toga izvršitelj upita uzima radne procese iz bazena, ograničenog parametrom . Posljednje ograničenje je , tj. ukupni broj pozadinskih procesa.
Ako nije moguće dodijeliti radni proces, obrada će biti jednoprosjedna.
Planer upita može smanjiti radne procese ovisno o veličini tablice ili indeksa. Za to postoje parametri i .
set min_parallel_table_scan_size='8MB'
8MB tablica => 1 radnik
24MB tablica => 2 radnika
72MB tablica => 3 radnika
x => log(x / min_parallel_table_scan_size) / log(3) + 1 radnikSvaki put kada je tablica 3 puta veća od min_parallel_(index|table)_scan_size, Postgres dodaje radni proces. Broj radnih procesa nije temeljen na troškovima. Ciklična ovisnost otežava složene implementacije. Umjesto toga, planer koristi jednostavna pravila.
U praksi ova pravila nisu uvijek primjenjiva za produkciju, pa je moguće promijeniti broj radnih procesa za određenu tablicu: ALTER TABLE … SET (parallel_workers = N).
Zašto se ne koristi paralelna obrada?
Osim dugog popisa ograničenja, postoje i provjere troškova:
— kako bi se izbjegla paralelna obrada kratkih upita. Ovaj parametar procjenjuje vrijeme potrebno za pripremu memorije, pokretanje procesa i inicijalnu razmjenu podataka.
: komunikacija vođe s radnicima može se produžiti proporcionalno broju tuplova od radnih procesa. Ovaj parametar izračunava troškove razmjene podataka.
Spajanje ugniježdenih petlji — Nested Loop Join
PostgreSQL 9.6+ može paralelno izvršavati ugniježdene petlje — to je jednostavna operacija.
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 '%specialposits%'
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 !~~ '%specialposits%'::text)Sakupljanje se odvija u posljednjoj fazi, tako da je Nested Loop Left Join — paralelna operacija. Parallel Index Only Scan pojavio se tek u verziji 10. Radi analogno paralelnom sekvencijalnom skeniranju. Uvjet c_custkey = o_custkey čitati jedan nalog za svaki klijentski redak. Dakle, nije paralelan.
Hash join — Hash Join
Svaki radni proces stvara svoju hash-tablicu do PostgreSQL 11. A ako je tih procesa više od četiri, performanse se neće poboljšati. U novoj verziji hash-tablica je zajednička. Svaki radni proces može koristiti WORK_MEM za stvaranje hash-tablice.
odabir
l_shipmode,
zbroj(case
kada o_orderpriority = '1-URGENT'
ili o_orderpriority = '2-HIGH'
onda 1
inače 0
kraj) kao high_line_count,
zbroj(case
kada o_orderpriority '1-URGENT'
i o_orderpriority '2-HIGH'
onda 1
inače 0
kraj) kao low_line_count
iz
narudžbe,
stavka
gdje
o_orderkey = l_orderkey
i l_shipmode u ('MAIL', 'AIR')
i l_commitdate < l_receiptdate
i l_shipdate = datum '1996-01-01'
i l_receiptdate < datum '1996-01-01' + interval '1' godina
grupa po
l_shipmode
naručite po
l_shipmode
LIMIT 1;
PLAN UPITA
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Ograničenje (trošak=1964755.66..1964961.44 redaka=1 širina=27) (stvarno vrijeme=7579.592..7922.997 redaka=1 petlje=1)
-> Finalizacija GrupaAgregacija (trošak=1964755.66..1966196.11 redaka=7 širina=27) (stvarno vrijeme=7579.590..7579.591 redaka=1 petlje=1)
Ključ grupe: lineitem.l_shipmode
-> Zbirka Merge (trošak=1964755.66..1966195.83 redaka=28 širina=27) (stvarno vrijeme=7559.593..7922.319 redaka=6 petlje=1)
Planned Workers: 4
Pokrenuti radnici: 4
-> Djelomična GrupaAgregacija (trošak=1963755.61..1965192.44 redaka=7 širina=27) (stvarno vrijeme=7548.103..7564.592 redaka=2 petlje=5)
Ključ grupe: lineitem.l_shipmode
-> Sort (trošak=1963755.61..1963935.20 redaka=71838 širina=27) (stvarno vrijeme=7530.280..7539.688 redaka=62519 petlje=5)
Ključ sortiranja: lineitem.l_shipmode
Metoda sortiranja: vanjsko spajanje Disk: 2304kB
Radnik 0: Metoda sortiranja: vanjsko spajanje Disk: 2064kB
Radnik 1: Metoda sortiranja: vanjsko spajanje Disk: 2384kB
Radnik 2: Metoda sortiranja: vanjsko spajanje Disk: 2264kB
Radnik 3: Metoda sortiranja: vanjsko spajanje Disk: 2336kB
-> Paralelno Hash Spajanje (trošak=382571.01..1957960.99 redaka=71838 širina=27) (stvarno vrijeme=7036.917..7499.692 redaka=62519 petlje=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Paralelno Sekvencijalno Skeniranje na lineitem (trošak=0.00..1552386.40 redaka=71838 širina=19) (stvarno vrijeme=0.583..4901.063 redaka=62519 petlje=5)
Filter: ((l_shipmode = BILO ('{MAIL,AIR}'::bpchar[])) I (l_commitdate < l_receiptdate) I (l_shipdate = '1996-01-01'::datum) I (l_receiptdate < '1997-01-01 00:00:00'::timestamp bez vremenske zone))
Redci uklonjeni filtriranjem: 11934691
-> Paralelno Hash (trošak=313722.45..313722.45 redaka=3750045 širina=20) (stvarno vrijeme=2011.518..2011.518 redaka=3000000 petlje=5)
Kante: 65536 Serije: 256 Korištenje memorije: 3840kB
-> Paralelno Sekvencijalno Skeniranje na narudžbama (trošak=0.00..313722.45 redaka=3750045 širina=20) (stvarno vrijeme=0.029..995.948 redaka=3000000 petlje=5)
Vrijeme planiranja: 0.977 ms
Vrijeme izvođenja: 7923.770 msUpit 12 iz TPC-H jasno prikazuje paralelno hash povezivanje. Svaka radna jedinica sudjeluje u stvaranju zajedničke hash tablice.
Spojivanje preko spajanja — Merge Join
Spojivanje preko spajanja nije paralelno po svojoj prirodi. Ne brinite ako je to posljednja faza upita, — može se i dalje izvršavati paralelno.
-- Upit 2 iz TPC-H
objašnjenje (troškovi isključeni) odaberi s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
iz part, supplier, partsupp, nation, region
gdje
p_partkey = ps_partkey
i s_suppkey = ps_suppkey
i p_size = 36
i p_type poput '%BRASS'
i s_nationkey = n_nationkey
i n_regionkey = r_regionkey
i r_name = 'AMERIKA'
i ps_supplycost = (
odaberi
min(ps_supplycost)
iz partsupp, supplier, nation, region
gdje
p_partkey = ps_partkey
i s_suppkey = ps_suppkey
i s_nationkey = n_nationkey
i n_regionkey = r_regionkey
i r_name = 'AMERIKA'
)
redom po s_acctbal opadajuće, n_name, s_name, p_partkey
LIMIT 100;
PLAN UPITA
----------------------------------------------------------------------------------------------------------
Limit
-> Sort
Ključ sortiranja: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
-> Merge Join
Spajanje uvjeta: (part.p_partkey = partsupp.ps_partkey)
Uvjet za spajanje: (partsupp.ps_supplycost = (SubPlan 1))
-> Gather Merge
Planirano radnika: 4
-> Paralelno pretraživanje indeksa koristeći <strong>part_pkey</strong> na dijelu
Filter: (((p_type)::text ~~ '%BRASS'::text) I (p_size = 36))
-> Materijalizirati
-> Sort
Ključ sortiranja: partsupp.ps_partkey
-> Ugniježđena petlja
-> Ugniježđena petlja
Uvjet za spajanje: (nation.n_regionkey = region.r_regionkey)
-> Seq Scan na region
Filter: (r_name = 'AMERIKA'::bpchar)
-> Hash Join
Hash uvjet: (supplier.s_nationkey = nation.n_nationkey)
-> Seq Scan na supplier
-> Hash
-> Seq Scan na nation
-> Pretraživanje indeksa koristeći idx_partsupp_suppkey na partsupp
Uvjet indeksa: (ps_suppkey = supplier.s_suppkey)
SubPlan 1
-> Agregat
-> Ugniježđena petlja
Uvjet za spajanje: (nation_1.n_regionkey = region_1.r_regionkey)
-> Seq Scan na region region_1
Filter: (r_name = 'AMERIKA'::bpchar)
-> Ugniježđena petlja
-> Ugniježđena petlja
-> Pretraživanje indeksa koristeći idx_partsupp_partkey na partsupp partsupp_1
Uvjet indeksa: (part.p_partkey = ps_partkey)
-> Pretraživanje indeksa koristeći supplier_pkey na supplier supplier_1
Uvjet indeksa: (s_suppkey = partsupp_1.ps_suppkey)
-> Pretraživanje indeksa koristeći nation_pkey na nation nation_1
Uvjet indeksa: (n_nationkey = supplier_1.s_nationkey)Čvor «Merge Join» nalazi se iznad «Gather Merge». Dakle, spajanje ne koristi paralelnu obradu. No, čvor «Parallel Index Scan» i dalje pomaže s segmentom. part_pkey.
Spojivanje po sekcijama
U PostgreSQL 11 onemogućeno prema zadanim postavkama: ima vrlo skupo planiranje. Tablice sa sličnim sekcioniranjem mogu se spajati sekcija po sekciju. Tako će Postgres koristiti manje hash-tablice. Svako spajanje sekcija može biti paralelno.
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;
PLAN UPITA
---------------------------------------------------
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;
PLAN UPITA
-----------------------------------------------------------
Gather
Planirani radnici: 4
-> Paralelno dodavanje
-> Paralelno hash spajanje
Hash Cond: (t2_1.b = t1_1.a)
-> Paralelno Seq Scan on prt2_p2 t2_1
Filter: ((b >= 0) AND (b <= 10000))
-> Paralelni Hash
-> Paralelno Seq Scan on prt1_p2 t1_1
Filter: (b = 0)
-> Paralelno hash spajanje
Hash Cond: (t2.b = t1.a)
-> Paralelno Seq Scan on prt2_p1 t2
Filter: ((b >= 0) AND (b <= 10000))
-> Paralelni Hash
-> Paralelno Seq Scan on prt1_p1 t1
Filter: (b = 0)Glavno je da spojavanje po sekcijama može biti paralelno, samo ako su te sekcije dovoljno velike.
Paralelno dodavanje — Parallel Append
može se koristiti umjesto različitih blokova u različitim radnim procesima. Obično se to događa s upitima UNION ALL. Nedostatak — manje paralelizma, jer svaki radni proces obrađuje samo 1 upit.
Ovdje su pokrenuta 2 radna procesa, iako je uključeno 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 <= 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)Najvažnije varijable
- WORK_MEM ograničava količinu memorije za svaki proces, ne samo za upite: work_mem procesi veze = jako puno memorije.
- — koliko će radnih procesa program koristiti za paralelnu obradu prema planu.
- — prilagođava ukupni broj radnih procesa prema broju CPU jezgri na poslužitelju.
- — isto, ali za paralelne radne procese.
Zaključci
Počevši od verzije 9.6, paralelna obrada može značajno poboljšati performanse složenih upita koji skeniraju mnogo redaka ili indeksa. U PostgreSQL 10, paralelno procesiranje je uključeno po defaultu. Ne zaboravite ga onemogućiti na poslužiteljima s velikim OLTP opterećenjem. Sekvencijalna skeniranja ili skeniranja indeksa troše jako puno resursa. Ako ne izvodite izvještaj na cijelom skupu podataka, možete učiniti upite učinkovitijima jednostavno dodavanjem nedostajućih indeksa ili korištenjem pravog sekcioniranja.
Linkovi
Izvor: habr.com
