Müasir CPU-larda çoxlu nüvələr var. İllərdir ki, tətbiqlər paralel olaraq verilənlər bazasına sorğular göndərir. Əgər bu, cədvəldəki birdən çox cərgəyə qarşı hesabat sorğusudursa, o, birdən çox CPU istifadə etdikdə daha sürətli olur və PostgreSQL 9.6 versiyasından bəri bunu edə bilir.
Paralel sorğu funksiyasının həyata keçirilməsi 3 il çəkdi - sorğunun icrasının müxtəlif mərhələlərində kodu yenidən yazmalı oldum. PostgreSQL 9.6 kodu daha da təkmilləşdirmək üçün infrastruktur təqdim etdi. Sonrakı versiyalarda digər növ sorğular paralel olaraq yerinə yetirilir.
Məhdudiyyətlər
- Bütün nüvələr artıq məşğuldursa, paralel icranı aktivləşdirməyin, əks halda digər sorğular yavaşlayacaq.
- Ən əsası, yüksək WORK_MEM dəyərləri ilə paralel emal çoxlu yaddaş istifadə edir - hər bir hash qoşulma və ya çeşidləmə iş_mem miqdarında yaddaş istehlak edir.
- Aşağı gecikmə OLTP sorğuları paralel icra ilə sürətləndirilə bilməz. Sorğu tək bir sıra qaytarırsa, paralel emal onu yalnız yavaşlatacaq.
- Tərtibatçılar TPC-H etalonundan istifadə etməyi sevirlər. Mükəmməl paralel icra üçün oxşar sorğularınız ola bilər.
- Yalnız predikat kilidi olmayan SELECT sorğuları paralel olaraq yerinə yetirilir.
- Bəzən düzgün indeksləşdirmə paralel olaraq ardıcıl cədvəl taramalarından daha yaxşıdır.
- Sorğunun dayandırılması və kursorlar dəstəklənmir.
- Pəncərə funksiyaları və sifarişli çoxluq toplama funksiyaları paralel deyil.
- Siz I/O iş yükündə heç nə qazanmırsınız.
- Paralel çeşidləmə alqoritmləri yoxdur. Lakin növlü sorğular bəzi aspektlərdə paralel olaraq işləyə bilər.
- Paralel işləməni aktivləşdirmək üçün CTE-ni (WITH ...) daxili SEÇİM ilə əvəz edin.
- Xarici məlumat sarğıları hələ paralel işləməni dəstəkləmir (amma onlar edə bilər!)
- FULL OUTER JOIN dəstəklənmir.
- max_rows paralel emal prosesini söndürür.
- Əgər sorğunun PARALLEL SAFE kimi qeyd olunmayan funksiyası varsa, o, tək yivli olacaq.
- Tranzaksiya təcrid səviyyəsi SERIALIZABLE paralel emal prosesini deaktiv edir.
Test mühiti
PostgreSQL tərtibatçıları TPC-H etalon sorğularının cavab müddətini azaltmağa çalışdılar. Benchmarkı yükləyin və . Bu, TPC-H etalonunun qeyri-rəsmi istifadəsidir - verilənlər bazası və ya aparatı müqayisə etmək üçün deyil.
- TPC-H_Tools_v2.17.3.zip (və ya daha sonra) yükləyin .
- makefile.suite adını Makefile olaraq dəyişdirin və burada təsvir olunduğu kimi dəyişdirin: . make ilə kodu tərtib edin.
- Məlumat yaradın:
./dbgen -s 1023 GB verilənlər bazası yaradır. Bu, paralel və qeyri-paralel sorğular arasındakı performans fərqini görmək üçün kifayətdir. - Faylları çevirmək
tblвcsv с forиsed. - Anbarı klonlayın
pg_tpchvə faylları kopyalayıncsvвpg_tpch/dss/data. - Bir əmrlə sorğu yaradın
qgen. - Komanda ilə məlumatları verilənlər bazasına yükləyin
./tpch.sh.
Paralel Ardıcıl Skan
Paralel oxuma səbəbindən deyil, verilənlər çoxlu CPU nüvələrinə səpələnmiş olduğundan daha sürətli ola bilər. Müasir əməliyyat sistemlərində PostgreSQL məlumat faylları yaxşı yaddaşda saxlanılır. İrəli oxuma ilə yaddaşdan PG demon sorğularından daha böyük blok əldə etmək mümkündür. Buna görə də, sorğunun performansı diskin I/O ilə məhdudlaşmır. CPU dövrələrini aşağıdakılar üçün istehlak edir:
- cədvəlin səhifələrindən sətirləri bir-bir oxuyun;
- sətir dəyərlərini və şərtlərini müqayisə edin
WHERE.
Sadə bir sorğu keçirək 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 msArdıcıl skan aqreqasiya olmadan həddən artıq çox sıra verir, ona görə də sorğu bir CPU nüvəsi tərəfindən yerinə yetirilir.
Əlavə etsəniz SUM(), iki iş axınının sorğunu sürətləndirməyə kömək edəcəyini görə bilərsiniz:
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 msParalel aqreqasiya
"Paralel Seq Scan" qovşağı qismən toplama üçün cərgələr yaradır. "Partial Aggregate" node bu sıraları ilə kəsir SUM(). Sonda, hər bir iş axınından SUM sayğacı Toplayıcı qovşağı tərəfindən toplanır.
Yekun nəticə “Məcmu yekunlaşdır” nodu ilə hesablanır. Öz toplama funksiyalarınız varsa, onları "paralel seyf" kimi qeyd etməyi unutmayın.
İşçi proseslərinin sayı
Serveri yenidən başlatmadan işçi proseslərin sayı artırıla bilər:
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 msBurda nə baş verir? 2 dəfə daha çox işçi prosesləri var və sorğu yalnız 1,6599 dəfə sürətlidir. Hesablamalar maraqlıdır. 2 işçi prosesimiz və 1 liderimiz var idi. Dəyişiklikdən sonra 4+1 oldu.
Paralel emaldan maksimum sürətimiz: 5/3 = 1,66(6) dəfə.
Necə işləyir?
Proseslər
Sorğunun icrası həmişə aparıcı prosesdən başlayır. Lider hər şeyi qeyri-paralel və bəzi paralel emal edir. Eyni sorğuları yerinə yetirən digər proseslər işçi prosesləri adlanır. Paralel emal infrastrukturdan istifadə edir (9.4 versiyasından bəri). PostgreSQL-in digər hissələri mövzulardan çox proseslərdən istifadə etdiyinə görə, 3 işçi prosesi olan sorğu ənənəvi emaldan 4 dəfə daha sürətli ola bilər.
Qarşılıqlı əlaqə
İşçi prosesləri liderlə mesaj növbəsi vasitəsilə əlaqə qurur (ortaq yaddaş əsasında). Hər bir prosesin 2 növbəsi var: səhvlər üçün və dəstlər üçün.
Nə qədər işçi prosesinə ehtiyacınız var?
Minimum limit parametri təyin edir . Sonra sorğu icraçısı parametrlə məhdudlaşdırılan işçi proseslərini hovuzdan götürür . Son məhdudiyyətdir , bu fon proseslərinin ümumi sayıdır.
Bir işçi prosesini ayırmaq mümkün olmadıqda, emal tək proses olacaq.
Sorğu planlayıcısı cədvəlin və ya indeksin ölçüsündən asılı olaraq iş axınlarını azalda bilər. Bunun üçün variantlar var. и .
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 workerHər dəfə masa 3 dəfə böyükdür min_parallel_(index|table)_scan_size, Postgres işçi prosesi əlavə edir. İş axınının sayı xərcə əsaslanmır. Dairəvi asılılıq kompleks tətbiqləri çətinləşdirir. Bunun əvəzinə planlayıcı sadə qaydalardan istifadə edir.
Təcrübədə bu qaydalar həmişə istehsal üçün uyğun deyildir, ona görə də müəyyən bir cədvəl üçün işçi proseslərinin sayını dəyişdirmək mümkündür: TABLO ALTER ... SET (parallel_workers = N).
Paralel emal niyə istifadə edilmir?
Məhdudiyyətlərin uzun siyahısına əlavə olaraq, xərc yoxlamaları da var:
- qısa sorğuların paralel işlənməsinin qarşısını almaq. Bu parametr yaddaşın hazırlanması, prosesin işə salınması və ilkin məlumat mübadiləsi üçün vaxtı təxmin edir.
: liderlə işçilər arasında əlaqə işçi proseslərindən gələn dəstlərin sayına mütənasib olaraq gecikdirilə bilər. Bu parametr məlumat mübadiləsinin dəyərini nəzərə alır.
Nested Loop Joins - 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)Toplama son mərhələdə baş verir, ona görə də İç İçə Döngə Sol Qoşulma paralel əməliyyatdır. Yalnız Paralel İndeks Skanı yalnız 10-cu versiyada təqdim edilmişdir. O, paralel ardıcıl skan kimi işləyir. Vəziyyət c_custkey = o_custkey hər müştəri xətti üçün bir sifariş oxuyur. Yəni paralel deyil.
Hash Join - Hash Join
Hər bir işçi prosesi PostgreSQL 11-dən əvvəl öz hash cədvəlini yaradır. Və bu proseslərin dörddən çoxu varsa, performans yaxşılaşmayacaq. Yeni versiyada hash cədvəli paylaşılır. Hər bir işçi prosesi hash cədvəli yaratmaq üçün WORK_MEM-dən istifadə edə bilər.
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-dən sorğu 12 paralel hash birləşməsini göstərir. Hər bir işçi prosesi paylaşılan hash cədvəlinin yaradılmasında iştirak edir.
Qoşulun
Birləşmə birləşməsi təbiətdə paralel deyil. Bu sorğunun son addımıdırsa, narahat olmayın - o, hələ də paralel olaraq işləyə bilər.
-- 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)"Birləşməyə qoşulma" qovşağı "Birləşməni topla" nın üstündədir. Beləliklə, birləşmə paralel emaldan istifadə etmir. Ancaq "Paralel İndeks Skanı" qovşağı hələ də seqmentə kömək edir part_pkey.
Bölmə bağlantısı
PostgreSQL 11-də default olaraq qeyri-aktivdir: çox bahalı planlaşdırma var. Bənzər bölmələrə malik cədvəllər bölmələrə birləşdirilə bilər. Bu, Postgres-in daha kiçik hash cədvəllərindən istifadə etməsinə səbəb olacaq. Bölmələrin hər bir əlaqəsi paralel ola bilər.
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)Əsas odur ki, bölmələr ilə əlaqə yalnız bu bölmələr kifayət qədər böyük olduqda paraleldir.
Paralel Əlavə
müxtəlif iş proseslərində müxtəlif bloklar yerinə istifadə edilə bilər. Bu adətən UNION ALL sorğuları ilə baş verir. İşin mənfi tərəfi daha az paralellikdir, çünki hər bir işçi prosesi yalnız 1 sorğunu idarə edir.
Burada işləyən 2 işçi prosesi var, baxmayaraq ki, 4-ü aktivdir.
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)Ən Əhəmiyyətli Dəyişənlər
- WORK_MEM hər proses üçün yaddaşın miqdarını məhdudlaşdırır, sadəcə sorğular deyil: work_mem proseslər bağlantılar = çox yaddaş.
- - icraçı proqramın plandan paralel emal üçün neçə işçi prosesindən istifadə edəcəyi.
- - işçi proseslərinin ümumi sayını serverdəki CPU nüvələrinin sayına uyğunlaşdırır.
- - eyni, lakin paralel işçi prosesləri üçün.
Nəticələri
9.6 versiyasından başlayaraq paralel emal çoxlu sətirləri və ya indeksləri skan edən mürəkkəb sorğuların işini xeyli yaxşılaşdıra bilər. PostgreSQL 10-da paralel emal standart olaraq aktivləşdirilir. Ağır OLTP iş yükü olan serverlərdə onu söndürməyi unutmayın. Ardıcıl və ya indeksli taramalar çox resurs tələb edir. Əgər siz bütün verilənlər bazası haqqında hesabat vermirsinizsə, sadəcə çatışmayan indeksləri əlavə etməklə və ya düzgün bölmədən istifadə etməklə sorğuları daha effektiv etmək olar.
References
Mənbə: www.habr.com