Iwwert wéi mir d'PostgreSQL Ufro musse optimiséieren a wat alles erauskoum.
Firwat musst Dir? Jo, well déi 4 Joer virdrun huet alles roueg, roueg, wéi eng Auer tickt.
Als Epigraph.
Baséiert op real Evenementer.
All Nimm goufen geännert, Zoufall sinn zoufälleg.
Wann Dir e bestëmmt Resultat erreecht, ass et ëmmer interessant ze erënneren wat den Impuls fir den Ufank war, wou alles ugefaang huet.
Also, wat geschitt ass als Resultat ass kuerz am Artikel beschriwwen "".
Et wäert wahrscheinlech interessant sinn d'Kette vu fréieren Eventer nei ze kreéieren.
D'Geschicht huet de genaue Startdatum gespäichert - 2018-09-10 18:02:48.
Och an der Geschicht gëtt et eng Ufro, aus där alles ugefaang huet:
Problem Ufroberuflecher Organisatioun
p. "PARAMETER_ID" als parameter_id,
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 lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s.“SPENT_DATE” AS spend_date,
Extrait(Joer vum "SPENT_DATE") AS Joer,
Extrait(Mount vum "SPENT_DATE") als Mount,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p. "CUSTOMERPARAM_NAME" AS customerparam_name
VUN wdata w,
verbruecht s,
pmtr p,
spend_pd sp,
pd pd
WOU s.“SPENT_ID” = w.“SPENT_ID”
AN p."PARAMETER_ID" = w."PARAMETER_ID"
AN s.“SPENT_ID” = sp.“SPENT_ID”
AND pd."PD_ID" = sp."PD_ID"
AN s.“SPENT_DATE” >= '2018-07-01' AN s.“SPENT_DATE” <= '2018-09-30'
an s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
VUN ausginn s2,
wdatt w2
WHERE s2.“SPENT_ID” = w2.“SPENT_ID”
AN w2.“LRM” = w.“LRM”);
Beschreiwung vum Problem ass prévisibel Standard - "Alles ass schlecht. Sot mir wat de Problem ass."
Ech erënnere mech direkt un eng Anekdot aus der Zäit vun 3 an en halleft Zoll Drive:
De Lamer kënnt bei den Hacker.
-Näischt funktionnéiert fir mech, sot mir wou de Problem ass.
-An DNA...
Awer natierlech ass dëst net de Wee fir Performance Tëschefäll ze léisen. "Si verstinn eis vläicht net"(Mat). Mir mussen et erausfannen.
Ma, loosst eis graven. Vläicht wäert eppes als Resultat accumuléieren.
Enquête ugefaang
Also, wat kann direkt mat bloussem A gesi ginn, ouni iwwerhaapt ze ERKLÄREN.
1) JOIN sinn net benotzt. Dëst ass schlecht, besonnesch wann d'Zuel vun de Verbindungen méi wéi eng ass.
2) Awer wat nach méi schlëmm ass ass korreléiert Ënnerfroen, ausserdeem mat Aggregatioun. Dëst ass ganz schlecht.
Dëst ass natierlech schlecht. Mä dëst ass nëmmen op der enger Säit. Op där anerer Säit ass dat ganz gutt, well de Problem kloer eng Léisung huet an eng Demande déi ka verbessert ginn.
Gitt net bei e Verméigen (C).
De Ufroplang ass net sou komplizéiert, awer et ass ganz indikativ:
Ausféierung Plang
Déi interessantst an nëtzlech, wéi gewinnt, ass am Ufank an um Enn.
Nested Loop (Käschte = 935.84..479763226.18 Reihen = 3322 Breet = 135) (tatsächlech Zäit = 31.536..8220420.295 Reihen = 8111656 Loops = 1)
Planungszäit: 3.807 ms
Ausféierung Zäit: 8222351.640 ms
Ofschlosszäit ass méi wéi 2 Stonnen.
Falsch Hypothesen déi Zäit gedauert hunn
Hypothese 1 - Den Optimizer mécht e Feeler a baut de falsche Plang.
Fir den Ausféierungsplang ze visualiséieren, benotze mir de Site . Wéi och ëmmer, de Site huet näischt interessant oder nëtzlech gewisen. Op den éischte an zweete Bléck gëtt et näischt wat wierklech hëllefe kéint. Ass et méiglech datt Voll Scan minimal ass. Maach weider.
Hypothese 2-Impakt op der Basis vun der Autovakuum Säit, Dir musst vun de Bremsen lass ginn.
Awer d'Autovakuum Daemone behuelen sech gutt, et gi keng laang hängend Prozesser. Keng sérieux Laascht. Mir mussen eppes anescht sichen.
Hypothese 3 - Statistike sinn al, alles muss nei berechent ginn
Nach eng Kéier, net dat. D'Statistike sinn aktuell. Wat, wéinst dem Mangel u Probleemer mam Autovakuum, net iwwerrascht ass.
Loosst eis ufänken ze optimiséieren
D'Haapttabell 'wdata' ass sécher net kleng, bal 3 Millioune Rekorder.
An et ass dës Tabell déi de Full Scan verfollegt.
Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") AN ((SubPlan 1) = s"SPENT_DATE"))
-> Seq Scan op wdata w (Käschte=0.00..574151.49 Reihen=26886249 Breet=46) (tatsächlech Zäit=0.005..8153.565 Reihen=26873950 Schleifen=1)
Mir maachen déi Standard Saach: "Komm, loosst eis en Index maachen an alles wäert fléien."
Erstellt en Index am Feld "SPENT_ID".
Als Resultat vun:
Ufro Ausféierung Plang benotzt Index
Gutt, huet et gehollef?
Et war: 8 222 351.640 ms (e bësse méi wéi 2 Stonnen)
Et gouf: 6 985 431.575 ms (bal 2 Stonnen)
Am Allgemengen, déi selwecht Äppel, Säit Vue.
Loosst eis un d'Klassiker erënneren:
"Hutt Dir déiselwecht, awer ouni Flilleken? wäert sichen".
Prinzipiell kann dat e gutt Resultat genannt ginn, gutt, net gutt, awer akzeptabel. Gitt op d'mannst e grousse Bericht un de Client dee beschreiwt wéi vill gemaach gouf a firwat wat gemaach gouf gutt war.
Awer trotzdem ass déi definitiv Decisioun nach wäit ewech. Ganz wäit.
An elo déi interessantst Saach - mir optimiséieren weider, mir poléieren d'Ufro
Schrëtt One - Benotzt JOIN
Déi ëmgeschriwwen Ufro gesäit elo esou aus (gutt op d'mannst méi schéin):
Ufro mat JOINberuflecher Organisatioun
p. "PARAMETER_ID" als parameter_id,
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 lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s.“SPENT_DATE” AS spend_date,
Extrait(Joer vum "SPENT_DATE") AS Joer,
Extrait(Mount vum "SPENT_DATE") als Mount,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p. "CUSTOMERPARAM_NAME" AS customerparam_name
VUN wdata w INNER JOIN ausginn s ON 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”
WOU
s.“SPENT_DATE” >= '2018-07-01' AN s.“SPENT_DATE” <= '2018-09-30'AN
s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
VUN wdata w2 INNER JOIN ausginn s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
ON w2.“LRM” = w.“LRM”);
Planungszäit: 2.486 ms
Ausféierung Zäit: 1223680.326 ms
Also, dat éischt Resultat.
Et war: 6 ms (bal 985 Stonnen).
Et gouf: 1 223 680.326 ms (just iwwer 20 Minutten).
Gutt Resultat. Prinzipiell kéinte mer do erëm ophalen. Awer et ass sou oninteressant, Dir kënnt net ophalen.
FIR
Schrëtt zwee - lass vun der korreléierter Ënnerquery
Geännert Ufro Text:
Ouni korreléiert Ënnerqueryberuflecher Organisatioun
p. "PARAMETER_ID" als parameter_id,
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 lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s.“SPENT_DATE” AS spend_date,
Extrait(Joer vum "SPENT_DATE") AS Joer,
Extrait(Mount vum "SPENT_DATE") als Mount,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p. "CUSTOMERPARAM_NAME" AS customerparam_name
VUN wdata w INNER JOIN ausginn s ON 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 (SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”)
VUN ausginn s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GROUP BY w2.“LRM”
) md op w.“LRM” = md.“LRM”
WOU
s."SPENT_DATE" >= '2018-07-01' AN s."SPENT_DATE" <= '2018-09-30';
Planungszäit: 2.291 ms
Ausféierung Zäit: 165021.870 ms
Et war: 1 223 680.326 ms (just iwwer 20 Minutten).
Et gouf: 165 021.870 ms (just iwwer 2 Minutten).
Dëst ass schonn zimlech gutt.
Wéi och ëmmer, wéi d'Briten soen "Awer, et gëtt ëmmer en awer" E Resultat dat ze gutt ass, soll automatesch Mësstrauen opwerfen. Hei ass eppes falsch.
D'Hypothese iwwer d'Korrektur vun der Ufro fir vun der korreléierter Ënnerquery lass ze ginn ass richteg. Awer Dir musst et e bëssen upassen fir datt d'Finale Resultat richteg ass.
Als Resultat, déi éischt Tëschenzäit Resultat:
Geännert Ufro ouni korreléiert Ënnerqueryberuflecher Organisatioun
p. "PARAMETER_ID" als parameter_id,
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 lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s.“SPENT_DATE” AS spend_date,
Extrait(Joer ab s.“SPENT_DATE”) AS Joer,
Extrait(Mount ab s.“SPENT_DATE”) als Mount,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p. "CUSTOMERPARAM_NAME" AS customerparam_name
VUN wdata w INNER JOIN ausginn s ON 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 ( SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”) AS “SPENT_DATE”
VUN ausginn s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GROUP BY w2.“LRM”
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” AND md.“LRM” = w.“LRM”
WOU
s."SPENT_DATE" >= '2018-07-01' AN s."SPENT_DATE" <= '2018-09-30';
Planungszäit: 3.192 ms
Ausféierung Zäit: 208014.134 ms
Also, wat mir ophalen ass dat éischt akzeptabelt Resultat, wat net schued ass dem Client ze weisen:
Ugefaangen mat: 8 222 351.640 ms (méi wéi 2 Stonnen)
Mir hunn et fäerdeg bruecht ze erreechen: 1 ms (e bësse méi wéi 223 Minutten).
Resultat (Zwëschenzäit): 208 014.134 ms (just iwwer 3 Minutten).
Exzellent Resultat.
D 'Resultat
Mir hätten do ophalen.
MEE…
Den Appetit kënnt mam Iessen. Deen, dee geet, wäert d'Strooss beherrschen. All Resultat ass Mëttelstuf. Gestoppt a gestuerwen. etc.
Loosst eis weider Optimisatioun maachen.
Flott Iddi. Besonnesch wann ee bedenkt datt de Client et net emol verstanen huet. An esouguer staark dofir.
Also, et ass Zäit fir eng Datebank Redesign. D'Ufrostruktur selwer kann net méi optimiséiert ginn (obwuel, wéi et spéider erausgestallt gouf, et ass eng Optioun fir sécherzestellen datt alles tatsächlech klappt). Awer fir den Datebankdesign ze optimiséieren an z'entwéckelen ass schonn eng ganz villverspriechend Iddi. An am wichtegsten interessant. Nach eng Kéier, erënnert Är Jugend. Ech sinn net direkt en DBA ginn, ech sinn als Programméierer opgewuess (BASIC, assembler, C, double-plus C, Oracle, plsql). En interessant Thema, natierlech, fir eng separat Mémoire ;-).
Loosst eis awer net oflenken.
An dofir,
Oder vläicht wäert d'Partitionéierung eis hëllefen?
Spoiler - "Jo, et huet gehollef, och bei der Optimiséierung vun der Leeschtung."
Awer dat ass eng ganz aner Geschicht ...
Fortsetzung kënnt no…
Source: will.com