Kiedy VACUUM nie działa — czyścimy tabelę ręcznie

VACUUM może "wyczyścić" z tabeli w PostgreSQL tylko to, co nikt nie może zobaczyć — to znaczy, że nie ma żadnego aktywnego zapytania, które rozpoczęło się wcześniej niż te rekordy zostały zmienione.

A co jeśli taki niemiły typ (długoterminowe obciążenie OLAP na bazie OLTP) jednak istnieje? Jak wyczyścić aktywnie zmieniającą się tabelę w otoczeniu długich zapytań i nie nastąpić na grabie?

Rozkładamy grabie

Najpierw określimy, na czym polega i jak w ogóle może powstać problem, który chcemy rozwiązać.

Zazwyczaj taka sytuacja występuje na stosunkowo małej tabeli, ale w której zachodzi bardzo wiele zmian. Zazwyczaj są to albo różne liczniki/agregaty/rankingi, na których często wykonuje się UPDATE, albo bufor-kolejka do przetwarzania jakiegoś ciągłego strumienia wydarzeń, do których informacje są ciągle INSERT/DELETE.

Spróbujmy odtworzyć wariant z rankingami:

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- będziemy budować ranking według tego indeksu

INSERT INTO
  tbl
SELECT
  chr(ascii('a'::text) + i) k
, 0 v
FROM
  generate_series(0, 25) i;

A równocześnie, w innym połączeniu, uruchamia się długie zapytanie, które zbiera jakąś skomplikowaną statystykę, ale nie dotyczące naszej tabeli:

SELECT pg_sleep(10000);

Teraz wiele, wiele razy aktualizujemy wartość jednego z liczników. Aby eksperyment był czysty, zrobimy to w oddzielnych transakcjach przy użyciu dblink, tak jak to będzie się działo w rzeczywistości:

DO $$
DECLARE
  i integer;
  tsb timestamp;
  tse timestamp;
  d double precision;
BEGIN
  PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port'));
  FOR i IN 1..10000 LOOP
    tsb = clock_timestamp();
    PERFORM dblink($e$UPDATE tbl SET v = v + 1 WHERE k = 'a';$e$);
    tse = clock_timestamp();
    IF i % 1000 = 0 THEN
      d = (extract('epoch' from tse) - extract('epoch' from tsb)) * 1000;
      RAISE NOTICE 'i = %, exectime = %', lpad(i::text, 5), lpad(d::text, 5);
    END IF;
  END LOOP;
  PERFORM dblink_disconnect();
END;
$$ LANGUAGE plpgsql;

NOTICE:  i =  1000, exectime = 0.524
NOTICE:  i =  2000, exectime = 0.739
NOTICE:  i =  3000, exectime = 1.188
NOTICE:  i =  4000, exectime = 2.508
NOTICE:  i =  5000, exectime = 1.791
NOTICE:  i =  6000, exectime = 2.658
NOTICE:  i =  7000, exectime = 2.318
NOTICE:  i =  8000, exectime = 2.572
NOTICE:  i =  9000, exectime = 2.929
NOTICE:  i = 10000, exectime = 3.808

Co się stało? Dlaczego nawet dla najprostszej aktualizacji jednego rekordu czas wykonania pogorszył się 7-krotnie — z 0.524 ms do 3.808 ms? Nasz ranking również buduje się coraz wolniej.

Winowajca to MVCC

Wszystko sprowadza się do mechanizmu MVCC, który zmusza zapytanie do przeglądania wszystkich poprzednich wersji wpisu. Więc oczyśćmy naszą tabelę z „martwych” wersji:

VACUUM VERBOSE tbl;

INFO: powiększanie "public.tbl"
INFO: "tbl": znaleziono 0 usuwalnych, 10026 nieusuwalnych wersji wierszy w 45 z 45 stron
SZCZEGÓŁY: 10000 martwych wersji wierszy nie może jeszcze zostać usuniętych, najstarszy xmin: 597439602

Ojej, ale nie ma co sprzątać! Równolegle wykonujące się zapytanie nam przeszkadza — bo kiedyś może zechcieć sięgnąć po te wersje (a nuż?), a one muszą być dla niego dostępne. Dlatego nawet VACUUM FULL nam nie pomoże.

„Zgniatamy” tabelę

Ale my doskonale wiemy, że to zapytanie nie potrzebuje naszej tabeli. Dlatego spróbujmy jednak przywrócić wydajność systemu do akceptowalnych granic, wyrzucając z tabeli wszystko, co niepotrzebne — przynajmniej „ręcznie”, skoro VACUUM się poddaje.

Aby było to bardziej przejrzyste, rozważmy na przykładzie tabeli buforowej. To znaczy, następuje duży przepływ INSERT/DELETE, a czasami tabela okazuje się całkowicie pusta. Ale jeśli nie jest pusta, musimy zachować jej bieżącą zawartość.

#0: Оцениваем ситуацию

Jasne jest, że można próbować coś robić z tabelą nawet po każdej operacji, ale nie ma to większego sensu — koszty utrzymania będą zdecydowanie wyższe niż przepustowość docelowych zapytań.

Sformułujmy kryteria — „już najwyższy czas działać”, jeśli:

• VACUUM był uruchamiany wystarczająco dawno
  Oczekujemy dużego obciążenia, więc niech to będzie 60 sekund od ostatniego [auto]VACUUM.
• fizyczny rozmiar tabeli większy niż docelowy
  Zdefiniujemy go jako podwójną liczbę stron (bloków po 8KB) w stosunku do minimalnego rozmiaru — 1 blk na heap + 1 blk na każdy z indeksów — dla potencjalnie pustej tabeli. Jeśli jednak oczekujemy, że w buforze „normalnie” zawsze pozostanie pewna ilość danych, warto dostosować tę formułę.

Zapytanie kontrolne

SELECT
  relpages
, ((
    SELECT
      count()
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- tu lepiej robić * current_setting('block_size')::bigint, ale kto zmienia rozmiar bloku?..
, pg_total_relation_size(oid) size
, coalesce(extract('epoch' from (now() - greatest(
    pg_stat_get_last_vacuum_time(oid)
  , pg_stat_get_last_autovacuum_time(oid)
  ))), 1 << 30) vaclag
FROM
  pg_class cl
WHERE
  oid = $1::regclass -- tbl
LIMIT 1;

relpages | size_norm | size    | vaclag
-------------------------------------------
       0 |     24576 | 1105920 | 3392.484835

#1: Все равно VACUUM

Nie możemy z góry wiedzieć, jak bardzo równoległe zapytanie nam przeszkadza - ile dokładnie rekordów "eksploatacyjnych" pojawiło się od momentu jego rozpoczęcia. Dlatego gdy już zdecydujemy się jakoś przetworzyć tabelę, najpierw warto na niej wykonać VACUUM — w przeciwieństwie do VACUUM FULL, równoległym procesom nie przeszkadza praca z danymi do odczytu i zapisu.

Jednocześnie może on od razu usunąć dużą część tego, co chcielibyśmy wyczyścić. A kolejne zapytania dotyczące tej tabeli będą u nas z "gorącą pamięcią podręczną", co skróci ich czas trwania - a więc także całkowity czas blokowania innych transakcji serwisowych.

#2: Есть кто-нибудь дома?

Sprawdźmy - czy w tabeli w ogóle jest coś:

TABLE tbl LIMIT 1;

Jeśli nie pozostał ani jeden rekord, to możemy znacząco zaoszczędzić na przetwarzaniu - po prostu wykonując TRUNCATE:

Działa to tak samo jak bezwarunkowa komenda DELETE dla każdej tabeli, ale znacznie szybciej, ponieważ faktycznie nie skanuje tabel. Co więcej, natychmiast zwalnia przestrzeń dyskową, więc wykonanie operacji VACUUM po niej nie jest konieczne.

Czy w tym przypadku należy zresetować licznik sekwencji tabeli (RESTART IDENTITY) - decydujcie sami.

#3: Все — по-очереди!

Ponieważ działamy w warunkach wysokiej konkurencyjności, podczas gdy sprawdzamy brak rekordów w tabeli, ktoś mógł już coś tam zapisać. Nie możemy pozwolić, aby ta informacja została utracona - więc co? Oczywiście, musimy upewnić się, że nikt nie może już zapisywać.

W tym celu musimy włączyć SERIALIZOWANY-izolację dla naszej transakcji (tak, tutaj rozpoczynamy transakcję) i „zablokować” tabelę „na stałe”:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;

Taki poziom blokady jest wymuszony przez operacje, które chcemy na niej przeprowadzić.

#4: Конфликт интересов

Przychodzimy tutaj i chcemy „zablokować” tabelę - a co, jeśli w tym momencie ktoś był na niej aktywny, na przykład, odczytywał z niej? „Zawiesimy się” w oczekiwaniu na zwolnienie tej blokady, a inni, którzy chcą poczytać, napotkają już na nas...

Aby do tego nie doszło, „poświęcimy się” - jeśli przez określony (dozwolony mały) czas nie uda nam się uzyskać blokady, otrzymamy wyjątek od bazy, ale przynajmniej nie przeszkodzimy innym.

W tym celu ustawimy zmienną sesji lock_timeout (dla wersji 9.3+) lub/i statement_timeoutNajważniejsze, aby pamiętać, że wartość statement_timeout dotyczy tylko następnego statementa. Czyli w taki sposób w sklejce — nie zadziała:

SET statement_timeout = ...;LOCK TABLE ...;

Aby nie zajmować się później przywracaniem "starej" wartości zmiennej, używamy formy SET LOCAL, która ogranicza zakres stosowania ustawienia do bieżącej transakcji.

Pamiętajmy, że statement_timeout dotyczy wszystkich następnych zapytań, aby transakcja nie mogła się przeciągać do nieakceptowalnych wartości, jeśli danych w tabeli jednak będzie dużo.

#5: Копируем данные

Jeśli tabela okazała się niezbyt pusta — dane będzie trzeba ponownie zapisać przez pomocniczą tymczasową tabelę:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Sygnatura ON COMMIT DROP oznacza, że w momencie zakończenia transakcji tymczasowa tabela przestanie istnieć, i nie trzeba manualnie się nią zajmować w kontekście połączenia.

Ponieważ zakładamy, że "żywych" danych nie ma zbyt wiele, operacja powinna przejść stosunkowo szybko.

No i to wszystko! Nie zapomnij po zakończeniu transakcji uruchomić ANALYZE w celu normalizacji statystyk tabeli, jeśli to konieczne.

Zbieramy końcowy skrypt

Używamy takiego "pseudo-pythona":

# собираем статистику с таблицы
stat <-
  SELECT
    relpages
  , ((
      SELECT
        count(*)
      FROM
        pg_index
      WHERE
        indrelid = cl.oid
    ) + 1) << 13 size_norm
  , pg_total_relation_size(oid) size
  , coalesce(extract('epoch' from (now() - greatest(
      pg_stat_get_last_vacuum_time(oid)
    , pg_stat_get_last_autovacuum_time(oid)
    ))), 1 << 30) vaclag
  FROM
    pg_class cl
  WHERE
    oid = $1::regclass -- table_name
  LIMIT 1;

# таблица больше целевого размера и VACUUM был давно
if stat.size > 2 * stat.size_norm and stat.vaclag is None or stat.vaclag > 60:
  -> VACUUM %table;
  try:
    -> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    # пытаемся захватить монопольную блокировку с предельным временем ожидания 1s
    -> SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s';
    -> LOCK TABLE %table IN ACCESS EXCLUSIVE MODE;
    # надо убедиться в пустоте таблицы внутри транзакции с блокировкой
    row <- TABLE %table LIMIT 1;
    # если в таблице нет ни одной "живой" записи - очищаем ее полностью, в противном случае - "перевставляем" все записи через временную таблицу
    if row is None:
      -> TRUNCATE TABLE %table RESTART IDENTITY;
    else:
      # создаем временную таблицу с данными таблицы-оригинала
      -> CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE %table;
      # очищаем оригинал без сброса последовательности
      -> TRUNCATE TABLE %table;
      # вставляем все сохраненные во временной таблице данные обратно
      -> INSERT INTO %table TABLE _tmp_swap;
    -> COMMIT;
  except Exception as e:
    # если мы получили ошибку, но соединение все еще "живо" - словили таймаут
    if not isinstance(e, InterfaceError):
      -> ROLLBACK;

Czy można nie kopiować danych drugi raz?W zasadzie tak, jeśli na oid samej tabeli nie są związane jakieś inne aktywności z strony BL lub FK z strony Bazy Danych:

CREATE TABLE _swap_%table(LIKE %table INCLUDING ALL);
INSERT INTO _swap_%table TABLE %table;
DROP TABLE %table;
ALTER TABLE _swap_%table RENAME TO %table;

Uruchamiamy skrypt na oryginalnej tabeli i sprawdzamy metryki:

VACUUM tbl;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s';
  LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;
  CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;
  TRUNCATE TABLE tbl;
  INSERT INTO tbl TABLE _tmp_swap;
COMMIT;

relpages | size_norm | size   | vaclag
-------------------------------------------
       0 |     24576 |  49152 | 32.705771

Wszystko się udało! Tabela została zredukowana 50 razy, a wszystkie UPDATE znów działają szybko.

Źródło: habr.com