Questa pagina descrive cosa puoi fare quando il tuo database esegue la protezione anti-wraparound 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 wrapping.
- Controlla se c'è qualcosa che trattiene il passaggio (AUTO)VACUUM (ad esempio, un ID transazione bloccato).
- Misura la velocità di AUTOVACUUM. Se è lenta, puoi provare ad accelerarla.
- 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 relativi ai 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 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 problema è il database con il valore remaining
vicino a 0.
Trovare la tabella
Connettiti a quel 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 ti consente di utilizzare le tabelle temporanee VACUUM create in
sessioni. A volte, se la sessione è aperta e accessibile, puoi passare la schermata
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, tra circa un minuto l'autovacuum seleziona
su questa modifica e sposta il datfrozenxid
in avanti in pg_database
. Questa operazione risolve
lo stato di sola lettura della protezione wraparound.
Tabelle normali
Per le tabelle normali (non temporanee), continua con i passaggi successivi di seguito per verificare se sono presenti bloccare la pulizia, se la VACUUM funziona abbastanza velocemente e la pulizia di una tabella importante.
Verifica la presenza di 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 comporterà 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 un ID transazione potrebbe essere bloccato. informazioni su come mitigarne l'impatto:
- Transazioni di lunga durata: identificale e annulla o termina le per sbloccare l'aspirapolvere.
- 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 segnalati come vicini o inferiori a a 1 milione di distanza dal wraparound. Questo valore è il limite di protezione wraparound quando PostgreSQL smetterà 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
sono all'interno di 1
e il limite di protezione wraparound. In questo caso, devi prima rimuovere i blocchi per
VACUUM per poter procedere.
Rimuovi i blocchi VACUUM
Transazioni di lunga durata
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 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 a lunga esecuzione e annullarla o terminarla per sbloccare
aspirapolvere, seleziona prima alcune delle query meno recenti. La linea LIMIT 10
consente
per adattare il risultato sullo schermo. Potrebbe essere necessario ripetere il passaggio una volta 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 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, dovrai
per 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 di
utilizzando il valore gid
dell'ultima query (in questo caso, trx_id_pin
)
l'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 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 è ancora rimane alta, elimina la replica
Gli slot di replica sono visibili nell'pg_replication_slots
vista di sistema.
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 esiste un modo semplice per sapere quale sia la replica nel caso in cui la query recuperi più record. Controllali tutti nel caso in cui ci sia un'istanza
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 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 due flag seguenti:
- autovacuum_vacuum_cost_delay = 0
- autovacuum_work_mem = 1048576
Il primo disabilita qualsiasi limitazione del disco per l'aspirapolvere da parte di PostgreSQL, quindi VACUUM possono essere eseguite alla massima velocità. Per impostazione predefinita, la funzionalità autovacuum è limitata per non consumare a tutte le I/O del disco sui server più lenti.
Il secondo flag, autovacuum_work_mem
, riduce il numero di indici
pass di pulizia. 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, 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
altre sono rimaste di riserva. Se lasci il database in esecuzione in modalità di sola lettura:
considera anche la 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 può contenere fino a circa 178 milioni di tuple non valide. Qualsiasi altra causa comunque 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 venga avviato con i nuovi valori.
Puoi utilizzare la vista pg_stat_progress_vacuum
per monitorare l'avanzamento di
VACUUM avviate con autovacuum. Questa visualizzazione mostra le VACUUM in esecuzione in tutti
e tabelle (relazioni) di altri database che non puoi cercare
il nome della tabella utilizzando la colonna della vista relid
.
Per identificare i database e le tabelle che richiedono l'operazione VACUUM successiva, utilizza le query riportate in Ottimizzazione, monitoraggio e risoluzione dei problemi delle operazioni VACUUM in PostgreSQL. Se la VM del server è sufficientemente potente e dispone della larghezza di banda per più processi VACUUM in parallelo rispetto a quelli avviati da autovacuum, puoi avviare alcuni vuoti manuali.
Controlla la velocità del VACUUM
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 attuale è scanning heap
, puoi monitorare
l'avanzamento esaminando 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 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 è possibile salvare elementi nelle tabelle già in wraparound, utilizza
set_config(flag, value)
per impostare due flag definiti dall'utente: save.ts
e save.heap_blks_scanned
- ai valori correnti da
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 uno
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. Questo è vero anche per la prossima query.
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 correnti 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 le altre fasi richiedono tempo, a volte anche di più. Puoi scoprire di più sull'aspirapolvere
e visualizzare post del blog su internet per discutere di alcuni aspetti
gli aspetti del vuoto.
Accelera VACUUM
Il modo più semplice e veloce per velocizzare la scansione VACUUM è impostare
autovacuum_vacuum_cost_delay=0
. Questa operazione può essere eseguita
nella console Google Cloud.
Purtroppo il sistema VACUUM già in esecuzione non rileva questo valore e tu potrebbe essere necessario riavviare il database.
Dopo un riavvio, potresti vedere 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 dovrebbero una 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 altrimenti, imposta autovacuum_work_mem
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 tavoli molto grandi, VACUUM dedica la maggior parte del tempo alla pulizia degli indici.
PostgreSQL 14 ha ottimizzazioni speciali per evitare la pulizia dell'indice se il sistema è a rischio di avvolgente.
In PostgreSQL 12 e 13, puoi eseguire manualmente la seguente istruzione:
VACUUM (INDEX_CLEANUP OFF, TRUNCATE OFF) <tablename>;
Nella versione 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 un semplice da ripristinare da un'altra origine, come un backup o un'altra per configurare un database.
Devi ancora usare cloudsql.enable_maintenance_mode = 'on'
e probabilmente
termina anche VACUUM sulla tabella, come mostrato nella sezione precedente.
VACUUM FULL
In rari casi, è più veloce eseguire VACUUM FULL FREEZE
,
di solito quando la tabella ha solo una piccola percentuale di tuple attive.
Questo valore può essere controllato dalla visualizzazione pg_stat_user_tables
(a meno che non si sia verificato un
crash che abbia eliminato le statistiche).
Il comando VACUUM FULL
copia le tuple attive in un nuovo file, quindi spazio sufficiente
deve essere disponibile per il nuovo file e i suoi indici.
Passaggi successivi
- Scopri di più su VACUUM for wraparound
- Scopri di più sull'aspirapolvere di routine.
- Scopri di più sull'aspirapolvere automatico
- Scopri di più su ottimizzazione, monitoraggio e risoluzione dei problemi delle operazioni VACUUM in PostgreSQL