Misoedd yn ol - cyhoeddus i PostgreSQL.
Rydych chi wedi ei ddefnyddio dros 6000 o weithiau ers hynny, ond efallai bod un o'r nodweddion defnyddiol wedi mynd heb ei sylwi yw cliwiau strwythurol, sy'n edrych rhywbeth fel hyn:
Gwrandewch arnynt a bydd eich ceisiadau "yn dod yn sidanaidd llyfn". 🙂
Ond o ddifrif, mae llawer o sefyllfaoedd sy’n gwneud cais yn araf ac yn “glwtyn” o ran adnoddau, yn nodweddiadol a gellir eu hadnabod gan strwythur a data'r cynllun.
Yn yr achos hwn, ni fydd yn rhaid i bob datblygwr unigol chwilio am opsiwn optimeiddio ar ei ben ei hun, gan ddibynnu ar ei brofiad ei hun yn unig - gallwn ddweud wrtho beth sy'n digwydd yma, beth allai fod y rheswm, a sut i ddod o hyd i ateb. Sef a wnaethom.
Gadewch i ni edrych yn agosach ar yr achosion hyn - sut y cânt eu diffinio a pha argymhellion y maent yn arwain atynt.
I gael trochiad gwell yn y pwnc, gallwch chi wrando ar y bloc cyfatebol yn gyntaf , a dim ond wedyn ewch i ddadansoddiad manwl o bob enghraifft:
#1: mynegai "tansortio"
Pan fydd yn codi
Dangoswch yr anfoneb olaf ar gyfer y cleient "LLC Kolokolchik".
Sut i adnabod
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Argymhellion
Mynegai a ddefnyddir ehangu gyda meysydd didoli.
Enghraifft:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- отбор по конкретной связи
ORDER BY
pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;
Gallwch sylwi ar unwaith bod mwy na 100 o gofnodion wedi'u tynnu gan y mynegai, a gafodd eu didoli wedyn, ac yna dim ond un oedd ar ôl.
Rydym yn trwsio:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Hyd yn oed ar sampl mor gyntefig - 8.5x yn gyflymach a 33x yn llai o ddarlleniadau. Bydd yr effaith yn gliriach, y mwyaf o "ffeithiau" sydd gennych ar gyfer pob gwerth. fk.
Nodaf y bydd mynegai o'r fath yn gweithio fel mynegai “rhagddodiad” heb fod yn waeth na'r un blaenorol ar gyfer ymholiadau eraill fk, lle didoli yn ôl pk nid oedd ac nid yw (gallwch ddarllen mwy am hyn ). Yn benodol, bydd yn darparu arferol cymorth allweddol tramor amlwg gan y maes hwn.
#2: croestoriad mynegai (DidmapAnd)
Pan fydd yn codi
Dangoswch yr holl gontractau ar gyfer y cleient "LLC Kolokolchik" i ben ar ran "NJSC Lyutik".
Sut i adnabod
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanArgymhellion
creu mynegai cyfansawdd gan gaeau o'r ddwy ffynhonnell neu ehangu un o'r meysydd presennol o'r ail.
Enghraifft:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре
Rydym yn trwsio:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Yma mae'r cynnydd yn llai, gan fod Bitmap Heap Scan yn eithaf effeithiol ar ei ben ei hun. Ond beth bynnag 7x yn gyflymach a 2.5x yn llai o ddarlleniadau.
#3: Cyfuno Mynegeion (DidmapOr)
Pan fydd yn codi
Dangoswch yr 20 cais hynaf "ei hun" neu heb eu neilltuo i'w prosesu, gyda'r rhai eu hunain yn flaenoriaeth.
Sut i adnabod
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanArgymhellion
Defnyddio UNDEB [PAWB] i gyfuno subqueries ar gyfer pob un o'r cyflwr NEU flociau.
Enghraifft:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- свои
fk_own IS NULL -- ... или "ничьи"
ORDER BY
pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;
Rydym yn trwsio:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- сначала "свои" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- потом "ничьи" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо
Fe wnaethom fanteisio ar y ffaith bod pob un o'r 20 cofnod angenrheidiol wedi'u cael ar unwaith yn y bloc cyntaf, felly ni chafodd yr ail un, gyda'r Sgan Tomen Bitmap mwy “drutach”, ei weithredu hyd yn oed - o ganlyniad. 22x yn gyflymach, 44 gwaith yn llai o ddarlleniadau!
Stori fanylach am y dull optimeiddio hwn ar enghreifftiau diriaethol gellir ei ddarllen mewn erthyglau и .
Fersiwn cyffredinol dewis wedi'i archebu gan sawl allwedd (ac nid dim ond ar gyfer pâr o const / NULL) yn cael ei drafod yn yr erthygl .
#4: Rydym yn darllen gormod
Pan fydd yn codi
Fel rheol, mae'n digwydd pan fyddwch chi eisiau "atodi hidlydd arall" i gais sy'n bodoli eisoes.
“Ac nid oes gennych yr un peth, ond gyda botymau perl? » ffilm "Diamond Hand"
Er enghraifft, gan addasu'r dasg uchod, dangoswch yr 20 cais "hanfodol" hynaf cyntaf i'w prosesu, waeth beth fo'u pwrpas.
Sut i adnabod
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Argymhellion
Creu [mwy] arbenigol mynegai gyda chymal WHERE neu gynnwys meysydd ychwanegol yn y mynegai.
Os yw'r cyflwr hidlo yn "statig" ar gyfer eich tasgau - hynny yw nid yw'n cynnwys ehangu rhestr o werthoedd yn y dyfodol - mae'n well defnyddio mynegai BLE. Mae amrywiol statws boolean/enum yn ffitio'n dda i'r categori hwn.
Os yw'r cyflwr hidlo yn gallu cymryd gwahanol werthoedd, mae'n well ehangu'r mynegai gyda'r meysydd hyn - fel yn y sefyllfa gyda BitmapAnd uchod.
Enghraifft:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20;
Rydym yn trwsio:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Fel y gallwch weld, mae'r hidlo o'r cynllun wedi diflannu'n llwyr, ac mae'r cais wedi dod 5 gwaith yn gyflymach.
#5: bwrdd tenau
Pan fydd yn codi
Ymdrechion amrywiol i wneud eich ciw prosesu tasg eich hun, pan fydd nifer fawr o ddiweddariadau / dileu cofnodion ar y bwrdd yn arwain at sefyllfa o nifer fawr o gofnodion "marw".
Sut i adnabod
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Argymhellion
Cyflawni â llaw yn rheolaidd GWAG [LLAWN] neu gyflawni prosesu digon aml trwy fireinio ei baramedrau, gan gynnwys .
Yn y rhan fwyaf o achosion, mae problemau o'r fath yn cael eu hachosi gan gynllun ymholiad gwael pan gânt eu galw o resymeg busnes, fel y rhai a drafodwyd yn .
Ond mae'n rhaid i ni ddeall na all hyd yn oed WACUUM LLAWN helpu bob amser. Ar gyfer achosion o'r fath, dylech ymgyfarwyddo â'r algorithm o'r erthygl. .
#6: darllen o "ganol" y mynegai
Pan fydd yn codi
Mae'n ymddangos eu bod yn darllen ychydig, a phopeth wedi'i fynegeio, ac ni wnaethant hidlo unrhyw un yn ychwanegol - ond eto, darllenwyd llawer mwy o dudalennau nag yr hoffem.
Sut i adnabod
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Argymhellion
Cymerwch olwg fanwl ar strwythur y mynegai a ddefnyddir a'r meysydd allweddol a nodir yn yr ymholiad - yn fwyaf tebygol, rhan mynegai heb ei osod. Mae'n debyg y bydd angen i chi greu mynegai tebyg, ond heb feysydd rhagddodiad, neu .
Enghraifft:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;
Mae popeth i’w weld yn iawn, hyd yn oed o ran y mynegai, ond rhywsut yn amheus – ar gyfer pob un o’r 20 cofnod a ddarllenwyd, bu’n rhaid tynnu 4 tudalen o ddata, 32KB y cofnod – onid yw’n feiddgar? Ie ac enw mynegai tbl_fk_org_fk_cli_idx yn arwain at feddwl.
Rydym yn trwsio:
CREATE INDEX ON tbl(fk_cli);
Yn sydyn - 10 gwaith yn gyflymach a phedair gwaith yn llai i'w darllen!
Am ragor o enghreifftiau o ddefnydd aneffeithlon o fynegeion, gweler yr erthygl .
#7: CTE × CTE
Pan fydd yn codi
Ar gais sgoriodd "braster" CTE o wahanol fyrddau, ac yna penderfynodd wneud rhyngddynt JOIN.
Mae'r achos yn berthnasol ar gyfer fersiynau isod v12 neu geisiadau gyda WITH MATERIALIZED.
Sut i adnabod
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Argymhellion
Dadansoddwch y cais yn ofalus ? Os oes, yna cymhwyso "geiriadur" yn hstore/json yn ôl y model a ddisgrifir yn .
#8: cyfnewid i ddisg (ysgrifenedig dros dro)
Pan fydd yn codi
Nid yw prosesu un-amser (didoli neu unigrywi) nifer fawr o gofnodion yn ffitio i'r cof a neilltuwyd ar gyfer hyn.
Sut i adnabod
-> *
&& temp written > 0Argymhellion
Os nad yw maint y cof a ddefnyddir gan y llawdriniaeth yn fwy na gwerth gosodedig y paramedr yn fawr , dylid ei gywiro. Gallwch chi ar unwaith yn y config i bawb, neu gallwch chi drwodd SET [LOCAL] ar gyfer cais/trafodiad penodol.
Enghraifft:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Rydym yn trwsio:
SET work_mem = '128MB'; -- перед выполнением запроса
Am resymau amlwg, os mai dim ond cof a ddefnyddir ac nid disg, yna bydd yr ymholiad yn llawer cyflymach. Ar yr un pryd, mae rhan o'r llwyth hefyd yn cael ei dynnu o'r HDD.
Ond mae angen i chi ddeall na fydd dyrannu llawer o gof bob amser yn gweithio chwaith - yn syml, ni fydd yn ddigon i bawb.
#9: Ystadegau amherthnasol
Pan fydd yn codi
Arllwyswyd llawer i'r gwaelod ar unwaith, ond nid oedd ganddynt amser i'w yrru i ffwrdd ANALYZE.
Sut i adnabod
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Argymhellion
Gwario yr un peth ANALYZE.
Disgrifir y sefyllfa hon yn fanylach yn .
#10: "aeth rhywbeth o'i le"
Pan fydd yn codi
Roedd clo yn aros am gais a oedd yn cystadlu, neu nid oedd digon o adnoddau caledwedd CPU/hypervisor.
Sut i adnabod
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Argymhellion
Defnyddiwch allanol system fonitro gweinydd ar gyfer blocio neu ddefnydd annormal o adnoddau. Rydym eisoes wedi siarad am ein fersiwn o drefnu'r broses hon ar gyfer cannoedd o weinyddion. и .
Ffynhonnell: hab.com