Moderne CPU'er har mange kerner. I årevis har applikationer sendt forespørgsler til databaser parallelt. Hvis det er en rapporteringsforespørgsel til mange rækker i en tabel, udføres den hurtigere, når flere CPU'er bruges, og i PostgreSQL er dette muligt fra version 9.6.
Det tog tre år at implementere funktionen til parallelle forespørgsler - koden måtte skrives om på forskellige trin af forespørgslerne. I PostgreSQL 9.6 blev infrastrukturen til løbende forbedringer af koden introduceret. I de efterfølgende versioner udføres andre typer forespørgsler også parallelt.
Begrænsninger
- Aktiver ikke parallelt udførelse, hvis alle kerner allerede er optaget, ellers vil andre forespørgsler langsomme.
- Det vigtigste er, at parallel behandling med høje WORK_MEM-værdier bruger meget hukommelse - hver hash-forbindelse eller sortering optager hukommelse svarende til work_mem.
- OLTP-forespørgsler med lav forsinkelse kan ikke accelereres ved parallel udførelse. Og hvis forespørgslen returnerer én række, vil parallel behandling kun sænke den.
- Udviklere elsker at bruge TPC-H benchmark. Måske har du lignende forespørgsler til ideel parallel udførelse.
- Kun SELECT-forespørgsler uden prædikatlåsning udføres parallelt.
- Nogle gange er korrekt indeksering bedre end sekventiel scanning af tabellen i parallelt mode.
- Pause af forespørgsler og markører understøttes ikke.
- Vinduet og aggregatfunktioner til ordnede sæt er ikke parallelle.
- Du får ikke noget ud af input-output arbejdsbelastning.
- Der findes ingen parallelle sorteringsalgoritmer. Men forespørgsler med sorteringer kan udføres parallelt i nogle aspekter.
- Erstat CTE (WITH …) med en indlejret SELECT for at inkludere parallel behandling.
- Indpakninger af tredjepartsdata understøtter endnu ikke parallel behandling (men kunne gøre det!)
- FULL OUTER JOIN understøttes ikke.
- max_rows deaktiverer parallel behandling.
- Hvis der i forespørgslen er en funktion, der ikke er markeret som PARALLEL SAFE, vil den være en-trådet.
- Transaktionsisoleringsniveauet SERIALIZABLE deaktiverer parallel behandling.
Testmiljø
Udviklerne af PostgreSQL prøvede at reducere responstiden for forespørgsler i TPC-H benchmark. Download benchmarken og . Dette er en uofficiel brug af TPC-H benchmark - ikke til sammenligning af databaser eller hardware.
- Download TPC-H_Tools_v2.17.3.zip (or a newer version) .
- Rename makefile.suite to Makefile and modify it as described here: . Compile the code with the command make.
- Generate data:
./dbgen -s 10creates a database of 23 GB. This is enough to see the performance difference between parallel and non-parallel queries. - Convert files
tblicsv with forogsed. - Clone the repository
pg_tpchand copy the filescsvipg_tpch/dss/data. - Create queries with the command
qgen. - Load data into the database with the command
./tpch.sh.
Parallel sequential scan
It may be faster not due to parallel reading but because data is spread across many CPU cores. In modern OS, PostgreSQL data files are well cached. With prefetching, a block larger than requested by the PG daemon can be read from storage. Therefore, the performance of the query is not limited by disk I/O. It consumes CPU cycles to:
- read rows one by one from table pages;
- compare row values and conditions
HVOR.
Let's execute a simple query 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 msA sequential scan gives too many rows without aggregation, so the query is executed by a single CPU core.
If you add SUM(), it shows that two worker processes will help speed up the query:
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)
-> 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 msParallel aggregation
The 'Parallel Seq Scan' node produces rows for partial aggregation. The 'Partial Aggregate' node trims these rows using SUM(). In the end, the SUM counter from each worker process is gathered by the 'Gather' node.
Det endelige resultat beregnes af noden „Finalize Aggregate“. Hvis du har dine egne aggregeringsfunktioner, skal du huske at markere dem som „parallel safe“.
Antal arbejdsprocesser
Antallet af arbejdsprocesser kan øges uden genstart af serveren:
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)
-> 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 msHvad sker der her? Antallet af arbejdsprocesser er blevet fordoblet, og forespørgslen blev kun 1,6599 gange hurtigere. Beregningerne er interessante. Vi havde 2 arbejdsprocesser og 1 leder. Efter ændringen blev det 4+1.
Vores maksimale acceleration fra parallelbehandling: 5/3 = 1,66(6) gange.
Hvordan fungerer det?
Processer
Udførelsen af forespørgslen begynder altid med den ledende proces. Lederen udfører alt, der ikke er parallelt, og en del af den parallelle behandling. Andre processer, der udfører de samme forespørgsler, kaldes arbejdsprocesser. Parallelbehandling bruger infrastrukturen (siden version 9.4). Da andre dele af PostgreSQL bruger processer, og ikke tråde, kunne en forespørgsel med 3 arbejdsprocesser være 4 gange hurtigere end traditionel behandling.
Interaktion
Arbejdsprocesserne kommunikerer med lederen via en meddelelseskø (baseret på delt hukommelse). Hver proces har 2 køer: til fejl og til tuples.
Hvor mange arbejdsprocesser er nødvendige?
Det minimale loft gives af parameteren . Derefter tager forespørgselsudføreren arbejdsprocesser fra puljen, som er begrænset af parameteren . Den sidste begrænsning er , hvilket er det samlede antal baggrundsprocesser.
Hvis det ikke er muligt at allokere en arbejdsproces, vil behandlingen være en-proces.
Forespørgselsplanlæggeren kan reducere arbejdsprocesserne afhængigt af størrelsen på tabellen eller indekset. Der er parametre til dette og .
set min_parallel_table_scan_size='8MB'
8MB tabel => 1 arbejder
24MB tabel => 2 arbejdere
72MB tabel => 3 arbejdere
x => log(x / min_parallel_table_scan_size) / log(3) + 1 arbejderHver gang tabellen er 3 gange større end min_parallel_(index|table)_scan_size, tilføjer Postgres en arbejdsproces. Antallet af arbejdsprocesser er ikke baseret på omkostninger. Cirkulær afhængighed komplicerer komplekse implementeringer. I stedet bruger planlæggeren enkle regler.
I praksis er disse regler ikke altid passende til produktionen, så man kan ændre antallet af arbejdsprocesser for en bestemt tabel: ALTER TABLE … SET (parallel_workers = N).
Hvorfor bruges parallelbehandling ikke?
Udover den lange liste af begrænsninger er der også omkostningskontroller:
— for at undgå parallel behandling af korte forespørgsler. Denne parameter estimerer tiden til at forberede hukommelsen, starte processen og den indledende dataudveksling.
: kommunikationen mellem lederen og arbejdstagerne kan trække ud i forhold til antallet af tuples fra arbejdsprocesserne. Denne parameter beregner omkostningerne ved dataudveksling.
Nedlagte loop-sammenføjning — Nested Loop Join
PostgreSQL 9.6+ kan udføre indlejrede løkker parallelt — dette er en simpel operation.
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)Indsamlingen sker i det sidste trin, så Nested Loop Left Join er en parallel operation. Parallel Index Only Scan kom først med version 10. Den fungerer på samme måde som parallel sekventiel scanning. Betingelsen c_custkey = o_custkey læsser én ordre for hver kundelinje. Så det er ikke parallelt.
Hash-sammenføjning — Hash Join
Hver arbejdsproces opretter sin egen hash-tabel indtil PostgreSQL 11. Og hvis der er mere end fire processer, vil ydeevnen ikke stige. I den nye version er hash-tabellen fælles. Hver arbejdsproces kan bruge WORK_MEM til at oprette en hash-tabel.
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 msForespørgsel 12 fra TPC-H viser tydeligt parallel hash join. Hver arbejdsgang deltager i oprettelsen af en fælles hash-tabel.
Sammenføjning - Merge Join
Sammenføjning er i sin natur ikke parallel. Bekymr dig ikke, hvis dette er det sidste trin i forespørgslen - det kan stadig udføres parallelt.
-- Query 2 fra TPC-H
forklar (omkostninger fra) vælg s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
fra part, leverandør, partsupp, nation, region
hvor
p_partkey = ps_partkey
og s_suppkey = ps_suppkey
og p_size = 36
og p_type som '%BRASS'
og s_nationkey = n_nationkey
og n_regionkey = r_regionkey
og r_name = 'AMERIKA'
og ps_supplycost = (
vælg
min(ps_supplycost)
fra partsupp, leverandør, nation, region
hvor
p_partkey = ps_partkey
og s_suppkey = ps_suppkey
og s_nationkey = n_nationkey
og n_regionkey = r_regionkey
og r_name = 'AMERIKA'
)
bestil efter s_acctbal desc, n_name, s_name, p_partkey
BEGRÆNS 100;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Begrænsning
-> Sorter
Sortér nøgle: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
-> Merge Join
Merge betingelse: (part.p_partkey = partsupp.ps_partkey)
Join filter: (partsupp.ps_supplycost = (SubPlan 1))
-> Saml Merge
Arbejdere planlagt: 4
-> Parallel indeks scanning ved brug af <strong>part_pkey</strong> på part
Filter: (((p_type)::text ~~ '%BRASS'::text) OG (p_size = 36))
-> Materialiser
-> Sorter
Sortér nøgle: partsupp.ps_partkey
-> Indlejret løkke
-> Indlejret løkke
Join filter: (nation.n_regionkey = region.r_regionkey)
-> Sekventiel scanning på region
Filter: (r_name = 'AMERIKA'::bpchar)
-> Hash Join
Hash betingelse: (supplier.s_nationkey = nation.n_nationkey)
-> Sekventiel scanning på leverandør
-> Hash
-> Sekventiel scanning på nation
-> Indeks scanning ved brug af idx_partsupp_suppkey på partsupp
Indeks betingelse: (ps_suppkey = supplier.s_suppkey)
SubPlan 1
-> Aggregat
-> Indlejret løkke
Join filter: (nation_1.n_regionkey = region_1.r_regionkey)
-> Sekventiel scanning på region region_1
Filter: (r_name = 'AMERIKA'::bpchar)
-> Indlejret løkke
-> Indlejret løkke
-> Indeks scanning ved brug af idx_partsupp_partkey på partsupp partsupp_1
Indeks betingelse: (part.p_partkey = ps_partkey)
-> Indeks scanning ved brug af supplier_pkey på leverandør supplier_1
Indeks betingelse: (s_suppkey = partsupp_1.ps_suppkey)
-> Indeks scanning ved brug af nation_pkey på nation nation_1
Indeks betingelse: (n_nationkey = supplier_1.s_nationkey)Noden „Merge Join“ er over „Gather Merge“. Så sammenføjning bruger ikke parallel behandling. Men noden „Parallel Index Scan“ hjælper stadig med segmentet. part_pkey.
Sammenføjning efter sektioner
I PostgreSQL 11 er som standard deaktiveret: den har meget omkostningsfuld planlægning. Tabeller med lignende sektionering kan sammenslås sektion for sektion. Så Postgres vil bruge mindre hash-tabeller. Hver sektion sammenføjning kan være parallel.
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)Det vigtigste er, at sammenføjning efter sektioner kun kan være parallel, hvis disse sektioner er store nok.
Parallel tilføjelse - Parallel Append
kan bruges i stedet for forskellige blokke i forskellige arbejdsgange. Dette ses ofte med UNION ALL forespørgsler. Ulempen er mindre parallelisme, da hver arbejdsgang kun behandler 1 forespørgsel.
Her kører 2 arbejdsgange, selvom 4 er inkluderet.
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)De vigtigste variable
- WORK_MEM begrænser den mængde hukommelse, der er til rådighed for hver proces, ikke kun for forespørgsler: work_mem processer forbindelser = meget hukommelse.
- — hvor mange arbejdsprocesser programmet vil bruge til paralleldbehandling af planen.
- — tilpasser det samlede antal arbejdsprocesser efter antallet af CPU-kerner på serveren.
- — det samme, men for parallele arbejdsprocesser.
Resumé
Fra version 9.6 kan paralleldbehandling væsentligt forbedre ydeevnen af komplekse forespørgsler, der scanner mange rækker eller indekser. I PostgreSQL 10 er paralleldbehandling slået til som standard. Husk at deaktivere det på servere med høj OLTP-belastning. Sekventielle scanninger eller indeksscanninger bruger meget ressourcer. Hvis du ikke udfører rapporten på hele datasættet, kan forespørgsler gøres mere effektive ved blot at tilføje manglende indekser eller bruge korrekt partitionering.
Links
Kilde: habr.com
