O tom, ako sme museli optimalizovať PostgreSQL dotaz a čo z toho všetkého vzišlo.
Prečo si musel? Áno, pretože predchádzajúce 4 roky všetko fungovalo ticho, pokojne, ako tikajúce hodiny.
Ako epigraf.
Na základe skutočných udalostí.
Všetky mená boli zmenené, náhody sú náhodné.
Keď dosiahnete určitý výsledok, vždy je zaujímavé pripomenúť si, čo bolo impulzom na začiatok, kde to všetko začalo.
Čo sa stalo v dôsledku toho, je stručne popísané v článku „".
Pravdepodobne bude zaujímavé obnoviť reťazec predchádzajúcich udalostí.
História uložila presný dátum začiatku - 2018-09-10 18:02:48.
V príbehu je tiež požiadavka, od ktorej to všetko začalo:
Problémová žiadosťSELECT
p.„PARAMETER_ID“ ako parameter_id,
pd."PD_NAME" AS pd_name,
pd "CUSTOMER_PARTNUMBER" AKO customer_partnumber,
w. "LRM" AKO LRM,
w. "LOTID" AKO lotid,
w „RTD_VALUE“ AKO RTD_value,
w „LOWER_SPEC_LIMIT“ AKO dolný_špecifikačný_limit,
w „UPPER_SPEC_LIMIT“ AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s „SPENT_DATE“ AKO dátum_utratenia,
extrakt(rok od "SPENT_DATE") AS rok,
výpis (mesiac od „SPENT_DATE“) ako mesiac,
s."REPORT_NAME" AS názov_prehľadu,
p."STPM_NAME" AS stpm_name,
p.„CUSTOMERPARAM_NAME“ AKO customerparam_name
OD wdata w,
strávil s,
pmtr p,
strávil_pd sp,
pd pd
WHERE s.„SPENT_ID“ = w„SPENT_ID“
A p."PARAMETER_ID" = w."PARAMETER_ID"
A s.„SPENT_ID“ = sp.„SPENT_ID“
AND pd."PD_ID" = sp."PD_ID"
AND s.“SPENT_DATE” >= '2018-07-01' AND s.“SPENT_DATE” <= '2018-09-30'
a s.“SPENT_DATE“ = (VYBERTE MAX (s2.„SPENT_DATE“)
OD vynaložených 2,
wdata w2
WHERE s2.„SPENT_ID“ = w2.„SPENT_ID“
AND w2.“LRM“ = w.“LRM“);
Opis problému je predvídateľne štandardný – „Všetko je zlé. Povedz mi, v čom je problém."
Hneď som si spomenul na anekdotu z čias 3 a pol palcových diskov:
Lamer prichádza k hackerovi.
-Nič mi nefunguje, povedz mi, kde je problém.
V DNA...
Ale samozrejme, toto nie je spôsob, ako vyriešiť incidenty s výkonom. “Možno nám nerozumejú“ (S). Musíme na to prísť.
Nuž, poďme kopať. Možno sa v dôsledku toho niečo nahromadí.
Začalo sa vyšetrovanie
Takže to, čo je možné okamžite vidieť voľným okom, bez toho, aby ste sa uchýlili k VYSVETLENIE.
1) JOINy sa nepoužívajú. To je zlé, najmä ak je počet spojení viac ako jeden.
2) Čo je však ešte horšie, sú korelované poddotazy, navyše s agregáciou. Toto je veľmi zlé.
To je samozrejme zlé. Ale to je len na jednej strane. Na druhej strane je to veľmi dobré, pretože problém má jednoznačne riešenie a požiadavku, ktorú možno zlepšiť.
Nechoďte k veštcovi (C).
Plán dotazov nie je taký zložitý, ale je celkom orientačný:
Realizačný plán
To najzaujímavejšie a najužitočnejšie je, ako inak, na začiatku a na konci.
Vnorená slučka (cena=935.84...479763226.18 riadkov=3322 šírka=135) (skutočný čas=31.536..8220420.295 riadkov=8111656 slučiek=1)
Plánovací čas: 3.807 ms
Čas vykonania: 8222351.640 ms
Čas dokončenia je viac ako 2 hodiny.
Falošné hypotézy, ktoré si vyžadovali čas
Hypotéza 1 – Optimalizátor urobí chybu a zostaví nesprávny plán.
Na vizualizáciu plánu realizácie použijeme stránku . Stránka však neukázala nič zaujímavé ani užitočné. Na prvý a druhý pohľad neexistuje nič, čo by skutočne mohlo pomôcť. Je možné, že úplné skenovanie je minimálne. Pokračuj.
Hypotéza 2-Náraz na základňu zo strany autovákua, treba sa zbaviť bŕzd.
Ale autovacuum démoni sa správajú dobre, neexistujú žiadne dlhotrvajúce procesy. Žiadne vážne zaťaženie. Musíme hľadať niečo iné.
Hypotéza 3 - Štatistiky sú zastarané, všetko treba prepočítať
To zase nie. Štatistiky sú aktuálne. Čo vzhľadom na nedostatok problémov s autovakuom nie je prekvapujúce.
Začnime s optimalizáciou
Hlavná tabuľka 'wdata' určite nie je malá, takmer 3 milióny záznamov.
A práve túto tabuľku nasleduje Full Scan.
Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") AND ((podplán 1) = s."SPENT_DATE"))
-> Seq Scan na wdata w (cena=0.00..574151.49 riadkov=26886249 šírka=46) (skutočný čas=0.005..8153.565 riadkov=26873950 slučiek=1)
Robíme štandardnú vec: „poď, urobíme index a všetko bude lietať“.
V poli „SPENT_ID“ bol vytvorený index
V dôsledku toho:
Plán vykonávania dotazu pomocou indexu
No pomohlo to?
To bolo: 8 222 351.640 ms (trochu viac ako 2 hodiny)
Sa stal: 6 985 431.575 ms (takmer 2 hodiny)
Vo všeobecnosti rovnaké jablká, bočný pohľad.
Pripomeňme si klasiku:
„Máš ten istý, ale bez krídel? Bude hľadať“.
V zásade by sa to dalo nazvať dobrým výsledkom, dobre, nie dobrým, ale prijateľným. Prinajmenšom poskytnite zákazníkovi veľkú správu s popisom toho, koľko sa urobilo a prečo to, čo sa urobilo, bolo dobré.
Konečné rozhodnutie je však ešte ďaleko. Veľmi ďaleko.
A teraz to najzaujímavejšie – pokračujeme v optimalizácii, požiadavku vyleštíme
Prvý krok - Použite JOIN
Prepísaná žiadosť teraz vyzerá takto (no aspoň krajšie):
Dopytujte pomocou JOINSELECT
p.„PARAMETER_ID“ ako parameter_id,
pd."PD_NAME" AS pd_name,
pd "CUSTOMER_PARTNUMBER" AKO customer_partnumber,
w. "LRM" AKO LRM,
w. "LOTID" AKO lotid,
w „RTD_VALUE“ AKO RTD_value,
w „LOWER_SPEC_LIMIT“ AKO dolný_špecifikačný_limit,
w „UPPER_SPEC_LIMIT“ AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s „SPENT_DATE“ AKO dátum_utratenia,
extrakt(rok od "SPENT_DATE") AS rok,
výpis (mesiac od „SPENT_DATE“) ako mesiac,
s."REPORT_NAME" AS názov_prehľadu,
p."STPM_NAME" AS stpm_name,
p.„CUSTOMERPARAM_NAME“ AKO customerparam_name
FROM wdata w INNER JOIN strávil s na w.“SPENT_ID”=s.”„SPENT_ID“
INNER JOIN pmtr p ON p.“PARAMETER_ID“ = w.“PARAMETER_ID“
VNÚTORNÉ SPOJENIE strávil_pd sp ON s.“SPENT_ID“ = sp.“SPENT_ID“
INNER JOIN pd pd ON pd.“PD_ID“ = sp.“PD_ID“
KDE
s.“SPENT_DATE” >= '2018-07-01' AND s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE“ = (VYBERTE MAX (s2.„SPENT_DATE“)
FROM wdata w2 INNER JOIN strávil s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”
VNÚTORNÉ SPOJENIE wdata w
ON w2.“LRM“ = w.“LRM“ );
Plánovací čas: 2.486 ms
Čas vykonania: 1223680.326 ms
Takže prvý výsledok.
To bolo: 6 985 431.575 ms (takmer 2 hodiny).
Sa stal: 1 223 680.326 ms (niečo viac ako 20 minút).
Dobrý výsledok. V zásade by sme sa tam opäť mohli zastaviť. Ale je to také nezaujímavé, že nemôžete prestať.
PRE
Krok dva – zbavte sa korelovaného poddotazu
Zmenený text žiadosti:
Bez korelovaného poddotazuSELECT
p.„PARAMETER_ID“ ako parameter_id,
pd."PD_NAME" AS pd_name,
pd "CUSTOMER_PARTNUMBER" AKO customer_partnumber,
w. "LRM" AKO LRM,
w. "LOTID" AKO lotid,
w „RTD_VALUE“ AKO RTD_value,
w „LOWER_SPEC_LIMIT“ AKO dolný_špecifikačný_limit,
w „UPPER_SPEC_LIMIT“ AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s „SPENT_DATE“ AKO dátum_utratenia,
extrakt(rok od "SPENT_DATE") AS rok,
výpis (mesiac od „SPENT_DATE“) ako mesiac,
s."REPORT_NAME" AS názov_prehľadu,
p."STPM_NAME" AS stpm_name,
p.„CUSTOMERPARAM_NAME“ AKO customerparam_name
FROM wdata w INNER JOIN strávil s „SPENT_ID“ = w.“SPENT_ID“
INNER JOIN pmtr p ON p.“PARAMETER_ID“ = w.“PARAMETER_ID“
VNÚTORNÉ SPOJENIE strávil_pd sp ON s.“SPENT_ID“ = sp.“SPENT_ID“
INNER JOIN pd pd ON pd.“PD_ID“ = sp.“PD_ID“
VNÚTORNÉ PRIPOJENIE (SELECT w2.„LRM“, MAX(s2.„SPENT_DATE“)
FROM strávil s2 VNÚTORNÉ PRIPOJENIE wdata w2 ON s2.“SPENT_ID“ = w2.“SPENT_ID“
GROUP BY w2 „LRM“
) md na w.„LRM“ = md.„LRM“
KDE
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
Plánovací čas: 2.291 ms
Čas vykonania: 165021.870 ms
To bolo: 1 223 680.326 ms (niečo viac ako 20 minút).
Sa stal: 165 021.870 ms (niečo viac ako 2 minúty).
Toto je už celkom dobré.
Ako však hovoria Briti „Ale, vždy existuje nejaké ale" Príliš dobrý výsledok by mal automaticky vzbudiť podozrenie. Niečo tu nie je v poriadku.
Hypotéza o oprave dotazu s cieľom zbaviť sa korelovaného poddotazu je správna. Ale musíte to trochu upraviť, aby bol konečný výsledok správny.
Výsledkom je, že prvý medzivýsledok:
Upravený dotaz bez korelovaného poddotazuSELECT
p.„PARAMETER_ID“ ako parameter_id,
pd."PD_NAME" AS pd_name,
pd "CUSTOMER_PARTNUMBER" AKO customer_partnumber,
w. "LRM" AKO LRM,
w. "LOTID" AKO lotid,
w „RTD_VALUE“ AKO RTD_value,
w „LOWER_SPEC_LIMIT“ AKO dolný_špecifikačný_limit,
w „UPPER_SPEC_LIMIT“ AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s „SPENT_DATE“ AKO dátum_utratenia,
extrakt(rok od s.“SPENT_DATE“) AS rok,
výpis (mesiac od s.„SPENT_DATE“) ako mesiac,
s."REPORT_NAME" AS názov_prehľadu,
p."STPM_NAME" AS stpm_name,
p.„CUSTOMERPARAM_NAME“ AKO customerparam_name
FROM wdata w INNER JOIN strávil s „SPENT_ID“ = w.“SPENT_ID“
INNER JOIN pmtr p ON p.“PARAMETER_ID“ = w.“PARAMETER_ID“
VNÚTORNÉ SPOJENIE strávil_pd sp ON s.“SPENT_ID“ = sp.“SPENT_ID“
INNER JOIN pd pd ON pd.“PD_ID“ = sp.“PD_ID“
VNÚTORNÉ PRIPOJENIE ( SELECT w2.„LRM“, MAX(s2.„SPENT_DATE“) AKO „SPENT_DATE“
FROM strávil s2 VNÚTORNÉ PRIPOJENIE wdata w2 ON s2.“SPENT_ID“ = w2.“SPENT_ID“
GROUP BY w2 „LRM“
) md ON md.“SPENT_DATE“ = s.“SPENT_DATE“ A md.“LRM“ = w.“LRM“
KDE
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
Plánovací čas: 3.192 ms
Čas vykonania: 208014.134 ms
Výsledkom je teda prvý prijateľný výsledok, ktorý nie je hanba ukázať zákazníkovi:
Začiatok: 8 222 351.640 ms (viac ako 2 hodiny)
Podarilo sa nám dosiahnuť: 1 223 680.326 ms (o niečo viac ako 20 minút).
Výsledok (priebežný): 208 014.134 ms (niečo viac ako 3 minúty).
Vyborny vysledok.
Celkový
Mohli sme sa tam zastaviť.
ALE…
S jedlom prichádza chuť do jedla. Ten, kto kráča, ovládne cestu. Akýkoľvek výsledok je stredný. Zastavil a zomrel. Atď.
Pokračujme v optimalizácii.
Výborný nápad. Najmä vzhľadom na to, že zákazníkovi to ani nevadilo. A dokonca silno za to.
Je teda čas na redizajn databázy. Samotnú štruktúru dotazu už nie je možné optimalizovať (hoci, ako sa neskôr ukázalo, existuje možnosť, ako zabezpečiť, aby všetko skutočne zlyhalo). Ale začať s optimalizáciou a vývojom návrhu databázy je už teraz veľmi sľubný nápad. A hlavne zaujímavé. Opäť si spomeňte na svoju mladosť. Nestal som sa okamžite DBA, vyrástol som ako programátor (BASIC, assembler, C, double-plus C, Oracle, plsql). Zaujímavá téma, samozrejme, na samostatnú spomienku ;-).
Nenechajme sa však rozptyľovať.
Takže,
Alebo nám možno pomôže rozdelenie?
Spoiler – „Áno, pomohlo to aj pri optimalizácii výkonu.“
Ale to je úplne iný príbeh...
Pokračovanie nabudúce…
Zdroj: hab.com