🥇Když selže VACUUM, vyčistíme stůl ručně

VACUUM dokáže „vyčistit“ z tabulky v PostgreSQL jen to, co nikdo nevidí - to znamená, že neexistuje jediný aktivní požadavek, který by začal před změnou těchto záznamů.

Ale co když takový nepříjemný typ (dlouhodobé zatížení OLAP databáze OLTP) stále existuje? Jak vyčistit aktivně přebalovací pult obklopený dlouhými dotazy a nešlápnout na hrábě?

Rozkládání hrábě

Nejprve si určíme, jaký je problém, který chceme vyřešit, a jak může vzniknout.

Obvykle tato situace nastává na relativně malém stole, ale ve kterém se vyskytuje hodně změn. Obvykle toto nebo jiné metry/agregáty/hodnocení, na kterém se často provádí UPDATE, popř buffer-queue zpracovávat nějaký neustále probíhající proud událostí, jejichž záznamy jsou neustále INSERT/DELETE.

Pokusme se reprodukovat možnost s hodnocením:

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;

A paralelně se v jiném spojení spustí dlouhý, dlouhý požadavek, sbírající nějaké složité statistiky, ale neovlivní náš stůl:

SELECT pg_sleep(10000);

Nyní aktualizujeme hodnotu jednoho z čítačů mnohokrát. Pro čistotu experimentu udělejme toto v samostatných transakcích pomocí dblinkjak to bude 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 za tu nejjednodušší AKTUALIZACI jednoho záznamu doba provádění snížena 7krát - od 0.524 ms do 3.808 ms? A naše hodnocení se buduje stále pomaleji.

Za všechno může MVCC.

Všechno je to o Mechanismus MVCC, což způsobí, že dotaz prohledá všechny předchozí verze položky. Pojďme tedy vyčistit náš stůl od „mrtvých“ verzí:

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

Ach, není co čistit! Paralelní Běžící požadavek nám překáží - koneckonců se může jednoho dne chtít obrátit na tyto verze (co kdyby?), a měly by mu být dostupné. A proto nám ani VACUUM FULL nepomůže.

"Zhroucení" stolu

Ale víme jistě, že tento dotaz nepotřebuje naši tabulku. Stále se proto budeme snažit vrátit výkon systému na adekvátní meze odstraněním všeho nepotřebného ze stolu – alespoň „ručně“, protože VACUUM ustupuje.

Aby to bylo jasnější, podívejme se na příklad případu tabulky vyrovnávací paměti. To znamená, že dochází k velkému toku INSERT/DELETE a někdy je tabulka úplně prázdná. Ale pokud není prázdný, musíme uložit jeho aktuální obsah.

#0: Vyhodnocení situace

Je jasné, že i po každé operaci můžete zkusit s tabulkou něco udělat, ale to nedává moc smysl – režie údržby bude jednoznačně větší než propustnost cílových dotazů.

Formulujme kritéria – „je čas jednat“, pokud:

VACUUM bylo spuštěno již poměrně dávno
Očekáváme velkou zátěž, tak to nechme být 60 sekund od posledního [auto]VAKUA.
fyzická velikost tabulky je větší než cíl
Definujme to jako dvojnásobek počtu stránek (8KB bloků) vzhledem k minimální velikosti - 1 blok za haldu + 1 blok za každý index - pro potenciálně prázdný stůl. Pokud očekáváme, že určité množství dat vždy zůstane ve vyrovnávací paměti „normálně“, je rozumné tento vzorec upravit.

Žádost o ověření

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: Stále VAKUUM

Nemůžeme předem vědět, zda nás paralelní dotaz výrazně ruší – přesně kolik záznamů se stalo „zastaralými“ od doby, kdy začal. Proto, když se rozhodneme tabulku nějak zpracovat, v každém případě bychom na ní měli nejprve provést VACUUM - na rozdíl od VACUUM FULL nezasahuje do paralelních procesů pracujících s daty čtení a zápisu.

Zároveň dokáže okamžitě uklidit většinu toho, co bychom chtěli odstranit. Ano a další dotazy na této tabulce budou směřovat k nám podle "hot cache", což zkrátí jejich dobu trvání – a tím i celkovou dobu blokování ostatních naší servisní transakcí.

#2: Je někdo doma?

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

TABLE tbl LIMIT 1;

Pokud nezůstane ani jeden záznam, pak můžeme na zpracování hodně ušetřit jednoduchým provedením Zkrátit:

Funguje stejně jako nepodmíněný příkaz DELETE pro každou tabulku, ale je mnohem rychlejší, protože tabulky ve skutečnosti neprohledává. Navíc okamžitě uvolňuje místo na disku, takže není potřeba následně provádět operaci VACUUM.

Je na vás, zda potřebujete resetovat čítač sekvencí tabulky (RESTART IDENTITY).

#3: Všichni – střídejte se!

Vzhledem k tomu, že pracujeme ve vysoce konkurenčním prostředí, zatímco my tady kontrolujeme, že v tabulce nejsou žádné záznamy, mohl tam už někdo něco napsat. O tyto informace bychom neměli přijít, tak co? Je to tak, musíme zajistit, aby to nikdo nemohl s jistotou zapsat.

K tomu musíme povolit SERIALIZAČNÍ-izolace pro naši transakci (ano, zde zahájíme transakci) a „pevně“ uzamkneme tabulku:

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

Tato úroveň blokování je určena operacemi, které na ní chceme provádět.

#4: Střet zájmů

Přijdeme sem a chceme nápis „zamknout“ – co kdyby na něm byl v tu chvíli někdo aktivní, třeba z něj četl? Budeme „viset“ čekat na uvolnění tohoto bloku a ostatní, kteří chtějí číst, na nás narazí...

Aby se tak nestalo, „obětujeme se“ – pokud se nám nepodařilo získat zámek do určité (akceptovatelně krátké) doby, pak dostaneme výjimku ze základny, ale alespoň nebudeme příliš zasahovat ostatní.

Chcete-li to provést, nastavte proměnnou relace lock_timeout (pro verze 9.3+) nebo/a výpis_časový limit. Hlavní věc, kterou je třeba si zapamatovat, je, že hodnota statement_timeout platí až od dalšího příkazu. Tedy takhle při lepení - nebude fungovat:

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

Abychom se později nemuseli zabývat obnovováním „staré“ hodnoty proměnné, použijeme formulář NASTAVIT MÍSTNĚ, která omezuje rozsah nastavení na aktuální transakci.

Pamatujeme si, že statement_timeout se vztahuje na všechny následující požadavky, takže transakce se nemůže roztáhnout na nepřijatelné hodnoty, pokud je v tabulce mnoho dat.

#5: Kopírování dat

Pokud tabulka není zcela prázdná, bude nutné data znovu uložit pomocí pomocné dočasné tabulky:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Podpis ON COMMIT DROP znamená, že ve chvíli, kdy transakce skončí, dočasná tabulka přestane existovat a není nutné ji ručně mazat v kontextu připojení.

Protože předpokládáme, že „živých“ dat není mnoho, měla by tato operace proběhnout poměrně rychle.

No, to je vše! Po dokončení transakce nezapomeňte spustit ANALÝZA v případě potřeby normalizovat statistiku tabulky.

Dát dohromady finální scénář

Používáme tento „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;

Je možné data podruhé nezkopírovat?V zásadě je možné, pokud samotný oid tabulky není vázán na žádné další aktivity ze strany BL nebo FK ze strany 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;

Spusťte skript na zdrojové tabulce a zkontrolujte 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 se povedlo! Tabulka se 50krát zmenšila a všechny AKTUALIZACE opět běží rychle.

Zdroj: www.habr.com

Když VACUUM selže, vyčistíme stůl ručně