Când VACUUM e insuficient — curățăm manual tabela

VACUUM poate „curăța” din tabelul din PostgreSQL doar ceea ce nimeni nu poate vedea — adică nu există nicio cerere activă care a început înainte ca aceste înregistrări să fie modificate.

Dar dacă există un astfel de tip neplăcut (încărcare OLAP de lungă durată pe baza OLTP)? Cum să curățăm un tabel care se modifică activ într-un mediu cu cereri lungi și să nu dăm de niște probleme?

Să analizăm problemele

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

De obicei, o astfel de situație apare pe un tabel relativ mic, dar în care se petrec multe modificări.De obicei, sunt fie diferite conturi/aggregatori/ clasificări, pe care se execută frecvent UPDATE, fie un buffer de așteptare pentru procesarea unui flux constant de evenimente, înregistrările despre care sunt inserate/șterse continuu.

Să încercăm să reproducem un exemplu cu clasificările:

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- pe acest index ne vom baza clasificarea

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

Între timp, într-o altă conexiune, se declanșează o cerere lungă, care adună o statistică complexă, dar nu afectează tabelul nostru:

SELECT pg_sleep(10000);

Acum actualizăm de foarte multe ori valoarea uneia dintre conturi. Pentru puritatea experimentului, vom face asta în tranzacții separate folosind dblink, așa cum 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 cel mai simplu UPDATE al unei singure înregistrări timpul de execuție a degradat în 7 ori — de la 0.524ms la 3.808ms? Și clasificarea noastră se construiește din ce în ce mai lent.

Totul este din cauza MVCC

Totul se rezumă la mecanismul MVCC, care obligă cererea să răsfoiască toate versiunile anterioare ale înregistrării. Așadar, să curățăm tabela noastră de versiunile „moarte”:

VACUUM VERBOSE tbl;

INFO:  curățare "public.tbl"
INFO:  "tbl": găsite 0 versiuni de rând eliminabile, 10026 versiuni de rând nerecuperabile în 45 din 45 pagini
DETALIU:  10000 versiuni de rând moarte nu pot fi eliminate încă, xmin-ul celui mai vechi: 597439602

Oh, dar nu avem nimic de curățat! Paralelel cererea care se execută ne împiedică — pentru că s-ar putea să vrea să acceseze aceste versiuni (numai dacă?), și ele trebuie să rămână disponibile pentru el. Așadar, nici măcar VACUUM FULL nu ne va ajuta.

„Compactăm” tabela

Dar știm cu siguranță că acelei cereri tabela noastră nu îi este necesară. Așadar, să încercăm totuși să readucem performanța sistemului în limitele adecvate, eliminând tot ce este inutil din tabelă - măcar „manual”, din moment ce VACUUM refuză.

Pentru a fi mai clar, să luăm deja un exemplu de tabel tampon. Adică, există un flux mare de INSERT/DELETE, și uneori tabela rămâne complet goală. Dar dacă nu este goală, trebuie să întreținem conținutul său actual.

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

Este clar că putem încerca să facem ceva cu tabela chiar după fiecare operație, dar nu are mult sens - costurile de întreținere vor fi evident mai mari decât capacitatea de procesare a cererilor țintă.

Să formulăm criteriile - „e deja timpul să acționăm”, dacă:

• VACUUM a fost inițiat cu mult timp în urmă
  Anticipăm o sarcină mare, de aceea să fie 60 de secunde de la [auto]VACUUM.
• dimensiunea fizică a tabelei este mai mare decât cea țintită
  Să o definim ca fiind dublul numărului de pagini (blocuri de 8KB) față de dimensiunea minimă - 1 blk pe heap + 1 blk pentru fiecare din indexuri — pentru o tabelă potențial goală. Dacă ne așteptăm că în tampon „normal” va rămâne întotdeauna un anumit volum de date, această formulă merită ajustată.

Cererea de verificare

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- aici ar fi corect să facem * current_setting('block_size')::bigint, dar cine schimbă dimensiunea blocului?..
, 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

Nu putem ști dinainte cât de mult ne va afecta o interogare paralelă - câte înregistrări au devenit „învechite” de la începutul acesteia. Prin urmare, când ne vom decide să procesăm tabela, mai întâi ar trebui să executăm VACUUM — spre deosebire de VACUUM FULL, nu interferează cu procesele paralele care lucrează cu datele de citire și scriere.

De asemenea, poate curăța o mare parte din ceea ce dorim să eliminăm. Și interogările ulterioare pentru această tabelă vor merge pe „cache-ul cald”, ceea ce va reduce durata acestora - iar, prin urmare, și timpul total de blocare a altor tranzacții suportate de noi.

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

Hai să verificăm - există în tabelă ceva?

TABLE tbl LIMIT 1;

Dacă nu a mai rămas nicio înregistrare, putem economisi mult la procesare - pur și simplu executând TRUNCATE:

Aceasta funcționează la fel ca o comandă DELETE necondiționată pentru fiecare tabelă, dar mult mai rapid, deoarece de fapt nu scanează tabelele. Mai mult decât atât, eliberază imediat spațiul de pe disc, astfel încât nu este necesar să executăm operația VACUUM după ea.

Trebuie să decideti dacă doriți să resetați contorul secvenței tabelei (RESTART IDENTITY) - decideți voi.

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

Având în vedere că lucrăm în condiții de mare concurență, în timp ce verificăm lipsa înregistrărilor în tabelă, cineva ar fi putut deja să scrie ceva acolo. Nu trebuie să pierdem aceste informații, așa că - ce? Corect, trebuie să ne asigurăm că nimeni nu poate scrie.

Pentru aceasta, trebuie să activăm SERIALIZABLE-izolația pentru tranzacția noastră (da, aici începem tranzacția) și să blocăm tabela „total”:

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

Acest nivel de blocare este determinat de operațiile pe care dorim să le efectuam asupra ei.

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

Venim aici și dorim să „blocăm” tabelul – iar dacă în acel moment cineva a fost activ pe el, de exemplu, l-a citit? Vom „aștepta” eliberarea acestei blocări, iar alții care doresc să citească se vor ciocni de noi...

Pentru a nu se întâmpla așa, „ne vom sacrifica” - dacă în decursul unui timp (considerabil de scurt) nu am reușit să obținem totuși blocarea, vom primi o excepție din baza de date, dar cel puțin nu vom interfera prea mult cu ceilalți.

Pentru aceasta, vom seta o variabilă de sesiune lock_timeout (pentru versiunile 9.3+) sau/și statement_timeoutPrimul lucru de reținut este că valoarea statement_timeout se aplică doar următoarei instrucțiuni. Adică astfel în combinație — nu va funcționa:

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

Pentru a nu fi nevoit să restaurez apoi „valoarea veche” a variabilei, folosim forma SET LOCAL, care limitează domeniul de aplicare al setării la tranzacția curentă.

Rețineți că statement_timeout se aplică tuturor cererilor ulterioare, astfel încât tranzacția să nu se poată extinde la dimensiuni inacceptabile, în cazul în care datele din tabel s-ar dovedi a fi numeroase.

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

Dacă tabelul nu este complet gol — datele vor trebui salvate din nou printr-un tabel temporar auxiliar:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Semnătura ON COMMIT DROP înseamnă că, la finalizarea tranzacției, tabelul temporar va înceta să existe și nu va fi nevoie să ne ocupăm de ștergerea manuală a acestuia în contextul conexiunii.

Deoarece presupunem că nu sunt foarte multe date „active”, această operațiune ar trebui să decurgă destul de repede.

Ei bine, cam asta e tot! Nu uitați să rulați ANALYZE pentru normalizarea statisticii tabelului, dacă este necesar.

Adunăm scriptul final

Folosim un așa-numit „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;

Se poate ca datele să nu fie copiate a doua oară?În principiu, este posibil, dacă pe oid-ul tabelului în sine nu se leagă alte activități din partea BL sau FK din partea Bazei de Date:

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

Rulăm scriptul pe tabelul inițial și verificăm metricile:

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

A ieșit bine! Tabelul s-a micșorat de 50 de ori, iar toate UPDATE-urile rulează din nou rapid.

Sursa: habr.com