🥇Kur VACUUM dështon — pastrojmë tabelën manualisht

VACUUM mund të "pastrojë" nga tabela në PostgreSQL vetëm atë që askush nuk mund ta shohë — do të thotë se nuk ka asnjë kërkesë aktive, e cila ka filluar përpara se këto regjistrime të ndryshonin.

Dhe nëse ka një tip të pakëndshëm (ngarkesë të vazhdueshme OLAP në një bazë të dhënash OLTP) gjithsesi? Si të pastrojmë një tabelë që ndryshon aktivisht në një mjedis me kërkesa të gjata dhe të mos bie në grepa?

Të vendosim grepat

Së pari, le të përcaktojmë, në çfarë përbëhet dhe si mund të ndodhi problemi që duam të zgjidhim.

Zakonisht, kjo situatë ndodh në një tabelë relativisht të vogël, por në të cilën ndodhin shumë ndryshime. Zakonisht, kjo është ose diferenca numëruesve/aggregateve/rangimeve, mbi të cilat shpesh bëhet UPDATE, ose bufferi-radhë për përpunimin e një fluksi ngjarjesh që vazhdon, regjistrimet e të cilave janë gjithmonë INSERT/DELETE.

Le të përpiqemi të riprodhojmë një variant me rangimet:

KRIJONI TABELË tbl(k tekst PRIMARE KRYESORE, v integer);
KRIJO INDHEKS NË tbl(v DESC); -- sipas këtij indeksi do të ndërtosh rangimin

INSERT INTO
  tbl
Zgjidhni
  chr(ascii('a'::tekst) + i) k
, 0 v
Nga
  generate_series(0, 25) i;

Ndërkohë, në një lidhje tjetër, fillon një kërkesë e gjatë që mbledh ndonjë statistikë të komplikuar, por nuk prek tabelën tonë:

Zgjidhni pg_sleep(10000);

Tani ne shumë herë përditësojmë vlerën e një nga numëruesve. Për pastërtinë e eksperimentit le të bëjmë këtë në transaksione të veçanta duke përdorur dblink, si do të ndodhte në realitet:

BËJ $$
SHKELLOJ
  i integer;
  tsb timestamp;
  tse timestamp;
  d saktësi të dyfishtë;
FILLIM
  KRYEJ dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port'));
  PËR i NË 1..10000 LOOP
    tsb = clock_timestamp();
    KRYEJ dblink($e$UPDATE tbl SET v = v + 1 KU k = 'a';$e$);
    tse = clock_timestamp();
    NËSE i % 1000 = 0 ATËHERË
      d = (ekstrakto('epokë' nga tse) - ekstrakto('epokë' nga tsb)) * 1000;
      RAISE NOTICE 'i = %, exectime = %', lpad(i::tekst, 5), lpad(d::tekst, 5);
    FUND
  FUND LOOP;
  KRYEJ dblink_disconnect();
FUND;
$$ GJUHA plpgsql;

NJOFTIM:  i =  1000, exectime = 0.524
NJOFTIM:  i =  2000, exectime = 0.739
NJOFTIM:  i =  3000, exectime = 1.188
NJOFTIM:  i =  4000, exectime = 2.508
NJOFTIM:  i =  5000, exectime = 1.791
NJOFTIM:  i =  6000, exectime = 2.658
NJOFTIM:  i =  7000, exectime = 2.318
NJOFTIM:  i =  8000, exectime = 2.572
NJOFTIM:  i =  9000, exectime = 2.929
NJOFTIM:  i = 10000, exectime = 3.808

Çfarë ndodhi? Pse madje për një UPDATE të thjeshtë të një regjistrimi të vetëm koha e ekzekutimit ka degraduar me 7 herë — nga 0.524ms në 3.808ms? Po ashtu, rangimi ynë po ndërtohet gjithnjë e më ngadalë.

Përgjegjës është MVCC

Të gjithë do të thotë në mekanizmin MVCC, i cili e detyron kërkesën të shqyrtojë të gjitha versionet e mëparshme të regjistrimit. Le t'i pastrojmë tabelën tonë nga versionet "e vdekura":

VACUUM VERBOSE tbl;

INFO:  po vakuumohet "public.tbl"
INFO:  "tbl": u gjetën 0 versionet e fshirshme, 10026 versionet e panegociueshme të rreshtave në 45 nga 45 faqe
DETAL:  10000 versionet e vdekura të rreshtave nuk mund të fshihen akoma, xmin më i vjetër: 597439602

Ouf, nuk ka asgjë për të pastruar! Në të njëjtën kohë kërkesa që po ekzekutohet po na pengon — sepse ndonjëherë ajo mund të dëshirojë të qaset në këto versione (ndoshta?), dhe ato duhet të jenë të aksesueshme për të. Prandaj, edhe VACUUM FULL nuk do na ndihmojë.

"Kombinojmë" tabelën

Por ne e dimë me siguri se për atë kërkesë, tabela jonë nuk ka nevojë. Prandaj do të përpiqemi ende të rikthejmë performancën e sistemit në një nivel të arsyeshëm, duke hequr nga tabela gjithçka të panevojshme — të paktën "me dorë", që të dyja VACUUM dështon.

Për ta bërë më të qartë, le të shqyrtojmë rastin e tabelës-bufër. Domethënë, ka një fluks të madh INSERT/DELETE, dhe ndonjëherë tabela del krejtësisht bosh. Por nëse nuk është bosh, ne duhet të ruajmë përmbajtjen aktuale të saj.

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

Është e qartë se mund të përpiqemi të bëjmë diçka me tabelën pas çdo operacioni, por nuk ka shumë kuptim — kostot e mbajtjes do të jenë dukshëm më të larta se kapaciteti i kërkesave përkatëse.

Le të formulon kriteret — "ka ardhur koha të veprojmë", nëse:

VACUUM është përshkruar mjaft kohë më parë
Prandaj presim një ngarkesë të madhe, kështu që le të jetë 60 sekonda nga [auto]VACUUM i fundit.
masa fizike e tabelës është më e madhe se e synuara
Ta përcaktojmë si dyfishin e numrit të faqeve (blloqeve prej 8KB) në raport me masën minimale — 1 blk në heap + 1 blk për secilin nga indekset — për një tabelë potencialisht bosh. Nëse presim që në bufër 'normalisht' të ketë gjithmonë një sasi të caktuar të dhënash, kjo formulë është e arsyeshme të rregullohet.

Kërkesa e verifikimit

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- këtu është më e saktë të bëj * current_setting('block_size')::bigint, por kush e ndryshon madhësinë e bllokut?..
, 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

Nuk e dimë paraprakisht nëse kërkesa paralele na pengon shumë — sa pikërisht janë "rrjedhura" që nga fillimi i saj. Prandaj, kur vendosim ta procesojmë tabelën ndonjëherë, fillimisht duhet të kryejmë VACUUM — ai, për dallim nga VACUUM FULL, nuk e pengon proceset paralele që të punojnë me të dhënat për lexim-shkrim.

Po ashtu, ai mund të pastrojë menjëherë shumicën e atyre që do donim të hiqnim. Dhe kërkesat e mëpasshme për këtë tabelë do të shkojnë në "keshin e nxehtë", që do të shkurtojë kohën e tyre — pra, edhe kohën totale të bllokimit të tjerave nga transaksioni ynë shërbues.

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

Le të kontrollem — a ka ndonjë gjë në tabelë për të filluar:

TABLE tbl LIMIT 1;

Nëse nuk ka mbetur asnjë rekord, ne mund të kursejmë ndjeshëm në procesim — thjesht duke ekzekutuar TRUNCATE:

Ajo funksionon ashtu si një komandë DELETE pa kushte për çdo tabelë, por shumë më shpejt, pasi nuk skanon tabelat. Për më tepër, ajo menjëherë çliron hapësirën në disqet, kështu që operacioni VACUUM pas saj nuk është i nevojshëm.

Nëse duhet ta rifreshni numëruesin e sekuencës së tabelës (RESTART IDENTITY) — vendosni vetë.

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

Duke qenë se punojmë në kushte të larta konkurrencë, kur ne po kontrollojmë mungesën e regjistrave në tabelë, dikush mund të ketë shkruar already ndonjë gjë aty. Ne nuk duhet ta humbasim këtë informacion, pra — çfarë? E saktë, duhet të sigurohemi se askush nuk mund të shkruajë më.

Për këtë, na nevojitet të aktivizojmë SERIALIZABLE-izolimin për transaksionin tonë (po, këtu e fillojmë transaksionin) dhe të bllokojmë tabelën "përfundimisht":

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

Ky nivel bllokimi përcaktohet nga operacionet që duam të realizojmë mbi të.

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

Këtu ne vijmë dhe duam ta "bllokojmë" tabelën — e nëse atëkohë dikush kishte qenë aktiv mbi të, për shembull, po e lexonte? Ne do të "pendohemi" duke pritur lirimin e kësaj bllokade, dhe ata që duan të lexojnë do të ndeshen me ne...

Që kjo të mos ndodhë, do të "sakrifikojmë veten" — po që se për një kohë të caktuar (që lejohet të jetë e vogël) nuk arritëm të merrnim bllokadën, do të marrim një përjashtim nga baza, por së paku nuk do të pengojmë shumë të tjerët.

Për këtë, do të vendosim një variabël sesioni lock_timeout (për versionet 9.3+) ose/edhe statement_timeoutE rëndësishme të mbani mend se vlera e statement_timeout aplikohet vetëm me statement-in e ardhshëm. Pra, kështu në bashkim — nuk do të funksionojë:

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

Për të mos u marrë më vonë me rikthimin e vlerës "të vjetër" të variablës, përdorim formën SET LOCAL, e cila kufizon rrethin e zbatimit të cilësimit në transaksionin aktual.

Mbani mend se statement_timeout përfshin të gjitha kërkesat në vazhdim, që transaksioni të mos mund të zgjatet deri në përmasa të papranueshme, nëse ndonjë të dhënë do të kishte shumë në tabelë.

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

Nëse tabela rezultoi të mos ishte krejtësisht e zbrazët — të dhënat do të duhej të ruhet përsëri përmes një tabele ndihmëse temporale:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Nënshkrimi ON COMMIT DROP nënkupton se në momentin e përfundimit të transaksionit tabela temporale do të shqitet, duke e bërë që të mos kemi nevojë ta fshimë manualisht në kontekstin e lidhjes.

Duke pasur parasysh se supozojmë se nuk ka shumë të dhëna "aktive", kjo operacion duhet të kalojë mjaft shpejt.

Pra, kaq është! Mos harroni pas përfundimit të transaksionit të aktivizoni ANALYZE për normalizimin e statistikave të tabelës, nëse është e nevojshme.

Mblidhen skenarët përfundimtarë

Përdorim këtë "pseudo-piton":

# собираем статистику с таблицы
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 mund të mos kopjojmë të dhënat një herë tjetër?Në parim, mundemi, nëse në oid e tabelës vetë nuk janë të lidhura aktivitete të tjera nga BL ose FK nga Baza e të Dhënave:

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

Eshte nevojë të kalojmë skenarin në tabelën origjinale dhe të kontrollojmë metriket:

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

Çdo gjë shkoi mirë! Tabela u zvogëlua në 50 herë, dhe të gjitha UPDATE përsëri ecin shpejt.

Burimi: habr.com

Kur kur VACUUM — pastrojmë manualisht tabelën