Paralelni zahtevi u PostgreSQL

Paralelni zahtevi u PostgreSQL
U modernim CPU ima mnogo jezgara. Godinama su aplikacije slale zahteve u baze podataka paralelno. Ako je to izveštajni zahtev za više redova u tabeli, izvršava se brže kada se koriste više CPU-a, a u PostgreSQL-u to je moguće od verzije 9.6.

Treba tri godine da se implementira funkcija paralelnih zahteva — bilo je potrebno prepisivati kod u različitim fazama izvršavanja zahteva. U PostgreSQL 9.6 uvedena je infrastruktura za daljnje poboljšanje koda. U narednim verzijama i druge vrste zahteva se izvršavaju paralelno.

Ograničenja

  • Ne uključujte paralelno izvršavanje ako su sva jezgra već zauzeta, inače će drugi zahtevi usporiti.
  • Najvažnije je to da paralelna obrada sa visokim vrednostima WORK_MEM zahteva mnogo memorije — svaka hašna veza ili sortiranje zauzima memoriju u volumenu work_mem.
  • OLTP zahtevi sa niskom latencijom se ne mogu ubrzati paralelnim izvršavanjem. A ako zahtev vraća jedan red, paralelna obrada ga samo usporava.
  • Programeri vole da koriste TPC-H benchmark. Možda imate slične zahteve za savršeno paralelno izvršavanje.
  • Samo SELECT zahtevi bez predikatske blokade se izvršavaju paralelno.
  • Ponekad je pravilna indeksacija bolja od sekvencijalnog skeniranja tabele u paralelnom režimu.
  • Pauziranje zahteva i kursora nije podržano.
  • Prozorske funkcije i agregatne funkcije uređenih skupova nisu paralelne.
  • Ne dobijate ništa u radnom opterećenju ulaza-izlaza.
  • Ne postoje paralelni algoritmi sortiranja. Međutim, zahtevi sa sortiranjem se mogu izvršavati paralelno u nekim aspektima.
  • Zamenite CTE (WITH …) sa ugniježdenim SELECT-om kako biste omogućili paralelnu obradu.
  • Omotnici spoljašnjih podataka trenutno ne podržavaju paralelnu obradu (mada bi mogli!)
  • FULL OUTER JOIN nije podržan.
  • max_rows onemogućava paralelnu obradu.
  • Ako zahtevu postoji funkcija koja nije označena kao PARALLEL SAFE, biće jednopotni.
  • Nivo izolacije transakcije SERIALIZABLE onemogućava paralelnu obradu.

Testna okruženja

Programeri PostgreSQL su pokušali smanjiti vreme odziva zahteva benchmarka TPC-H. Preuzmite benchmark i prilagodite ga PostgreSQL-u. Ovo je neautorizovana upotreba benchmarka TPC-H — nije za upoređivanje baza podataka ili opreme.

  1. Preuzmite TPC-H_Tools_v2.17.3.zip (ili noviju verziju) sa TPC zvanične stranice.
  2. Preimenujte makefile.suite u Makefile i izmenite, kako je ovde opisano: https://github.com/tvondra/pg_tpch . Kompajlirajte kod komandom make.
  3. Generišite podatke: ./dbgen -s 10 stvara bazu podataka od 23 GB. To je dovoljno da se vidi razlika u performansama paralelnih i neparalelnih upita.
  4. Konvertujte datoteke tbl u csv sa for i sed.
  5. Klonirajte repozitorij pg_tpch i kopirajte datoteke csv u pg_tpch/dss/data.
  6. Kreirajte upite komandom qgen.
  7. Učitajte podatke u bazu komandom ./tpch.sh.

Paralelno sekvencijalno skeniranje

Može biti brže ne zbog paralelnog čitanja, već zbog toga što su podaci raspoređeni po više CPU jezgara. U savremenim operativnim sistemima, datoteke podataka PostgreSQL se dobro keširaju. Sa anticipativnim čitanjem možete dobiti više blokova iz skladišta nego što PG demon zahteva. Stoga performanse upita nisu ograničene ulazno-izlaznim vremenom diska. Troši CPU cikluse da:

  • čita redove jedan po jedan sa stranica tabele;
  • upoređuje vrednosti redova i uslove 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 ms

Sekvencijalno skeniranje daje previše redova bez agregacije, pa se upit izvršava jednim CPU jezgrom.

Ako dodamo SUM(), može se primetiti da će dva radna procesa pomoći u ubrzavanju upita:

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 ms

Paralelna agregacija

Čvor «Parallel Seq Scan» proizvodi redove za delimičnu agregaciju. Čvor «Partial Aggregate» skraćuje ove redove pomoću SUM(). Na kraju, SUM prebrojava iz svakog radnog procesa se sabira u čvoru «Gather».

Konačni rezultat izračunava se putem čvora «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  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 ms

Šta se ovde dešava? Broj radnih procesa je udvostručen, a zahtev je postao 1,6599 puta brži. Izgleda zanimljivo. Imali smo 2 radna procesa i 1 lidera. Nakon promene sada imamo 4+1.

Naša maksimalna brzina paralelnog obrade: 5/3 = 1,66(6) puta.

Kako to radi?

Procesi

Izvršenje zahteva uvek počinje od vodećeg procesa. Lider obavlja sve neprilagodljive i deo paralelne obrade. Ostali procesi koji obrađuju iste zahteve nazivaju se radnim procesima. Paralelna obrada koristi infrastrukturu dinamičkih pozadinskih radnih procesa (od verzije 9.4). Kako druge komponente PostgreSQL koriste procese, a ne niti, zahtev sa 3 radna procesa mogao bi biti 4 puta brži od tradicionalne obrade.

Interakcija

Radni procesi komuniciraju s liderom 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 max_parallel_workers_per_gather. Zatim izvršitelj zahteva uzima radne procese iz bazena, ograničenog parametrom max_parallel_workers size. Poslednje ograničenje je max_worker_processes, tj. ukupan broj pozadinskih procesa.

Ako ne uspe da dodeli radni proces, obrada će biti jednoprosesna.

Planer zahteva može smanjiti radne procese u zavisnosti od veličine tabele ili indeksa. Postoje parametri min_parallel_table_scan_size i min_parallel_index_scan_size.

postavi min_parallel_table_scan_size='8MB'
8MB tabela => 1 radnik
24MB tabela => 2 radnika
72MB tabela => 3 radnika
x => log(x / min_parallel_table_scan_size) / log(3) + 1 radnik

Svaki put kada je tabela 3 puta veća od min_parallel_(index|table)_scan_size, Postgres dodaje radni proces. Broj radnih procesa nije zasnovan na troškovima. Ciklična zavisnost otežava složene implementacije. Umesto toga, planer koristi jednostavna pravila.

U praksi ova pravila nisu uvek primenljiva za produkciju, pa se može promeniti broj radnih procesa za određenu tabelu: ALTER TABLE … SET (parallel_workers = N).

Zašto se paralelna obrada ne koristi?

Osim dugog popisa ograničenja, postoje još i troškovi provjere:

parallel_setup_cost — kako bi se izbjeglo paralelno obrađivanje kratkih upita. Ova stavka procjenjuje vrijeme za pripremu memorije, pokretanje procesa i početnu razmjenu podataka.

parallel_tuple_cost: komunikacija vođe sa radnicima može trajati proporcionalno broju kuta iz radnih procesa. Ova stavka izračunava troškove razmjene podataka.

Spojevi ugniježdenih petlji — Nested Loop Join

PostgreSQL 9.6+ može izvršavati ugniježdene petlje paralelno — 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;
 PLAN UPITA
--------------------------------------------------------------------------------------
 Finalize GroupAggregate
 Ključ grupe: customer.c_custkey
 -> Gather Merge
 Planirani radnici: 4
 -> Partial GroupAggregate
 Ključ grupe: customer.c_custkey
 -> Nested Loop Left Join
 -> Paralelno skeniranje indeksa samo koristeći customer_pkey na customer
 -> Skeniranje indeksa koristeći idx_orders_custkey na orders
 Uslov indeksa: (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 je uveden tek u verziji 10. Radi na sličan način kao paralelno sekvencijalno skeniranje. Uslov c_custkey = o_custkey učitava jednu narudžbu za svaki red klijenta. Stoga nije paralelno.

Hash Join — Hash Join

Svaki radni proces stvara svoju hash-tablicu do PostgreSQL 11. I ako tih procesa ima 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.

izaberi
        l_shipmode,
        zbir(case
                kada je o_orderpriority = '1-URGENT'
                        ili o_orderpriority = '2-HIGH'
                        onda 1
                inače 0
        kraj) kao high_line_count,
        zbir(case
                kada je 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   Finalizuj Grupnu Agregaciju  (trošak=1964755.66..1966196.11 redovi=7 širina=27) (aktuelno vreme=7579.590..7579.591 redovi=1 petlje=1)
         Ključ Grupe: lineitem.l_shipmode
         ->  Prikupi Spojeno  (trošak=1964755.66..1966195.83 redovi=28 širina=27) (aktuelno vreme=7559.593..7922.319 redovi=6 petlje=1)
               Planirani Radnici: 4
               Pokrenuti Radnici: 4
               ->  Djelimična Grupna Agregacija  (trošak=1963755.61..1965192.44 redovi=7 širina=27) (aktuelno vreme=7548.103..7564.592 redovi=2 petlje=5)
                     Ključ Grupe: lineitem.l_shipmode
                     ->  Sortiranje  (trošak=1963755.61..1963935.20 redovi=71838 širina=27) (aktuelno vreme=7530.280..7539.688 redovi=62519 petlje=5)
                           Ključ Sortiranja: lineitem.l_shipmode
                           Metoda Sortiranja: spoljnje spajanje  Disk: 2304kB
                           Radnik 0:  Metoda Sortiranja: spoljnje spajanje  Disk: 2064kB
                           Radnik 1:  Metoda Sortiranja: spoljnje spajanje  Disk: 2384kB
                           Radnik 2:  Metoda Sortiranja: spoljnje spajanje  Disk: 2264kB
                           Radnik 3:  Metoda Sortiranja: spoljnje spajanje  Disk: 2336kB
                           ->  Paralelno Hash Spajanje  (trošak=382571.01..1957960.99 redovi=71838 širina=27) (aktuelno vreme=7036.917..7499.692 redovi=62519 petlje=5)
                                 Hash Uslov: (lineitem.l_orderkey = orders.o_orderkey)
                                 ->  Paralelno Sekvencijalno Skenerisanje na lineitem  (trošak=0.00..1552386.40 redovi=71838 širina=19) (aktuelno vreme=0.583..4901.063 redovi=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   Paralelno Hash  (trošak=313722.45..313722.45 redovi=3750045 širina=20) (aktuelno vreme=2011.518..2011.518 redovi=3000000 petlje=5)
                                       Kante: 65536  Serije: 256  Korišćenje Memorije: 3840kB
                                       ->  Paralelno Sekvencijalno Skenerisanje na naredbama  (trošak=0.00..313722.45 redovi=3750045 širina=20) (aktuelno vreme=0.029..995.948 redovi=3000000 petlje=5)
 Vreme Planiranja: 0.977 ms
 Vreme Izvršenja: 7923.770 ms

Upit 12 iz TPC-H jasno prikazuje paralelno hash spajanje. Svaki radni proces učestvuje u kreiranju zajedničke hash tabele.

Spoj kroz spajanje — Merge Join

Spoj kroz spajanje je inherentno nep paralelan. Ne brinite se ako je ovo poslednja faza upita — ona se i dalje može izvršavati paralelno.

-- Upit 2 iz TPC-H
objasni (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'
        )
redosljed s_acctbal opadajuće, n_name, s_name, p_partkey
LIMIT 100;
                                                PLAN UPITA
----------------------------------------------------------------------------------------------------------
 Limit
   -&gt;  Sortiranje
         Ključ sortiranja: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
         -&gt;  Merge Join
               Merge Uslov: (part.p_partkey = partsupp.ps_partkey)
               Uslov spajanja: (partsupp.ps_supplycost = (Podplan 1))
               -&gt;  Prikupljanje spajanja
                     Planirani radnici: 4
                     -&gt;  Paralelno skeniranje indeksa preko <strong>part_pkey</strong> na part
                           Filter: (((p_type)::text ~~ '%BRASS'::text) I (p_size = 36))
               -&gt;  Materijalizacija
                     -&gt;  Sortiranje
                           Ključ sortiranja: partsupp.ps_partkey
                           -&gt;  Ugniježdena petlja
                                 -&gt;  Ugniježdena petlja
                                       Uslov spajanja: (nation.n_regionkey = region.r_regionkey)
                                       -&gt;  Započni skeniranje na region
                                             Filter: (r_name = 'AMERIKA'::bpchar)
                                       -&gt;  Hash Join
                                             Hash Uslov: (supplier.s_nationkey = nation.n_nationkey)
                                             -&gt;  Započni skeniranje na supplier
                                             -&gt;  Hash
                                                   -&gt;  Započni skeniranje na nation
                                 -&gt;  Skeniranje indeksa koristeći idx_partsupp_suppkey na partsupp
                                       Uslov indeksa: (ps_suppkey = supplier.s_suppkey)
               Podplan 1
                 -&gt;  Agregat
                       -&gt;  Ugniježdena petlja
                             Uslov spajanja: (nation_1.n_regionkey = region_1.r_regionkey)
                             -&gt;  Započni skeniranje na region region_1
                                   Filter: (r_name = 'AMERIKA'::bpchar)
                             -&gt;  Ugniježdena petlja
                                   -&gt;  Ugniježdena petlja
                                         -&gt;  Skeniranje indeksa koristeći idx_partsupp_partkey na partsupp partsupp_1
                                               Uslov indeksa: (part.p_partkey = ps_partkey)
                                         -&gt;  Skeniranje indeksa koristeći supplier_pkey na supplier supplier_1
                                               Uslov indeksa: (s_suppkey = partsupp_1.ps_suppkey)
                                   -&gt;  Skeniranje indeksa koristeći nation_pkey na nation nation_1
                                         Uslov indeksa: (n_nationkey = supplier_1.s_nationkey)

Čvor „Merge Join” se nalazi iznad „Gather Merge”. Dakle, spajanje ne koristi paralelnu obradu. Međutim, čvor „Parallel Index Scan” i dalje pomaže u segmentu part_pkey.

Spoj po sekcijama

U PostgreSQL 11 spoj po sekcijama je prema defaultu isključen: ima vrlo visok trošak planiranja. Tabele sa sličnim sekcioniranjem mogu se spajati sekcija po sekciju. Tako će Postgres koristiti manje heš-tabele. 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
   ->  Heš Spoj
         Heš Uslov: (t2.b = t1.a)
         ->  Sekvencijalno skeniranje na prt2_p1 t2
               Filter: ((b >= 0) AND (b <= 10000))
         ->  Heš
               ->  Sekvencijalno skeniranje na prt1_p1 t1
                     Filter: (b = 0)
   ->  Heš Spoj
         Heš Uslov: (t2_1.b = t1_1.a)
         ->  Sekvencijalno skeniranje na prt2_p2 t2_1
               Filter: ((b >= 0) AND (b <= 10000))
         ->  Heš
               ->  Sekvencijalno skeniranje na 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 Append
         ->  Paralelni Heš Spoj
               Heš Uslov: (t2_1.b = t1_1.a)
               ->  Paralelno Sekvencijalno skeniranje na prt2_p2 t2_1
                     Filter: ((b >= 0) AND (b <= 10000))
               ->  Paralelni Heš
                     ->  Paralelno Sekvencijalno skeniranje na prt1_p2 t1_1
                           Filter: (b = 0)
         ->  Paralelni Heš Spoj
               Heš Uslov: (t2.b = t1.a)
               ->  Paralelno Sekvencijalno skeniranje na prt2_p1 t2
                     Filter: ((b >= 0) AND (b <= 10000))
               ->  Paralelni Heš
                     ->  Paralelno Sekvencijalno skeniranje na prt1_p1 t1
                           Filter: (b = 0)

Glavna stvar je da spoj po sekcijama može biti paralelan samo ako su te sekcije dovoljno velike.

Paralelno dodavanje — Parallel Append

Parallel Append može se koristiti umesto različitih blokova u različitim radnim procesima. Obično se javlja kod upita UNION ALL. Nedostatak je manji paralelizam, jer svaki radni proces obrađuje samo 1 upit.

Ovde su pokrenuta 2 radna procesa, iako su uključena 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   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)

Najvažnije varijable

  • WORK_MEM ograničava količinu memorije za svaki proces, ne samo za upite: work_mem procese veza = jako puno memorije.
  • max_parallel_workers_per_gather — koliko radnih procesa programas će koristiti za paralelnu obradu iz plana.
  • max_worker_processes — prilagođava ukupni broj radnih procesa prema broju CPU jezgara na serveru.
  • max_parallel_workers — isto, ali za paralelne radne procese.

Zaključci

Počevši od verzije 9.6, paralelna obrada može znatno poboljšati performanse složenih upita koji skeniraju puno redova ili indeksa. U PostgreSQL 10, paralelna obrada se podrazumeva. Ne zaboravite da je isključite na serverima sa velikim OLTP opterećenjem. Sekvencijalni skenovi ili skenovi indeksa troše jako puno resursa. Ako ne izvršavate izveštaj nad celim skupom podataka, upite možete učiniti efikasnijim jednostavno dodavanjem nedostajućih indeksa ili korišćenjem pravog particionisanja.

Linkovi

Izvor: habr.com

Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere 🔥 Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere | ProHoster