può "ripulire" dalla tabella in PostgreSQL solo ciò che nessuno può vedere cioè non ci sono richieste attive che siano iniziate prima che queste righe venissero modificate.
E se c'è un tipo sgradevole (un carico OLAP prolungato su un database OLTP)? Come pulire una tabella attivamente in cambiamento in un contesto di richieste lunghe senza incorrere in errori?
Esaminiamo gli errori
Iniziamo a determinare in cosa consiste e come può sorgere il problema che vogliamo risolvere.
Di solito, questa situazione si verifica in una tabella relativamente piccola, ma in cui avvengono molti cambiamenti.Di solito si tratta di diversi counter/aggregati/classifiche, sui quali viene eseguito frequentemente l'UPDATE, o una coda per elaborare un flusso di eventi in corso, con registrazioni per cui vengono eseguiti continuamente INSERT/DELETE.
Cerchiamo di riprodurre un esempio con le classifiche:
CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- costruiremo la classifica su questo indice
INSERT INTO
tbl
SELECT
chr(ascii('a'::text) + i) k
, 0 v
FROM
generate_series(0, 25) i;Parallelamente, in un'altra connessione, si avvia una richiesta lunga-lunga che raccoglie qualche statistica complessa, ma non coinvolge la nostra tabella:
SELECT pg_sleep(10000);Ora aggiorniamo molte molte volte il valore di uno dei contatori. Per pulire l'esperimento, faremo questo , come accadrebbe 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.808Cosa è 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 anche il nostro punteggio cresce sempre più lentamente.
Tutto è colpa del MVCC
Il problema risiede nel , che costringe la query a esaminare tutte le versioni precedenti del record. Quindi, puliamo la nostra tabella da versioni "morte":
VACUUM VERBOSE tbl;INFO: pulizia di "public.tbl"
INFO: "tbl": trovati 0 rimovibili, 10026 versioni di righe non rimovibili in 45 su 45 pagine
DETTAGLIO: 10000 versioni di righe morte non possono essere rimosse ancora, xmin più antico: 597439602Oh, ma non c'è nulla da pulire! Contestualmente la query in esecuzione ci ostacola — perché potrebbe un giorno voler accedere a queste versioni (e se?), e devono essere disponibili per lui. E quindi anche VACUUM FULL non ci aiuterà.
«Stringiamo» la tabella
Ma noi sappiamo con certezza che quella query non ha bisogno della nostra tabella. Quindi cerchiamo di restituire le prestazioni del sistema a livelli adeguati, eliminando il superfluo dalla tabella — almeno "manualmente", dato che VACUUM non ci assiste.
Per rendere le cose più chiare, consideriamo il caso della tabella buffer. C'è un grande flusso 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ò tentare di fare qualcosa con la tabella anche dopo ogni operazione, ma non ha molto senso: i costi di gestione saranno sicuramente superiori alla capacità di elaborazione delle query mirate.
Formuliamo i criteri: "è ora di agire" se:
- VACUUM è stato avviato abbastanza tempo fa
Ci aspettiamo un carico elevato, quindi lasciamo che questo sia 60 secondi dall'ultima operazione [auto]VACUUM. - la dimensione fisica della tabella è superiore a quella prevista
Definiamola come il doppio del numero di pagine (blocchi di 8KB) rispetto alla dimensione minima - 1 blk nel heap + 1 blk per ciascun indice - per una tabella potenzialmente vuota. Se, invece, ci aspettiamo che nel buffer ci sia sempre una certa quantità di dati, è ragionevole ottimizzare questa formula.
Query di verifica
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 la dimensione del blocco?..
, 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'istruzione parallela possa influire — quante esatte righe sono risultate "obsolete" dall'inizio della sua esecuzione. Quindi, quando decideremo di elaborare la tabella, è sicuramente consigliabile eseguire prima VACUUM — a differenza di VACUUM FULL, non ostacola i processi paralleli che operano sui dati in lettura-scrittura.
Inoltre, può eliminare immediatamente gran parte di ciò che vogliamo rimuovere. E le richieste successive su questa tabella saranno sul "cache caldo", il che ridurrà la loro durata — e, di conseguenza, il tempo totale di blocco di altre transazioni che la nostra operazione sta gestendo.
#2: Есть кто-нибудь дома?
Verifichiamo — c'è davvero qualcosa nella tabella:
TABLE tbl LIMIT 1;Se non è rimasta nemmeno una riga, possiamo risparmiare notevolmente sulla lavorazione — semplicemente eseguendo :
Funziona proprio come un comando DELETE incondizionato per ogni tabella, ma è molto più veloce, poiché non scansiona effettivamente le tabelle. Inoltre, rilascia immediatamente spazio su disco, quindi non è necessario eseguire l'operazione VACUUM dopo.
Dovete decidere se resettare il contatore di sequenza della tabella (RESTART IDENTITY).
#3: Все — по-очереди!
Poiché operiamo in un contesto altamente competitivo, mentre verifichiamo l'assenza di record nella tabella, qualcuno potrebbe già averci scritto qualcosa. Non dobbiamo perdere queste informazioni, quindi? Esatto, dobbiamo assicurarci che nessuno possa scrivere.
Per fare ciò, è necessario attivare SERIALIZABLE-l'isolamento per la nostra transazione (sì, qui avviamo la transazione) e bloccare la tabella "in modo permanente":
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;Proprio questo livello di blocco è dovuto alle operazioni che vogliamo eseguire su di essa.
#4: Конфликт интересов
Qui vogliamo "bloccare" la tabella - e se nel frattempo qualcuno era attivo su di essa, per esempio, la stava leggendo? Rimarremo "appesi" in attesa che tale blocco venga rilasciato, mentre altri che desiderano leggere si imbatteranno in noi...
Per evitare che ciò accada, "sacrificeremo noi stessi": se per un certo (eccessivamente breve) tempo non riusciamo comunque a ottenere il blocco, riceveremo un'eccezione dal database, ma almeno non ostacoleremo eccessivamente gli altri.
Per questo, impostiamo una variabile di sessione (per le versioni 9.3+) o/e . È importante ricordare che il valore di statement_timeout si applica solo alla dichiarazione successiva. Quindi, in questo modo in concatenazione — non funzionerà:
SET statement_timeout = ...;LOCK TABLE ...;Per non dover ripristinare poi il ‘vecchio’ valore della variabile, utilizziamo la forma SET LOCAL, che limita il campo di applicazione della configurazione alla transazione corrente.
Ricordiamo che statement_timeout si applica a tutte le richieste successive, in modo che la transazione non possa protrarsi indefinitamente, se i dati nella tabella risultano essere molti.
#5: Копируем данные
Se la tabella non è completamente vuota — i dati dovranno essere salvati nuovamente attraverso 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 cesserà di esistere, e non sarà necessario eliminarla manualmente nel contesto della connessione.
Poiché supponiamo che ci siano pochi ‘dati vivi’, questa operazione dovrebbe essere abbastanza rapida.
Ecco, sembra che sia tutto! Non dimenticate di per normalizzare le statistiche della tabella, se necessario.
Raccogliamo lo script finale
Utilizziamo 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 massima, sì, se su oid della tabella non sono legate altre attività da parte di 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 verifichiamo 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 è andato a buon fine! La tabella si è ridotta di 50 volte e tutti gli UPDATE ora sono di nuovo rapidi.
Fonte: habr.com
