Заманбап процессорлордун өзөктөрү көп. Көп жылдар бою тиркемелер параллелдүү маалымат базаларына суроо жөнөтүп келишет. Эгерде бул таблицадагы бир нече саптар боюнча отчет суроосу болсо, ал бир нече CPU колдонгондо тезирээк иштейт жана PostgreSQL муну 9.6 версиясынан бери жасай алган.
Параллелдүү суроо функциясын ишке ашыруу үчүн 3 жыл талап кылынды – суроону аткаруунун ар кандай баскычтарында кодду кайра жазууга туура келди. PostgreSQL 9.6 кодду андан ары жакшыртуу үчүн инфраструктураны киргизди. Кийинки версияларда сурамдардын башка түрлөрү параллелдүү түрдө аткарылат.
чектөөлөр
- Бардык өзөктөр бош эмес болсо, параллелдүү аткарууну иштетпеңиз, антпесе башка сурамдар жайлайт.
- Эң негизгиси, жогорку WORK_MEM маанилери менен параллелдүү иштетүү көп эстутумду колдонот - ар бир хэшти бириктирүү же сорттоо work_mem эстутумун ээлейт.
- Төмөн күтүү OLTP сурамдарын параллелдүү аткаруу менен тездетүүгө болбойт. Эгерде суроо бир катарды кайтарса, параллелдүү иштетүү аны жайлатат.
- Иштеп чыгуучулар TPC-H эталондорун колдонууну жакшы көрүшөт. Мүмкүн, сизде кемчиликсиз параллелдүү аткаруу үчүн ушундай суроолор бардыр.
- Предикат кулпусу жок SELECT сурамдары гана параллелдүү аткарылат.
- Кээде туура индекстөө параллелдүү режимде ырааттуу таблицага караганда жакшыраак.
- Сурамдарды тындыруу жана курсорлор колдоого алынбайт.
- Терезе функциялары жана иреттелген топтолгон агрегаттык функциялар параллелдүү эмес.
- Киргизүү/чыгаруу иш жүгүндө сиз эч нерсеге ээ болбойсуз.
- Параллелдүү сорттоо алгоритмдери жок. Бирок сорттору бар суроолор кээ бир аспектилерде параллелдүү аткарылышы мүмкүн.
- Параллель иштетүүнү иштетүү үчүн CTE (WITH ...) уя салынган SELECT менен алмаштырыңыз.
- Үчүнчү тараптын маалымат пакеттери параллелдүү иштетүүнү азырынча колдобойт (бирок алар мүмкүн!)
- FULL OUTER JOIN колдоого алынбайт.
- max_rows параллелдүү иштетүүнү өчүрөт.
- Эгерде сурамда PARALLEL SAFE деп белгиленбеген функция болсо, ал бир жиптүү болот.
- SERIALIZABLE транзакциянын изоляция деңгээли параллелдүү иштетүүнү өчүрөт.
Сыноо чөйрөсү
PostgreSQL иштеп чыгуучулары TPC-H эталондук суроолоруна жооп берүү убактысын кыскартууга аракет кылышкан. Эталонду жүктөп алыңыз жана . Бул TPC-H эталонунун расмий эмес колдонулушу - маалымат базасын же аппараттык каражаттарды салыштыруу үчүн эмес.
- TPC-H_Tools_v2.17.3.zip (же жаңыраак версия) жүктөп алыңыз .
- makefile.suite атын Makefile кылып өзгөртүп, бул жерде сүрөттөлгөндөй өзгөртүңүз: . Make буйругу менен кодду түзүңүз.
- Дайындарды түзүү:
./dbgen -s 1023 ГБ маалымат базасын түзөт. Бул параллелдүү жана параллель эмес сурамдардын аткарылышынын айырмасын көрүү үчүн жетиштүү. - Файлдарды айландыруу
tblвcsv с forиsed. - Репозиторийди клондоштуруу
pg_tpchжана файлдарды көчүрүңүзcsvвpg_tpch/dss/data. - буйрук менен суроо түзүү
qgen. - буйрук менен маалымат базасына жүктөө
./tpch.sh.
Параллель ырааттуу сканерлөө
Бул параллелдүү окуунун аркасында эмес, маалымат процессордун көптөгөн өзөктөрүндө таралгандыктан ылдамыраак болушу мүмкүн. Заманбап операциялык системаларда PostgreSQL маалымат файлдары жакшы кэштелген. Алдыда окуу менен, сактагычтан PG демон сурамдарына караганда чоңураак блокту алууга болот. Демек, суроо аткаруу диск I/O менен чектелбейт. Ал CPU циклдерин керектейт:
- таблица барактарынан саптарды бирден окуу;
- сап баалуулуктарын жана шарттарын салыштыруу
WHERE.
Келгиле, жөнөкөй суроону иштетели 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Ырааттуу сканерлөө өтө көп саптарды бириктирбестен чыгарат, андыктан сурам бир CPU өзөгү тарабынан аткарылат.
Кошсоңуз SUM(), эки иш процесси суроону тездетүүгө жардам берерин көрө аласыз:
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 msПараллель топтоо
Parallel Seq Scan түйүнү жарым-жартылай топтоо үчүн катарларды чыгарат. "Жарым-жартылай топтоо" түйүнү бул сызыктарды колдонуп кыркышат SUM(). Аягында, ар бир жумушчу процессинен SUM эсептегич "Жыйноо" түйүнү тарабынан чогултулат.
Акыркы жыйынтык "Агрегатты жыйынтыктоо" түйүнү менен эсептелет. Эгерде сизде өзүңүздүн топтоо функцияларыңыз болсо, аларды “параллель сейф” деп белгилөөнү унутпаңыз.
Жумушчу процесстеринин саны
Жумушчу процесстеринин санын серверди өчүрбөй эле көбөйтүүгө болот:
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 msБул жакта эмне болуп жатат? Жумуш процесстери 2 эсе көп, ал эми суроо-талап 1,6599 эсе гана тезирээк болду. Эсептөөлөр кызыктуу. Бизде 2 жумушчу процесси жана 1 жетекчи бар болчу. Өзгөртүүдөн кийин 4+1 болуп калды.
Параллелдүү иштетүүдөн максималдуу ылдамдыгыбыз: 5/3 = 1,66 (6) эсе.
Бул кандай иштейт?
жараяндар
Сурамдын аткарылышы ар дайым алдыңкы процесстен башталат. Лидер баарын параллелдүү эмес жана кээ бир параллелдүү иштетүүнү жасайт. Ошол эле суроо-талаптарды аткарган башка процесстер жумушчу процесстер деп аталат. Параллель иштетүү инфраструктураны колдонот (9.4 версиясынан). PostgreSQLдин башка бөлүктөрүндө жиптерге караганда процесстер колдонулгандыктан, 3 жумушчу процесси бар суроо салттуу иштетүүгө караганда 4 эсе тезирээк болушу мүмкүн.
өз ара аракеттенүү
Жумушчу процесстери лидер менен билдирүү кезеги аркылуу байланышат (жалпы эс тутумдун негизинде). Ар бир процессте 2 кезек бар: каталар жана кортеждер үчүн.
Канча иш процесси керек?
Минималдуу чек параметр менен аныкталат . Сурамдын жөө күлүгү параметр менен чектелген бассейнден жумушчу процесстерин алат . акыркы чектөө болуп саналат , башкача айтканда, фон процесстеринин жалпы саны.
Эгерде жумушчу процессин бөлүштүрүү мүмкүн болбосо, кайра иштетүү бир процесстүү болот.
Сурамдарды пландоочу таблицанын же индекстин өлчөмүнө жараша иш процесстерин кыскарта алат. Бул үчүн параметрлер бар и .
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 workerАр бир жолу үстөл караганда 3 эсе көп min_parallel_(index|table)_scan_size, Postgres жумушчу процессин кошот. Жумуш процесстеринин саны чыгымдарга негизделбейт. Тегерек көз карандылык татаал ишке ашырууну кыйындатат. Анын ордуна, пландоочу жөнөкөй эрежелерди колдонот.
Иш жүзүндө, бул эрежелер өндүрүш үчүн дайыма эле ылайыктуу эмес, ошондуктан белгилүү бир таблица үчүн жумушчу процесстеринин санын өзгөртө аласыз: ALTER TABLE ... SET (parallel_workers = N).
Эмне үчүн параллелдүү иштетүү колдонулбайт?
Чектөөлөрдүн узун тизмесинен тышкары, чыгымдарды текшерүү дагы бар:
- кыска суроо-талаптарды параллелдүү иштетүүдөн качуу. Бул параметр эстутумду даярдоо, процессти баштоо жана баштапкы маалымат алмашуу убактысын эсептейт.
: жетекчи менен жумушчулардын ортосундагы байланышты иш процесстеринен келген кортеждердин санына жараша кечиктирсе болот. Бул параметр маалымат алмашуунун баасын эсептейт.
Уюшкан цикл кошулат
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)Чогултуу акыркы этапта ишке ашат, ошондуктан Nested Loop Left Join параллелдүү операция. Parallel Index Only Scan 10-версиясында гана киргизилген. Ал параллелдүү сериялык сканерлөө сыяктуу иштейт. Шарт c_custkey = o_custkey кардардын сабына бир буйрукту окуйт. Демек, бул параллелдүү эмес.
Hash кошулуу
Ар бир жумушчу процесс PostgreSQL 11ге чейин өзүнүн хэш таблицасын түзөт. Ал эми бул процесстердин төрттөн көпү болсо, аткаруу жакшырбайт. Жаңы версияда хэш таблицасы бөлүшүлгөн. Ар бир жумушчу процесс хэш таблицасын түзүү үчүн WORK_MEM колдоно алат.
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ден 12 суроо параллелдүү хэш байланышын ачык көрсөтөт. Ар бир жумушчу процесси жалпы хэш таблицасын түзүүгө салым кошот.
Кошулуу
Биригүү табияты боюнча параллелдүү эмес. Бул суроонун акыркы кадамы болсо, кабатыр болбоңуз - ал дагы эле параллелдүү иштей алат.
-- 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)"Бириктирүү" түйүнү "Бириктирүүнү чогултуу" түйүнүнүн үстүндө жайгашкан. Ошентип, бириктирүү параллелдүү иштетүүнү колдонбойт. Бирок "Параллель индексти сканерлөө" түйүнү сегментке дагы деле жардам берет part_pkey.
Бөлүмдөр боюнча байланыш
PostgreSQL 11де демейки боюнча өчүрүлгөн: ал абдан кымбат пландаштырууга ээ. Окшош бөлүштүрүлгөн таблицаларды бөлүү боюнча бириктирсе болот. Ошентип, Postgres кичинекей хэш таблицаларын колдонот. Бөлүмдөрдүн ар бир байланышы параллелдүү болушу мүмкүн.
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)Эң негизгиси, бул бөлүмдөр жетиштүү чоң болгондо гана бөлүмдөрдөгү байланыш параллелдүү болот.
Параллель тиркеме
ар кандай иш процесстеринде ар кандай блоктордун ордуна колдонулушу мүмкүн. Бул көбүнчө UNION ALL сурамдары менен болот. Кемчилиги азыраак параллелдүүлүк, анткени ар бир жумушчу процесс 1 гана өтүнүчтү иштетет.
Бул жерде иштеп жаткан 2 жумушчу процесси бар, бирок 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 <= 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)Эң маанилүү өзгөрмөлөр
- WORK_MEM ар бир процесске эстутумду чектейт, жөн гана сурамдарды эмес: work_mem жараяндар байланыштар = көп эстутум.
- — аткарылуучу программа пландан параллелдуу иштеп чыгуу үчүн канча жумушчу процессин колдонот.
- — жумушчу процесстеринин жалпы санын сервердеги CPU өзөктөрүнүн санына тууралайт.
- - бирдей, бирок параллелдүү иш процесстери үчүн.
натыйжалары
9.6 версиясынан баштап, параллелдүү иштетүү көптөгөн саптарды же индекстерди сканерлөөчү татаал сурамдардын иштешин бир топ жакшыртат. PostgreSQL 10до демейки боюнча параллелдүү иштетүү иштетилген. Аны чоң OLTP жүктөмү бар серверлерде өчүрүүнү унутпаңыз. Кезектеги сканерлөө же индекстик сканерлөө көптөгөн ресурстарды талап кылат. Эгер сиз бүтүндөй маалымат топтому боюнча отчетту иштетпесеңиз, анда жетишпеген индекстерди кошуу же туура бөлүштүрүү аркылуу суроонун иштешин жакшыртсаңыз болот.
шилтемелер
Source: www.habr.com