🥇Kada USISAVANJE ne uspije, stol čistimo ručno

VAKUUM može "očistiti" samo iz tablice u PostgreSQL-u koja nitko ne može vidjeti - to jest, ne postoji niti jedan aktivan zahtjev koji je započeo prije nego što su ovi zapisi promijenjeni.

Ali što ako takav neugodan tip (dugoročno OLAP opterećenje OLTP baze podataka) postoji? Kako? očistiti stol za aktivno presvlačenje okružen dugim upitima i ne stati na grablje?

Rasporedimo grablje

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

Ova situacija se obično događa na relativno malom stolu, ali u kojem se to događa puno promjenaObično su to ili različiti brojači/agregati/ocjene, na kojima se ažuriranje (UPRAIRANJE) izvodi vrlo često, ili red međuspremnika za obradu nekog stalno tekućeg toka događaja, čiji se zapisi stalno UMETAJU/BRIŠU.

Pokušajmo reproducirati verziju 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, pokreće se vrlo dugačak upit, prikupljajući 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činit ćemo ovo u odvojenim transakcijama koristeći dblink, kako ć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 se smanjilo za faktor 7 — s 0.524 ms na 3.808 ms? A naša ocjena raste sve sporije i sporije.

MVCC je kriv za sve

Sve se vrti oko MVCC mehanizam, što prisiljava upit da pregleda sve prethodne verzije zapisa. Dakle, očistimo našu tablicu "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 tekući upit ometa naš rad — uostalom, možda će jednog dana htjeti pristupiti tim verzijama (što ako?), a one moraju biti dostupne. I zato nam čak ni VACUUM FULL neće pomoći.

Lupamo po stolu

Ali sigurno znamo da tom upitu nije potrebna naša tablica. Stoga pokušajmo vratiti performanse sustava u razumne granice uklanjanjem svih nepotrebnih podataka iz tablice - barem ručno, budući da VACUUM ne radi.

Da bismo ovo pojasnili, pogledajmo primjer međuspremničke tablice. To jest, postoji veliki protok INSERT/DELETE naredbi, a ponekad je tablica potpuno prazna. Ali ako nije prazna, trebali bismo spremi trenutni sadržaj.

#0: Procijenite situaciju

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

Formulirajmo kriterije - "vrijeme je za djelovanje" ako:

VACUUM je pokrenut prije dosta vremena
Očekujemo težak teret, pa neka bude tako 60 sekundi od zadnjeg [automatskog]VAKUMA.
fizička veličina tablice je veća od ciljne
Definirajmo to kao dvostruki broj stranica (blokovi od 8 KB) u odnosu na minimalnu veličinu - 1 blok po hrpi + 1 blok po indeksu — za potencijalno praznu tablicu. Ako očekujemo da međuspremnik uvijek sadrži neku količinu podataka, ima smisla 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: USISAVAČ U SVAKOM SLUČAJU

Ne možemo unaprijed znati koliko nas paralelni upit ometa - ili točno koliko je zapisa "zastarjelo" od njegovog početka. Stoga, kada konačno odlučimo nekako obraditi tablicu, svakako bismo prvo trebali pokrenuti upit nad njom. VAKUUM — za razliku od VACUUM FULL, ne ometa paralelne procese koji rade s podacima za čitanje i pisanje.

Istovremeno, može odmah očistiti većinu onoga što bismo željeli ukloniti. A naknadne upite na ovoj tablici i dalje ćemo obrađivati. pomoću "vruće predmemorije", što će smanjiti njihovo trajanje - i, prema tome, ukupno vrijeme blokiranja drugih od strane naše servisne transakcije.

#2: Ima li koga kod kuće?

Provjerimo ima li uopće išta u tablici:

TABLE tbl LIMIT 1;

Ako ne ostane niti jedan zapis, možemo puno uštedjeti na obradi jednostavnim pokretanjem SKRATITI:

Radi isto kao bezuvjetna naredba DELETE za svaku tablicu, ali je puno brža jer zapravo ne skenira tablice. Nadalje, odmah oslobađa prostor na disku, tako da nakon toga nije potrebno izvoditi operaciju VACUUM.

Trebate li resetirati brojač slijeda tablice (RESTART IDENTITY) ovisi o vama.

#3: Svi, jedan po jedan!

Budući da radimo u vrlo konkurentnom okruženju, dok provjeravamo nedostajuće unose u tablici, netko je već mogao nešto tamo napisati. Ne bismo smjeli izgubiti te podatke, pa što? Dobro, moramo se pobrinuti da nitko tamo ne može ništa napisati.

Da bismo to učinili, moramo omogućiti SERIJALIZABILNO- izolacija za našu transakciju (da, ovdje pokrećemo transakciju) i zaključavanje tablice na "mrtvo":

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

Upravo je ta razina blokiranja određena operacijama koje želimo na njemu izvršiti.

#4: Sukob interesa

Dođemo ovdje i želimo "zaključati" znak - ali što ako je netko u to vrijeme bio aktivan na njemu, poput čitanja s njega? Zaglavit ćemo čekajući da se brava otključa, dok će drugi koji žele čitati ostati zaglavljeni s nama...

Kako bismo to spriječili, „žrtvovat ćemo se“ - ako i dalje ne uspijemo dobiti zaključavanje unutar određenog (prihvatljivo kratkog) vremena, tada ćemo dobiti iznimku od baze podataka, ali barem se nećemo previše miješati u druge.

Da bismo to učinili, postavljamo varijablu sesije vrijeme_zaključavanja (za verzije 9.3+) ili/i vrijeme_isticanja_izjaveGlavna stvar koju treba zapamtiti jest da se vrijednost statement_timeout primjenjuje tek počevši od sljedeće naredbe. Dakle, u spajanju poput ovog: neće uspjeti:

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

Kako bismo izbjegli kasnije vraćanje „stare“ vrijednosti varijable, koristimo oblik POSTAVI LOKALNO, što ograničava opseg postavke na trenutnu transakciju.

Imajte na umu da se statement_timeout primjenjuje na sve sljedeće zahtjeve, tako da se transakcija ne može rastegnuti do neprihvatljivih duljina ako u tablici ima puno podataka.

#5: Kopirajte podatke

Ako tablica nije potpuno prazna, podatke će trebati ponovno spremiti putem pomoćne privremene tablice:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Potpis PRI POTVRĐIVANJU ISPUŠTANJA znači da će na kraju transakcije privremena tablica prestati postojati i nema potrebe za ručnim brisanjem u kontekstu veze.

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

Pa, to je otprilike to! Ne zaboravite nakon što završite transakciju pokrenite ANALIZU za normalizaciju statistike tablice, 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 sam oid tablice nije povezan s bilo kojim drugim aktivnostima na BL strani ili FK na DB strani:

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 metrike:

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 radilo! Tablica se smanjila za faktor 50, a sva ažuriranja ponovno se brzo izvršavaju.

Izvor: www.habr.com

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