🥇 Amikor a VACUUM nem működik — tisztítsuk meg a táblát manuálisan

VACUUM a PostgreSQL táblából csak azt lehet "törölni", ami senki számára nem látható — vagyis nincs aktív lekérdezés, amely korábban indult, mint ahogyan ezek a rekordok megváltoztak.

De mi van, ha van egy zavaró tényező (hosszan tartó OLAP-terhelés OLTP-adatbázison)? Hogyan takarítsuk ki az aktívan változó táblát hosszú lekérdezésekkel teli környezetben, anélkül, hogy hibába ütköznénk?

Feltárjuk a hibákat

Először határozzuk meg, hogy miben áll a probléma, és hogyan jelentkezhet az általunk megoldani kívánt helyzet.

Egy ilyen helyzet általában akkor fordul elő viszonylag kis táblán, de amelyben nagyon sok változás történik . Általában ezek különbözőszámlálók/aggregátumok/besorolások , amelyeken gyakran hajtanak végre UPDATE-ot, vagytárba valamilyen állandó eseményfolyam feldolgozásához, amelynek adatai folyamatosan INSERT/DELETE történnek. Próbáljunk reprodukálni egy példát a besorolásokkal:

CREATE TABLE tbl(k text PRIMARY KEY, v integer); CREATE INDEX ON tbl(v DESC); -- ezen az indexen fogjuk összeállítani a besorolástINSERT INTO tbl SELECT chr(ascii('a'::text) + i) k , 0 v FROM generate_series(0, 25) i;

Közben egy másik kapcsolatban egy hosszú lekérdezés kezdődik, amely valamilyen bonyolult statisztikát gyűjt, de

nem érinti a mi táblánkat SELECT pg_sleep(10000);:

Most sok-sok alkalommal frissítjük egy számláló értékét. A tiszta kísérlet érdekében ezt

külön tranzakciókban végezzük dblink segítségével , ahogyan ez a valóságban történne: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;

ÉRTESÍTÉS:  i =  1000, végrehajtási idő = 0.524
ÉRTESÍTÉS:  i =  2000, végrehajtási idő = 0.739
ÉRTESÍTÉS:  i =  3000, végrehajtási idő = 1.188
ÉRTESÍTÉS:  i =  4000, végrehajtási idő = 2.508
ÉRTESÍTÉS:  i =  5000, végrehajtási idő = 1.791
ÉRTESÍTÉS:  i =  6000, végrehajtási idő = 2.658
ÉRTESÍTÉS:  i =  7000, végrehajtási idő = 2.318
ÉRTESÍTÉS:  i =  8000, végrehajtási idő = 2.572
ÉRTESÍTÉS:  i =  9000, végrehajtási idő = 2.929
ÉRTESÍTÉS:  i = 10000, végrehajtási idő = 3.808

Mi történt? Miért romlott le a végrehajtási idő egyetlen egyszerű UPDATE esetén

7-szeresére — 0.524 ms-től 3.808 ms-ig? Ráadásul a besorolásunk egyre lassabbá válik. Mindez a MVCC miatt van.

Maga a probléma a

MVCC mechanizmusban rejlik. MVCC mechanizmusában rejlik., amely arra kényszeríti a lekérdezést, hogy megvizsgálja az összes korábbi rekordverziót. Tisztítsuk meg tehát táblánkat a "halott" verzióktól:

VACUUM VERBOSE tbl;

INFO:  táblázat ürítése "public.tbl"
INFO:  "tbl": 0 eltávolítható, 10026 eltávolíthatatlan sorverziót találtunk 45-ből 45 oldalban
Részletek:  10000 halott sorverzió még nem távolítható el, legrégebbi xmin: 597439602

Hát, nincs mit tisztítani! Párhuzamosan folyamatban lévő lekérdezés akadályoz minket — mert előfordulhat, hogy egyszer csak szeretne hozzáférni ezekhez a verziókhoz (mi van, ha?), és elérhetőeknek kell lenniük számára. Ezért még a VACUUM FULL sem segít nekünk.

"Összepréseljük" a táblát

De pontosan tudjuk, hogy annak a lekérdezésnek nem szükséges a táblánk. Ezért próbáljuk meg helyreállítani a rendszerteljesítményt ésszerű keretek közé, kidobva a táblából minden felesleget — legalábbis "kézzel", mivel a VACUUM nem segít.

Hogy világosabb legyen, nézzük meg már a táblázati puffert példáján. Tehát nagy mennyiségű INSERT/DELETE zajlik, és néha a táblázat teljesen üres is lehet. De ha nem üres, meg kell tartani a jelenlegi tartalmát.

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

Nyilvánvaló, hogy megpróbálhatunk valamit tenni a táblával akár minden művelet után, de ennek nagy értelme nincs — a fenntartási költségek egyértelműen magasabbak lesznek, mint a célzott lekérdezések áteresztőképessége.

Fogalmazzunk meg kritériumokat — "itt az ideje cselekedni", ha:

a VACUUM régóta futott
Nagy terhelésre számítunk, ezért legyen 60 másodperc az utolsó [auto]VACUUM óta.
a tábla fizikai mérete meghaladja a célértéket
Határozzuk meg úgy, mint a blokk (8KB) kétszeresét a minimális mérethez képest — 1 blk a heap-n | + 1 blk minden indexhez — potenciálisan üres táblához. Ha azonban azt várjuk, hogy a puffernél "normálisan" mindig lesz valamekkora mennyiségű adat, ezt a képletet érdemes finomhangolni.

Ellenőrző lekérdezés

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- itt helyesebb a * current_setting('block_size')::bigint-et alkalmazni, de ki változtatja a blokk méretét?..
, 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

Nem tudjuk előre, mennyire zavar minket a párhuzamos lekérdezés - hány bejegyzés "elavult" a kezdete óta. Ezért, amikor végül eldöntjük, hogyan dolgozzunk fel a táblázaton, mindenképp először érdemes végrehajtani rajta VACUUM — ez, ellentétben a VACUUM FULL-lal, nem akadályozza a párhuzamos folyamatokat az olvasás-írás adatok kezelésében.

Ezen kívül azonnal tisztíthatja a legtöbb dolgot, amit el szeretnénk távolítani. Sőt, a későbbi lekérdezések ezen a táblázaton a "forró cache"-ből fognak menni, ami lerövidíti a végrehajtásukat — tehát összességében csökkenti más tranzakcióink által okozott blokkolási időt.

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

Nézzük meg - van-e egyáltalán bármi a táblázatban:

TABLE tbl LIMIT 1;

Ha nincs egyetlen bejegyzés sem, akkor jelentős megtakarítást érhetünk el a feldolgozás során - egyszerűen végrehajtva TRUNCATE:

Ez úgy működik, mint egy feltétel nélküli DELETE parancs minden táblázatra, de sokkal gyorsabb, mivel gyakorlatilag nem vizsgálja át a táblázatokat. Ráadásul azonnal felszabadítja a lemezterületet, így nem szükséges a VACUUM műveletet végrehajtani utána.

Önnek kell-e resetelnie a táblázat sorozatszámlálóját (RESTART IDENTITY) - döntse el maga.

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

Mivel magas versenyképességű környezetben dolgozunk, amíg itt ellenőrizzük a táblázat bejegyzéseinek hiányát, valaki már írt valamit oda. Nem szabad, hogy ezt az információt elveszítsük, tehát - mi a teendő? Pontosan, meg kell tennünk, hogy senki ne tudjon biztosan írni.

Ehhez aktiválni kell SERIALIZABLE-izolációt a tranzakciónk számára (igen, itt kezdjük a tranzakciót) és blokkoljuk a táblázatot "halálosan":

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

Ez a blokkolási szint pontosan azokkal a műveletekkel kapcsolatos, amelyeket el szeretnénk végezni rajta.

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

Itt megérkezünk, és szeretnénk "lezárni" a táblázatot - de ha közben valaki aktív volt rajta, például olvasott belőle? Mi "lebénulunk" a zár feloldásának várakozásában, míg mások, akik olvasni szeretnének, már belénk ütköznek...

Hogy ez ne történhessen meg, "feláldozzuk magunkat" - ha egy adott (megengedhetően kicsi) időn belül mégsem tudtuk megszerezni a zárat, akkor a rendszer egy kivételt küld, de legalább nem zavarjuk meg túlságosan a többieket.

Ehhez állítsuk be a session változót lock_timeout (a 9.3+ verziókhoz) vagy/és statement_timeoutA legfontosabb, hogy a statement_timeout értéke csak a következő utasításra vonatkozik. Ezért a következőképpen nem működik: nem fog működni:

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

Annak érdekében, hogy ne kelljen később visszaállítani a változó „régi” értékét, használjuk a következő formát: SET LOCAL, amely korlátozza a beállítás hatályát az aktuális tranzakcióra.

Ne feledjük, hogy a statement_timeout minden következő lekérdezésre vonatkozik, hogy a tranzakció ne nyúljon el elfogadhatatlan mértékig, ha sok adat található a táblában.

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

Ha a tábla nem teljesen üres, az adatokat egy ideiglenes segédtáblán keresztül kell újból menteni:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Aláírás ON COMMIT DROP azt jelenti, hogy a tranzakció befejezésekor az ideiglenes tábla megszűnik létezni, és nem kell manuálisan törölni a kapcsolat kontextusában.

Mivel azt feltételezzük, hogy nem sok „élő” adat van, ez az művelet viszonylag gyorsan le kell, hogy folytatódjon.

Nos, nagyjából ennyi! Ne felejtsétek el a tranzakció befejezése után futtatni az ANALYZE-t a táblázat statisztikáinak normalizálásához, ha ez szükséges.

Összeállítjuk a végső szkriptet

Használjunk egy ilyen „álpython”-t:

# собираем статистику с таблицы
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;

Lehet, hogy nem kell másodszor másolni az adatokat?Elvileg lehet, ha a tábla oid-jához nem kapcsolódnak más tevékenységek az BL vagy FK oldaláról:

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

Futtassuk a szkriptet az eredeti táblán, és ellenőrizzük a metrikákat:

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

Minden sikerült! A tábla 50-szer kisebb lett, és minden UPDATE újra gyorsan fut.

Forrás: habr.com

Amikor a VACUUM elmarad — takarítsd ki a táblázatot kézzel