Questa pagina descrive cosa puoi fare quando il database viene eseguito nella protezione Wraparound 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 wraparound.
- Controlla se sono presenti elementi che rallentano (AUTO)VACUUM (ad esempio un ID transazione bloccato).
- Misura la velocità di AUTOVACUUM. Se è lento, puoi provare ad aumentarlo.
- Se necessario, esegui alcuni altri comandi VACUUM manualmente.
- Cerca altri modi per accelerare l'aspirapolvere. A volte il modo più rapido è eliminare la tabella o alcuni indici.
Molti dei suggerimenti per i valori dei flag sono volutamente non esatti perché dipendono da molti parametri del database. Leggi i documenti ai link in fondo a questa pagina per un'analisi più approfondita dell'argomento.
Trova il database e la tabella che causano il wrapping
Individuazione del database
Per scoprire quale database o quali database contengono le tabelle che causano il wraparound, esegui la query seguente:
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 query seguente:
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 VACUUM creare tabelle temporanee create in altre sessioni. A volte, se la sessione è aperta e accessibile, è possibile svuotare il tavolo lì, 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 è stato l'unico blocco, tra circa un minuto l'aspirapolvere rileva la modifica e sposta datfrozenxid
in avanti in pg_database
. In questo modo viene risolto lo stato di sola lettura della protezione wraparound.
Tabelle normali
Per le tabelle normali (ovvero non temporanee), continua con i passaggi successivi di seguito per verificare se qualcosa blocca la pulizia, se VACUUM è in esecuzione abbastanza veloce e se viene aspirato l'aspirapolvere della tabella più 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ò freeze (vale a dire, contrassegnare come visibili a tutte le transazioni) gli ID transazione creati dopo l'avvio della transazione attualmente in esecuzione meno recente. Questo è dovuto alle regole MVCC (Multiversion controllo della contemporaneità). In casi estremi, queste transazioni possono diventare così obsolete da impedire a VACUUM di eliminare le vecchie transazioni 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 vengono visualizzati anche avvisi nel file di log, 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.
Di seguito sono riportati quattro potenziali motivi per cui potrebbe essere presente un ID transazione bloccato, con informazioni su come risolverli:
- 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
: identificali e annulla o termina il backend per sbloccare l'aspirapolvere.
In questi scenari, la seguente query restituisce l'età della transazione meno recente e il numero di transazioni rimaste fino al completamento:
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 quasi o meno di 1 milione di distanza dal wraparound. Questo valore è il limite di protezione wraparound quando PostgreSQL smette di accettare nuovi comandi di scrittura. In questo caso, consulta Rimuovere i blocchi del VACUUM o Tune 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 protezione perimetrale. In questo caso, devi prima rimuovere i blocchi per consentire al VACUUM di procedere.
Rimuovi i blocchi del VACUUM
Transazioni a lunga esecuzione
Nella query precedente, se oldest_running_xact
è uguale a
oldest_prepared_xact
, vai alla sezione
Preparazione della transazione per le persone orfane, perché il valore più recente in 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 annullarla direttamente. Devi riavviare il database per annullarlo.
Per identificare una query a lunga esecuzione e annullarla o terminarla per sbloccare l'aspirapolvere, seleziona prima alcune delle query meno recenti. La riga LIMIT 10
aiuta ad adattare il risultato sullo schermo. Potrebbe essere necessario 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 che puoi ignorare
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 relativa al ROLLBACK SQL.
Slot di replica abbandonati
Nel caso in cui lo slot di replica venga abbandonato perché la replica esistente è stata arrestata, messa in pausa o presenta altri problemi, 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 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 di pg_replication_slots
è integro (età == 59).
Se l'età era vicina ai 2 miliardi, sarebbe opportuno eliminare l'area annuncio. Non esiste un modo semplice per capire quale replica sia e quale nel caso in cui la query restituisca più record. Quindi, controllali tutti nel caso in cui ci sia una transazione
a lunga esecuzione su qualsiasi replica.
Transazioni a lunga esecuzione sulle repliche
Controlla le repliche della 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 valore TXID
meno recente necessario nella replica.
Per andare avanti, interrompi la query che lo blocca sulla replica. Per scoprire quale query lo trattiene, utilizza la query in Transazioni a lunga esecuzione, ma questa volta eseguila 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 disattiva qualsiasi limitazione del disco per l'aspirapolvere da parte di PostgreSQL in modo che VACUUM possa essere eseguito alla massima velocità. Per impostazione predefinita, la funzionalità Autovacuum è limitata, quindi non utilizza tutti gli I/O del disco sui server più lenti.
Il secondo flag, autovacuum_work_mem
, diminuisce il numero di passaggi di pulizia dell'indice. Se possibile, deve essere abbastanza grande da archiviare tutti gli ID di righe non recapitabili 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 riservata. Se lasci il database in esecuzione in modalità di sola lettura, prendi in considerazione anche la memoria locale utilizzata per le query di sola lettura.
Sulla maggior parte dei sistemi, utilizzare il valore massimo (1 GB o 1048576 kB, come mostrato nell'esempio). Questo valore rientra fino a circa 178 milioni di tuple morte. Ogni altro numero comporta 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 inizi con i nuovi valori.
Puoi utilizzare la vista pg_stat_progress_vacuum
per monitorare l'avanzamento degli
aspirapolvere con aspirapolvere automatico. Questa visualizzazione mostra gli VACUUM in esecuzione in tutti i database e, per le tabelle (relazioni) di altri database, in cui non è possibile cercare il nome della tabella utilizzando la colonna di visualizzazione relid
.
Per identificare i database e le tabelle che richiedono il successivo svuotamento, utilizza le query da Ottimizzare, monitorare e risolvere i problemi delle operazioni VACUUM in PostgreSQL. Se la VM server è abbastanza potente e ha la larghezza di banda necessaria per processi VACUUM più paralleli rispetto a quelli avviati dall'aspirapolvere automatico, puoi avviare alcune aspirapolvere manuali.
Controlla velocità VACUUM
Questa sezione descrive come controllare la velocità del VACUUM e come accelerarla, se necessario.
Controlla gli aspirapolvere in funzione
Tutti i backend che eseguono VACUUM sono visibili nella vista 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 è possibile determinare facilmente la velocità di scansione in altre fasi.
Stima la velocità di scansione VACUUM
Per stimare la velocità di scansione, devi prima archiviare 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 nulla nelle tabelle già presenti nel wrapping, 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 prossima query, li utilizziamo come base di confronto per determinare la velocità e il tempo di completamento stimato.
NOTA: WHERE datname = DB_NAME
limita l'indagine a un database alla volta. Questo numero è sufficiente se nel database è in esecuzione una sola autovacuum, con più di una riga per database. Condizioni di filtro extra
('AND relid= …'')
devono essere aggiunte 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 funziona abbastanza velocemente o se vogliamo aumentarlo. Il valore remaining_time
è solo per la fase scanning heap
.
Anche altre fasi richiedono tempo, a volte anche di più. Puoi leggere ulteriori informazioni sull'aspirapolvere
e leggere i post dei blog su internet che trattano alcuni degli aspetti
complessi dell'aspirapolvere.
Velocizza 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.
Sfortunatamente, l'oggetto VACUUM già in esecuzione non acquisisce questo valore e potrebbe essere necessario riavviare il database.
Dopo il 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 heap è diminuito da 9 ore a 23 minuti.
La velocità di scansione delle altre fasi non è così facile da misurare, ma dovrebbero mostrare una velocità simile.
Inoltre, valuta la possibilità di utilizzare le dimensioni di autovacuum_work_mem
più grandi possibile per evitare più passaggi sugli indici. Ogni volta che la memoria
è piena di puntatori a tuple non funzionanti, si verifica un passaggio dell'indice.
Se il database non viene utilizzato in altro modo, imposta autovacuum_work_mem
in modo che abbia 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 dall'aspirapolvere. Se vuoi continuare a eseguire carichi di lavoro di sola lettura, utilizza meno memoria.
Altri modi per migliorare la velocità
Evita di passare l'aspirapolvere con gli indici
Per le tabelle di grandi dimensioni, VACUUM dedica la maggior parte del tempo a eliminare gli indici.
PostgreSQL 14 dispone di ottimizzazioni speciali per evitare la pulizia dell'indice se il sistema è a rischio di wrapping.
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 attivare vacuum e ricrearlo in un secondo momento.
Per eliminare l'indice quando è già in esecuzione un'aspirapolvere nella tabella, è necessario annullare l'aspirapolvere in esecuzione e quindi eseguire immediatamente il comando dell'indice di caduta prima che l'aspirapolvere automatico riesca ad avviare di nuovo 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 istruzioni seguenti per terminare la vacuum in esecuzione e rilasciare 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 in questione
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 terminare anche il VACUUM su quella tabella, come mostrato nella sezione precedente.
Aspirapolvere COMPLETO
In rari casi, l'esecuzione di VACUUM FULL FREEZE
è più veloce, di solito quando la tabella ha solo una piccola percentuale di tuple attive.
Questa opzione può essere verificata 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, in modo che sia disponibile spazio sufficiente per il nuovo file e i relativi 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 come ottimizzare, monitorare e risolvere i problemi delle operazioni VACUUM in PostgreSQL