Risoluzione dei problemi di wraparound degli ID transazione (TXID)

Questa pagina descrive cosa puoi fare quando il tuo database riscontra la protezione dal wraparound dell'ID transazione in PostgreSQL. Si manifesta come un 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 wraparound.
  • Verifica se c'è qualcosa che blocca (AUTO)VACUUM (ad esempio, un ID transazione bloccato).
  • Misura la velocità di AUTOVACUUM. Se è lento, puoi provare a velocizzarlo.
  • Se necessario, esegui manualmente alcuni comandi VACUUM.
  • Esplora altri modi per velocizzare il vacuum. A volte il modo più veloce è eliminare la tabella o alcuni indici.

Molti dei consigli per i valori dei flag non sono volutamente esatti perché dipendono da molti parametri del database. Per un'analisi più approfondita dell'argomento, leggi i documenti collegati alla fine di questa pagina.

Trovare il database e la tabella che causano il wraparound

Trovare il database

Per scoprire 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 al 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 TEMPORARY

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 l'autovacuum rileva questa modifica e sposta datfrozenxid in avanti in pg_database. In questo modo si risolve lo stato di sola lettura della protezione dal wraparound.

Tabelle normali

Per le tabelle normali (ovvero non temporanee), continua con i passaggi successivi per verificare se qualcosa blocca la pulizia, se VACUUM viene eseguito abbastanza velocemente e se viene eseguito il vacuum della tabella più importante.

Verificare la presenza di 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'inizio della transazione più vecchia attualmente in esecuzione. Ciò è dovuto alle regole di controllo della contemporaneità multiversione (MVCC). In casi estremi, queste transazioni possono diventare così vecchie da rendere impossibile a VACUUM liberare spazio dalle 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.

Devi passare all'ottimizzazione solo dopo aver risolto il problema dell'ID transazione bloccato.

Ecco quattro possibili motivi per cui potrebbe esserci un ID transazione bloccato, con informazioni su come mitigare ognuno di essi:

  • 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: identificale 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 dei valori *_left segnalati vicino o inferiore a 1 milione di distanza dal wraparound. Questo valore è il limite di protezione dal wraparound quando PostgreSQL smette di accettare nuovi comandi di scrittura. In questo caso, consulta Rimuovere i blocchi di 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 dal wraparound di 1 milione. In questo caso, devi prima rimuovere i blocchi per poter procedere con VACUUM.

Rimuovere i blocchi di 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 che causa il problema 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 più vecchie. La riga LIMIT 10 consente di adattare il risultato allo schermo. Potresti dover ripetere questa operazione dopo aver risolto le query in esecuzione più vecchie.

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 avvicina 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 più vecchie 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 di SQL ROLLBACK PREPARED.

Slot di replica abbandonati

Se lo slot di replica viene abbandonato perché la replica esistente è arrestata, messa in pausa o presenta un altro problema, puoi eliminare la replica da gcloud o dalla Google Cloud console.

Innanzitutto, verifica che la replica non sia disattivata come descritto in Gestire le repliche di lettura. Se la replica è disattivata, 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à fosse vicina a 2 miliardi, dovresti eliminare lo slot. Non è facile sapere quale replica è quale se la query restituisce più record. Controllali tutti nel caso in cui sia presente una transazione a lunga esecuzione su una replica.

Transazioni a lunga esecuzione sulle repliche

Controlla le repliche per la transazione in esecuzione più vecchia con hot_standby_feedback impostato su on e disattivalo sulla replica.

La colonna backend_xmin nella visualizzazione pg_stat_replication contiene il TXID più vecchio necessario sulla replica.

Per spostarlo in avanti, arresta la query che lo blocca sulla replica. Per scoprire quale query lo blocca, utilizza la query in Transazioni a lunga esecuzione, ma questa volta eseguila sulla replica.

Un'altra opzione è riavviare la replica.

Configurare VACUUM

Imposta i seguenti due flag:

  • autovacuum_vacuum_cost_delay = 0
  • autovacuum_work_mem = 1048576

Il primo disabilita qualsiasi limitazione del disco per il vacuum da parte di PostgreSQL, in modo che VACUUM possa essere eseguito alla massima velocità. Per impostazione predefinita, l'autovacuum è 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 memorizzare tutti gli ID delle righe non attive 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 più di quanto è disponibile, con una parte 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 non attive. Qualsiasi altro valore causa comunque più passaggi di scansione dell'indice.

Questi e altri flag sono spiegati in modo più dettagliato in Ottimizzazione, monitoraggio e risoluzione dei problemi delle operazioni VACUUM in PostgreSQL.

Dopo aver impostato questi flag, riavvia il database in modo che l'autovacuum inizi con i nuovi valori.

Puoi utilizzare la visualizzazione pg_stat_progress_vacuum per monitorare l'avanzamento dei VACUUM avviati da autovacuum. Questa visualizzazione mostra i VACUUM in esecuzione in tutti i database e per le tabelle (relazioni) di altri database di cui non puoi cercare il nome della tabella utilizzando la colonna della visualizzazione relid.

Per identificare i database e le tabelle che richiedono il vacuum successivo, utilizza le query in 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 di quelli avviati da autovacuum, puoi avviare alcuni vacuum manuali.

Verificare la velocità di VACUUM

Questa sezione descrive come verificare la velocità di VACUUM e come accelerarla, se necessario.

Verificare gli autovacuum in esecuzione

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 è facile determinare la velocità di scansione in altre fasi.

Stimare 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 già in wraparound, utilizza set_config(flag, value) per impostare due flag definiti dall'utente, save.ts e save.heap_blks_scanned, sui valori attuali di pg_stat_progress_vacuum.

Nella query successiva, li utilizziamo 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. È necessario aggiungere condizioni di filtro aggiuntive ('AND relid= …'') a WHERE per indicare una singola riga di autovacuum. Questo vale anche 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 leggere ulteriori informazioni sul vacuum e visualizzare i post del blog su internet che trattano alcuni degli aspetti complessi del vacuum.

Accelerare VACUUM

Il modo più semplice e veloce per velocizzare la scansione di VACUUM è impostare autovacuum_vacuum_cost_delay=0. Questa operazione può essere eseguita dalla Google Cloud console.

Purtroppo, il 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 esempio, 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 è così facile da misurare, ma dovrebbe mostrare un'accelerazione simile.

Valuta anche la possibilità di impostare autovacuum_work_mem il più grande 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 attive.

Se il database non viene utilizzato in altro modo, imposta autovacuum_work_mem in modo che la memoria libera sia pari a 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 il vacuum degli indici

Per le tabelle di grandi dimensioni, VACUUM trascorre la maggior parte del tempo a pulire gli indici.

PostgreSQL 14 ha ottimizzazioni speciali per evitare la pulizia dell'indice se il sistema è a rischio di wraparound.

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 il vacuum e ricrearlo in un secondo momento.

L'eliminazione dell'indice quando è già in esecuzione un autovacuum sulla tabella richiede l'annullamento del vacuum in esecuzione e l'esecuzione immediata del comando di eliminazione dell'indice prima che l'autovacuum riesca ad avviare di nuovo il vacuum sulla tabella.

Innanzitutto, esegui la seguente istruzione per trovare il PID del processo 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 il 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 <index1>;DROP INDEX <index2>; ...

Eliminare la tabella che causa il problema

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 sulla tabella come mostrato nella sezione precedente.

VACUUM FULL

In rari casi, è più veloce eseguire VACUUM FULL FREEZE, in genere quando la tabella ha solo una piccola parte 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