Come posso risolvere i problemi del processo di autovacuum quando esegue il flag "to prevent wraparound" nei database Amazon RDS per PostgreSQL o Aurora compatibile con PostgreSQL?

Breve descrizione

Quando esegui query pg_stat_activity, autovacuum potrebbe eseguire continuamente il flag "to prevent wraparound".

I worker di autovacuum generalmente non bloccano altri comandi. Quando un processo tenta di acquisire un blocco che è in conflitto con il blocco SHARE UPDATE EXCLUSIVE del processo di autovacuum, l'acquisizione del blocco può interromperlo. Quando tuttavia il processo di autovacuum è in esecuzione per prevenire il wraparound dell'ID transazione, il sistema non lo interrompe automaticamente.

Un processo di autovacuum aggressivo con il flag "to prevent wraparound" può verificarsi nei seguenti casi:

• Il valore relfrozenxid della tabella è maggiore delle transazioni vacuum_freeze_table_age, per cui il sistema esegue un processo di vacuum aggressivo per bloccare le tuple obsolete e far avanzare relfrozenxid.
• Una tabella raggiunge il suo valore autovacuum_freeze_max_age.

Quando il processo di autovacuum viene eseguito in modo aggressivo, possono verificarsi problemi di prestazioni del database. Per ulteriori informazioni, consulta Automatic vacuuming (Autovacuum) sul sito web PostgreSQL.

Risoluzione

Determina se autovacuum è in esecuzione

Per determinare se il processo di autovacuum è in esecuzione, per quanto tempo è in esecuzione o se è in attesa, esegui questa query:

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

Nel seguente esempio di output, il flag "to prevent wraparound" flag è in esecuzione su mydb per mytable1 e mytable2:

datname | usename  | pid   | state  | wait_event |       xact_runtime      | query  
--------+----------+-------+--------+------------+-------------------------+----------------------------------------------------------------------------------------------  
mydb    | rdsadmin | 16473 | active |            | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound)  
mydb    | rdsadmin | 22553 | active |            | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound)  
mydb    | rdsadmin | 41909 | active |            | 3 days 02:43:54.203349  | autovacuum: VACUUM ANALYZE public.mytable3  
mydb    | rdsadmin | 618   | active |            | 00:00:00                | SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query+  
        |          |       |        |            |                         | FROM pg_stat_activity +  
        |          |       |        |            |                         | WHERE query like '%VACUUM%'+  
        |          |       |        |            |                         | ORDER BY xact_start; +

Il processo di autovacuum interviene sempre sulle tabelle con un valore relfrozenxid maggiore del numero di transazioni in autovaccum_freeze_max_age.

Identifica le transazioni bloccate

Per identificare le transazioni non bloccate nel database, esegui questa query:

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) desc limit 20;

Nel seguente esempio di output, il daemon autovacuum dà la priorità al database con un datfrozenxid maggiore di 200 milioni:

datname   | age  
mydb      | 1771757888  
template0 | 1721757888  
template1 | 1721757888  
rdsadmin  | 1694008527  
postgres  | 1693881061  
(5 rows)

La colonna datfrozenxid della riga pg_database di un database si trova alla fine dei normali ID transazione nel database. La colonna datfrozenxid è il minimo dei valori relfrozenxid per ogni tabella all'interno del database.

Blocca le tabelle

Se la tabella sta raggiungendo la sua età massima relfrozenxid, bloccala manualmente.

Una volta completato il blocco manuale, visualizza l'output dettagliato per determinare se sono presenti blocchi, come slot di replica obsoleti e transazioni preparate. Se ricevi il messaggio "DETAIL: xxx dead row versions cannot be removed yet, oldest xmin: xxx", significa che il processo di vacuum è in esecuzione ma non riesce a ripulire le tuple obsolete.

Per identificare i blocchi del processo di vacuum, puoi utilizzare la funzione postgres_get_av_diag(). Per ulteriori informazioni, consulta Identificazione e risoluzione dei blocchi per i processi di vacuum aggressivi in RDS per PostgreSQL.

Aumenta il numero di worker di autovacuum e rimuovi gli indici inutilizzati

Quando i database o le tabelle che richiedono un processo di vacuum superano la quota autovacuum_max_workers, il motore elabora il database o la tabella successiva solo quando un worker diventa disponibile. In ambienti con numero elevato di transazioni, possono verificarsi problemi di wraparound nel processo di vacuum.

Per risolverli, aumenta il parametro autovacuum_max_workers in modo che vi siano più worker di autovacuum concorrenti.

Se la tabella ha indici di grandi dimensioni, è consigliabile rimuovere gli indici inutilizzati.

Aumenta la memoria di vacuum

Per assicurarsi che il motore applichi l'allocazione di memoria modificata ai nuovi processi di autovacuum, aumenta il parametro autovacuum_work_mem.

Per aumentare maintenance_work_mem per le operazioni di vacuum manuali, come un vacuum freeze, esegui questo comando:

SET maintenance_work_mem TO '2GB';

Nota: il valore predefinito per maintenance_work_mem è 64 MB. Una volta completato il processo di vacuum, riporta il valore al valore precedente. Se non modifichi il valore, potrebbero verificarsi problemi di memoria per istanze di dimensioni inferiori.

Per avviare l'operazione di vacuum, esegui questo comando:

VACUUM FREEZE VERBOSE table_name;

Se sono presenti indici di grandi dimensioni che richiedono una pulizia, il processo di autovacuum deve eseguire più passaggi. In PostgreSQL 16 e versioni precedenti, è prevista una quota di 1 GB per maintenance_work_mem utilizzata dall'operazione di vacuum. PostgreSQL 17 e versioni successive utilizzano TidStore che alloca dinamicamente la memoria e non utilizza un array ad allocazione singola in modo che il processo di vacuum possa utilizzare in modo più efficiente maintenance_work_mem.

Attiva il processo di vacuum parallelo

Per un operazione di vacuum manuale, puoi attivare un processo parallelo in modo che PostgreSQL assegni un worker di vacuum a ogni indice ed esegua il processo in parallelo.

Per attivare il processo di vacuum parallelo, regola l'impostazione max_parallel_maintenance_workers in base al numero di vCPU disponibili per l'istanza database e al numero di indici della tabella.

Per ulteriori informazioni, consulta Parallel vacuuming in Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL (Processo di vacuum parallelo in Amazon RDS per PostgreSQL e Amazon Aurora PostgreSQL).

Monitora il numero di transazioni utilizzate dal database

Quando l'operazione di autovacuum non riesce a completare il processo di vacuum, il database potrebbe continuare ad aumentare. Puoi utilizzare la metrica di Amazon CloudWatch MaximumUsedTransactionIDs per ottenere il numero massimo di transazioni utilizzate dal database.

Inoltre, crea un allarme in CloudWatch per la metrica MaximumUsedTransactionIDs per notificarti quando il numero di transazioni raggiunge il valore specificato. Per ulteriori informazioni, consulta Implement an Early Warning System for Transaction ID Wraparound in Amazon RDS for PostgreSQL (Come implementare un sistema di allerta precoce per il wraparound degli ID transazione in Amazon RDS per PostgreSQL).

Per monitorare MaximumUsedTransactionIDs sulla console Amazon RDS, completa i seguenti passaggi:

1. Apri la console Amazon RDS.
2. Scegli Database dal pannello di navigazione, quindi seleziona il database.
3. Nella scheda Log ed eventi, puoi visualizzare MaximumUsedTransactionIDs nella sezione Eventi recenti.

Se stai raggiungendo la quota MaximumUsedTransactionIDs, devi svuotare la tabella.

Per identificare i blocchi del processo di vacuum, consulta Identificazione e risoluzione dei blocchi per i processi di vacuum aggressivi in RDS per PostgreSQL.