Questa pagina descrive cosa puoi fare quando il tuo database rileva la protezione da wraparound dell'ID transazione in PostgreSQL. Si manifesta come messaggio ERROR
, come
segue:
database is not accepting commands to avoid wraparound data loss in database dbname. Stop the postmaster and vacuum that database in single-user mode. You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
In alternativa, potrebbe essere visualizzato un messaggio WARNING
come segue:
database dbname must be vacuumed within 10985967 transactions. To avoid a database shutdown, execute a database-wide VACUUM in that database.
Panoramica della procedura
- Scopri quale database e quali tabelle causano il wrapping.
- Controlla se c'è qualcosa che impedisce l'esecuzione di (AUTO)VACUUM (ad esempio, un ID transazione bloccato).
- Misura la velocità di AUTOVACUUM. Se è lento, puoi provare ad accelerarlo.
- Se necessario, esegui manualmente altri comandi VACUUM.
- Esamina altri modi per velocizzare l'aspirazione. A volte il modo più rapido è eliminare la tabella o alcuni indici.
Molti suggerimenti per i valori dei flag non sono volutamente esatti perché dipendono da molti parametri del database. Leggi i documenti collegati alla fine di questa pagina per un'analisi più approfondita di questo argomento.
Trovare il database e la tabella che causano il wrapping
Trovare il database
Per scoprire quale o quali database contengono le tabelle che causano il wraparound, esegui la seguente query:
SELECT datname,
age(datfrozenxid),
2^31-1000000-age(datfrozenxid) as remaining
FROM pg_database
ORDER BY 3
Il database con il valore remaining
vicino a 0 è quello che causa il problema.
Trovare la tabella
Connettiti a questo database ed esegui la seguente query:
SELECT c.relnamespace::regnamespace as schema_name,
c.relname as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age,
2^31-1000000-greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as remaining
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY 4;
Questa query restituisce la tabella o le tabelle che causano il problema.
Per le tabelle TEMPORANEE
Se schema_name
inizia con pg_temp_
, l'unico modo per risolvere il problema è eliminare la tabella, perché PostgreSQL non consente di eseguire VACUUM sulle tabelle temporanee create in altre sessioni. A volte, se la sessione è aperta e accessibile, puoi eseguire il vacuum della tabella, ma spesso non è così.
Utilizza le seguenti istruzioni SQL per eliminare la tabella temporanea:
SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
DROP TABLE pg_temp_<N>.<tablename>;
Se questo era l'unico blocco, in circa un minuto autovacuum rileva questa modifica e sposta datfrozenxid
in avanti in pg_database
. In questo modo
viene risolto lo stato di sola lettura della protezione di wraparound.
Tabelle normali
Per le tabelle normali (non temporanee), continua con i passaggi successivi riportati di seguito per verificare se qualcosa blocca la pulizia, se VACUUM viene eseguito abbastanza velocemente e se la tabella più importante viene sottoposta a vacuum.
Controllare se un ID transazione è bloccato
Un possibile motivo per cui il sistema può esaurire gli ID transazione è che
PostgreSQL non può congelare (ovvero contrassegnare come visibili a tutte le transazioni)
gli ID transazione creati dopo l'avvio della transazione attualmente in esecuzione più vecchia. Ciò è dovuto alle regole di controllo della contemporaneità multiversione (MVCC). In casi
estremi, queste transazioni possono diventare così vecchie da rendere impossibile
a VACUUM ripulire le transazioni precedenti per l'intero limite di wraparound di 2 miliardi
di ID transazione e causare l'interruzione dell'accettazione di nuovi
DML da parte dell'intero sistema. In genere, nel file di log vengono visualizzati anche avvisi che indicano WARNING: oldest
xmin is far in the past
.
Puoi passare all'ottimizzazione solo dopo aver risolto il problema relativo all'ID transazione bloccato.
Ecco quattro potenziali motivi per cui potrebbe esserci un ID transazione bloccato, con informazioni su come risolvere ciascun problema:
- Transazioni a lunga esecuzione:identificale e annulla o termina il backend per sbloccare l'aspirapolvere.
- Transazioni di preparazione orfane: esegui il rollback di queste transazioni.
- Slot di replica abbandonati:elimina gli slot abbandonati.
- Transazione a lunga esecuzione sulla replica, con
hot_standby_feedback = on
: identificala e annullala o termina il backend per sbloccare il vacuum.
Per questi scenari, la seguente query restituisce l'età della transazione più vecchia e il numero di transazioni rimanenti fino al wraparound:
WITH q AS ( SELECT (SELECT max(age(backend_xmin)) FROM pg_stat_activity WHERE state != 'idle' ) AS oldest_running_xact_age, (SELECT max(age(transaction)) FROM pg_prepared_xacts) AS oldest_prepared_xact_age, (SELECT max(greatest(age(catalog_xmin),age(xmin))) FROM pg_replication_slots) AS oldest_replication_slot_age, (SELECT max(age(backend_xmin)) FROM pg_stat_replication) AS oldest_replica_xact_age ) SELECT *, 2^31 - oldest_running_xact_age AS oldest_running_xact_left, 2^31 - oldest_prepared_xact_age AS oldest_prepared_xact_left, 2^31 - oldest_replication_slot_age AS oldest_replication_slot_left, 2^31 - oldest_replica_xact_age AS oldest_replica_xact_left FROM q;
Questa query potrebbe restituire uno qualsiasi dei valori *_left segnalati vicino o inferiore a 1 milione di distanza dal wraparound. Questo valore è il limite di protezione del wraparound quando PostgreSQL smette di accettare nuovi comandi di scrittura. In questo caso, consulta Rimuovere i blocchi VACUUM o Ottimizzare VACUUM.
Ad esempio, la query precedente potrebbe restituire:
┌─[ RECORD 1 ]─────────────────┬────────────┐ │ oldest_running_xact_age │ 2146483655 │ │ oldest_prepared_xact_age │ 2146483655 │ │ oldest_replication_slot_age │ ¤ │ │ oldest_replica_xact_age │ ¤ │ │ oldest_running_xact_left │ 999993 │ │ oldest_prepared_xact_left │ 999993 │ │ oldest_replication_slot_left │ ¤ │ │ oldest_replica_xact_left │ ¤ │ └──────────────────────────────┴────────────┘
dove oldest_running_xact_left
e oldest_prepared_xact_left
rientrano nel limite di protezione
di 1 milione. In questo caso, per poter procedere devi prima rimuovere i blocchi per
l'aspirapolvere.
Rimuovere i blocchi di VACUUM
Transazioni a lunga esecuzione
Nella query precedente, se oldest_running_xact
è uguale a
oldest_prepared_xact
, vai alla sezione
Prepara transazione orfana, perché il valore ultima esecuzione
include anche le transazioni preparate.
Potresti dover prima eseguire questo comando come utente postgres
:
GRANT pg_signal_backend TO postgres;
Se la transazione incriminata appartiene a uno degli utenti di sistema (a partire da
cloudsql...
), non puoi annullarla direttamente. Per annullarlo, devi riavviare il database.
Per identificare una query di lunga durata e annullarla o terminarla per sbloccare
il comando vacuum, seleziona prima alcune delle query meno recenti. La riga LIMIT 10
aiuta
a visualizzare il risultato sullo schermo. Potresti dover ripetere questa operazione dopo aver risolto
le query in esecuzione meno recenti.
SELECT pid, age(backend_xid) AS age_in_xids, now() - xact_start AS xact_age, now() - query_start AS query_age, state, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY 2 DESC LIMIT 10;
Se age_in_xids
restituisce NULL
, significa che alla transazione non è stato assegnato un ID transazione permanente e può essere ignorata in sicurezza.
Annulla le query in cui xids_left_to_wraparound
si sta avvicinando a 1 milione.
Se state
è active
, la query può essere annullata
utilizzando SELECT pg_cancel_backend(pid);
. In caso contrario, devi
terminare l'intera connessione utilizzando SELECT pg_terminate_backend(pid);
,
dove pid è pid
della query precedente
Transazioni di preparazione orfane
Elenca tutte le transazioni preparate:
DB_NAME=> SELECT age(transaction),* FROM pg_prepared_xacts ; ┌─[ RECORD 1 ]┬───────────────────────────────┐ │ age │ 2146483656 │ │ transaction │ 2455493932 │ │ gid │ trx_id_pin │ │ prepared │ 2021-03-03 16:54:07.923158+00 │ │ owner │ postgres │ │ database │ DB_NAME │ └─────────────┴───────────────────────────────┘
Esegui il rollback delle transazioni preparate orfane meno recenti
utilizzando gid
dell'ultima query (in questo caso, trx_id_pin
) come
ID transazione:
ROLLBACK PREPARED trx_id_pin;
In alternativa, esegui il commit:
COMMIT PREPARED trx_id_pin;
Per una spiegazione completa, consulta la documentazione relativa a SQL ROLLBACK PREPARED.
Slot di replica abbandonati
Se lo slot di replica viene abbandonato perché la replica esistente
è stata interrotta, sospesa o presenta un altro problema, puoi
eliminare la replica dalla console gcloud
o Google Cloud .
Innanzitutto, verifica che la replica non sia disattivata come descritto nella sezione Gestione delle repliche di lettura. Se la replica è disabilitata, riattivala. Se il ritardo rimane elevato, elimina la replica,
Gli slot di replica sono visibili nella visualizzazione di sistema pg_replication_slots
.
La seguente query recupera le informazioni pertinenti:
SELECT *, age(xmin) AS age FROM pg_replication_slots; ┌─[ RECORD 1 ]────────┬─────────────────────────────────────────────────┐ │ slot_name │ cloudsql_1_355b114b_9ff4_4bc3_ac88_6a80199bd738 │ │ plugin │ ¤ │ │ slot_type │ physical │ │ datoid │ ¤ │ │ database │ ¤ │ │ active │ t │ │ active_pid │ 1126 │ │ xmin │ 2453745071 │ │ catalog_xmin │ ¤ │ │ restart_lsn │ C0/BEF7C2D0 │ │ confirmed_flush_lsn │ ¤ │ │ age │ 59 │ └─────────────────────┴─────────────────────────────────────────────────┘
In questo esempio, il valore pg_replication_slots
è integro (age == 59).
Se l'età è vicina a 2 miliardi, ti consigliamo di eliminare lo slot. Non esiste un modo semplice per sapere quale replica è quale nel caso in cui la query
restituisca più record. Quindi, controllali tutti nel caso in cui ci sia una transazione
di lunga durata su una replica.
Transazioni a lunga esecuzione sulle repliche
Controlla le repliche per la transazione in esecuzione meno recente con hot_standby_feedback
impostato su on
e disabilitala nella replica.
La colonna backend_xmin
nella visualizzazione pg_stat_replication
contiene il TXID
più vecchio necessario per la replica.
Per farla avanzare, interrompi la query che la blocca sulla replica. Per scoprire quale query la sta rallentando, utilizza la query in Transazioni a lunga esecuzione, ma questa volta eseguila sulla replica.
Un'altra opzione è riavviare la replica.
Configura VACUUM
Imposta i seguenti due flag:
- autovacuum_vacuum_cost_delay = 0
- autovacuum_work_mem = 1048576
Il primo disattiva qualsiasi limitazione del disco per l'operazione VACUUM di PostgreSQL, in modo che VACUUM possa essere eseguita alla massima velocità. Per impostazione predefinita, autovacuum viene limitato in modo da non utilizzare tutto l'I/O del disco sui server più lenti.
Il secondo flag, autovacuum_work_mem
, riduce il numero di passaggi di pulizia
dell'indice. Se possibile, deve essere abbastanza grande da archiviare tutti gli ID delle righe
eliminate in una tabella che VACUUM pulirà. Quando imposti questo valore,
tieni presente che si tratta della quantità massima di memoria locale che ogni VACUUM in esecuzione
può allocare. Assicurati di non consentire un numero superiore a quello disponibile, con
una parte lasciata in riserva. Se lasci il database in esecuzione in modalità di sola lettura, considera anche la memoria locale utilizzata per le query di sola lettura.
Nella maggior parte dei sistemi, utilizza il valore massimo (1 GB o 1048576 kB, come mostrato nell'esempio). Questo valore può contenere fino a circa 178 milioni di tuple morte. Qualsiasi altro valore causa ancora più passaggi di scansione dell'indice.
Questi e altri flag sono spiegati in modo più dettagliato nell'articolo Ottimizzazione, monitoraggio e risoluzione dei problemi delle operazioni VACUUM in PostgreSQL.
Dopo aver impostato questi flag, riavvia il database in modo che autovacuum inizi con i nuovi valori.
Puoi utilizzare la visualizzazione pg_stat_progress_vacuum
per monitorare l'avanzamento dei
VACUUM avviati automaticamente. Questa visualizzazione mostra i VACUUM in esecuzione in tutti i database e per le tabelle (relazioni) di altri database per cui non è possibile cercare il nome della tabella utilizzando la colonna della visualizzazione relid
.
Per identificare i database e le tabelle che devono essere sottoposti a VACUUM, utilizza le query riportate in Ottimizzazione, monitoraggio e risoluzione dei problemi delle operazioni VACUUM in PostgreSQL. Se la VM server è sufficientemente potente e ha la larghezza di banda per più processi VACUUM paralleli di quelli avviati da autovacuum, puoi avviare alcuni vacuum manuali.
Controlla la velocità di VACUUM
Questa sezione descrive come controllare la velocità di VACUUM e come accelerarla, se necessario.
Controllare gli aspirapolvere automatici in funzione
Tutti i backend che eseguono VACUUM sono visibili nella visualizzazione di sistema pg_stat_progress_vacuum.
Se la fase attuale è scanning heap
, puoi monitorare
l'avanzamento osservando le modifiche nella colonna heap_blks_scanned
.
Purtroppo, non esiste un modo semplice per determinare la velocità di scansione in altre fasi.
Stimare la velocità di scansione VUOTO
Per stimare la velocità di scansione, devi prima memorizzare i valori di base e poi
calcolare la variazione nel tempo per stimare il tempo di completamento. Innanzitutto, devi salvare uno snapshot di heap_blks_scanned
insieme a un timestamp utilizzando la seguente query snapshot:
SELECT set_config('save.ts', clock_timestamp()::text, false), set_config('save.heap_blks_scanned', heap_blks_scanned::text, false) FROM pg_stat_progress_vacuum WHERE datname = 'DB_NAME';
Poiché non possiamo salvare nulla nelle tabelle già in wraparound, utilizza
set_config(flag, value)
per impostare due flag definiti dall'utente, save.ts
e save.heap_blks_scanned
, sui valori correnti di
pg_stat_progress_vacuum
.
Nella query successiva, utilizziamo questi due valori come base di confronto per determinare la velocità e stimare il tempo di completamento.
NOTA: WHERE datname = DB_NAME
limita l'indagine a un
database alla volta. Questo numero è sufficiente se in questo database è in esecuzione un solo autovacuum, con più di una riga per database. Le condizioni di filtro aggiuntive
('AND relid= …'')
devono essere aggiunte a WHERE per indicare una singola
riga di autovacuum. Lo stesso vale per la query successiva.
Dopo aver salvato i valori di base, puoi eseguire la seguente query:
with q as ( SELECT datname, phase, heap_blks_total, heap_blks_scanned, clock_timestamp() - current_setting('save.ts')::timestamp AS ts_delta, heap_blks_scanned - current_setting('save.heap_blks_scanned')::bigint AS scanned_delta FROM pg_stat_progress_vacuum WHERE datname = DB_NAME ), q2 AS ( SELECT *, scanned_delta / extract('epoch' FROM ts_delta) AS pages_per_second FROM q ) SELECT *, (heap_blks_total - heap_blks_scanned) / pages_per_second AS remaining_time FROM q2 ;
┌─[ RECORD 1 ]──────┬──────────────────┐ │ datname │ DB_NAME │ │ phase │ scanning heap │ │ heap_blks_total │ 9497174 │ │ heap_blks_scanned │ 18016 │ │ ts_delta │ 00:00:40.30126 │ │ as_scanned_delta │ 11642 │ │ pages_per_second │ 288.87434288655 │ │ remaining_time │ 32814.1222418038 │ └───────────────────┴──────────────────┘
Questa query confronta i valori attuali con i valori di base salvati e calcola
pages_per_second
e remaining_time
, il che ci consente di decidere se
VACUUM viene eseguito abbastanza velocemente o se vogliamo accelerarlo. Il valore
remaining_time
è valido solo per la fase scanning heap
.
Anche le altre fasi richiedono tempo, a volte anche di più. Puoi scoprire di più sull'aspirazione
e visualizzare i post del blog su internet che trattano alcuni degli aspetti complessi
del vuoto.
Velocizzare VACUUM
Il modo più semplice e veloce per velocizzare la scansione VACUUM è impostare
autovacuum_vacuum_cost_delay=0
. Questa operazione può essere eseguita dalla console
Google Cloud .
Purtroppo, l'operazione VACUUM già in esecuzione non rileva questo valore e potresti dover riavviare il database.
Dopo il riavvio, potresti visualizzare un risultato simile al seguente:
┌─[ RECORD 1 ]──────┬──────────────────┐ │ datname │ DB_NAME │ │ phase │ scanning heap │ │ heap_blks_total │ 9497174 │ │ heap_blks_scanned │ 222382 │ │ ts_delta │ 00:00:21.422615 │ │ as_scanned_delta │ 138235 │ │ pages_per_second │ 6452.76031894332 │ │ remaining_time │ 1437.33713040171 │ └───────────────────┴──────────────────┘
In questo campione, la velocità è aumentata da <300 pagine/sec a circa 6500 pagine/sec e il tempo rimanente previsto per la fase di scansione dell'heap è diminuito da 9 ore a 23 minuti.
La velocità di scansione delle altre fasi non è altrettanto facile da misurare, ma dovrebbe mostrare un'accelerazione simile.
Valuta anche la possibilità di impostare autovacuum_work_mem
sul valore più alto possibile per
evitare più passaggi sugli indici. Un passaggio dell'indice si verifica ogni volta che la memoria
viene riempita con puntatori di tuple non valide.
Se il database non viene utilizzato in altro modo, imposta autovacuum_work_mem
in modo che la memoria libera sia circa l'80% dopo aver consentito la quantità richiesta per shared_buffers
.
Questo è il limite superiore per ciascuno dei processi VACUUM avviati da autovacuum. Se
vuoi continuare a eseguire carichi di lavoro di sola lettura, utilizza meno memoria.
Altri modi per migliorare la velocità
Evitare di eseguire il vacuum degli indici
Per le tabelle di grandi dimensioni, VACUUM trascorre la maggior parte del tempo a pulire gli indici.
PostgreSQL 14 dispone di ottimizzazioni speciali per evitare la pulizia degli indici se il sistema rischia di esaurire lo spazio di archiviazione.
In PostgreSQL 12 e 13, puoi eseguire manualmente la seguente istruzione:
VACUUM (INDEX_CLEANUP OFF, TRUNCATE OFF) <tablename>;
Nelle versioni 11 e precedenti, puoi DROP
l'indice prima di eseguire vacuum e ricrearlo in un secondo momento.
L'eliminazione dell'indice quando è già in esecuzione un autovacuum sulla tabella richiede l'annullamento della pulizia in esecuzione e l'esecuzione immediata del comando di eliminazione dell'indice prima che autovacuum riesca a riavviare la pulizia della tabella.
Innanzitutto, esegui la seguente istruzione per trovare il PID del processo autovacuum da terminare:
SELECT pid, query
FROM pg_stat_activity
WHERE state != 'idle'
AND query ilike '%vacuum%';
Quindi, esegui le seguenti istruzioni per terminare l'esecuzione di vacuum e eliminare uno o più indici:
SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
SELECT pg_terminate_backend(<pid>);DROP INDEX <index1>;DROP INDEX <index2>; ...
Elimina la tabella incriminata
In alcuni rari casi, puoi eliminare la tabella. Ad esempio, se si tratta di una tabella facile da ripristinare da un'altra origine, come un backup o un altro database.
Devi comunque utilizzare cloudsql.enable_maintenance_mode = 'on'
e probabilmente
anche terminare VACUUM nella tabella come mostrato nella sezione precedente.
ASPIRAPOLVERE PIENO
In rari casi, è più veloce eseguire VACUUM FULL FREEZE
,
in genere quando la tabella contiene solo una piccola parte di tuple attive.
Puoi controllare questa informazione dalla visualizzazione pg_stat_user_tables
(a meno che non si sia verificato un arresto anomalo che ha cancellato le statistiche).
Il comando VACUUM FULL
copia le tuple attive in un nuovo file, quindi deve essere disponibile spazio sufficiente per il nuovo file e i relativi indici.
Passaggi successivi
- Scopri di più su VACUUM per il wraparound
- Scopri di più sulla pulizia di routine.
- Scopri di più sull'aspirazione automatica
- Scopri di più su Ottimizzazione, monitoraggio e risoluzione dei problemi delle operazioni VACUUM in PostgreSQL