Mitu kuud tagasi - avalik PostgreSQL-ile.
Olete seda juba rohkem kui 6000 korda kasutanud, kuid üks käepärane funktsioon, mis võis tähelepanuta jääda, on struktuursed vihjed, mis näevad välja umbes sellised:
Kuulake neid ja teie taotlused "muutuvad sujuvaks ja siidiseks". 🙂
Aga tõsiselt, paljud olukorrad, mis muudavad taotluse aeglaseks ja ressursinõudlikuks on tüüpilised ning neid saab ära tunda plaani struktuuri ja andmete järgi.
Sel juhul ei pea iga arendaja ise optimeerimisvõimalust otsima, tuginedes ainult oma kogemustele – saame talle öelda, mis siin toimub, mis võib olla põhjuseks ja kuidas lahendusele läheneda. Seda me tegimegi.
Vaatame neid juhtumeid lähemalt – kuidas need on määratletud ja milliste soovitusteni need viivad.
Et teemasse paremini süveneda, võid esmalt kuulata vastavat plokki alates ja alles seejärel liikuge iga näite üksikasjaliku analüüsi juurde:
#1: indeks "alasorteerimine"
Kui tekib
Näita kliendi "LLC Kolokolchik" viimast arvet.
Kuidas tuvastada
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Soovitused
Kasutatud indeks laiendada sortimisväljadega.
Näide:
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;
Kohe on märgata, et indeksist lahutati üle 100 kirje, mis siis kõik sorteeriti ja siis jäi ainuke alles.
Parandamine:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Isegi sellisel primitiivsel proovil - 8.5 korda kiirem ja 33 korda vähem lugemist. Mida rohkem "fakte" teil iga väärtuse kohta on, seda ilmsem on mõju fk.
Märgin, et selline indeks töötab „eesliite” indeksina mitte halvemini kui varem teiste päringute puhul fk, kus sortida pk ei olnud ega ole (selle kohta saate rohkem lugeda ). Sealhulgas tagab see normaalse selgesõnaline võõrvõtme tugi sellel väljal.
#2: indeksi ristumiskoht (BitmapAnd)
Kui tekib
Kuva kõik kliendi “LLC Kolokolchik” lepingud, mis on sõlmitud “NAO Buttercup” nimel.
Kuidas tuvastada
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanSoovitused
looma liitindeks väljade kaupa mõlemast algsest või laiendage ühte olemasolevatest väljadega teisest.
Näide:
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); -- отбор по конкретной паре
Parandamine:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Väljamakse on siin väiksem, kuna Bitmap Heap Scan on iseenesest üsna tõhus. Aga igatahes 7 korda kiirem ja 2.5 korda vähem lugemist.
#3: ühendage indeksid (BitmapOr)
Kui tekib
Kuvage esimesed 20 vanimat „meie” või määramata taotlust töötlemiseks, eelistades teie oma.
Kuidas tuvastada
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanSoovitused
Kasutage LIIT [KÕIK] alampäringute kombineerimiseks iga tingimuste VÕI-ploki jaoks.
Näide:
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;
Parandamine:
(
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, больше и не надо
Kasutasime ära asjaolu, et esimeses plokis saadi kohe kätte kõik 20 nõutavat kirjet, nii et teist, “kallima” bitmapkuhja skaneerimisega, ei tehtudki - lõpuks 22x kiirem, 44x vähem lugemist!
Üksikasjalikum lugu selle optimeerimismeetodi kohta kasutades konkreetseid näiteid saab lugeda artiklitest и .
Üldine versioon tellitud valik mitme võtme alusel (ja mitte ainult paari const/NULL) käsitletakse artiklis .
#4: me loeme palju mittevajalikke asju
Kui tekib
Reeglina tekib see siis, kui soovitakse juba olemasolevale päringule "muu filtri kinnitada".
"Ja teil pole sama, aga pärlmutternööpidega? " film "Teemantkäsi"
Näiteks ülaltoodud ülesande muutmisel kuvage esimesed 20 vanimat "kriitilist" töötlemise taotlust, olenemata nende eesmärgist.
Kuidas tuvastada
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Soovitused
Loo [veel] spetsialiseerunud indeks tingimusega WHERE või lisage indeksisse täiendavaid välju.
Kui filtri seisund on teie eesmärkide jaoks "staatiline" - see tähendab ei tähenda laienemist väärtuste loend tulevikus - parem on kasutada indeksit WHERE. Sellesse kategooriasse sobivad hästi erinevad tõeväärtuse/enum olekud.
Kui filtreerimise tingimus võivad omandada erinevaid tähendusi, siis on parem indeks nende väljadega laiendada – nagu ülaltoodud BitmapAnd puhul.
Näide:
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;
Parandamine:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Nagu näete, on filtreerimine plaanist täielikult kadunud ja taotlus on muutunud 5 korda kiirem.
#5: hõre laud
Kui tekib
Erinevad katsed luua oma tööülesannete töötlemise järjekord, kui suur hulk kirjete värskendusi/kustutusi tabelis viivad olukorrani, kus on suur hulk “surnud” kirjeid.
Kuidas tuvastada
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Soovitused
Tehke regulaarselt käsitsi VAKUUM [TÄIS] või saavutage piisavalt sagedane treening peenhäälestades selle parameetreid, sh .
Enamikul juhtudel on sellised probleemid põhjustatud kehvast päringukoostisest, kui helistate äriloogikast, nagu kirjeldatud artiklis .
Kuid peate mõistma, et isegi VACUUM FULL ei pruugi alati aidata. Sellistel juhtudel tasub artiklist pärit algoritmiga tutvuda .
#6: Lugemine indeksi "keskosast".
Kui tekib
Tundub, et lugesime vähe ja kõik oli indekseeritud ning me ei filtreerinud kedagi liigselt välja - kuid siiski lugesime oluliselt rohkem lehti, kui tahaksime.
Kuidas tuvastada
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Soovitused
Vaadake hoolikalt kasutatud indeksi struktuuri ja päringus määratud võtmevälju – kõige tõenäolisemalt osa indeksist pole määratud. Tõenäoliselt peate looma sarnase indeksi, kuid ilma eesliiteväljadeta või .
Näide:
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;
Kõik tundub olevat korras, isegi indeksi järgi, kuid see on kuidagi kahtlane – iga 20 loetud kirje kohta pidime lahutama 4 lehekülge andmeid, 32 KB kirje kohta – kas pole see julge? Ja indeksi nimi tbl_fk_org_fk_cli_idx mõtlemapanev.
Parandamine:
CREATE INDEX ON tbl(fk_cli);
Järsku - 10 korda kiirem ja 4 korda vähem lugeda!
Artiklis on näha ka teisi näiteid indeksite ebatõhusa kasutamise olukordadest .
#7: CTE × CTE
Kui tekib
Soovi korral saavutas “paksu” CTE erinevatest laudadest ja otsustasin siis seda nende vahel teha JOIN.
Juhtum on asjakohane versioonide puhul, mis on vanemad kui v12, või taotluste puhul WITH MATERIALIZED.
Kuidas tuvastada
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Soovitused
Analüüsige taotlust hoolikalt - ja ? Kui jah, siis rakendage "sõnastikku" hstore/jsonis aastal kirjeldatud mudeli järgi .
#8: vaheta kettale (temp kirjutatud)
Kui tekib
Suure hulga kirjete ühekordne töötlemine (sorteerimine või kordustamine) ei mahu selleks eraldatud mällu.
Kuidas tuvastada
-> *
&& temp written > 0Soovitused
Kui toimingu jaoks kasutatud mälumaht ei ületa oluliselt parameetri määratud väärtust , tasub seda parandada. Saate kohe kõigi jaoks mõeldud konfiguratsioonis või saate läbi SET [LOCAL] konkreetse päringu/tehingu jaoks.
Näide:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Parandamine:
SET work_mem = '128MB'; -- перед выполнением запроса
Arusaadavatel põhjustel, kui kasutatakse ainult mälu, mitte ketast, täidetakse päring palju kiiremini. Samal ajal eemaldatakse ka osa HDD koormusest.
Kuid peate mõistma, et te ei saa alati palju-palju mälu eraldada - sellest lihtsalt ei jätku kõigile.
#9: ebaoluline statistika
Kui tekib
Nad valasid andmebaasi korraga palju, kuid neil polnud aega seda minema ajada ANALYZE.
Kuidas tuvastada
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Soovitused
Viige see läbi ANALYZE.
Seda olukorda kirjeldatakse üksikasjalikumalt artiklis .
#10: "midagi läks valesti"
Kui tekib
Oodati konkureeriva päringu kehtestatud lukku või polnud piisavalt CPU/hüperviisori riistvararessursse.
Kuidas tuvastada
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Soovitused
Kasutage välist seiresüsteem server blokeerimiseks või ebanormaalseks ressursitarbimiseks. Oleme juba rääkinud meie versioonist selle protsessi korraldamiseks sadade serverite jaoks и .
Allikas: www.habr.com