O tome kako sam morao da se nosim sa PostgreSQL optimizacijom upita i šta je ispalo iz svega ovoga.
Zašto si morao? Da, jer je prethodne 4 godine sve radilo tiho, mirno, kao da je sat otkucavao.
kao epigraf.
Zasnovano na stvarnim događajima.
Sva imena su promijenjena, slučajnosti su nasumične.
Kada se postigne određeni rezultat, uvijek je zanimljivo prisjetiti se šta je bio poticaj za početak, kako je sve počelo.
Dakle, ono što se dogodilo kao rezultat je ukratko opisano u članku “".
Vjerovatno će biti zanimljivo rekreirati lanac prethodnih događaja.
Istorija je zadržala tačan datum početka — 2018-09-10 18:02:48.
Također, u priči postoji i zahtjev od kojeg je sve počelo:
Problemski zahtjevSELECT
p. "PARAMETER_ID" kao parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LRM" AS LRM,
w. "LOTID" KAO lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS niže_spec_limit,
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS potrošeno_name,
s."SPENT_DATE" AS Potrošeno_datum,
izvod (godina od "SPENT_DATE") AS godina,
izvod (mjesec od "SPENT_DATE") kao mjesec,
s."REPORT_NAME" AS naziv_izvještaja,
str. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
IZ wdataw,
proveo s,
pmtrp,
potrošeno_pdsp,
pd pd
WHERE s."SPENT_ID" = w."SPENT_ID"
I p."PARAMETER_ID" = w."PARAMETER_ID"
AND s."SPENT_ID" = sp."SPENT_ID"
I pd. "PD_ID" = sp. "PD_ID"
AND s."SPENT_DATE" >= '2018-07-01' I s."SPENT_DATE" <= '2018-09-30'
i s."SPENT_DATE" = (ODABIR MAX(s2."SPENT_DATE")
OD potrošeno s2,
wdata w2
WHERE s2."SPENT_ID" = w2."SPENT_ID"
I w2."LRM" = w."LRM");
Opis problema, predvidljivo standardan - „Sve je loše. Reci mi u čemu je problem."
Odmah sam se sjetio vica iz vremena 3 i pol inča pogona:
Lamer dolazi do hakera.
- Ništa mi ne ide, reci mi gde je problem.
-U DNK...
Ali, naravno, ovo nije način za rješavanje incidenata performansi. “Možda nas neće razumeti" (Sa). Treba to shvatiti.
Pa, hajde da kopamo. Možda će se to akumulirati kao rezultat.
započeta investicija
Dakle, ono što se može odmah vidjeti golim okom, a da se čak i ne pribjegne pomoći EXPLAIN-a.
1) JOIN-ovi se ne koriste. Ovo je loše, posebno ako je broj veza više od jedne.
2) Ali što je još gore - korelirani podupit, štaviše, sa agregacijom. Ovo je jako loše.
Ovo je loše, naravno. Ali ovo je samo s jedne strane. S druge strane, to je jako dobro, jer problem jasno ima rješenje i zahtjev se može poboljšati.
Ne idite gatari (C).
Plan upita nije tako komplikovan, ali prilično indikativan:
Plan izvršenja
Najzanimljivije i najkorisnije, kao i obično, na početku i na kraju.
Ugniježđena petlja (cijena=935.84..479763226.18 redova=3322 širina=135) (stvarno vrijeme=31.536..8220420.295 redova=8111656 petlji=1)
Vrijeme planiranja: 3.807ms
Vrijeme izvršenja: 8222351.640ms
Vrijeme izvršenja je više od 2 sata.
Lažne hipoteze za koje je trebalo vremena
Hipoteza 1- Optimizator je pogrešan, gradi pogrešan plan.
Za vizualizaciju plana izvršenja koristit ćemo stranicu . Međutim, stranica nije pokazala ništa zanimljivo ili korisno. Na prvi i drugi pogled - ništa što bi moglo pomoći. Osim ako - Puno skeniranje je minimalno. Nastavi.
Hipoteza 2-Udar na bazu sa strane autovakuma, morate se riješiti kočnica.
Ali, autovacuum demoni se ponašaju dobro, nema dugotrajnih procesa. Bilo kakvo ozbiljno opterećenje - ne. Treba tražiti nešto drugo.
Hipoteza 3-Statistika je zastarjela, morate preračunati sve leti
Opet, ne to. Statistika je ažurirana. Što, s obzirom na nedostatak problema sa autovakuumom, nije iznenađujuće.
Počinjemo optimizirati
Glavna tabela 'wdata' sigurno nije mala, skoro 3 miliona zapisa.
I upravo na ovom stolu ide Full Scan.
Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") I ((PodPlan 1) = s."SPENT_DATE"))
-> Seq Scan na wdata w (cijena=0.00..574151.49 redova=26886249 širina=46) (stvarno vrijeme=0.005..8153.565 redova=26873950 petlji=1)
Ponašamo se standardno: „napravimo indeks i sve leti“.
Napravljen indeks na polju "SPENT_ID"
Kao rezultat:
Plan izvršenja upita koristeći indeks
Pa, da li je 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:
“Imaš li ti isti, ali bez krila? Tražiće“.
U principu, ovo bi se moglo nazvati dobrim rezultatom, pa, ne dobrim, ali prihvatljivim. U najmanju ruku, pružite veliki izvještaj kupcu koji opisuje koliko je urađeno i zašto je ono što je urađeno dobro.
Međutim, do konačne odluke još je daleko. Jako daleko.
A sada najzanimljivije - nastavljamo s optimizacijom, poliraćemo upit
Prvi korak - koristite JOIN
Prepisan upit, sada izgleda ovako (pa barem lepša):
Upit koristeći JOINSELECT
p. "PARAMETER_ID" kao parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LRM" AS LRM,
w. "LOTID" KAO lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS niže_spec_limit,
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS potrošeno_name,
s."SPENT_DATE" AS Potrošeno_datum,
izvod (godina od "SPENT_DATE") AS godina,
izvod (mjesec od "SPENT_DATE") kao mjesec,
s."REPORT_NAME" AS naziv_izvještaja,
str. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN potrošio s ON w."SPENT_ID"=s."SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN proveo_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER 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" = (ODABIR MAX(s2."SPENT_DATE")
FROM wdata w2 INNER JOIN potrošio s2 NA w2."SPENT_ID"=s2."SPENT_ID"
INNER JOIN wdata w
ON w2."LRM" = w."LRM" );
Vrijeme planiranja: 2.486ms
Vrijeme izvršenja: 1223680.326ms
Dakle, evo prvog rezultata.
Bilo je: 6 985 431.575 ms (skoro 2 sata).
Postalo je: 1 223 680.326 ms (nešto više od 20 minuta).
Dobar rezultat. U principu, opet, tu bi bilo moguće stati. Ali tako nezanimljivo, ne možete stati.
ZA
Drugi korak - Oslobodite se koreliranog podupita
Promijenjen tekst zahtjeva:
Nema koreliranog podupitaSELECT
p. "PARAMETER_ID" kao parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LRM" AS LRM,
w. "LOTID" KAO lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS niže_spec_limit,
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS potrošeno_name,
s."SPENT_DATE" AS Potrošeno_datum,
izvod (godina od "SPENT_DATE") AS godina,
izvod (mjesec od "SPENT_DATE") kao mjesec,
s."REPORT_NAME" AS naziv_izvještaja,
str. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN potrošio s ON s."SPENT_ID" = w."SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN proveo_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
INNER JOIN (ODABIR w2."LRM", MAX(s2."SPENT_DATE")
FROM potrošeno s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
GROUP BY 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.291ms
Vrijeme izvršenja: 165021.870ms
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 Englezi,Ali, uvek postoji ali". Previše dobar rezultat bi automatski trebao izazvati sumnju. Ovdje nešto nije u redu.
Hipoteza o ispravljanju upita da se riješi koreliranog potupita je tačna. Ali potrebno je malo dotjerivanja da bi konačni rezultat bio ispravan.
Kao rezultat, prvi srednji rezultat:
Uređeni upit bez koreliranog potupitaSELECT
p. "PARAMETER_ID" kao parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LRM" AS LRM,
w. "LOTID" KAO lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS niže_spec_limit,
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS potrošeno_name,
s."SPENT_DATE" AS Potrošeno_datum,
izvod (godina od s. "SPENT_DATE") AS godina,
izvod (mjesec od s. "SPENT_DATE") kao mjesec,
s."REPORT_NAME" AS naziv_izvještaja,
str. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN potrošio s ON s."SPENT_ID" = w."SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN proveo_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
INNER JOIN ( SELECT w2."LRM", MAX(s2."SPENT_DATE") AS "SPENT_DATE"
FROM potrošeno s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
GROUP BY 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.192ms
Vrijeme izvršenja: 208014.134ms
Dakle, ono što imamo kao rezultat je prvi prihvatljiv rezultat, koji se ne stidimo pokazati kupcu:
Počelo sa: 8 222 351.640 ms (više od 2 sata)
Ostvareno: 1 ms (nešto više od 223 minuta).
Ishod (srednji): 208 014.134 ms (nešto više od 3 minute).
Odličan rezultat.
Rezultat
Ovo je moglo prestati.
ALI…
Apetit dolazi sa jelom. Put će se savladati hodanjem. Svaki rezultat je srednji. Zaustavljen mrtav. itd.
Nastavimo sa optimizacijom.
Odlicna ideja. Pogotovo s obzirom da mušterija nije bila ni protiv. I čak snažno - za.
Dakle, vrijeme je za redizajn baze podataka. Sama struktura upita se više ne može optimizirati (iako, kako se kasnije pokazalo, postoji opcija da sve zaista leti). Ali sada optimizirati i razviti dizajn baze podataka, ovo je već vrlo obećavajuća ideja. I što je najvažnije zanimljivo. Opet, setite se mladosti. Uostalom, nisam odmah postao DBA, izrastao sam iz programera (basic, asembler, si, si duplo plus, oracle, plsql). Zanimljiva tema, naravno, za zasebne memoare ;-).
Međutim, da se ne skrećemo.
Tako
A možda će nam sekcija pomoći?
Spojler - "Da, pomoglo je, uključujući i optimizaciju performansi."
Ali to je sasvim druga priča...
Nastavlja se…
izvor: www.habr.com