PostgreSQL kontsulten optimizazioari nola egin behar izan diodan aurre eta guzti honetatik zer atera zen.
Zergatik behar izan zenuen? Bai, aurreko 4 urteetan dena isil-isilik, lasai, erloju batek jotzen zuen bezala funtzionatu baitzuen.
epigrafe gisa.
Benetako gertakarietan oinarrituta.
Izen guztiak aldatu dira, kasualitateak ausazkoak dira.
Emaitza jakin bat lortzen denean, beti da interesgarria gogoratzea zein izan zen hasierako bultzada, nola hasi zen dena.
Beraz, horren ondorioz gertatutakoa laburki deskribatzen da artikuluan "'.
Ziurrenik interesgarria izango da aurreko ekitaldien katea birsortzea.
Historiak hasiera data zehatza mantendu zuen - 2018-09-10 18:02:48.
Gainera, istorioan dena hasi zen eskaera bat dago:
Arazo eskaeraAUKERATU
or. "PARAMETER_ID" parameter_id gisa,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LRM" LRM AS,
w. "LOTID" lotid AS,
w. "RTD_VALUE" RTD_value AS,
w. "LOWER_SPEC_LIMIT" beheko_spec_limit AS,
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s."SPENT_DATE" AS gastatu_data,
atera (urtea "SPENT_DATE") AS urteko,
atera("SPENT_DATEtik aurrera hilabetea") hilabete gisa,
s."REPORT_NAME" AS txosten_izena,
or. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
wdataw-tik,
gastatu s,
pmtrp,
gastatu_pdsp,
pd pd
WHERE s."SPENT_ID" = w "SPENT_ID"
ETA p."PARAMETER_ID" = w "PARAMETER_ID"
AND s."SPENT_ID" = sp."SPENT_ID"
ETA pd. "PD_ID" = sp. "PD_ID"
ETA s."SPENT_DATE" >= '2018-07-01' ETA s."SPENT_DATE" <= '2018-09-30'
eta s."SPENT_DATE" = (HAUSTU MAX (s2."SPENT_DATE")
s2 gastatu FROM,
wdata w2
WHERE s2."SPENT_ID" = w2."SPENT_ID"
ETA w2."LRM" = w."LRM");
Arazoaren deskribapena, aurreikusteko estandarra - βDena da txarra. Esadazu zein den arazoaΒ».
Berehala gogoratu nintzen 3 hazbete eta erdiko diskoen garaiko txantxa bat:
Lamer hackerra dator.
- Ezer ez zait balio, esaidazu non dagoen arazoa.
-ADNan...
Baina, noski, hau ez da errendimenduko gorabeherak konpontzeko modua. βAgian ez gaituzte ulertu"(Ekin). Asmatu beharra dago.
Tira, zula dezagun. Agian hori metatuko da ondorioz.
inbertsioa hasi zen
Beraz, berehala begi hutsez ikus daitekeena, AZALDU-ren laguntzara jo ere egin gabe.
1) JOIN-ak ez dira erabiltzen. Hau txarra da, batez ere konexio kopurua bat baino gehiago bada.
2) Baina are okerragoa dena: azpikontsulta korrelazionatua, gainera, agregazioarekin. Hau oso txarra da.
Hau txarra da, noski. Baina hau alde batetik baino ez da. Bestalde, hau oso ona da, arazoak argi eta garbi duelako irtenbidea eta eskaera hobetu daitekeelako.
Ez joan igarlearengana (C).
Kontsulta plana ez da hain konplikatua, baina nahiko adierazgarria:
Exekuzio plana
Interesgarriena eta erabilgarriena, ohi bezala, hasieran eta amaieran.
Begizta habiaratua (kostua=935.84..479763226.18 errenkada=3322 zabalera=135) (benetako denbora=31.536..8220420.295 errenkada=8111656 begizta=1)
Planifikazio-denbora: 3.807 ms
Exekuzio denbora: 8222351.640ms
Exekuzio-denbora 2 ordu baino gehiagokoa da.
Denbora behar izan duten hipotesi faltsuak
1. hipotesia- Optimizatzailea gaizki dago, plan okerra eraikitzen du.
Exekuzio plana ikusteko, gunea erabiliko dugu . Hala ere, webguneak ez zuen ezer interesgarri edo erabilgarria erakutsi. Lehen eta bigarren begiratuan - benetan lagun dezakeen ezer. Eskaneatu osoa gutxienekoa ez bada behintzat. Segi aurrera.
Hipotesia 2-Auto-hutsean oinarrian inpaktua, balaztak kendu behar dituzu.
Baina, hutsean automatikoko deabruek ondo portatzen dute, ez dago prozesu luzerik. Edozein karga larri - ez. Beste zerbait bilatu behar.
3. Hipotesia-Estatistikak zaharkituta daude, euli dena berriro kalkulatu behar duzu
Berriz ere, ez hori. Estatistikak eguneratuta daude. Hori, autohutsean arazorik ez dagoela ikusita, ez da harritzekoa.
Optimitzen hasita
'wdata' taula nagusia ez da txikia, ia 3 milioi erregistro.
Eta mahai horretan doa Full Scan.
Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") ETA ((Subplan 1) = s."SPENT_DATE"))
-> Seq Scan wdata w-n (kostua=0.00..574151.49 errenkada=26886249 zabalera=46) (benetako denbora=0.005..8153.565 errenkada=26873950 begiztak=1)
Estandar gisa jokatzen dugu: βegin dezagun aurkibidea eta dena hegan egiten duβ.
Indize bat egin du "SPENT_ID" eremuan
Hori dela eta:
Kontsulta exekuzio plana indize bat erabiliz
Beno, lagundu al du?
izan zen: 8 222 351.640 ms (2 ordu pasatxo)
Hau bihurtu zen: 6 985 431.575 ms (ia 2 ordu)
Oro har, sagar berdinak, alboko ikuspegia.
Gogora ditzagun klasikoak:
Β«Berdin al duzu, baina hegorik gabe? Bilatuko du".
Printzipioz, horri emaitza ona dei genioke, ba, ez ona, baina onargarria. Gutxienez, eman txosten handi bat bezeroari, zenbat egin den eta zergatik den ona deskribatzen duena.
Hala ere, azken erabakia urrun dago oraindik. Oso urrun.
Eta orain gauzarik interesgarriena - optimizatzen jarraitzen dugu, kontsulta leunduko dugu
Lehen urratsa - erabili JOIN
Berridatzitako kontsulta, orain honen itxura du (ba politagoa behintzat):
Egin kontsulta JOIN erabilizAUKERATU
or. "PARAMETER_ID" parameter_id gisa,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LRM" LRM AS,
w. "LOTID" lotid AS,
w. "RTD_VALUE" RTD_value AS,
w. "LOWER_SPEC_LIMIT" beheko_spec_limit AS,
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s."SPENT_DATE" AS gastatu_data,
atera (urtea "SPENT_DATE") AS urteko,
atera("SPENT_DATEtik aurrera hilabetea") hilabete gisa,
s."REPORT_NAME" AS txosten_izena,
or. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN gastatu 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"
NON
s."SPENT_DATE" >= '2018-07-01' ETA s."SPENT_DATE" <= '2018-09-30'AND
s."SPENT_DATE" = (HAUTATU MAX (s2."SPENT_DATE")
FROM wdata w2 INNER JOIN gastatu s2 ON w2."SPENT_ID"=s2."SPENT_ID"
INNER JOIN wdata w
ON w2."LRM" = w."LRM" );
Planifikazio-denbora: 2.486 ms
Exekuzio denbora: 1223680.326ms
Beraz, hona hemen lehen emaitza.
izan zen: 6 985 431.575 ms (ia 2 ordu).
Hau bihurtu zen: 1 223 680.326 ms (20 minutu pasatxo).
Emaitza ona. Printzipioz, berriro ere, posible izango litzateke hor gelditzea. Baina hain interesgabea, ezin duzu gelditu.
EGITEKO
Bigarren urratsa - Kendu korrelazionatutako azpikontsulta
Eskaeraren testua aldatu da:
Ez dago erlazionatutako azpikontsultarikAUKERATU
or. "PARAMETER_ID" parameter_id gisa,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LRM" LRM AS,
w. "LOTID" lotid AS,
w. "RTD_VALUE" RTD_value AS,
w. "LOWER_SPEC_LIMIT" beheko_spec_limit AS,
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s."SPENT_DATE" AS gastatu_data,
atera (urtea "SPENT_DATE") AS urteko,
atera("SPENT_DATEtik aurrera hilabetea") hilabete gisa,
s."REPORT_NAME" AS txosten_izena,
or. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN gastatu 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 (HAUSTU w2."LRM", MAX(s2."SPENT_DATE")
FROM gastatu s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
TALDEA w2.LRM
) md on w. "LRM" = md. "LRM"
NON
s."SPENT_DATE" >= '2018-07-01' ETA s."SPENT_DATE" <= '2018-09-30';
Planifikazio-denbora: 2.291 ms
Exekuzio denbora: 165021.870ms
izan zen: 1 223 680.326 ms (20 minutu pasatxo).
Hau bihurtu zen: 165 021.870 ms (2 minutu pasatxo).
Hau dagoeneko nahiko ona da.
Hala ere, ingelesek diotenez,Baina, beti dago baina". Emaitza onek automatikoki susmoa piztu beharko luke. Zerbait gaizki dago hemen.
Korrelatutako azpikontsulta kentzeko kontsulta zuzentzeari buruzko hipotesia zuzena da. Baina doikuntza txiki bat behar da azken emaitza egokia lortzeko.
Ondorioz, tarteko lehen emaitza:
Editatutako kontsulta azpikontsulta korrelazionatu gabeAUKERATU
or. "PARAMETER_ID" parameter_id gisa,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LRM" LRM AS,
w. "LOTID" lotid AS,
w. "RTD_VALUE" RTD_value AS,
w. "LOWER_SPEC_LIMIT" beheko_spec_limit AS,
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s."SPENT_DATE" AS gastatu_data,
atera(urtea "SPENT_DATE") AS urteko,
atera("SPENT_DATE") hilabete gisa,
s."REPORT_NAME" AS txosten_izena,
or. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN gastatu 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 ( HAUTATU w2."LRM", MAX(s2."SPENT_DATE") "SPENT_DATE" AS
FROM gastatu s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
TALDEA w2.LRM
) md ON md."SPENT_DATE" = s."SPENT_DATE" ETA md."LRM" = w."LRM"
NON
s."SPENT_DATE" >= '2018-07-01' ETA s."SPENT_DATE" <= '2018-09-30';
Planifikazio-denbora: 3.192 ms
Exekuzio denbora: 208014.134ms
Beraz, emaitza onargarria den lehenengo emaitza da, bezeroari erakusteko lotsarik ez duguna:
Honekin hasi zen: 8 222 351.640 ms (2 ordu baino gehiago)
Lortutakoa: 1 ms (223 minutu pasatxo).
Emaitza (tartekoa): 208 014.134 ms (3 minutu pasatxo).
Emaitza bikaina.
Guztira
Hau gelditu zitekeen.
BAINAβ¦
Gosea jatearekin dator. Oinez jabetuko da bidea. Edozein emaitza da tarteko. Hilda gelditu. Etab.
Jarrai dezagun optimizazioarekin.
Ideia bikaina. Batez ere bezeroa kontra ere ez zegoela kontuan hartuta. Eta nahiz eta biziki - gatik.
Beraz, datu-basea birdiseinatzeko garaia da. Kontsulta-egitura bera ezin da jada optimizatu (nahiz eta, geroago agertu zen bezala, dena benetan hegan egiteko aukera dagoen). Baina orain datu-basearen diseinua optimizatzeko eta garatzeko, dagoeneko oso ideia itxaropentsua da. Eta garrantzitsuena interesgarria. Berriz ere, gogoratu gaztetasuna. Azken finean, ez nintzen berehala DBA bihurtu, programatzaileetatik hazi nintzen (oinarrizkoa, muntatzailea, si, si bitan plused, oracle, plsql). Gai interesgarria, noski, aparteko memoriak egiteko ;-).
Hala ere, ez gaitezen alde batera utzi.
Horrela,
Eta agian sekzioak lagunduko digu?
Spoiler - "Bai, lagundu zuen, eta errendimendua optimizatzen, barne."
Baina hori guztiz bestelako istorio bat da...
Jarraituko duβ¦
Iturria: www.habr.com