O tym, jak musieliśmy zoptymalizować zapytanie PostgreSQL i co z tego wszystkiego wyszło.
Dlaczego musiałeś? Tak, bo przez poprzednie 4 lata wszystko działało cicho, spokojnie, jak tykanie zegara.
jako epigraf.
Oparta na prawdziwych wydarzeniach.
Wszystkie imiona zostały zmienione, zbiegi okoliczności są przypadkowe.
Kiedy osiągniesz określony wynik, zawsze warto pamiętać, jaki był impuls na początku, od czego wszystko się zaczęło.
To, co się w rezultacie wydarzyło, zostało krótko opisane w artykule „".
Pewnie ciekawie będzie odtworzyć ciąg poprzednich wydarzeń.
W historii zapisano dokładną datę rozpoczęcia - 2018-09-10 18:02:48.
Również w tej historii jest prośba, od której wszystko się zaczęło:
Prośba o problemSELECT
p.„PARAMETER_ID” jako parametr_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS numer_części klienta,
w."LRM" AS LRM,
w. „LOTID” AS lotid,
w.“RTD_VALUE” AS RTD_value,
w.„LOWER_SPEC_LIMIT” AS dolny_limit_specyfikacji,
w.„UPPER_SPEC_LIMIT” AS górny_limit_specyfikacji,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS nazwa_wydatku,
s.„SPENT_DATE” AS wydana_data,
ekstrakt (rok z „SPENT_DATE”) AS rok,
wyodrębnij (miesiąc z „SPENT_DATE”) jako miesiąc,
s."REPORT_NAME" AS nazwa_raportu,
p."STPM_NAME" AS stpm_name,
p.„CUSTOMERPARAM_NAME” JAKO nazwa_parametru klienta
Z wdata w,
spędził s,
pmtr p,
spędził_pd sp,
pd pd
GDZIE s.„SPENT_ID” = w.„SPENT_ID”
AND p.PARAMETER_ID" = w.PARAMETER_ID"
AND s.„SPENT_ID” = sp.„SPENT_ID”
ORAZ pd."PD_ID" = sp."PD_ID"
AND s.„SPENT_DATE” >= '2018-07-01' ORAZ s.„SPENT_DATE” <= '2018-09-30'
i s. „SPENT_DATE” = (WYBIERZ MAX(s2. „SPENT_DATE”)
Z spędził s2,
wdata w2
GDZIE s2.“SPENT_ID” = w2.“SPENT_ID”
ORAZ w2.„LRM” = w.„LRM”);
Opis problemu jest przewidywalnie standardowy – „Wszystko jest źle. Powiedz mi, na czym polega problem.
Od razu przypomniała mi się anegdota z czasów dysków 3 i pół cala:
Lamer przychodzi do hakera.
-U mnie nic nie działa, powiedz gdzie jest problem.
-W DNA...
Ale oczywiście nie jest to sposób na rozwiązywanie incydentów związanych z wydajnością. „Mogą nas nie zrozumieć" (Z). Musimy to rozgryźć.
Cóż, kopmy. Może w rezultacie coś się zgromadzi.
Rozpoczęło się śledztwo
A więc to, co można od razu zobaczyć gołym okiem, nawet bez uciekania się do WYJAŚNIENIA.
1) Łączenia JOIN nie są używane. Jest to złe, szczególnie jeśli liczba połączeń jest większa niż jedno.
2) Ale jeszcze gorsze jest to, że podzapytania są zresztą skorelowane z agregacją. To jest bardzo złe.
To jest oczywiście złe. Ale to tylko z jednej strony. Z drugiej strony jest to bardzo dobre, ponieważ problem wyraźnie ma rozwiązanie i prośbę, którą można poprawić.
Nie idź do wróżki (C).
Plan zapytań nie jest aż tak skomplikowany, ale ma charakter orientacyjny:
Plan wykonania
Najciekawsze i najbardziej przydatne jest jak zwykle na początku i na końcu.
Zagnieżdżona pętla (koszt=935.84..479763226.18 wierszy=3322 szerokość=135) (rzeczywisty czas=31.536..8220420.295 wierszy=8111656 pętli=1)
Czas planowania: 3.807 ms
Czas wykonania: 8222351.640 ms
Czas realizacji to ponad 2 godziny.
Fałszywe hipotezy, które wymagały czasu
Hipoteza 1 – Optymalizator popełnia błąd i buduje zły plan.
Aby zwizualizować plan wykonania, skorzystamy z witryny . Jednak strona nie pokazała niczego interesującego ani przydatnego. Na pierwszy i drugi rzut oka nie ma nic, co mogłoby naprawdę pomóc. Czy to możliwe, że pełne skanowanie jest minimalne? Zacząć robić.
Hipoteza 2 - Uderzenie w bazę od strony autovacuum, trzeba pozbyć się hamulców.
Ale demony automatycznej próżni zachowują się dobrze, nie ma długotrwałych procesów. Żadnego poważnego obciążenia. Musimy poszukać czegoś innego.
Hipoteza 3 – Statystyki są nieaktualne, wszystko trzeba przeliczyć
Powtórzę: nie to. Statystyki są aktualne. Co, biorąc pod uwagę brak problemów z autovacuum, nie jest zaskakujące.
Zacznijmy optymalizację
Główna tabela „wdata” z pewnością nie jest mała, ma prawie 3 miliony rekordów.
I to właśnie z tej tabeli korzysta Pełne skanowanie.
Warunek skrótu: ((w."SPENT_ID" = s."SPENT_ID") ORAZ ((Plan podrzędny 1) = s."SPENT_DATE"))
-> Kolejne skanowanie na wdata w (koszt=0.00..574151.49 wierszy=26886249 szerokość=46) (rzeczywisty czas=0.005..8153.565 wierszy=26873950 pętli=1)
Robimy standardową rzecz: „No dalej, zróbmy indeks i wszystko będzie latać”.
Utworzono indeks w polu „SPENT_ID”.
W rezultacie:
Zapytanie o plan wykonania przy użyciu indeksu
No i co, pomogło?
To było: 8 222 351.640 ms (trochę ponad 2 godziny)
Stało się: 6 985 431.575 ms (prawie 2 godziny)
Ogólnie te same jabłka, widok z boku.
Przypomnijmy klasykę:
„Czy masz taki sam, ale bez skrzydeł? Będzie szukać".
W zasadzie można to nazwać wynikiem dobrym, cóż, niezbyt dobrym, ale do zaakceptowania. Przynajmniej przekaż klientowi duży raport opisujący, ile zostało zrobione i dlaczego to, co zostało zrobione, było dobre.
Jednak ostateczna decyzja jest wciąż odległa. Bardzo daleko.
A teraz najciekawsze - kontynuujemy optymalizację, dopracowujemy żądanie
Krok pierwszy — użyj DOŁĄCZ
Przepisane żądanie wygląda teraz tak (cóż, przynajmniej piękniej):
Zapytanie za pomocą JOINSELECT
p.„PARAMETER_ID” jako parametr_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS numer_części klienta,
w."LRM" AS LRM,
w. „LOTID” AS lotid,
w.“RTD_VALUE” AS RTD_value,
w.„LOWER_SPEC_LIMIT” AS dolny_limit_specyfikacji,
w.„UPPER_SPEC_LIMIT” AS górny_limit_specyfikacji,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS nazwa_wydatku,
s.„SPENT_DATE” AS wydana_data,
ekstrakt (rok z „SPENT_DATE”) AS rok,
wyodrębnij (miesiąc z „SPENT_DATE”) jako miesiąc,
s."REPORT_NAME" AS nazwa_raportu,
p."STPM_NAME" AS stpm_name,
p.„CUSTOMERPARAM_NAME” JAKO nazwa_parametru klienta
FROM wdata w INNER JOIN wydał s ON w.„SPENT_ID”=s.”„SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
WEWNĘTRZNE ZŁĄCZENIE Sp._pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
WEWNĘTRZNE ZŁĄCZENIE pd pd ON pd.“PD_ID” = sp.“PD_ID”
WHERE
s.„SPENT_DATE” >= '2018-07-01' ORAZ s.„SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (WYBIERZ MAX(s2.“SPENT_DATE”)
FROM wdata w2 INNER JOIN spędził s2 na w2.“SPENT_ID”=s2.“SPENT_ID”
POŁĄCZENIE WEWNĘTRZNE wdata w
ON w2.“LRM” = w.“LRM” );
Czas planowania: 2.486 ms
Czas wykonania: 1223680.326 ms
A więc pierwszy wynik.
To było: 6 985 431.575 ms (prawie 2 godziny).
Stało się: 1 223 680.326 ms (nieco ponad 20 minut).
Dobry wynik. W zasadzie znowu moglibyśmy na tym poprzestać. Ale to jest tak nieciekawe, że nie możesz przestać.
DLA
Krok drugi - pozbądź się skorelowanego podzapytania
Zmieniony tekst żądania:
Bez skorelowanego podzapytaniaSELECT
p.„PARAMETER_ID” jako parametr_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS numer_części klienta,
w."LRM" AS LRM,
w. „LOTID” AS lotid,
w.“RTD_VALUE” AS RTD_value,
w.„LOWER_SPEC_LIMIT” AS dolny_limit_specyfikacji,
w.„UPPER_SPEC_LIMIT” AS górny_limit_specyfikacji,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS nazwa_wydatku,
s.„SPENT_DATE” AS wydana_data,
ekstrakt (rok z „SPENT_DATE”) AS rok,
wyodrębnij (miesiąc z „SPENT_DATE”) jako miesiąc,
s."REPORT_NAME" AS nazwa_raportu,
p."STPM_NAME" AS stpm_name,
p.„CUSTOMERPARAM_NAME” JAKO nazwa_parametru klienta
FROM wdata w INNER JOIN wydał s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
WEWNĘTRZNE ZŁĄCZENIE Sp._pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
WEWNĘTRZNE ZŁĄCZENIE pd pd ON pd.“PD_ID” = sp.“PD_ID”
POŁĄCZENIE WEWNĘTRZNE (WYBIERZ w2.“LRM”, MAX(s2.“SPENT_DATE”)
FROM spędzonego s2 WEWNĘTRZNE POŁĄCZENIE wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GRUPUJ WEDŁUG w2.“LRM”
) md na w.„LRM” = md.„LRM”
WHERE
s."SPENT_DATE" >= '2018-07-01' ORAZ s."SPENT_DATE" <= '2018-09-30';
Czas planowania: 2.291 ms
Czas wykonania: 165021.870 ms
To było: 1 223 680.326 ms (nieco ponad 20 minut).
Stało się: 165 021.870 ms (nieco ponad 2 minuty).
To już jest całkiem dobre.
Jednak jak mówią Brytyjczycy „Ale zawsze jest ale" Wynik zbyt dobry powinien automatycznie wzbudzić podejrzenia. Coś tu nie gra.
Hipoteza o poprawieniu zapytania w celu pozbycia się skorelowanego podzapytania jest słuszna. Trzeba jednak trochę popracować, żeby efekt końcowy był prawidłowy.
W rezultacie pierwszy wynik pośredni:
Edytowane zapytanie bez skorelowanego podzapytaniaSELECT
p.„PARAMETER_ID” jako parametr_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS numer_części klienta,
w."LRM" AS LRM,
w. „LOTID” AS lotid,
w.“RTD_VALUE” AS RTD_value,
w.„LOWER_SPEC_LIMIT” AS dolny_limit_specyfikacji,
w.„UPPER_SPEC_LIMIT” AS górny_limit_specyfikacji,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS nazwa_wydatku,
s.„SPENT_DATE” AS wydana_data,
wyciąg (rok z s. „SPENT_DATE”) AS rok,
wyodrębnij (miesiąc z s. „SPENT_DATE”) jako miesiąc,
s."REPORT_NAME" AS nazwa_raportu,
p."STPM_NAME" AS stpm_name,
p.„CUSTOMERPARAM_NAME” JAKO nazwa_parametru klienta
FROM wdata w INNER JOIN wydał s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
WEWNĘTRZNE ZŁĄCZENIE Sp._pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
WEWNĘTRZNE ZŁĄCZENIE pd pd ON pd.“PD_ID” = sp.“PD_ID”
WEWNĘTRZNE POŁĄCZENIE (WYBIERZ w2.“LRM”, MAX(s2.“SPENT_DATE”) JAKO „SPENT_DATE”
FROM spędzonego s2 WEWNĘTRZNE POŁĄCZENIE wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GRUPUJ WEDŁUG w2.“LRM”
) md ON md.„SPENT_DATE” = s.„SPENT_DATE” ORAZ md.„LRM” = w.„LRM”
WHERE
s."SPENT_DATE" >= '2018-07-01' ORAZ s."SPENT_DATE" <= '2018-09-30';
Czas planowania: 3.192 ms
Czas wykonania: 208014.134 ms
Kończymy więc na pierwszym akceptowalnym wyniku, którego nie wstydzimy się pokazać klientowi:
Zaczęło się od: 8 222 351.640 ms (ponad 2 godziny)
Udało nam się osiągnąć: 1 223 680.326 ms (nieco ponad 20 minut).
Wynik (tymczasowy): 208 014.134 ms (nieco ponad 3 minuty).
Doskonały wynik.
Łączny
Mogliśmy się tam zatrzymać.
ALE…
Apetyt pojawia się wraz z jedzeniem. Ten, kto idzie, opanuje drogę. Każdy wynik jest pośredni. Zatrzymał się i zmarł. Itp.
Kontynuujmy optymalizację.
Świetny pomysł. Tym bardziej, że klientowi nawet to nie przeszkadzało. A nawet mocno za to.
Nadszedł więc czas na przeprojektowanie bazy danych. Sama struktura zapytania nie daje się już optymalizować (choć, jak się później okazało, istnieje możliwość zapewnienia, że faktycznie wszystko się nie powiedzie). Jednak rozpoczęcie optymalizacji i rozwoju projektu bazy danych jest już bardzo obiecującym pomysłem. I co najważniejsze interesujące. Jeszcze raz przypomnij sobie swoją młodość. Nie od razu zostałem DBA, dorastałem jako programista (BASIC, assembler, C, double-plus C, Oracle, plsql). Ciekawy temat oczywiście na osobne wspomnienie ;-).
Nie dajmy się jednak zwieść.
W ten sposób
A może partycjonowanie nam pomoże?
Spoiler — „Tak, pomogło, w tym w optymalizacji wydajności”.
Ale to zupełnie inna historia...
Ciąg dalszy nastąpi…
Źródło: www.habr.com