Come posso risolvere i problemi di wraparound degli ID delle transazioni in Aurora compatibile con PostgreSQL?

Risoluzione

Monitora le metriche e gli allarmi in CloudWatch per prevenire problemi

Per monitorare la datazione delle transazioni non eliminate più obsolete, controlla la metrica MaximumUsedTransactionIDs in Amazon CloudWatch. Per evitare problemi di wrapround dell'ID della transazione, crea un allarme in CloudWatch. Per ulteriori informazioni, consulta Implement an Early Warning System for Transaction ID Wraparound in Amazon RDS for PostgreSQL (Attuazione di un sistema di allerta precoce per il wraparound degli ID delle transazioni in Amazon Relational Database Service (Amazon RDS) per PostgreSQL).

Esegui query diagnostiche

Utilizza psql o pgAdmin per connetterti all'istanza database PostgreSQL.

Per verificare la datazione del database, esegui questo comando:

SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY 2 DESC
LIMIT 20;

Nota: se l'output mostra che il database rdsadmin o template0 ha l'ID della transazione meno recente, contatta il Supporto AWS.

Per connetterti al database più obsoleto per identificare potenziali problemi con le tabelle, esegui questo comando:

SELECT
c.relnamespace::regnamespace as schema_name,
c.oid::regclass as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age,
pg_size_pretty(pg_table_size(c.oid)) as table_size,
t.relkind, t.relpersistence
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY 3 DESC
LIMIT 20;

Per rivedere la cronologia delle operazioni autovacuum delle tabelle obsolete, esegui questo comando:

SELECT
  relname,
  n_live_tup, n_tup_upd, n_tup_del, n_dead_tup,
  last_vacuum, last_autovacuum,
  last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname='table_name';

Nota: sostituisci table_name con il nome della tua tabella.

Per monitorare le operazioni vacuum attive, esegui questo comando:

SELECT datname, usename, pid, state, wait_event,
current_timestamp - xact_start AS xact_runtime, query
FROM pg_stat_activity
WHERE upper(query) LIKE '%VACUUM%'
AND pid <> pg_backend_pid()
ORDER BY xact_start;

Risolvi i problemi derivanti da blocchi dei processi di autovacuum

Se utilizzi una delle seguenti versioni di Aurora compatibile con PostgreSQL, consulta Identificazione e risoluzione dei blocchi per i processi di vacuum aggressivi in Aurora PostgreSQL:

• 13.19 o successiva
• 14.16 o successiva
• 15.11 o successiva
• 16.7 o successiva
• 17.2 o successiva

Risolvi i problemi derivanti da transazioni lunghe

Le transazioni lunghe o le sessioni in transizione inattive possono bloccare un'operazione autovacuum.

Nei log degli errori di PostgreSQL ricevi il seguente messaggio di avviso:

"WARNING: oldest xmin is far in the past HINT: Close open transactions soon to avoid wraparound problems."

Per identificare le transazioni che bloccano l'operazione autovacuum, esegui questo comando:

SELECT pid,
    age(backend_xid),
    substr(query,1,20),
    extract(epoch from now()) - extract(epoch from xact_start) as duration,
    state
FROM pg_stat_activity
WHERE xact_start is not null
AND age(backend_xid) is not null
ORDER BY 2 DESC
LIMIT 10;

Per risolvere il problema, puoi annullare le query attive o terminare le connessioni.

Per annullare le query attive, esegui questo comando:

SELECT pg_cancel_backend(pid);

Nota: sostituisci pid con l'ID del processo ottenuto dai risultati della query.

Per terminare le connessioni, esegui questo comando:

SELECT pg_terminate_backend(pid);

Nota: sostituisci pid con l'ID del processo ottenuto dai risultati della query.

Per assicurarti che l'operazione autovacuum non sia bloccata nuovamente da transazioni lunghe, imposta i parametri statement_timeout, idle_in_transaction_session_timeout e log_min_duration_statement.

Risolvi i problemi derivanti da slot di replica logica inattivi

Per identificare gli slot inattivi, esegui questo comando:

SELECT slot_name, slot_type, database, xmin, catalog_xmin,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag,
    active
FROM pg_replication_slots
ORDER BY age(xmin), age(catalog_xmin) DESC;

Attenzione: prima di eliminare uno slot di replica, verifica che non abbia una replica in corso, sia inattivo e sia in uno stato irrecuperabile. Se perdi uno slot ancora in fase di replica, attivo e ripristinabile, potresti interrompere la replica o causare una perdita di dati.

Per eliminare gli slot inutilizzati, esegui questo comando:

SELECT pg_drop_replication_slot('slot_name');

Nota: sostituisci slot_name con il nome del tuo slot di replica.

Risolvi i problemi delle istanze di lettura

Per identificare le istanze di lettura in un cluster Aurora che potrebbero impedire il feedback in hot standby e bloccare l'operazione autovacuum, esegui questa query:

select server_id, feedback_epoch, feedback_xmin
from aurora_replica_status()
WHERE (feedback_xmin IS NOT NULL AND feedback_xmin > 0)

Esegui questa query per le istanze di lettura che impediscono il feedback in hot standby nei cluster Global Database:

select server_id,aws_region,feedback_epoch,feedback_xmin
from aurora_global_db_instance_status ()
WHERE (feedback_xmin IS NOT NULL AND feedback_xmin > 0);

Esegui questa query nelle istanze di lettura con il valore combinato più basso di feedback_epoch e feedback_xmin:

SELECT pid,
    age(backend_xid),
    substr(query,1,20),
    extract(epoch from now()) - extract(epoch from xact_start) as duration,
    state
FROM pg_stat_activity WHERE backend_xmin::text::bigint =feedback_xmin

Nota: sostituisci feedback_xmin con il valore feedback_xmin ottenuto dai risultati della query precedente.

Se puoi farlo in sicurezza, utilizza pg_terminate_backend(pid) per terminare le transazioni lunghe.

Risolvi i problemi derivanti da transazioni preparate non utilizzate

Per identificare le transazioni preparate, esegui questo comando:

SELECT database, gid, prepared, owner, database, transaction::text::bigint as xid,
now() - prepared AS time_since_prepared
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;

Per risolvere i problemi derivanti da una transazione preparata, puoi eseguire il commit o il rollback della transazione preparata.

Per il commit della transazione preparata, esegui questo comando:

COMMIT PREPARED 'gid'

Nota: sostituisci gid con l'ID transazione globale ottenuto dai risultati della query.

Attenzione: prima di eseguire il rollback di una transazione preparata, assicurati che la transazione non faccia parte di una sequenza di transazioni distribuite.

Per il rollback della transazione preparata, esegui questo comando:

ROLLBACK PREPARED 'gid'

Nota: sostituisci gid con l'ID transazione globale ottenuto dai risultati della query.

Risolvi i problemi derivanti da tabelle temporanee orfane

Quando il processo di autovacuum di PostgreSQL incontra tabelle temporanee orfane, registra il seguente evento:

LOG: autovacuum: found orphan temp table "%s"."%s" in database "%s"

Per identificare le tabelle temporanee orfane, esegui questo comando:

SELECT
  oid,
  relname,
  relnamespace::regnamespace,
  age(relfrozenxid)
FROM pg_class
WHERE relpersistence = 't'
ORDER BY age(relfrozenxid) DESC;

Per eliminare una tabella temporanea, esegui questo comando:

DROP TABLE temporary_table

Nota: sostituisci temporary_table con il nome della tua tabella temporanea.

Risolvi i problemi derivanti dalle prestazioni dell'operazione vacuum

Quando l'operazione autovacuum è lenta, le operazioni di congelamento potrebbero subire ritardi.

Per risolvere il problema, aumenta prima di tutto il parametro autovacuum_max_workers per più operazioni vaccum concorrenti. Quindi utilizza la seguente formula per aumentare allo stesso modo il parametro autovacuum_vacuum_cost_limit:

cost_limit del singolo worker = autovacuum_vacuum_cost_limit / autovacuum_max_workers

Se le prestazioni dell'operazione vacuum rimangono insufficienti dopo aver aumentato i parametri, aggiorna l'istanza Amazon RDS a una classe con più vCPU. Puoi modificare il parametro autovacuum_vacuum_cost_delay a livello di tabella per velocizzare le operazioni di autovacuum.

Per modificare il parametro autovacuum_vacuum_cost_delay a livello di tabella, esegui questo comando:

ALTER TABLE mytable SET ( autovacuum_vacuum_cost_delay = value, autovacuum_vacuum_cost_limit = value );

Nota: sostituisci mytable con il nome della tua tabella e value con i valori desiderati per i tuoi parametri.

Prestazioni dell'operazione vacuum in caso di indici di grandi dimensioni

Quando esegui un'operazione vacuum su indici di grandi dimensioni, i vincoli di memoria possono forzare più passaggi sullo stesso indice e aumentare significativamente la durata VACUUM totale.

Se riscontri operazioni VACUUM lente su indici di grandi dimensioni in PostgreSQL 16 o versioni precedenti, verifica le seguenti cause potenziali:

• La dimensione dell'indice supera 1 GB.
• Si verificano più passaggi VACUUM. Per visualizzare il numero di passaggi, esegui una query su index_vacuum_count dalla vista pg_stat_progress_vacuum.
• Hai configurato in modo errato maintenance_work_mem o autovacuum_work_mem.

Per risolvere il problema, intraprendi le seguenti azioni:

• Elimina gli indici inutilizzati o duplicati.
• Aumenta maintenance_work_mem o autovacuum_work_mem. Affinché la nuova impostazione della memoria abbia effetto, interrompi i processi che attualmente eseguono autovacuum
• Esegui manualmente un'operazione VACUUM FREEZE e aumenta maintenance_work_mem all'interno della sessione.

Se utilizzi PostgreSQL 12 o versione successiva, esegui questo comando per esaminare potenziali operazioni vacuum manuali con esclusioni dell'indice:

VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) table_name;

Nota: sostituisci table_name con il nome della tua tabella.

Incoerenza logica

Se un indice logicamente incoerente blocca il processo di autovacuum, potresti ricevere uno dei seguenti messaggi di errore:

• ERROR: right sibling's left-link doesn't match:block 5 links to 10 instead of expected 2 in index ix_name
• ERROR: failed to re-find parent key in index "XXXXXXXXXX" for deletion target page XXX CONTEXT: while vacuuming index index_name of relation schema.table

Per risolvere il problema, ricostruisci l'indice. Oppure, per PostgreSQL 12 e versioni successive, puoi saltare la pulizia dell'indice.

Per ricostruire l'indice, esegui questo comando:

REINDEX INDEX ix_name CONCURRENTLY;

Nota: sostituisci ix_name con il nome del tuo indice.

-oppure-

Per saltare la pulizia dell'indice per PostgreSQL 12 e versioni successive, esegui questo comando:

VACUUM FREEZE INDEX_CLEANUP FALSE table_name;

Nota: sostituisci table_name con il nome della tua tabella.

Informazioni correlate

Preventing transaction ID wraparound failures (Prevenzione degli errori di wraparound degli ID delle transazioni) sul sito web PostgreSQL

Utilizzo della funzionalità di autovacuum di PostgreSQL in Amazon Aurora PostgreSQL

Understanding autovacuum in Amazon RDS for PostgreSQL environments (Funzionamento di autovacuum in ambienti Amazon RDS per PostgreSQL)

How do I end long-running queries in my Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible DB instance? (Come posso terminare le query lunghe nella mia istanza database Amazon RDS per PostgreSQL o Aurora compatibile con PostgreSQL?)