Questa pagina descrive cosa puoi fare quando il tuo database rileva la protezione Transaction ID Wraparound in PostgreSQL. Si manifesta come un messaggio ERROR, come segue:
database is not accepting commands to avoid wraparound data loss in database DB_NAME. 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 il seguente:
database DB_NAME must be vacuumed within 10985967 transactions. To avoid a database shutdown, execute a database-wide VACUUM in that database.
Segnala cloudsql.enable_maintenance_mode
Per impostazione predefinita, non puoi eseguire DDL o DML quando il database è "in wraparound", ma Cloud SQL per PostgreSQL offre una modalità di manutenzione di emergenza che ti offre altri 500.000 ID transazione da utilizzare per uscire dalla situazione senza dover riavviare il server in modalità utente singolo.
Per attivare la modalità di manutenzione, imposta cloudsql.enable_maintenance_mode su on nella sessione del database prima di utilizzare qualsiasi comando che richieda un ID transazione:
SET cloudsql.enable_maintenance_mode = 'on';
[ your SQL, DML or DDL commands here ]
Questa operazione deve essere eseguita nella sessione in cui esegui la manutenzione prima di qualsiasi operazione che richieda un ID transazione.
Panoramica della procedura
Scopri quale database e quali tabelle stanno causando il wraparound.
Controlla se c'è qualcosa che blocca (AUTO)VACUUM (ad esempio un ID transazione bloccato).
Misura la velocità di AUTOVACUUM. Se è lenta, puoi provare a velocizzarla.
Se necessario, esegui manualmente altri comandi VACUUM.
Esamina altri modi per velocizzare l'aspirapolvere. A volte il modo più rapido è eliminare la tabella o alcuni indici.
Molti dei suggerimenti per i valori dei flag non sono volutamente esatti perché dipendono da molti parametri del database. Leggi i link alla documentazione collegati alla fine di questa pagina per un'analisi più approfondita di questo argomento.
Trova il database e la tabella che causano il wraparound
Ricerca del database
Per scoprire quale o quali database contengono le tabelle che causano il wraparound, esegui questa 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.
Ricerca della tabella
Connettiti al database ed esegui questa 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 TEMPORARY
Se schema_name inizia con pg_temp_, l'unico modo per risolvere il problema è eliminare la tabella, perché PostgreSQL non consente di eseguire l'operazione 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.TABLE_NAME;
Sostituisci quanto segue:
N: il numero dello schema temporaneo.TABLE_NAME: il nome della tabella temporanea da eliminare.
Se questo era l'unico blocco, dopo circa un minuto l'autovacuum rileva questa modifica e sposta datfrozenxid in avanti in pg_database. In questo modo, lo stato di sola lettura della protezione wraparound viene risolto.
Tabelle normali
Per le tabelle normali (ovvero 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 viene eseguito il vacuuming della tabella più importante.
Controlla se un ID transazione è bloccato
Un possibile motivo per cui il sistema può esaurire gli ID transazione è che PostgreSQL non può bloccare (ovvero contrassegnare come visibili per tutte le transazioni) gli ID transazione creati dopo l'avvio della transazione attualmente in esecuzione più vecchia. Ciò è dovuto alle regole del controllo della contemporaneità multiversione (MVCC). In casi estremi, queste transazioni possono diventare così vecchie da rendere impossibile per VACUUM ripulire le transazioni precedenti per l'intero limite di wraparound dell'ID transazione di 2 miliardi 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.
Dovresti passare all'ottimizzazione solo dopo aver risolto il problema dell'ID transazione bloccato.
Ecco quattro possibili motivi per cui un ID transazione potrebbe essere bloccato, con informazioni su come risolvere ciascuno di questi problemi:
- Transazioni a lunga esecuzione: identificale e annulla o termina il backend per sbloccare il vacuum.
- 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 annulla 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 come vicini o inferiori a 1 milione di distanza dal wraparound. Questo valore è il limite di protezione da wraparound quando PostgreSQL smette di accettare nuovi comandi di scrittura. In questo caso, consulta Rimuovi i blocchi di VACUUM o Ottimizza 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 wraparound di 1 milione. In questo caso, devi prima rimuovere i blocchi affinché VACUUM possa procedere.
Rimuovi i blocchi VACUUM
Transazioni a lunga esecuzione
Nella query precedente, se oldest_running_xact è uguale a
oldest_prepared_xact, vai alla sezione
Transazione di preparazione orfana, perché il valore ultima esecuzione
include anche le transazioni preparate.
Potresti dover prima eseguire il seguente comando come utente postgres:
GRANT pg_signal_backend TO postgres;
Se la transazione incriminata appartiene a uno degli utenti di sistema (che iniziano con
cloudsql...), non puoi annullarla direttamente. Devi riavviare il database per annullarla.
Per identificare una query a lunga esecuzione e annullarla o terminarla per sbloccare il vacuum, seleziona prima alcune delle query meno recenti. La linea LIMIT 10 aiuta
a adattare il risultato allo 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 è il 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 il 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 di SQL ROLLBACK PREPARED.
Slot di replica abbandonati
Se lo slot di replica viene abbandonato perché la replica esistente è stata interrotta, messa in pausa o presenta un altro problema, puoi eliminarla dalla consolegcloud o Cloud de Confiance .
Innanzitutto, verifica che la replica non sia disabilitata come descritto in Gestione delle repliche di lettura. Se la replica è disabilitata, abilitala di nuovo. Se il ritardo rimane elevato, elimina la replica,
Gli slot di replica sono visibili nella vista 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à era 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, controllale tutte per verificare se esiste una transazione a lunga esecuzione 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 disabilitalo sulla replica.
La colonna backend_xmin nella vista pg_stat_replication contiene il TXID meno recente necessario per la replica.
Per spostarlo in avanti, interrompi la query che lo blocca sulla replica. Per scoprire quale query lo sta bloccando, 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 = 0autovacuum_work_mem = 1048576
Il primo disabilita qualsiasi limitazione del disco per l'operazione VACUUM di PostgreSQL, in modo che VACUUM possa essere eseguito alla massima velocità. Per impostazione predefinita, l'autovacuum è limitato in modo da non consumare tutte le operazioni di 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 memorizzare tutti gli ID delle righe inattive in una tabella che VACUUM deve ripulire. 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 più di quanto sia disponibile, lasciando una parte in riserva. Se lasci il database in esecuzione in modalità di sola lettura, tieni conto anche della 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 inattive. Un numero maggiore di valori causa comunque più passaggi di scansione dell'indice.
Questi e altri flag sono spiegati in maggiore dettaglio in Ottimizzazione, monitoraggio e risoluzione dei problemi delle operazioni VACUUM in PostgreSQL.
Dopo aver impostato questi flag, riavvia il database in modo che autovacuum venga avviato con i nuovi valori.
Puoi utilizzare la vista pg_stat_progress_vacuum per monitorare l'avanzamento delle operazioni VACUUM avviate da autovacuum. Questa vista mostra i VACUUM in esecuzione in tutti i database e per le tabelle (relazioni) di altri database per i quali non puoi cercare il nome della tabella utilizzando la colonna della vista relid.
Per identificare i database e le tabelle che devono essere sottoposti a vacuuming, utilizza le query di Ottimizzazione, monitoraggio e risoluzione dei problemi delle operazioni VACUUM in PostgreSQL. Se la VM del server è abbastanza potente e ha la larghezza di banda per più processi VACUUM paralleli rispetto a quelli avviati da autovacuum, puoi avviare alcuni vacuum manuali.
Controlla la velocità dell'ASPIRAPOLVERE
Questa sezione descrive come controllare la velocità di VACUUM e come accelerarla, se necessario.
Controlla gli autovacuum in esecuzione
Tutti i backend che eseguono VACUUM sono visibili nella vista di sistema pg_stat_progress_vacuum.
Se la fase corrente è 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.
Stima la velocità di scansione di VACUUM
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 di 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 che sono 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.
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 è in esecuzione 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ù sul vacuuming e visualizzare i blog post su internet che discutono alcuni degli aspetti complessi del vacuuming.
Velocizza VACUUM
Il modo più semplice e veloce per velocizzare la scansione di VACUUM è impostare autovacuum_vacuum_cost_delay=0. Puoi farlo dalla consoleCloud de Confiance .
Purtroppo, l'operazione VACUUM già in esecuzione non rileva questo valore e potrebbe essere necessario riavviare il database.
Dopo un 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 esempio, la velocità è aumentata da meno di 300 pagine al secondo a circa 6500 pagine al secondo 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 è così 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 di indicizzazione avviene ogni volta che la memoria viene riempita con puntatori di tuple inattive.
Se il database non viene utilizzato in altro modo, imposta autovacuum_work_mem
in modo da avere circa l'80% di memoria libera dopo aver concesso la quantità richiesta per shared_buffers.
Questo è il limite massimo per ciascuno dei processi VACUUM avviati da autovacuum. Se vuoi continuare a eseguire workload di sola lettura, utilizza meno memoria.
Altri modi per migliorare la velocità
Evita di eseguire il vacuuming degli indici
Per le tabelle di grandi dimensioni, VACUUM impiega 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 subire un wraparound.
In PostgreSQL 12 e 13, puoi eseguire manualmente la seguente istruzione:
VACUUM (INDEX_CLEANUP OFF, TRUNCATE OFF) TABLE_NAME;
Sostituisci TABLE_NAME con il nome della tabella da sottoporre a vacuum.
Nelle versioni 11 e precedenti, puoi DROP l'indice prima di eseguire vacuum e ricrearlo in un secondo momento.
Per eliminare l'indice quando è già in esecuzione un autovacuum su quella tabella, è necessario annullare il vacuum in esecuzione e poi eseguire immediatamente il comando DROP INDEX prima che l'autovacuum riesca ad avviare nuovamente il vacuum su quella tabella.
Innanzitutto, esegui la seguente istruzione per trovare il PID del processo di autovacuum che devi terminare:
SELECT pid, query
FROM pg_stat_activity
WHERE state != 'idle'
AND query ilike '%vacuum%';
Quindi esegui le seguenti istruzioni per terminare l'operazione di vacuum in esecuzione ed eliminare uno o più indici:
SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
SELECT pg_terminate_backend(PID);
DROP INDEX INDEX_1;
DROP INDEX INDEX_2;
Sostituisci quanto segue:
PID: l'ID di processo del processo autovacuum da terminare.INDEX_1eINDEX_2: i nomi degli indici da eliminare.
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 il VACUUM su quella tabella, come mostrato nella sezione precedente.
VACUUM FULL
In rari casi, è più veloce eseguire VACUUM FULL FREEZE, di solito quando la tabella contiene solo una piccola percentuale di tuple attive.
Puoi verificarlo 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ù sull'aspirazione di routine.
Scopri di più sull'aspirazione automatica.
Scopri di più su ottimizzazione, monitoraggio e risoluzione dei problemi delle operazioni VACUUM in PostgreSQL.