Dostrajanie automatycznego czyszczenia na serwerze elastycznym Azure Database for PostgreSQL

Ten artykuł zawiera omówienie funkcji autovacuum dla usługi Azure Database for PostgreSQL oraz przewodniki dotyczące rozwiązywania problemów, które są dostępne do monitorowania problemów z nadmiarem danych w bazie i blokad funkcji autovacuum. Zawiera również informacje o tym, jak daleko baza danych znajduje się od sytuacji awaryjnej lub przekroczenia granic.

Uwaga / Notatka

W tym artykule opisano dostrajanie automatycznego czyszczenia dla wszystkich obsługiwanych wersji bazy danych PostgreSQL na serwerze elastycznym usługi Azure Database for PostgreSQL. Niektóre wymienione funkcje są specyficzne dla wersji (np vacuum_buffer_usage_limit . dla bazy danych PostgreSQL 16 i nowszych oraz autovacuum_vacuum_max_threshold dla bazy danych PostgreSQL 18 i nowszych).

Co to jest automatyczne czyszczenie?

Autovacuum to proces działający w tle w PostgreSQL, który automatycznie czyści martwe krotki i aktualizuje statystyki. Ułatwia utrzymanie wydajności bazy danych przez automatyczne uruchamianie dwóch kluczowych zadań konserwacji:

  • VACUUM — odzyskuje miejsce w plikach bazodanowych poprzez usuwanie martwych krotek i oznaczanie go jako możliwe do ponownego użycia przez PostgreSQL. Niekoniecznie zmniejsza rozmiar fizyczny plików bazy danych na dysku. Aby zwolnić miejsce z powrotem dla systemu operacyjnego, należy użyć operacji, które przepisują tabelę (na przykład VACUUM FULL lub pg_repack), co wiąże się z dodatkowymi kwestiami, takimi jak blokady wyłączne lub okna serwisowe.
  • ANALYZE — zbiera statystyki tabeli i indeksu używane przez planistę zapytań PostgreSQL do wybierania wydajnych planów wykonywania.

Aby zapewnić prawidłowe działanie autovacuum, ustaw parametr autovacuum na ON. Po włączeniu baza danych PostgreSQL automatycznie decyduje o tym, kiedy należy uruchomić narzędzie VACUUM lub ANALYZE w tabeli, zapewniając, że baza danych pozostaje wydajna i zoptymalizowana.

Mechanizm wewnętrzny autovacuum

Autovacuum odczytuje strony w poszukiwaniu martwych krotek. Jeśli nie znajdzie żadnych martwych krotek, autovacuum odrzuci stronę. Gdy autovacuum znajdzie martwe krotki, usuwa je. Koszt jest oparty na następujących parametrach:

Parameter Description
vacuum_cost_page_hit Koszt odczytywania strony, która jest już w udostępnionych buforach i nie wymaga odczytu z dysku. Wartość domyślna to 1.
vacuum_cost_page_miss Koszt pobrania strony, której nie ma w buforach współdzielonych. Wartość domyślna to 10.
vacuum_cost_page_dirty Koszt zapisu na stronie, gdy w niej znajdują się martwe krotki. Wartość domyślna to 20.

Ilość pracy wykonywanej przez autovacuum zależy od dwóch parametrów:

Parameter Description
autovacuum_vacuum_cost_limit Ilość pracy wykonywanej przez autovacuum za jednym razem.
autovacuum_vacuum_cost_delay Liczba milisekund, przez które autovacuum pozostaje uśpiony po osiągnięciu limitu kosztów określonego przez parametr autovacuum_vacuum_cost_limit.

We wszystkich aktualnie obsługiwanych wersjach bazy danych PostgreSQL wartość autovacuum_vacuum_cost_limit domyślna to 200 (wartość domyślna to -1, co sprawia, że jest równa wartości zwykłej vacuum_cost_limit, która domyślnie wynosi 200).

Wartość domyślna to autovacuum_vacuum_cost_delay 2 milisekundy w programie PostgreSQL w wersji 12 i nowszych (było to 20 milisekund w wersji 11).

Limit użycia buforu (PostgreSQL 16+)

Począwszy od bazy danych PostgreSQL w wersji 16, można użyć parametru vacuum_buffer_usage_limit do kontrolowania użycia pamięci podczas operacji VACUUM, ANALYZE i autovacuum.

Parameter Description
vacuum_buffer_usage_limit Ustawia rozmiar puli bufora dla operacji VACUUM, ANALYZE i autovacuum. Ten parametr ogranicza ilość udostępnionej pamięci podręcznej buforu, z którego mogą korzystać te operacje, uniemożliwiając im nadmierne wykorzystanie zasobów pamięci.

Ten parametr pomaga zapobiec eksmitowaniu zbyt wielu przydatnych stron z buforów współdzielonych przez VACUUM oraz autovacuum, co może poprawić całkowitą wydajność bazy danych podczas operacji konserwacyjnych. Wartość domyślna jest zwykle ustawiana na podstawie shared_buffers, i można ją skonfigurować tak, aby równoważyć wydajność odkurzania z potrzebami regularnych operacji bazy danych.

Maksymalny próg automatycznego czyszczenia (PostgreSQL 18+)

Począwszy od wersji 18 PostgreSQL, można użyć parametru autovacuum_vacuum_max_threshold, aby ustawić górny limit liczby aktualizacji lub usunięć rekordów, które wyzwalają autovacuum.

Parameter Description
autovacuum_vacuum_max_threshold Ustawia maksymalną liczbę aktualizacji lub usunięć krotek przed odkurzaniem. Po ustawieniu na -1, maksymalny próg jest wyłączony. Ten parametr służy do precyzyjnej kontroli nad wyzwalaniem autovacuum w bardzo dużych tabelach.

Ten parametr jest szczególnie przydatny w przypadku dużych tabel, gdzie domyślne wyzwalanie oparte na współczynniku skalowania może spowodować, że autovacuum będzie zbyt długo czekać przed rozpoczęciem działania.

Autovacuum budzi się 50 razy (50*20 ms=1000 ms) co sekundę. Za każdym razem, gdy autovacuum się uruchamia, odczytuje 200 stron.

Oznacza to, że w jednej sekundzie funkcja automatycznego czyszczenia może wykonywać następujące czynności:

  • ~80 MB/s [ (200 stronach/vacuum_cost_page_hit) * 50 * 8 KB na stronę] jeśli wszystkie strony z martwymi krotkami znajdują się w buforach współdzielonych.
  • ~8 MB/s [ (200 stron/vacuum_cost_page_miss) * 50 * 8 KB na stronę], jeśli wszystkie strony z martwymi krotkami są odczytywane z dysku.
  • ~4 MB/s [ (200 stron/vacuum_cost_page_dirty) * 50 * 8 KB na stronę] autovacuum może zapisywać z prędkością do 4 MB/s.

Monitoruj autovacuum

Usługa Azure Database for PostgreSQL udostępnia następujące metryki monitorowania automatycznego odkurzania.

Metryki autovacuum mogą służyć do monitorowania i dostrajania wydajności autovacuum dla elastycznego serwera usługi Azure Database for PostgreSQL. Każda metryka jest emitowana w 30-minutowym interwale i ma do 93 dni przechowywania. Możesz utworzyć alerty dla określonych metryk i podzielić i filtrować dane metryk przy użyciu DatabaseName wymiaru.

Jak włączyć metryki autovacuum

  • Metryki autovacuum są domyślnie wyłączone.
  • Aby włączyć te metryki, ustaw parametr metrics.autovacuum_diagnostics na ON.
  • Ten parametr jest dynamiczny, więc ponowne uruchomienie wystąpienia nie jest wymagane.

Lista metryk autovacuum

nazwa wyświetlana Identyfikator metryki Unit Description Wymiar Domyślnie włączone
Analiza tabel licznikowych użytkowników analyze_count_user_tables Liczba Liczba razy tabele tylko dla użytkowników zostały ręcznie przeanalizowane w tej bazie danych. DatabaseName Nie.
Tabele użytkowników liczników AutoAnalyze autoanalyze_count_user_tables Liczba Ile razy tabele należące wyłącznie do użytkownika zostały przeanalizowane przez demona autovacuum w tej bazie danych. DatabaseName Nie.
Tabele użytkowników AutoVacuum autovacuum_count_user_tables Liczba Ile razy tabele tylko dla użytkownika zostały opróżnione przez demona automatycznego czyszczenia w tej bazie danych. DatabaseName Nie.
Procent wzdęć (wersja zapoznawcza) bloat_percent Procent Szacowany procent nadmiaru danych w tabelach użytkownika. DatabaseName Nie.
Szacowana liczba martwych wierszy w tabelach użytkownika n_dead_tup_user_tables Liczba Szacowana liczba utraconych wierszy dla tabel tylko dla użytkowników w tej bazie danych. DatabaseName Nie.
Szacowana liczba wierszy aktywnych w tabelach użytkowników n_live_tup_user_tables Liczba Szacowana liczba aktywnych wierszy dla tabel tylko dla użytkowników w tej bazie. DatabaseName Nie.
Szacowane modyfikacje tabel użytkowników n_mod_since_analyze_user_tables Liczba Szacowana liczba wierszy, które zostały zmodyfikowane od czasu ostatniego przeanalizowania tabel tylko dla użytkownika. DatabaseName Nie.
Przeanalizowane tabele użytkowników tables_analyzed_user_tables Liczba Liczba tabel tylko dla użytkowników, które zostały przeanalizowane w tej bazie danych. DatabaseName Nie.
Automatycznie analizowane tabele użytkowników tables_autoanalyzed_user_tables Liczba Liczba tabel tylko dla użytkownika, które zostały przeanalizowane przez demona automatycznego czyszczenia w tej bazie danych. DatabaseName Nie.
Automatyczne odkurzanie tabel użytkowników tables_autovacuumed_user_tables Liczba Liczba tabel tylko dla użytkownika, które zostały opróżnione przez demona automatycznego czyszczenia w tej bazie danych. DatabaseName Nie.
Licznik tabel użytkowników tables_counter_user_tables Liczba Liczba tabel tylko dla użytkowników w tej bazie danych. DatabaseName Nie.
Wyczyszczono tabele użytkowników tables_vacuumed_user_tables Liczba Liczba tabel tylko dla użytkowników, które zostały opróżnione w tej bazie danych. DatabaseName Nie.
Tabele użytkowników licznika próżni vacuum_count_user_tables Liczba Ile razy tabele tylko dla użytkownika zostały ręcznie opróżnione w tej bazie danych (nie licząc VACUUM FULL). DatabaseName Nie.

Zagadnienia dotyczące korzystania z metryk autovacuum

  • Metryki autovacuum korzystające z wymiaru DatabaseName mają ograniczenie do 30 baz danych.
  • W jednostce Burstable SKU limit wynosi 10 baz danych dla metryk używających wymiaru DatabaseName.
  • Limit wymiarów DatabaseName jest stosowany w kolumnie OID, która odzwierciedla kolejność tworzenia bazy danych.

Aby uzyskać więcej informacji, zobacz Metryki automatycznego czyszczenia (Autovacuum Metrics).

Użyj następujących zapytań, aby monitorować autovacuum:

select schemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup::float/n_live_tup::float*100) dead_pct,autovacuum_count,last_vacuum,last_autovacuum,last_autoanalyze,last_analyze from pg_stat_all_tables where n_live_tup >0;

Poniższe kolumny pomagają określić, czy autovacuum nadąża za aktywnością tabeli.

Parameter Description
dead_pct Procent martwych krotek w porównaniu z żywymi krotkami.
last_autovacuum Data ostatniego automatycznego odkurzania tabeli.
last_autoanalyze Data ostatniej analizy tabeli.

Wyzwalanie automatycznego czyszczenia

Akcja automatycznego czyszczenia (ANALYZE lub VACUUM) jest wyzwalana, gdy liczba martwych krotek przekracza pewną wartość. Ta liczba zależy od dwóch czynników: całkowitej liczby wierszy w tabeli oraz stałego progu. Funkcja ANALYZE jest domyślnie wyzwalana, gdy zmieni się 10% tabeli plus 50 wierszy, podczas gdy funkcja VACUUM jest wyzwalana, gdy nastąpi zmiana 20% tabeli plus 50 wierszy. Ponieważ próg dla VACUUM jest dwa razy wyższy niż dla ANALYZE, ANALYZE wykonuje się wcześniej niż VACUUM.

W przypadku bazy danych PostgreSQL w wersji 13 lub nowszej funkcja ANALYZE jest domyślnie wyzwalana, gdy wystąpi 20% tabeli oraz 1000 wstawień wierszy.

Dokładne równania dla każdej akcji to:

  • Autoanaliza = autovacuum_analyze_scale_factor * tupy + autovacuum_analyze_threshold lub autovacuum_vacuum_insert_scale_factor * tupy + autovacuum_vacuum_insert_threshold (PostgreSQL wersja 13 i późniejsze)
  • Autovacuum = autovacuum_vacuum_scale_factor * krotki + autovacuum_vacuum_threshold

Na przykład, gdy masz tabelę z 100 wierszami, można zobaczyć w następujących równaniach, kiedy uruchamiają się akcje analizy i odkurzania danych.

W przypadku aktualizacji i usuwania: Autoanalyze = 0.1 * 100 + 50 = 60Autovacuum = 0.2 * 100 + 50 = 70

Operacja ANALYZE jest uruchamiana po zmianie 60 wierszy w tabeli, a operacja VACUUM po zmianie 70 wierszy w tabeli.

Do wstawień: Autoanalyze = 0.2 * 100 + 1000 = 1020

ANALYZE uruchamia się po wstawieniu 1 020 wierszy do tabeli.

Oto opis parametrów używanych w równaniu:

Parameter Description
autovacuum_analyze_scale_factor Procent wstawek, aktualizacji i usunięć, który wyzwala ANALYZE w tabeli.
autovacuum_analyze_threshold Minimalna liczba krotek wstawionych, zaktualizowanych lub usuniętych do ANALYZY tabeli.
autovacuum_vacuum_insert_scale_factor Procent wstawek, które wyzwalają ANALYZE na tabeli.
autovacuum_vacuum_insert_threshold Minimalna liczba krotek wstawionych do analizy tabeli ANALYZE.
autovacuum_vacuum_scale_factor Procent aktualizacji i usunięć, który uruchamia VACUUM na tabeli.

Użyj następującego zapytania, aby wyświetlić listę tabel w bazie danych i zidentyfikować tabele, które kwalifikują się do procesu automatycznego czyszczenia:

 SELECT *
      ,n_dead_tup > av_threshold AS av_needed
      ,CASE
        WHEN reltuples > 0
          THEN round(100.0 * n_dead_tup / (reltuples))
        ELSE 0
        END AS pct_dead
    FROM (
      SELECT N.nspname
        ,C.relname
        ,pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins
        ,pg_stat_get_tuples_updated(C.oid) AS n_tup_upd
        ,pg_stat_get_tuples_deleted(C.oid) AS n_tup_del
        ,pg_stat_get_live_tuples(C.oid) AS n_live_tup
        ,pg_stat_get_dead_tuples(C.oid) AS n_dead_tup
        ,C.reltuples AS reltuples
        ,round(current_setting('autovacuum_vacuum_threshold')::INTEGER + current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * C.reltuples) AS av_threshold
        ,date_trunc('minute', greatest(pg_stat_get_last_vacuum_time(C.oid), pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum
        ,date_trunc('minute', greatest(pg_stat_get_last_analyze_time(C.oid), pg_stat_get_last_autoanalyze_time(C.oid))) AS last_analyze
      FROM pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE C.relkind IN (
          'r'
          ,'t'
          )
        AND N.nspname NOT IN (
          'pg_catalog'
          ,'information_schema'
          )
        AND N.nspname !~ '^pg_toast'
      ) AS av
    ORDER BY av_needed DESC ,n_dead_tup DESC;

Uwaga / Notatka

Zapytanie nie bierze pod uwagę, że można skonfigurować autovacuum dla pojedynczych tabel przy użyciu polecenia DDL "alter table".

Typowe problemy z autovacuum

Zapoznaj się z poniższą listą typowych problemów z procesem autovacuum.

Brak możliwości nadążenia za przeciążonym serwerem

Proces automatycznego czyszczenia szacuje koszt każdej operacji we/wy, gromadzi sumę dla każdej wykonywanej operacji i wstrzymuje się po osiągnięciu górnego limitu kosztów. Proces używa dwóch parametrów: autovacuum_vacuum_cost_delay i autovacuum_vacuum_cost_limit.

Domyślnie wartość autovacuum_vacuum_cost_limit jest ustawiona na -1, co oznacza, że limit kosztów autovacuum używa tej samej wartości co parametr vacuum_cost_limit. Wartość domyślna to vacuum_cost_limit 200. vacuum_cost_limit reprezentuje koszt ręcznego czyszczenia.

Jeśli ustawisz autovacuum_vacuum_cost_limit wartość -1, funkcja automatycznego czyszczenia używa parametru vacuum_cost_limit . Jeśli ustawisz autovacuum_vacuum_cost_limit wartość większą niż -1, funkcja automatycznego czyszczenia używa parametru autovacuum_vacuum_cost_limit .

Jeśli funkcja automatycznego czyszczenia nie nadąża, rozważ zmianę następujących parametrów:

Parameter Description
autovacuum_vacuum_cost_limit Wartość domyślna: 200. Możesz zwiększyć limit kosztów. Monitoruj wykorzystanie procesora CPU i operacji wejścia/wyjścia w bazie danych przed i po wprowadzeniu zmian.
autovacuum_vacuum_cost_delay PostgreSQL w wersji 12 lub nowszej — ustawienie domyślne: 2 ms. Możesz zmniejszyć tę wartość, aby uzyskać bardziej agresywne automatyczne odkurzanie.
vacuum_buffer_usage_limit PostgreSQL w wersji 16 i nowszych — ustawia rozmiar puli buforów dla operacji VACUUM i autovacuum. Dostosowanie tego parametru może pomóc zrównoważyć wydajność automatycznego czyszczenia z ogólną wydajnością systemu, kontrolując, ile współużytkowanej pamięci podręcznej buforu jest używane podczas operacji opróżniania.

Uwaga / Notatka

  • Wartość autovacuum_vacuum_cost_limit jest dystrybuowana proporcjonalnie do uruchomionych procesów automatycznego czyszczenia. Jeśli istnieje więcej niż jeden proces roboczy, suma limitów dla każdego procesu roboczego nie przekracza wartości parametru autovacuum_vacuum_cost_limit .
  • autovacuum_vacuum_scale_factor to inny parametr, który może wyzwalać próżniowanie na tabeli na podstawie gromadzenia martwych krotek. Ustawienie domyślne: 0.2, Dozwolony zakres: 0.05 - 0.1. Współczynnik skalowania jest specyficzny dla obciążenia i powinien być ustawiany w zależności od ilości danych w tabelach. Przed zmianą wartości zbadaj obciążenie i poszczególne woluminy tabeli.

Autovacuum stale działa

Jeśli autovacuum działa ciągle, może to wpływać na wykorzystanie procesora i operacje wejścia/wyjścia na serwerze. Oto kilka możliwych powodów:

maintenance_work_mem

Demon autovacuum używa autovacuum_work_mem, która jest domyślnie ustawiona na -1. To ustawienie domyślne oznacza, że autovacuum_work_mem używa tej samej wartości co maintenance_work_mem parametr. W tym artykule przyjęto założenie, że autovacuum_work_mem jest ustawione na -1 i demon autovacuum używa maintenance_work_mem.

Jeśli maintenance_work_mem jest niski, możesz zwiększyć go do 2 GB na elastycznym serwerze usługi Azure Database for PostgreSQL. Ogólna zasada jest taka, aby przydzielać 50 MB dla maintenance_work_mem na każdy 1 GB pamięci RAM.

Duża liczba baz danych

Autovacuum próbuje uruchomić proces roboczy w każdej bazie danych co autovacuum_naptime sekund.

Jeśli na przykład serwer ma 60 baz danych i autovacuum_naptime jest ustawiony na 60 sekund, proces roboczy automatycznego czyszczenia uruchamia się co sekundę [autovacuum_naptime/liczba baz danych].

Jeśli w klastrze znajduje się więcej baz danych, zwiększ wartość autovacuum_naptime. Aby jednocześnie uczynić operację automatycznego czyszczenia bardziej agresywną, zwiększ parametry autovacuum_cost_limit i zmniejsz autovacuum_cost_delay. Można również zwiększyć autovacuum_max_workers wartość z wartości domyślnej 3 do 4 lub 5.

Błędy związane z brakiem pamięci

Zbyt agresywne maintenance_work_mem ustawienia mogą okresowo powodować błędy braku pamięci w systemie. Przed zmianą parametru maintenance_work_mem zapoznaj się z dostępną pamięcią RAM na serwerze.

Autovacuum powoduje zbyt duże zakłócenia

Jeśli funkcja automatycznego czyszczenia zużywa zbyt wiele zasobów, spróbuj wykonać następujące czynności:

Parametry automatycznego czyszczenia

Oceń parametry autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limiti autovacuum_max_workers. Nieprawidłowe ustawienie parametrów autovacuum może prowadzić do sytuacji, w których autovacuum staje się zbyt uciążliwe.

Jeśli autovacuum jest zbyt uciążliwy, rozważ następujące działania:

  • Zwiększ autovacuum_vacuum_cost_delay i zmniejsz autovacuum_vacuum_cost_limit , jeśli ustawisz go wyżej niż wartość domyślna 200.
  • Zmniejsz liczbę parametrów autovacuum_max_workers , jeśli ustawisz ją wyższą niż wartość domyślna 3.

Zbyt wiele procesów roboczych automatycznego czyszczenia

Zwiększenie liczby procesów roboczych automatycznego czyszczenia nie zwiększa szybkości próżni. Nie używaj dużej liczby pracowników autovacuum.

Zwiększenie liczby procesów roboczych automatycznego czyszczenia powoduje większe zużycie pamięci. W zależności od wartości parametru maintenance_work_memmoże to spowodować obniżenie wydajności.

Każdy proces roboczy autovacuum otrzymuje tylko (1/autovacuum_max_workers) całkowitej wartości autovacuum_cost_limit, więc duża liczba procesów roboczych sprawia, że każdy z nich działa wolniej.

Jeśli zwiększysz liczbę pracowników, zwiększ autovacuum_vacuum_cost_limit i/lub zmniejsz autovacuum_vacuum_cost_delay, aby proces próżniowy był szybszy.

Jeśli jednak ustawisz parametr na poziomie autovacuum_vacuum_cost_delay tabeli lub autovacuum_vacuum_cost_limit parametrach, procesy robocze uruchomione w tych tabelach nie będą uwzględniane w algorytmie równoważenia [autovacuum_cost_limit/autovacuum_max_workers].

Ochrona przed zawijaniem identyfikatora transakcji (TXID) przez autovacuum

Gdy baza danych przechodzi do ochrony identyfikatora transakcji wraparound, zostanie wyświetlony komunikat o błędzie podobny do następującego błędu:

Database isn't accepting commands to avoid wraparound data loss in database 'xx'
Stop the postmaster and vacuum that database in single-user mode.

Uwaga / Notatka

Ten komunikat o błędzie to wieloletnie przeoczenie. Zazwyczaj nie trzeba przełączać się do trybu pojedynczego użytkownika. Zamiast tego możesz uruchomić wymagane polecenia VACUUM i wykonać dostrajanie, aby polecenie VACUUM działało szybko. Chociaż nie można uruchomić żadnego języka manipulowania danymi (DML), nadal można uruchomić polecenie VACUUM.

Problem przepełnienia występuje, gdy baza danych nie jest odkurzona lub gdy autovacuum nie usuwa zbyt wielu martwych krotek.

Możliwe przyczyny tego problemu obejmują następujące przyczyny:

Duże obciążenie

Duże obciążenie powoduje zbyt wiele martwych krotek w krótkim okresie, co utrudnia automatyczne czyszczenie zaległości. Martwe krotki w systemie gromadzą się z czasem, co prowadzi do pogorszenia wydajności zapytań oraz do sytuacji wraparound. Jedną z przyczyn tej sytuacji może być to, że parametry autovacuum nie są odpowiednio ustawione i proces ten nie nadąża za obciążeniem mocno obciążonego serwera.

Długotrwałe transakcje

Każda długotrwała transakcja w systemie uniemożliwia autovacuum usunięcie martwych krotek. Stanowią przeszkodę dla procesu próżniowego. Usunięcie długotrwałych transakcji umożliwia usunięcie martwych krotek, gdy działa autovacuum.

Długotrwałe transakcje można wykryć przy użyciu następującego zapytania:

    SELECT pid, age(backend_xid) AS age_in_xids,
    now () - xact_start AS xact_age,
    now () - query_start AS query_age,
    state,
    query
    FROM pg_stat_activity
    WHERE state != 'idle'
    ORDER BY 2 DESC
    LIMIT 10;

Przygotowane instrukcje

Jeśli istnieją przygotowane instrukcje, które nie zostały zatwierdzone, uniemożliwiają autovacuum usunięcie martwych krotek. Następujące zapytanie pomaga znaleźć niezatwierdzone przygotowane instrukcje:

    SELECT gid, prepared, owner, database, transaction
    FROM pg_prepared_xacts
    ORDER BY age(transaction) DESC;

Użyj COMMIT PREPARED lub ROLLBACK PREPARED, aby zatwierdzić lub wycofać te instrukcje.

Nieużywane miejsca replikacji

Nieużywane miejsca replikacji uniemożliwiają automatyczne wyczyszczanie martwych krotek. Następujące zapytanie pomaga zidentyfikować nieużywane miejsca replikacji:

    SELECT slot_name, slot_type, database, xmin
    FROM pg_replication_slots
    ORDER BY age(xmin) DESC;

Użyj pg_drop_replication_slot() polecenia , aby usunąć nieużywane miejsca replikacji.

Gdy uruchamia się mechanizm ochrony przed zawinięciem identyfikatorów transakcji w bazie danych, sprawdź, czy nie ma żadnych procesów blokujących, jak wspomniano wcześniej, i usuń je ręcznie, aby autovacuum mogło kontynuować działanie i się zakończyć. Możesz również zwiększyć szybkość działania autovacuum, ustawiając autovacuum_cost_delay na 0 i zwiększając wartość autovacuum_cost_limit do poziomu wyższego niż 200. Jednak zmiany tych parametrów nie mają zastosowania do istniejących procesów automatycznego czyszczenia. Albo uruchom ponownie bazę danych, albo ręcznie zakończ istniejące procesy robocze, aby zastosować zmiany parametrów.

Wymagania specyficzne dla tabeli

Możesz ustawić parametry autovacuum dla poszczególnych tabel. Te ustawienia są szczególnie ważne w przypadku małych i dużych tabel. Na przykład, dla małej tabeli zawierającej tylko 100 wierszy, autovacuum wyzwala operację VACUUM, gdy zmieni się 70 wierszy (jak obliczono wcześniej). Jeśli często aktualizujesz tę tabelę, możesz zobaczyć setki operacji automatycznego czyszczenia dziennie. Te operacje uniemożliwiają automatyczne czyszczenie pozostałych tabel, w których wartość procentowa zmian nie jest tak znacząca. Alternatywnie tabela zawierająca miliard wierszy musi zmienić 200 milionów wierszy, aby wyzwolić operacje automatycznego czyszczenia. Odpowiednie ustawienie parametrów autovacuum zapobiega takim sytuacjom.

Aby ustawić ustawienia automatycznego czyszczenia dla każdej tabeli, zmień parametry, jak pokazano w poniższych przykładach:

    ALTER TABLE <table name> SET (autovacuum_analyze_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_analyze_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_delay = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_limit = xx);
    -- For PostgreSQL 16 and later:
    ALTER TABLE <table name> SET (vacuum_buffer_usage_limit = 'xx MB');

Obciążenia obejmujące wyłącznie wstawianie

W programie PostgreSQL w wersji 13 lub starszej funkcja automatycznego czyszczenia nie jest uruchamiana w tabelach z obciążeniem tylko do wstawiania, ponieważ nie ma martwych krotek i nie ma wolnego miejsca, które należy odzyskać. Jednak automatyczna analiza jest uruchamiana dla obciążeń obejmujących wyłącznie operacje wstawiania, ponieważ pojawiają się nowe dane. Wady tego zachowania to:

  • Mapa widoczności tabel nie jest aktualizowana, a w związku z tym wydajność zapytań, szczególnie w przypadku skanowania tylko indeksu, zaczyna cierpieć w czasie.
  • Baza danych może wejść w stan zabezpieczenia przed zawinięciem identyfikatora transakcji.
  • Bity podpowiedzi nie są ustawione.

Solutions

PostgreSQL w wersji 13 i starszych

Za pomocą rozszerzenia pg_cron można skonfigurować zadanie cron w celu zaplanowania okresowej analizy próżni w tabeli. Częstotliwość zadania cron zależy od obciążenia.

Aby uzyskać wskazówki, zobacz specjalne zagadnienia dotyczące używania pg_cron w usłudze Azure Database for PostgreSQL.

PostgreSQL 13 i nowsze wersje

Autovacuum działa na tabelach z obciążeniem obejmującym wyłącznie operacje wstawiania. Dwa parametry, autovacuum_vacuum_insert_threshold i autovacuum_vacuum_insert_scale_factor, pomagają określić, kiedy autovacuum może zostać uruchomiony w tabelach, do których wykonywane są wyłącznie operacje wstawiania.

Przewodniki rozwiązywania problemów

Elastyczny serwer usługi Azure Database for PostgreSQL zawiera przewodniki rozwiązywania problemów w portalu, które ułatwiają monitorowanie nadmiaru na poziomie bazy danych lub indywidualnego schematu oraz identyfikowanie potencjalnych utrudnień dla procesu autovacuum.

Dostępne są dwa przewodniki rozwiązywania problemów:

  • Monitorowanie Autovacuum — ten przewodnik służy do monitorowania rozrostu na poziomie schematu lub całej bazy danych.
  • Blokady automatycznego czyszczenia i zawijania — ten przewodnik pomaga zidentyfikować potencjalne blokady automatycznego czyszczenia i zawiera informacje na temat tego, jak daleko bazy danych na serwerze pochodzą z zawijania lub sytuacji awaryjnych.

Przewodniki rozwiązywania problemów udostępniają również zalecenia, aby rozwiązać potencjalne problemy. Aby uzyskać informacje o sposobie konfigurowania i używania przewodników rozwiązywania problemów, zobacz przewodniki rozwiązywania problemów z konfiguracją.

Zakończenie procesu automatycznego odkurzania: rola pg_signal_autovacuum_worker

Automatyczne odkurzanie jest ważnym procesem w tle, ponieważ sprzyja efektywnemu magazynowaniu danych oraz utrzymaniu wydajności bazy danych. W normalnym procesie autovacuum, anuluje się po deadlock_timeout. Jeśli użytkownik wykonuje instrukcję DDL w tabeli, może być konieczne odczekanie interwału deadlock_timeout . Autovacuum nie pozwala na wykonywanie odczytów ani zapisów w tabeli przez niezależne żądania połączeń, co zwiększa opóźnienie w transakcji.

Wprowadziliśmy nową rolę pg_signal_autovacuum_worker w PostgreSQL, która umożliwia członkom niebędącym superużytkownikami przerywanie trwającego zadania autovacuum. Nowa rola pomaga użytkownikom uzyskać bezpieczny i kontrolowany dostęp do procesu autovacuum. Użytkownicy niebędący superużytkownikami mogą anulować proces automatycznego czyszczenia po przyznaniu roli pg_signal_autovacuum_worker za pomocą polecenia pg_terminate_backend. Rola pg_signal_autovacuum_worker jest dostępna w usłudze Azure Database for PostgreSQL w wersji 15 lub nowszej.

W rzadkich scenariuszach, takich jak automatyczne wraparound autovacuum, procesy robocze mogą zostać uruchomione ponownie natychmiast po zakończeniu, ponieważ mają krytyczne znaczenie dla zapobiegania wyczerpaniu identyfikatorów transakcji. Aby zminimalizować powtarzające się konflikty, wykonaj następujące kroki:

  • Umieść operację DDL w kolejce przed zakończeniem.

    • Sesja 1. Przygotowanie i uruchomienie instrukcji DDL.

    • Sesja 2. Zakończenie procesu automatycznego czyszczenia.

      Ważne

      Te dwa kroki muszą być wykonane bezpośrednio po sobie. Jeśli instrukcja DDL pozostaje zablokowana zbyt długo, może ona przechowywać blokady i blokować inne operacje DML na serwerze.

  • Zakończ automatyczne czyszczenie i wykonaj DDL: jeśli DDL musi działać natychmiast:

    • Zakończ proces autovacuum przy użyciu pg_terminate_backend().
    • Wykonaj instrukcję DDL bezpośrednio po zakończeniu.

Kroki umożliwiające uniknięcie powtarzających się konfliktów:

  1. Udzielanie roli użytkownikowi

    GRANT pg_signal_autovacuum_worker TO app_user;
    1. Identyfikowanie identyfikatora procesu automatycznego czyszczenia
    SELECT pid, query FROM pg_stat_activity WHERE query LIKE '%autovacuum%' and pid!=pg_backend_pid();

  2. Kończenie automatycznego czyszczenia

    SELECT pg_terminate_backend(<pid>);

  3. Wykonaj instrukcję DDL natychmiast

    ALTER TABLE my_table ADD COLUMN new_col TEXT;

Uwaga / Notatka

Nie zalecamy przerywania trwających procesów automatycznego odkurzania, ponieważ może to prowadzić do nadmiernej objętości tabel i bazy danych, co z kolei może prowadzić do pogorszenia wydajności. Jednak w przypadkach, gdy istnieje wymaganie krytyczne dla działania firmy obejmujące zaplanowane wykonanie instrukcji DDL, która pokrywa się z procesem automatycznego czyszczenia, użytkownicy niebędący superużytkownikami mogą zakończyć automatyczne czyszczenie w kontrolowany i bezpieczny sposób przy użyciu pg_signal_autovacuum_worker roli.

Rekomendacje usługi Azure Advisor

Rekomendacje usługi Azure Advisor aktywnie identyfikują, czy serwer ma wysoki wskaźnik nadmiaru lub czy serwer zbliża się do scenariusza przekroczenia limitu transakcji. Możesz również utworzyć alerty usługi Azure Advisor dla zaleceń.

Zalecenia są następujące:

  • Wysoki współczynnik wzdęć: Wysoki współczynnik wzdęć może wpływać na wydajność serwera na kilka sposobów. Jednym z znaczących problemów jest to, że optymalizator aparatu PostgreSQL może mieć trudności z wybraniem najlepszego planu wykonania, co prowadzi do obniżenia wydajności zapytań. W związku z tym zalecenie jest wyzwalane, gdy procent wzdęć na serwerze osiągnie określony próg, aby uniknąć takich problemów z wydajnością.

  • Zawijanie transakcji: ten scenariusz jest jednym z najpoważniejszych problemów, które może napotkać serwer. Gdy serwer znajduje się w tym stanie, może przestać akceptować nowe transakcje, co powoduje, że serwer przechodzi w tryb tylko do odczytu. W związku z tym zalecenie jest wyzwalane, gdy serwer przekroczy próg 1 mld transakcji.

Treści powiązane

  • Last updated on