🥇Kiedy VACUUM zawiedzie, czyścimy stół ręcznie

ODKURZAĆ może „posprzątać” z tabeli w PostgreSQL tylko co nikt nie może zobaczyć - oznacza to, że nie było ani jednego aktywnego żądania, które rozpoczęłoby się przed zmianą tych rekordów.

Ale co, jeśli taki nieprzyjemny typ (długoterminowe obciążenie OLAP w bazie danych OLTP) nadal istnieje? Jak wyczyść aktywnie przewijany stół otoczony długimi zapytaniami i nie nadepnąć na grabie?

Rozkładanie grabi

Na początek ustalmy, jaki jest problem, który chcemy rozwiązać i w jaki sposób może on powstać.

Zazwyczaj taka sytuacja ma miejsce na stosunkowo małym stole, ale w którym to występuje wiele zmian. Zwykle to lub inne metry/kruszywo/wartości znamionowe, na którym często wykonywana jest UPDATE, lub kolejka buforowa do przetwarzania jakiegoś stale trwającego strumienia zdarzeń, których zapisy są stale WSTAWIAĆ/USUWAĆ.

Spróbujmy odtworzyć opcję z ocenami:

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- по этому индексу будем строить рейтинг

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

Równolegle w innym połączeniu rozpoczyna się długie, długie żądanie, które zbiera złożone statystyki, ale nie wpływa na nasz stół:

SELECT pg_sleep(10000);

Teraz aktualizujemy wartość jednego z liczników wiele, wiele razy. Dla czystości eksperymentu zróbmy to w oddzielnych transakcjach za pomocą dblinkjak to będzie wyglądać 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 w przypadku najprostszej AKTUALIZACJI pojedynczego rekordu czas wykonania skrócony 7-krotnie — od 0.524 ms do 3.808 ms? A nasza ocena rośnie coraz wolniej.

To wszystko wina MVCC.

To wszystko o Mechanizm MVCC, co powoduje, że zapytanie przegląda wszystkie poprzednie wersje wpisu. Oczyśćmy więc nasz stół z „martwych” wersji:

VACUUM VERBOSE tbl;

INFO:  vacuuming "public.tbl"
INFO:  "tbl": found 0 removable, 10026 nonremovable row versions in 45 out of 45 pages
DETAIL:  10000 dead row versions cannot be removed yet, oldest xmin: 597439602

Och, nie ma co sprzątać! Równoległy Bieżące żądanie przeszkadza nam – w końcu może kiedyś będzie chciał sięgnąć po te wersje (a co jeśli?), a one powinny być dla niego dostępne. I dlatego nawet VACUUM FULL nam nie pomoże.

„Zwijanie” stołu

Ale wiemy na pewno, że to zapytanie nie potrzebuje naszej tabeli. Dlatego nadal będziemy próbować przywrócić wydajność systemu do odpowiednich granic, eliminując z tabeli wszystko, co niepotrzebne - przynajmniej „ręcznie”, bo VACUUM daje sobie spokój.

Aby było to bardziej jasne, spójrzmy na przykład przypadku tabeli buforów. Oznacza to, że występuje duży przepływ operacji INSERT/DELETE, a czasami tabela jest całkowicie pusta. Ale jeśli nie jest pusty, musimy zapisz jego aktualną zawartość.

#0: Ocena sytuacji

Jasne jest, że można próbować coś zrobić z tabelą nawet po każdej operacji, ale nie ma to większego sensu – koszty utrzymania będą wyraźnie większe niż przepustowość docelowych zapytań.

Sformułujmy kryteria – „czas działać”, jeśli:

VACUUM powstał dość dawno temu
Spodziewamy się dużego obciążenia, więc niech tak będzie 60 sekund od ostatniego [auto]VACUUM.
Rozmiar tabeli fizycznej jest większy niż docelowy
Zdefiniujmy to jako dwukrotność liczby stron (bloków 8 KB) w stosunku do minimalnego rozmiaru - 1 blok na stertę + 1 blok na każdy indeks - dla potencjalnie pustego stołu. Jeśli spodziewamy się, że pewna ilość danych zawsze pozostanie w buforze „normalnie”, zasadne jest zmodyfikowanie tej formuły.

Prośba o weryfikację

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- тут правильнее делать * current_setting('block_size')::bigint, но кто меняет размер блока?..
, 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: Nadal PRÓŻNIA

Nie możemy z góry wiedzieć, czy zapytanie równoległe znacząco nam przeszkadza – ile dokładnie rekordów stało się „nieaktualnych” od momentu jego rozpoczęcia. Dlatego też, gdy zdecydujemy się w jakiś sposób przetworzyć tabelę, w każdym razie powinniśmy najpierw wykonać na niej wykonanie ODKURZAĆ - w przeciwieństwie do VACUUM FULL, nie zakłóca równoległych procesów pracujących z danymi do odczytu i zapisu.

Jednocześnie potrafi błyskawicznie oczyścić większość tego, co chcielibyśmy usunąć. Tak, a kolejne zapytania dotyczące tej tabeli trafią do nas przez „gorącą pamięć podręczną”, co skróci czas ich trwania - a co za tym idzie, całkowity czas blokowania innych przez naszą obsługę transakcji.

#2: Czy ktoś jest w domu?

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

TABLE tbl LIMIT 1;

Jeśli nie pozostał ani jeden rekord, możemy dużo zaoszczędzić na przetwarzaniu, po prostu robiąc to ŚCIĘTY:

Działa tak samo jak bezwarunkowe polecenie DELETE dla każdej tabeli, ale jest znacznie szybsze, ponieważ w rzeczywistości nie skanuje tabel. Co więcej, natychmiast zwalnia miejsce na dysku, dzięki czemu nie ma potrzeby wykonywania później operacji VACUUM.

Decyzja o konieczności zresetowania licznika sekwencji tabeli (RESTART IDENTITY) należy do Ciebie.

#3: Wszyscy – na zmianę!

Ponieważ pracujemy w bardzo konkurencyjnym środowisku, podczas gdy my tutaj sprawdzamy, czy w tabeli nie ma wpisów, ktoś mógł już coś tam napisać. Nie powinniśmy tracić tych informacji. I co z tego? Zgadza się, musimy się upewnić, że nikt nie może tego zapisać na pewno.

Aby to zrobić, musimy włączyć SERIALIZOWANY-izolację dla naszej transakcji (tak, tutaj rozpoczynamy transakcję) i blokujemy tabelę „szczelnie”:

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

O tym poziomie blokowania decydują operacje, jakie chcemy na nim wykonać.

#4: Konflikt interesów

Przychodzimy tutaj i chcemy „zablokować” znak – a co jeśli ktoś był na nim w tym momencie aktywny, np. czytając z niego? Będziemy „wisieć” w oczekiwaniu na uwolnienie tego bloku, a inni, którzy będą chcieli czytać, wpadną na nas…

Aby temu zapobiec, „poświęcimy się” – jeśli nie uda nam się uzyskać blokady w określonym (w miarę krótkim) czasie, to otrzymamy wyjątek od bazy, ale przynajmniej nie będziemy się zbytnio wtrącać inni.

Aby to zrobić, ustaw zmienną sesji limit czasu_blokady (dla wersji 9.3+) lub/i limit_czasu instrukcji. Najważniejszą rzeczą do zapamiętania jest to, że wartość Statement_timeout ma zastosowanie dopiero od następnej instrukcji. To znaczy tak przy klejeniu - nie zadziała:

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

Aby później nie musieć zajmować się przywracaniem „starej” wartości zmiennej, korzystamy z formularza USTAW LOKALNE, co ogranicza zakres ustawienia do bieżącej transakcji.

Pamiętamy, że Statement_timeout dotyczy wszystkich kolejnych żądań, dzięki czemu transakcja nie może rozciągnąć się do niedopuszczalnych wartości, jeśli w tabeli jest dużo danych.

#5: Skopiuj dane

Jeżeli tabela nie jest całkowicie pusta, należy ponownie zapisać dane, korzystając z pomocniczej tabeli tymczasowej:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Podpis PRZY ZATWIERDZENIU ZATWIERDZENIA oznacza, że w momencie zakończenia transakcji tabela tymczasowa przestanie istnieć i nie ma potrzeby jej ręcznego usuwania w kontekście połączenia.

Ponieważ zakładamy, że „żywych” danych jest niewiele, operacja ta powinna nastąpić dość szybko.

Cóż, to wszystko! Nie zapomnij po zakończeniu transakcji uruchom ANALIZĘ w razie potrzeby znormalizować statystyki tabeli.

Składanie ostatecznego scenariusza

Używamy tego „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 jest to możliwe, jeśli sam oid tabeli nie jest powiązany z żadnymi innymi działaniami ze strony BL lub FK ze strony DB:

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

Uruchommy skrypt na tabeli źródłowej i sprawdźmy 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 skurczyła się 50 razy i wszystkie AKTUALIZACJE znów działają szybko.

Źródło: www.habr.com

Gdy VACUUM zawiedzie, czyścimy stół ręcznie