ProHoster > Блог > Administracija > Kada VACUUM ne uspije, stol čistimo ručno

Kada VACUUM ne uspije, stol čistimo ručno

VACUUM može "počistiti" iz tabele u PostgreSQL samo ono što niko ne može da vidi - odnosno nema niti jednog aktivnog zahtjeva koji je pokrenut prije promjene ovih zapisa.

Ali što ako takav neugodan tip (dugotrajno OLAP opterećenje na OLTP bazi podataka) i dalje postoji? Kako čisti stol za aktivno presvlačenje okružen dugim upitima i ne stane na grabulje?

Kada VACUUM ne uspije, stol čistimo ručno

Polaganje grabulja

Prvo, odredimo koji je problem koji želimo riješiti i kako može nastati.

Obično se ova situacija dešava na relativno malom stolu, ali u kojoj se javlja puno promjena. Obično ovo ili drugačije metara/agregata/ocena, na kojem se UPDATE često izvršava, ili buffer-queue za obradu nekog stalno tekućeg toka događaja, čiji se zapisi stalno INSERT/DELETE.

Pokušajmo reproducirati opciju s ocjenama:

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;

I paralelno, u drugoj vezi, kreće dug, dug zahtjev, prikupljanje neke složene statistike, ali ne utiče na naš sto:

SELECT pg_sleep(10000);

Sada ažuriramo vrijednost jednog od brojača mnogo, mnogo puta. Radi čistoće eksperimenta, uradimo ovo u odvojenim transakcijama koristeći dblinkkako ce se to desiti u stvarnosti:

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

Šta se desilo? Zašto čak i za najjednostavnije AŽURIRANJE jednog zapisa vrijeme izvršenja smanjeno za 7 puta — od 0.524 ms do 3.808 ms? I naš rejting se sve sporije gradi.

Za sve je kriv MVCC.

To je sve MVCC mehanizam, što uzrokuje da upit pregleda sve prethodne verzije unosa. Dakle, očistimo našu tablicu od "mrtvih" verzija:

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, nema šta da se čisti! Paralelno Pokrenuti zahtjev nas ometa - uostalom, možda će jednog dana poželjeti da se okrene ovim verzijama (šta ako?), a one bi mu trebale biti dostupne. I stoga nam ni VAKUUM FULL neće pomoći.

„Kolapsiranje“ stola

Ali sigurno znamo da tom upitu nije potrebna naša tabela. Stoga ćemo i dalje pokušati vratiti performanse sistema na adekvatne granice tako što ćemo eliminirati sve nepotrebno sa tabele – barem „ručno“, pošto VACUUM odustaje.

Da bi bilo jasnije, pogledajmo primjer slučaja tabele bafera. To jest, postoji veliki tok INSERT/DELETE, a ponekad je tabela potpuno prazna. Ali ako nije prazan, moramo sačuvati njegov trenutni sadržaj.

#0: Procjena situacije

Jasno je da možete pokušati učiniti nešto s tablicom čak i nakon svake operacije, ali to nema puno smisla - troškovi održavanja će očito biti veći od propusnosti ciljnih upita.

Formulirajmo kriterije - "vrijeme je za djelovanje" ako:

  • VACUUM je lansiran dosta davno
    Očekujemo veliki teret, neka bude 60 sekundi od posljednjeg [auto]VACUUM-a.
  • fizička veličina tabele je veća od ciljne
    Definirajmo to kao dvostruko veći broj stranica (blokova od 8KB) u odnosu na minimalnu veličinu - 1 blk za hrpu + 1 blk za svaki indeks - za potencijalno prazan sto. Ako očekujemo da će određena količina podataka uvijek ostati u baferu „normalno“, razumno je podesiti ovu formulu.

Zahtjev za verifikaciju

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: I dalje VAKUUM

Ne možemo unaprijed znati da li nas paralelni upit značajno ometa - koliko je tačno zapisa postalo "zastarjelo" otkako je počelo. Stoga, kada odlučimo na neki način obraditi tabelu, u svakom slučaju, prvo treba izvršiti na njoj VACUUM - za razliku od VACUUM FULL, ne ometa paralelne procese koji rade sa podacima za čitanje i pisanje.

Istovremeno, može odmah očistiti većinu onoga što bismo željeli ukloniti. Da, i naknadni upiti u ovoj tabeli će ići nama po "hot cache", što će smanjiti njihovo trajanje - a samim tim i ukupno vrijeme blokiranja drugih putem naše servisne transakcije.

#2: Ima li koga kod kuće?

Provjerimo ima li uopće nečega u tabeli:

TABLE tbl LIMIT 1;

Ako ne ostane ni jedan jedini zapis, jednostavno možemo uštedjeti na obradi TRUNCATE:

Djeluje isto kao bezuvjetna naredba DELETE za svaku tablicu, ali je mnogo brža jer zapravo ne skenira tabele. Štaviše, odmah oslobađa prostor na disku, tako da nema potrebe za obavljanjem operacije VACUUM nakon toga.

Na vama je da odlučite da li trebate resetirati brojač sekvenci tablice (RESTART IDENTITY).

#3: Svi - smenjujte se!

Pošto radimo u visokokonkurentnom okruženju, dok mi ovdje provjeravamo da nema unosa u tabeli, neko je mogao već tamo nešto napisati. Ne bismo trebali izgubiti ove informacije, pa šta? Tako je, moramo se pobrinuti da to niko ne može sigurno zapisati.

Da bismo to uradili moramo da omogućimo SERIALIZABLE-izolacija za našu transakciju (da, ovdje počinjemo transakciju) i zaključajte tablicu "čvrsto":

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

Ovaj nivo blokiranja određen je operacijama koje želimo izvršiti na njemu.

#4: Sukob interesa

Dolazimo ovdje i želimo da "zaključamo" znak - šta ako je neko bio aktivan na njemu u tom trenutku, na primjer, čitajući s njega? Mi ćemo "visiti" čekajući da ovaj blok bude pušten, a drugi koji žele čitati će naletjeti na nas...

Da se to ne dogodi, "žrtvovaćemo se" - ako nismo bili u mogućnosti da dobijemo bravu u određenom (prihvatljivo kratkom) vremenu, tada ćemo dobiti izuzetak iz baze, ali se barem nećemo previše mešati u drugi.

Da biste to učinili, postavite varijablu sesije lock_timeout (za verzije 9.3+) ili/i statement_timeout. Glavna stvar koju treba zapamtiti je da se vrijednost statement_timeout primjenjuje samo od sljedećeg izraza. Odnosno, ovako u lepljenju - neće raditi: 

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

Kako se kasnije ne bismo morali baviti vraćanjem “stare” vrijednosti varijable, koristimo obrazac SET LOCAL, što ograničava opseg postavke na trenutnu transakciju.

Sjećamo se da se statement_timeout primjenjuje na sve naredne zahtjeve tako da se transakcija ne može proširiti na neprihvatljive vrijednosti ako ima puno podataka u tablici.

#5: Kopiraj podatke

Ako tabela nije potpuno prazna, podaci će se morati ponovo pohraniti pomoću pomoćne privremene tablice:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Potpis ON COMMIT DROP znači da će u trenutku završetka transakcije privremena tabela prestati da postoji i nema potrebe da je ručno brišete u kontekstu veze.

Budući da pretpostavljamo da nema puno „živih“ podataka, ova operacija bi se trebala odvijati prilično brzo.

Pa, to je sve! Ne zaboravite nakon završetka transakcije pokrenite ANALIZU za normalizaciju statistike tabele ako je potrebno.

Sastavljanje konačnog scenarija

Koristimo ovaj "pseudo-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;

Da li je moguće ne kopirati podatke drugi put?U principu, moguće je ako sam oid tabele nije vezan ni za jednu drugu aktivnost sa strane BL ili FK sa strane 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;

Pokrenimo skriptu na izvornoj tabeli i provjerimo metriku:

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

Sve je uspjelo! Tabela se smanjila za 50 puta i sva AŽURIRANJA ponovo brzo rade.

izvor: www.habr.com

Dodajte komentar