Kaasaegsetel protsessoritel on palju südamikke. Aastaid on rakendused saatnud paralleelselt päringuid andmebaasidesse. Kui see on aruandepäring tabeli mitmel real, töötab see kiiremini, kui kasutatakse mitut protsessorit, ja PostgreSQL on seda suutnud teha alates versioonist 9.6.
Paralleelpäringu funktsiooni juurutamine võttis aega 3 aastat – päringu täitmise erinevates etappides pidime koodi ümber kirjutama. PostgreSQL 9.6 tutvustas koodi veelgi täiustamiseks infrastruktuuri. Järgmistes versioonides täidetakse paralleelselt muud tüüpi päringuid.
Piirangud
- Ärge lubage paralleelkäivitust, kui kõik tuumad on juba hõivatud, vastasel juhul aeglustuvad muud päringud.
- Kõige tähtsam on see, et paralleeltöötlus kõrgete WORK_MEM väärtustega kasutab palju mälu – iga räsiühendus või sortimine võtab work_mem mälu.
- Madala latentsusega OLTP-päringuid ei saa paralleelkäivitamisega kiirendada. Ja kui päring tagastab ühe rea, aeglustab paralleeltöötlus seda ainult.
- Arendajad armastavad kasutada TPC-H etaloni. Võib-olla on teil sarnaseid päringuid täiusliku paralleelse täitmise jaoks.
- Paralleelselt täidetakse ainult SELECT päringud ilma predikaatlukuta.
- Mõnikord on õige indekseerimine parem kui paralleelrežiimis järjestikune tabeli skaneerimine.
- Päringute ja kursorite peatamist ei toetata.
- Aknafunktsioonid ja järjestatud komplekti koondfunktsioonid ei ole paralleelsed.
- Te ei võida I/O töökoormusega midagi.
- Paralleelseid sorteerimisalgoritme ei ole. Kuid sorteerimisega päringuid saab mõnes aspektis paralleelselt täita.
- Paralleeltöötluse lubamiseks asendage CTE (WITH ...) pesastatud SELECT-ga.
- Kolmandate osapoolte andmemähised ei toeta veel paralleeltöötlust (kuid võiksid!)
- FULL OUTTER JOIN ei toetata.
- max_rows keelab paralleeltöötluse.
- Kui päringul on funktsioon, mis ei ole märgistatud PARALLELSELT OHUTUKS, on see ühe lõimega.
- Tehingu eraldatuse tase SERIALIZABLE keelab paralleelse töötlemise.
Testikeskkond
PostgreSQL-i arendajad püüdsid vähendada TPC-H võrdluspäringute reageerimisaega. Laadige alla võrdlusalus ja . See on TPC-H võrdlusaluse mitteametlik kasutamine – mitte andmebaasi ega riistvara võrdlemiseks.
- Laadige alla TPC-H_Tools_v2.17.3.zip (või uuem versioon) .
- Nimetage makefile.suite ümber nimeks Makefile ja muutke seda siin kirjeldatud viisil: . Koostage kood käsuga make.
- Andmete genereerimine:
./dbgen -s 10loob 23 GB andmebaasi. Sellest piisab, et näha erinevust paralleelsete ja mitteparalleelsete päringute toimivuses. - Failide teisendamine
tblвcsv с forиsed. - Kloonige hoidla
pg_tpchja kopeerige failidcsvвpg_tpch/dss/data. - Looge päringuid käsuga
qgen. - Laadige andmed andmebaasi käsuga
./tpch.sh.
Paralleelne järjestikune skaneerimine
See võib olla kiirem mitte paralleellugemise tõttu, vaid seetõttu, et andmed jagunevad paljude protsessori tuumade vahel. Kaasaegsetes operatsioonisüsteemides on PostgreSQL-i andmefailid hästi vahemällu salvestatud. Ettelugemisega on võimalik salvestusruumist saada suurem plokk, kui PG deemon nõuab. Seetõttu ei piira päringu jõudlust ketta I/O. See kulutab CPU tsükleid, et:
- loe tabelilehtedelt ridu ükshaaval;
- võrrelge stringi väärtusi ja tingimusi
WHERE.
Käivitame lihtsa päringu 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 msJärjestikune skannimine loob ilma liitmiseta liiga palju ridu, nii et päringu teostab üks CPU tuum.
Kui lisada SUM(), näete, et kaks töövoogu aitavad päringut kiirendada:
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 msParalleelne liitmine
Parallel Seq Scan sõlm toodab ridu osaliseks liitmiseks. Sõlm "Osaline koondamine" kärbib neid ridu kasutades SUM(). Lõpuks kogub sõlm „Kogu” iga töötaja protsessi SUM-i loenduri.
Lõpptulemuse arvutab sõlm "Lõpeta koond". Kui teil on oma liitmisfunktsioonid, ärge unustage neid märkida "paralleelturvaliseks".
Töötajate protsesside arv
Tööprotsesside arvu saab suurendada ilma serverit taaskäivitamata:
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 msMis siin toimub? Tööprotsesse oli 2 korda rohkem ja päring muutus vaid 1,6599 korda kiiremaks. Arvutused on huvitavad. Meil oli 2 töölist protsessi ja 1 juht. Pärast vahetust sai 4+1.
Meie maksimaalne kiirus paralleeltöötlusest: 5/3 = 1,66 (6) korda.
Kuidas see toimib?
Protsessid
Taotluse täitmine algab alati juhtiva protsessiga. Juht teeb kõike mitteparalleelselt ja mingit paralleelset töötlemist. Muid protsesse, mis täidavad samu taotlusi, nimetatakse tööprotsessideks. Paralleeltöötlus kasutab infrastruktuuri (alates versioonist 9.4). Kuna teised PostgreSQL-i osad kasutavad pigem protsesse kui lõime, võib 3 tööprotsessiga päring olla 4 korda kiirem kui traditsiooniline töötlemine.
Koostoime
Töötajate protsessid suhtlevad juhiga sõnumijärjekorra kaudu (ühismälu alusel). Igal protsessil on 2 järjekorda: vigade jaoks ja korteeži jaoks.
Kui palju töövooge on vaja?
Minimaalne piirmäär määratakse parameetriga . Seejärel võtab päringu käivitaja töötaja protsessid parameetriga piiratud kogumist . Viimane piirang on , st taustprotsesside koguarv.
Kui töötaja protsessi ei olnud võimalik eraldada, toimub töötlemine ühe protsessina.
Päringuplaneerija saab töövooge vähendada olenevalt tabeli või indeksi suurusest. Selle jaoks on parameetrid и .
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 workerIga kord on laud 3 korda suurem kui min_parallel_(index|table)_scan_size, Postgres lisab töötaja protsessi. Töövoogude arv ei põhine kuludel. Tsirkulaarne sõltuvus muudab keerukad rakendused keeruliseks. Selle asemel kasutab planeerija lihtsaid reegleid.
Praktikas ei sobi need reeglid alati tootmiseks, nii et saate muuta konkreetse tabeli töötaja protsesside arvu: ALTER TABLE ... SET (parallel_workers = N).
Miks paralleeltöötlust ei kasutata?
Lisaks pikale piirangute loetelule on olemas ka kulukontroll:
- vältida lühikeste päringute paralleelset töötlemist. See parameeter hindab mälu ettevalmistamiseks, protsessi käivitamiseks ja esialgseks andmevahetuseks kuluvat aega.
: suhtlemine juhi ja töötajate vahel võib hilineda proportsionaalselt tööprotsesside arvuga. See parameeter arvutab andmevahetuse maksumuse.
Pesastatud silmus liitub
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)Kogumine toimub viimases etapis, seega on pesastatud silmuse vasakühendus paralleeltoiming. Parallel Index Only Scan võeti kasutusele ainult versioonis 10. See toimib sarnaselt paralleelse jadasännimisega. Seisund c_custkey = o_custkey loeb ühe tellimuse kliendi stringi kohta. Nii et see pole paralleelne.
Hash Liitu
Iga töötaja protsess loob oma räsitabeli kuni versioonini PostgreSQL 11. Ja kui neid protsesse on rohkem kui neli, siis jõudlus ei parane. Uues versioonis on räsitabel jagatud. Iga töötaja protsess saab kasutada WORK_MEM-i räsitabeli loomiseks.
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 msTPC-H päring 12 näitab selgelt paralleelset räsiühendust. Iga töötaja protsess aitab kaasa ühise räsitabeli loomisele.
Ühenda Liitu
Ühendusliit on olemuselt mitteparalleelne. Ärge muretsege, kui see on päringu viimane samm – see võib siiski töötada paralleelselt.
-- 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)Sõlm "Ühenda liitu" asub "Kogu ühendamise" kohal. Seega ei kasuta liitmisel paralleeltöötlust. Kuid sõlm "Parallel Index Scan" aitab segmendiga siiski kaasa part_pkey.
Ühendus sektsioonide kaupa
PostgreSQL 11-s vaikimisi keelatud: sellel on väga kallis ajakava. Sarnase partitsiooniga tabeleid saab partitsioonide kaupa liita. Nii kasutab Postgres väiksemaid räsitabeleid. Iga sektsioonide ühendus võib olla paralleelne.
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)Peaasi, et sektsioonide ühendus oleks paralleelne ainult siis, kui need sektsioonid on piisavalt suured.
Paralleelne lisamine
saab kasutada erinevate plokkide asemel erinevates töövoogudes. Tavaliselt juhtub see UNION ALL päringutega. Puuduseks on väiksem paralleelsus, kuna iga töötaja töötleb ainult 1 päringu.
Siin töötab 2 tööprotsessi, kuigi 4 on lubatud.
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)Olulisemad muutujad
- WORK_MEM piirab mälu iga protsessi, mitte ainult päringute kohta: work_mem protsessid ühendused = palju mälu.
- — mitut töötaja protsessi täitev programm plaani paralleelseks töötlemiseks kasutab.
- — kohandab tööprotsesside koguarvu serveri CPU tuumade arvuga.
- - sama, kuid paralleelsete tööprotsesside jaoks.
Tulemused
Alates versioonist 9.6 võib paralleelne töötlemine oluliselt parandada keeruliste päringute jõudlust, mis skannivad palju ridu või indekseid. PostgreSQL 10-s on paralleeltöötlus vaikimisi lubatud. Ärge unustage seda suure OLTP-töökoormusega serverites keelata. Järjestikused skannimised või indekskontrollid kulutavad palju ressursse. Kui te ei käita aruannet kogu andmestiku kohta, saate päringu toimivust parandada, lisades lihtsalt puuduvad indeksid või kasutades õiget partitsiooni.
Viited
Allikas: www.habr.com