ProHoster > Blog > Uprava > Ko VAKUUM odpove, mizo očistimo ročno

Ko VAKUUM odpove, mizo očistimo ročno

VAKUUM lahko iz tabele v PostgreSQL »počisti« samo tisto nihče ne vidi - to pomeni, da ni niti ene aktivne zahteve, ki bi se začela, preden so bili ti zapisi spremenjeni.

Kaj pa, če takšna neprijetna vrsta (dolgotrajna obremenitev OLAP baze podatkov OLTP) še vedno obstaja? kako čista aktivno previjalna miza obdan z dolgimi vprašanji in ne stopiš na grablje?

Ko VAKUUM odpove, mizo očistimo ročno

Polaganje grablje

Najprej ugotovimo, kaj je problem, ki ga želimo rešiti, in kako lahko nastane.

Ponavadi se zgodi ta situacija na razmeroma majhni mizi, v kateri pa se pojavi veliko sprememb. Običajno to ali drugače metrov/agregatov/ocen, na katerem se pogosto izvaja UPDATE, oz medpomnilnik-čakalna vrsta za obdelavo nekega nenehno tekočega toka dogodkov, katerih zapisi se nenehno VSTAVLJAJO/BRIŠI.

Poskusimo reproducirati možnost z ocenami:

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;

In vzporedno se v drugi povezavi začne dolga, dolga zahteva, zbiranje neke kompleksne statistike, ampak ne vpliva na našo mizo:

SELECT pg_sleep(10000);

Zdaj posodabljamo vrednost enega od števcev veliko, velikokrat. Zaradi čistosti poskusa naredimo to v ločenih transakcijah z uporabo dblinkkako bo v resnici:

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

Kaj se je zgodilo? Zakaj tudi za najpreprostejšo POSODOBITEV posameznega zapisa čas izvedbe se je zmanjšal za 7-krat — od 0.524ms do 3.808ms? In naš rating raste vse počasneje.

Vsega je kriv MVCC.

Vse je o mehanizem MVCC, kar povzroči, da poizvedba pregleda vse prejšnje različice vnosa. Torej očistimo našo tabelo iz "mrtvih" različic:

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

Oh, nič ni za očistiti! Vzporedno Zagonska zahteva nas moti - navsezadnje se bo morda nekoč želel obrniti na te različice (kaj če?) in morale bi mu biti na voljo. In zato nam tudi VACUUM FULL ne bo pomagal.

"Sesedanje" tabele

Zagotovo pa vemo, da ta poizvedba ne potrebuje naše tabele. Zato bomo vseeno skušali sistemsko delovanje vrniti v ustrezne meje tako, da bomo z mize odstranili vse nepotrebno – vsaj “ročno”, saj VACUUM popusti.

Da bo bolj jasno, si poglejmo primer vmesne tabele. To pomeni, da obstaja velik pretok INSERT/DELETE in včasih je tabela popolnoma prazna. Če pa ni prazen, moramo shranite trenutno vsebino.

#0: Ocenjevanje situacije

Jasno je, da lahko poskusite nekaj narediti s tabelo tudi po vsaki operaciji, vendar to nima velikega smisla - režijski stroški vzdrževanja bodo očitno večji od prepustnosti ciljnih poizvedb.

Oblikujmo merilo - "čas je za ukrepanje", če:

  • VACUUM je bil predstavljen že dolgo nazaj
    Pričakujemo veliko obremenitev, pa naj bo 60 sekund od zadnjega [avto]VAKUUMA.
  • fizična velikost tabele je večja od ciljne
    Definirajmo ga kot dvakratno število strani (8 KB blokov) glede na najmanjšo velikost - 1 blk za kopico + 1 blk za vsak indeks - za potencialno prazno mizo. Če pričakujemo, da bo določena količina podatkov vedno "normalno" ostala v medpomnilniku, je smiselno prilagoditi to formulo.

Zahteva za preverjanje

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: Še vedno VAKUUM

Ne moremo vnaprej vedeti, ali nas vzporedna poizvedba bistveno moti – točno koliko zapisov je postalo »zastarelih« od začetka. Zato, ko se odločimo, da bomo tabelo nekako obdelali, moramo v vsakem primeru najprej izvesti na njej VAKUUM - za razliko od VACUUM FULL ne moti vzporednih procesov, ki delajo s podatki za branje in pisanje.

Hkrati lahko takoj očisti večino tistega, kar bi radi odstranili. Da, in nadaljnje poizvedbe v tej tabeli bodo poslane nam z "hot cache", kar bo skrajšalo njihovo trajanje – in s tem skupni čas blokiranja drugih z našo servisno transakcijo.

#2: Je kdo doma?

Preverimo, če je sploh kaj v tabeli:

TABLE tbl LIMIT 1;

Če ne ostane niti en sam zapis, potem lahko veliko prihranimo pri obdelavi, če preprosto naredimo RAZREŽI:

Deluje enako kot brezpogojni ukaz DELETE za vsako tabelo, vendar je veliko hitrejši, saj dejansko ne pregleduje tabel. Poleg tega takoj sprosti prostor na disku, tako da naknadno ni treba izvesti operacije SESANJA.

Ali morate ponastaviti števec zaporedja tabel (PONOVNI ZAGON IDENTITETE), se odločite sami.

#3: Vsi - po vrsti!

Ker delamo v zelo konkurenčnem okolju, je lahko med preverjanjem odsotnosti vnosov v tabeli že kdo kaj napisal. Teh informacij ne smemo izgubiti, kaj pa? Tako je, poskrbeti moramo, da tega ne more nihče zagotovo zapisati.

Za to moramo omogočiti SERIJSKO PRIZN-izolacija za našo transakcijo (ja, tukaj začnemo transakcijo) in "tesno" zaklene tabelo:

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

Ta stopnja blokiranja je določena z operacijami, ki jih želimo izvesti na njem.

#4: Nasprotje interesov

Pridemo sem in hočemo znak »zakleniti« - kaj če bi bil nekdo tisti trenutek aktiven na njem, na primer bral z njega? Bomo "viseli" čakali na sprostitev tega bloka, drugi, ki bodo želeli brati, pa bodo naleteli na nas ...

Da do tega ne bi prišlo, se bomo »žrtvovali« - če nam v določenem (sprejemljivo kratkem) času ni uspelo pridobiti zaklepanja, bomo prejeli izjemo iz baze, a vsaj ne bomo preveč posegali v drugi.

Če želite to narediti, nastavite spremenljivko seje lock_timeout (za različice 9.3+) ali/in stavek_timeout. Glavna stvar, ki si jo morate zapomniti, je, da se vrednost statement_timeout uporablja samo od naslednjega stavka. Se pravi tako pri lepljenju - ne bo delovalo: 

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

Da se pozneje ne bi ukvarjali z obnavljanjem "stare" vrednosti spremenljivke, uporabimo obrazec NASTAVITE LOKALNO, ki omeji obseg nastavitve na trenutno transakcijo.

Ne pozabimo, da statement_timeout velja za vse nadaljnje zahteve, tako da se transakcija ne more raztegniti do nesprejemljivih vrednosti, če je v tabeli veliko podatkov.

#5: Kopirajte podatke

Če tabela ni popolnoma prazna, bo treba podatke ponovno shraniti s pomočjo pomožne začasne tabele:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Podpis ON COMMIT DROP pomeni, da v trenutku, ko se transakcija konča, začasna tabela preneha obstajati in je ni treba ročno izbrisati v kontekstu povezave.

Ker predvidevamo, da ni veliko "živih" podatkov, bi morala ta operacija potekati precej hitro.

No, to je vse! Ne pozabite po zaključku transakcije zaženi ANALIZA po potrebi normalizirati statistiko tabele.

Sestavljanje končnega scenarija

Uporabljamo ta "psevdo-python":

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

Ali je mogoče podatkov ne kopirati drugič?Načeloma je to možno, če sam oid tabele ni vezan na druge dejavnosti s strani BL ali FK s strani 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;

Zaženimo skript na izvorni tabeli in preverimo meritve:

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

Vse se je izšlo! Tabela se je zmanjšala za 50-krat in vse POSODOBITVE spet tečejo hitro.

Vir: www.habr.com

Dodaj komentar