ProHoster > blog > Gweinyddiaeth > Ydych chi'n cofio sut y dechreuodd y cyfan. Roedd popeth am y tro cyntaf ac eto

Ydych chi'n cofio sut y dechreuodd y cyfan. Roedd popeth am y tro cyntaf ac eto

Ynglŷn â sut roedd yn rhaid i mi ddelio ag optimeiddio ymholiad PostgreSQL a beth ddaeth allan o hyn i gyd.
Pam oedd rhaid i chi? Oedd, oherwydd y 4 blynedd blaenorol roedd popeth yn gweithio'n dawel, yn dawel, fel cloc yn tician.
fel epigraff.

Ydych chi'n cofio sut y dechreuodd y cyfan. Roedd popeth am y tro cyntaf ac eto

Yn seiliedig ar ddigwyddiadau go iawn.
Mae pob enw wedi'i newid, mae cyd-ddigwyddiadau ar hap.

Pan gyflawnir canlyniad penodol, mae bob amser yn ddiddorol cofio beth oedd yr ysgogiad ar gyfer y dechrau, sut y dechreuodd y cyfan.

Felly, mae'r hyn a ddigwyddodd o ganlyniad yn cael ei ddisgrifio'n fyr yn yr erthygl “Synthesis fel un o'r dulliau i wella perfformiad PostgreSQL'.

Mae'n debyg y bydd yn ddiddorol ail-greu'r gadwyn o ddigwyddiadau blaenorol.
Cadwodd yr hanes yr union ddyddiad cychwyn—2018-09-10 18:02:48.
Hefyd, yn y stori mae cais y dechreuodd y cyfan ohono:
Cais problemSELECT
p. "PARAMETER_ID" fel parameter_id,
pd. "PD_NAME" AS pd_name,
ll. "CUSTOMER_PARTNUMBER" FEL rhif rhan cwsmer,
w."LRM" FEL LRM,
w. "LOTID" AS lotid, Mr.
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS is_spec_limit,
w. "UPPER_SPEC_LIMIT" AS terfyn_spec_uwch,
p." TYPE_CALCUL" AS type_calcul,
s." SPENT_NAME" AS spent_name,
s." SPENT_DATE" AS dyddiad_wedi darfod,
dyfyniad (blwyddyn o "SPENT_DATE") UG blwyddyn,
dyfyniad (mis o "SPENT_DATE") fel mis,
s." REPORT_NAME" AS report_name,
p. " STPM_NAME " AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
RHAG wdataw,
gwario s,
pmtrp,
wedi gwario_pdsp,
pd pd
LLE s."SPENT_ID" = w."SPENT_ID"
AND p."PARAMETER_ID" = w. "PARAMETER_ID"
AND s."SPENT_ID" = sp. "SPENT_ID"
AND pd." PD_ID" = sb. "PD_ID"
AND s."SPENT_DATE" > = '2018-07-01' AC s."SPENT_DATE" <= '2018-09-30'
ac s."SPENT_DATE" = (SELECT MAX(s2. "SPENT_DATE")
O wariwyd s2,
wdata w2
LLE s2."SPENT_ID" = w2."SPENT_ID"
AND w2."LRM" = w."LRM");


Disgrifiad o'r broblem, safonol rhagweladwy - “Mae popeth yn ddrwg. Dywedwch wrthyf beth yw'r broblem."
Cofiais yn syth am jôc o amseroedd gyrru 3 modfedd a hanner:

Daw'r lamer i'r haciwr.
- Does dim byd yn gweithio i mi, dywedwch wrthyf ble mae'r broblem.
- Mewn DNA...

Ond, wrth gwrs, nid dyma'r ffordd i ddatrys digwyddiadau perfformiad. “Efallai nad ydym yn cael ein deall" (Gyda). Mae angen ei chyfrifo.
Wel, gadewch i ni gloddio. Efallai y bydd hynny'n cronni o ganlyniad.

Ydych chi'n cofio sut y dechreuodd y cyfan. Roedd popeth am y tro cyntaf ac eto

buddsoddiad wedi dechrau

Felly, yr hyn y gellir ei weld ar unwaith gyda'r llygad noeth, heb hyd yn oed droi at gymorth ESBONIAD.
1) Ni ddefnyddir JOINs. Mae hyn yn ddrwg, yn enwedig os yw nifer y cysylltiadau yn fwy nag un.
2) Ond yr hyn sydd hyd yn oed yn waeth - subquery cydberthyn, ar ben hynny, gyda agregu. Mae hyn yn ddrwg iawn.
Mae hyn yn ddrwg, wrth gwrs. Ond dim ond ar y naill law y mae hyn. Ar y llaw arall, mae hyn yn dda iawn, oherwydd mae'n amlwg bod gan y broblem ateb a gellir gwella'r cais.
Peidiwch â mynd at y ffortiwn (C).
Nid yw cynllun yr ymholiad mor gymhleth â hynny, ond yn eithaf dangosol:
Cynllun gweithreduYdych chi'n cofio sut y dechreuodd y cyfan. Roedd popeth am y tro cyntaf ac eto

Y mwyaf diddorol a defnyddiol, yn ôl yr arfer, ar ddechrau a diwedd.
Dolen nythu (cost=935.84..479763226.18 rhesi=3322 lled=135) (amser gwirioneddol=31.536..8220420.295 rhesi=8111656 dolenni=1)
Amser cynllunio: 3.807ms
Amser cyflawni: 8222351.640ms
Mae'r amser gweithredu yn fwy na 2 awr.

Ydych chi'n cofio sut y dechreuodd y cyfan. Roedd popeth am y tro cyntaf ac eto

Rhagdybiaethau ffug a gymerodd amser

Damcaniaeth 1- Mae'r optimizer yn anghywir, yn adeiladu'r cynllun anghywir.

I ddelweddu'r cynllun gweithredu, byddwn yn defnyddio'r wefan https://explain.depesz.com/. Fodd bynnag, nid oedd y wefan yn dangos unrhyw beth diddorol na defnyddiol. Ar yr olwg gyntaf a'r ail - dim byd a allai helpu mewn gwirionedd. Oni bai - Mae Sgan Llawn yn fach iawn. Cer ymlaen.

Rhagdybiaeth 2-Effaith ar y sylfaen o ochr y autovacuum, mae angen i chi gael gwared ar y breciau.

Ond, mae'r daemons autovacuum yn ymddwyn yn dda, nid oes prosesau hirsefydlog. Unrhyw lwyth difrifol - na. Angen chwilio am rywbeth arall.

Mae damcaniaeth 3-Ystadegau yn hen ffasiwn, mae angen i chi ailgyfrifo popeth yn hedfan

Unwaith eto, nid hynny. Mae'r ystadegau'n gyfredol. Sydd, o ystyried y diffyg problemau gyda autovacuum, nid yw'n syndod.

Dechrau gwneud y gorau

Yn sicr nid yw'r prif dabl 'wdata' yn fach, bron i 3 miliwn o gofnodion.
Ac ar y bwrdd hwn y mae Full Scan yn mynd.

Hash Cond: ((w."SPENT_ID" = s. "SPENT_ID") A ((SubPlan 1) = s. "SPENT_DATE"))
-> Sgan Seq ar wdata w (cost=0.00..574151.49 rhesi=26886249 lled=46) (amser gwirioneddol=0.005..8153.565 rhesi=26873950 dolenni=1)
Rydym yn gweithredu fel safon: “gadewch i ni wneud mynegai a phopeth yn hedfan”.
Wedi gwneud mynegai ar y maes "SPENT_ID"
Fel canlyniad:
Ymholiad cynllun gweithredu gan ddefnyddio mynegaiYdych chi'n cofio sut y dechreuodd y cyfan. Roedd popeth am y tro cyntaf ac eto

Wel, a oedd yn helpu?
Oedd: 8 222 351.640 ms (ychydig dros 2 awr)
Daeth yn: 6 985 431.575 ms (bron i 2 awr)
Yn gyffredinol, yr un afalau, golwg ochr.
Gadewch i ni gofio'r clasuron:
“Oes gennych chi'r un un, ond heb adenydd? Bydd yn ceisio".

Ydych chi'n cofio sut y dechreuodd y cyfan. Roedd popeth am y tro cyntaf ac eto

Mewn egwyddor, gellid galw hyn yn ganlyniad da, yn dda, nid yn dda, ond yn dderbyniol. O leiaf, darparwch adroddiad mawr i'r cwsmer yn disgrifio faint sydd wedi'i wneud a pham mae'r hyn a wneir yn dda.
Fodd bynnag, mae'r penderfyniad terfynol yn dal i fod ymhell i ffwrdd. Bell iawn.

Ac yn awr y peth mwyaf diddorol - rydym yn parhau i wneud y gorau, byddwn yn rhoi sglein ar yr ymholiad

Cam un - defnyddiwch JOIN

Ymholiad wedi'i ailysgrifennu, nawr yn edrych fel hyn (wel o leiaf yn harddach):
Ymholiad gan ddefnyddio JOINSELECT
p. "PARAMETER_ID" fel parameter_id,
pd. "PD_NAME" AS pd_name,
ll. "CUSTOMER_PARTNUMBER" FEL rhif rhan cwsmer,
w."LRM" FEL LRM,
w. "LOTID" AS lotid, Mr.
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS is_spec_limit,
w. "UPPER_SPEC_LIMIT" AS terfyn_spec_uwch,
p." TYPE_CALCUL" AS type_calcul,
s." SPENT_NAME" AS spent_name,
s." SPENT_DATE" AS dyddiad_wedi darfod,
dyfyniad (blwyddyn o "SPENT_DATE") UG blwyddyn,
dyfyniad (mis o "SPENT_DATE") fel mis,
s." REPORT_NAME" AS report_name,
p. " STPM_NAME " AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
O wdata w INNER JOIN gwariwyd s AR w."SPENT_ID" = s. "SPENT_ID"
YMuno MEWNOL pmtr p AR p."PARAMETER_ID" = w."PARAMETER_ID"
YMUNWCH INNER sp ON spent_pd sp ON s."SPENT_ID" = sp. "SPENT_ID"
YMUNWCH INNER pd pd AR pd."PD_ID" = sp. "PD_ID"
LLE
s."SPENT_DATE" > = '2018-07-01' AC s."SPENT_DATE" <= '2018-09-30'AND
s."SPENT_DATE" = (SELECT MAX(s2. "SPENT_DATE")
O wdata w2 INNER JOIN wedi gwario s2 AR w2."SPENT_ID" = s2. "SPENT_ID"
JOIN INNER wdata w
AR w2."LRM" = w."LRM" );
Amser cynllunio: 2.486ms
Amser cyflawni: 1223680.326ms

Felly dyma'r canlyniad cyntaf.
Oedd: 6 985 431.575 ms (bron i 2 awr).
Daeth yn: 1 223 680.326 ms (ychydig dros 20 munud).
Canlyniad da. Mewn egwyddor, unwaith eto, byddai'n bosibl stopio yno. Ond mor anniddorol, ni allwch stopio.
ER MWYN

Ydych chi'n cofio sut y dechreuodd y cyfan. Roedd popeth am y tro cyntaf ac eto

Cam Dau - Cael gwared ar y subquery cydberthynol

Testun cais wedi'i newid:
Dim subquery cydberthynolSELECT
p. "PARAMETER_ID" fel parameter_id,
pd. "PD_NAME" AS pd_name,
ll. "CUSTOMER_PARTNUMBER" FEL rhif rhan cwsmer,
w."LRM" FEL LRM,
w. "LOTID" AS lotid, Mr.
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS is_spec_limit,
w. "UPPER_SPEC_LIMIT" AS terfyn_spec_uwch,
p." TYPE_CALCUL" AS type_calcul,
s." SPENT_NAME" AS spent_name,
s." SPENT_DATE" AS dyddiad_wedi darfod,
dyfyniad (blwyddyn o "SPENT_DATE") UG blwyddyn,
dyfyniad (mis o "SPENT_DATE") fel mis,
s." REPORT_NAME" AS report_name,
p. " STPM_NAME " AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
O wdata w INNER JOIN gwariwyd s AR s."SPENT_ID" = w."SPENT_ID"
YMuno MEWNOL pmtr p AR p."PARAMETER_ID" = w."PARAMETER_ID"
YMUNWCH INNER sp ON spent_pd sp ON s."SPENT_ID" = sp. "SPENT_ID"
YMUNWCH INNER pd pd AR pd."PD_ID" = sp. "PD_ID"
YMuno INNER (SELECT w2."LRM", MAX(s2."SPENT_DATE")
O wariwyd s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2. "SPENT_ID"
GRWP GAN w2.LRM
) md ar w." LRM " = md. "LRM"
LLE
s."SPENT_DATE" > = '2018-07-01' AC s."SPENT_DATE" <= '2018-09-30';
Amser cynllunio: 2.291ms
Amser cyflawni: 165021.870ms

Oedd: 1 223 680.326 ms (ychydig dros 20 munud).
Daeth yn: 165 021.870 ms (ychydig dros 2 funud).
Mae hyn eisoes yn eithaf da.
Fodd bynnag, fel y dywed y Saeson,Ond, mae yna bob amser ond" . Dylai canlyniad rhy dda godi amheuaeth yn awtomatig. Mae rhywbeth o'i le yma.

Mae'r ddamcaniaeth ynghylch cywiro'r ymholiad er mwyn cael gwared ar y subquery cydberthynol yn gywir. Ond mae angen tweaking bach i gael y canlyniad terfynol yn gywir.
O ganlyniad, y canlyniad canolradd cyntaf:
Ymholiad wedi'i olygu heb subquery cydberthynolSELECT
p. "PARAMETER_ID" fel parameter_id,
pd. "PD_NAME" AS pd_name,
ll. "CUSTOMER_PARTNUMBER" FEL rhif rhan cwsmer,
w."LRM" FEL LRM,
w. "LOTID" AS lotid, Mr.
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS is_spec_limit,
w. "UPPER_SPEC_LIMIT" AS terfyn_spec_uwch,
p." TYPE_CALCUL" AS type_calcul,
s." SPENT_NAME" AS spent_name,
s." SPENT_DATE" AS dyddiad_wedi darfod,
dyfyniad (blwyddyn o s. "SPENT_DATE") UG blwyddyn,
dyfyniad (mis o s. "SPENT_DATE") fel mis,
s." REPORT_NAME" AS report_name,
p. " STPM_NAME " AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
O wdata w INNER JOIN gwariwyd s AR s."SPENT_ID" = w."SPENT_ID"
YMuno MEWNOL pmtr p AR p."PARAMETER_ID" = w."PARAMETER_ID"
YMUNWCH INNER sp ON spent_pd sp ON s."SPENT_ID" = sp. "SPENT_ID"
YMUNWCH INNER pd pd AR pd."PD_ID" = sp. "PD_ID"
INNER JOIN ( SELECT w2."LRM", MAX(s2."SPENT_DATE") FEL "SPENT_DATE"
O wariwyd s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2. "SPENT_ID"
GRWP GAN w2.LRM
) md AR md."SPENT_DATE" = s."SPENT_DATE" A md."LRM" = w."LRM"
LLE
s."SPENT_DATE" > = '2018-07-01' AC s."SPENT_DATE" <= '2018-09-30';
Amser cynllunio: 3.192ms
Amser cyflawni: 208014.134ms

Felly, yr hyn sydd gennym o ganlyniad yw'r canlyniad derbyniol cyntaf, nad oes gennym gywilydd ei ddangos i'r cwsmer:
Wedi dechrau gyda: 8 222 351.640 ms (mwy na 2 awr)
Cyflawnwyd: 1 ms (ychydig dros 223 munud).
Canlyniad (canolradd): 208 014.134 ms (ychydig dros 3 munud).

Canlyniad ardderchog.

Ydych chi'n cofio sut y dechreuodd y cyfan. Roedd popeth am y tro cyntaf ac eto

Cyfanswm

Gallai hyn fod wedi dod i ben.
OND…
Daw archwaeth gyda bwyta. Bydd y ffordd yn cael ei meistroli trwy gerdded. Mae unrhyw ganlyniad yn ganolradd. Wedi stopio marw. Etc.
Gadewch i ni barhau â'r optimeiddio.
Syniad gwych. Yn enwedig o ystyried nad oedd y cwsmer hyd yn oed yn ei erbyn. A hyd yn oed yn gryf - ar gyfer.

Felly, mae'n bryd ailgynllunio'r gronfa ddata. Ni ellir optimeiddio strwythur yr ymholiad ei hun mwyach (er, fel y digwyddodd yn ddiweddarach, mae opsiwn i bopeth hedfan mewn gwirionedd). Ond nawr er mwyn optimeiddio a datblygu dyluniad y gronfa ddata, mae hwn eisoes yn syniad addawol iawn. Ac yn bwysicaf oll diddorol. Eto, cofiwch ieuenctid. Wedi'r cyfan, ni ddes i'n DBA ar unwaith, fe wnes i dyfu allan o raglenwyr (sylfaenol, cyfosodwr, si, si ddwywaith plws, oracle, plsql). Pwnc diddorol, wrth gwrs, ar gyfer cofiannau ar wahân ;-).
Fodd bynnag, gadewch i ni beidio crwydro.

Felly,

Ydych chi'n cofio sut y dechreuodd y cyfan. Roedd popeth am y tro cyntaf ac eto

Ac efallai y bydd adranu yn ein helpu ni?
Spoiler - "Ie, fe helpodd, ac wrth optimeiddio perfformiad, gan gynnwys."

Ond mae honno'n stori hollol wahanol...

I'w barhau…

Ffynhonnell: hab.com

Ychwanegu sylw