Pri kiel ni devis optimumigi la PostgreSQL-demandon kaj kio eliris el ĉio.
Kial vi devis? Jes, ĉar dum la antaŭaj 4 jaroj ĉio funkciis trankvile, trankvile, kiel horloĝo.
Kiel surskribo.
Surbaze de realaj eventoj.
Ĉiuj nomoj estas ŝanĝitaj, koincidoj estas hazardaj.
Kiam vi atingas certan rezulton, ĉiam estas interese memori, kio estis la impeto por la komenco, kie ĉio komenciĝis.
Do, kio okazis kiel rezulto estas mallonge priskribita en la artikolo "".
Verŝajne estos interese rekrei la ĉenon de antaŭaj eventoj.
La historio konservis la ĝustan komencan daton - 2018-09-10 18:02:48.
Ankaŭ en la rakonto estas peto de kiu ĉio komenciĝis:
Problema petoELEKTU
p.“PARAMETER_ID” kiel parametro_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" KIEL LRM,
w. "LOTID" KIEL lotido,
w.“RTD_VALUE” AS RTD_valoro,
w.“LOWER_SPEC_LIMIT” AS malsupera_spec_limo,
w.“UPPER_SPEC_LIMIT” AS supra_spec_limo,
p."TYPE_CALCUL" AS tipo_kalkulo,
s."SPENT_NAME" AS elspezita_nomo,
s.“SPENT_DATE” AS elspezita_dato,
ekstrakto(jaro el "SPENT_DATE") AS jaro,
ekstrakto(monato de "SPENT_DATE") kiel monato,
s."REPORT_NAME" AS raport_nomo,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
DE wdata w,
elspezis s,
pmtr p,
spent_pd sp,
pd pd
WHERE s.“SPENT_ID” = w.“SPENT_ID”
AND p."PARAMETER_ID" = w "PARAMETER_ID"
AND s.“SPENT_ID” = sp.“SPENT_ID”
AND pd."PD_ID" = sp."PD_ID"
KAJ s.“SPENT_DATE” >= ‘2018-07-01’ KAJ s.“SPENT_DATE” <= ‘2018-09-30’
kaj s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
DE elspezita s2,
wdata w2
WHERE s2.“SPENT_ID” = w2.“SPENT_ID”
KAJ w2.“LRM” = w.“LRM”);
Priskribo de la problemo estas antaŭvideble norma - "Ĉio estas malbona. Diru al mi, kio estas la problemo."
Mi tuj rememoris anekdoton el la tempoj de 3 kaj duoncola veturadoj:
La lamanto venas al la retpirato.
-Nenio funkcias por mi, diru al mi kie estas la problemo.
- En DNA...
Sed kompreneble, ĉi tio ne estas la maniero solvi rendimentokazaĵojn. “Ili eble ne komprenas nin" (Kun). Ni devas eltrovi ĝin.
Nu, ni fosu. Eble io akumuliĝos kiel rezulto.
Esploro komenciĝis
Do, kion oni povas tuj vidi per la nuda okulo, eĉ sen recurri al KLARIGI.
1) JOIN-oj ne estas uzataj. Ĉi tio estas malbona, precipe se la nombro da konektoj estas pli ol unu.
2) Sed eĉ pli malbona estas korelaciitaj subdemandoj, krome, kun agregado. Ĉi tio estas tre malbona.
Ĉi tio estas malbona kompreneble. Sed ĉi tio estas nur unuflanke. Aliflanke, tio estas tre bona, ĉar la problemo klare havas solvon kaj peton plibonigeblan.
Ne iru al aŭguristo (C).
La demandplano ne estas tiom komplika, sed ĝi estas sufiĉe indika:
Plano de Ekzekuto
La plej interesa kaj utila, kiel kutime, estas ĉe la komenco kaj fino.
Nestita Buklo (kosto=935.84..479763226.18 vicoj=3322 larĝo=135) (fakta tempo=31.536..8220420.295 vicoj=8111656 bukloj=1)
Plana tempo: 3.807 ms
Tempo de ekzekuto: 8222351.640 ms
Kompleta tempo estas pli ol 2 horoj.
Falsaj hipotezoj, kiuj prenis tempon
Hipotezo 1 - La optimumigilo faras eraron kaj konstruas la malĝustan planon.
Por bildigi la ekzekutplanon, ni uzos la retejon . Tamen la retejo montris nenion interesan aŭ utilan. Unue kaj dua rigardo, estas nenio, kio vere povus helpi. Ĉu eblas, ke Plena Skanado estas minimuma. Antaŭeniri.
Hipotezo 2-Efiko sur la bazo de la aŭtomalplena flanko, vi devas forigi la bremsojn.
Sed la aŭtovakuaj demonoj bone kondutas, ne ekzistas longdaŭraj procezoj. Neniu grava ŝarĝo. Ni devas serĉi ion alian.
Hipotezo 3 - Statistikoj estas malmodernaj, ĉio necesas rekalkuli
Denove, ne tio. La statistikoj estas ĝisdatigitaj. Kio, pro la manko de problemoj kun aŭtomata vakuo, ne estas surpriza.
Ni komencu optimumigi
La ĉefa tablo 'wdata' certe ne estas malgranda, preskaŭ 3 milionoj da registroj.
Kaj estas ĉi tiu tablo, kiun sekvas Plena Skanado.
Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") KAJ ((SubPlano 1) = s."SPENT_DATE"))
-> Seq Scan sur wdata w (kosto=0.00..574151.49 vicoj=26886249 larĝo=46) (fakta tempo=0.005..8153.565 vicoj=26873950 bukloj=1)
Ni faras la norman aferon: "venu, ni faru indekson kaj ĉio flugos."
Kreis indekson sur la kampo "SPENT_ID".
Tial:
Demandu ekzekutplanon uzante indekson
Nu, ĉu ĝi helpis?
Estis: 8 222 351.640 ms (iom pli ol 2 horoj)
Ĝi fariĝis: 6 985 431.575 ms (preskaŭ 2 horoj)
Ĝenerale, la samaj pomoj, flanka vido.
Ni memoru la klasikaĵojn:
“Ĉu vi havas la saman, sed sen flugiloj? serĉos".
Principe oni povus nomi tion bona rezulto, nu, ne bona, sed akceptebla. Almenaŭ, provizu grandan raporton al la kliento priskribante kiom multe estis farita kaj kial tio, kio estis farita, estis bona.
Sed tamen, la fina decido estas ankoraŭ malproksime. Tre malproksima.
Kaj nun la plej interesa afero - ni daŭre optimumigas, ni poluros la peton
Paŝo Unu - Uzu JOIN
La reverkita peto nun aspektas tiel (nu almenaŭ pli bela):
Demandu uzante JOINELEKTU
p.“PARAMETER_ID” kiel parametro_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" KIEL LRM,
w. "LOTID" KIEL lotido,
w.“RTD_VALUE” AS RTD_valoro,
w.“LOWER_SPEC_LIMIT” AS malsupera_spec_limo,
w.“UPPER_SPEC_LIMIT” AS supra_spec_limo,
p."TYPE_CALCUL" AS tipo_kalkulo,
s."SPENT_NAME" AS elspezita_nomo,
s.“SPENT_DATE” AS elspezita_dato,
ekstrakto(jaro el "SPENT_DATE") AS jaro,
ekstrakto(monato de "SPENT_DATE") kiel monato,
s."REPORT_NAME" AS raport_nomo,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
DE wdata w INNER JOIN elspezita 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”
WHERE
s.“SPENT_DATE” >= ‘2018-07-01’ KAJ s.“SPENT_DATE” <= ‘2018-09-30’AND
s.“SPENT_DATE” = (ELECTU MAX(s2.“SPENT_DATE”)
DE wdata w2 INNER JOIN elspezis s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
ON w2.“LRM” = w.“LRM” );
Plana tempo: 2.486 ms
Tempo de ekzekuto: 1223680.326 ms
Do, la unua rezulto.
Estis: 6 ms (preskaŭ 985 horoj).
Ĝi fariĝis: 1 223 680.326 ms (iom pli ol 20 minutoj).
Bona rezulto. Principe, denove, ni povus halti tie. Sed ĝi estas tiel neinteresa, vi ne povas ĉesi.
POR
Paŝo du - forigu la korelacian subdemandon
Ŝanĝita peta teksto:
Sen korelacia subdemandoELEKTU
p.“PARAMETER_ID” kiel parametro_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" KIEL LRM,
w. "LOTID" KIEL lotido,
w.“RTD_VALUE” AS RTD_valoro,
w.“LOWER_SPEC_LIMIT” AS malsupera_spec_limo,
w.“UPPER_SPEC_LIMIT” AS supra_spec_limo,
p."TYPE_CALCUL" AS tipo_kalkulo,
s."SPENT_NAME" AS elspezita_nomo,
s.“SPENT_DATE” AS elspezita_dato,
ekstrakto(jaro el "SPENT_DATE") AS jaro,
ekstrakto(monato de "SPENT_DATE") kiel monato,
s."REPORT_NAME" AS raport_nomo,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
DE wdata w INNER JOIN elspezita 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”
INTERNA ALIGO (SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”)
DE elspezita s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GRUPO DE w2.“LRM”
) md on w.“LRM” = md.“LRM”
WHERE
s.“SPENT_DATE” >= ‘2018-07-01’ KAJ s.“SPENT_DATE” <= ‘2018-09-30’;
Plana tempo: 2.291 ms
Tempo de ekzekuto: 165021.870 ms
Estis: 1 223 680.326 ms (iom pli ol 20 minutoj).
Ĝi fariĝis: 165 021.870 ms (iom pli ol 2 minutoj).
Ĉi tio jam estas sufiĉe bona.
Tamen, kiel la britoj diras "Sed, ĉiam estas sed" Rezulto tro bona devus aŭtomate veki suspekton. Io estas malĝusta ĉi tie.
La hipotezo pri korektado de la demando por forigi la korelacian subdemandon estas ĝusta. Sed vi devas iom ĝustigi ĝin por ke la fina rezulto estu ĝusta.
Kiel rezulto, la unua meza rezulto:
Redaktita demando sen korelacia subdemandoELEKTU
p.“PARAMETER_ID” kiel parametro_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" KIEL LRM,
w. "LOTID" KIEL lotido,
w.“RTD_VALUE” AS RTD_valoro,
w.“LOWER_SPEC_LIMIT” AS malsupera_spec_limo,
w.“UPPER_SPEC_LIMIT” AS supra_spec_limo,
p."TYPE_CALCUL" AS tipo_kalkulo,
s."SPENT_NAME" AS elspezita_nomo,
s.“SPENT_DATE” AS elspezita_dato,
ekstrakto(jaro el s.“SPENT_DATE”) AS jaro,
ekstrakto(monato de s.“SPENT_DATE”) kiel monato,
s."REPORT_NAME" AS raport_nomo,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
DE wdata w INNER JOIN elspezita 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”
INTERNA ALIGO ( ELEKTU w2.“LRM”, MAX(s2.“SPENT_DATE”) AS “SPENT_DATE”
DE elspezita s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GRUPO DE w2.“LRM”
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” KAJ md.“LRM” = w.“LRM”
WHERE
s.“SPENT_DATE” >= ‘2018-07-01’ KAJ s.“SPENT_DATE” <= ‘2018-09-30’;
Plana tempo: 3.192 ms
Tempo de ekzekuto: 208014.134 ms
Do, kion ni finas, estas la unua akceptebla rezulto, kio ne estas domaĝe montri al la kliento:
Komencis per: 8 222 351.640 ms (pli ol 2 horoj)
Ni sukcesis atingi: 1 ms (iom pli ol 223 minutoj).
Rezulto (provizora): 208 014.134 ms (iom pli ol 3 minutoj).
Bonega rezulto.
La rezulto
Ni povus halti tie.
SED…
Apetito venas kun manĝado. Kiu marŝas, tiu regos la vojon. Ajna rezulto estas meza. Haltis kaj mortis. Ktp.
Ni daŭrigu optimumigon.
Bonega ideo. Precipe konsiderante ke la kliento eĉ ne ĝenis. Kaj eĉ forte por ĝi.
Do, estas tempo por restrukturado de datumbazo. La demandstrukturo mem ne plu povas esti optimumigita (kvankam, kiel ĝi montriĝis poste, ekzistas eblo por certigi, ke ĉio efektive malsukcesas). Sed komenci optimumigi kaj disvolvi la datumbazan dezajnon jam estas tre promesplena ideo. Kaj plej grave interesa. Denove, memoru vian junecon. Mi ne tuj fariĝis DBA, mi kreskis kiel programisto (BASIC, assembler, C, double-plus C, Oracle, plsql). Interesa temo, kompreneble, por aparta memoraĵo ;-).
Tamen, ni ne distriĝu.
Kaj tiel,
Aŭ eble dispartigo helpos nin?
Spoiler - "Jes, ĝi helpis, inkluzive en optimumigo de rendimento."
Sed tio estas tute alia historio...
Daŭrigota…
fonto: www.habr.com