Husker du hvordan det hele begynte. Alt var for første gang og igjen

Om hvordan vi måtte optimalisere PostgreSQL-spørringen og hva som kom ut av det hele.
Hvorfor måtte du det? Ja, for de siste 4 årene fungerte alt stille, rolig, som en klokke som tikker.
Som en epigraf.

Basert på virkelige hendelser.
Alle navn er endret, tilfeldigheter er tilfeldige.

Når du oppnår et bestemt resultat, er det alltid interessant å huske hva som var drivkraften til begynnelsen, hvor det hele begynte.

Så hva som skjedde som et resultat er kort beskrevet i artikkelen "Syntese som en av metodene for å forbedre PostgreSQL-ytelsen'.

Det blir nok interessant å gjenskape kjeden av tidligere hendelser.
Historien lagret den nøyaktige startdatoen - 2018-09-10 18:02:48.
Også i historien er det en forespørsel som det hele begynte fra:
ProblemforespørselVELG
p.“PARAMETER_ID” som parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" AS LRM,
m. "LOTID" AS lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS øvre_spesifikasjonsgrense,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS used_name,
s.“SPENT_DATE” AS used_date,
ekstrakt(år fra "SPENT_DATE") AS år,
trekke ut (måned fra "SPENT_DATE") som måned,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FRA wdata w,
brukte s,
pmtr p,
spend_pd sp,
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” = (VELG MAX(s2.“SPENT_DATE”)
FRA brukt s2,
wdata w2
WHERE s2.“SPENT_ID” = w2.“SPENT_ID”
OG w2.“LRM” = w.“LRM”);

Beskrivelse av problemet er forutsigbart standard - "Alt er dårlig. Fortell meg hva problemet er."
Jeg husket umiddelbart en anekdote fra tiden med 3 og en halv tommers stasjoner:

Lameren kommer til hackeren.
-Ingenting fungerer for meg, fortell meg hvor problemet er.
-I DNA...

Men dette er selvfølgelig ikke måten å løse ytelseshendelser på. "De forstår oss kanskje ikke"(Med). Vi må finne ut av det.
Vel, la oss grave. Kanskje vil noe samle seg som et resultat.

Etterforskning startet

Så, hva kan sees umiddelbart med det blotte øye, uten engang å ty til FORKLAR.
1) JOINs brukes ikke. Dette er dårlig, spesielt hvis antallet tilkoblinger er mer enn én.
2) Men det som er enda verre er korrelerte underspørringer, dessuten med aggregering. Dette er veldig dårlig.
Dette er selvfølgelig dårlig. Men dette er bare på den ene siden. På den annen side er dette veldig bra, fordi problemet helt klart har en løsning og en forespørsel som kan forbedres.
Ikke gå til en spådame (C).
Spørreplanen er ikke så komplisert, men den er ganske veiledende:
Gjennomføringsplan

Det mest interessante og nyttige, som vanlig, er i begynnelsen og slutten.
Nestet sløyfe (kostnad=935.84..479763226.18 rader=3322 bredde=135) (faktisk tid=31.536..8220420.295 rader=8111656 looper=1)
Planleggingstid: 3.807 ms
Utførelsestid: 8222351.640 ms
Gjennomføringstid er mer enn 2 timer.

Falske hypoteser som tok tid

Hypotese 1 - Optimalisatoren gjør en feil og bygger feil plan.

For å visualisere utførelsesplanen vil vi bruke siden https://explain.depesz.com/. Siden viste imidlertid ikke noe interessant eller nyttig. Ved første og andre øyekast er det ingenting som virkelig kan hjelpe. Er det mulig at Full Scan er minimal. Gå videre.

Hypotese 2-Slag på basen fra autovakuumsiden, du må kvitte deg med bremsene.

Men autovakuum-demonene oppfører seg bra, det er ingen langvarige prosesser. Ingen alvorlig belastning. Vi må se etter noe annet.

Hypotese 3 - Statistikk er utdatert, alt må beregnes på nytt

Igjen, ikke det. Statistikken er oppdatert. Noe som, gitt mangelen på problemer med autovakuum, ikke er overraskende.

La oss begynne å optimalisere

Hovedtabellen 'wdata' er absolutt ikke liten, nesten 3 millioner poster.
Og det er denne tabellen Full Scan følger.

Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") OG ((SubPlan 1) = s."SPENT_DATE"))
-> Seq Scan på wdata w (kostnad=0.00..574151.49 rader=26886249 bredde=46) (faktisk tid=0.005..8153.565 rader=26873950 sløyfer=1)
Vi gjør standardtingen: "kom igjen, la oss lage en indeks og alt vil fly."
Opprettet en indeks i «SPENT_ID»-feltet
Som et resultat:
Spørringsutførelsesplan ved hjelp av indeks

Vel, hjalp det?
Det var: 8 222 351.640 ms (litt mer enn 2 timer)
Det ble: 6 985 431.575 ms (nesten 2 timer)
Generelt de samme eplene, fra siden.
La oss huske klassikerne:
«Har du den samme, men uten vinger? Vil søke".

I prinsippet kan dette kalles et godt resultat, vel, ikke bra, men akseptabelt. Gi i det minste en stor rapport til kunden som beskriver hvor mye som er gjort og hvorfor det som ble gjort var bra.
Men fortsatt er den endelige avgjørelsen langt unna. Veldig langt.

Og nå er det mest interessante - vi fortsetter å optimalisere, vi vil polere forespørselen

Trinn én - Bruk JOIN

Den omskrevne forespørselen ser nå slik ut (vel i det minste vakrere):
Søk med JOINVELG
p.“PARAMETER_ID” som parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" AS LRM,
m. "LOTID" AS lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS øvre_spesifikasjonsgrense,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS used_name,
s.“SPENT_DATE” AS used_date,
ekstrakt(år fra "SPENT_DATE") AS år,
trekke ut (måned fra "SPENT_DATE") som måned,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FRA wdata w INNER JOIN brukt s ON w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spent_pd sp PÅ 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” = (VELG MAX(s2.“SPENT_DATE”)
FRA wdata w2 INNER JOIN brukte s2 PÅ w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
PÅ w2.“LRM” = w.“LRM” );
Planleggingstid: 2.486 ms
Utførelsestid: 1223680.326 ms

Så det første resultatet.
Det var: 6 ms (nesten 985 timer).
Det ble: 1 223 680.326 ms (litt over 20 minutter).
Godt resultat. I prinsippet kunne vi igjen stoppet der. Men det er så uinteressant at du ikke kan stoppe.
TIL

Trinn to - bli kvitt den korrelerte underspørringen

Endret forespørselstekst:
Uten korrelert underspørringVELG
p.“PARAMETER_ID” som parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" AS LRM,
m. "LOTID" AS lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS øvre_spesifikasjonsgrense,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS used_name,
s.“SPENT_DATE” AS used_date,
ekstrakt(år fra "SPENT_DATE") AS år,
trekke ut (måned fra "SPENT_DATE") som måned,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FRA wdata w INNER JOIN brukt s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spent_pd sp PÅ s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (VELG w2.“LRM”, MAX(s2.“SPENT_DATE”)
FRA brukt 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';
Planleggingstid: 2.291 ms
Utførelsestid: 165021.870 ms

Det var: 1 223 680.326 ms (litt over 20 minutter).
Det ble: 165 021.870 ms (litt over 2 minutter).
Dette er allerede ganske bra.
Imidlertid, som britene sier "Men det er alltid et men" Et resultat som er for godt bør automatisk vekke mistanke. Noe er galt her.

Hypotesen om å korrigere spørringen for å bli kvitt den korrelerte underspørringen er riktig. Men du må justere det litt for at det endelige resultatet skal bli riktig.
Som et resultat, det første mellomresultatet:
Redigert spørring uten korrelert underspørringVELG
p.“PARAMETER_ID” som parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" AS LRM,
m. "LOTID" AS lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS øvre_spesifikasjonsgrense,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS used_name,
s.“SPENT_DATE” AS used_date,
ekstrakt(år fra s.“SPENT_DATE”) AS år,
ekstrakt(måned fra s.“SPENT_DATE”) som måned,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FRA wdata w INNER JOIN brukt s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spent_pd sp PÅ s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN ( VELG w2.“LRM”, MAX(s2.“SPENT_DATE”) SOM “SPENT_DATE”
FRA brukt s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GROUP BY w2.“LRM”
) md ON 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';
Planleggingstid: 3.192 ms
Utførelsestid: 208014.134 ms

Så det vi ender opp med er det første akseptable resultatet, som ikke er en skam å vise til kunden:
Startet med: 8 222 351.640 ms (mer enn 2 timer)
Vi klarte å oppnå: 1 ms (litt mer enn 223 minutter).
Resultat (midlertidig): 208 014.134 ms (litt over 3 minutter).

Flott resultat.

Total

Vi kunne ha stoppet der.
MEN…
Matlyst følger med å spise. Den som går vil mestre veien. Ethvert resultat er middels. Stoppet og døde. Etc.
La oss fortsette å optimalisere.
God idé. Spesielt med tanke på at kunden ikke engang brydde seg. Og til og med sterkt for det.

Så det er på tide med en databaseredesign. Selve spørringsstrukturen kan ikke lenger optimaliseres (selv om det, som det viste seg senere, er et alternativ for å sikre at alt faktisk mislykkes). Men å begynne å optimalisere og utvikle databasedesignet er allerede en veldig lovende idé. Og viktigst av alt interessant. Igjen, husk ungdommen din. Tross alt ble jeg ikke umiddelbart DBA, jeg vokste opp som programmerer (BASIC, assembler, C, double-plus C, Oracle, plsql). Et interessant tema, selvfølgelig, for en egen memoarbok ;-).
La oss imidlertid ikke bli distrahert.

således

Eller kanskje partisjonering vil hjelpe oss?
Spoiler - "Ja, det hjalp, inkludert å optimalisere ytelsen."

Men det er en helt annen historie...

Fortsettelse følger…

Kilde: www.habr.com