ProHoster > Blog > uprava > Kada VAKUUM zakaže, stol čistimo ručno

Kada VAKUUM zakaže, stol čistimo ručno

VAKUUM može “počistiti” iz tablice u PostgreSQL-u samo ono nitko ne može vidjeti - odnosno ne postoji niti jedan aktivan zahtjev koji je pokrenut prije promjene tih zapisa.

Ali što ako takva neugodna vrsta (dugotrajno OLAP opterećenje OLTP baze podataka) još uvijek postoji? Kako čist aktivno prematanje stol okruženi dugim upitima i ne stati na grablje?

Kada VAKUUM zakaže, stol čistimo ručno

Polaganje grablja

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

Obično se ova situacija događa na relativno malom stolu, ali u kojem se javlja puno promjena. Obično ovo ili drugačije metara/agregata/ocjena, na kojem se često izvršava UPDATE, ili međuspremnik-red 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 dugi, dugi zahtjev, prikupljanje neke složene statistike, ali ne utječe na naš stol:

SELECT pg_sleep(10000);

Sada ažuriramo vrijednost jednog od brojača mnogo, mnogo puta. Radi čistoće eksperimenta, učinimo ovo u zasebnim transakcijama koristeći dblinkkako će se to dogoditi 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

Što se dogodilo? Zašto čak i za najjednostavnije AŽURIRANJE jednog zapisa vrijeme izvršenja degradirano za 7 puta - od 0.524ms do 3.808ms? A naš rejting raste sve sporije.

Za sve je kriv MVCC.

Sve je o 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 se što čistiti! Paralelno Izvršeni zahtjev nas ometa - uostalom, možda će jednog dana poželjeti okrenuti se tim verzijama (što ako?), a trebale bi mu biti dostupne. I stoga nam ni VACUUM FULL neće pomoći.

“Urušavanje” stola

Ali pouzdano znamo da taj upit ne treba našu tablicu. Stoga ćemo ipak pokušati vratiti performanse sustava u adekvatne okvire eliminirajući sve nepotrebno sa stola - barem "ručno", jer VACUUM popušta.

Da bi bilo jasnije, pogledajmo primjer slučaja međuspremnika. Odnosno, postoji veliki protok INSERT/DELETE, a ponekad je tablica potpuno prazna. Ali ako nije prazno, moramo spremite 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 previše smisla - troškovi održavanja očito će biti veći od propusnosti ciljanih upita.

Formulirajmo kriterij - "vrijeme je za djelovanje" ako:

  • VACUUM je lansiran dosta davno
    Očekujemo veliko opterećenje, pa neka bude 60 sekundi od zadnjeg [auto]VAKUUMA.
  • fizička veličina tablice je veća od ciljne
    Definirajmo to kao dvostruki broj stranica (8KB blokova) u odnosu na minimalnu veličinu - 1 blk za gomilu + 1 blk za svaki indeks - za potencijalno prazan stol. Ako očekujemo da će određena količina podataka uvijek ostati u međuspremniku "normalno", razumno je prilagoditi ovu formulu.

Zahtjev za provjeru

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: Još uvijek USISAVAJ

Ne možemo unaprijed znati ometa li nas paralelni upit - koliko je točno zapisa postalo "zastarjelo" otkako je počelo. Stoga, kada odlučimo nekako obraditi tablicu, u svakom slučaju, prvo bismo trebali izvršiti na njoj VAKUUM - za razliku od VACUUM FULL, ne ometa paralelne procese koji rade s podacima za čitanje i pisanje.

Istodobno, može odmah očistiti većinu onoga što bismo htjeli ukloniti. Da, i sljedeći upiti o ovoj tablici ići će nama prema "hot cache", što će smanjiti njihovo trajanje - a time 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 tablici:

TABLE tbl LIMIT 1;

Ako ne preostane niti jedan jedini zapis, tada možemo puno uštedjeti na obradi jednostavnim izvođenjem SKRATITI:

Djeluje isto kao i bezuvjetna naredba DELETE za svaku tablicu, ali je mnogo brža jer zapravo ne skenira tablice. Štoviše, odmah oslobađa prostor na disku, tako da nema potrebe za izvođenjem operacije VAKUUMIRANJA nakon toga.

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

#3: Svi - redom!

Budući da radimo u jako kompetitivnom okruženju, dok mi ovdje provjeravamo da nema unosa u tablici, netko je već mogao tamo nešto napisati. Ne bismo trebali izgubiti ove informacije, pa što? Tako je, moramo se pobrinuti da to nitko ne može sigurno zapisati.

Da bismo to učinili, moramo omogućiti SERIJALIZIVO-izolacija za našu transakciju (da, ovdje započinjemo transakciju) i "čvrsto" zaključati tablicu:

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

Ova razina blokiranja određena je operacijama koje želimo izvesti na njemu.

#4: Sukob interesa

Dođemo ovdje i želimo “zaključati” znak – što ako je netko u tom trenutku bio aktivan na njemu, na primjer, čitao s njega? Mi ćemo “visjeti” čekajući da ovaj blok bude pušten, a drugi koji žele čitati će nam naletjeti...

Da se to ne dogodi, "žrtvovat ćemo se" - ako nismo uspjeli dobiti zaključavanje u određenom (prihvatljivo kratkom) vremenu, tada ćemo dobiti iznimku iz baze, ali se barem nećemo previše miješati u drugi.

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

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

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

Sjećamo se da se statement_timeout primjenjuje na sve sljedeće zahtjeve tako da se transakcija ne može rastegnuti do neprihvatljivih vrijednosti ako u tablici ima puno podataka.

#5: Kopirajte podatke

Ako tablica nije potpuno prazna, podaci će se morati ponovno spremiti 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 tablica prestati postojati i nema potrebe da je ručno brišete u kontekstu veze.

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

Pa, to je sve! Ne zaboravite nakon dovršetka transakcije pokreni ANALIZA za normalizaciju tablične statistike 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;

Je li moguće ne kopirati podatke drugi put?U principu, moguće je ako oid same tablice 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 tablici 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! Tablica se smanjila 50 puta i svi UPDATE-i opet rade brzo.

Izvor: www.habr.com

Dodajte komentar