🥇Ha a PORSZÍVÓ meghibásodik, kézzel tisztítjuk meg az asztalt

VÁKUUM csak egy olyan táblából tud "kitakarítani" PostgreSQL-ben, amely senki sem láthatja - azaz egyetlen aktív kérés sem indult el a rekordok módosítása előtt.

De mi van akkor, ha egy ilyen kellemetlen típus (hosszú távú OLAP terhelés egy OLTP adatbázison) létezik? Hogyan? letakarítani egy aktívan pelenkázó asztalt hosszú lekérdezésekkel körülvéve, és nem lépsz gereblyére?

Terítsük ki a gereblyét

Először is, határozzuk meg, hogy milyen problémát szeretnénk megoldani, és hogyan jöhet létre.

Ez a helyzet általában előfordul egy viszonylag kis asztalon, de amelyben ez megtörténik sok változásÁltalában ezek vagy különbözőek számlálók/aggregátumok/értékelések, amelyen nagyon gyakran történik UPDATE (frissítés), vagy puffer sor hogy folyamatosan futó eseményfolyamot dolgozzon fel, amelynek rekordjait folyamatosan INSERT/DELETE műveletek végzik.

Próbáljuk meg reprodukálni az értékelésekkel ellátott verziót:

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;

És ezzel párhuzamosan, egy másik kapcsolatban, egy nagyon hosszú lekérdezés indul, amely néhány összetett statisztikát gyűjt, de nem befolyásolja az asztalunkat:

SELECT pg_sleep(10000);

Most az egyik számláló értékét sokszor frissítjük. A kísérlet tisztasága érdekében a következőket tesszük külön tranzakciókban dblink használatával, hogyan fog ez történni a valóságban:

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

Mi történt? Miért történt még egyetlen rekord legegyszerűbb FRISSÍTÉSE is? a végrehajtási idő 7-szeresére romlott — 0.524 ms-ról 3.808 ms-ra? És az értékelésünk egyre lassabban épül.

Az MVCC a hibás mindenért

Minden arról szól, MVCC mechanizmus, ami arra kényszeríti a lekérdezést, hogy a rekord összes korábbi verzióját átnézze. Tehát tisztítsuk meg a táblázatunkat a „halott” verzióktól:

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

Ó, nincs mit takarítani! Ezzel párhuzamosan egy folyamatban lévő lekérdezés zavarja a munkánkat – végül is lehet, hogy egyszer hozzá akar majd férni ezekhez a verziókhoz (mi van, ha?), és hozzáférhetőnek kell lenniük. És ezért még a VACUUM FULL sem segít rajtunk.

Becsapjuk az asztalt

De biztosan tudjuk, hogy ehhez a lekérdezéshez nincs szükség a táblázatunkra. Próbáljuk meg tehát a rendszer teljesítményét ésszerű keretek közé hozni az összes felesleges adat eltávolításával a táblázatból – legalább manuálisan, mivel a VACUUM nem működik.

Hogy ezt tisztábban lássuk, nézzük meg egy puffertábla példáját. Vagyis nagyszámú INSERT/DELETE művelet történik, és néha a tábla teljesen üres. De ha nem üres, akkor a következőt kell tennünk: mentse el az aktuális tartalmát.

#0: Értékeld a helyzetet

Egyértelmű, hogy minden művelet után megpróbálhatsz valamit csinálni a táblázattal, de ennek nincs sok értelme – a karbantartás többletterhelése egyértelműen nagyobb lesz, mint a cél lekérdezések átviteli sebessége.

Fogalmazzuk meg a kritériumokat - "itt az ideje cselekedni", ha:

A VACUUM-ot már jó ideje piacra dobták.
Nagy teherre számítunk, hát legyen. 60 másodperc az utolsó [automatikus]porszívózás óta.
a tábla fizikai mérete nagyobb, mint a céltábla mérete
Definiáljuk úgy, hogy az oldalak száma (8 KB-os blokkok) a minimális mérethez viszonyítva kétszerese - 1 blokk halmonként + 1 blokk indexenként — egy potenciálisan üres tábla esetén. Ha azt várjuk, hogy a puffer mindig tartalmazzon valamilyen mennyiségű adatot, akkor érdemes módosítani ezt a képletet.

Ellenőrzési kérelem

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: Mindenképpen porszívózz

Előre nem tudhatjuk, hogy egy párhuzamos lekérdezés mennyire akadályoz minket – vagy hogy pontosan hány rekord „elavult” a kezdete óta. Ezért, amikor végre úgy döntünk, hogy valahogy feldolgozzuk a táblát, mindenképpen először futtassunk rajta egy lekérdezést. VÁKUUM — a VACUUM FULL-lal ellentétben nem zavarja az írás-olvasási adatokkal dolgozó párhuzamos folyamatokat.

Ugyanakkor azonnal eltávolítja a legtöbbet abból, amit el szeretnénk távolítani. A táblázatban később feldolgozott lekérdezéseket továbbra is mi fogjuk feldolgozni. „forró gyorsítótár” által, ami csökkenti azok időtartamát – és ezáltal a szolgáltatási tranzakciónk által mások blokkolásának teljes idejét is.

#2: Van itthon valaki?

Nézzük meg, hogy van-e valami a táblázatban:

TABLE tbl LIMIT 1;

Ha egyetlen rekord sem maradt, akkor sokat spórolhatunk a feldolgozáson, ha egyszerűen lefuttatjuk a CSONKA:

Ugyanúgy működik, mint egy feltétel nélküli DELETE parancs minden táblára, de sokkal gyorsabb, mivel valójában nem vizsgálja át a táblákat. Továbbá azonnal felszabadít lemezterületet, így nincs szükség utána VACUUM művelet végrehajtására.

A tábla szekvencia számlálójának visszaállításáról (RESTART IDENTITY) a te döntésed.

#3: Mindenki, egyszerre csak egyet!

Mivel egy nagyon versenyképes környezetben dolgozunk, miközben egy táblázatban hiányzó bejegyzéseket keresünk, valaki már írhatott oda valamit. Nem szabad elveszítenünk ezt az információt, akkor mi van? Rendben, biztosítanunk kell, hogy senki ne írhasson oda semmit.

Ehhez engedélyeznünk kell SOROZHATÓ- tranzakciónk elkülönítése (igen, itt kezdjük a tranzakciót) és a tábla „halott” állapotba zárása:

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

Pontosan ezt a blokkolási szintet határozzák meg azok a műveletek, amelyeket rajta végre akarunk hajtani.

#4: Összeférhetetlenség

Idejövünk, és le akarunk „zárni” egy táblát – de mi van, ha valaki éppen aktív volt rajta, például olvasott róla? Akkor ott ragadunk, és várjuk, hogy a zár feloldódjon, míg mások, akik olvasni akarnak, velünk ragadnak...

Ennek megakadályozása érdekében „feláldozzuk magunkat” – ha egy bizonyos (elfogadhatóan rövid) időn belül még mindig nem sikerül zárolást szereznünk, akkor kivételt kapunk az adatbázistól, de legalább nem avatkozunk túlságosan mások dolgába.

Ehhez beállítjuk a munkamenet változót lock_timeout (9.3+ verziókhoz) és/vagy nyilatkozat_időtúllépésA legfontosabb dolog, amire emlékezni kell, hogy a statement_timeout érték csak a következő utasítástól kezdve érvényesül. Tehát egy ilyen összefűzésben: nem fog működni:

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

Annak elkerülése érdekében, hogy később vissza kelljen állítani a változó „régi” értékét, a következő űrlapot használjuk: HELYI BEÁLLÍTÁS, ami a beállítás hatókörét az aktuális tranzakcióra korlátozza.

Ne feledd, hogy a statement_timeout minden további kérésre vonatkozik, így a tranzakció nem nyúlhat elfogadhatatlan hosszúságúra, ha sok adat van a táblázatban.

#5: Másolja az adatokat

Ha a tábla nem teljesen üres, az adatokat újra kell menteni egy ideiglenes kiegészítő táblán keresztül:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Aláírás BEKÖTELEZETT CSÖKKENTÉS azt jelenti, hogy a tranzakció végén az ideiglenes tábla megszűnik létezni, és nincs szükség manuális törlésre a kapcsolat kontextusában.

Mivel feltételezzük, hogy nincs sok "élő" adat, ennek a műveletnek meglehetősen gyorsnak kell lennie.

Nos, nagyjából ennyi! Ne felejtsd el a tranzakció befejezése után futtatni az ANALÍZIST a táblastatisztikák normalizálásához, ha szükséges.

A végleges forgatókönyv összeállítása

Ezt a "pszeudo-python"-t használjuk:

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

Lehetséges, hogy ne másoljuk másodszor az adatokat?Elvileg lehetséges, ha a tábla oid-ja nem kapcsolódik semmilyen más tevékenységhez a BL oldalon vagy az FK-hoz a DB oldalon:

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 a forrástáblán, és ellenőrizzük a metrikák értékét:

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 működött! A táblázat 50-szeresére csökkent, és az összes FRISSÍTÉS ismét gyorsan fut.

Forrás: will.com

Ha a VÁKUUM nem működik, kézzel tisztítjuk meg az asztalt