Sellest, kuidas ma pidin tegelema PostgreSQL päringu optimeerimisega ja mis sellest kõigest välja tuli.
Miks sa pidid? Jah, sest eelnevad 4 aastat töötas kõik vaikselt, rahulikult, nagu kell tiksus.
epigraafina.
Põhineb tõsistel sündmustel.
Kõik nimed on muudetud, kokkusattumused on juhuslikud.
Teatud tulemuse saavutamisel on alati huvitav meenutada, mis oli alguse tõukejõud, kuidas see kõik alguse sai.
Niisiis, selle tulemusena juhtunut kirjeldatakse lühidalt artiklis "'.
Tõenäoliselt on huvitav taasluua varasemate sündmuste ahel.
Ajalugu säilitas täpse alguskuupäeva — 2018-09-10 18:02:48.
Samuti on loos taotlus, millest see kõik alguse sai:
ProbleemitaotlusSELECT
lk "PARAMETER_ID" kui parameetri_id,
pd "PD_NAME" AS pd_name,
lk "CUSTOMER_PARTNUMBER" AS kliendi_osanumber,
w"LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_väärtus,
w. "LOWER_SPEC_LIMIT" AS alumine_spec_limit,
w. "UPPER_SPEC_LIMIT" AS ülemine_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS kulutatud_nimi,
s."SPENT_DATE" AS kulutatud_kuupäev,
väljavõte(aasta alates "SPENT_DATE") AS aasta,
väljavõte (kuu alates "SPENT_DATE") kuuna,
s"REPORT_NAME" AS aruande_nimi,
lk "STPM_NAME" AS stpm_name,
p"CUSTOMERPARAM_NAME" AS kliendiparameetri_nimi
wdataw'st,
kulutanud s,
pmtrp,
kulutatud_pdsp,
pd pd
WHERE s."SPENT_ID" = w."SPENT_ID"
JA p."PARAMETER_ID" = w."PARAMETER_ID"
JA s."SPENT_ID" = sp."SPENT_ID"
JA pd. "PD_ID" = sp. "PD_ID"
JA s."SPENT_DATE" >= '2018-07-01' JA s."SPENT_DATE" <= '2018-09-30'
ja s."SPENT_DATE" = (VALI MAX(s2."SPENT_DATE")
ALAST kulutatud s2,
wdata w2
WHERE s2."SPENT_ID" = w2."SPENT_ID"
JA w2."LRM" = w"LRM");
Probleemi kirjeldus, ennustatavalt standardne - "Kõik on halvasti. Ütle mulle, milles probleem on."
Kohe meenus nali 3 ja poole tolliste sõitude aegadest:
Lamer tuleb häkkeri juurde.
- Minu jaoks ei tööta miski, öelge, kus probleem on.
- DNA-s...
Kuid loomulikult ei saa niimoodi esinemisjuhtumeid lahendada. “Meist ei pruugita aru saada" (koos). Tuleb välja mõelda.
No kaevame. Võib-olla koguneb see selle tulemusena.
investeerimine algas
Niisiis, mida saab kohe palja silmaga näha, ilma isegi EXPLAIN abi kasutamata.
1) JOIN-e ei kasutata. See on halb, eriti kui ühenduste arv on rohkem kui üks.
2) Aga mis veelgi hullem – korrelatsioonis alampäring, pealegi koondamisega. See on väga halb.
See on muidugi halb. Kuid see on ainult ühelt poolt. Teisest küljest on see väga hea, sest probleemil on selgelt lahendus ja taotlust saab parandada.
Ära mine ennustaja juurde (C).
Päringuplaan pole nii keeruline, kuid üsna soovituslik:
Täitmisplaan
Kõige huvitavam ja kasulikum, nagu tavaliselt, alguses ja lõpus.
Pesastatud silmus (kulu = 935.84..479763226.18 rida = 3322 laius = 135) (tegelik aeg = 31.536..8220420.295 rida = 8111656 silmust = 1)
Planeerimisaeg: 3.807 ms
Täitmisaeg: 8222351.640 ms
Täitmisaeg on üle 2 tunni.
Valed hüpoteesid, mis võtsid aega
Hüpotees 1 – optimeerija eksib, koostab vale plaani.
Täitmisplaani visualiseerimiseks kasutame saiti . Samas ei näidanud sait midagi huvitavat ega kasulikku. Esmapilgul ja teisel pilgul – mitte midagi, mis tegelikult aidata saaks. Välja arvatud juhul, kui – täielik skannimine on minimaalne. Lase käia.
Hüpotees 2 - Lööge autovaakumi küljelt alusele, peate piduritest lahti saama.
Kuid autovaakumdeemonid käituvad hästi, pikalt rippuvaid protsesse pole. Igasugune tõsine koormus - ei. Vaja midagi muud otsida.
Hüpotees 3-Statistika on aegunud, peate kõik ümber arvutama
Jällegi mitte seda. Statistika on ajakohane. Mis, arvestades autovaakumiga seotud probleemide puudumist, pole üllatav.
Alustatakse optimeerimist
Põhitabel 'wdata' pole kindlasti väike, peaaegu 3 miljonit kirjet.
Ja just sellele lauale läheb Full Scan.
Räsitingimus: ((w."SPENT_ID" = s."SPENT_ID") JA ((alamplaan 1) = s."SPENT_DATE"))
-> Seq Scan kohta wdata w (kulu = 0.00..574151.49 rida = 26886249 laius = 46) (tegelik aeg = 0.005..8153.565 rida = 26873950 silmust = 1)
Toimime nagu standard: "teeme indeksi ja kõik lendab".
Tegi indeksi väljale "SPENT_ID"
Tulemusena:
Päringu täitmisplaan indeksi abil
No kas see aitas?
See oli: 8 222 351.640 ms (veidi üle 2 tunni)
Sellest sai: 6 985 431.575 ms (peaaegu 2 tundi)
Üldiselt samad õunad, külgvaade.
Meenutagem klassikat:
“Kas teil on samasugune, aga ilma tiibadeta? Hakkab otsima".
Põhimõtteliselt võiks seda nimetada heaks tulemuseks, noh, mitte heaks, aga vastuvõetavaks. Esitage kliendile vähemalt mahukas aruanne, mis kirjeldab, kui palju on tehtud ja miks tehtud on hea.
Lõplik otsus on aga veel kaugel. Väga kaugel.
Ja nüüd kõige huvitavam - jätkame optimeerimist, lihvime päringut
Esimene samm – kasutage JOIN
Päring ümber kirjutatud, näeb nüüd välja selline (no vähemalt ilusam):
Päring kasutades JOINSELECT
lk "PARAMETER_ID" kui parameetri_id,
pd "PD_NAME" AS pd_name,
lk "CUSTOMER_PARTNUMBER" AS kliendi_osanumber,
w"LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_väärtus,
w. "LOWER_SPEC_LIMIT" AS alumine_spec_limit,
w. "UPPER_SPEC_LIMIT" AS ülemine_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS kulutatud_nimi,
s."SPENT_DATE" AS kulutatud_kuupäev,
väljavõte(aasta alates "SPENT_DATE") AS aasta,
väljavõte (kuu alates "SPENT_DATE") kuuna,
s"REPORT_NAME" AS aruande_nimi,
lk "STPM_NAME" AS stpm_name,
p"CUSTOMERPARAM_NAME" AS kliendiparameetri_nimi
FROM wdata w INNER JOIN kulutati s ON w."SPENT_ID"=s."SPENT_ID"
SISEMINE LIITUMINE pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
SISEMINE LIITUMINE kulutatud_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
KUS
s."SPENT_DATE" >= '2018-07-01' JA s."SPENT_DATE" <= '2018-09-30'AND
s."SPENT_DATE" = (VALI MAX(s2."SPENT_DATE")
FROM wdata w2 INNER JOIN kulutati s2 ON w2."SPENT_ID"=s2."SPENT_ID"
SISEMINE LIITUMINE wdata w
ON w2."LRM" = w."LRM" );
Planeerimisaeg: 2.486 ms
Täitmisaeg: 1223680.326 ms
Nii et siin on esimene tulemus.
See oli: 6 985 431.575 ms (peaaegu 2 tundi).
Sellest sai: 1 223 680.326 ms (veidi üle 20 minuti).
Hea tulemus. Põhimõtteliselt oleks jällegi võimalik sellega peatuda. Kuid nii ebahuvitav, et te ei saa peatuda.
FOR
Teine samm – vabanege korrelatsioonist alampäringust
Taotluse tekst muudetud:
Korreleeruvat alampäringut poleSELECT
lk "PARAMETER_ID" kui parameetri_id,
pd "PD_NAME" AS pd_name,
lk "CUSTOMER_PARTNUMBER" AS kliendi_osanumber,
w"LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_väärtus,
w. "LOWER_SPEC_LIMIT" AS alumine_spec_limit,
w. "UPPER_SPEC_LIMIT" AS ülemine_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS kulutatud_nimi,
s."SPENT_DATE" AS kulutatud_kuupäev,
väljavõte(aasta alates "SPENT_DATE") AS aasta,
väljavõte (kuu alates "SPENT_DATE") kuuna,
s"REPORT_NAME" AS aruande_nimi,
lk "STPM_NAME" AS stpm_name,
p"CUSTOMERPARAM_NAME" AS kliendiparameetri_nimi
FROM wdata w SISEMINE JOIN kulutati s ON s."SPENT_ID" = w."SPENT_ID"
SISEMINE LIITUMINE pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
SISEMINE LIITUMINE kulutatud_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
SISEMINE LIITUMINE (VALI w2."LRM", MAX(s2."SPENT_DATE")
ALAST kulutatud s2 SISEMINE LIITUMINE wdata w2 ON s2."KUJUTATUD_ID" = w2."KÄITATUD_ID"
GROUP W2.LRM
) md kohta w. "LRM" = md. "LRM"
KUS
s."SPENT_DATE" >= '2018-07-01' JA s."SPENT_DATE" <= '2018-09-30';
Planeerimisaeg: 2.291 ms
Täitmisaeg: 165021.870 ms
See oli: 1 223 680.326 ms (veidi üle 20 minuti).
Sellest sai: 165 021.870 ms (veidi üle 2 minuti).
See on juba päris hea.
Kuid nagu inglased ütlevad,Kuid alati on aga". Liiga hea tulemus peaks automaatselt äratama kahtlust. Midagi on siin valesti.
Hüpotees päringu parandamise kohta, et vabaneda korrelatsioonist alampäringust, on õige. Kuid lõpptulemuse õigeks saamiseks vajab see veidi kohandamist.
Selle tulemusena esimene vahetulemus:
Muudetud päring ilma korrelatsiooniga alampäringutaSELECT
lk "PARAMETER_ID" kui parameetri_id,
pd "PD_NAME" AS pd_name,
lk "CUSTOMER_PARTNUMBER" AS kliendi_osanumber,
w"LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_väärtus,
w. "LOWER_SPEC_LIMIT" AS alumine_spec_limit,
w. "UPPER_SPEC_LIMIT" AS ülemine_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS kulutatud_nimi,
s."SPENT_DATE" AS kulutatud_kuupäev,
väljavõte(aasta alates s. "SPENT_DATE") AS aasta,
väljavõte (kuu alates s. "SPENT_DATE") kui kuu,
s"REPORT_NAME" AS aruande_nimi,
lk "STPM_NAME" AS stpm_name,
p"CUSTOMERPARAM_NAME" AS kliendiparameetri_nimi
FROM wdata w SISEMINE JOIN kulutati s ON s."SPENT_ID" = w."SPENT_ID"
SISEMINE LIITUMINE pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
SISEMINE LIITUMINE kulutatud_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
SISEMINE LIITUMINE ( SELECT w2."LRM", MAX(s2."SPENT_DATE") AS "SPENT_DATE"
ALAST kulutatud s2 SISEMINE LIITUMINE wdata w2 ON s2."KUJUTATUD_ID" = w2."KÄITATUD_ID"
GROUP W2.LRM
) md ON md."SPENT_DATE" = s."SPENT_DATE" JA md."LRM" = w"LRM"
KUS
s."SPENT_DATE" >= '2018-07-01' JA s."SPENT_DATE" <= '2018-09-30';
Planeerimisaeg: 3.192 ms
Täitmisaeg: 208014.134 ms
Seega on meil tulemuseks esimene vastuvõetav tulemus, mida me ei häbene kliendile näidata:
Algas: 8 222 351.640 ms (rohkem kui 2 tundi)
Saavutatud: 1 223 680.326, 20 ms (veidi üle XNUMX minuti).
Tulemus (kesktasemel): 208 014.134 ms (veidi üle 3 minuti).
Suurepärane tulemus.
Summaarne
See oleks võinud peatuda.
AGA…
Söömisega tuleb isu. Tee meisterdatakse kõndides. Iga tulemus on vahepealne. Peatus surnuks. Jne.
Jätkame optimeerimisega.
Suurepärane mõte. Eriti kui arvestada, et klient polnud isegi selle vastu. Ja isegi tugevalt - eest.
Seega on aeg andmebaas ümber kujundada. Päringustruktuuri ennast enam optimeerida ei saa (kuigi nagu hiljem selgus, on võimalus, et kõik tõesti lendaks). Kuid nüüd on see andmebaasi disaini optimeerimiseks ja arendamiseks juba väga paljutõotav idee. Ja mis kõige tähtsam, huvitav. Jällegi meenutage noorust. Ma ei saanud ju kohe DBA-ks, kasvasin välja programmeerijatest (basic, assembler, si, si double plused, oracle, plsql). Huvitav teema muidugi eraldi memuaaride jaoks ;-).
Ärgem siiski kaldugem kõrvale.
Niisiis,
Ja võib-olla aitab jaotus meid?
Spoiler – "Jah, see aitas ja jõudluse optimeerimisel, sealhulgas."
Aga see on hoopis teine lugu...
Jätkub…
Allikas: www.habr.com