O tome kako smo morali optimizirati PostgreSQL upit i što je iz svega ispalo.
Zašto si morao? Da, jer prethodne 4 godine sve je radilo tiho, mirno, kao sat koji otkucava.
Kao epigraf.
Temeljeno na stvarnim događajima.
Sva imena su promijenjena, slučajnosti su slučajne.
Kad postignete neki rezultat, uvijek je zanimljivo prisjetiti se što je bio poticaj za početak, gdje je sve počelo.
Dakle, ono što se dogodilo kao rezultat ukratko je opisano u članku "".
Vjerojatno će biti zanimljivo ponovno stvoriti lanac prethodnih događaja.
Povijest je sačuvala točan datum početka - 2018-09-10 18:02:48.
Također, u priči postoji zahtjev od kojeg je sve počelo:
Zahtjev za problemSELECT
str.“PARAMETER_ID” kao parameter_id,
pd."PD_NAME" KAO pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" KAO LRM,
w. "LOTID" KAO lotid,
w.“RTD_VALUE” KAO RTD_vrijednost,
w.“LOWER_SPEC_LIMIT” KAO donji_spec_limit,
w.“UPPER_SPEC_LIMIT” KAO gornje_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" KAO potrošeno_ime,
s.“SPENT_DATE” KAO potrošen_datum,
ekstrakt(godina od "SPENT_DATE") KAO godina,
ekstrakt (mjesec od "SPENT_DATE") kao mjesec,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" KAO stpm_name,
str.“CUSTOMERPARAM_NAME” KAO customerparam_name
OD wdata w,
potrošeno s,
pmtr p,
potrošeno_pd sp,
pd pd
WHERE s.“SPENT_ID” = w.“SPENT_ID”
I p."PARAMETER_ID" = w."PARAMETER_ID"
I s.“SPENT_ID” = sp.“SPENT_ID”
I pd."PD_ID" = sp."PD_ID"
I s.“SPENT_DATE” >= '2018-07-01' I s.“SPENT_DATE” <= '2018-09-30'
i s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
OD potrošeno s2,
wdata w2
WHERE s2.“SPENT_ID” = w2.“SPENT_ID”
I w2.“LRM” = w.“LRM”);
Opis problema je očekivano standardan - „Sve je loše. Reci mi u čemu je problem.”
Odmah sam se sjetio jedne anegdote iz vremena pogona od 3 i pol inča:
Lamer dolazi do hakera.
-Meni ništa ne ide, reci mi gdje je problem.
- U DNK...
Ali naravno, ovo nije način za rješavanje incidenata performansi. “Možda nas ne razumiju" (sa). Moramo to shvatiti.
Pa, idemo kopati. Možda će se zbog toga nešto nakupiti.
Istraga je započela
Dakle, ono što se odmah može vidjeti golim okom, čak i bez pribjegavanja OBJAŠNJENJU.
1) JOIN-ovi se ne koriste. To je loše, pogotovo ako je broj veza veći od jedne.
2) Ali ono što je još gore je korelacija podupita, štoviše, s agregacijom. Ovo je jako loše.
Ovo je naravno loše. Ali to je samo s jedne strane. S druge strane, to je jako dobro, jer problem jasno ima rješenje i zahtjev koji se može poboljšati.
Ne idi gatari (C).
Plan upita nije toliko kompliciran, ali je prilično indikativan:
Plan izvedbe
Najzanimljivije i najkorisnije, kao i obično, je na početku i na kraju.
Ugniježđena petlja (cijena=935.84..479763226.18 redaka=3322 širina=135) (stvarno vrijeme=31.536..8220420.295 redaka=8111656 petlji=1)
Vrijeme planiranja: 3.807 ms
Vrijeme izvršenja: 8222351.640 ms
Vrijeme završetka je više od 2 sata.
Lažne hipoteze za koje je trebalo vremena
Hipoteza 1 - Optimizator griješi i gradi pogrešan plan.
Za vizualizaciju plana izvedbe koristit ćemo se web mjestom . Međutim, stranica nije pokazala ništa zanimljivo ili korisno. Na prvi i drugi pogled ne postoji ništa što bi stvarno moglo pomoći. Je li moguće da je Full Scan minimalan. Samo naprijed.
Hipoteza 2-Udarac na podlogu sa strane autovakuma, morate se riješiti kočnica.
Ali demoni autovakuuma dobro se ponašaju, nema dugotrajnih procesa. Bez ozbiljnog opterećenja. Moramo potražiti nešto drugo.
Hipoteza 3 - Statistika je zastarjela, treba sve preračunati
Opet, ne to. Statistika je ažurirana. Što, s obzirom na nedostatak problema s autovakuumom, ne čudi.
Krenimo s optimizacijom
Glavna tablica 'wdata' svakako nije mala, gotovo 3 milijuna zapisa.
A ovu tablicu slijedi Full Scan.
Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") I ((Podplan 1) = s."SPENT_DATE"))
-> Skeniranje sekvenci na wdata w (cijena=0.00..574151.49 redaka=26886249 širina=46) (stvarno vrijeme=0.005..8153.565 redaka=26873950 petlji=1)
Radimo standardnu stvar: 'ajde, napravimo indeks i sve će letjeti'.
Stvoren je indeks u polju “SPENT_ID”.
Kao rezultat toga:
Plan izvršenja upita pomoću indeksa
Pa, je li pomoglo?
Bilo je: 8 222 351.640 ms (nešto više od 2 sata)
Postalo je: 6 985 431.575 ms (skoro 2 sata)
Općenito, iste jabuke, pogled sa strane.
Prisjetimo se klasika:
“Imate li isti takav, ali bez krila? Tražit će se".
U principu, to bi se moglo nazvati dobrim rezultatom, dobro, ne dobrim, ali prihvatljivim. U najmanju ruku, pružite veliko izvješće kupcu s opisom koliko je učinjeno i zašto je ono što je učinjeno bilo dobro.
No, ipak je konačna odluka još daleko. Jako daleko.
A sada ono najzanimljivije - nastavljamo s optimizacijom, izgladit ćemo zahtjev
Prvi korak - Koristite JOIN
Ponovno napisani zahtjev sada izgleda ovako (pa barem ljepši):
Upit koristeći JOINSELECT
str.“PARAMETER_ID” kao parameter_id,
pd."PD_NAME" KAO pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" KAO LRM,
w. "LOTID" KAO lotid,
w.“RTD_VALUE” KAO RTD_vrijednost,
w.“LOWER_SPEC_LIMIT” KAO donji_spec_limit,
w.“UPPER_SPEC_LIMIT” KAO gornje_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" KAO potrošeno_ime,
s.“SPENT_DATE” KAO potrošen_datum,
ekstrakt(godina od "SPENT_DATE") KAO godina,
ekstrakt (mjesec od "SPENT_DATE") kao mjesec,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" KAO stpm_name,
str.“CUSTOMERPARAM_NAME” KAO customerparam_name
FROM wdata w INNER JOIN potrošeno s ON w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON str.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN potrošeno_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
UNUTARNJI JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
GDJE
s.“SPENT_DATE” >= '2018-07-01' I s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
FROM wdata w2 INNER JOIN potrošen s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”
UNUTARNJI JOIN wdata w
ON w2.“LRM” = w.“LRM” );
Vrijeme planiranja: 2.486 ms
Vrijeme izvršenja: 1223680.326 ms
Dakle, prvi rezultat.
Bilo je: 6 ms (skoro 985 sata).
Postalo je: 1 223 680.326 ms (nešto više od 20 minuta).
Dobar rezultat. U principu, opet, tu bismo mogli stati. Ali toliko je nezanimljivo da ne možete stati.
IBO
Drugi korak - riješite se koreliranog podupita
Promijenjen tekst zahtjeva:
Bez koreliranog podupitaSELECT
str.“PARAMETER_ID” kao parameter_id,
pd."PD_NAME" KAO pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" KAO LRM,
w. "LOTID" KAO lotid,
w.“RTD_VALUE” KAO RTD_vrijednost,
w.“LOWER_SPEC_LIMIT” KAO donji_spec_limit,
w.“UPPER_SPEC_LIMIT” KAO gornje_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" KAO potrošeno_ime,
s.“SPENT_DATE” KAO potrošen_datum,
ekstrakt(godina od "SPENT_DATE") KAO godina,
ekstrakt (mjesec od "SPENT_DATE") kao mjesec,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" KAO stpm_name,
str.“CUSTOMERPARAM_NAME” KAO customerparam_name
FROM wdata w INNER JOIN potrošeno s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON str.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN potrošeno_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
UNUTARNJI JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
UNUTARNJE SPAJANJE (SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”)
FROM potrošeno s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GRUPIRAJ PO w2.“LRM”
) md na w.“LRM” = md.“LRM”
GDJE
s."SPENT_DATE" >= '2018-07-01' I s."SPENT_DATE" <= '2018-09-30';
Vrijeme planiranja: 2.291 ms
Vrijeme izvršenja: 165021.870 ms
Bilo je: 1 223 680.326 ms (nešto više od 20 minuta).
Postalo je: 165 021.870 ms (nešto više od 2 minute).
Ovo je već prilično dobro.
Međutim, kako kažu Britanci "Ali, uvijek postoji ali" Predobar rezultat automatski bi trebao izazvati sumnju. Nešto nije u redu.
Hipoteza o ispravljanju upita kako bi se riješio koreliranog podupita je točna. Ali morate ga malo dotjerati da bi konačni rezultat bio točan.
Kao rezultat, prvi međurezultat:
Uređeni upit bez koreliranog podupitaSELECT
str.“PARAMETER_ID” kao parameter_id,
pd."PD_NAME" KAO pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" KAO LRM,
w. "LOTID" KAO lotid,
w.“RTD_VALUE” KAO RTD_vrijednost,
w.“LOWER_SPEC_LIMIT” KAO donji_spec_limit,
w.“UPPER_SPEC_LIMIT” KAO gornje_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" KAO potrošeno_ime,
s.“SPENT_DATE” KAO potrošen_datum,
izvadak (godina iz s.“SPENT_DATE”) KAO godina,
ekstrakt (mjesec iz s.“SPENT_DATE”) kao mjesec,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" KAO stpm_name,
str.“CUSTOMERPARAM_NAME” KAO customerparam_name
FROM wdata w INNER JOIN potrošeno s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON str.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN potrošeno_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
UNUTARNJI JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN ( SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”) KAO “SPENT_DATE”
FROM potrošeno s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GRUPIRAJ PO w2.“LRM”
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” I md.“LRM” = w.“LRM”
GDJE
s."SPENT_DATE" >= '2018-07-01' I s."SPENT_DATE" <= '2018-09-30';
Vrijeme planiranja: 3.192 ms
Vrijeme izvršenja: 208014.134 ms
Dakle, na kraju smo dobili prvi prihvatljiv rezultat, koji nije sramota pokazati kupcu:
Započelo sa: 8 222 351.640 ms (više od 2 sata)
Uspjeli smo postići: 1 ms (nešto više od 223 minuta).
Rezultat (privremeni): 208 014.134 ms (nešto više od 3 minute).
Izvrstan rezultat.
Ukupan
Tu smo mogli stati.
ALI…
Apetit dolazi s jelom. Onaj tko hoda svladat će cestu. Svaki rezultat je posredan. Stao i umro. itd.
Nastavimo s optimizacijom.
Odlična ideja. Pogotovo s obzirom na to da mušterija nije ni imala ništa protiv. I čak snažno za to.
Dakle, vrijeme je za redizajn baze podataka. Sama struktura upita više se ne može optimizirati (iako, kako se kasnije pokazalo, postoji opcija da se osigura da sve zapravo ne uspije). Ali početak optimizacije i razvoja dizajna baze podataka već je vrlo obećavajuća ideja. I što je najvažnije zanimljivo. Opet, sjeti se svoje mladosti. Nisam odmah postao DBA, odrastao sam kao programer (BASIC, asembler, C, double-plus C, Oracle, plsql). Zanimljiva tema, naravno, za poseban memoar ;-).
Međutim, ne dajmo se omesti.
Dakle,
Ili će nam možda particioniranje pomoći?
Spoiler - "Da, pomoglo je, uključujući optimizaciju performansi."
Ali to je sasvim druga priča...
Nastavit će se…
Izvor: www.habr.com