ProHoster > Blog > administratë > Kur VACUUM dështon, ne pastrojmë tabelën me dorë

Kur VACUUM dështon, ne pastrojmë tabelën me dorë

vakum mund të "pastrohet" nga një tabelë në PostgreSQL vetëm çfarë askush nuk mund të shohë - d.m.th., nuk ka asnjë kërkesë të vetme aktive që ka filluar para se të ndryshohen këto regjistrime.

Por, çka nëse një lloj i tillë i pakëndshëm (ngarkesa afatgjatë OLAP në një bazë të dhënash OLTP) ekziston ende? Si pastroni tavolinën që ndryshon në mënyrë aktive rrethuar nga pyetje të gjata dhe nuk shkel mbi një grabujë?

Kur VACUUM dështon, ne pastrojmë tabelën me dorë

Shtrimi i grabukut

Së pari, le të përcaktojmë se cili është problemi që duam të zgjidhim dhe si mund të lindë.

Zakonisht kjo situatë ndodh në një tavolinë relativisht të vogël, por në të cilën ndodh shumë ndryshime. Zakonisht kjo ose ndryshe metra/agregate/vlerësime, mbi të cilin shpesh ekzekutohet UPDATE, ose buffer-radhë për të përpunuar disa rrjedha të vazhdueshme ngjarjesh, regjistrimet e të cilave janë vazhdimisht INSERT/DELETE.

Le të përpiqemi të riprodhojmë opsionin me vlerësime:

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;

Dhe paralelisht, në një lidhje tjetër, nis një kërkesë e gjatë, e gjatë, duke mbledhur disa statistika komplekse, por nuk ndikon në tryezën tonë:

SELECT pg_sleep(10000);

Tani ne përditësojmë vlerën e njërit prej numëruesve shumë e shumë herë. Për pastërtinë e eksperimentit, le ta bëjmë këtë në transaksione të veçanta duke përdorur dblinksi do të ndodhë në realitet:

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

Cfare ndodhi? Pse edhe për përditësimin më të thjeshtë të një rekordi të vetëm koha e ekzekutimit është degraduar me 7 herë - nga 0.524ms në 3.808ms? Dhe vlerësimi ynë po ndërtohet gjithnjë e më ngadalë.

Është i gjithë faji i MVCC.

Eshte e gjitha per Mekanizmi MVCC, gjë që bën që pyetja të shikojë të gjitha versionet e mëparshme të hyrjes. Pra, le të pastrojmë tabelën tonë nga versionet "të vdekura":

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, nuk ka asgjë për të pastruar! Paralele Kërkesa e ekzekutuar po na ndërhyn - në fund të fundit, ai një ditë mund të dëshirojë t'i drejtohet këtyre versioneve (po sikur?), dhe ato duhet të jenë të disponueshme për të. Dhe prandaj edhe VACUUM FULL nuk do të na ndihmojë.

"Shembja" e tryezës

Por ne e dimë me siguri se kjo pyetje nuk ka nevojë për tabelën tonë. Prandaj, ne ende do të përpiqemi të kthejmë performancën e sistemit në kufijtë e duhur duke eliminuar gjithçka të panevojshme nga tabela - të paktën "me dorë", pasi VACUUM heq dorë.

Për ta bërë më të qartë, le të shohim shembullin e rastit të një tabele buffer. Kjo do të thotë, ka një fluks të madh të INSERT/DELETE, dhe ndonjëherë tabela është plotësisht bosh. Por nëse nuk është bosh, ne duhet ruaj përmbajtjen e tij aktuale.

#0: Vlerësimi i situatës

Është e qartë se mund të përpiqeni të bëni diçka me tabelën edhe pas çdo operacioni, por kjo nuk ka shumë kuptim - shpenzimi i përgjithshëm i mirëmbajtjes do të jetë qartësisht më i madh se xhiroja e pyetjeve të synuara.

Le të formulojmë kriteret - "është koha për të vepruar" nëse:

  • VACUUM u lançua shumë kohë më parë
    Ne presim një ngarkesë të rëndë, kështu që le të jetë 60 dytë që nga [auto]VAKUUM-i i fundit.
  • Madhësia fizike e tabelës është më e madhe se objektivi
    Le ta përcaktojmë si dyfishin e numrit të faqeve (blloqe 8 KB) në krahasim me madhësinë minimale - 1 blk për grumbull + 1 blk për çdo indeks - për një tryezë potencialisht bosh. Nëse presim që një sasi e caktuar e të dhënave do të mbetet gjithmonë në tampon "normalisht", është e arsyeshme të ndryshohet kjo formulë.

Kërkesë verifikimi

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: Ende VAKUUM

Ne nuk mund ta dimë paraprakisht nëse një pyetje paralele po ndërhyn ndjeshëm me ne - saktësisht sa regjistrime janë bërë "të vjetruara" që nga fillimi. Prandaj, kur vendosim të përpunojmë disi tabelën, në çdo rast, fillimisht duhet të ekzekutojmë në të vakum - ndryshe nga VACUUM FULL, nuk ndërhyn në proceset paralele që punojnë me të dhënat e leximit-shkrimit.

Në të njëjtën kohë, ai mund të pastrojë menjëherë pjesën më të madhe të asaj që do të dëshironim të hiqnim. Po, dhe pyetjet pasuese në këtë tabelë do të shkojnë tek ne nga "cache e nxehtë", gjë që do të zvogëlojë kohëzgjatjen e tyre - dhe, për rrjedhojë, kohën totale të bllokimit të të tjerëve nga transaksioni ynë i shërbimit.

#2: A është dikush në shtëpi?

Le të kontrollojmë nëse ka ndonjë gjë në tabelë:

TABLE tbl LIMIT 1;

Nëse nuk ka mbetur asnjë rekord i vetëm, atëherë mund të kursejmë shumë në përpunim thjesht duke bërë TRUNGONI:

Vepron njësoj si një komandë DELETE e pakushtëzuar për secilën tabelë, por është shumë më e shpejtë pasi në fakt nuk skanon tabelat. Për më tepër, ai liron menjëherë hapësirën në disk, kështu që nuk ka nevojë të kryeni një operacion VACUUM më pas.

Nëse ju duhet të rivendosni numëruesin e sekuencës së tabelës (RISTART IDENTITY) varet nga ju që të vendosni.

#3: Të gjithë - merrni radhën!

Meqenëse ne punojmë në një mjedis shumë konkurrues, ndërsa jemi këtu duke kontrolluar që nuk ka shënime në tabelë, dikush mund të kishte shkruar tashmë diçka atje. Ne nuk duhet ta humbim këtë informacion, pra çfarë? Kjo është e drejtë, ne duhet të sigurohemi që askush nuk mund ta shkruajë me siguri.

Për ta bërë këtë duhet të mundësojmë E SERIALIZueshme-izolim për transaksionin tonë (po, këtu fillojmë një transaksion) dhe mbyllim "fort" tabelën:

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

Ky nivel bllokimi përcaktohet nga operacionet që duam të kryejmë në të.

#4: Konflikti i interesit

Ne vijmë këtu dhe duam të "kyçim" shenjën - po sikur dikush të ishte aktiv në të në atë moment, për shembull, duke lexuar prej saj? Ne do të "varemi" duke pritur që të dalë ky bllok dhe të tjerët që duan të lexojnë do të përplasen me ne...

Për të parandaluar që kjo të ndodhë, ne do të "sakrifikojmë veten" - nëse nuk mund të merrnim një bllokim brenda një kohe të caktuar (të pranueshme të shkurtër), atëherë do të marrim një përjashtim nga baza, por të paktën nuk do të ndërhyjmë shumë. të tjerët.

Për ta bërë këtë, vendosni ndryshoren e sesionit lock_timeout (për versionet 9.3+) ose/dhe deklarata_koha. Gjëja kryesore për të mbajtur mend është se vlera statement_timeout zbatohet vetëm nga deklarata tjetër. Kjo është, si kjo në ngjitje - nuk do të funksionojë: 

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

Për të mos u marrë më vonë me rivendosjen e vlerës "të vjetër" të ndryshores, ne përdorim formularin VENDOSET LOKALE, e cila kufizon shtrirjen e cilësimeve në transaksionin aktual.

Kujtojmë se deklarata_timeout zbatohet për të gjitha kërkesat pasuese, në mënyrë që transaksioni të mos mund të shtrihet në vlera të papranueshme nëse ka shumë të dhëna në tabelë.

#5: Kopjoni të dhënat

Nëse tabela nuk është plotësisht bosh, të dhënat do të duhet të ruhen përsëri duke përdorur një tabelë të përkohshme ndihmëse:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Nënshkrimi NË KOMITET DORËZIM do të thotë që në momentin që transaksioni përfundon, tabela e përkohshme do të pushojë së ekzistuari dhe nuk ka nevojë ta fshini manualisht në kontekstin e lidhjes.

Meqenëse supozojmë se nuk ka shumë të dhëna "live", ky operacion duhet të bëhet mjaft shpejt.

Epo, kjo është e gjitha! Mos harroni pasi të keni përfunduar transaksionin drejtuar ANALIZO për të normalizuar statistikat e tabelës nëse është e nevojshme.

Duke bashkuar skenarin përfundimtar

Ne përdorim këtë "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 është e mundur të mos kopjoni të dhënat për herë të dytë?Në parim, është e mundur nëse vetë oid-i i tabelës nuk është i lidhur me ndonjë aktivitet tjetër nga ana BL ose FK nga ana 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;

Le të ekzekutojmë skriptin në tabelën burimore dhe të kontrollojmë matjet:

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

Gjithçka funksionoi! Tabela është tkurrur me 50 herë dhe të gjitha përditësimet po ecin përsëri me shpejtësi.

Burimi: www.habr.com

Shto një koment