Autovacuum nie nadąża - jak go dostroić

PostgreSQL

Autovacuum nie nadąża - jak go dostroić

W skrócie

Autovacuum to proces, który w tle usuwa martwe wiersze i aktualizuje statystyki. Domyślna konfiguracja jest bezpieczna, ale dla dużych, mocno zapisywanych tabel bywa zbyt zachowawcza. Pokazujemy, jak rozpoznać, że nie nadąża, i jak go dostroić bez ryzyka dla produkcji.

Jak to wygląda w praktyce

Klasyczny obraz: rosnący n_dead_tup w pg_stat_user_tables, spadająca wydajność zapytań i puchnąca tabela. W logu przy log_autovacuum_min_duration = 0 widzisz, że przebiegi autovacuum trwają długo albo pojawiają się rzadko. Czasem w pg_stat_activity widać, że wszystkie sloty autovacuum są zajęte jednym gigantycznym przebiegiem, a reszta tabel czeka w kolejce.

Dlaczego tak się dzieje

Autovacuum decyduje o starcie dla tabeli według wzoru: sprząta, gdy liczba martwych wierszy przekroczy autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * liczba_wierszy. Domyślny scale_factor to 0.2, czyli 20 procent tabeli. Dla tabeli z 50 milionami wierszy oznacza to 10 milionów martwych rekordów, zanim cokolwiek ruszy - stanowczo za późno.

Drugi hamulec to mechanizm kosztowy. Autovacuum po wykonaniu określonej liczby operacji (autovacuum_vacuum_cost_limit) usypia na autovacuum_vacuum_cost_delay, żeby nie obciążać dysków. Przy domyślnych wartościach na szybkich dyskach NVMe sprzątanie idzie wolniej, niż mogłoby. Trzeci to autovacuum_max_workers - jeśli gorących tabel jest więcej niż workerów, ustawiają się one w kolejce.

Jak to rozwiązać krok po kroku

1. Zdiagnozuj, które tabele generują najwięcej martwych wierszy: SELECT relname, n_dead_tup, n_live_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;. Włącz też logowanie: ALTER SYSTEM SET log_autovacuum_min_duration = '0'; i SELECT pg_reload_conf();.
2. Dla konkretnych gorących tabel obniż progi lokalnie, zamiast ruszać globalnie cały serwer: ALTER TABLE zamowienia SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_threshold = 1000);. Teraz sprzątanie ruszy już przy 2 procentach martwych wierszy.
3. Przyspiesz przebieg na wydajnych dyskach. Globalnie w postgresql.conf: podnieś autovacuum_vacuum_cost_limit = 2000 i zmniejsz autovacuum_vacuum_cost_delay = 2ms. To pozwala workerowi wykonać więcej pracy przed uśpieniem.
4. Zwiększ liczbę równoległych workerów, jeśli gorących tabel jest wiele: autovacuum_max_workers = 5 w postgresql.conf. Ta zmiana wymaga restartu serwera, więc zaplanuj ją w oknie serwisowym.
5. Daj sprzątaniu więcej pamięci roboczej, dzięki czemu jeden przebieg czyści więcej indeksów za jednym razem: autovacuum_work_mem = 512MB (lub maintenance_work_mem, jeśli autovacuum_work_mem nie jest ustawione).
6. Zastosuj zmiany niewymagające restartu: SELECT pg_reload_conf();. Parametry ustawiane przez ALTER TABLE działają natychmiast, a globalne z ALTER SYSTEM po przeładowaniu (poza autovacuum_max_workers, który potrzebuje restartu).

Jak sprawdzić, że zadziałało

Obserwuj, czy n_dead_tup dla problematycznych tabel przestaje rosnąć i zaczyna spadać po kolejnych przebiegach - SELECT relname, n_dead_tup, last_autovacuum, autovacuum_count FROM pg_stat_user_tables WHERE relname = 'zamowienia';. Kolumna autovacuum_count powinna rosnąć częściej niż wcześniej, a last_autovacuum być świeży. W logu serwera (dzięki log_autovacuum_min_duration) zobaczysz każdy przebieg z liczbą usuniętych wierszy i czasem trwania. Rozmiar gorącej tabeli powinien się ustabilizować.

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