Kaasaegsetes CPU-des on palju tuumasid. Aastate jooksul on rakendused teinud päringuid andmebaasidesse paralleelselt. Kui see on aruanne, kus küsitakse mitmeid ridu tabelis, siis täidetakse see kiiremini, kui kasutatakse mitut CPU-d, ja PostgreSQL-is on see võimalik alates versioonist 9.6.
Funktsiooni paralleelsete päringute rakendamine võttis aega 3 aastat — päringute täitmise erinevatel etappidel tuli koodi ümber kirjutada. PostgreSQL 9.6-s ilmus infrastruktuur koodi edasiste täiustuste jaoks. Järgmistes versioonides saavad paralleelselt täidetud ka teised päringutüübid.
Piirangud
- Ärge lülitage paralleelset täitmist sisse, kui kõik tuumad on juba hõivatud, muidu saavad teised päringud takistusi.
- Kõige olulisem on see, et kõrge WORK_MEM väärtusega paralleelne töötlemine kasutab palju mälu — iga hash-ühendus või sorteerimine kasutab mälu mahus work_mem.
- Madala latentsusajaga OLTP päringute kiirus paralleelse täitmisega ei suurene. Ja kui päring tagastab ühe rea, aeglustab paralleelne töötlemine seda ainult.
- Arendajad armastavad kasutada TPC-H benchmarket. Võib-olla on teil sarnaseid päringuid ideaalse paralleelse täitmise jaoks.
- Ainult SELECT-päringud ilma predikaatide lukustamiseta täidetakse paralleelselt.
- Mõnikord on õige indekseerimine parem kui järjestikune tabeli skannimine paralleelses režiimis.
- Päringute peatamine ja kursori kasutamine ei ole toetatud.
- Akenfunktsioonid ja sorteeritud kogumite agregaatfunktsioonid ei ole paralleelsed.
- Te ei saa midagi kasulikku I/O koormuse osas.
- Paralleelseid sorteerimisalgoritme ei ole. Kuid sorteerimisega päringud võivad teatud aspektides toimuda paralleelselt.
- Asendage CTE (WITH ...) pesa vahekaardiga, et võimaldada paralleelset töötlemist.
- Kolmandate osapoolte andmete mähised ei toeta praegu paralleelset töötlemist (kuigi võiksid!).
- FULL OUTER JOIN ei ole toetatud.
- max_rows keelab paralleelse töötlemise.
- Kui päringus on funktsioon, mis ei ole märgistatud kui PARALLEL SAFE, siis see käivitub ühe niidina.
- Tehingu isoleerituse tase SERIALIZABLE keelab paralleelse töötlemise.
Testkeskkond
PostgreSQL arendajad püüdsid vähendada TPC-H testimist päringute vastuste aega. Laadige test ja . See on TPC-H testi mitteametlik kasutamine — mitte andmebaaside või riistvara võrdlemiseks.
- Laadige alla TPC-H_Tools_v2.17.3.zip (või uuem versioon) .
- Nimetage makefile.suite ümber Makefile-iks ja tehke muudatused, nagu siin kirjeldatud: . Kompileerige kood käsklusega make.
- Genereerige andmed:
./dbgen -s 10loob 23 GB suuruse andmebaasi. Seda piisab, et näha vahet paralleelsete ja mitteparalleelsete päringute vahel. - Konverteerige failid
tblühescsv formaatijased. - Kloonige hoidla
pg_tpchja kopeerige failidcsvühespg_tpch/dss/data. - Looge päringud käsuga
qgen. - Laadige andmed mitme käsuga
./tpch.sh.
Paralleelne järjestikune skaneerimine
See võib olla kiirem mitte paralleelse lugemise tõttu, vaid seetõttu, et andmed on hajutatud paljude CPU tuumade vahel. Kaasaegsed operatsioonisüsteemid vahemälustavad PostgreSQL andmefailid hästi. Eelnev lugemine võimaldab saada mälust ploki, mis on suurem kui PG deemon nõuab. Seetõttu ei ole päringu jõudlus piiratud ketta I/O-ga. See kasutab CPU tsükleid, et:
- lugeda ridu üksikute tabeli lehtedelt;
- võrrelda ridade väärtusi ja tingimusi
WHERE.
Tehkem lihtne päring select:
tpch=# selgitada analüüsige valikut l_quantity kui sum_qty alates lineitem, kus l_shipdate <= kuupäev '1998-12-01' - intervall '105' päev;
KÜSI PLANEERIMINE
--------------------------------------------------------------------------------------------------------------------------
Seq skannimine lineitem (kulu=0.00..1964772.00 read=58856235 laius=5) (tegelik aeg=0.014..16951.669 read=58839715 tsüklites=1)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::aeg ilma ajavööndita)
Filteri tõttu eemaldatud read: 1146337
Planeerimise aeg: 0.203 ms
Täideviimise aeg: 19035.100 msJärjekorraskannimine annab liiga palju read ilma agregatsioonita, mistõttu päring käitatakse ühe CPU tuuma poolt.
Kui lisada SUM(), on näha, et kaks tööprotsessi aitavad päringut kiirendada:
selgitada analüüsige valikut sum(l_quantity) kui sum_qty alates lineitem, kus l_shipdate <= kuupäev '1998-12-01' - intervall '105' päev;
KÜSI PLANEERIMINE
----------------------------------------------------------------------------------------------------------------------------------------------------
Lõpeta Aggregate (kulu=1589702.14..1589702.15 read=1 laius=32) (tegelik aeg=8553.365..8553.365 read=1 tsüklites=1)
-> Kogumise (kulu=1589701.91..1589702.12 read=2 laius=32) (tegelik aeg=8553.241..8555.067 read=3 tsüklites=1)
Plaanitavad töötajad: 2
Käivitamisprotsessid: 2
-> Osaline Aggregate (kulu=1588701.91..1588701.92 read=1 laius=32) (tegelik aeg=8547.546..8547.546 read=1 tsüklites=3)
-> Paralleelne Seq Scan lineitem (kulu=0.00..1527393.33 read=24523431 laius=5) (tegelik aeg=0.038..5998.417 read=19613238 tsüklites=3)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::aeg ilma ajavööndita)
Filteri tõttu eemaldatud read: 382112
Planeerimise aeg: 0.241 ms
Täideviimise aeg: 8555.131 msParalleelne agregatsioon
Nood «Parallel Seq Scan» genereerib read osalise agregatsiooni jaoks. Nood «Partial Aggregate» kärbib neid ridu, kasutades SUM(). Lõpus kogub iga tööprotsessi SUMi loendur nood «Gather».
Lõplik tulemus arvutatakse noodis «Finalize Aggregate». Kui teil on oma aggregeerimisfunktsioonid, ärge unustage neid tähistada kui «parallel safe».
Tööprotsesside arv
Tööprotsesside arvu saab suurendada ilma serverit taaskäivitamata:
selgitada analüüsige valikut sum(l_quantity) kui sum_qty alates lineitem, kus l_shipdate <= kuupäev '1998-12-01' - intervall '105' päev;
KÜSI PLANEERIMINE
----------------------------------------------------------------------------------------------------------------------------------------------------
Lõpeta Aggregate (kulu=1589702.14..1589702.15 read=1 laius=32) (tegelik aeg=8553.365..8553.365 read=1 tsüklites=1)
-> Kogumise (kulu=1589701.91..1589702.12 read=2 laius=32) (tegelik aeg=8553.241..8555.067 read=3 tsüklites=1)
Plaanitavad töötajad: 2
Käivitamisprotsessid: 2
-> Osaline Aggregate (kulu=1588701.91..1588701.92 read=1 laius=32) (tegelik aeg=8547.546..8547.546 read=1 tsüklites=3)
-> Paralleelne Seq Scan lineitem (kulu=0.00..1527393.33 read=24523431 laius=5) (tegelik aeg=0.038..5998.417 read=19613238 tsüklites=3)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::aeg ilma ajavööndita)
Filteri tõttu eemaldatud read: 382112
Planeerimise aeg: 0.241 ms
Täideviimise aeg: 8555.131 msMis siin toimub? Tööprotsesside arv on kahekordistunud, kuid päring on ainult 1,6599 korda kiirem. Arvutused on huvitavad. Meil oli 2 tööprotsessi ja 1 liider. Muudatuse järel on tulemuseks 4+1.
Meie maksimaalne kiirus parallel töötlemiselt: 5/3 = 1,66 (6) korda.
Kuidas see töötab?
Protsessid
Päringu täitmine algab alati liidris. Liider teeb kõik mitte-paralleelsed ja osa paralleelseid töötlemisi. Teised protsessid, mis täidavad samu päringuid, nimetatakse tööprotsessideks. Paralleelne töötlemine kasutab infrastruktuuri (versioonist 9.4). Kuna teised PostgreSQL osad kasutavad protsesse, mitte lõime, võis päringu täitmine kolme tööprotsessiga olla neli korda kiirem kui traditsiooniline töötlemine.
Koostoime
Tööprotsessid suhtlevad liidri ehk peaprotsessiga sõnumijärgijate kaudu (ühisest mälust). Igal protsessil on kaks järjekorda: vigade ja tupikute jaoks.
Kui palju tööprotsesse on vajalik?
Minimaalne piirang määratakse parameetriga . Seejärel võtab päringute täitja tööprotsessid basseinist, mille suurust piirab parameeter . Viimane piirang on , mis tähistab taustaprotsesside koguarvu.
Kui tööprotsessi eraldamine ebaõnnestub, toimub töötlemine ühes protsessis.
Päringute planeerija võib vähendada tööprotsesside arvu sõltuvalt tabeli või indeksi suurusest. Selle jaoks on olemas parameetrid ja .
set min_parallel_table_scan_size='8MB'
8MB tabel => 1 töötaja
24MB tabel => 2 töötajat
72MB tabel => 3 töötajat
x => log(x / min_parallel_table_scan_size) / log(3) + 1 töötajaIga kord, kui tabel on kolm korda suurem kui min_parallel_(index|table)_scan_size, Postgres lisab töövoo. Töövoogude arv ei põhine kuludel. Ringline sõltuvus raskendab keerukate rakenduste loomist. Selle asemel kasutab ajakava lihtsaid reegleid.
Praktikas ei sobi need reeglid alati tootmiseks, seetõttu on võimalik muuta töövoogude arvu konkreetse tabeli jaoks: ALTER TABLE … SET (parallel_workers = N).
Miks paralleelset töötlemist ei kasutata?
Pika piirangute nimekirja kõrval on olemas ka kulude kontrollid:
— et vältida paralleelset töötlemist lühikeste päringute jaoks. See parameeter hindab mälu ettevalmistamise, protsessi käivitamise ja esialgse andmevahetuse aega.
: juhipoolne suhtlemine töödega võib pikendada proportsionaalselt tööprotsesside kinnitusproovide arvuga. See parameeter arvutab andmevahetuse kulud.
Sisemised tsüklilised ühendused — Nested Loop Join
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, nii et Nested Loop Left Join on paralleelne operatsioon. Parallel Index Only Scan ilmus alles versioonis 10. See töötab sarnaselt paralleelsele järjestikusele skannimisele. Tingimus c_custkey = o_custkey loob ühe tellimuse iga kliendi rea kohta. Nii et see ei ole paralleelne.
Hash Join
Iga tööprotsess loob oma räsitabeli kuni PostgreSQL 11. Ja kui neid protsesse on rohkem kui neli, siis jõudlus ei parane. Uues versioonis on räsitabel ühine. Iga tööprotsess võib kasutada WORK_MEM-i, et luua räsitabel.
valige
l_shipmode,
summa(juhul
kui o_orderpriority = '1-URGENT'
või o_orderpriority = '2-HIGH'
siis 1
muidu 0
lõpp) kui high_line_count,
summa(juhul
kui o_orderpriority <> '1-URGENT'
ja o_orderpriority <> '2-HIGH'
siis 1
muidu 0
lõpp) kui low_line_count
from
orders,
lineitem
kus
o_orderkey = l_orderkey
ja l_shipmode in ('MAIL', 'AIR')
ja l_commitdate < l_receiptdate
ja l_shipdate < l_commitdate
ja l_receiptdate >= date '1996-01-01'
ja l_receiptdate < date '1996-01-01' + interval '1' aasta
grupi järgi
l_shipmode
järjekorras
l_shipmode
PIIRANG 1;
KÜSI PLAA
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Piirang (kulu=1964755.66..1964961.44 read=1 laius=27) (reaalne aeg=7579.592..7922.997 read=1 tsüklit=1)
-> Lõpeta GroupAggregate (kulu=1964755.66..1966196.11 read=7 laius=27) (reaalne aeg=7579.590..7579.591 read=1 tsüklit=1)
Grupp võtme: lineitem.l_shipmode
-> Koosta Merge (kulu=1964755.66..1966195.83 read=28 laius=27) (reaalne aeg=7559.593..7922.319 read=6 tsüklit=1)
Plaanitud Töötajad: 4
Käivitatud Töötajad: 4
-> Osaline GroupAggregate (kulu=1963755.61..1965192.44 read=7 laius=27) (reaalne aeg=7548.103..7564.592 read=2 tsüklit=5)
Grupp võtme: lineitem.l_shipmode
-> Sorteeri (kulu=1963755.61..1963935.20 read=71838 laius=27) (reaalne aeg=7530.280..7539.688 read=62519 tsüklit=5)
Sorteeri võtme: lineitem.l_shipmode
Sorteerimise meetod: väline ühine Disk: 2304kB
Töötaja 0: Sorteerimise meetod: väline ühine Disk: 2064kB
Töötaja 1: Sorteerimise meetod: väline ühine Disk: 2384kB
Töötaja 2: Sorteerimise meetod: väline ühine Disk: 2264kB
Töötaja 3: Sorteerimise meetod: väline ühine Disk: 2336kB
-> Paralleelne Hash Join (kulu=382571.01..1957960.99 read=71838 laius=27) (reaalne aeg=7036.917..7499.692 read=62519 tsüklit=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Paralleelne Seq Scan lineitem (kulu=0.00..1552386.40 read=71838 laius=19) (reaalne aeg=0.583..4901.063 read=62519 tsüklit=5)
Filter: ((l_shipmode = ANY ('{MAIL,AIR}'::bpchar[])) JA (l_commitdate < l_receiptdate) JA (l_shipdate < l_commitdate) JA (l_receiptdate >= '1996-01-01'::date) JA (l_receiptdate < '1997-01-01 00:00:00'::timestamp without time zone))
Readud read Filteri kaudu: 11934691
-> Paralleelne Hash (kulu=313722.45..313722.45 read=3750045 laius=20) (reaalne aeg=2011.518..2011.518 read=3000000 tsüklit=5)
Kasti: 65536 Batchid: 256 Mälu kasutamine: 3840kB
-> Paralleelne Seq Scan orders (kulu=0.00..313722.45 read=3750045 laius=20) (reaalne aeg=0.029..995.948 read=3000000 tsüklit=5)
Planeerimise aeg: 0.977 ms
Täitmise aeg: 7923.770 msTõe 12 TPC-H-i järgi illustreerib paralleelset hash-ühendust. Iga tööprotsess osaleb ühise hash-tabeli loomises.
Ühendus liitmisega — Merge Join
Liitmise ühendamine on iseenesest paralleelne. Ärge muretsege, kui see on päringu viimane etapp — see võib olla siiski paralleelne.
-- KÜSIMUS 2 TPC-H-st
selgita (kulud välja) vali s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
part, supplier, partsupp, nation, region
kus
p_partkey = ps_partkey
ja s_suppkey = ps_suppkey
ja p_size = 36
ja p_type nagu '%BRASS'
ja s_nationkey = n_nationkey
ja n_regionkey = r_regionkey
ja r_name = 'AMERICA'
ja ps_supplycost = (
vali
min(ps_supplycost)
partsupp, supplier, nation, region
kus
p_partkey = ps_partkey
ja s_suppkey = ps_suppkey
ja s_nationkey = n_nationkey
ja n_regionkey = r_regionkey
ja r_name = 'AMERICA'
)
järjesta s_acctbal järsult, n_name, s_name, p_partkey
PIIR 100;
KÜSIMUSE PLAAN
----------------------------------------------------------------------------------------------------------
Piir
-> Sort
Sortimise võti: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
-> Merge Join
Ühenduse tingimus: (part.p_partkey = partsupp.ps_partkey)
Ühenduse filter: (partsupp.ps_supplycost = (SubPlan 1))
-> Kogum Merge
Planeeritud töötajaid: 4
-> Paralleelne indeksiskannimine kasutades <strong>part_pkey</strong> osa
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)Node 'Merge Join' asub 'Gather Merge' kohal. Seega ei kasuta liitmine paralleelset töötlemist. Kuid node 'Parallel Index Scan' aitab siiski segmendi puhul. part_pkey.
Sektsioonide kaupa ühendamine
PostgreSQL 11-s on vaikimisi keelatud: sellel on väga kulukas planeerimine. Sarnase sektsiooniga tabelid saab ühendada sektsioonide kaupa. Nii kasutab Postgres väiksemaid hash-tabeleid. Iga sektsioonide vaheline ühendamine 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 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 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 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 Parallel Hash
-> Parallel Seq Scan on prt1_p1 t1
Filter: (b = 0)Peamine, sektsioonide vahelised ühendused on paralleelsed ainult siis, kui need sektsioonid on piisavalt suured.
Paralleelne lisamine — Parallel Append
võib kasutada erinevates töövoogudes erinevate plokkide asemel. See juhtub tavaliselt UNION ALL päringutega. Puuduseks on vähem paralleelsust, kuna iga tööprotsess töötleb ainult ühte päringut.
Siin on käivitatud 2 tööprotsessi, kuigi on aktiveeritud 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 Paralleelne lisamine
-> Agregaat
-> Seq Scan on lineitem
Filter: (l_shipdate Agregaat
-> Seq Scan on lineitem lineitem_1
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)Kõige olulisemad muutujad
- WORK_MEM piirdab mälu mahtu iga protsessi jaoks, mitte ainult päringute jaoks: work_mem protsessid ühendused = väga suur mälu.
- — kui palju tööprotsesse programm kasutab paralleelse töötlemise jaoks plaanist.
- — kohandab tööprotsesside üldarvu serveri CPU südamete arvu järgi.
- — sama, kuid paralleelsete tööprotsesside jaoks.
Kokkuvõte
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 paralleelne töötlemine vaikimisi sisse lülitatud. Ärge unustage seda välja lülitada OLTP-suurte töökoormuste serverites. Järjekindlad skannimised või indeksi skannimised tarbivad väga palju ressursse. Kui te ei tee aruannet kogu andmestiku ulatuses, on päringute tõhusamatel saavutamiseks lihtsalt vaja lisada puuduvad indeksid või kasutada õiget jagamist.
Lingid
Allikas: habr.com
