Om hvordan jeg skulle håndtere PostgreSQL-forespørgselsoptimering og hvad der kom ud af alt dette.
Hvorfor var du nødt til det? Ja, for de foregående 4 år fungerede alt stille, roligt, som et ur tikkede.
som epigraf.
Baseret på virkelige begivenheder.
Alle navne er ændret, tilfældigheder er tilfældige.
Når et bestemt resultat er opnået, er det altid interessant at huske, hvad der var drivkraften til begyndelsen, hvordan det hele begyndte.
Så hvad der skete som et resultat er kort beskrevet i artiklen "'.
Det bliver nok interessant at genskabe kæden af tidligere begivenheder.
Historien holdt den nøjagtige startdato — 2018-09-10 18:02:48.
Også i historien er der en anmodning, hvorfra det hele begyndte:
ProblemanmodningSELECT
s. "PARAMETER_ID" som parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" SOM kunde_partnummer,
w."LRM" SOM LRM,
m. "LOTID" SOM lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS lower_spec_limit,
w. "UPPER_SPEC_LIMIT" AS øvre_spec_limit,
p."TYPE_CALCUL" AS type_calculation,
s."SPENT_NAME" AS used_name,
s."SPENT_DATE" AS spent_date,
ekstrakt(år fra "SPENT_DATE") AS år,
udtræk (måned fra "SPENT_DATE") som måned,
s."REPORT_NAME" AS report_name,
s. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS kundeparam_navn
FRA wdataw,
brugt s,
pmtrp,
brugt_pdsp,
pd pd
WHERE s."SPENT_ID" = w."SPENT_ID"
OG p."PARAMETER_ID" = w."PARAMETER_ID"
OG s."SPENT_ID" = sp."SPENT_ID"
OG pd. "PD_ID" = sp. "PD_ID"
OG s."SPENT_DATE" >= '2018-07-01' OG s."SPENT_DATE" <= '2018-09-30'
og s."SPENT_DATE" = (VÆLG MAX(s2."SPENT_DATE")
FRA brugt s2,
wdata w2
WHERE s2."SPENT_ID" = w2."SPENT_ID"
OG w2"LRM" = w"LRM");
Beskrivelse af problemet, forudsigeligt standard - “Alt er dårligt. Fortæl mig, hvad problemet er."
Jeg huskede straks en vittighed fra tiden med 3 og en halv tomme drev:
Lameren kommer til hackeren.
- Intet virker for mig, fortæl mig, hvor problemet er.
- I DNA...
Men det er selvfølgelig ikke måden at løse præstationshændelser på. “Vi bliver måske ikke forstået"(Med). Skal finde ud af det.
Nå, lad os grave. Måske vil det akkumulere som et resultat.
investering startet
Så hvad kan ses umiddelbart med det blotte øje, uden selv at ty til hjælpen fra EXPLAIN.
1) JOINs bruges ikke. Dette er dårligt, især hvis antallet af forbindelser er mere end én.
2) Men hvad der er endnu værre - en korreleret underforespørgsel, desuden med aggregering. Det er meget dårligt.
Det er selvfølgelig dårligt. Men dette er kun på den ene side. På den anden side er dette meget godt, fordi problemet helt klart har en løsning, og anmodningen kan forbedres.
Gå ikke til spåmanden (C).
Forespørgselsplanen er ikke så kompliceret, men ret vejledende:
Udførelsesplan
Den mest interessante og nyttige, som sædvanlig, i begyndelsen og slutningen.
Indlejret sløjfe (pris=935.84..479763226.18 rækker=3322 bredde=135) (faktisk tid=31.536..8220420.295 rækker=8111656 sløjfer=1)
Planlægningstid: 3.807ms
Udførelsestid: 8222351.640ms
Udførelsestiden er mere end 2 timer.
Falske hypoteser, der tog tid
Hypotese 1- Optimizeren er forkert, bygger den forkerte plan.
For at visualisere udførelsesplanen vil vi bruge siden . Siden viste dog ikke noget interessant eller nyttigt. Ved første og andet øjekast - intet der virkelig kunne hjælpe. Medmindre - Fuld scanning er minimal. Fortsæt.
Hypotese 2-Slag på basen fra siden af autovakuum, du skal slippe af med bremserne.
Men autovacuum-dæmonerne opfører sig godt, der er ingen langvarige processer. Enhver alvorlig belastning - nej. Skal lede efter noget andet.
Hypotese 3-Statistik er forældet, du skal genberegne alt flyver
Igen, ikke det. Statistikken er opdateret. Hvilket i betragtning af manglen på problemer med autovakuum ikke er overraskende.
Begynder at optimere
Hovedtabellen 'wdata' er bestemt ikke lille, næsten 3 millioner poster.
Og det er på dette bord, at Full Scan går.
Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") OG ((Underplan 1) = s."SPENT_DATE"))
-> Seq Scan på wdata w (pris=0.00..574151.49 rækker=26886249 bredde=46) (faktisk tid=0.005..8153.565 rækker=26873950 sløjfer=1)
Vi fungerer som standard: "lad os lave et indeks og alt flyver".
Lavede et indeks på feltet "SPENT_ID"
Som resultat:
Forespørgselsudførelsesplan ved hjælp af et indeks
Nå, hjalp det?
Var: 8 222 351.640 ms (lidt over 2 timer)
Blev til: 6 985 431.575 ms (næsten 2 timer)
Generelt de samme æbler, set fra siden.
Lad os huske klassikerne:
“Har du den samme, men uden vinger? vil søge".
I princippet kan dette kaldes et godt resultat, ja, ikke godt, men acceptabelt. Giv i det mindste en stor rapport til kunden, der beskriver, hvor meget der er blevet gjort, og hvorfor det der er gjort er godt.
Den endelige beslutning er dog stadig langt væk. Meget langt.
Og nu er det mest interessante - vi fortsætter med at optimere, vi vil polere forespørgslen
Trin et - brug JOIN
Omskrevet forespørgsel, ser nu sådan ud (godt i det mindste smukkere):
Forespørg ved hjælp af JOINSELECT
s. "PARAMETER_ID" som parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" SOM kunde_partnummer,
w."LRM" SOM LRM,
m. "LOTID" SOM lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS lower_spec_limit,
w. "UPPER_SPEC_LIMIT" AS øvre_spec_limit,
p."TYPE_CALCUL" AS type_calculation,
s."SPENT_NAME" AS used_name,
s."SPENT_DATE" AS spent_date,
ekstrakt(år fra "SPENT_DATE") AS år,
udtræk (måned fra "SPENT_DATE") som måned,
s."REPORT_NAME" AS report_name,
s. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS kundeparam_navn
FRA wdata w INNER JOIN brugt s PÅ w."SPENT_ID"=s."SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
HVOR
s."SPENT_DATE" >= '2018-07-01' OG s."SPENT_DATE" <= '2018-09-30'AND
s."SPENT_DATE" = (VÆLG MAX(s2."SPENT_DATE")
FRA wdata w2 INNER JOIN brugt s2 PÅ w2."SPENT_ID"=s2."SPENT_ID"
INNER JOIN wdata w
ON w2."LRM" = w"LRM" );
Planlægningstid: 2.486ms
Udførelsestid: 1223680.326ms
Så her er det første resultat.
Var: 6 985 431.575 ms (næsten 2 timer).
Blev til: 1 223 680.326 ms (lidt over 20 minutter).
Godt resultat. I princippet ville det igen være muligt at stoppe der. Men så uinteressant, du kan ikke stoppe.
TIL
Trin to - Slip af med den korrelerede underforespørgsel
Ændret anmodningstekst:
Ingen korreleret underforespørgselSELECT
s. "PARAMETER_ID" som parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" SOM kunde_partnummer,
w."LRM" SOM LRM,
m. "LOTID" SOM lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS lower_spec_limit,
w. "UPPER_SPEC_LIMIT" AS øvre_spec_limit,
p."TYPE_CALCUL" AS type_calculation,
s."SPENT_NAME" AS used_name,
s."SPENT_DATE" AS spent_date,
ekstrakt(år fra "SPENT_DATE") AS år,
udtræk (måned fra "SPENT_DATE") som måned,
s."REPORT_NAME" AS report_name,
s. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS kundeparam_navn
FRA wdata w INNER JOIN brugt s PÅ s."SPENT_ID" = w."SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
INNER JOIN (VÆLG w2."LRM", MAX(s2."SPENT_DATE")
FRA brugt s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
GROUP BY w2.LRM
) md på w. "LRM" = md. "LRM"
HVOR
s."SPENT_DATE" >= '2018-07-01' OG s."SPENT_DATE" <= '2018-09-30';
Planlægningstid: 2.291ms
Udførelsestid: 165021.870ms
Var: 1 223 680.326 ms (lidt over 20 minutter).
Blev til: 165 021.870 ms (lidt over 2 minutter).
Dette er allerede ret godt.
Men som englænderne siger,Men der er altid et men". Et for godt resultat burde automatisk vække mistanke. Der er noget galt her.
Hypotesen om at korrigere forespørgslen for at slippe af med den korrelerede underforespørgsel er korrekt. Men det kræver lidt justeringer for at få det endelige resultat rigtigt.
Som et resultat, det første mellemresultat:
Redigeret forespørgsel uden korreleret underforespørgselSELECT
s. "PARAMETER_ID" som parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" SOM kunde_partnummer,
w."LRM" SOM LRM,
m. "LOTID" SOM lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS lower_spec_limit,
w. "UPPER_SPEC_LIMIT" AS øvre_spec_limit,
p."TYPE_CALCUL" AS type_calculation,
s."SPENT_NAME" AS used_name,
s."SPENT_DATE" AS spent_date,
ekstrakt(år fra s. "SPENT_DATE") AS år,
uddrag (måned fra s. "SPENT_DATE") som måned,
s."REPORT_NAME" AS report_name,
s. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS kundeparam_navn
FRA wdata w INNER JOIN brugt s PÅ s."SPENT_ID" = w."SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
INNER JOIN (VÆLG w2."LRM", MAX(s2."SPENT_DATE") SOM "SPENT_DATE"
FRA brugt s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
GROUP BY w2.LRM
) md PÅ md."SPENT_DATE" = s."SPENT_DATE" OG md."LRM" = w."LRM"
HVOR
s."SPENT_DATE" >= '2018-07-01' OG s."SPENT_DATE" <= '2018-09-30';
Planlægningstid: 3.192ms
Udførelsestid: 208014.134ms
Så det, vi har som resultat, er det første acceptable resultat, som vi ikke skammer os over at vise til kunden:
Startede med: 8 222 351.640 ms (mere end 2 timer)
Opnået: 1 ms (lidt over 223 minutter).
Resultat (mellemliggende): 208 014.134 ms (lidt over 3 minutter).
Fremragende resultat.
Total
Dette kunne være stoppet.
MEN…
Appetit følger med at spise. Vejen vil blive mestret ved at gå. Ethvert resultat er mellemliggende. Stoppet død. Etc.
Lad os fortsætte med optimeringen.
Rigtig god idé. Især i betragtning af at kunden ikke engang var imod det. Og endda stærkt - for.
Så det er tid til at redesigne databasen. Selve forespørgselsstrukturen kan ikke længere optimeres (selvom der, som det viste sig senere, er en mulighed for, at alt virkelig flyver). Men nu for at optimere og udvikle designet af databasen, er dette allerede en meget lovende idé. Og vigtigst af alt interessant. Igen, husk ungdommen. Jeg blev jo ikke umiddelbart DBA, jeg voksede ud af programmører (basic, assembler, si, si to gange plused, oracle, plsql). Et interessant emne, selvfølgelig, for separate erindringer ;-).
Lad os dog ikke gå videre.
således
Og måske vil sektionering hjælpe os?
Spoiler - "Ja, det hjalp, og med at optimere ydeevnen, herunder."
Men det er en helt anden historie...
Fortsættes…
Kilde: www.habr.com