🥇Když VACUUM selže — vyčistíme tabulku ručně

VACUUM může „vyčistit“ z tabulky v PostgreSQL pouze to, co nikdo nemůže vidět — tedy neexistuje žádný aktivní dotaz, který by začal předtím, než byly tyto záznamy změněny.

A co když takový nepříjemný typ (dlouhotrvající OLAP zátěž na OLTP databázi) přece jen existuje? Jak vyčistit aktivně se měnící tabulku v prostředí dlouhých dotazů a nenarazit na hrable?

Rašujeme hrable

Nejprve stanovíme, v čem spočívá a jak vůbec může vzniknout problém, který chceme vyřešit.

Obvykle k takové situaci dochází na relativně malé tabulce, ale kde dochází k velmi mnoha změnám. Obvykle to jsou buď různé počítadla/agregáty/zhodnocení, na kterých se často provádí UPDATE, nebo bufferová fronta pro zpracování nějakého neustále probíhajícího toku událostí, záznamy o nichž se neustále INSERT/DELETE.

Zkusíme reprodukovat variantu s hodnocením:

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- podle tohoto indexu budeme stavět hodnocení

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

A paralelně, v jiném spojení, startuje dlouhý dotaz, shromažďující nějakou složitou statistiku, ale nedotýká se naší tabulky:

SELECT pg_sleep(10000);

Teď opakovaně aktualizujeme hodnotu jednoho z počítadel. Pro čistotu experimentu to provedeme v samostatných transakcích pomocí dblink, jak by to probíhalo ve skutečnosti:

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 se stalo? Proč i pro nejjednodušší UPDATE jediného záznamu se doba vykonání zhoršila 7krát — z 0.524 ms na 3.808 ms? A naše hodnocení se stále buduje pomaleji.

Za vším může MVCC

Všechny souvisí s mechanismem MVCC, který nutí dotaz procházet všechny předchozí verze záznamu. Tak si vyčistíme naši tabulku od „mrtvých“ verzí:

VACUUM VERBOSE tbl;

INFO:  úklid "public.tbl"
INFO:  "tbl": nalezeno 0 odstraňitelných a 10026 neodstraňitelných verzí řádků ve 45 ze 45 stránek
DETAIL:  10000 mrtvých verzí řádků zatím nelze odstranit, nejstarší xmin: 597439602

Ojej, a nic k úklidu tu ani není! Paralelně běžící dotaz nám brání — protože se kdysi může chtít obrátit na tyto verze (co kdyby?), a ty by mu měly být přístupné. A proto ani VACUUM FULL nám nepomůže.

„Sklápíme“ tabulku

Ale my víme, že tomuto dotazu naše tabulka není potřeba. Takže zkusíme přesto vrátit výkonnost systému do přijatelných mezí, odstraněním všech zbytečností z tabulky — i když „ručně“, když VACUUM se vzdává.

Aby to bylo názornější, podívejme se už na příkladu případu tabulky-buffer. Tedy probíhá velký tok INSERT/DELETE a občas se tabulka ukáže být úplně prázdná. Ale pokud tam není prázdná, musíme uchovat její aktuální obsah.

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

Je jasné, že se můžeš pokusit něco udělat s tabulkou i po každé operaci, ale to nemá velký smysl — náklady na údržbu budou očividně větší než propustnost cílových dotazů.

Stanovíme kritéria — „už je na čase jednat“, pokud:

VACUUM bylo spuštěno dost dávno
Očekáváme velké zatížení, takže ať to bude 60 sekund od posledního [auto]VACUUM.
fyzická velikost tabulky je větší než cílová
Stanovíme ji jako dvojnásobný počet stránek (bloků po 8 KB) vzhledem k minimální velikosti — 1 blk na heap + 1 blk na každý z indexů — pro potenciálně prázdnou tabulku. Pokud však očekáváme, že v bufferu „standardně“ zůstane nějaký objem dat, je rozumné tuto formuli doladit.

Kontrolní dotaz

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- tady je lepší dělat * current_setting('block_size')::bigint, ale kdo mění velikost 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

Nemůžeme předem vědět, jak moc nám bude paralelní dotaz překážet – kolik přesně záznamů je „ zastaralých“ od okamžiku jeho spuštění. Proto, když se rozhodneme, jakým způsobem tabulku zpracovat, v každém případě je nejlepší nejprve provést na ní VACUUM – na rozdíl od VACUUM FULL mu paralelní procesy při čtení a zápisu dat nebrání.

Může také okamžitě vyčistit většinu toho, co bychom chtěli odstranit. A následující dotazy na této tabulce pak proběhnou z „horkého cache“, což zkrátí jejich délku – a tím i celkový čas blokování ostatních transakcí obsluhovaných námi.

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

Zkontrolujme, zda je v tabulce vůbec něco:

TABLE tbl LIMIT 1;

Pokud nezůstal ani jediný záznam, můžeme výrazně ušetřit na zpracování – stačí provést TRUNCATE:

Děje se to stejně jako bezpodmínečný příkaz DELETE pro každou tabulku, ale mnohem rychleji, jelikož v podstatě neprocházejí tabulkami. Navíc okamžitě uvolňuje diskový prostor, takže operaci VACUUM po něm není třeba provádět.

Rozhodněte se sami, zda byste chtěli resetovat číslo sekvence tabulky (RESTART IDENTITY).

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

Jelikož pracujeme za podmínek vysoké konkurence, zatímco kontrolujeme, zda v tabulce nejsou žádné záznamy, někdo mohl již něco zapsat. Tuto informaci nesmíme ztratit, takže co? Správně, musíme zajistit, aby nikdo nemohl zapisovat.

K tomu je nutné aktivovat SERIALIZABLE-izolaci pro naši transakci (ano, zde spouštíme transakci) a zamknout tabulku „na pevno“:

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

Tento úroveň blokování je dána operacemi, které na ní chceme provádět.

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

Přicházíme sem a chceme tabulku „zamknout“ – co když někdo v ten okamžik na ní byl aktivní, například z ní četl? Zůstaneme viset v očekávání uvolnění tohoto bloku a ostatní, kteří chtějí číst, narazí už na nás...

Aby se tomu předešlo, „obětujeme se“ – pokud jsme v určitém (přijatelně malém) čase blokaci nezískali, dostaneme od databáze výjimku, ale alespoň nebudeme silně překážet ostatním.

K tomu nastavíme proměnnou session lock_timeout (pro verze 9.3+) nebo/i statement_timeoutHlavní je pamatovat si, že hodnota statement_timeout se uplatní pouze na následující příkaz. Tedy takto v sekvenci — nebude fungovat:

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

Abychom se později nemuseli zabývat obnovením "staré" hodnoty proměnné, použijeme formu SET LOCAL, která omezuje rozsah nastavení na aktuální transakci.

Pamatujme, že statement_timeout se vztahuje na všechny následující dotazy, takže transakce nemůže trvat nepřijatelně dlouho, pokud se v tabulce opravdu objeví hodně dat.

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

Pokud tabulka není úplně prázdná — data je třeba znovu uložit prostřednictvím pomocné dočasné tabulky:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Podpis ON COMMIT DROP znamená, že v okamžiku ukončení transakce dočasná tabulka přestane existovat a není třeba se v rámci spojení zabývat jejím ručním odstraněním.

Vzhledem k tomu, že předpokládáme, že "živých" dat není moc, tato operace by měla proběhnout dostatečně rychle.

A to je všechno! Nezapomeňte po dokončení transakce spustit ANALYZE pro normalizaci statistik tabulky, pokud je to nutné.

Sestavujeme finální skript

Používáme takový "pseudo python":

# собираем статистику с таблицы
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;

A není možné data nekopírovat podruhé?V zásadě to možné je, pokud na oid samotné tabulky nejsou vázány žádné další aktivity ze strany BLL nebo FK ze strany databáze:

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

Spustíme skript na původní tabulce a zkontrolujeme metriky:

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

Všechno vyšlo! Tabulka se zmenšila 50krát a všechny UPDATE znovu běhají rychle.

Zdroj: habr.com

Když VACUUM selže — čistí tabelu ručně