ProHoster > blog > amministrazione > Quando VACUUM fallisce, puliamo manualmente il tavolo

Quando VACUUM fallisce, puliamo manualmente il tavolo

VUOTO può “ripulire” da una tabella in PostgreSQL solo cosa nessuno può vedere - ovvero non esiste una singola richiesta attiva avviata prima che questi record venissero modificati.

Ma cosa succederebbe se esistesse ancora un tipo così spiacevole (carico OLAP a lungo termine su un database OLTP)? Come pulire il fasciatoio attivo circondato da lunghe domande e non calpestare un rastrello?

Quando VACUUM fallisce, puliamo manualmente il tavolo

Disporre il rastrello

Innanzitutto, determiniamo qual è il problema che vogliamo risolvere e come può presentarsi.

Di solito si verifica questa situazione su un tavolo relativamente piccolo, ma in cui si verifica molti cambiamenti. Di solito questo o diverso contatori/aggregati/potenze, su cui viene spesso eseguito UPDATE, o coda-buffer per elaborare un flusso di eventi costantemente in corso, i cui record vengono costantemente INSERT/DELETE.

Proviamo a riprodurre l'opzione con le valutazioni:

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;

E parallelamente, in un'altra connessione, inizia una lunga, lunga richiesta, che raccoglie alcune statistiche complesse, ma senza influenzare la nostra tavola:

SELECT pg_sleep(10000);

Ora aggiorniamo il valore di uno dei contatori molte, molte volte. Per la purezza dell'esperimento, facciamolo in transazioni separate utilizzando dblinkcome accadrà nella realtà:

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

Quello che è successo? Perché anche per il più semplice AGGIORNAMENTO di un singolo record tempo di esecuzione ridotto di 7 volte - da 0.524 ms a 3.808 ms? E la nostra valutazione sta crescendo sempre più lentamente.

È tutta colpa dell'MVCC.

È tutta una questione di Meccanismo MVCC, che fa sì che la query esamini tutte le versioni precedenti della voce. Quindi puliamo la nostra tabella dalle versioni “morte”:

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, non c'è niente da pulire! Parallelo La richiesta in esecuzione interferisce con noi - dopo tutto, un giorno potrebbe voler ricorrere a queste versioni (e se?), e dovrebbero essere a sua disposizione. E quindi anche il VUOTO PIENO non ci aiuterà.

“Crollare” il tavolo

Ma sappiamo per certo che quella query non ha bisogno della nostra tabella. Cercheremo quindi comunque di riportare le prestazioni del sistema entro limiti adeguati eliminando tutto ciò che non è necessario dalla tabella - almeno "manualmente", poiché VACUUM cede.

Per renderlo più chiaro, diamo un’occhiata all’esempio del caso di una tabella buffer. Cioè, c'è un grande flusso di INSERT/DELETE e talvolta la tabella è completamente vuota. Ma se non è vuoto, dobbiamo farlo salvarne il contenuto corrente.

#0: Valutare la situazione

È chiaro che puoi provare a fare qualcosa con la tabella anche dopo ogni operazione, ma questo non ha molto senso: il sovraccarico di manutenzione sarà chiaramente maggiore del rendimento delle query di destinazione.

Formuliamo i criteri: "è ora di agire" se:

  • VACUUM è stato lanciato molto tempo fa
    Ci aspettiamo un carico pesante, quindi lascia che sia secondi 60 dall'ultimo [auto]VACUUM.
  • la dimensione della tabella fisica è maggiore della destinazione
    Definiamolo come il doppio del numero di pagine (blocchi da 8 KB) rispetto alla dimensione minima - 1 nero per heap + 1 nero per ogni indice - per una tabella potenzialmente vuota. Se prevediamo che una certa quantità di dati rimarrà sempre nel buffer “normalmente”, è ragionevole modificare questa formula.

Richiesta di verifica

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: Ancora VUOTO

Non possiamo sapere in anticipo se una query parallela interferisce in modo significativo con noi - esattamente quanti record sono diventati "obsoleti" da quando è iniziata. Pertanto, quando decidiamo di elaborare in qualche modo la tabella, in ogni caso dovremmo prima eseguirla VUOTO - a differenza di VACUUM FULL, non interferisce con i processi paralleli che lavorano con dati di lettura-scrittura.

Allo stesso tempo, può ripulire immediatamente la maggior parte di ciò che vorremmo rimuovere. Sì, e le domande successive su questa tabella verranno inviate a noi per "cache attiva", che ne ridurrà la durata e, quindi, il tempo totale di blocco degli altri tramite la nostra transazione di servizio.

#2: C'è qualcuno in casa?

Controlliamo se c'è qualcosa nella tabella:

TABLE tbl LIMIT 1;

Se non è rimasto un solo record, possiamo risparmiare molto sull'elaborazione semplicemente facendo TRONCARE:

Funziona allo stesso modo di un comando DELETE incondizionato per ciascuna tabella, ma è molto più veloce poiché in realtà non esegue la scansione delle tabelle. Inoltre, libera immediatamente spazio su disco, quindi non è necessario eseguire successivamente un'operazione VACUUM.

Sta a te decidere se sia necessario reimpostare il contatore della sequenza della tabella (RESTART IDENTITY).

#3: Tutti, a turno!

Dato che lavoriamo in un ambiente altamente competitivo, mentre siamo qui a controllare che non ci siano voci nella tabella, qualcuno potrebbe aver già scritto qualcosa lì. Non dovremmo perdere queste informazioni, e allora? Esatto, dobbiamo assicurarci che nessuno possa scriverlo con certezza.

Per fare questo dobbiamo abilitare SERIALIZZABILE-isolamento per la nostra transazione (sì, qui iniziamo una transazione) e blocchiamo la tabella “strettamente”:

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

Questo livello di blocco è determinato dalle operazioni che vogliamo eseguire su di esso.

# 4: Conflitto di interessi

Veniamo qui e vogliamo "bloccare" il cartello: e se qualcuno fosse attivo su di esso in quel momento, ad esempio, leggendolo? Resteremo “in sospeso” in attesa che questo blocco venga rilasciato, e altri che vogliono leggere si imbatteranno in noi...

Per evitare che ciò accada, "sacrificheremo noi stessi": se non siamo riusciti a ottenere un blocco entro un certo tempo (accettabilmente breve), riceveremo un'eccezione dalla base, ma almeno non interferiremo troppo con altri.

Per fare ciò, imposta la variabile di sessione lock_timeout (per le versioni 9.3+) o/e timeout_istruzione. La cosa principale da ricordare è che il valore istruzione_timeout si applica solo dall'istruzione successiva. Cioè, così nell'incollaggio - non funzionerà: 

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

Per non doverci occupare successivamente di ripristinare il “vecchio” valore della variabile, utilizziamo il form IMPOSTA LOCALE, che limita l'ambito dell'impostazione alla transazione corrente.

Ricordiamo che Statement_timeout si applica a tutte le richieste successive in modo che la transazione non possa allungarsi a valori inaccettabili se nella tabella sono presenti molti dati.

# 5: copia i dati

Se la tabella non è completamente vuota, i dati dovranno essere nuovamente salvati utilizzando una tabella temporanea ausiliaria:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Firma AL RILASCIO DELL'IMPEGNO significa che nel momento in cui la transazione termina, la tabella temporanea cesserà di esistere e non sarà necessario eliminarla manualmente nel contesto della connessione.

Dato che presupponiamo che i dati “in tempo reale” non siano molti, questa operazione dovrebbe avvenire abbastanza rapidamente.

Bene, questo è tutto! Non dimenticare dopo aver completato la transazione eseguire ANALIZZA per normalizzare le statistiche della tabella, se necessario.

Mettere insieme la sceneggiatura finale

Usiamo questo “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;

È possibile non copiare i dati una seconda volta?In linea di principio è possibile se l'oid della tabella stessa non è legato ad altre attività dal lato BL o FK dal lato 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;

Eseguiamo lo script sulla tabella di origine e controlliamo le metriche:

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

Tutto ha funzionato! La tabella si è ridotta di 50 volte e tutti gli AGGIORNAMENTI funzionano di nuovo velocemente.

Fonte: habr.com

Aggiungi un commento