Risoluzione dei problemi di wraparound degli ID transazione (TXID)

Questa pagina descrive cosa puoi fare quando il tuo database esegue la protezione da aggiramento dell'ID transazione in PostgreSQL. Si manifesta come un messaggio ERROR, che 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 il 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.
  • 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 qualche altro comando VACUUM manualmente.
  • Scopri altri modi per velocizzare l'aspirapolvere. A volte il modo più rapido è eliminare la tabella o alcuni indici.

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

Trova il database e la tabella che causano il wraparound

Trovare il database

Per scoprire quali database o 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.

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 ti consente di utilizzare le tabelle temporanee VACUUM create in sessioni. A volte, se la sessione è aperta e accessibile, puoi passare la query 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, dopo circa un minuto, l'autovacuum rileva questa modifica e sposta datfrozenxid in avanti in pg_database. Questa operazione risolve lo stato di sola lettura della protezione wraparound.

Tabelle normali

Per le tabelle normali (ovvero non temporanee), continua con i passaggi successivi per verificare se c'è qualcosa che blocca la pulizia, se VACUUM è in esecuzione abbastanza velocemente e se la tabella più importante è sottoposta a VACUUM.

Controllare se è presente un ID transazione bloccato

Uno dei possibili motivi per cui il sistema può esaurire gli ID transazione è che PostgreSQL non può bloccare (ovvero, contrassegnarlo come visibile per tutte le transazioni) qualsiasi ID transazione creato dopo la transazione meno recente attualmente in esecuzione a iniziare. Questo accade a causa delle regole del controllo della contemporaneità multiversione (MVCC). Estrema casi, tali transazioni possono diventare così vecchie da rendere impossibile VACUUM per ripulire eventuali vecchie transazioni per tutti i 2 miliardi il limite di wraparound di ID transazione e provoca l'interruzione dell'accettazione da parte dell'intero sistema con DML. In genere, nel file di log vengono visualizzati anche avvisi, ad esempio WARNING: oldest xmin is far in the past.

Devi passare all'ottimizzazione solo dopo che l'ID transazione è bloccato è stato risolto.

Ecco quattro potenziali motivi per cui potrebbe esserci un ID transazione bloccato, con informazioni su come mitigare ciascuno di questi:

  • Transazioni a lunga esecuzione: identificale e annulla o termina il backend per sbloccare il sottovuoto.
  • Transazioni preparate per dispositivi orfani: esegui il rollback di queste transazioni.
  • Slot di replica abbandonati: elimina gli slot abbandonati.
  • Transazione di lunga durata sulla replica, con hot_standby_feedback = on: identificali e annulla o termina il backend per sbloccare il vacuum.

Per questi scenari, la seguente query restituisce l'età del transazione 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 registrati vicino o meno di un milione di volte rispetto al wrapping. Questo valore è il limite di protezione wraparound quando PostgreSQL smetterà di accettare nuovi comandi di scrittura. In questo caso, consulta Rimuovi i blocchi VACUUM oppure Regola 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 1 milione di protezioni con arrotondamento. In questo caso, devi prima rimuovere i blocchi per VACUUM per poter procedere.

Rimuovi i blocchi VACUUM

Transazioni a lunga esecuzione

Nella query precedente, se oldest_running_xact è uguale a oldest_prepared_xact, poi vai alla sezione di preparazione della transazione orfana, perché l'ultima versione in esecuzione include anche le transazioni preparate.

Potresti prima dover eseguire questo comando come utente postgres: 

GRANT pg_signal_backend TO postgres;

Se la transazione in questione appartiene a uno degli utenti del sistema (a partire da cloudsql...), non puoi annullarlo direttamente. Per annullarlo, devi riavviare il database.

Per identificare una query di lunga durata e annullarla o terminarla per sbloccare il vuoto, seleziona innanzitutto 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 puoi ignorato.

Annulla le query in cui xids_left_to_wraparound è che 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 è pid della query precedente

Preparare le transazioni 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;

Vedi il ROLLBACK SQL PREPARATO documentazione per una spiegazione esaustiva.

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 da gcloud o dalla console Google Cloud.

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 visualizzazione del 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 ai 2 miliardi di persone, dovresti eliminare slot machine. Non è facile sapere quale replica sia nel caso in cui la query restituisce più record. Controllali tutti nel caso in cui sia presente un su qualsiasi 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 in pg_stat_replication ha la meno recente TXID necessaria nella replica.

Per avanzare, interrompi la query che lo trattiene sulla replica. A per scoprire quale query lo sta bloccando, usala Transazioni di lunga durata, ma questa volta, eseguilo sulla replica.

Un'altra opzione è riavviare la replica.

Configura VACUUM

Imposta i seguenti due flag:

  • autovacuum_vacuum_cost_delay = 0
  • memoria_lavoro_autovacuum = 1048576

Il primo disabilita qualsiasi limitazione dei dischi per l'aspirapolvere da parte di PostgreSQL, quindi VACUUM possono essere eseguite alla massima velocità. Per impostazione predefinita, la funzionalità di pulizia automatica viene limitata in modo da non utilizzare tutta 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 eliminate in una tabella che verrà ripulita da VACUUM. Quando imposti questo valore, considera che questa è la quantità massima di memoria locale ogni volta che esegue VACUUM da allocare. Assicurati di non consentire più di quanto disponibile, con un po' di 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.

Sulla maggior parte dei sistemi, utilizza il valore massimo (1 GB o 1048576 kB, come mostrato nell'esempio). Questo valore corrisponde a circa 178 milioni di tuple morte. Un numero maggiore provoca comunque più passaggi di scansione dell'indice.

Questi e altri flag sono spiegati più dettagliatamente in 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 degli comandi VACUUM avviati da autovacuum. Questa vista mostra i comandi 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, usa le query Ottimizzazione, monitoraggio e risoluzione dei problemi delle operazioni VACUUM in PostgreSQL Se la VM server abbastanza potente e ha la larghezza di banda per più processi VACUUM paralleli rispetto a avviato automaticamente, puoi avviare degli aspirapolvere manuali.

Controlla la velocità dell'aspirapolvere

Questa sezione descrive come controllare la velocità del VACUUM e come accelerare se necessario.

Controlla gli aspirapolvere in funzione

Tutti i backend che eseguono VACUUM sono visibili nella vista del sistema pg_stat_progress_vacuum.

Se la fase corrente è scanning heap, puoi monitorare lo stato di avanzamento osservando le modifiche nella colonna heap_blks_scanned. Sfortunatamente, non è facile determinare la velocità di scansione in altre fasi.

Stima la velocità di scansione del VACUUM

Per stimare la velocità di scansione, devi prima archiviare i valori di base e Calcolare la variazione nel tempo per stimare il tempo di completamento. Innanzitutto, devi avere per 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 che sono già in modalità di a capo, 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, 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 c'è un solo autovacuum in esecuzione questo database, con più di una riga per database. Condizioni di filtro aggiuntive ('AND relid= …'') deve essere aggiunto a WHERE per indicare un singolo 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 base salvati e calcola pages_per_second e remaining_time, che ci consentono di decidere se VACUUM funziona abbastanza velocemente o se vogliamo velocizzarlo. La Il valore remaining_time riguarda solo la fase scanning heap. Anche altre fasi richiedono tempo, a volte anche di più. Puoi scoprire di più sull'aspirapolvere e visualizzare post di blog su internet che trattano alcuni degli aspetti complessi dell'aspirapolvere.

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, il comando 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 <300 pagine/sec a circa 6500 pagine/sec e il tempo rimanente previsto per la fase di scansione dell'heap è diminuito da 9 ore ai 23 minuti.

La velocità di scansione delle altre fasi non è così facile da misurare, ma dovrebbe mostrare un aumento della velocità simile.

Inoltre, valuta la possibilità di aumentare le dimensioni di autovacuum_work_mem evitare più passaggi sugli indici. Viene superato ogni volta che la memoria è piena di puntatori a tuple non attivi.

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 consentito la quantità richiesta per shared_buffers. Questo è il limite massimo 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à

Evita la pulizia degli indici

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

PostgreSQL 14 dispone di ottimizzazioni speciali per evitare la pulizia degli indici se il sistema rischia di aggirare il problema.

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.

Per eliminare l'indice quando un autovacuum è già in esecuzione su quella tabella è necessario annullando il vacuum in esecuzione ed eseguendo immediatamente il comando drop index prima che l'autovacuum riavvii l'aspirapolvere su quella 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 processo di sottovuoto 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>; ...

Elimina la tabella con problemi

In alcuni rari casi, puoi abbandonare 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 ancora usare cloudsql.enable_maintenance_mode = 'on' e probabilmente termina anche la VACUUM sulla tabella come mostrato nella sezione precedente.

VACUUM FULL

In rari casi, è più veloce eseguire VACUUM FULL FREEZE, solitamente quando la tabella contiene solo una piccola proporzione di tuple attive. Puoi controllare questa opzione dalla visualizzazione pg_stat_user_tables (a meno che non sia presente una arresto anomalo che ha cancellato le statistiche).

Il comando VACUUM FULL copia le tuple in tempo reale in un nuovo file, quindi deve essere disponibile spazio sufficiente per il nuovo file e i relativi indici.

Passaggi successivi