Oor hoe ek PostgreSQL-navraagoptimalisering moes hanteer en wat uit dit alles gekom het.
Hoekom moes jy? Ja, want die vorige 4 jaar het alles stil, rustig gewerk, soos 'n horlosie tik.
as 'n epigraaf.
Gebaseer op werklike gebeure.
Alle name is verander, toevallighede is lukraak.
Wanneer 'n sekere resultaat behaal word, is dit altyd interessant om te onthou wat die stukrag vir die begin was, hoe dit alles begin het.
Dus, wat as gevolg hiervan gebeur het, word kortliks beskryf in die artikel "".
Dit sal waarskynlik interessant wees om die ketting van vorige gebeure te herskep.
Die geskiedenis het die presiese begindatum gehou - 2018-09-10 18:02:48.
Ook in die verhaal is daar 'n versoek waaruit dit alles begin het:
Probleem versoekKIES
bl. "PARAMETER_ID" as parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS klant_deelnommer,
w."LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS onderste_spesifikasielimiet,
w. "UPPER_SPEC_LIMIT" AS boonste_spesifikasielimiet,
p."TYPE_CALCUL" AS tipe_berekening,
s."SPENT_NAME" AS spandeer_naam,
s."SPENT_DATE" AS spandeer_datum,
uittreksel (jaar vanaf "SPENT_DATE") AS jaar,
uittreksel (maand vanaf "SPENT_DATE") as maand,
s."REPORT_NAME" AS verslagnaam,
bl. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS klantparam_naam
VAN Wdataw,
spandeer s,
pmtrp,
spandeer_pdsp,
pd pd
WAAR s."SPENT_ID" = w."SPENT_ID"
EN p."PARAMETER_ID" = w."PARAMETER_ID"
EN s."SPENT_ID" = sp."SPENT_ID"
EN pd. "PD_ID" = sp. "PD_ID"
EN s."SPENT_DATE" >= '2018-07-01' EN s."SPENT_DATE" <= '2018-09-30'
en s."SPENT_DATE" = (KIES MAX(s2."SPENT_DATE")
VAN bestee s2,
wdata w2
WHERE s2."SPENT_ID" = w2."SPENT_ID"
EN w2."LRM" = w."LRM");
Beskrywing van die probleem, voorspelbaar standaard - “Alles is sleg. Vertel my wat die probleem is.”
Ek het dadelik 'n grappie uit die tye van 3 en 'n half duim dryf onthou:
Die lamer kom na die hacker.
- Niks werk vir my nie, sê vir my waar die probleem is.
- In DNA...
Maar dit is natuurlik nie die manier om prestasie-voorvalle op te los nie. “Ons word dalk nie verstaan nie"(Met). Moet dit uitvind.
Wel, kom ons grawe. Miskien sal dit as gevolg daarvan ophoop.
belegging begin
Dus, wat kan dadelik met die blote oog gesien word, sonder om eers die hulp van VERDUIDELIK te gebruik.
1) JOINs word nie gebruik nie. Dit is sleg, veral as die aantal verbindings meer as een is.
2) Maar wat nog erger is - 'n gekorreleerde subnavraag, bowendien, met samevoeging. Dit is baie erg.
Dit is natuurlik sleg. Maar dit is net aan die een kant. Aan die ander kant is dit baie goed, want die probleem het duidelik 'n oplossing en die versoek kan verbeter word.
Moenie na die waarsêer (C) toe gaan nie.
Die navraagplan is nie so ingewikkeld nie, maar redelik aanduidend:
Uitvoeringsplan
Die interessantste en nuttigste, soos gewoonlik, aan die begin en einde.
Geneste lus (koste=935.84..479763226.18 rye=3322 breedte=135) (werklike tyd=31.536..8220420.295 rye=8111656 lusse=1)
Beplanningstyd: 3.807ms
Uitvoertyd: 8222351.640ms
Die uitvoeringstyd is meer as 2 uur.
Valse hipoteses wat tyd geneem het
Hipotese 1- Die optimaliseerder is verkeerd, bou die verkeerde plan.
Om die uitvoeringsplan te visualiseer, sal ons die webwerf gebruik . Die webwerf het egter niks interessant of nuttig getoon nie. Met die eerste en tweede oogopslag – niks wat regtig kon help nie. Tensy - Volledige skandering is minimaal. Gaan voort.
Hipotese 2 - Impak op die basis vanaf die kant van die outovakuum, jy moet van die remme ontslae raak.
Maar die outovakuum-demone tree goed op, daar is geen langdurige prosesse nie. Enige ernstige las - nee. Moet iets anders soek.
Hipotese 3-Statistiek is verouderd, jy moet alles wat vlieg, herbereken
Weereens, nie dit nie. Die statistieke is op datum. Wat, gegewe die gebrek aan probleme met outovakuum, nie verbasend is nie.
Begin om te optimaliseer
Die hooftabel 'wdata' is beslis nie klein nie, amper 3 miljoen rekords.
En dit is op hierdie tafel wat Full Scan gaan.
Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") EN ((Subplan 1) = s."SPENT_DATE"))
-> Volgende skandering op wdata w (koste=0.00..574151.49 rye=26886249 breedte=46) (werklike tyd=0.005..8153.565 rye=26873950 lusse=1)
Ons tree as standaard op: “kom ons maak 'n indeks en alles vlieg”.
Het 'n indeks op die veld "SPENT_ID" gemaak
As gevolg daarvan:
Doen navraag oor uitvoeringsplan deur 'n indeks te gebruik
Wel, het dit gehelp?
Was: 8 222 351.640 ms (net meer as 2 uur)
Dit het geword: 6 985 431.575 ms (byna 2 uur)
In die algemeen, dieselfde appels, sy-aansig.
Kom ons onthou die klassieke:
“Het jy dieselfde een, maar sonder vlerke? Sal soek".
In beginsel kan dit 'n goeie resultaat genoem word, wel, nie goed nie, maar aanvaarbaar. Verskaf ten minste 'n groot verslag aan die kliënt wat beskryf hoeveel gedoen is en hoekom wat gedoen word goed is.
Die finale besluit is egter nog ver. Baie ver.
En nou is die interessantste ding - ons gaan voort om te optimaliseer, ons sal die navraag poets
Stap een - gebruik JOIN
Herskryfde navraag, lyk nou so (wel ten minste mooier):
Doen navraag deur JOIN te gebruikKIES
bl. "PARAMETER_ID" as parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS klant_deelnommer,
w."LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS onderste_spesifikasielimiet,
w. "UPPER_SPEC_LIMIT" AS boonste_spesifikasielimiet,
p."TYPE_CALCUL" AS tipe_berekening,
s."SPENT_NAME" AS spandeer_naam,
s."SPENT_DATE" AS spandeer_datum,
uittreksel (jaar vanaf "SPENT_DATE") AS jaar,
uittreksel (maand vanaf "SPENT_DATE") as maand,
s."REPORT_NAME" AS verslagnaam,
bl. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS klantparam_naam
FROM wdata w INNER JOIN spandeer s OP w."SPENT_ID"=s."SPENT_ID"
INNER JOIN pmtr p OP p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN spent_pd sp OP s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd OP pd."PD_ID" = sp."PD_ID"
WAAR
s."SPENT_DATE" >= '2018-07-01' EN s."SPENT_DATE" <= '2018-09-30'EN
s."SPENT_DATE" = (KIES MAX(s2."SPENT_DATE")
FROM wdata w2 INNER JOIN spandeer s2 OP w2."SPENT_ID"=s2."SPENT_ID"
INNER JOIN wdata w
OP w2."LRM" = w."LRM" );
Beplanningstyd: 2.486ms
Uitvoertyd: 1223680.326ms
So hier is die eerste resultaat.
Was: 6 985 431.575 ms (byna 2 uur).
Dit het geword: 1 223 680.326 ms (net meer as 20 minute).
Goeie resultaat. In beginsel sou dit weer moontlik wees om daar te stop. Maar so oninteressant, jy kan nie ophou nie.
VIR
Stap Twee - Raak ontslae van die gekorreleerde subnavraag
Veranderde versoekteks:
Geen gekorreleerde subnavraag nieKIES
bl. "PARAMETER_ID" as parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS klant_deelnommer,
w."LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS onderste_spesifikasielimiet,
w. "UPPER_SPEC_LIMIT" AS boonste_spesifikasielimiet,
p."TYPE_CALCUL" AS tipe_berekening,
s."SPENT_NAME" AS spandeer_naam,
s."SPENT_DATE" AS spandeer_datum,
uittreksel (jaar vanaf "SPENT_DATE") AS jaar,
uittreksel (maand vanaf "SPENT_DATE") as maand,
s."REPORT_NAME" AS verslagnaam,
bl. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS klantparam_naam
FROM wdata w INNER JOIN spandeer s OP s."SPENT_ID" = w."SPENT_ID"
INNER JOIN pmtr p OP p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN spent_pd sp OP s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd OP pd."PD_ID" = sp."PD_ID"
INNER JOIN (KIES w2."LRM", MAX(s2."SPENT_DATE")
VANAF spandeer s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
GROEP DEUR w2.LRM
) md op w. "LRM" = md. "LRM"
WAAR
s."SPENT_DATE" >= '2018-07-01' EN s."SPENT_DATE" <= '2018-09-30';
Beplanningstyd: 2.291ms
Uitvoertyd: 165021.870ms
Was: 1 223 680.326 ms (net meer as 20 minute).
Dit het geword: 165 021.870 ms (net meer as 2 minute).
Dit is reeds redelik goed.
Maar soos die Engelse sê,Maar, daar is altyd 'n maar". 'n Te goeie resultaat behoort outomaties agterdog te wek. Iets is fout hier.
Die hipotese om die navraag reg te stel om van die gekorreleerde subnavraag ontslae te raak, is korrek. Maar dit moet 'n bietjie opknapping om die finale resultaat reg te kry.
As gevolg hiervan, die eerste intermediêre resultaat:
Bewerkte navraag sonder gekorreleerde subnavraagKIES
bl. "PARAMETER_ID" as parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS klant_deelnommer,
w."LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS onderste_spesifikasielimiet,
w. "UPPER_SPEC_LIMIT" AS boonste_spesifikasielimiet,
p."TYPE_CALCUL" AS tipe_berekening,
s."SPENT_NAME" AS spandeer_naam,
s."SPENT_DATE" AS spandeer_datum,
uittreksel(jaar vanaf a. "SPENT_DATE") AS jaar,
uittreksel (maand vanaf s. "SPENT_DATE") as maand,
s."REPORT_NAME" AS verslagnaam,
bl. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS klantparam_naam
FROM wdata w INNER JOIN spandeer s OP s."SPENT_ID" = w."SPENT_ID"
INNER JOIN pmtr p OP p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN spent_pd sp OP s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd OP pd."PD_ID" = sp."PD_ID"
INNER JOIN (KIES w2."LRM", MAX(s2."SPENT_DATE") AS "SPENT_DATE"
VANAF spandeer s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
GROEP DEUR w2.LRM
) md OP md."SPENT_DATE" = s."SPENT_DATE" EN md."LRM" = w."LRM"
WAAR
s."SPENT_DATE" >= '2018-07-01' EN s."SPENT_DATE" <= '2018-09-30';
Beplanningstyd: 3.192ms
Uitvoertyd: 208014.134ms
Dus, wat ons as gevolg daarvan het, is die eerste aanvaarbare resultaat, wat ons nie skaam is om aan die kliënt te wys nie:
Het begin met: 8 222 351.640 ms (meer as 2 uur)
Bereik: 1 223 680.326 ms (net meer as 20 minute).
Uitkoms (intermediêr): 208 014.134 ms (net meer as 3 minute).
Uitstekende resultaat.
Totale
Dit kon gestop het.
MAAR…
Eetlus kom saam met eet. Die pad sal bemeester word deur te stap. Enige resultaat is intermediêr. Gestop dood. Ens.
Kom ons gaan voort met die optimalisering.
Goeie idee. Veral as in ag geneem word dat die kliënt nie eers daarteen was nie. En selfs sterk - vir.
Dit is dus tyd om die databasis te herontwerp. Die navraagstruktuur self kan nie meer geoptimaliseer word nie (hoewel, soos later geblyk het, daar 'n opsie is vir alles om regtig te vlieg). Maar om nou die ontwerp van die databasis te optimaliseer en te ontwikkel, is dit reeds 'n baie belowende idee. En die belangrikste interessant. Weereens, onthou die jeug. Ek het immers nie dadelik 'n DBA geword nie, ek het uit programmeerders gegroei (basic, assembler, si, si twee keer plused, oracle, plsql). 'n Interessante onderwerp natuurlik vir aparte memoires ;-).
Laat ons egter nie afwyk nie.
So,
En miskien sal seksie ons help?
Bederf - "Ja, dit het gehelp, en in die optimalisering van prestasie, insluitend."
Maar dis 'n heel ander storie...
Vervolg…
Bron: will.com