Современите процесори имаат многу јадра. Со години, апликациите паралелно испраќаат прашања до базите на податоци. Ако се работи за барање за извештај на повеќе редови во табела, работи побрзо кога се користат повеќе процесори, а PostgreSQL може да го стори тоа од верзијата 9.6.
Беа потребни 3 години за да се имплементира функцијата за паралелно барање - моравме да го преработиме кодот во различни фази на извршување на барањето. PostgreSQL 9.6 воведе инфраструктура за понатамошно подобрување на кодот. Во следните верзии, паралелно се извршуваат и други типови на барања.
Ограничувања
- Не овозможувајте паралелно извршување ако сите јадра се веќе зафатени, инаку другите барања ќе се забават.
- Што е најважно, паралелната обработка со високи WORK_MEM вредности користи многу меморија - секое хаш приклучување или сортирање зафаќа меморија на work_mem.
- Прашањата за OLTP со мала латентност не може да се забрзаат со паралелно извршување. И ако барањето врати еден ред, паралелната обработка само ќе го забави.
- Програмерите сакаат да го користат реперот TPC-H. Можеби имате слични прашања за совршено паралелно извршување.
- Само SELECT пребарувања без заклучување предикати се извршуваат паралелно.
- Понекогаш правилното индексирање е подобро од секвенцијалното скенирање на табелата во паралелен режим.
- Паузирањето на барањата и курсорите не се поддржани.
- Функциите на прозорецот и нарачаните збирни функции не се паралелни.
- Вие не добивате ништо во обемот на работа I/O.
- Нема паралелни алгоритми за сортирање. Но, прашањата со сортирање може да се извршуваат паралелно во некои аспекти.
- Заменете го CTE (СО ...) со вгнезден SELECT за да овозможите паралелна обработка.
- Обвивките на податоци од трета страна сè уште не поддржуваат паралелна обработка (но може!)
- ЦЕЛОСНО НАДВОРЕШНО ПРИКЛУЧУВАЊЕ не е поддржано.
- max_rows ја оневозможува паралелната обработка.
- Ако барањето има функција која не е означена PARALLEL SAFE, тоа ќе биде со една нишка.
- Нивото на изолација на трансакција SERIALIZABLE ја оневозможува паралелната обработка.
Тестна средина
Програмерите на PostgreSQL се обидоа да го намалат времето на одговор на барањата за репер TPC-H. Преземете го реперот и . Ова е неофицијална употреба на реперот TPC-H - не за споредба на базата на податоци или хардвер.
- Преземете TPC-H_Tools_v2.17.3.zip (или понова верзија) .
- Преименувајте го makefile.suite во Makefile и променете го како што е опишано овде: . Компајлирајте го кодот со командата make.
- Генерирајте податоци:
./dbgen -s 10создава база на податоци од 23 GB. Ова е доволно за да се види разликата во перформансите на паралелните и непаралелните прашања. - Конвертирај датотеки
tblвcsv с forиsed. - Клонирајте го складиштето
pg_tpchи копирајте ги датотекитеcsvвpg_tpch/dss/data. - Креирајте прашања со команда
qgen. - Вчитај податоци во базата на податоци со командата
./tpch.sh.
Паралелно секвенцијално скенирање
Можеби е побрзо не поради паралелното читање, туку затоа што податоците се шират низ многу јадра на процесорот. Во современите оперативни системи, датотеките со податоци PostgreSQL се добро кеширани. Со читање напред, можно е да добиете поголем блок од складирање отколку што бара демонот на PG. Затоа, перформансите на барањето не се ограничени со влез/излез на дискот. Ги троши циклусите на процесорот за:
- читајте редови еден по еден од страниците на табелата;
- споредете ги вредностите и условите на низата
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Секвенцијалното скенирање произведува премногу редови без агрегација, така што барањето се извршува од едно јадро на процесорот.
Ако додадете 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 од секој работник процес се собира од јазолот „Gather“.
Конечниот резултат се пресметува со јазолот „Финализирај агрегат“. Ако имате свои функции за собирање, не заборавајте да ги означите како „паралелно безбедни“.
Број на работни процеси
Бројот на работни процеси може да се зголеми без рестартирање на серверот:
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 е паралелна операција. Скенирањето само со паралелен индекс беше воведено само во верзијата 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 msБарањето 12 од TPC-H јасно покажува паралелна хаш врска. Секој работник процес придонесува за создавање на заедничка табела за хаш.
Спој се приклучи
Спојувањето со спојување е непаралелно по природа. Не грижете се ако ова е последниот чекор од барањето - сепак може да работи паралелно.
-- 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)Јазолот „Merge Join“ се наоѓа над „Gather Merge“. Значи, спојувањето не користи паралелна обработка. Но, јазолот „Паралелно скенирање индекс“ сè уште помага со сегментот 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 процеси врски = многу меморија.
- — колку работни процеси ќе употреби извршната програма за паралелна обработка од планот.
- — го прилагодува вкупниот број работни процеси на бројот на јадра на процесорот на серверот.
- - исто, но за паралелни работни процеси.
Резултатите од
Од верзијата 9.6, паралелната обработка може во голема мера да ги подобри перформансите на сложените барања што скенираат многу редови или индекси. Во PostgreSQL 10, паралелната обработка е стандардно овозможена. Не заборавајте да го оневозможите на серверите со големо оптоварување на OLTP. Секвенцијалните скенирања или скеновите на индекси трошат многу ресурси. Ако не извршувате извештај за целата база на податоци, можете да ги подобрите перформансите на барањето со едноставно додавање на индекси што недостасуваат или со користење на соодветна партиција.
референци
Извор: www.habr.com