Agresywne autovacuum na PostgreSQL

Próbuję zmusić PostgreSQL do agresywnego automatycznego odkurzania mojej bazy danych. Obecnie skonfigurowałem automatyczne odkurzanie w następujący sposób:

• autovacuum_vacuum_cost_delay = 0 # Wyłącz próżnię opartą na kosztach
• autovacuum_vacuum_cost_limit = 10000 # Wartość maksymalna
• autovacuum_vacuum_threshold = 50 # Wartość domyślna
• autovacuum_vacuum_scale_factor = 0.2 # Wartość domyślna

Zauważam, że automatyczne odkurzanie włącza się tylko wtedy, gdy baza danych nie jest obciążona, więc wpadam w sytuacje, w których jest o wiele więcej martwych krotek niż krotek na żywo. Przykład znajduje się w załączonym zrzucie ekranu. Jeden ze stołów ma 23 krotki na żywo, ale 16845 martwych krotek czeka na próżnię. To jest szalone!

Automatyczne odkurzanie uruchamia się, gdy zakończy się test i serwer bazy danych jest bezczynny, co nie jest tym, czego chcę, ponieważ chciałbym, aby automatyczne odkurzanie uruchamiało się, ilekroć liczba martwych krotek przekracza 20% krotek na żywo + 50, ponieważ baza danych została uruchomiona skonfigurowany. Automatyczne odkurzanie, gdy serwer jest bezczynny, jest dla mnie bezużyteczne, ponieważ oczekuje się, że serwer produkcyjny osiągnie 1000 aktualizacji / s przez dłuższy czas, dlatego potrzebuję automatycznego odkurzania, aby działać, nawet gdy serwer jest obciążony.

Czy czegoś mi brakuje? Jak wymusić uruchomienie automatycznego odkurzania, gdy serwer jest obciążony?

Aktualizacja

Czy to może być problem z blokowaniem? Tabele, o których mowa, to tabele podsumowań wypełniane za pomocą wyzwalacza po wstawieniu. Tabele te są zablokowane w trybie UDOSTĘPNIJ WIERSZ, aby zapobiec równoczesnym zapisom w tym samym wierszu.

Eelke prawie na pewno ma rację, że twoje blokowanie blokuje automatyczną próżnię. Autovacuum zostało zaprojektowane, aby celowo ustępować aktywności użytkownika. Jeśli te tabele są zablokowane, autovacuum nie może ich odkurzyć.

Jednak dla potomnych chciałem podać przykładowy zestaw ustawień dla hiperagresywnej autow próżni, ponieważ podane ustawienia nie do końca to robią. Pamiętaj jednak, że bardziej agresywne automatyczne odkurzanie raczej nie rozwiąże twojego problemu. Należy również pamiętać, że domyślne ustawienia automatycznego odkurzania opierają się na uruchomieniu ponad 200 uruchomień testowych przy użyciu DBT2 w poszukiwaniu optymalnej kombinacji ustawień, więc należy przyjąć, że wartości domyślne są dobre, chyba że masz uzasadniony powód, aby myśleć inaczej lub baza danych znajduje się znacznie poza główny nurt baz danych OLTP (np. mała baza danych, która pobiera 10 000 aktualizacji na sekundę lub hurtownia danych 3 TB).

Najpierw włącz rejestrowanie, aby sprawdzić, czy autovacuum robi to, co myślisz:

log_autovacuum_min_duration = 0

Następnie zróbmy więcej pracowników autovac i niech częściej sprawdzają tabele:

autovacuum_max_workers = 6
autovacuum_naptime = 15s

Obniżmy progi dla automatycznego odkurzania i automatycznej analizy, aby uruchomić wcześniej:

autovacuum_vacuum_threshold = 25
autovacuum_vacuum_scale_factor = 0.1

autovacuum_analyze_threshold = 10
autovacuum_analyze_scale_factor = 0.05 

Następnie sprawmy, by autovacuum było mniej przerywalne, aby kończyło się szybciej, ale kosztem większego wpływu na równoczesną aktywność użytkownika:

autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000

Jest twój pełny program generalnie agresywnego automatycznego odkurzania, który może być odpowiedni dla małej bazy danych, która otrzymuje bardzo wysoki poziom aktualizacji, ale może mieć zbyt duży wpływ na równoczesną aktywność użytkowników.

Zauważ też, że parametry autow próżni można regulować według tabeli , co prawie zawsze jest lepszą odpowiedzią na konieczność dostosowania zachowania autow próżni.

Ponownie jednak mało prawdopodobne jest, aby rozwiązać Twój prawdziwy problem.

Aby zobaczyć, które tabele w ogóle kwalifikują się do automatycznego odkurzania, można użyć następującego zapytania (na podstawie http://www.postgresql.org/docs/current/static/routine-vacuuming.html ). Należy jednak pamiętać, że zapytanie nie szuka ustawień specyficznych dla tabeli:

 SELECT psut.relname,
     to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum,
     to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') as last_autovacuum,
     to_char(pg_class.reltuples, '9G999G999G999') AS n_tup,
     to_char(psut.n_dead_tup, '9G999G999G999') AS dead_tup,
     to_char(CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
         + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
            * pg_class.reltuples), '9G999G999G999') AS av_threshold,
     CASE
         WHEN CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
             + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
                * pg_class.reltuples) < psut.n_dead_tup
         THEN '*'
         ELSE ''
     END AS expect_av
 FROM pg_stat_user_tables psut
     JOIN pg_class on psut.relid = pg_class.oid
 ORDER BY 1;

Tak, to jest problem z blokowaniem. Zgodnie z tą stroną (niepełna) VACUUM potrzebuje WYŁĄCZNEGO AKTUALIZACJI dostępu WYŁĄCZNIE, który jest blokowany przez używany poziom blokady.

Czy na pewno potrzebujesz tej blokady? PostgreSQL jest zgodny z ACID, więc jednoczesne zapisywanie w większości przypadków nie stanowi problemu, ponieważ PostgreSQL przerwie jedną z transakcji, jeśli dojdzie do naruszenia serializacji.

Możesz także zablokować wiersze, używając WYBIERZ DO AKTUALIZACJI, aby zablokować wiersze zamiast całej tabeli.

Inną alternatywą bez blokowania byłoby zastosowanie poziomu izolacji transakcji możliwego do serializacji. Może to jednak wpłynąć na wydajność innych transakcji i należy być przygotowanym na kolejne niepowodzenia serializacji.

Zwiększenie liczby procesów automatycznego odkurzania i skrócenie czasu przestoju prawdopodobnie pomoże. Oto konfiguracja PostgreSQL 9.1, której używam na serwerze, który przechowuje informacje o kopiach zapasowych, w wyniku czego uzyskuje się dużą aktywność wstawiania.

http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html

autovacuum_max_workers = 6              # max number of autovacuum subprocesses
autovacuum_naptime = 10         # time between autovacuum runs
autovacuum_vacuum_cost_delay = 20ms     # default vacuum cost delay for

Spróbuję również obniżyć, cost_delayaby odkurzanie było bardziej agresywne.

Mogę również przetestować automatyczne odkurzanie za pomocą pgbench.

http://wiki.postgresql.org/wiki/Pgbenchtesting

Przykład wysokiej rywalizacji:

Utwórz bazę danych bench_replication

pgbench -i -p 5433 bench_replication

Uruchom pgbench

pgbench -U postgres -p 5432 -c 64 -j 4 -T 600 bench_replication

Sprawdź status automatycznego odkurzania

psql
>\connect bench_replicaiton
bench_replication=# select schemaname, relname, last_autovacuum from pg_stat_user_tables;
 schemaname |     relname      |        last_autovacuum        
------------+------------------+-------------------------------
 public     | pgbench_branches | 2012-07-18 18:15:34.494932+02
 public     | pgbench_history  | 
 public     | pgbench_tellers  | 2012-07-18 18:14:06.526437+02
 public     | pgbench_accounts | 

Istniejący skrypt „kwalifikuj się do automatycznego odkurzania” jest bardzo przydatny, ale (jak słusznie stwierdzono) brakowało opcji specyficznych dla tabeli. Oto jego zmodyfikowana wersja, która uwzględnia te opcje:

WITH rel_set AS
(
    SELECT
        oid,
        CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1)
            WHEN '' THEN NULL
        ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1)::BIGINT
        END AS rel_av_vac_threshold,
        CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_scale_factor=', 2), ',', 1)
            WHEN '' THEN NULL
        ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_scale_factor=', 2), ',', 1)::NUMERIC
        END AS rel_av_vac_scale_factor
    FROM pg_class
) 
SELECT
    PSUT.relname,
    to_char(PSUT.last_vacuum, 'YYYY-MM-DD HH24:MI')     AS last_vacuum,
    to_char(PSUT.last_autovacuum, 'YYYY-MM-DD HH24:MI') AS last_autovacuum,
    to_char(C.reltuples, '9G999G999G999')               AS n_tup,
    to_char(PSUT.n_dead_tup, '9G999G999G999')           AS dead_tup,
    to_char(coalesce(RS.rel_av_vac_threshold, current_setting('autovacuum_vacuum_threshold')::BIGINT) + coalesce(RS.rel_av_vac_scale_factor, current_setting('autovacuum_vacuum_scale_factor')::NUMERIC) * C.reltuples, '9G999G999G999') AS av_threshold,
    CASE
        WHEN (coalesce(RS.rel_av_vac_threshold, current_setting('autovacuum_vacuum_threshold')::BIGINT) + coalesce(RS.rel_av_vac_scale_factor, current_setting('autovacuum_vacuum_scale_factor')::NUMERIC) * C.reltuples) < PSUT.n_dead_tup
        THEN '*'
    ELSE ''
    END AS expect_av
FROM
    pg_stat_user_tables PSUT
    JOIN pg_class C
        ON PSUT.relid = C.oid
    JOIN rel_set RS
        ON PSUT.relid = RS.oid
ORDER BY C.reltuples DESC;