ProHoster > Blog > Administrácia > Keď VACUUM zlyhá, vyčistíme stôl ručne

Keď VACUUM zlyhá, vyčistíme stôl ručne

VACUUM dokáže „vyčistiť“ z tabuľky v PostgreSQL len to, čo nikto nevidí - to znamená, že neexistuje ani jedna aktívna požiadavka, ktorá sa začala pred zmenou týchto záznamov.

Čo ak však takýto nepríjemný typ (dlhodobé OLAP zaťaženie databázy OLTP) stále existuje? Ako vyčistiť aktívne prebaľovací pult obklopený dlhými dopytmi a nešliapnuť na hrable?

Keď VACUUM zlyhá, vyčistíme stôl ručne

Rozloženie hrablí

Najprv si určme, aký je problém, ktorý chceme vyriešiť, a ako môže vzniknúť.

Zvyčajne sa táto situácia stáva na relatívne malom stole, ale v ktorom sa vyskytuje veľa zmien. Zvyčajne toto alebo iné metre/agregáty/hodnotenia, na ktorom sa často vykonáva UPDATE, príp buffer-queue na spracovanie nejakého neustále prebiehajúceho prúdu udalostí, ktorých záznamy sú neustále VLOŽIŤ/VYMAZAŤ.

Pokúsme sa reprodukovať možnosť s hodnoteniami:

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 paralelne sa v inom spojení spustí dlhá, dlhá požiadavka, zbierajúca nejaké zložité štatistiky, ale neovplyvní náš stôl:

SELECT pg_sleep(10000);

Teraz aktualizujeme hodnotu jedného z počítadiel mnohokrát. Pre čistotu experimentu urobme toto v samostatných transakciách pomocou dblinkako to bude v skutoč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

Čo sa stalo? Prečo aj za tú najjednoduchšiu AKTUALIZÁCIU jedného záznamu čas vykonania znížený 7-krát - od 0.524 ms do 3.808 ms? A naše hodnotenie sa buduje čoraz pomalšie.

Za všetko môže MVCC.

Je to všetko o mechanizmus MVCC, čo spôsobí, že dotaz prejde všetky predchádzajúce verzie záznamu. Vyčistime teda náš stôl od „mŕtvych“ verzií:

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, nie je čo čistiť! Paralelné Bežiaca požiadavka nám prekáža - koniec koncov, možno sa jedného dňa bude chcieť obrátiť na tieto verzie (čo ak?) a mali by mať k dispozícii. A preto nám nepomôže ani VACUUM FULL.

„Zrútenie“ stola

S istotou však vieme, že tento dotaz nepotrebuje našu tabuľku. Preto sa ešte pokúsime vrátiť výkon systému na adekvátne limity odstránením všetkého nepotrebného zo stola – aspoň „ručne“, keďže VACUUM ustupuje.

Aby to bolo jasnejšie, pozrime sa na príklad prípadu vyrovnávacej tabuľky. To znamená, že existuje veľký tok INSERT/DELETE a niekedy je tabuľka úplne prázdna. Ale ak nie je prázdny, musíme uložiť jej aktuálny obsah.

#0: Zhodnotenie situácie

Je jasné, že s tabuľkou sa môžete pokúsiť niečo urobiť aj po každej operácii, ale nedáva to veľký zmysel – réžia údržby bude jednoznačne väčšia ako priepustnosť cieľových dopytov.

Sformulujme kritériá – „je čas konať“, ak:

  • VACUUM bolo spustené už pomerne dávno
    Očakávame veľkú záťaž, tak nech sa páči 60 sekúnd od posledného [auto]Vákua.
  • fyzická veľkosť tabuľky je väčšia ako cieľová
    Definujme to ako dvojnásobok počtu stránok (8 kB blokov) vzhľadom na minimálnu veľkosť - 1 blok za haldu + 1 blok za každý index - pre potenciálne prázdny stôl. Ak očakávame, že určité množstvo údajov vždy zostane vo vyrovnávacej pamäti „normálne“, je rozumné tento vzorec upraviť.

Žiadosť o overenie

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 VÁKUUM

Nemôžeme dopredu vedieť, či nám paralelný dopyt výrazne prekáža – presne koľko záznamov sa stalo „neaktuálnych“ od jeho začiatku. Preto, keď sa rozhodneme tabuľku nejako spracovať, v každom prípade by sme ju mali najskôr vykonať VACUUM - na rozdiel od VACUUM FULL nezasahuje do paralelných procesov pracujúcich s dátami na čítanie a zápis.

Zároveň dokáže okamžite vyčistiť väčšinu toho, čo by sme chceli odstrániť. Áno, a ďalšie otázky v tejto tabuľke budú smerované k nám podľa "horúcej vyrovnávacej pamäte", čím sa skráti ich trvanie – a teda aj celkový čas blokovania ostatných našou servisnou transakciou.

#2: Je niekto doma?

Pozrime sa, či je v tabuľke vôbec niečo:

TABLE tbl LIMIT 1;

Ak nezostane ani jeden záznam, potom môžeme ušetriť veľa na spracovaní jednoduchým vykonaním OCELIŤ:

Funguje rovnako ako bezpodmienečný príkaz DELETE pre každú tabuľku, ale je oveľa rýchlejší, pretože v skutočnosti tabuľky neskenuje. Navyše okamžite uvoľňuje miesto na disku, takže nie je potrebné následne vykonávať operáciu VACUUM.

Je na vás, či potrebujete vynulovať počítadlo sekvencií tabuľky (RESTART IDENTITY).

#3: Všetci – striedajte sa!

Keďže pracujeme vo vysoko konkurenčnom prostredí, zatiaľ čo my tu kontrolujeme, že v tabuľke nie sú žiadne záznamy, mohol tam už niekto niečo napísať. O tieto informácie by sme nemali prísť, no a čo? Je to tak, musíme sa uistiť, že to nikto nemôže s istotou zapísať.

Aby sme to dosiahli, musíme povoliť SERIALIZOVATEĽNÝ-izolácia pre našu transakciu (áno, tu začneme transakciu) a „pevne“ uzamkneme tabuľku:

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

Táto úroveň blokovania je určená operáciami, ktoré na nej chceme vykonávať.

#4: Konflikt záujmov

Prichádzame sem a chceme nápis „zamknúť“ – čo keby na ňom bol v tej chvíli niekto aktívny, napríklad z neho čítal? Budeme „visieť“ čakaním na uvoľnenie tohto bloku a ostatní, ktorí chcú čítať, nám nabehnú...

Aby sa to nestalo, „obetujeme sa“ – ak sa nám nepodarilo získať zámok do určitého (prijateľne krátkeho) času, tak dostaneme výnimku zo základne, ale aspoň do toho nebudeme príliš zasahovať. iní.

Ak to chcete urobiť, nastavte premennú relácie lock_timeout (pre verzie 9.3+) alebo/a statement_timeout. Hlavná vec na zapamätanie je, že hodnota statement_timeout platí až od nasledujúceho príkazu. Teda takto pri lepení - nebude fungovať: 

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

Aby sme sa neskôr nemuseli zaoberať obnovovaním „starej“ hodnoty premennej, použijeme formulár NASTAVIŤ MIESTNE, ktorá obmedzuje rozsah nastavenia na aktuálnu transakciu.

Pamätáme si, že statement_timeout sa vzťahuje na všetky nasledujúce požiadavky, aby sa transakcia nemohla natiahnuť na neprijateľné hodnoty, ak je v tabuľke veľa údajov.

#5: Kopírovanie údajov

Ak tabuľka nie je úplne prázdna, údaje sa budú musieť znova uložiť pomocou pomocnej dočasnej tabuľky:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Podpis ON COMMITS DROP znamená, že v momente ukončenia transakcie dočasná tabuľka prestane existovať a nie je potrebné ju ručne odstraňovať v kontexte pripojenia.

Keďže predpokladáme, že „živých“ údajov nie je veľa, táto operácia by mala prebehnúť pomerne rýchlo.

No, to je všetko! Po dokončení transakcie nezabudnite spustiť ANALÝZA v prípade potreby normalizovať štatistiku tabuľky.

Skladanie finálneho scenára

Používame 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é neskopírovať údaje druhýkrát?V zásade je možné, ak samotný oid tabuľky nie je viazaný na žiadne iné aktivity zo strany BL alebo FK zo 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;

Spustite skript na zdrojovej tabuľke a skontrolujte 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šetko vyšlo! Tabuľka sa zmenšila 50-krát a všetky AKTUALIZÁCIE opäť bežia rýchlo.

Zdroj: hab.com

Pridať komentár