Jak sprawdzić, które tabele wymagają VACUUM i ANALYZE

PostgreSQL

Jak sprawdzić, które tabele wymagają VACUUM i ANALYZE

W skrócie

Odpalanie VACUUM na wszystkim po kolei to marnowanie zasobów - część tabel tego w ogóle nie potrzebuje, a inne wołają o pomoc od tygodni. PostgreSQL sam zbiera statystyki, które pozwalają celnie wskazać, gdzie zaległości są realne: ile jest martwych wierszy, kiedy ostatni raz przeszło sprzątanie i czy autovacuum w ogóle daje radę. Pokażemy, jak wyciągnąć te dane jednym zapytaniem i jak zinterpretować liczby, żeby sprzątać tam, gdzie to naprawdę potrzebne.

Jak to wygląda w praktyce

Zapytania, które kiedyś były szybkie, zaczynają zwalniać, a plany wykonania wyglądają dziwnie - planer sięga po skan sekwencyjny tam, gdzie powinien użyć indeksu. Albo widzisz, że kilka tabel urosło na dysku ponad rozsądek. Podejrzewasz zaległy VACUUM i nieświeże statystyki, ale odpalanie ręcznego VACUUM na całej bazie trwa i obciąża serwer, a nie masz pewności, czy trafiasz w cel. Bywa też, że autovacuum niby działa, ale jedna gorąca tabela z lawiną aktualizacji ciągle wyprzedza jego tempo i puchnie mimo wszystko - a Ty nie wiesz, która to.

Dlaczego tak się dzieje

Przy modelu wielowersyjnym każda aktualizacja i każde usunięcie wiersza zostawia po sobie wersję martwą. Dopóki VACUUM jej nie posprząta, zajmuje miejsce i spowalnia skany, bo baza musi przechodzić także przez nieaktualne wersje. Osobnym problemem są statystyki rozkładu danych - to na nich planer opiera decyzje o użyciu indeksów i kolejności łączeń. Jeśli tabela mocno się zmieniła, a ANALYZE dawno na niej nie przeszedł, planer działa na nieaktualnym obrazie i wybiera słabe plany. Autovacuum ma progi, po których się uruchamia (zależne od odsetka zmienionych wierszy), więc bardzo duże albo bardzo intensywnie aktualizowane tabele potrafią mu uciekać - albo wpada na długo otwartą transakcję, która blokuje sprzątanie. Dobra wiadomość jest taka, że PostgreSQL zlicza to wszystko w tle, więc wystarczy odczytać właściwy widok, zamiast zgadywać.

Jak to rozwiązać krok po kroku

1. Wyciągnij tabele z największą liczbą martwych wierszy i datami ostatniego sprzątania: SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;.
2. Oceniaj proporcję, nie samą liczbę. Tabela, w której martwe wiersze stanowią duży odsetek żywych (n_dead_tup wobec n_live_tup), jest realnym kandydatem do VACUUM. Kilkaset martwych wierszy w wielomilionowej tabeli to nic.
3. Wychwyć tabele, które nigdy nie były analizowane albo mają bardzo stare daty w kolumnach last_analyze i last_autoanalyze - one najprawdopodobniej mają nieaktualne statystyki i psują plany zapytań.
4. Sprawdź, czy autovacuum nie jest przytłoczony jedną tabelą - jeśli last_autovacuum jest świeże, ale n_dead_tup i tak rośnie, znaczy, że nie nadąża. Rozważ zaostrzenie progów dla tej konkretnej tabeli.
5. Na wytypowanych tabelach uruchom celowany zabieg zamiast całej bazy: VACUUM ANALYZE nazwa_tabeli;. Sprząta martwe wiersze i od razu odświeża statystyki.
6. Dla gorącej tabeli dostrój autovacuum na stałe, na przykład obniżając próg skalowania: ALTER TABLE gorąca_tabela SET (autovacuum_vacuum_scale_factor = 0.02);, żeby sprzątanie ruszało częściej.

Jak sprawdzić, że zadziałało

Po zabiegu odczytaj pg_stat_user_tables ponownie dla tych samych tabel. Kolumna n_dead_tup powinna spaść blisko zera, a last_vacuum i last_analyze pokazać świeżą datę i godzinę. Jeśli poprawiałeś statystyki pod planer, sprawdź to wprost: wykonaj EXPLAIN na problematycznym zapytaniu i zobacz, czy planer sięga teraz po właściwy indeks i czy szacowane liczby wierszy są bliższe rzeczywistości. Warto też po kilku dniach zerknąć na te tabele jeszcze raz - jeśli martwe wiersze znów szybko narastają na tabeli, dla której zaostrzyłeś progi autovacuum, ustawienia trzeba dopracować albo poszukać długo otwartych transakcji blokujących sprzątanie.

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