Se spomnite, kako se je vse začelo. Vse je bilo prvič in znova

O tem, kako sem moral optimizirati poizvedbo PostgreSQL in kaj se je iz vsega tega izcimilo.
Zakaj si moral? Da, ker je prejšnja 4 leta vse delovalo tiho, mirno, kot bi ura tiktakala.
Kot epigraf.

Na podlagi resničnih dogodkov.
Vsa imena so spremenjena, naključja so naključna.

Ko dosežeš določen rezultat, se je vedno zanimivo spomniti, kaj je bil povod za začetek, kje se je vse začelo.

Torej, kaj se je zgodilo kot rezultat, je na kratko opisano v članku "Sinteza kot ena od metod za izboljšanje zmogljivosti PostgreSQL".

Verjetno bo zanimivo poustvariti verigo prejšnjih dogodkov.
Zgodovina je shranila točen začetni datum - 2018-09-10 18:02:48.
Tudi v zgodbi je prošnja, iz katere se je vse začelo:
Zahteva za problemIZBIRA
p.“PARAMETER_ID” kot parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" KOT LRM,
w. "LOTID" KOT lotid,
w.“RTD_VALUE” KOT RTD_vrednost,
w.“LOWER_SPEC_LIMIT” KOT spodnja_spec_limit,
w.“UPPER_SPEC_LIMIT” KOT zgornja_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS porabljeno_ime,
s.“SPENT_DATE” KOT datum_porabe,
izvleček (leto iz "SPENT_DATE") KOT leto,
izvleček (mesec iz "SPENT_DATE") kot mesec,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
OD wdata w,
porabil s,
pmtr p,
porabljen_pd sp,
pd pd
WHERE s.“SPENT_ID” = w.“SPENT_ID”
IN p."PARAMETER_ID" = w."PARAMETER_ID"
IN s.“SPENT_ID” = sp.“SPENT_ID”
IN pd."PD_ID" = sp."PD_ID"
IN s.“SPENT_DATE” >= '2018-07-01' IN s.“SPENT_DATE” <= '2018-09-30'
in s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
OD porabljenega s2,
wdata w2
WHERE s2.“SPENT_ID” = w2.“SPENT_ID”
IN w2.»LRM« = w.»LRM«);

Opis težave je predvidljivo standarden - »Vse je slabo. Povej mi, v čem je težava.”
Takoj sem se spomnil anekdote iz časov 3inpolcolskih pogonov:

Lamer pride do hekerja.
-Nič mi ne uspe, povej mi kje je problem.
-V DNK...

Seveda pa to ni način za reševanje incidentov pri delovanju. “Morda nas ne razumejo« (Z). Moramo ugotoviti.
No, gremo kopati. Mogoče se bo posledično kaj nabralo.

Začela se je preiskava

Torej, kar je mogoče takoj videti s prostim očesom, ne da bi se sploh zatekli k RAZLAGI.
1) JOIN-i se ne uporabljajo. To je slabo, še posebej, če je število povezav več kot ena.
2) Še huje pa so korelirane podpoizvedbe, poleg tega z združevanjem. To je zelo slabo.
To je seveda slabo. A to je le po eni strani. Po drugi strani pa je to zelo dobro, saj ima problem očitno rešitev in zahtevo, ki jo je mogoče izboljšati.
Ne hodi k vedeževalki (C).
Načrt poizvedbe ni tako zapleten, je pa precej okviren:
Izvedbeni načrt

Najbolj zanimivo in uporabno je, kot ponavadi, na začetku in koncu.
Ugnezdena zanka (cena=935.84..479763226.18 vrstic=3322 širina=135) (dejanski čas=31.536..8220420.295 vrstic=8111656 zank=1)
Čas načrtovanja: 3.807 ms
Čas izvajanja: 8222351.640 ms
Čas dokončanja je več kot 2 uri.

Napačne hipoteze, ki so vzele čas

Hipoteza 1 – Optimizator naredi napako in sestavi napačen načrt.

Za vizualizacijo načrta izvedbe bomo uporabili spletno mesto https://explain.depesz.com/. Vendar stran ni pokazala nič zanimivega ali uporabnega. Na prvi in ​​drugi pogled ni ničesar, kar bi lahko resnično pomagalo. Ali je možno, da je Full Scan minimalen. Kar daj.

Hipoteza 2-Udarec na podlago s strani avtovakuuma, morate se znebiti zavor.

Toda avtovakuumski demoni se dobro obnašajo, ni dolgotrajnih procesov. Brez resne obremenitve. Moramo poiskati nekaj drugega.

Hipoteza 3 - Statistika je zastarela, vse je treba preračunati

Še enkrat, ne to. Statistika je ažurna. Kar glede na pomanjkanje težav z avtovakuumom ni presenetljivo.

Začnimo z optimizacijo

Glavna tabela 'wdata' vsekakor ni majhna, skoraj 3 milijone zapisov.
In tej tabeli sledi Full Scan.

Pogoj zgoščevanja: ((w."SPENT_ID" = s."SPENT_ID") IN ((Podnačrt 1) = s."SPENT_DATE"))
-> Seq Scan na wdata w (cena=0.00..574151.49 vrstic=26886249 širina=46) (dejanski čas=0.005..8153.565 vrstic=26873950 zank=1)
Delamo standardno stvar: "Dajmo, naredimo indeks in bo vse letelo."
Ustvaril indeks v polju »SPENT_ID«.
Kot rezultat:
Načrt izvajanja poizvedbe z uporabo indeksa

No, je pomagalo?
Bilo je: 8 222 351.640 ms (malo več kot 2 uri)
Postati: 6 985 431.575 ms (skoraj 2 uri)
Na splošno ista jabolka, stranski pogled.
Spomnimo se klasike:
»Imate takega, vendar brez kril? Iskal bo".

Načeloma bi temu lahko rekli dober rezultat, no, ne dober, ampak sprejemljiv. Stranki zagotovite vsaj veliko poročilo, v katerem opisujete, koliko je bilo narejenega in zakaj je bilo dobro.
A vseeno je do končne odločitve še daleč. Zelo daleč.

In zdaj najbolj zanimiva stvar - nadaljujemo z optimizacijo, izpilili bomo zahtevo

Prvi korak - Uporabite JOIN

Prepisana zahteva zdaj izgleda tako (no vsaj lepši):
Poizvedba z uporabo JOINIZBIRA
p.“PARAMETER_ID” kot parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" KOT LRM,
w. "LOTID" KOT lotid,
w.“RTD_VALUE” KOT RTD_vrednost,
w.“LOWER_SPEC_LIMIT” KOT spodnja_spec_limit,
w.“UPPER_SPEC_LIMIT” KOT zgornja_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS porabljeno_ime,
s.“SPENT_DATE” KOT datum_porabe,
izvleček (leto iz "SPENT_DATE") KOT leto,
izvleček (mesec iz "SPENT_DATE") kot mesec,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN porabljen s ON w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN porabljen_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
KJE
s.“SPENT_DATE” >= '2018-07-01' IN s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
FROM wdata w2 INNER JOIN porabljen s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
ON w2.“LRM” = w.“LRM” );
Čas načrtovanja: 2.486 ms
Čas izvajanja: 1223680.326 ms

Torej, prvi rezultat.
Bilo je: 6 ms (skoraj 985 uri).
Postati: 1 223 680.326 ms (nekaj več kot 20 minut).
Dober rezultat. Načeloma bi se spet lahko ustavili pri tem. Vendar je tako nezanimivo, da se ne moreš ustaviti.
ZA

Drugi korak - znebite se korelirane podpoizvedbe

Spremenjeno besedilo zahteve:
Brez korelirane podpoizvedbeIZBIRA
p.“PARAMETER_ID” kot parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" KOT LRM,
w. "LOTID" KOT lotid,
w.“RTD_VALUE” KOT RTD_vrednost,
w.“LOWER_SPEC_LIMIT” KOT spodnja_spec_limit,
w.“UPPER_SPEC_LIMIT” KOT zgornja_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS porabljeno_ime,
s.“SPENT_DATE” KOT datum_porabe,
izvleček (leto iz "SPENT_DATE") KOT leto,
izvleček (mesec iz "SPENT_DATE") kot mesec,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN porabljen s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN porabljen_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (IZBERI w2.»LRM«, MAX(s2.»SPENT_DATE«)
FROM porabljen s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
ZDRUŽI PO w2.»LRM«
) md na w.“LRM” = md.“LRM”
KJE
s."SPENT_DATE" >= '2018-07-01' IN s."SPENT_DATE" <= '2018-09-30';
Čas načrtovanja: 2.291 ms
Čas izvajanja: 165021.870 ms

Bilo je: 1 223 680.326 ms (nekaj več kot 20 minut).
Postati: 165 021.870 ms (malo več kot 2 minuti).
To je že kar dobro.
Vendar, kot pravijo Britanci "Ampak, vedno obstaja ampak" Predober rezultat bi moral samodejno vzbuditi sum. Tukaj je nekaj narobe.

Hipoteza o popravljanju poizvedbe, da se znebite korelirane podpoizvedbe, je pravilna. Vendar ga morate malo prilagoditi, da bo končni rezultat pravilen.
Kot rezultat, prvi vmesni rezultat:
Urejena poizvedba brez povezane podpoizvedbeIZBIRA
p.“PARAMETER_ID” kot parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" KOT LRM,
w. "LOTID" KOT lotid,
w.“RTD_VALUE” KOT RTD_vrednost,
w.“LOWER_SPEC_LIMIT” KOT spodnja_spec_limit,
w.“UPPER_SPEC_LIMIT” KOT zgornja_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS porabljeno_ime,
s.“SPENT_DATE” KOT datum_porabe,
izvleček (leto iz s.“SPENT_DATE”) KOT leto,
izvleček (mesec iz s.»SPENT_DATE«) kot mesec,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN porabljen s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN porabljen_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (IZBERI w2.“LRM”, MAX(s2.“SPENT_DATE”) KOT “SPENT_DATE”
FROM porabljen s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
ZDRUŽI PO w2.»LRM«
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” IN md.“LRM” = w.“LRM”
KJE
s."SPENT_DATE" >= '2018-07-01' IN s."SPENT_DATE" <= '2018-09-30';
Čas načrtovanja: 3.192 ms
Čas izvajanja: 208014.134 ms

Torej, na koncu dobimo prvi sprejemljiv rezultat, ki ga ni škoda pokazati kupcu:
Začelo z: 8 222 351.640 ms (več kot 2 uri)
Uspelo nam je doseči: 1 ms (malo več kot 223 minut).
Rezultat (vmesni): 208 014.134 ms (nekaj več kot 3 minute).

Odličen rezultat.

Skupaj

Lahko bi se tam ustavili.
AMPAK…
Apetit pride z jedjo. Kdor hodi, bo obvladal cesto. Vsak rezultat je vmesni. Ustavil in umrl. itd.
Nadaljujmo z optimizacijo.
Odlična ideja. Še posebej glede na to, da stranka niti ni imela nič proti. In celo močno za to.

Torej, čas je za prenovo baze podatkov. Same strukture poizvedbe ni več mogoče optimizirati (čeprav, kot se je kasneje izkazalo, obstaja možnost, da zagotovite, da vse dejansko ne uspe). Toda začeti optimizirati in razvijati zasnovo baze podatkov je že zelo obetavna ideja. In kar je najpomembneje zanimivo. Še enkrat, spomnite se svoje mladosti. Nisem takoj postal DBA, odraščal sem kot programer (BASIC, asembler, C, double-plus C, Oracle, plsql). Zanimiva tema, seveda, za ločene spomine ;-).
Vendar se ne pustimo motiti.

Torej,

Ali pa nam bo morda particioniranje pomagalo?
Spojler - "Da, pomagalo je, vključno z optimizacijo delovanja."

Ampak to je čisto druga zgodba...

Se nadaljuje…

Vir: www.habr.com