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

VACUUM mundja "të pastrojë" nga tabela në PostgreSQL vetëm atë që askush nuk mund ta shohë - domethënë nuk ka asnjë kërkesë aktive, që ka filluar më herët se sa këto regjistra u ndryshuan.

Por nëse ekziston një tip i pakëndshëm (ngarkesë e gjatë OLAP në bazën OLTP)? Si të pastrojmë një tabelë në aktivitet të konsiderueshëm në një mjedis me kërkesa të gjata dhe të mos biem në gracka?

Të shohim grackat

Së pari do të përcaktojmë në çfarë përbëhet dhe si mund të ndodhë 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 në lidhje me numrat/ agregatët / renditjet, mbi të cilat kryhet shpesh UPDATE, ose buffer për përpunimin e ndonjë fluksi ngjarjesh që ndodhin vazhdimisht, regjistrat e të cilave vazhdimisht INSERT / DELETE.

Të përpiqemi të riprodhojmë një variant me renditjet:

CREO TABELA tbl(k tekst PRIMAR KEY, v integer);
KRIJO INDEKSO NE tbl(v DESC); -- sipas këtij indeksi do të ndërtojmë renditjen

INSERTO KEMBIMIN
  tabelë
Zgjidh
  chr(ascii('a'::tekst) + i) k
, 0 v
FROM
  gjenero_serie (0, 25) i;

Dhe paralelisht, në një lidhje tjetër, fillon një kërkesë shumë të gjatë, që mbledh ndonjë statistikë të komplikuar, por nuk preket nga tabela jonë:

SELECT pg_sleep(10000);

Tani ne shumë-më shumë herë përditësojmë vlerën e njërit prej numrave. Për pastërtinë e eksperimentit, do ta bëjmë këtë në transaksione të veçanta duke përdorur dblink, ashtu si do të ndodhte në realitet:

BËJ $$
SHYQYR
  i integer;
  tsb timestamp;
  tse timestamp;
  d dyfishim precis;
FILLON
  PERFORMO dblink_lidhje('dbname=' || current_database() || ' port=' || current_setting('port'));
  PËR i NË 1..10000 LOOP
    tsb = clock_timestamp();
    PERFORMO dblink($e$UPDATE tbl vendos v = v + 1 KU k = 'a';$e$);
    tse = clock_timestamp();
    NËSE i % 1000 = 0 ATËHERË
      d = (ekstrakto('epoch' nga tse) - ekstrakto('epoch' nga tsb)) * 1000;
      RAISE NOTICE 'i = %, exectime = %', lpad(i::tekst, 5), lpad(d::tekst, 5);
    PËR FUND LOOP
  PERFORMO dblink_shkëputje();
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 edhe për një UPDATE shumë të thjeshtë të regjistrit të vetëm koha e ekzekutimit degradoi 7 herë - nga 0.524ms në 3.808ms? Madhe dhe renditja jonë është duke u ndërtuar gjithnjë e më ngadalë.

I gjithë faji është MVCC.

Çështja është në mekanizmin MVCC, i cili detyron kërkesën të shikojë të gjitha versionet e mëparshme të regjistrit. Pra, le të pastrojmë tabelën tonë nga versionet "të vdekura":

VACUUM VERBOSE tbl;

INFORMACION:  po pastrohet "public.tbl"
INFORMACION:  "tbl": gjetur 0 të fshirë, 10026 versione të rruazave jo të fshira në 45 nga 45 faqe
PËRGJEGJESI:  10000 versione të vdekur nuk mund të fshihen akoma, xmin më i vjetër: 597439602

Oh, dhe nuk ka asgjë për të pastruar! Paralelisht kërkesa e ekzekutuar na pengon - sepse ai ndonjëherë mund të dëshirojë të qaset këtyre versioneve (a ndonjëherë?), dhe ato duhet të jenë të aksesueshme për të. Prandaj, edhe VACUUM FULL nuk do të na ndihmojë.

"Kombinojmë" tabelën

Por we know for sure that our table is not needed for that query. Therefore, we will try to return the system's performance to reasonable limits by removing everything unnecessary from the table — even if it means doing it "manually" since VACUUM is failing.

To make it clearer, let’s consider the example of a buffer table. There is a large flow of INSERT/DELETE operations, and sometimes the table turns out to be completely empty. But if it is not empty, we must preserve its current contents..

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

It’s clear that one could try to do something with the table after each operation, but this doesn't make much sense — the overhead for maintenance will clearly exceed the throughput of the target queries.

We will formulate the criteria — it's time to act, if:

VACUUM was started a long time ago,
We expect a heavy load, so let it be 60 seconds since the last [auto]VACUUM.
the physical size of the table exceeds the target
We will define it as double the number of pages (blocks of 8KB) relative to the minimum size — 1 blk on heap + 1 blk for each index — for a potentially empty table. If we expect that a certain volume of data will always remain in the buffer, it makes sense to tune this formula.

The check query

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- it would be more accurate to do * current_setting('block_size')::bigint here, but who changes the block size?..
, 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

We cannot know in advance how much a parallel query interferes with us — how many records have become "stale" since it started. Therefore, once we decide to process the table somehow, it's definitely worth performing VACUUM — unlike VACUUM FULL, it does not interfere with parallel processes working with read-write data.

At the same time, it can immediately clean up a large part of what we wanted to remove. Subsequent queries on this table will proceed from the "hot cache", which will reduce their duration — and, consequently, the total time blocking other transactions serviced by us.

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

Let’s check if there is anything left in the table at all:

TABLE tbl LIMIT 1;

If not a single record is left, we can save significantly on processing — just executing TRUNCATE:

It acts like an unconditional DELETE command for each table, but much faster, as it does not actually scan the tables. Moreover, it immediately frees up disk space, so a VACUUM operation after it is not required.

Whether you need to reset the table's sequence counter (RESTART IDENTITY) — decide for yourself.

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

Duke punojmë në kushte shumë konkurruese, ndërsa ne po kontrollojmë për mungesë të regjistrimeve në tabelë, dikush mund të kenë shkruar tashmë diçka atje. Ne nuk duhet ta humbasim këtë informacion, pra – çfarë? E saktë, duhet të bëjmë që askush të mos mund të shkruajë me siguri.

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

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

Ky nivel bllokimi është i nevojshëm për operacionet që duam të kryejmë mbi të.

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

Ne po vijmë këtu dhe duam të "bllokojmë" tabelën — por nëse në atë moment dikush ishte aktiv mbi të, për shembull, duke e lexuar? Ne do të 'nëpërkëmbemi' duke pritur për të liruar këtë bllokim, dhe të tjerët që duan të lexojnë do të përballen me ne...

Për të evituar që kjo të ndodhë, do të "sakrifikojmë vetveten" — nëse brenda një kohe të caktuar (padyshim të vogël) nuk arritëm të marrim bllokimin, atëherë do të marrim një përjashtim nga baza, por të paktën nuk do të pengojmë shumë të tjerët.

Për këtë do të vendosim një variabël seance lock_timeout (për versionet 9.3+) ose/edhe statement_timeout. E rëndësishme është të mbahet mend se vlera e statement_timeout zbaton vetëm për deklaratën e ardhshme. Pra, diçka e tillë në bashkim — nuk do të funksionojë:

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

Për të mos u angazhuar më vonë në rikthimin e vlerës 'të vjetër' të variablës, përdorim formën SET LOCAL, e cila kufizon fushën e veprimit të konfigurimit në transaksionin aktual.

Mbaj mend se statement_timeout shtrihet mbi të gjitha kërkesat e ardhshme, në mënyrë që transaksioni ynë të mos mund të zgjasë deri në disa përmasa të papranueshme, nëse të dhënat në tabelë megjithatë do të ishin shumë.

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

Nëse tabela rezultoi që nuk ishte krejtësisht bosh — të dhënat duhet të ruhet sërish përmes një tabele ndihmëse të përkohshme:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Nënshkrimi ON COMMIT DROP do të thotë se në momentin përfundimtar të transaksionit, tabela e përkohshme do të ndalojë së ekzistuari, dhe nuk do të nevojitet ta fshijmë manualisht në kontekstin e lidhjes.

Duke pasur parasysh që supozojmë se "të dhënat aktive" nuk janë shumë, atëherë kjo operacion duhet të kalojë mjaft shpejt.

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

Këtu kemi skriptin përfundimtar

Përdorim diçka të tillë si "pseudopiton":

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

Do të ekzekutojmë skriptin mbi tabelën origjinale dhe do të kontrollojmë metrikat:

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

Ishte gjithçka e suksesshme! Tabela u reduktua 50 herë, dhe të gjitha UPDATE merrnin përsëri shpejtësi.

Burimi: habr.com

Kur bëhet pasimi VACUUM — pastroni tabelën manualisht