Mõned kuud tagasi — avalikust PostgreSQL-i jaoks.
Viimase aja jooksul olete seda kasutanud juba üle 6000 korra, kuid üks mugav funktsioon võib olla jäänud märkamata — need on struktuurilised vihjed, mis näevad välja umbes nii:
Kuulake neid, ja teie päringud „muutuvad sujuvamaks ja siidisemaks“. 🙂
Aga tõsiselt, paljud olukorrad, mis teevad päringu aeglaseks ja „ressursinäljas“ on tüüpilised ja neid saab ära tunda struktuuri ja plaani andmete järgi..
Sel juhul ei pea iga eraldi arendaja otsima optimeerimise võimalust üksi, toetudes vaid oma kogemusele — me saame talle öelda, mis siin toimub, mis võib olla põhjus, ja kuidas lahendusele läheneda. Nii me tegime.
Vaadakem neid olukordi natuke lähemalt — kuidas need määratakse ja milliste soovitusteni need viivad.
Teema paremaks mõistmiseks võiks esmalt kuulata vastavat osa minu ettekandest , ja seejärel liikuda iga näite detailse analüüsi juurde:
#1: индексная «недосортировка»
Kui see tekib
Näita viimast arvet kliendi „OÜ Kolokolchik“ kohta.
Kuidas tuvastada
-> Limit
-> Sort
-> Index [Ainult] skaneeri [Tagurpidi] | Bitmap Heap Scan
Soovitused
Kasutatud indeks laienda sortimise väljadega.
Näide:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "fakti"
, (random() * 1000)::integer fk_cli; -- 1K erinevat välist võtit
CREATE INDEX ON tbl(fk_cli); -- indeks välise võti jaoks
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- valik konkreetse seose järgi
ORDER BY
pk DESC -- tahame ainult ühte "viimast" kirje
LIMIT 1; 
Koheselt saab tähele panna, et indeksi kaudu loeti rohkem kui 100 kirjet, mis kõik järjestati ja alles siis jäi üksainus.
Parandame:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- lisatud sortimisvõti
Isegi sellise primitiivse valiku puhul — on see 8,5 korda kiirem ja 33 korda vähem lugemist. Efekt on seda selgem, mida rohkem on teil „fakte“ iga väärtuse kohta fk.
Tänan, et selline indeks töötab nagu „prefiks“ mitte halvemini kui eelmine ja ka teiste päringute jaoks, kus sortimisi ei olnud fk, kus sortimisi pk ei olnud ja ei ole (rohkem infot selle kohta saab lugeda ). Sealhulgas tagab see ka normaalse toetuse selgele välisele võtmele sellel alal.
#2: пересечение индексов (BitmapAnd)
Kui see tekib
Kuva kõik lepingud kliendi «ООО Колокольчик» nimel, mis on sõlmitud «НАО Лютик» poolt.
Kuidas tuvastada
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanSoovitused
Loo komposiitindeks mõlema allika välja lääne või laiendada ühe olemasoleva välja teisega.
Näide:
LOO TABLE tbl NIMEL
VALI
genereeri_seeria(1, 100000) pk -- 100K "fakti"
, (juhuslik() * 100)::integer fk_org -- 100 erinevat välist võtit
, (juhuslik() * 1000)::integer fk_cli; -- 1K erinevat välist võtit
LOO INDEKS tbl(fk_org); -- indeks välise võtme jaoks
LOO INDEKS tbl(fk_cli); -- indeks välise võtme jaoks
VALI
*
FROM
tbl
KUS
(fk_org, fk_cli) = (1, 999); -- valik konkreetse paari põhjal 
Parandame:
KUSTUTA INDEKS tbl_fk_org_idx;
LOO INDEKS tbl(fk_org, fk_cli);
Siin on kasu väiksem, kuna Bitmap Heap Scan on iseenesest piisavalt efektiivne. Kuid siiski 7 korda kiiremini ja 2,5 korda vähem lugemist.
#3: объединение индексов (BitmapOr)
Kui see tekib
Kuva esimesed 20 kõige vanemat «oma» või määramata taotlust töötlemiseks, kusjuures oma on esikohal.
Kuidas tuvastada
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanSoovitused
Kasutage UNION [ALL] alamküsitluste ühendamiseks igas OR-blokis tingimustes.
Näide:
Loo tabel tbl kui
SELECT
generate_series(1, 100000) pk -- 100K "fakti"
, CASE
WHEN random() < 1::real/16 THEN NULL -- tõenäosusega 1:16 "viigipunkt"
ELSE (random() * 100)::integer -- 100 erinevat välist võtmet
END fk_own;
Loo indeks tbl(fk_own, pk); -- indeks "näiliselt sobiva" sorteerimisega
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- omad
fk_own IS NULL -- ... või "viigipunkt"
ORDER BY
pk
, (fk_own = 1) DESC -- esmalt "omad"
LIMIT 20;
Parandame:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- esmalt "omad" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- siis "viigipunkt" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- aga kokku - 20, rohkem pole vaja 
Me kasutasime seda, et kõik 20 vajalikku kirjet saadi kohe esimeses plokis, seega teist, kus on "kallim" Bitmap Heap Scan, isegi ei täidetud - kokkuvõttes 22 korda kiirem, 44 korda vähem lugemisi!
Detailsemat selgitust selle optimeerimise meetodi kohta konkreetselt näidete põhjal võib lugeda artiklites ja .
Üldine variant mitme võtme järgi järjestatud valik (mitte ainult paari const/NULL põhjal) käsitletakse artiklis .
#4: читаем много лишнего
Kui see tekib
Tavaliselt tekib see soovist "lisada veel üks filter" juba olemasolevale päringule.
"Aga kas teil ei ole midagi, mis on sama, kuid pärlmutter nööpidega?» film "Teemantkäsi"
Näiteks muutes ülaltoodud ülesannet, et näidata esimesi 20 kõige vanemat „kriitilist“ taotlust töötlemiseks, olenemata nende määramisest.
Kuidas tuvastada
-> Seq Scan | Bitmap Heap Scan | Indeks [Ainult] Scan [Tagasi]
&& 5 × ridu < RRbF -- filtreeritud >80% loetust
&& tsüklid × RRbF > 100 -- ja üle 100 kirje kokku
Soovitused
Luua [rohkem] spetsialiseeritud indeks koos WHERE-tingimusega või lisada indeksi täiendavad väljad.
Kui filtreerimise tingimus on „staatiline“ teie ülesannete jaoks — st ei eelda tulevikus väärtuste loendi laiendamist — on parem kasutada WHERE-indeksit. Sellesse kategooriasse sobivad erinevad boolean/enum staatused. Kuid kui filtreerimise tingimus
Kui filtritingimus võib võtta erinevaid väärtusi, on parem laiendada indeksit nende väljadega — nagu BitmapAndi puhul eespool.
Näide:
Loo TABEL tbl NII
VALI
genereeri_seeria(1, 100000) pk -- 100K "fakti"
, JUHUL
KUI juhuslik() < 1::reaal/16 SIIS NULL
MUUL JUHUL (juhuslik() * 100)::täisarv -- 100 erinevat välist võtit
LÕPP fk_own
, (juhuslik() < 1::reaal/50) kriitiline; -- 1:50, et taotlus on "kriitiline"
LOO INDEKS tbl(pk);
LOO INDEKS tbl(fk_own, pk);
VALI
*
KUST
tbl
KUS
kriitiline
KORDA
pk
PIIR 20; 
Parandame:
LOO INDEKS tbl(pk)
KUS kriitiline; -- lisasime "staatilise" filtreerimise tingimuse
Nagu näha, on plaanist täielikult kadunud filtreerimine ja päring on nüüd 5 korda kiirem.
#5: разреженная таблица
Kui see tekib
Mitmed katsed luua oma ülesannete töötlemise järjekord, kui tabelis on suur hulk uuendusi/kustutamisi, toovad kaasa olukorra, kus tekib palju 'surnud' kirjeid.
Kuidas tuvastada
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- loeti iga kirje kohta rohkem kui 1KB
&& shared hit + shared read > 64
Soovitused
Regulaarselt käsitsi teostama VACUUM [FULL] või saavutama piisavalt tiheda käitamise selle parameetrite täpse seadistamise abil, sealhulgas .
Enamikul juhtudel on sellised probleemid põhjustatud halbade päringute koostamisest, kui neid kutsutakse välja äriloogikaga, nagu neid, mida on käsitletud .
Kuid tuleb mõista, et isegi VACUUM FULL ei pruugi alati aidata. Selliste juhtumite jaoks on soovitatav tutvuda artikli algoritmiga .
#6: чтение с «середины» индекса
Kui see tekib
Tundub, et oleme natuke lugenud, ja kõik on indeksilt, ja kedagi üleliigset pole filtreeritud — aga siiski on loetud oluliselt rohkem lehti, kui sooviks.
Kuidas tuvastada
-> Indeks [Ainult] Skaneeri [Tagasi]
&& silmus × (read + RRbF) 64
Soovitused
Uurige hoolikalt kasutatud indeksi struktuuri ja päringus määratud võtmevälju — tõenäoliselt, osa indeksist pole määratud. Tõenäoliselt peate looma sarnase indeksi, kuid ilma prefiksväljadeta või .
Näide:
LOO TABEL tbl KUIDAS
VALI
genereeri_seeria(1, 100000) pk -- 100K "faktid"
, (juhuslik() * 100)::integer fk_org -- 100 erinevat välist võtit
, (juhuslik() * 1000)::integer fk_cli; -- 1K erinevat välist võtit
LOO INDEKS tbl-fk_org, fk_cli; -- kõik peaaegu nagu #2
-- ainult, et eraldi indeks fk_cli osas oleme juba liigseks pidanud ja kustutanud
VALI
*
KUST
tbl
KUS
fk_cli = 999 -- aga fk_org pole määratud, kuigi see on indeksis enne
PIIRANG 20; 
Kaalub kõik hästi, isegi indeksi järgi, kuid kuidagi kahtlane — iga 20 loetud kirje puhul pidime lugema 4 andmelehte, 32KB iga kirje kohta — ei ole see liiga palju? Ja indeksi nimi tbl_fk_org_fk_cli_idx ajendab mõtlema.
Parandame:
LOO INDEKS tbl(fk_cli); 
Ühtäkki — 10 korda kiiremini, ja lugeda 4 korda vähem!
Teisi mitteefektiivsete indeksikasutuse näiteid saab näha artiklis .
#7: CTE × CTE
Kui see tekib
päringus oleme kirjutanud "rasvaseid" CTE-sid erinevatest tabelitest ja otsustasime nende vahel teha JOIN.
Juhtum on relevantne versioonide jaoks alla v12 või päringute puhul, mis sisaldavad WITH MATERIALIZED.
Kuidas tuvastada
-> CTE skannimine
&& tsüklid > 10
&& tsüklid × (read + RRbF) > 10000
-- liiga suur CTE dekartaalnähtus
Soovitused
Analüüsige päringut tähelepanelikult — kas ? Если все-таки да, то rakendada "sõnavaramist" hstore/json-is mudeli kohaselt, nagu on kirjeldatud .
#8: swap на диск (temp written)
Kui see tekib
Ühe korra töötlemine (järjestamine või unikaalne märgistamine) suure hulga kirjeid ei mahutu eraldatud mällu.
Kuidas tuvastada
-> *
&& ajutine kirjutatud > 0Soovitused
Kui päringus kasutatud mälu kogus ei ületa oluliselt määratud parameetrit , siis tuleks seda kohandada. Seda saab teha kohe konfi jaoks kõigile või 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; 
Parandame:
SET work_mem = '128MB'; -- päringu täitmise eel 
Mõistlikel põhjustel, kui kasutatakse ainult mälu, mitte ketast, siis täidetakse ka päring palju kiiremini. Sellega vähendatakse ka HDD-le langevat koormust.
Kuid on oluline mõista, et ei ole võimalik eraldada väga-väga palju mälu — seda ei jätku lihtsalt kõigile.
#9: неактуальная статистика
Kui see tekib
Andmebaasi lisati korraga palju, aga ei jõutud läbi viia. ANALYZE.
Kuidas tuvastada
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& suhe >> 10Soovitused
Viia läbi siiski ANALYZE.
Selles olukorras on rohkem detailset teavet. .
#10: «что-то пошло не так»
Kui see tekib
Tekkinud on ooteaeg, mille põhjustas konkurentsivõimeline päring või ei olnud CPU/hüperviisori jaoks piisavalt riistvaralisi ressursse.
Kuidas tuvastada
-> *
&& (shared hit / 8K) + (shared read / 1K) < aeg / 1000
-- RAM hit = 64MB/s, HDD lugemine = 8MB/s
&& aeg > 100ms -- lugesime vähe, aga liiga kaua
Soovitused
Kasutage välist süsteemi serveri jälgimiseks seoses blokeeringute või ebanormaalse ressursikasutusega. Oleme juba rääkinud meie variandist selle protsessi korraldamiseks sadades serverites. serverite osas blokeeringute või ebanormaalse ressursitarbimise olemasolu. Oleme juba rääkinud meie lähenemisest selle protsessi korraldamiseks sadade serverite jaoks. ja .
Allikas: habr.com
