Când VACUUM eșuează, curățăm masa manual

VID poate „curăța” dintr-un tabel în PostgreSQL doar ce nimeni nu poate vedea - adică nu există o singură cerere activă care a început înainte ca aceste înregistrări să fie schimbate.

Dar dacă un astfel de tip neplăcut (încărcare OLAP pe termen lung pe o bază de date OLTP) există încă? Cum curăță în mod activ masa de înfășat înconjurat de interogări lungi și nu călca pe greblă?

Întinderea greblei

Mai întâi, să stabilim care este problema pe care vrem să o rezolvăm și cum poate apărea.

De obicei se întâmplă această situație pe o masă relativ mică, dar în care apare multe schimbari. De obicei asta sau diferit contoare/agregate/evaluări, pe care se execută adesea UPDATE, sau tampon-coadă pentru a procesa un flux constant de evenimente, ale căror înregistrări sunt în mod constant INSERT/DELETE.

Să încercăm să reproducem opțiunea cu evaluări:

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;

Și în paralel, în altă legătură, începe o cerere lungă, lungă, culegând niște statistici complexe, dar nu ne afectează masa:

SELECT pg_sleep(10000);

Acum actualizăm valoarea unuia dintre contoare de multe, de multe ori. Pentru puritatea experimentului, să facem asta în tranzacții separate folosind dblinkcum se va întâmpla în realitate:

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

Ce s-a întâmplat? De ce chiar și pentru cea mai simplă ACTUALIZARE a unei singure înregistrări timpul de execuție a scăzut de 7 ori - de la 0.524 ms la 3.808 ms? Și ratingul nostru crește din ce în ce mai lent.

Totul este vina MVCC.

Este vorba despre Mecanismul MVCC, ceea ce face ca interogarea să caute prin toate versiunile anterioare ale intrării. Deci, să curățăm masa noastră de versiunile „moarte”:

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

Oh, nu e nimic de curățat! Paralel Solicitarea de rulare interferează cu noi - la urma urmei, s-ar putea ca într-o zi să vrea să apeleze la aceste versiuni (ce ar fi dacă?), iar ele ar trebui să fie disponibile pentru el. Și, prin urmare, nici VACUUM FULL nu ne va ajuta.

„Prăbușirea” tabelului

Dar știm sigur că această interogare nu are nevoie de tabelul nostru. Prin urmare, vom încerca în continuare să readucem performanța sistemului la limitele adecvate eliminând tot ce nu este necesar din tabel - cel puțin „manual”, deoarece VACUUM renunță.

Pentru a fi mai clar, să ne uităm la exemplul unui tabel tampon. Adică, există un flux mare de INSERT/DELETE, iar uneori tabelul este complet gol. Dar dacă nu este gol, trebuie salvați conținutul actual.

#0: Evaluarea situației

Este clar că puteți încerca să faceți ceva cu tabelul chiar și după fiecare operațiune, dar acest lucru nu are prea mult sens - suprasarcina de întreținere va fi în mod clar mai mare decât debitul interogărilor țintă.

Să formulăm criteriile - „este timpul să acționăm” dacă:

• VACUUM a fost lansat cu destul de mult timp în urmă
  Ne așteptăm la o încărcătură grea, așa că lăsați-o 60 secunde de la ultimul [auto]VACUUM.
• dimensiunea tabelului fizic este mai mare decât țintă
  Să-l definim ca de două ori numărul de pagini (blocuri de 8KB) față de dimensiunea minimă - 1 blk pentru grămada + 1 blk pentru fiecare index - pentru o masă potențial goală. Dacă ne așteptăm ca o anumită cantitate de date să rămână întotdeauna în buffer „în mod normal”, este rezonabil să modificăm această formulă.

Cerere de verificare

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: Încă VACUUM

Nu putem ști în avans dacă o interogare paralelă interferează în mod semnificativ cu noi - exact câte înregistrări au devenit „învechite” de când a început. Prin urmare, atunci când decidem să procesăm cumva tabelul, în orice caz, ar trebui mai întâi să executăm pe el VID - spre deosebire de VACUUM FULL, nu interferează cu procesele paralele care lucrează cu date de citire-scriere.

În același timp, poate curăța imediat cea mai mare parte a ceea ce am dori să eliminăm. Da, iar întrebările ulterioare de pe acest tabel vor ajunge la noi prin "hot cache", ceea ce va reduce durata acestora - și, prin urmare, timpul total de blocare a altora prin tranzacția noastră de service.

#2: E cineva acasă?

Să verificăm dacă există ceva în tabel:

TABLE tbl LIMIT 1;

Dacă nu a mai rămas o singură înregistrare, atunci putem economisi mult la procesare prin simpla procedură TRUNCHIA:

Acționează la fel ca o comandă DELETE necondiționată pentru fiecare tabel, dar este mult mai rapid, deoarece nu scanează de fapt tabelele. Mai mult, eliberează imediat spațiu pe disc, astfel încât nu este nevoie să efectuați ulterior o operațiune VACUUM.

Dacă trebuie să resetați contorul secvenței de tabel (RESTART IDENTITY) rămâne la latitudinea dvs. să decideți.

#3: Toată lumea - luați pe rând!

Din moment ce lucrăm într-un mediu extrem de competitiv, în timp ce suntem aici verificând că nu există intrări în tabel, cineva ar fi putut deja să scrie ceva acolo. Nu ar trebui să pierdem aceste informații, deci ce? Așa este, trebuie să ne asigurăm că nimeni nu o poate nota cu siguranță.

Pentru a face acest lucru, trebuie să activăm SERIALIZABIL-izolare pentru tranzacția noastră (da, aici începem o tranzacție) și blocați tabelul „strâns”:

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

Acest nivel de blocare este determinat de operațiunile pe care dorim să le facem asupra acestuia.

#4: Conflict de interese

Venim aici și vrem să „blocam” semnul - ce se întâmplă dacă cineva a fost activ pe el în acel moment, de exemplu, citind din el? Vom „atârna” așteptând ca acest bloc să fie eliberat, iar alții care vor să citească vor da peste noi...

Pentru a preveni acest lucru, ne vom „sacrifica” - dacă nu am reușit să obținem o blocare într-un anumit timp (acceptabil de scurt), atunci vom primi o excepție de la bază, dar cel puțin nu vom interveni prea mult cu alții.

Pentru a face acest lucru, setați variabila sesiune lock_timeout (pentru versiunile 9.3+) sau/și statement_timeout. Principalul lucru de reținut este că valoarea statement_timeout se aplică numai de la următoarea instrucțiune. Adică așa în lipire - nu va funcționa:

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

Pentru a nu avea de-a face cu refacerea valorii „vechi” a variabilei mai târziu, folosim formularul SETARE LOCAL, care limitează domeniul de aplicare al setării la tranzacția curentă.

Ne amintim că statement_timeout se aplică tuturor solicitărilor ulterioare, astfel încât tranzacția să nu se întindă la valori inacceptabile dacă există o mulțime de date în tabel.

#5: Copiați datele

Dacă tabelul nu este complet gol, datele vor trebui re-salvate folosind un tabel temporar auxiliar:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Semnătură PE COMMIT DROP înseamnă că în momentul încheierii tranzacției, tabelul temporar va înceta să mai existe și nu este nevoie să îl ștergeți manual în contextul conexiunii.

Deoarece presupunem că nu există multe date „în direct”, această operațiune ar trebui să aibă loc destul de repede.

Ei bine, asta-i tot! Nu uitați după finalizarea tranzacției rulați ANALYZE pentru a normaliza statisticile tabelului, dacă este necesar.

Alcătuirea scenariului final

Folosim acest „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;

Este posibil să nu copiați datele a doua oară?În principiu, este posibil dacă oidul mesei în sine nu este legat de alte activități din partea BL sau FK din partea 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;

Să rulăm scriptul pe tabelul sursă și să verificăm valorile:

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

Totul a mers! Tabelul s-a micșorat de 50 de ori și toate UPDATE-urile rulează din nou rapid.

Sursa: www.habr.com