🥇Quando VACUUM è in ritardo - puliamo la tabella manualmente

VACUUM può "ripulire" dalla tabella in PostgreSQL solo ciò che nessuno può vedere — cioè non ci sono richieste attive partite prima che queste registrazioni siano state modificate.

E se c'è quel tipo fastidioso (un carico OLAP prolungato su un database OLTP)? Come ripulire una tabella in cambiamento attivo in un ambiente di richieste lunghe senza pestare i rastrelli?

Discutiamo dei rastrelli

Iniziamo a definire in cosa consiste e come potrebbe sorgere il problema che vogliamo risolvere.

Di solito questa situazione si verifica su una tabella relativamente piccola, ma in cui ci sono molti cambiamenti. Di solito si tratta di diversi contatori/aggregati/classifiche, sui quali viene eseguito frequentemente un UPDATE, oppure una coda per elaborare un flusso costante di eventi, le registrazioni dei quali vengono continuamente INSERT/DELETE.

Proviamo a riprodurre un caso con classifiche:

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- utilizzeremo questo indice per costruire la classifica

INSERT INTO
  tbl
SELECT
  chr(ascii('a'::text) + i) k
, 0 v
FROM
  generate_series(0, 25) i;

E parallelamente, in un'altra connessione, parte una lunga lunga risorsa, raccolto qualche complessa statistica, ma non coinvolgendo la nostra tabella:

SELECT pg_sleep(10000);

Ora aggiorniamo molte molte volte il valore di uno dei contatori. Per mantenere l'onore dell'esperimento lo faremo in transazioni separate usando dblink, come accadrebbe in 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

Cosa è successo? Perché anche per un semplice UPDATE di un'unica registrazione il tempo di esecuzione è degradato di 7 volte — da 0.524 ms a 3.808 ms? E la nostra classifica viene costruita sempre più lentamente.

È colpa del MVCC

Tutto dipende dal meccanismo MVCC, che costringe la query a esaminare tutte le versioni precedenti della registrazione. Quindi, puliamo la nostra tabella dalle versioni "morte":

VACUUM VERBOSE tbl;

INFO: quindi "public.tbl"
INFO: "tbl": trovate 0 versioni di riga rimovibili, 10026 versioni di riga non rimovibili in 45 su 45 pagine
DETTAGLI: 10000 versioni di riga morte non possono ancora essere rimosse, xmin più vecchio: 597439602

Oh, ma non c'è niente da pulire! Parallelamente la query in esecuzione ci ostacola — poiché potrebbe voler accedere a queste versioni (e se mai?) e devono essergli accessibili. E quindi anche VACUUM FULL non ci aiuterà.

«Consolidiamo» la tabella

Ma noi sappiamo esattamente che quella query non ha bisogno della nostra tabella. Pertanto, proviamo comunque a riportare le prestazioni del sistema a livelli ragionevoli, eliminando dalla tabella tutto il superfluo — almeno manualmente, poiché VACUUM si tira indietro.

Per rendere le cose più chiare, analizziamo già un esempio di tabella buffer. Vale a dire, ci sono grandi flussi di INSERT/DELETE, e a volte la tabella risulta completamente vuota. Ma se non è vuota, dobbiamo mantenere il suo contenuto attuale.

#0: Оцениваем ситуацию

È chiaro che si può cercare di fare qualcosa con la tabella anche dopo ogni operazione, ma non ha molto senso — le spese generali di manutenzione saranno chiaramente superiori alla capacità di elaborazione delle query target.

Definiamo i criteri — "è già tempo di agire", se:

VACUUM è stato avviato abbastanza tempo fa
Ci aspettiamo un carico elevato, quindi che sia 60 secondi dall'ultimo [auto]VACUUM.
la dimensione fisica della tabella è superiore a quella obiettivo
Definiamola come il doppio del numero di pagine (blocchi da 8KB) rispetto alla dimensione minima — 1 blk su heap + 1 blk su ciascun indice — per una tabella potenzialmente vuota. Se ci aspettiamo che nel buffer rimanga sempre una certa quantità di dati, questa formula è ragionevole da ottimizzare.

Query di controllo

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- qui sarebbe più corretto fare * current_setting('block_size')::bigint, ma chi cambia le dimensioni dei blocchi?..
, 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: Все равно VACUUM

Non possiamo sapere in anticipo quanto un query parallelo ci influenzi - quante effettivamente le registrazioni siano "scadute" dal momento del suo inizio. Pertanto, quando decidiamo di elaborare in qualche modo la tabella, prima di tutto dovremmo eseguire su di essa VACUUM — essa, a differenza di VACUUM FULL, non ostacola i processi paralleli che lavorano su dati in lettura e scrittura.

In questo modo, può subito pulire gran parte di ciò che vorremmo rimuovere. E i successivi query su questa tabella saranno sulla «cache calda», il che ridurrà la loro durata - e, quindi, anche il tempo totale di blocco di altre transazioni servite dal nostro.

#2: Есть кто-нибудь дома?

Verifichiamo - c'è qualcosa nella tabella?

TABLE tbl LIMIT 1;

Se non è rimasta neppure una registrazione, possiamo risparmiare molto sulla lavorazione - semplicemente eseguendo (abbiamo risparmiato anche quella chiamata) al termine della transazione precedente.:

Essa agisce come un comando DELETE incondizionato per ogni tabella, ma molto più velocemente, poiché in realtà non scansiona le tabelle. Inoltre, libera immediatamente spazio su disco, quindi non è necessario eseguire l'operazione VACUUM dopo di essa.

Se è necessario azzerare il contatore della sequenza della tabella (RESTART IDENTITY) - decidi tu.

#3: Все — по-очереди!

Poiché stiamo operando in condizioni di alta concorrenza, mentre controlliamo l'assenza di registrazioni nella tabella, qualcun altro potrebbe già averci scritto. Non dobbiamo perdere queste informazioni, quindi - cosa fare? Giusto, dobbiamo assicurarci che nessuno possa scrivere.

Per questo dobbiamo attivare SERIALIZABLE- l'isolamento per la nostra transazione (sì, qui iniziamo una transazione) e bloccare la tabella "a morte":

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

Proprio questo livello di blocco è determinato dalle operazioni che vogliamo eseguire su di essa.

#4: Конфликт интересов

Noi arriviamo qui e vogliamo "bloccare" la tabella - e se in quel momento qualcun altro è attivo su di essa, ad esempio, la sta leggendo? Ci "fermeremo" in attesa di liberare questo blocco, mentre altri che vogliono leggere si scontreranno con noi...

Per evitare che ciò accada, ci "sacrificeremo" - se dopo un certo (meno accettabile) periodo non siamo riusciti a ottenere il blocco, riceveremo un'eccezione dal database, ma almeno non ostacoleremo eccessivamente gli altri.

Per questo imposteremo la variabile della sessione lock_timeout (per versioni 9.3+) o/ed statement_timeout. La cosa principale da ricordare è che il valore di statement_timeout si applica solo alla dichiarazione successiva. Cioè, in questo modo in un concatenamento — non funzionerà:

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

Per non dover ripristinare successivamente il valore 'vecchio' della variabile, utilizziamo la forma SET LOCAL, che limita l'ambito di applicazione dell'impostazione alla transazione corrente.

Ricordiamo che statement_timeout si applica a tutte le successive richieste, affinché la transazione non possa estendersi a dimensioni inaccettabili, se ci sono molti dati nella tabella.

#5: Копируем данные

Se la tabella non è del tutto vuota — i dati dovranno essere riscritti tramite una tabella temporanea ausiliaria:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Firma ON COMMIT DROP significa che al termine della transazione la tabella temporanea smetterà di esistere, e non sarà necessario occuparsi della sua eliminazione manuale nel contesto della connessione.

Poiché supponiamo che i dati 'vivi' non siano tanti, questa operazione dovrebbe avvenire abbastanza rapidamente.

Ecco, più o meno è tutto! Non dimenticate di eseguire ANALYZE per normalizzare le statistiche della tabella, se necessario.

Raccogliamo lo script finale

Usiamo un 'pseudo-python' come questo:

# собираем статистику с таблицы
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 massima, sì, se sull’oid della tabella stessa non ci sono altre attività collegate da parte della BL o FK da parte del 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 originale 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 tutte le UPDATE ora sono di nuovo veloci.

Fonte: habr.com

Quando VACUUM è in pausa — puliamo la tabella manualmente