Autovacuum blokuje wydajność w godzinach szczytu - jak go okiełznać

PostgreSQL

Autovacuum blokuje wydajność w godzinach szczytu - jak go okiełznać

W skrócie

Autovacuum to jeden z najważniejszych mechanizmów PostgreSQL - sprząta martwe wersje wierszy i aktualizuje statystyki, dzięki czemu tabele nie puchną, a planer podejmuje trafne decyzje. Problem w tym, że domyślnie uruchamia się wtedy, gdy nazbiera się dość zmian, a to często pokrywa się z godzinami szczytu. Efekt: właśnie pod największym obciążeniem baza dodatkowo mieli w tle. Kuszące jest wyłączenie autovacuum, ale to najgorsza możliwa decyzja - właściwą drogą jest jego okiełznanie, a nie wyłączenie.

Jak to wygląda w praktyce

W raportach monitoringu widzisz, że spowolnienia bazy pokrywają się z porą największego ruchu, a jednocześnie rośnie odczyt i zapis dysku. Zaglądasz do aktywności i widzisz proces autovacuum pracujący na dużej, mocno modyfikowanej tabeli - na przykład tabeli sesji albo kolejki zdarzeń, gdzie w ciągu godziny dzieją się setki tysięcy zmian. Zapytania użytkowników zwalniają, bo autovacuum konkuruje z nimi o dysk i procesor. Ktoś w panice proponuje ALTER TABLE ... SET (autovacuum_enabled = false) i faktycznie na chwilę robi się lżej - ale kilka dni później ta sama tabela jest spuchnięta, zapytania po niej są jeszcze wolniejsze, a statystyki tak nieaktualne, że planer wybiera fatalne plany.

Dlaczego tak się dzieje

Autovacuum nie ma zegara - decyzję o starcie podejmuje na podstawie liczby martwych wierszy w tabeli. Próg liczony jest ze wzoru: autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor pomnożony przez liczbę wierszy tabeli. Domyślny współczynnik skali to 0,2, czyli odkurzanie rusza po zmianie mniej więcej jednej piątej wierszy. Na tabeli z ogromnym ruchem ten próg przekracza się właśnie w szczycie - stąd zbieżność. Drugi element to hamulec kosztowy: autovacuum celowo pracuje wolno, robiąc przerwy sterowane parametrami autovacuum_vacuum_cost_limit i autovacuum_vacuum_cost_delay, żeby nie zajechać dysku. Domyślne ustawienia bywają jednak zbyt zachowawcze - odkurzanie ciągnie się długo i wchodzi w szczyt, zamiast szybko zrobić swoje poza nim. Do tego liczba równoległych procesów (autovacuum_max_workers) jest ograniczona, więc gdy kilka dużych tabel potrzebuje uwagi naraz, robotnicy się kolejkują. Wyłączenie autovacuum usuwa objaw, ale zamienia go na dużo gorszy: bloat i przeterminowane statystyki.

Jak to rozwiązać krok po kroku

1. Nigdy nie wyłączaj autovacuum globalnie. Jeśli ktoś ustawił autovacuum = off albo wyłączył go na tabeli, przywróć działanie - problemem jest tempo i moment, nie sam mechanizm.
2. Zidentyfikuj tabele, które realnie generują pracę: SELECT relname, n_dead_tup, n_live_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;. To pokaże, gdzie zbiera się najwięcej martwych wierszy.
3. Punktowo zaostrz progi dla najbardziej ruchliwych tabel, żeby odkurzanie ruszało częściej i w mniejszych porcjach, zamiast raz na jakiś czas w dużym uderzeniu: ALTER TABLE kolejka_zdarzen SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_threshold = 1000);. Częstsze, drobne przebiegi są mniej odczuwalne niż jeden wielki.
4. Podnieś przepustowość odkurzania, żeby kończyło się szybciej: zwiększ autovacuum_vacuum_cost_limit (na przykład do 2000) albo zmniejsz opóźnienie autovacuum_vacuum_cost_delay. Dzięki temu autovacuum wykona zadanie sprawnie, gdy zasoby są dostępne.
5. Dostosuj liczbę robotników do liczby dużych, aktywnych tabel: rozważ zwiększenie autovacuum_max_workers, jeśli monitoring pokazuje, że kilka tabel jednocześnie czeka w kolejce na odkurzenie.
6. Dla tabel z wyraźnym cyklem dobowym rozważ dodatkowe, ręczne VACUUM (ANALYZE) zaplanowane na okno poza szczytem - autovacuum zostaje włączony jako siatka bezpieczeństwa, a planowe odkurzanie odciąża go w ciągu dnia.

Jak sprawdzić, że zadziałało

Obserwuj liczbę martwych wierszy w newralgicznych tabelach: SELECT relname, n_dead_tup, last_autovacuum, autovacuum_count FROM pg_stat_user_tables WHERE relname = 'kolejka_zdarzen';. Po dostrojeniu n_dead_tup powinien utrzymywać się na niskim poziomie, a last_autovacuum pokazywać częste, świeże przebiegi zamiast rzadkich dużych. Że tabela nie puchnie, potwierdzisz porównując rozmiar w czasie przez SELECT pg_size_pretty(pg_total_relation_size('kolejka_zdarzen')); - wartość powinna być stabilna, a nie systematycznie rosnąć. Najważniejszy dowód biznesowy to zniknięcie zbieżności spowolnień ze szczytem: w monitoringu okresy odkurzania rozkładają się równiej i przestają nakładać się na godziny największego ruchu. Jeśli mimo zmian widzisz jednego robotnika mielącego godzinami tę samą tabelę, sprawdź, czy nie blokuje go długo otwarta transakcja - autovacuum nie posprząta wierszy nowszych niż najstarsza żywa transakcja w klastrze.

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