O PPU fa'aonaponei e tele naua. Mo le tele o tausaga, o talosaga o loʻo tuʻuina atu fesili i faʻamaumauga i faʻamaumauga tutusa. Afai ole lipoti ole fesili ile tele o laina ile laulau, e sili atu le saoasaoa pe a faʻaogaina le tele o PPU, ma PostgreSQL ua mafai ona faia lenei mea talu mai le version 9.6.
E 3 tausaga na fa'atino ai le fa'atusa fa'atatau - e tatau ona toe tusia le fa'ailoga i la'asaga 'ese'ese o le fa'atinoina o fesili. PostgreSQL 9.6 faʻafeiloaʻi atinaʻe e faʻaleleia atili ai le tulafono. I fa'aliliuga mulimuli ane, o isi ituaiga o fesili o lo'o fa'atinoina tutusa.
Tapulaa
Aua ne'i fa'aogaina le fa'atinoga fa'atusa pe afai o lo'o pisi uma cores, a leai o le a fa'agesegese isi talosaga.
O le mea pito sili ona taua, o le fa'agasologa tutusa ma tulaga maualuga WORK_MEM e fa'aogaina ai le tele o manatua - o hash ta'itasi po'o fa'avasega e alu ai le work_mem memory.
E le mafai ona faatelevaveina fesili OLTP maualalo ile fa'atinoina tutusa. Ma afai o le fesili e toe faʻafoʻi le tasi laina, o le faʻagasologa tutusa o le a faʻagesegese ai.
E fiafia le au atinaʻe e faʻaoga le TPC-H benchmark. Masalo e iai ni au fesili fa'apena mo le fa'atinoina tutusa lelei.
Na'o FILIFILI fesili e aunoa ma le lokaina o lo'o fa'atinoina fa'atasi.
O nisi taimi e sili atu le fa'asinomaga sa'o nai lo le su'eina o le laulau fa'asolosolo i le fa'atusa tutusa.
E le lagolagoina le taofi o fesili ma fa'ailo.
O galuega fa'amalama ma fa'atonuga tu'ufa'atasiga e le tutusa.
E te le maua se mea i le galuega I/O.
E leai ni algorithms fa'avasega tutusa. Ae o fesili fa'atasi e mafai ona fa'atino tutusa i nisi itu.
Suia le CTE (FAI ...) i se fa'aputuga FILIFILI e mafai ai ona fa'agasolo tutusa.
E le'o lagolagoina e fa'amaumauga fa'amaumauga a vaega lona tolu le faiga tutusa (ae mafai!)
FULL OUTER JOIN e le lagolagoina.
max_rows e fa'agata le faiga tutusa.
Afai o se fesili e iai se galuega e le o makaina PARALLEL SAFE, o le a tasi le filo.
O le SERIALIZABLE tulaga fa'aesea feuiaiga e fa'agata ai le faiga tutusa.
Si'osi'omaga su'ega
Na taumafai le au atinaʻe PostgreSQL e faʻaitiitia le taimi tali a TPC-H fesili faʻailoga. La'u mai le fa'ailoga ma fetuunai i le PostgreSQL. Ole fa'aoga le aloaia lea ole TPC-H benchmark - e le mo fa'amaumauga po'o meafaigaluega fa'atusatusa.
La'u mai i lalo le TPC-H_Tools_v2.17.3.zip (po'o le lomiga fou) mai le TPC i fafo.
Toe fa'aigoa makefile.suite ile Makefile ma sui e pei ona fa'amatalaina iinei: https://github.com/tvondra/pg_tpch . Fa'aopoopo le fa'ailoga ma le fa'atonuga.
Fausia fa'amaumauga: ./dbgen -s 10 fausia se 23 GB database. Ua lava lea e iloa ai le eseesega i le faatinoga o fesili tutusa ma le le tutusa.
Faaliliu faila tbl в csv с for и sed.
Clone le fale teu oloa pg_tpch ma kopi faila csv в pg_tpch/dss/data.
Fausia fesili ma se fa'atonuga qgen.
Tu'u fa'amaumauga i totonu o fa'amaumauga ma le fa'atonuga ./tpch.sh.
Fa'asologa fa'asolosolo fa'asolosolo
Atonu e sili atu le saoasaoa e le ona o le faitau tutusa, ae ona o faʻamaumauga o loʻo salalau i luga o le tele o pusa CPU. I faiga fa'aonaponei fa'aonaponei, o faila fa'amaumauga a PostgreSQL o lo'o teuina lelei. Faatasi ai ma le faitau i luma, e mafai ona maua se poloka tele mai le teuina nai lo talosaga PG daemon. O le mea lea, e le fa'atapula'aina le fa'atinoga o fesili ile disk I/O. E fa'aaogaina ta'amilosaga CPU i:
faitau laina tasi i le taimi mai itulau laulau;
fa'atusatusa tau ma tulaga o manoa WHERE.
Se'i fai se fesili faigofie 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
O le fa'asologa fa'asolosolo e maua ai le tele o laina e aunoa ma le fa'apotopotoina, o lea e fa'atino ai le su'esu'ega e le CPU e tasi.
Afai e te faaopoopo SUM(), e mafai ona e vaʻaia o le a fesoasoani faʻavavevave galuega e lua e faʻavave ai le fesili:
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
Fa'atasiga fa'atasi
O le Node Parallel Seq Scan e maua ai laina mo le fa'amaopoopo vaega. O le "Partial Aggregate" node e tipiina nei laina e faʻaaoga ai SUM(). I le fa'ai'uga, o le SUM fa'atau mai fa'agasologa o tagata faigaluega ta'itasi e aoina e le node "Fa'apotopoto".
O le fa'ai'uga fa'ai'uga e fa'atatau i le node "Fa'auma Fa'auma". Afai e iai sau galuega fa'aopoopo, 'aua ne'i galo e fa'ailoga i latou o le "saogalemu tutusa".
Numera o faiga faigaluega
Ole numera o faiga faigaluega e mafai ona faʻateleina e aunoa ma le toe amataina o le server:
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
O le a le mea o tupu iinei? E 2 taimi sili atu galuega, ma o le talosaga na na o le 1,6599 taimi vave. E manaia fa'atatau. E 2 a matou faiga faigaluega ma le ta'ita'i. Ina ua uma le suiga na avea ma 1 + 4.
O la matou saoasaoa maualuga mai le faiga tutusa: 5/3 = 1,66(6) taimi.
E faapefea ona galulue?
O faagasologa
Ole fa'atinoina ole talosaga e amata i taimi uma ile fa'agasolo fa'auluuluga. E faia e le ta'ita'i mea uma e le tutusa ma nisi faiga tutusa. O isi fa'agasologa o lo'o fa'atinoina ia lava talosaga e ta'ua o tagata faigaluega. Fa'agasologa tutusa e fa'aogaina ai atina'e fa'agaioiga fa'agaioiga a tagata faigaluega (mai lomiga 9.4). Talu ai ona o isi vaega o le PostgreSQL faʻaogaina faiga nai lo filo, o se fesili ma 3 tagata faigaluega faʻagasologa e mafai ona 4 taimi sili atu nai lo gaioiga masani.
Fegalegaleaiga
O faiga a le aufaigaluega e feso'ota'i ma le ta'ita'i e ala i le laina fe'au (fa'avae i luga o mafaufauga fa'asoa). E ta'i 2 laina ta'itasi: mo mea sese ma tuple.
E fia fa'asologa o galuega e mana'omia?
Ole tapula'a maualalo e fa'amaoti mai ile fa'ailoga max_parallel_workers_per_gather. Ona ave lea e le tagata tamo'e talosaga faiga a tagata faigaluega mai le vaita'ele fa'atapula'a e le parakalafa max_parallel_workers size. O le tapulaa mulimuli o max_worker_processes, o lona uiga, o le aofaʻi atoa o faʻagasologa o talaaga.
Afai e le mafai ona fa'asoa se fa'agasologa o tagata faigaluega, o le fa'agaioiga o le a tasi le faiga.
O taimi uma lava e 3 taimi e sili atu ai le laulau nai lo min_parallel_(index|table)_scan_size, Faʻaopoopo e Postgres se faʻagasologa o tagata faigaluega. Ole numera ole galuega e le fa'avae ile tau. O le fa'alagolago fa'atasi e fa'afaigata ai fa'atinoga. Nai lo lena, e faʻaaogaina e le faʻatulagaina tulafono faigofie.
I le faʻatinoga, o nei tulafono e le o taimi uma e talafeagai mo le gaosiga, o lea e mafai ai ona e suia le numera o tagata faigaluega faʻagasologa mo se laulau faʻapitoa: ALTER TABLE ... SET (parallel_workers = N).
Aisea e le fa'aogaina ai faiga tutusa?
I le faaopoopo atu i le lisi umi o tapulaʻa, e iai foʻi siaki tau:
parallel_setup_cost - ia aloese mai le faagasologa tutusa o talosaga pupuu. O lenei fa'amaufa'ailoga e fa'atatauina le taimi e saunia ai le manatua, amata le fa'agasologa, ma le fa'asoaina o fa'amatalaga muamua.
parallel_tuple_cost: o fesoʻotaʻiga i le va o le taʻitaʻi ma tagata faigaluega e mafai ona faʻatuai i le faʻatusatusa i le numera o tuple mai galuega faʻagasologa. O lenei fa'amaufa'ailoga e fa'atatau le tau o fa'amatalaga fa'amatalaga.
Fa'apipi'i fa'atasi
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)
O le aoina e tupu i le laasaga mulimuli, o lona uiga o le Nested Loop Left Join o se gaioiga tutusa. Fa'asinoga Fa'atusa Na'o le Va'aiga na fa'ailoa mai ile fa'asologa 10. E tutusa lelei ma le su'ega fa'asolosolo fa'atasi. Tulaga c_custkey = o_custkey faitau tasi le poloaiga i le manoa o tagata o tausia. E le tutusa la.
Hash Auai
E faia e tagata faigaluega uma lana lava laulau hash seia oʻo i le PostgreSQL 11. Ma afai e sili atu ma le fa o nei faiga, o le a le faʻaleleia le faʻatinoga. I le lomiga fou, ua fa'asoa le laulau hash. E mafai ona fa'aogaina e tagata faigaluega ta'itasi le WORK_MEM e fai ai se laulau hash.
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
Ole fesili ole 12 mai le TPC-H o lo'o fa'aalia manino ai se feso'ota'iga hash tutusa. O fa'agasologa o tagata faigaluega ta'itasi e saofagā i le fa'atupuina o se laulau hash masani.
Tuufaatasi Auai
O le tu'ufa'atasiga tu'ufa'atasi e le tutusa lona natura. Aua e te popole pe afai o le laasaga mulimuli lea o le fesili - e mafai lava ona alu tutusa.
-- 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)
O le node "Merge Join" o loʻo i luga aʻe o le "Gather Merge". O le tu'ufa'atasia la e le fa'aogaina ai le faiga tutusa. Ae o loo fesoasoani pea le node o le “Parallel Index Scan” i le vaega part_pkey.
Feso'ota'iga i vaega
I le PostgreSQL 11 feso'ota'iga i vaega disabled by default: e taugata tele le fa'atulagaga. E mafai ona tu'ufa'atasi laulau fa'atasi ma vaeluaga. O le auala lea o le a faʻaogaina ai e Postgres laulau laʻititi laʻititi. O so'oga ta'itasi o vaega e mafai ona tutusa.
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)
O le mea autu o le fesoʻotaʻiga i vaega e tutusa pe afai o nei vaega e tele.
Fa'aopoopo Fa'atasi
Fa'aopoopo Fa'atasi e mafai ona fa'aoga nai lo poloka 'ese'ese i fa'agasolo galuega 'ese'ese. E masani ona tupu lenei mea ile UNION ALL fesili. O le fa'aletonu e itiiti le tutusa, aua o tagata faigaluega ta'itasi e na'o le 1 talosaga.
E 2 tagata faigaluega o loʻo faʻagaoioia iinei, e ui o le 4 e mafai.
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)
O fesuiaiga sili ona taua
WORK_MEM fa'atapula'a le manatua ile fa'agasologa, ae le na'o fesili: work_mem faiga feso'ota'iga = tele le manatua.
max_parallel_workers_per_gather — pe fia le aofa'i o tagata faigaluega fa'agaioiga o le a fa'aogaina e le fa'atinoina o le polokalame mo le fa'agasologa tutusa mai le fuafuaga.
max_worker_processes - fetuutuunai le aofaʻi o faʻagasologa o tagata faigaluega i le aofaʻi o pusa CPU i luga o le 'auʻaunaga.
E pei o le version 9.6, fa'agasolo tutusa e mafai ona fa'aleleia atili ai le fa'atinoga o fesili lavelave e su'esu'e ai le tele o laina po'o fa'asino igoa. I le PostgreSQL 10, fa'agasologa tutusa e mafai ona fa'aletonu. Manatua e faʻamalo i luga o sapalai ma le tele o galuega OLTP. O su'esu'ega fa'asolosolo po'o su'esu'ega fa'asino e fa'aaoga ai le tele o punaoa. Afai e te le o faia se lipoti i luga o le dataset atoa, e mafai ona e faʻaleleia le faʻatinoga o fesili i le na o le faʻaopoopoina o faʻailoga o loʻo misi pe faʻaaoga le vaeluaga tatau.