Moderni procesori imaju puno jezgara. Već godinama aplikacije šalju upite bazama podataka paralelno. Ako je to upit za izvještavanje prema više redova u tabeli, brži je kada koristi više procesora, a PostgreSQL je to mogao učiniti od verzije 9.6.
Trebalo je 3 godine da implementiram funkciju paralelnog upita - morao sam ponovo napisati kod u različitim fazama izvršenja upita. PostgreSQL 9.6 je uveo infrastrukturu za dalje poboljšanje koda. U narednim verzijama, drugi tipovi upita se izvršavaju paralelno.
Ograničenja
- Ne omogućavajte paralelno izvršavanje ako su sve jezgre već zauzete, inače će se drugi zahtjevi usporiti.
- Ono što je najvažnije, paralelna obrada s visokim WORK_MEM vrijednostima koristi puno memorije - svako hash pridruživanje ili sortiranje troši memoriju u količini work_mem.
- OLTP upiti male latencije ne mogu se ubrzati paralelnim izvršavanjem. A ako upit vrati samo jedan red, paralelna obrada će ga samo usporiti.
- Programeri vole da koriste TPC-H benchmark. Možda imate slične upite za savršeno paralelno izvršavanje.
- Samo SELECT upiti bez zaključavanja predikata se izvode paralelno.
- Ponekad je pravilno indeksiranje bolje od uzastopnog paralelnog skeniranja tabela.
- Pauziranje upita i kursori nisu podržani.
- Funkcije prozora i agregatne funkcije uređenog skupa nisu paralelne.
- Ništa ne dobijate na I/O radnom opterećenju.
- Ne postoje algoritmi za paralelno sortiranje. Ali upiti sa sortama mogu se izvoditi paralelno u nekim aspektima.
- Zamijenite CTE (WITH ...) sa ugniježđenim SELECT da omogućite paralelnu obradu.
- Strani omoti podataka još ne podržavaju paralelnu obradu (ali bi mogli!)
- FULL OUTER JOIN nije podržan.
- max_rows onemogućuje paralelnu obradu.
- Ako zahtjev ima funkciju koja nije označena kao PARALELNO SIGURNA, bit će jednonitna.
- Nivo izolacije transakcije SERIALIZABLE onemogućuje paralelnu obradu.
Testno okruženje
PostgreSQL programeri su pokušali da smanje vrijeme odgovora TPC-H benchmark upita. Preuzmite benchmark i . Ovo je nezvanična upotreba TPC-H benchmark-a - ne za poređenje baza podataka ili hardvera.
- Preuzmite TPC-H_Tools_v2.17.3.zip (ili noviji) .
- Preimenujte makefile.suite u Makefile i modificirajte kako je ovdje opisano: . Kompajlirajte kod sa make.
- Generirajte podatke:
./dbgen -s 10kreira bazu podataka od 23 GB. Ovo je dovoljno da se vidi razlika u performansama između paralelnih i neparalelnih upita. - Konvertujte fajlove
tblвcsv с forиsed. - Klonirajte spremište
pg_tpchi kopirajte fajlovecsvвpg_tpch/dss/data. - Kreirajte zahtjeve naredbom
qgen. - Učitajte podatke u bazu podataka pomoću naredbe
./tpch.sh.
Paralelno sekvencijalno skeniranje
Možda je brži ne zbog paralelnog čitanja, već zato što su podaci razbacani po mnogim CPU jezgrama. U modernim operativnim sistemima, PostgreSQL datoteke podataka su dobro keširane. Sa čitanjem unaprijed, moguće je iz skladišta dobiti blok veći nego što to zahtijeva PG demon. Stoga, performanse upita nisu ograničene diskovnim I/O. Troši CPU cikluse da:
- čitati redove jedan po jedan sa stranica tabele;
- uporedite vrijednosti niza i uslove
WHERE.
Pokrenimo 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 msSekvencijalno skeniranje daje previše redova bez agregacije, tako da se upit izvršava od strane jednog CPU jezgra.
Ako dodate SUM(), možete vidjeti da će dva toka posla pomoći da se ubrza 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)
-> 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 msParalelna agregacija
Čvor "Parallel Seq Scan" proizvodi redove za djelomičnu agregaciju. Čvor "Partial Aggregate" skraćuje ove redove sa SUM(). Na kraju, SUM brojač iz svakog toka posla prikuplja čvor prikupljanja.
Konačni rezultat izračunava čvor “Finalize Aggregate”. Ako imate vlastite funkcije agregacije, ne zaboravite ih označiti kao "paralelno sigurne".
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)
-> 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 mssta se desava ovde? Radnih procesa ima 2 puta više, a upit je samo 1,6599 puta brži. Proračuni su zanimljivi. Imali smo 2 radna procesa i 1 lidera. Nakon promjene postalo je 4+1.
Naše maksimalno ubrzanje iz paralelne obrade: 5/3 = 1,66(6) puta.
Как это работает?
Procesi
Izvršenje upita uvijek počinje vodećim procesom. Vođa radi sve neparalelno i neke od paralelnih procesa. Drugi procesi koji izvršavaju iste zahtjeve nazivaju se radničkim procesima. Paralelna obrada koristi infrastrukturu (od verzije 9.4). Budući da drugi dijelovi PostgreSQL-a koriste procese umjesto niti, upit sa 3 radna procesa mogao bi biti 4 puta brži od tradicionalne obrade.
Interakcija
Radnički procesi komuniciraju s vođom putem reda poruka (zasnovano na zajedničkoj memoriji). Svaki proces ima 2 reda: za greške i za tuple.
Koliko radnih procesa vam je potrebno?
Minimalna granica postavlja parametar . Tada izvršitelj zahtjeva preuzima radne procese iz bazena, ograničenog parametrom . Posljednje ograničenje je , što je ukupan broj pozadinskih procesa.
Ako nije bilo moguće dodijeliti radni proces, obrada će biti jednoprocesna.
Planer upita može smanjiti tokove posla ovisno o veličini tablice ili indeksa. Za ovo postoje opcije. и .
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 workerSvaki put tabela je 3 puta veća od min_parallel_(index|table)_scan_size, Postgres dodaje radni proces. Broj tokova posla nije zasnovan na troškovima. Kružna zavisnost otežava složene implementacije. Umjesto toga, planer koristi jednostavna pravila.
U praksi ova pravila nisu uvijek prikladna za proizvodnju, pa je moguće promijeniti broj radnih procesa za određenu tabelu: ALTER TABLE ... SET (parallel_workers = N).
Zašto se paralelna obrada ne koristi?
Pored dugačke liste ograničenja, tu su i provjere troškova:
- da se izbjegne paralelna obrada kratkih zahtjeva. Ovaj parametar procjenjuje vrijeme za pripremu memorije, pokretanje procesa i početnu razmjenu podataka.
: komunikacija između vođe i radnika može biti odgođena proporcionalno broju torki iz radnih procesa. Ovaj parametar uzima u obzir troškove razmjene podataka.
Pridruživanje ugniježđene petlje - Pridruživanje ugniježđene petlje
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)Sakupljanje se događa u posljednjem koraku, tako da je ugniježđena petlja lijevo spajanje paralelna operacija. Parallel Index Only Scan je predstavljen tek u verziji 10. Radi slično paralelnom sekvencijalnom skeniranju. Stanje c_custkey = o_custkey čita jedan nalog za svaku liniju klijenta. Dakle, nije paralelno.
Hash Join - Hash Join
Svaki radni proces kreira sopstvenu hash tabelu pre PostgreSQL 11. A ako postoji više od četiri ova procesa, performanse se neće poboljšati. U novoj verziji, heš tabela je zajednička. Svaki radni proces može koristiti WORK_MEM za kreiranje hash tablice.
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 msUpit 12 iz TPC-H ilustruje paralelno heš pridruživanje. Svaki radni proces učestvuje u kreiranju zajedničke hash tabele.
Merge Join
Spajanje spajanja je neparalelno po prirodi. Ne brinite ako je ovo posljednji korak upita - još uvijek može raditi paralelno.
-- 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)Čvor "Merge Join" je iznad "Gather Merge". Dakle, spajanje ne koristi paralelnu obradu. Ali čvor “Parallel Index Scan” i dalje pomaže u segmentu part_pkey.
Veza sekcije
U PostgreSQL 11 podrazumevano onemogućeno: ima veoma skupo zakazivanje. Tabele sa sličnim particijama mogu se spajati dio po dio. Ovo će učiniti da Postgres koristi manje hash tablice. Svaka veza sekcija može biti paralelna.
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)Glavna stvar je da je veza po sekcijama paralelna samo ako su ti dijelovi dovoljno veliki.
Paralelno dodavanje
može se koristiti umjesto različitih blokova u različitim tokovima rada. Ovo se obično dešava sa upitima UNION ALL. Loša strana je manja konkurentnost, jer svaki radni proces obrađuje samo 1 zahtjev.
Ovdje se pokreću 2 radna procesa, iako su 4 omogućena.
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 po procesu, a ne samo po zahtjevima: work_mem procesi veze = puno memorije.
- - koliko radnih procesa će program koji izvršava izvršavati koristiti za paralelnu obradu iz plana.
- - prilagođava ukupan broj radnih procesa broju CPU jezgara na serveru.
- - isto, ali za paralelne tokove rada.
Ishodi
Počevši od verzije 9.6, paralelna obrada može uvelike poboljšati performanse složenih upita koji skeniraju mnoge redove ili indekse. U PostgreSQL 10, paralelna obrada je podrazumevano omogućena. Ne zaboravite da ga onemogućite na serverima sa velikim opterećenjem OLTP-a. Sekvencijalno ili indeksno skeniranje je veoma intenzivno. Ako ne izvještavate o cijelom skupu podataka, upiti se mogu učiniti efikasnijim jednostavnim dodavanjem indeksa koji nedostaju ili korištenjem odgovarajućeg particioniranja.
reference
izvor: www.habr.com