Muistatko kuinka kaikki alkoi. Kaikki oli ensimmäistä kertaa ja uudestaan

Siitä, kuinka minun piti käsitellä PostgreSQL-kyselyn optimointia ja mitä tästä kaikesta tuli.
Miksi sinun oli pakko? Kyllä, koska edelliset 4 vuotta kaikki toimi hiljaa, rauhallisesti, kuin kello tikittää.
epigrafina.

Perustuu tositapahtumiin.
Kaikki nimet on muutettu, sattumat ovat satunnaisia.

Kun tietty tulos on saavutettu, on aina mielenkiintoista muistella, mikä oli alun sysäys, miten kaikki alkoi.

Joten, mitä seurauksena tapahtui, kuvataan lyhyesti artikkelissa "Synteesi yhtenä menetelmistä parantaa PostgreSQL:n suorituskykyä'.

On luultavasti mielenkiintoista luoda uudelleen aikaisempien tapahtumien ketju.
Historia piti tarkan aloituspäivän — 2018-09-10 18:02:48.
Tarinassa on myös pyyntö, josta kaikki alkoi:
OngelmapyyntöVALITSE
p. "PARAMETER_ID" parametrin_tunnuksena,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w"LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS alempi_spec_limit,
w. "UPPER_SPEC_LIMIT" AS ylempi_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS kulutettu_nimi,
s."SPENT_DATE" AS kulutettu_päivä,
ote(vuosi alkaen "SPENT_DATE") AS vuosi,
ote (kuukausi "SPENT_DATE") kuukautena,
s."REPORT_NAME" AS raportin_nimi,
s. "STPM_NAME" AS stpm_name,
p"CUSTOMERPARAM_NAME" AS asiakkaan parametrin_nimi
FROM wdataw,
vietti s,
pmtrp,
käytetty_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" = (VALITSE MAX(s2."SPENT_DATE")
Käytetty s2,
wdata w2
WHERE s2."SPENT_ID" = w2."SPENT_ID"
JA w2."LRM" = w"LRM");

Ongelman kuvaus, ennustettavasti standardi - "Kaikki on huonosti. Kerro minulle, mikä on ongelma."
Tuli heti mieleen vitsi 3 ja puolen tuuman ajoajoilta:

Lamer tulee hakkerin luo.
- Mikään ei toimi minulle, kerro missä ongelma on.
-DNA:ssa...

Mutta tämä ei tietenkään ole tapa ratkaista suorituskykyhäiriöitä. "Meitä ei ehkä ymmärretä" (Kanssa). Pitää selvittää se.
No kaivetaan. Ehkä se kerääntyy sen seurauksena.

investointi alkoi

Joten mitä voidaan nähdä välittömästi paljaalla silmällä, turvautumatta edes EXPLAINin apuun.
1) JOINeja ei käytetä. Tämä on huono asia, varsinkin jos yhteyksiä on enemmän kuin yksi.
2) Mutta mikä vielä pahempaa - korreloitu alikysely, lisäksi aggregoinnin kanssa. Tämä on erittäin huono.
Tämä on tietysti huono. Mutta tämä on vain toiselta puolelta. Toisaalta tämä on erittäin hyvä, koska ongelmalla on selkeästi ratkaisu ja pyyntöä voidaan parantaa.
Älä mene ennustajan luo (C).
Kyselysuunnitelma ei ole kovin monimutkainen, mutta melko suuntaa-antava:
Toteutussuunnitelma

Mielenkiintoisin ja hyödyllisin, kuten tavallista, alussa ja lopussa.
Sisäkkäinen silmukka (hinta = 935.84..479763226.18 riviä = 3322 leveys = 135) (todellinen aika = 31.536..8220420.295 riviä = 8111656 silmukkaa = 1)
Suunnitteluaika: 3.807 ms
Suoritusaika: 8222351.640ms
Toteutusaika on yli 2 tuntia.

Väärät hypoteesit, jotka veivät aikaa

Hypoteesi 1 - Optimoija on väärässä, rakentaa väärän suunnitelman.

Toteutussuunnitelman visualisoimiseksi käytämme sivustoa https://explain.depesz.com/. Sivusto ei kuitenkaan näyttänyt mitään mielenkiintoista tai hyödyllistä. Ensi ja toisella silmäyksellä - mikään ei todellakaan auttaisi. Ellei - Full Scan on minimaalinen. Mene eteenpäin.

Hypoteesi 2 - Isku pohjaan autoimurin sivulta, sinun on päästävä eroon jarruista.

Mutta autovacuum-demonit käyttäytyvät hyvin, ei ole pitkiä prosesseja. Mikä tahansa vakava kuorma - ei. Pitää etsiä jotain muuta.

Hypoteesi 3 - Tilastot on vanhentunut, sinun on laskettava uudelleen kaikki lentää

Jälleen, ei sitä. Tilastot ovat ajan tasalla. Mikä ei ole yllättävää, kun otetaan huomioon autovakuumiongelmien puuttuminen.

Alkaa optimoida

Päätaulukko 'wdata' ei todellakaan ole pieni, lähes 3 miljoonaa tietuetta.
Ja juuri tälle pöydälle Full Scan menee.

Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") JA ((alasuunnitelma 1) = s."SPENT_DATE"))
-> Seq Scan wdata w:ssä (hinta = 0.00..574151.49 riviä = 26886249 leveys = 46) (todellinen aika = 0.005..8153.565 riviä = 26873950 silmukkaa = 1)
Toimimme normaalisti: "tehdään indeksi ja kaikki lentää".
Teki indeksin kenttään "SPENT_ID"
Tuloksena:
Kyselyn suoritussuunnitelma indeksin avulla

No auttoiko se?
Se oli: 8 222 351.640 ms (hieman yli 2 tuntia)
Siitä tuli: 6 985 431.575 ms (melkein 2 tuntia)
Yleensä samat omenat, sivukuva.
Muistellaanpa klassikoita:
"Onko sinulla sama, mutta ilman siipiä? tulee hakemaan".

Periaatteessa tätä voitaisiin kutsua hyväksi tulokseksi, ei hyväksi, mutta hyväksyttäväksi. Anna asiakkaalle vähintään iso raportti, jossa kerrotaan, kuinka paljon on tehty ja miksi tehty on hyvää.
Lopullinen päätös on kuitenkin vielä kaukana. Hyvin pitkälle.

Ja nyt mielenkiintoisin asia - jatkamme optimointia, hiomme kyselyä

Vaihe yksi - käytä JOIN

Uudelleenkirjoitettu kysely, nyt näyttää tältä (no ainakin kauniimpi):
Tee kysely käyttämällä JOINVALITSE
p. "PARAMETER_ID" parametrin_tunnuksena,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w"LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS alempi_spec_limit,
w. "UPPER_SPEC_LIMIT" AS ylempi_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS kulutettu_nimi,
s."SPENT_DATE" AS kulutettu_päivä,
ote(vuosi alkaen "SPENT_DATE") AS vuosi,
ote (kuukausi "SPENT_DATE") kuukautena,
s."REPORT_NAME" AS raportin_nimi,
s. "STPM_NAME" AS stpm_name,
p"CUSTOMERPARAM_NAME" AS asiakkaan parametrin_nimi
FROM wdata w INNER JOIN käytetty s ON w."SPENT_ID"=s."SPENT_ID"
SISÄLIITTYMINEN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
SISÄLIITTYMÄ käytetty_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
SISÄLIITTYMINEN pd pd PÄÄLLÄ pd."PD_ID" = sp."PD_ID"
MISTÄ
s."SPENT_DATE" >= '2018-07-01' JA s."SPENT_DATE" <= '2018-09-30'AND
s."SPENT_DATE" = (VALITSE MAX(s2."SPENT_DATE")
FROM wdata w2 INNER JOIN käytetty s2 ON w2."SPENT_ID"=s2."SPENT_ID"
SISÄLIITTYMINEN wdata w
PÄÄLLÄ w2."LRM" = w."LRM" );
Suunnitteluaika: 2.486 ms
Suoritusaika: 1223680.326ms

Tässä siis ensimmäinen tulos.
Se oli: 6 985 431.575 ms (melkein 2 tuntia).
Siitä tuli: 1 223 680.326 ms (hieman yli 20 minuuttia).
Hyvä tulos. Periaatteessa taas olisi mahdollista pysähtyä tähän. Mutta niin kiinnostamaton, et voi lopettaa.
FOR

Vaihe kaksi - Päästä eroon korreloidusta alikyselystä

Pyynnön teksti muutettu:
Ei korreloitua alikyselyäVALITSE
p. "PARAMETER_ID" parametrin_tunnuksena,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w"LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS alempi_spec_limit,
w. "UPPER_SPEC_LIMIT" AS ylempi_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS kulutettu_nimi,
s."SPENT_DATE" AS kulutettu_päivä,
ote(vuosi alkaen "SPENT_DATE") AS vuosi,
ote (kuukausi "SPENT_DATE") kuukautena,
s."REPORT_NAME" AS raportin_nimi,
s. "STPM_NAME" AS stpm_name,
p"CUSTOMERPARAM_NAME" AS asiakkaan parametrin_nimi
FROM wdata w SISÄINEN LIITTYMINEN käytetty s PÄÄLLÄ s."SPENT_ID" = w."SPENT_ID"
SISÄLIITTYMINEN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
SISÄLIITTYMÄ käytetty_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
SISÄLIITTYMINEN pd pd PÄÄLLÄ pd."PD_ID" = sp."PD_ID"
SISÄLIITTYMINEN (VALITSE w2."LRM", MAX(s2."SPENT_DATE")
FROM käytetty s2 SISÄINEN LIITTYMINEN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
GROUP W2.LRM
) md w. "LRM" = md. "LRM"
MISTÄ
s."SPENT_DATE" >= '2018-07-01' JA s."SPENT_DATE" <= '2018-09-30';
Suunnitteluaika: 2.291 ms
Suoritusaika: 165021.870ms

Se oli: 1 223 680.326 ms (hieman yli 20 minuuttia).
Siitä tuli: 165 021.870 ms (hieman yli 2 minuuttia).
Tämä on jo aika hyvä.
Mutta kuten englantilaiset sanovat,Mutta aina on mutta". Liian hyvän tuloksen pitäisi automaattisesti herättää epäilyksiä. Tässä on jotain vialla.

Hypoteesi kyselyn korjaamisesta korreloidun alikyselyn poistamiseksi on oikea. Mutta se vaatii hieman säätämistä, jotta lopputulos on oikea.
Tuloksena ensimmäinen välitulos:
Muokattu kysely ilman korreloitua alikyselyäVALITSE
p. "PARAMETER_ID" parametrin_tunnuksena,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w"LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS alempi_spec_limit,
w. "UPPER_SPEC_LIMIT" AS ylempi_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS kulutettu_nimi,
s."SPENT_DATE" AS kulutettu_päivä,
ote(vuosi s. "SPENT_DATE") AS vuosi,
ote (kuukausi s. "SPENT_DATE") kuukautena,
s."REPORT_NAME" AS raportin_nimi,
s. "STPM_NAME" AS stpm_name,
p"CUSTOMERPARAM_NAME" AS asiakkaan parametrin_nimi
FROM wdata w SISÄINEN LIITTYMINEN käytetty s PÄÄLLÄ s."SPENT_ID" = w."SPENT_ID"
SISÄLIITTYMINEN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
SISÄLIITTYMÄ käytetty_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
SISÄLIITTYMINEN pd pd PÄÄLLÄ pd."PD_ID" = sp."PD_ID"
SISÄLIITTYMINEN ( SELECT w2."LRM", MAX(s2."SPENT_DATE") AS "SPENT_DATE"
FROM käytetty s2 SISÄINEN LIITTYMINEN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
GROUP W2.LRM
) md ON md."SPENT_DATE" = s."SPENT_DATE" JA md."LRM" = w."LRM"
MISTÄ
s."SPENT_DATE" >= '2018-07-01' JA s."SPENT_DATE" <= '2018-09-30';
Suunnitteluaika: 3.192 ms
Suoritusaika: 208014.134ms

Joten tuloksena meillä on ensimmäinen hyväksyttävä tulos, jota emme häpeä näyttää asiakkaalle:
Alkoi: 8 222 351.640 ms (yli 2 tuntia)
Saavutettu: 1 223 680.326 ms (hieman yli 20 minuuttia).
Tulos (välitaso): 208 014.134 ms (hieman yli 3 minuuttia).

Erinomainen tulos.

Koko

Tämä olisi voinut lopettaa.
MUTTA…
Ruokahalu tulee syödessä. Tie hallitaan kävelemällä. Mikä tahansa tulos on välivaihe. Pysähtyi kuolleena. Jne.
Jatketaan optimointia.
Hyvä idea. Varsinkin kun otetaan huomioon, että asiakas ei edes vastustanut sitä. Ja jopa vahvasti - puolesta.

Joten on aika suunnitella tietokanta uudelleen. Itse kyselyrakennetta ei voi enää optimoida (vaikka, kuten myöhemmin kävi ilmi, on mahdollisuus, että kaikki todella lentää). Mutta nyt tietokannan suunnittelun optimointi ja kehittäminen on jo erittäin lupaava idea. Ja mikä tärkeintä, mielenkiintoista. Jälleen, muista nuoruus. Loppujen lopuksi minusta ei tullut heti DBA:ta, kasvoin ohjelmoijista (basic, assembler, si, si double plused, oracle, plsql). Mielenkiintoinen aihe tietysti erillisiin muistelmiin ;-).
Älä kuitenkaan poikkea.

Niin,

Ja ehkä osiointi auttaa meitä?
Spoileri - "Kyllä, se auttoi ja suorituskyvyn optimoinnissa, mukaan lukien."

Mutta se on täysin eri tarina...

Jatkuu…

Lähde: will.com