ProHoster > Blog > Adminisztráció > Ha a VÁKUUM nem működik, kézzel tisztítjuk meg az asztalt

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

VÁKUUM csak mit tud „takarítani” egy táblából a PostgreSQL-ben senki sem láthatja - vagyis nincs egyetlen aktív kérés sem, amely a rekordok módosítása előtt indult volna.

De mi van akkor, ha egy ilyen kellemetlen típus (hosszú távú OLAP terhelés egy OLTP adatbázison) még mindig létezik? Hogyan aktív pelenkázóasztal tisztítása hosszú lekérdezések veszik körül, és nem lépnek gereblyére?

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

A gereblye lerakása

Először is határozzuk meg, mi a megoldani kívánt probléma, és hogyan merülhet fel.

Általában ez a helyzet fordul elő egy viszonylag kis asztalon, hanem amelyben előfordul sok változás. Általában ez vagy más méter/aggregátum/besorolás, amelyen az UPDATE gyakran lefut, vagy puffer-sor valamilyen folyamatosan zajló eseményfolyam feldolgozására, amelyek rekordjai folyamatosan INSERT/DELETE.

Próbáljuk meg reprodukálni a lehetőséget értékelésekkel:

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 összefüggésben elindul egy hosszú-hosszú kérés, némi összetett statisztika gyűjtése, de nem érinti az asztalunkat:

SELECT pg_sleep(10000);

Most az egyik számláló értékét sokszor-sokszor frissítjük. A kísérlet tisztasága érdekében tegyük ezt külön tranzakciókban dblink segítségévelhogyan fog 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 akár egyetlen rekord legegyszerűbb FRISSÍTÉSÉRE is a végrehajtási idő 7-szeresére csökkent - 0.524 ms-ról 3.808 ms-ra? A minősítésünk pedig egyre lassabban épül fel.

Az egész az MVCC hibája.

Minden arról szól MVCC mechanizmus, aminek hatására a lekérdezés a bejegyzés összes korábbi verzióját átnézi. Tehát tisztítsuk meg asztalunkat 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! Párhuzamos A futó kérés zavar bennünket - elvégre lehet, hogy egyszer rá akar majd fordulni ezekre a verziókra (mi lenne, ha?), és ezek elérhetőek legyenek számára. És ezért még a VÁKUUM TELE sem segít rajtunk.

Az asztal „összecsukása”.

De biztosan tudjuk, hogy ennek a lekérdezésnek nincs szüksége a táblázatunkra. Ezért továbbra is igyekszünk a rendszer teljesítményét megfelelő korlátokra visszaállítani úgy, hogy minden feleslegeset eltávolítunk a táblázatból - legalábbis „manuálisan”, mivel a VACUUM feladja.

Hogy érthetőbb legyen, nézzük meg a puffertábla esetét. Vagyis nagy az INSERT/DELETE áramlása, és néha teljesen üres a tábla. De ha nem üres, akkor muszáj mentse el az aktuális tartalmát.

#0: A helyzet felmérése

Nyilvánvaló, hogy minden művelet után is meg lehet próbálni valamit csinálni a táblával, de ennek nincs sok értelme - a karbantartási többlet egyértelműen nagyobb lesz, mint a céllekérdezések átviteli sebessége.

Fogalmazzuk meg a kritériumokat – „ideje cselekedni”, ha:

  • A VACUUM-ot elég régen indították el
    Nagy terhelésre számítunk, úgyhogy legyen 60 másodperc az utolsó [auto]VÁKUUM óta.
  • A fizikai táblázat mérete nagyobb, mint a cél
    Határozzuk meg, hogy a minimális mérethez képest kétszer annyi oldal (8 KB blokk) 1 blk a kupacért + 1 blk minden indexért - potenciálisan üres asztalhoz. Ha arra számítunk, hogy bizonyos mennyiségű adat mindig „normálisan” marad a pufferben, akkor indokolt ezt a képletet módosítani.

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: Még mindig VÁKUUM

Nem tudhatjuk előre, hogy egy párhuzamos lekérdezés jelentősen zavar-e bennünket – pontosan hány rekord vált „elavulttá” a kezdete óta. Ezért, ha úgy döntünk, hogy valahogy feldolgozzuk a táblát, mindenesetre először végre kell hajtanunk rajta VÁKUUM - ellentétben a VACUUM FULL-lal, nem zavarja az olvasási-írási adatokkal dolgozó párhuzamos folyamatokat.

Ugyanakkor azonnal eltávolíthatja a legtöbbet, amit el szeretnénk távolítani. Igen, és a táblázatra vonatkozó későbbi lekérdezések hozzánk fognak eljutni a "hot cache" által, ami lerövidíti azok időtartamát – és ezáltal a szolgáltatási tranzakciónk által mások letiltásának teljes idejét.

#2: Van itthon valaki?

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

TABLE tbl LIMIT 1;

Ha már egyetlen rekord sem maradt, akkor egyszerűen futással sokat spórolhatunk a feldolgozáson CSONKA:

Ugyanúgy működik, mint egy feltétel nélküli DELETE parancs minden táblához, de sokkal gyorsabb, mivel valójában nem vizsgálja meg a táblákat. Ráadásul azonnal lemezterületet szabadít fel, így nem kell utána VÁKUUM műveletet végrehajtani.

Ön dönti el, hogy vissza kell-e állítania a táblázat sorozatszámlálóját (ÚJRAINDÍTÁSI IDENTITÁS).

#3: Mindenki – felváltva!

Mivel erős versenykörnyezetben dolgozunk, miközben itt ellenőrizzük, hogy nincsenek-e bejegyzések a táblázatban, valaki már írhatott valamit. Nem szabad elveszítenünk ezeket az információkat, akkor mi van? Így van, ügyelnünk kell arra, hogy senki ne tudja biztosan leírni.

Ehhez engedélyeznünk kell SOROZHATÓ-izoláció a tranzakciónkhoz (igen, itt indítunk egy tranzakciót), és zárjuk le a táblát „szorosan”:

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

Ezt a blokkolási szintet a rajta végrehajtani kívánt műveletek határozzák meg.

#4: Összeférhetetlenség

Idejövünk és le akarjuk zárni a táblát – mi lenne, ha valaki abban a pillanatban aktív lenne, például olvasott volna róla? „Akasztva” várjuk ennek a blokknak a megjelenését, és a többi olvasni vágyó is befut hozzánk...

Hogy ez ne történhessen meg, „feláldozzuk magunkat” – ha egy bizonyos (elfogadhatóan rövid) időn belül nem tudtunk zárat szerezni, akkor kivételt kapunk a bázistól, de legalább nem fogunk túlságosan beavatkozni. mások.

Ehhez állítsa be a session változót lock_timeout (9.3+ verziókhoz) vagy/és nyilatkozat_időtúllépés. A legfontosabb dolog, amit meg kell jegyezni, hogy az utasítás_időtúllépése érték csak a következő utasítástól érvényes. Vagyis így a ragasztásnál... nem fog működni: 

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

Annak érdekében, hogy a későbbiekben ne kelljen a változó „régi” értékének visszaállításával foglalkoznunk, az űrlapot használjuk HELYI BEÁLLÍTÁS, amely a beállítás hatókörét az aktuális tranzakcióra korlátozza.

Emlékezzünk arra, hogy az utasítás_időtúllépése minden további kérésre vonatkozik, így a tranzakció nem nyúlhat elfogadhatatlan értékre, ha sok adat van a táblázatban.

#5: Adatok másolása

Ha a tábla nem teljesen üres, az adatokat egy ideiglenes segédtábla segítségével újra el kell menteni:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Aláírás ON COMMIT Drop azt jelenti, hogy abban a pillanatban, amikor a tranzakció véget ér, az ideiglenes tábla megszűnik, és nincs szükség manuális törlésére a kapcsolati környezetben.

Mivel feltételezzük, hogy nincs sok „élő” adat, ennek a műveletnek elég gyorsan meg kell történnie.

Nos, ez minden! Ne felejtse el a tranzakció befejezése után futtassa az ELEMZÉST a táblázat statisztikai adatainak normalizálására, ha szükséges.

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

Ezt a „pszeudo-pythont” 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 másodszor ne másolja át az adatokat?Elvileg lehetséges, ha maga a tábla oidja nincs más tevékenységhez kötve BL oldalról vagy FK DB oldalró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 a forrástáblán, és ellenőrizzük a mutató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áblázat 50-szeresére zsugorodott, és az UPDATE ismét gyorsan fut.

Forrás: will.com

Hozzászólás