Prieš kelis mėnesius - viešas į PostgreSQL.
Ją jau naudojote daugiau nei 6000 kartų, bet viena patogi funkcija, kuri galėjo likti nepastebėta struktūriniai įkalčiai, kurie atrodo maždaug taip:
Klausykite jų ir jūsų prašymai „taps sklandūs ir šilkiniai“. 🙂
Bet jei rimtai, daugelis situacijų, dėl kurių prašymas yra lėtas ir reikalaujantis išteklių yra tipiški ir gali būti atpažįstami pagal plano struktūrą ir duomenis.
Tokiu atveju kiekvienam individualiam kūrėjui nereikia savarankiškai ieškoti optimizavimo varianto, pasikliaujant vien savo patirtimi – mes galime pasakyti, kas čia vyksta, kokia gali būti priežastis ir kaip priartėti prie sprendimo. Taip ir padarėme.
Pažvelkime į šiuos atvejus atidžiau – kaip jie apibrėžiami ir kokios rekomendacijos veda prie jų.
Norėdami geriau pasinerti į temą, pirmiausia galite klausytis atitinkamo bloko iš ir tik tada pereikite prie išsamios kiekvieno pavyzdžio analizės:
1: indeksas „nepakankamas rūšiavimas“
Kai atsiranda
Rodyti naujausią sąskaitą faktūrą klientui „LLC Kolokolchik“.
Kaip atpažinti
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Rekomendacijos
Naudojamas indeksas išplėsti rūšiavimo laukais.
Pavyzdys:
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;
Iš karto galite pastebėti, kad iš indekso buvo atimta daugiau nei 100 įrašų, kurie vėliau buvo surūšiuoti, o tada liko vienintelis.
Taisymas:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Net ir tokiame primityviame pavyzdyje - 8.5 karto greičiau ir 33 kartus mažiau skaitymo. Kuo daugiau kiekvienos vertės „faktų“, tuo akivaizdesnis poveikis fk.
Atkreipiu dėmesį, kad toks indeksas veiks kaip „priešdėlio“ indeksas ne prasčiau nei anksčiau kitoms užklausoms su fk, kur rūšiuoti pagal pk nebuvo ir nėra (apie tai galite paskaityti daugiau ). Įskaitant, tai suteiks normalų aiškus išorinio rakto palaikymas šioje srityje.
2: indekso sankirta (BitmapAnd)
Kai atsiranda
Rodyti visas kliento „LLC Kolokolchik“ sutartis, sudarytas „NAO Buttercup“ vardu.
Kaip atpažinti
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanRekomendacijos
kurti sudėtinis indeksas pagal laukus iš abiejų pirminių arba išplėsti vieną iš esamų laukais iš antrojo.
Pavyzdys:
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); -- отбор по конкретной паре
Taisymas:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Atlyginimas čia yra mažesnis, nes „Bitmap Heap Scan“ yra gana efektyvus. Bet, vis dėlto 7 karto greičiau ir 2.5 kartus mažiau skaitymo.
3: sujunkite indeksus (BitmapOr)
Kai atsiranda
Rodyti pirmuosius 20 seniausių „mes“ arba nepriskirtų apdorojimo užklausų, pirmenybę teikdami jums.
Kaip atpažinti
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanRekomendacijos
Naudokite SĄJUNGOS [VISOS] sujungti kiekvieno ARBA sąlygų bloko antrines užklausas.
Pavyzdys:
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;
Taisymas:
(
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, больше и не надо
Pasinaudojome tuo, kad pirmajame bloke iškart buvo gauti visi 20 reikiamų įrašų, todėl antrasis su „brangesniu“ bitmap krūvos nuskaitymu net nebuvo įvykdytas – galiausiai. 22 kartus greičiau, 44 kartus mažiau skaitymo!
Išsamesnė istorija apie šį optimizavimo metodą naudojant konkrečius pavyzdžius galima skaityti straipsniuose и .
Apibendrinta versija užsakytas pasirinkimas pagal kelis raktus (ir ne tik const/NULL pora) yra aptariama straipsnyje .
#4: Mes skaitome daug nereikalingų dalykų
Kai atsiranda
Paprastai jis atsiranda, kai norima „prijungti kitą filtrą“ prie jau esančios užklausos.
„Ir jūs neturite to paties, bet su perlamutro sagomis? " filmas "Deimantinė ranka"
Pavyzdžiui, pakeisdami aukščiau pateiktą užduotį, parodykite pirmąsias 20 seniausių „kritinių“ apdorojimo užklausų, neatsižvelgiant į jų tikslą.
Kaip atpažinti
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Rekomendacijos
Sukurkite [daugiau] specializuotų indeksas su WHERE sąlyga arba įtraukti papildomų laukų į rodyklę.
Jei filtro būklė yra „statinė“ jūsų tikslams – tai yra nereiškia plėtimosi vertybių sąrašas ateityje - geriau naudoti indeksą WHERE. Įvairios loginės / enum būsenos puikiai tinka šiai kategorijai.
Jei filtravimo sąlygos gali įgauti įvairias reikšmes, tada geriau išplėsti rodyklę šiais laukais – kaip ir su „BitmapAnd“ aukščiau.
Pavyzdys:
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;
Taisymas:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Kaip matote, filtravimas visiškai dingo iš plano, o prašymas tapo 5 kartus greičiau.
#5: retas stalas
Kai atsiranda
Įvairūs bandymai sukurti savo užduočių apdorojimo eilę, kai dėl daugybės įrašų atnaujinimų/ištrynimų lentelėje susidaro daug „mirusių“ įrašų.
Kaip atpažinti
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Rekomendacijos
Reguliariai atlikite rankiniu būdu VAKUUMAS [VISAS] arba pakankamai dažnai treniruotis tikslinant jo parametrus, įskaitant .
Daugeliu atvejų tokias problemas sukelia prasta užklausų sudėtis skambinant iš verslo logikos, kaip aptarta .
Bet jūs turite suprasti, kad net VACUUM FULL ne visada gali padėti. Tokiais atvejais verta susipažinti su algoritmu iš straipsnio .
#6: skaitymas iš rodyklės „vidurio“.
Kai atsiranda
Atrodo, kad skaitėme mažai, viskas buvo indeksuota, ir nieko per daug neišfiltravome - bet vis tiek perskaitome žymiai daugiau puslapių, nei norėtume.
Kaip atpažinti
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Rekomendacijos
Atidžiai peržiūrėkite naudojamo indekso struktūrą ir užklausoje nurodytus pagrindinius laukus – greičiausiai indekso dalis nenustatyta. Labiausiai tikėtina, kad turėsite sukurti panašų indeksą, bet be priešdėlio laukų arba .
Pavyzdys:
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;
Viskas atrodo gerai, net ir pagal indeksą, bet tai kažkaip įtartina - iš kiekvieno iš 20 perskaitytų įrašų turėjome atimti 4 puslapius duomenų, 32 KB vienam įrašui - ar ne paryškinta? Ir indekso pavadinimas tbl_fk_org_fk_cli_idx verčiantis susimąstyti.
Taisymas:
CREATE INDEX ON tbl(fk_cli);
Staiga - 10 kartų greičiau ir 4 kartus mažiau skaityti!
Kiti neefektyvaus indeksų naudojimo situacijų pavyzdžiai pateikiami straipsnyje .
#7: CTE × CTE
Kai atsiranda
Pagal pageidavimą pelnė „riebų“ CTE iš skirtingų lentelių, o tada nusprendė tai padaryti tarp jų JOIN.
Atvejis tinkamas versijoms, senesnėms nei v12 arba užklausoms su WITH MATERIALIZED.
Kaip atpažinti
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Rekomendacijos
Atidžiai išanalizuokite prašymą – ir ? Jei taip, tada pritaikykite „žodyną“ hstore/json pagal aprašytą modelį .
# 8: pakeiskite į diską (tempta įrašyta)
Kai atsiranda
Vienkartinis didelio skaičiaus įrašų apdorojimas (rūšiavimas ar unikalizavimas) netelpa į tam skirtą atmintį.
Kaip atpažinti
-> *
&& temp written > 0Rekomendacijos
Jei operacijos naudojamos atminties kiekis labai neviršija nurodytos parametro reikšmės , verta tai pataisyti. Galite iškart konfigūruoti visiems arba galite per SET [LOCAL] konkrečiam prašymui/sandoriui.
Pavyzdys:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Taisymas:
SET work_mem = '128MB'; -- перед выполнением запроса
Dėl akivaizdžių priežasčių, jei naudojama tik atmintis, o ne diskas, užklausa bus vykdoma daug greičiau. Tuo pačiu metu taip pat pašalinama dalis apkrovos iš HDD.
Bet jūs turite suprasti, kad ne visada galėsite skirti daug ir daug atminties - jos tiesiog neužteks visiems.
#9: nesvarbi statistika
Kai atsiranda
Jie iš karto daug supylė į duomenų bazę, bet nespėjo jos išvaryti ANALYZE.
Kaip atpažinti
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Rekomendacijos
Atlikite tai ANALYZE.
Ši situacija išsamiau aprašyta .
#10: „kažkas ne taip“
Kai atsiranda
Buvo laukiama užrakto, kurį nustatė konkuruojanti užklausa, arba nebuvo pakankamai procesoriaus / hipervizoriaus aparatinės įrangos išteklių.
Kaip atpažinti
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Rekomendacijos
Naudokite išorinį stebėjimo sistema serveris, skirtas blokuoti arba nenormaliai sunaudoti išteklius. Mes jau kalbėjome apie savo versiją, kaip organizuoti šį procesą šimtams serverių и .
Šaltinis: www.habr.com