CPUs na zamani suna da muryoyi da yawa. Shekaru da yawa, aikace-aikacen suna aika tambayoyin zuwa ma'ajin bayanai a layi daya. Idan tambaya ce ta rahoto akan layuka da yawa a cikin tebur, yana aiki da sauri lokacin amfani da CPUs da yawa, kuma PostgreSQL ya sami damar yin hakan tun sigar 9.6.
An ɗauki shekaru 3 don aiwatar da fasalin tambayar daidai - dole ne mu sake rubuta lambar a matakai daban-daban na aiwatar da tambaya. PostgreSQL 9.6 ya gabatar da abubuwan more rayuwa don ƙara haɓaka lambar. A cikin juzu'ai na gaba, ana aiwatar da wasu nau'ikan tambayoyin a layi daya.
Ƙuntatawa
- Kar a ba da damar aiwatar da layi ɗaya idan duk muryoyin sun riga sun yi aiki, in ba haka ba wasu buƙatun za su ragu.
- Mafi mahimmanci, aiki a layi daya tare da manyan ƙimar WORK_MEM suna amfani da ƙwaƙwalwar ajiya mai yawa - kowane haɗin hash ko nau'in yana ɗaukar ƙwaƙwalwar aiki_mem.
- Ba za a iya ƙara ƙaramar ƙaramar tambayoyin OLTP ta hanyar aiwatar da layi ɗaya ba. Kuma idan tambayar ta dawo jere guda, aiki a layi daya zai rage ta.
- Masu haɓakawa suna son amfani da ma'aunin TPC-H. Wataƙila kuna da irin waɗannan tambayoyin don cikakken aiwatar da daidaitaccen kisa.
- Tambayoyin SELECT kawai ba tare da kulle predicate ana aiwatar da su a layi daya ba.
- Wani lokaci madaidaicin firikwensin ya fi kyau duban tebur na jeri a cikin yanayin layi ɗaya.
- Ba a goyan bayan dakatar da tambayoyin da siginoni.
- Ayyukan taga da saitunan tara ayyukan da aka ba da umarni ba su daidaita ba.
- Ba ku sami komai a cikin aikin I/O ba.
- Babu daidaitattun algorithms rarrabuwa. Amma tambayoyi tare da nau'ikan za a iya aiwatar da su a layi daya a wasu fannoni.
- Sauya CTE (tare da ...) tare da SELECT mai gida don ba da damar sarrafa layi ɗaya.
- Rukunin bayanai na ɓangare na uku ba su goyi bayan aiwatar da layi ɗaya ba (amma suna iya!)
- BA a tallafawa CIKAKKEN JOIN.
- max_rows yana hana aiki da layi daya.
- Idan tambaya tana da aikin da ba a yiwa alama PARALLEL SAFE ba, za ta zama zaren zare guda ɗaya.
- Matsayin keɓewar ma'amalar SERIALIZABLE yana hana sarrafa layi ɗaya.
Gwajin yanayi
Masu haɓakawa na PostgreSQL sun yi ƙoƙarin rage lokacin amsa tambayoyin ma'auni na TPC-H. Zazzage alamar kuma . Wannan rashin amfani da ma'aunin TPC-H ne ba na hukuma ba - ba don kwatancen bayanai ko hardware ba.
- Zazzage TPC-H_Tools_v2.17.3.zip (ko sabon sigar) .
- Sake suna makefile.suite zuwa Makefile kuma canza kamar yadda aka bayyana anan: . Haɗa lambar tare da umarnin yin.
- Ƙirƙirar bayanai:
./dbgen -s 10yana haifar da 23 GB database. Wannan ya isa don ganin bambanci a cikin ayyukan layi daya da tambayoyin da ba a haɗa su ba. - Maida fayiloli
tblвcsv с forиsed. - Rufe ma'ajiyar
pg_tpchkuma kwafi fayilolincsvвpg_tpch/dss/data. - Ƙirƙiri tambayoyi tare da umarni
qgen. - Load da bayanai a cikin database tare da umurnin
./tpch.sh.
Daidaitacce jere na dubawa
Yana iya zama da sauri ba saboda karatun layi ɗaya ba, amma saboda bayanan an bazu cikin yawancin abubuwan CPU. A cikin tsarin aiki na zamani, fayilolin bayanan PostgreSQL ana adana su da kyau. Tare da karanta gaba, yana yiwuwa a sami babban shinge daga ajiya fiye da buƙatun PG daemon. Don haka, aikin tambaya baya iyakance ta faifai I/O. Yana amfani da hawan CPU zuwa:
- karanta layuka ɗaya bayan ɗaya daga shafukan tebur;
- kwatanta kirtani dabi'u da yanayi
WHERE.
Bari mu gudanar da tambaya mai sauƙi 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 msBinciken da aka jera yana samar da layuka da yawa ba tare da tarawa ba, don haka ana aiwatar da tambayar ta cibiyar CPU guda ɗaya.
Idan kun ƙara SUM(), Kuna iya ganin cewa hanyoyin aiki guda biyu zasu taimaka saurin tambayar:
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 msDaidaitawar tarawa
Parallel Seq Scan node yana samar da layuka don tara juzu'i. The "m sintreate" node datsa wadannan layin amfani SUM(). A ƙarshe, ƙididdigar SUM daga kowane tsari na ma'aikaci ana tattara shi ta kullin "Tara".
Ana ƙididdige sakamakon ƙarshe ta kumburin "Ƙara Ƙarshe". Idan kuna da ayyukan tara naku, kar ku manta da sanya su a matsayin "lafiya mai layi daya".
Yawan matakai na ma'aikata
Ana iya ƙara yawan matakan ma'aikata ba tare da sake kunna sabar ba:
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 msMe ke faruwa a nan? Akwai ƙarin matakan aiki sau 2, kuma buƙatar ta zama sau 1,6599 kawai cikin sauri. Lissafi suna da ban sha'awa. Muna da matakan ma'aikata 2 da jagora 1. Bayan canjin ya zama 4+1.
Matsakaicin saurin mu daga aiki a layi daya: 5/3 = 1,66(6) sau.
Yaya ta yi aiki?
A tafiyar matakai
Neman aiwatarwa koyaushe yana farawa tare da jagorar tsari. Jagora yana yin duk abin da ba daidai ba da kuma wasu daidaitawa. Sauran hanyoyin da ke yin buƙatun iri ɗaya ana kiran su matakan ma'aikata. Daidaita aiki yana amfani da abubuwan more rayuwa (daga sigar 9.4). Tun da sauran sassan PostgreSQL suna amfani da matakai maimakon zaren, tambaya tare da matakan ma'aikata 3 na iya zama sau 4 cikin sauri fiye da sarrafa al'ada.
Hadin kai
Ayyukan ma'aikata suna sadarwa tare da jagora ta hanyar layin saƙo (dangane da ƙwaƙwalwar ajiyar da aka raba). Kowane tsari yana da layi biyu: don kurakurai da tuples.
Nawa ake buƙata hanyoyin aiki?
An ƙayyade mafi ƙarancin iyaka ta hanyar siga . Mai neman buƙatun sannan ya ɗauki matakan ma'aikata daga tafkin da aka iyakance ta ma'auni . Iyaka na ƙarshe shine , wato, jimillar adadin hanyoyin baya.
Idan ba zai yiwu a ware tsarin ma'aikaci ba, sarrafawa zai zama tsari guda ɗaya.
Mai tsara shirin tambaya na iya rage ayyukan aiki dangane da girman tebur ko fihirisa. Akwai sigogi don wannan и .
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 workerDuk lokacin da tebur ya fi girma sau 3 min_parallel_(index|table)_scan_size, Postgres yana ƙara tsarin ma'aikaci. Adadin ayyukan aiki bai dogara da farashi ba. Dogaro da madauwari yana sa aiwatar da rikitarwa mai wahala. Maimakon haka, mai tsarawa yana amfani da dokoki masu sauƙi.
A aikace, waɗannan dokoki ba koyaushe suna dacewa da samarwa ba, saboda haka zaku iya canza yawan matakan ma'aikata don takamaiman tebur: ALTER TABLE ... SET (parallel_workers = N).
Me yasa ba a amfani da sarrafa layi ɗaya?
Baya ga dogon jerin ƙuntatawa, akwai kuma duban farashi:
- don kauce wa aiki a layi daya na gajerun buƙatun. Wannan siga yana ƙididdige lokaci don shirya ƙwaƙwalwar ajiya, fara tsari, da musayar bayanai na farko.
: ana iya jinkirta sadarwa tsakanin jagora da ma'aikata daidai da adadin tuples daga ayyukan aiki. Wannan siga yana ƙididdige farashin musayar bayanai.
Nsted Loop Joins
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)Tarin yana faruwa a mataki na ƙarshe, don haka Nested Loop Hagu Hagu aiki ne na layi daya. Parallel Index Only Scan an gabatar da shi a cikin sigar 10 kawai. Yana aiki kama da sikirin siriyal na layi ɗaya. Sharadi c_custkey = o_custkey yana karanta oda ɗaya kowane kirtani abokin ciniki. Don haka ba daidai ba ne.
Hash Join
Kowane tsari na ma'aikaci yana ƙirƙira teburin zanta har sai PostgreSQL 11. Kuma idan akwai fiye da hudu daga cikin waɗannan matakai, aikin ba zai inganta ba. A cikin sabon sigar, ana raba teburin zanta. Kowane tsari na ma'aikaci zai iya amfani da WORK_MEM don ƙirƙirar tebur mai 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 msTambaya ta 12 daga TPC-H tana nuna a sarari haɗin hash iri ɗaya. Kowane tsari na ma'aikaci yana ba da gudummawa ga ƙirƙirar tebur gama gari.
Haɗa Shiga
Haɗin haɗawa mara misaltuwa cikin yanayi. Kada ku damu idan wannan shine mataki na ƙarshe na tambayar - har yanzu yana iya aiki a layi daya.
-- 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)Kullin "Haɗa Haɗawa" yana sama da "Tara Haɗa". Don haka haɗawa baya amfani da sarrafa layi ɗaya. Amma kumburin “Parallel Index Scan” har yanzu yana taimakawa da sashin part_pkey.
Haɗin kai ta sassan
A cikin PostgreSQL 11 naƙasasshe ta tsohuwa: yana da tanadi mai tsada sosai. Tebura masu irin wannan rarrabuwa ana iya haɗa su da bangare ta bangare. Ta wannan hanyar Postgres za ta yi amfani da ƙaramin tebur na zanta. Kowane haɗin sassan sassan na iya zama a layi daya.
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)Babban abu shi ne cewa haɗin kai a cikin sassan yana layi daya ne kawai idan waɗannan sassan suna da girma.
Daidaitawa Append
za a iya amfani da a maimakon daban-daban tubalan a daban-daban workflows. Wannan yawanci yana faruwa tare da UNION DUKAN tambayoyi. Rashin lahani ba shi da alaƙa da juna, saboda kowane ma'aikaci yana aiwatar da buƙatar 1 kawai.
Akwai matakan ma'aikata guda 2 da ke gudana anan, kodayake an kunna 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)Mafi mahimmanci masu canji
- WORK_MEM yana iyakance ƙwaƙwalwar ajiya akan kowane tsari, ba kawai tambayoyin ba: work_mem matakai haɗi = yawan ƙwaƙwalwar ajiya.
- - ma'aikata nawa ne tsarin aiwatarwa zai yi amfani da shi don aiki a layi daya daga shirin.
- - yana daidaita jimlar matakan tafiyar da ma'aikata zuwa adadin ma'aunin CPU akan sabar.
- - iri ɗaya, amma don tsarin aiki guda ɗaya.
Sakamakon
Dangane da sigar 9.6, aiki na layi ɗaya na iya haɓaka aikin hadaddun tambayoyin da ke bincika layuka da yawa ko fihirisa. A cikin PostgreSQL 10, ana kunna aiki ta layi ɗaya ta tsohuwa. Ka tuna don kashe shi akan sabobin tare da babban aikin OLTP. Sikanin jeri ko sikanin fihirisa suna cinye albarkatu masu yawa. Idan ba ka gudanar da rahoto kan duk saitin bayanai, za ka iya inganta aikin tambaya ta hanyar ƙara bacewar fihirisa ko amfani da rarrabuwar kawuna.
nassoshi
source: www.habr.com