UPDATE produkuje bloat mimo autovacuum - HOT update i fillfactor

PostgreSQL

UPDATE produkuje bloat mimo autovacuum - HOT update i fillfactor

W skrócie

Ten problem dotyczy tabel, w których wiersze są często zmieniane: statusy zamówień, liczniki, sesje, stany magazynowe. Autovacuum pracuje poprawnie, liczba wierszy jest stała, a mimo to tabela i indeksy rosną po falach aktualizacji i nie wracają do dawnego rozmiaru. Pokażemy, jak sprawdzić, ile zmian PostgreSQL wykonuje jako HOT update (Heap Only Tuple, czyli zapis nowej wersji wiersza w tym samym bloku bez dotykania indeksów), i jak parametr fillfactor zmienia ten wynik.

Jak to wygląda w praktyce

Odtworzyliśmy to na PostgreSQL 16.4. Dwie tabele, orders_ff100 i orders_ff70, mają po 100 000 wierszy, klucz główny i indeks na kolumnie customer_id. Różnią się tylko parametrem fillfactor: 100 (wartość domyślna) i 70. W pięciu rundach zmieniamy po 10% wierszy, wyłącznie w kolumnach bez indeksu:

UPDATE orders_ff100 SET status = 'paid', amount = amount + 1
WHERE id % 10 = 0;
-- kolejne rundy: id % 10 = 1, 2, 3, 4

Już pierwsza runda na tabeli z fillfactor 100 nie dała ani jednego HOT update na 10 000 zmian. Tabela urosła z 6672 kB do 7336 kB, a indeks na customer_id z 1000 kB do 1104 kB, choć liczba wierszy się nie zmieniła. Po pięciu rundach liczniki wyglądają tak:

SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd
FROM pg_stat_user_tables
WHERE relname IN ('orders_ff100', 'orders_ff70')
ORDER BY relname;

   relname    | n_tup_upd | n_tup_hot_upd | n_tup_newpage_upd
--------------+-----------+---------------+-------------------
 orders_ff100 |     50000 |         37500 |             12500
 orders_ff70  |     50000 |         50000 |                 0

Tabela z fillfactor 70 przyjęła wszystkie 50 000 zmian jako HOT update i po pięciu rundach miała ten sam rozmiar co po załadowaniu (9640 kB), a jej indeksy się nie zmieniły. Tabela z fillfactor 100 urosła do 7504 kB, a 12 500 nowych wersji wierszy trafiło do innych bloków. Kolumna n_tup_newpage_upd liczy właśnie takie zmiany i każda z nich jest zwykłym UPDATE, nie HOT.

Zobacz też: Szkolenie Administracja, replikacja i tuning baz danych PostgreSQL

Liczniki powyżej pochodzą z przebiegu bez autovacuum między rundami. W powtórce, w której autovacuum przeszedł raz, po pierwszej rundzie, tabela z fillfactor 100 miała 38 333 HOT update z 50 000 i urosła do 7448 kB. Wzrost z pierwszej rundy został, a tabela z fillfactor 70 nie urosła w żadnym przebiegu.

Dlaczego tak się dzieje

PostgreSQL nie zmienia wiersza w miejscu. Każdy UPDATE zapisuje nową wersję wiersza (MVCC, czyli wielowersyjność danych), a stara zostaje, dopóki potrzebuje jej jakaś transakcja. Jeśli nowa wersja mieści się w tym samym bloku 8 kB, a UPDATE nie zmienia żadnej kolumny użytej w indeksach tabeli (wyjątkiem są indeksy podsumowujące, w standardowej dystrybucji to BRIN), zachodzi HOT update. Indeksy nie dostają wtedy nowych wpisów, a starą wersję da się usunąć w trakcie zwykłej pracy, także przy SELECT, bez czekania na VACUUM.

Gdy w bloku brakuje miejsca, nowa wersja trafia do innego bloku i każdy indeks musi dostać wpis wskazujący na nią. Fillfactor 100 oznacza, że INSERT wypełnia blok do końca, więc pierwsza zmiana wiersza w pełnym bloku nie może być HOT. W kolejnych rundach część zmian była już HOT, bo w blokach zwalniało się miejsce po starszych wersjach wierszy. Autovacuum nie cofa wzrostu plików. Zwykły VACUUM usuwa martwe wersje w tabeli i indeksach i oznacza miejsce do ponownego użycia, ale nie oddaje go systemowi operacyjnemu, poza całkiem wolnymi blokami na końcu tabeli.

Mniejszy fillfactor zostawia w każdym bloku zapas na nowe wersje wierszy. Ceną jest większa tabela od pierwszego dnia: w naszym teście 9640 kB zamiast 6672 kB. HOT nie pomoże też wtedy, gdy często zmieniana kolumna ma indeks. W tym samym teście 10 000 zmian kolumny customer_id w tabeli z fillfactor 70 nie dało żadnego HOT update.

Jak to rozwiązać krok po kroku

1. Znajdź kandydatów, czyli tabele z dużą liczbą UPDATE i niskim udziałem HOT update (kolumna hot_pct to procent zmian HOT):

   SELECT relname, n_tup_upd, n_tup_hot_upd,
          n_tup_newpage_upd,
          round(100.0 * n_tup_hot_upd
                / nullif(n_tup_upd, 0), 1) AS hot_pct
   FROM pg_stat_user_tables
   WHERE n_tup_upd > 0
   ORDER BY n_tup_upd DESC
   LIMIT 10;

2. Żeby oceniać obecne zachowanie aplikacji, a nie historię sprzed lat, zresetuj liczniki tabeli i wróć do nich po tygodniu albo miesiącu: SELECT pg_stat_reset_single_table_counters('orders'::regclass);. Reset zeruje też licznik martwych wierszy (n_dead_tup), z którego korzysta autovacuum, więc od razu potem wykonaj ANALYZE orders;, które go odtworzy.
3. Sprawdź, które kolumny mają indeksy: SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';. Jeśli UPDATE regularnie zmienia kolumnę z indeksem, sam fillfactor nie wystarczy. Wtedy oceń, czy ten indeks jest potrzebny.
4. Ustaw zapas miejsca w blokach: ALTER TABLE orders SET (fillfactor = 85);. W lekcji kursu podajemy 85 jako bezpieczną wartość dla większości aplikacji, czyli 15% bloku zostaje na zmiany. Polecenie bierze blokadę SHARE UPDATE EXCLUSIVE, więc nie zatrzymuje odczytu ani zapisu, ale działa tylko na bloki zapisywane od teraz.
5. Przebuduj tabelę, żeby istniejące bloki dostały wolne miejsce: VACUUM FULL orders;. To polecenie trzyma blokadę ACCESS EXCLUSIVE przez cały czas pracy, więc uruchamiaj je w oknie serwisowym albo użyj rozszerzenia pg_repack, które przebudowuje tabelę online. W naszym teście po samym ALTER TABLE (zmiana ze 100 na 70) tylko 4 z 10 000 zmian były HOT update, a po VACUUM FULL wszystkie 10 000.

Jak sprawdzić, że zadziałało

Zaraz po przebudowie zresetuj liczniki tabeli i wykonaj ANALYZE jak w kroku 2, a po kolejnej porcji ruchu uruchom ponownie zapytanie z kroku 1. Udział HOT powinien wyraźnie wzrosnąć, a n_tup_newpage_upd rosnąć znacznie wolniej. Sprawdź też, czy ustawienie jest zapisane i jak zmienia się rozmiar:

SELECT relname, reloptions FROM pg_class WHERE relname = 'orders';

 relname |   reloptions
---------+-----------------
 orders  | {fillfactor=85}

SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size,
       pg_size_pretty(pg_indexes_size('orders')) AS indexes_size;

Zapisz oba rozmiary przed serią UPDATE i porównaj je po niej. Jeśli tabela i indeksy mają ten sam rozmiar, nowe wersje wierszy mieszczą się w miejscu zarezerwowanym w blokach, a autovacuum ma mniej do sprzątania w indeksach.

Powiązane problemy

• Jeśli chcesz zrozumieć, skąd w ogóle biorą się martwe wersje wierszy, przejdź do tekstu tabela puchnie mimo usuwania wierszy, czyli czym jest bloat.
• Zanim zaplanujesz przebudowę tabeli po zmianie fillfactor, zajrzyj do tekstu VACUUM vs VACUUM FULL, czyli czym się różnią i kiedy który.
• Gdy indeks na często zmienianej kolumnie blokuje HOT update, sprawdź według tekstu które indeksy są nieużywane i jak je znaleźć, czy ten indeks jest potrzebny.

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