Manstu hvernig þetta byrjaði allt saman. Allt var í fyrsta skipti og aftur

Um hvernig við þurftum að fínstilla PostgreSQL fyrirspurnina og hvað kom út úr þessu öllu saman.
Hvers vegna þurftirðu að gera það? Já, vegna þess að síðustu 4 árin gekk allt hljóðlega, rólega, eins og klukka tifaði.
Sem grafskrift.

Byggt á raunverulegum atburðum.
Öllum nöfnum hefur verið breytt, tilviljanir eru tilviljunarkenndar.

Þegar þú nærð ákveðnum árangri er alltaf áhugavert að muna hvað var hvatinn að upphafinu, hvar allt byrjaði.

Svo, hvað gerðist í kjölfarið er stuttlega lýst í greininni "Myndun sem ein af aðferðunum til að bæta PostgreSQL árangur'.

Líklega verður fróðlegt að endurskapa keðju fyrri atburða.
Sagan vistaði nákvæma upphafsdagsetningu - 2018-09-10 18:02:48.
Einnig er í sögunni beiðni þar sem allt byrjaði:
VandamálabeiðniVELJA
p.„PARAMETER_ID“ sem parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS viðskiptavinarhlutanúmer,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.„RTD_VALUE“ AS RTD_value,
w.„LOWER_SPEC_LIMIT“ AS lower_spec_limit,
w.„UPPER_SPEC_LIMIT“ AS efri_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS eytt_nafn,
s.“SPENT_DATE” AS spent_date,
útdráttur (ár frá "SPENT_DATE") AS ár,
útdráttur (mánuður frá "SPENT_DATE") sem mánuður,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.„CUSTOMERPARAM_NAME“ AS viðskiptavinaparam_name
FRÁ wdata w,
eyddi 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” = (SELECT MAX(s2.“SPENT_DATE”)
FRÁ eytt s2,
wdata w2
WHERE s2.“SPENT_ID” = w2.“SPENT_ID“
OG w2.“LRM” = w.“LRM”);

Lýsing á vandamálinu er fyrirsjáanlega staðlað - „Allt er slæmt. Segðu mér hvert vandamálið er."
Ég mundi strax eftir sögu frá tímum 3 og hálfs tommu aksturs:

Lamerinn kemur að tölvuþrjótinum.
-Ekkert virkar fyrir mig, segðu mér hvar vandamálið er.
-Í DNA...

En auðvitað er þetta ekki leiðin til að leysa frammistöðuatvik. “Þeir skilja okkur kannski ekki"(Með). Við þurfum að finna út úr því.
Jæja, við skulum grafa. Kannski safnast eitthvað upp í kjölfarið.

Rannsókn hófst

Svo, hvað er hægt að sjá strax með berum augum, án þess að grípa til þess að útskýra.
1) JOIN eru ekki notuð. Þetta er slæmt, sérstaklega ef fjöldi tenginga er fleiri en ein.
2) En það sem er enn verra er fylgni undirfyrirspurna, þar að auki við samansafn. Þetta er mjög slæmt.
Þetta er auðvitað slæmt. En þetta er bara annars vegar. Hins vegar er þetta mjög gott því vandamálið hefur greinilega lausn og beiðni sem má bæta.
Ekki fara til spákonu (C).
Fyrirspurnaráætlunin er ekki svo flókin, en hún er frekar leiðbeinandi:
Framkvæmdaáætlun

Það áhugaverðasta og gagnlegasta, eins og venjulega, er í upphafi og lok.
Hreiður lykkja (kostnaður=935.84..479763226.18 raðir=3322 breidd=135) (raunverulegur tími=31.536..8220420.295 raðir=8111656 lykkjur=1)
Áætlunartími: 3.807 ms
Framkvæmdartími: 8222351.640 ms
Lokunartími er meira en 2 klst.

Rangar tilgátur sem tóku tíma

Tilgáta 1 - Hagræðingarmaðurinn gerir mistök og býr til ranga áætlun.

Til að sjá framkvæmdaráætlunina munum við nota síðuna https://explain.depesz.com/. Hins vegar sýndi síðan ekkert áhugavert eða gagnlegt. Við fyrstu og aðra sýn er ekkert sem gæti raunverulega hjálpað. Er mögulegt að Full Scan sé í lágmarki. Gjörðu svo vel.

Tilgáta 2-Áhrif á botninn frá sjálftæmdu hliðinni, þú þarft að losa þig við bremsurnar.

En autovacuum púkarnir hegða sér vel, það eru engir langvarandi ferli. Ekkert alvarlegt álag. Við þurfum að leita að einhverju öðru.

Tilgáta 3 - Tölfræði er úrelt, allt þarf að endurreikna

Aftur, ekki það. Tölfræðin er uppfærð. Sem, í ljósi skorts á vandamálum með autovacuum, kemur ekki á óvart.

Við skulum byrja að hagræða

Aðaltaflan 'wdata' er vissulega ekki lítil, tæpar 3 milljónir færslur.
Og það er þessi tafla sem Full Scan fylgir.

Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") OG ((SubPlan 1) = s."SPENT_DATE"))
-> Seq Scan á wdata w (kostnaður=0.00..574151.49 raðir=26886249 breidd=46) (raunverulegur tími=0.005..8153.565 raðir=26873950 lykkjur=1)
Við gerum staðlaðan hlut: „komdu, við skulum búa til vísitölu og allt mun fljúga.
Búið til vísitölu á „SPENT_ID“ reitnum
Í kjölfarið:
Framkvæmdaáætlun fyrirspurna með vísitölu

Jæja, hjálpaði það?
Það var: 8 222 351.640 ms (lítið meira en 2 tímar)
Það varð: 6 985 431.575 ms (næstum 2 klst.)
Almennt, sömu epli, hliðarsýn.
Við skulum muna eftir klassíkinni:
„Ertu með þann sama, en án vængja? Mun leita".

Í grundvallaratriðum mætti ​​kalla þetta góða niðurstöðu, tja, ekki góð, en ásættanleg. Að minnsta kosti, gefðu viðskiptavinum stóra skýrslu sem lýsir hversu mikið hefur verið gert og hvers vegna það sem var gert var gott.
En samt er endanleg ákvörðun enn langt í burtu. Mjög langt.

Og nú er það áhugaverðasta - við höldum áfram að hagræða, við munum pússa beiðnina

Skref eitt - Notaðu JOIN

Endurskrifaða beiðnin lítur nú svona út (ja allavega fallegri):
Fyrirspurn með JOINVELJA
p.„PARAMETER_ID“ sem parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS viðskiptavinarhlutanúmer,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.„RTD_VALUE“ AS RTD_value,
w.„LOWER_SPEC_LIMIT“ AS lower_spec_limit,
w.„UPPER_SPEC_LIMIT“ AS efri_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS eytt_nafn,
s.“SPENT_DATE” AS spent_date,
útdráttur (ár frá "SPENT_DATE") AS ár,
útdráttur (mánuður frá "SPENT_DATE") sem mánuður,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.„CUSTOMERPARAM_NAME“ AS viðskiptavinaparam_name
FROM wdata w INNER JOIN varið s ON w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON bls.“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“
HVAR
s.“SPENT_DATE” >= '2018-07-01' OG s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
FRÁ wdata w2 INNER JOIN eytt s2 ON w2.“SPENT_ID”=s2.“SPENT_ID“
INNER JOIN wdata w
ON w2.“LRM” = w.“LRM” );
Áætlunartími: 2.486 ms
Framkvæmdartími: 1223680.326 ms

Svo, fyrsta niðurstaðan.
Það var: 6 ms (tæplega 985 klst.).
Það varð: 1 223 680.326 ms (rúmlega 20 mínútur).
Góður árangur. Í grundvallaratriðum, aftur, gætum við stoppað þar. En það er svo óáhugavert að þú getur ekki hætt.
FYRIR

Skref tvö - losaðu þig við tengda undirfyrirspurnina

Breyttur beiðni texti:
Án fylgni undirfyrirspurnarVELJA
p.„PARAMETER_ID“ sem parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS viðskiptavinarhlutanúmer,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.„RTD_VALUE“ AS RTD_value,
w.„LOWER_SPEC_LIMIT“ AS lower_spec_limit,
w.„UPPER_SPEC_LIMIT“ AS efri_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS eytt_nafn,
s.“SPENT_DATE” AS spent_date,
útdráttur (ár frá "SPENT_DATE") AS ár,
útdráttur (mánuður frá "SPENT_DATE") sem mánuður,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.„CUSTOMERPARAM_NAME“ AS viðskiptavinaparam_name
FROM wdata w INNER JOIN varið s ON s.“SPENT_ID” = w.“SPENT_ID“
INNER JOIN pmtr p ON bls.“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 (VELDU w2.“LRM”, MAX(s2.“SPENT_DATE”)
FRÁ varið s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID“
GROUP BY w2.“LRM“
) md á w.“LRM” = md.“LRM“
HVAR
s."SPENT_DATE" >= '2018-07-01' OG s."SPENT_DATE" <= '2018-09-30';
Áætlunartími: 2.291 ms
Framkvæmdartími: 165021.870 ms

Það var: 1 223 680.326 ms (rúmlega 20 mínútur).
Það varð: 165 021.870 ms (rúmlega 2 mínútur).
Þetta er nú þegar nokkuð gott.
Hins vegar, eins og Bretar segja "En, það er alltaf en" Of góð niðurstaða ætti sjálfkrafa að vekja tortryggni. Hér er eitthvað að.

Tilgátan um að leiðrétta fyrirspurnina til að losna við tengda undirfyrirspurnina er rétt. En þú þarft að laga það aðeins til að lokaniðurstaðan verði rétt.
Þar af leiðandi, fyrsta milliniðurstaðan:
Breytt fyrirspurn án tengdrar undirfyrirspurnarVELJA
p.„PARAMETER_ID“ sem parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS viðskiptavinarhlutanúmer,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.„RTD_VALUE“ AS RTD_value,
w.„LOWER_SPEC_LIMIT“ AS lower_spec_limit,
w.„UPPER_SPEC_LIMIT“ AS efri_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS eytt_nafn,
s.“SPENT_DATE” AS spent_date,
útdráttur (ár frá s.“SPENT_DATE”) AS ár,
útdráttur (mánuður frá s.“SPENT_DATE”) sem mánuður,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.„CUSTOMERPARAM_NAME“ AS viðskiptavinaparam_name
FROM wdata w INNER JOIN varið s ON s.“SPENT_ID” = w.“SPENT_ID“
INNER JOIN pmtr p ON bls.“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 ( SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”) AS „SPENT_DATE“
FRÁ varið 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“
HVAR
s."SPENT_DATE" >= '2018-07-01' OG s."SPENT_DATE" <= '2018-09-30';
Áætlunartími: 3.192 ms
Framkvæmdartími: 208014.134 ms

Svo, það sem við endum með er fyrsta ásættanlega niðurstaðan, sem er ekki synd að sýna viðskiptavinum:
Byrjaði með: 8 222 351.640 ms (meira en 2 klst.)
Okkur tókst að ná: 1 ms (lítið meira en 223 mínútur).
Niðurstaða (tímabundið): 208 014.134 ms (rúmlega 3 mínútur).

Frábær árangur.

Samtals

Við hefðum getað stoppað þar.
EN…
Matarlyst fylgir því að borða. Sá sem gengur mun ráða veginum. Sérhver niðurstaða er millistig. Stöðvaði og dó. O.s.frv.
Höldum áfram hagræðingu.
Frábær hugmynd. Sérstaklega í ljósi þess að viðskiptavinurinn var ekki einu sinni sama. Og jafnvel sterklega fyrir það.

Svo það er kominn tími á endurhönnun gagnagrunns. Fyrirspurnarskipulagið sjálft er ekki lengur hægt að fínstilla (þó, eins og síðar kom í ljós, þá er möguleiki til að tryggja að allt bregðist í raun). En að byrja að hagræða og þróa gagnagrunnshönnunina er nú þegar mjög efnileg hugmynd. Og síðast en ekki síst áhugavert. Aftur, mundu æsku þína. Enda varð ég ekki strax DBA, ég ólst upp sem forritari (BASIC, assembler, C, double-plus C, Oracle, plsql). Athyglisvert efni auðvitað fyrir sérstaka minningargrein ;-).
Hins vegar skulum við ekki trufla okkur.

Svo,

Eða kannski mun skipting hjálpa okkur?
Spoiler - "Já, það hjálpaði, þar á meðal við að hámarka frammistöðu."

En það er allt önnur saga...

Framhald…

Heimild: www.habr.com