Zapytania są wolne - od czego zacząć tuning w PostgreSQL

PostgreSQL

Zapytania są wolne - od czego zacząć tuning w PostgreSQL

W skrócie

"Baza jest wolna" to nie diagnoza, tylko objaw. Pokazujemy uporządkowaną ścieżkę tuningu w PostgreSQL, która zaczyna się od pomiaru, a nie od zgadywania - dzięki temu poprawiamy to, co faktycznie boli.

Jak to wygląda w praktyce

Zgłoszenia brzmią zwykle mało konkretnie: "system muli", "raport się nie otwiera", "od rana wszystko wolniej". Czasem chodzi o jedno ciężkie zapytanie raportowe, czasem o setki drobnych zapytań, które pojedynczo są szybkie, ale w sumie zjadają serwer. Bywa, że problem pojawia się tylko w szczycie, a rano znika. Pokusa jest jedna: dorzucić indeks na chybił trafił albo podkręcić pamięć i liczyć, że pomoże. Zwykle nie pomaga, a czasem szkodzi - nadmiar indeksów spowalnia zapisy, a źle dobrany parametr obciąża serwer bardziej. Potrzebujemy metody, która wskaże palcem konkretnego winowajcę.

Dlaczego tak się dzieje

Wolne zapytanie prawie zawsze ma jedną z kilku przyczyn i wszystkie są mierzalne. Zapytanie może brakować odpowiedniego indeksu i baza przeczesuje całą tabelę (sekwencyjny odczyt milionów wierszy). Planer mógł źle oszacować liczbę wierszy, bo statystyki są nieaktualne, i wybrał gorszy plan. Zapytanie może zwracać albo sortować ogromną liczbę wierszy, które nie mieszczą się w pamięci roboczej i lądują na dysku. Wreszcie problemem bywa nie pojedyncze zapytanie, lecz ich liczba - typowy problem N+1, gdzie aplikacja wykonuje tysiąc małych zapytań zamiast jednego. Klucz w tym, że każdą z tych przyczyn widać w danych: PostgreSQL zbiera statystyki wykonań w rozszerzeniu pg_stat_statements, a dokładny przebieg pojedynczego zapytania pokazuje EXPLAIN ANALYZE. Bez tych dwóch narzędzi tuning jest zgadywaniem.

Jak to rozwiązać krok po kroku

1. Włączamy pomiar globalny. Dodajemy pg_stat_statements do shared_preload_libraries w postgresql.conf, restartujemy klaster i w bazie wykonujemy CREATE EXTENSION pg_stat_statements;. Od teraz baza zlicza czas i liczbę wykonań każdego zapytania.
2. Znajdujemy winowajców. Odpytujemy najbardziej kosztowne zapytania sumarycznie: SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;. Sortujemy po total_exec_time (łączny czas), bo to on realnie obciąża serwer - zapytanie szybkie, ale wołane milion razy, boli bardziej niż jedno wolne.
3. Bierzemy najgorsze zapytanie i oglądamy jego plan: EXPLAIN (ANALYZE, BUFFERS) <zapytanie>;. Szukamy węzłów z dużą liczbą wierszy i dużym czasem - to tam ucieka czas.
4. Reagujemy na to, co widać w planie. Sekwencyjny odczyt dużej tabeli przy selektywnym warunku to sygnał do założenia indeksu na kolumnie z WHERE lub JOIN. Zła estymacja wierszy to sygnał, by odświeżyć statystyki: ANALYZE nazwa_tabeli;.
5. Jeśli plan pokazuje sortowanie lub złączenie schodzące na dysk (widoczne przy BUFFERS jako zapisy tymczasowe), rozważamy podniesienie work_mem dla tej sesji lub zapytania, zamiast globalnie.
6. Jeśli winą jest wiele identycznych drobnych zapytań (wysokie calls, niski mean_exec_time), problem jest po stronie aplikacji - łączymy je w jedno zapytanie albo dodajemy pobieranie wsadowe. Żaden parametr bazy tego nie naprawi.
7. Po każdej zmianie mierzymy ponownie tym samym zapytaniem z EXPLAIN ANALYZE i porównujemy czas - zmieniamy jedną rzecz naraz, żeby wiedzieć, co zadziałało.

Jak sprawdzić, że zadziałało

Najprostszy dowód to spadek czasu w powtórzonym EXPLAIN (ANALYZE, BUFFERS) - węzeł, który wcześniej czytał całą tabelę, powinien zamienić się w odczyt po indeksie z dużo mniejszą liczbą wierszy i krótszym czasem. Na poziomie całej bazy zerujemy statystyki przez SELECT pg_stat_statements_reset();, dajemy aplikacji popracować pod typowym obciążeniem i po jakimś czasie znów sortujemy po total_exec_time - poprawione zapytanie powinno spaść w rankingu. Warto też obserwować systemowe metryki: mniejsze zużycie procesora i mniej odczytów z dysku pod tym samym ruchem to twardy sygnał, że tuning trafił w sedno. Jeśli po zmianie nic się nie poprawiło, cofamy ją i wracamy do planu - to znak, że diagnoza była błędna, a nie że trzeba dokładać kolejne poprawki.

Wróć do listy: 100 najczęstszych pytań i problemów z PostgreSQL

Sprawdź szkolenie: Administracja, replikacja i tuning baz danych PostgreSQL

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Sprawdź szkolenie: Zaawansowana administracja PostgreSQL (HA, DR, monitoring, skalowanie)

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5