Como soluciono problemas de autovaccum quando ele executa o sinalizador "to prevent wraparound" de bancos de dados do Amazon RDS para PostgreSQL ou do Aurora compatíveis com PostgreSQL?

Breve descrição

Quando você consulta pg_stat_activity, o autovacuum pode executar continuamente o sinalizador "to prevent wraparound".

Os processamentos de autovacuum geralmente não bloqueiam outros comandos. Quando um processo tenta adquirir um bloqueio que entra em conflito com o bloqueio SHARE UPDATE EXCLUSIVE do autocvacuum, a aquisição do bloqueio pode interromper o autovacuum. No entanto, quando o autovacuum está sendo executado para evitar o wraparound do ID da transação, o sistema não o interrompe automaticamente.

O autovacuum agressivo com o sinalizador "to prevent wraparound" pode ocorrer nos seguintes casos:

• O valor de relfrozenxid da tabela é maior do que as transações vacuum_freeze_table_age, então o sistema executa um vacuum agressivo para congelar tuplas antigas e avançar o relfrozenxid.
• Uma tabela atinge seu autovacuum_freeze_max_age.

Problemas de desempenho do banco de dados podem ocorrer quando o autovacuum é executado de forma agressiva. Para obter mais informações, consulte Automatic vacuuming (Vacuuming automático) no site do PostgreSQL.

Resolução

Verifique se o autovacuum está em execução

Para verificar se o autovacuum está em execução, há quanto tempo está em execução ou se está em espera, execute a seguinte consulta:

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;

No exemplo de saída a seguir, o sinalizador "to prevent wraparound" está sendo executado em mydb para 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; +

O autovaccum sempre tem como destino as tabelas que têm um relfrozenxid maior que o número de transações em autovaccum_freeze_max_age.

Identifique transações não congeladas

Para identificar transações não congeladas em seu banco de dados, execute a seguinte consulta:

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

No exemplo de saída a seguir, o deamon do autovacuum prioriza o banco de dados com um datfrozenxid maior que 200 milhões:

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

A coluna datfrozenxid da linha pg_database de um banco de dados está no final dos IDs de transação normais no banco de dados. A coluna datfrozenxid é o menor valor de relfrozenxid para cada tabela no banco de dados.

Congele as tabelas

Se sua tabela estiver atingindo a idade máxima da tabela relfrozenxid, realize o vacuum freeze manual nas tabelas.

Depois que o vacuum freeze manual for concluído, visualize a saída detalhada para verificar se há bloqueadores, como slots de replicação antigos e transações preparadas. Se a mensagem "DETAIL: xxx dead row versions cannot be removed yet, oldest xmin: xxx" for exibida, então o vacuum está em execução, mas não consegue limpar as tuplas mortas.

Para encontrar os bloqueadores de vacuum, é possível usar a função postgres_get_av_diag(). Para mais informações, consulte Identificar e resolver bloqueadores de limpeza agressivos no RDS para PostgreSQL.

Aumente o número de processamentos de autovacuum e remova índices não utilizados

Quando os bancos de dados ou tabelas que precisam de vacuuming excedem a cota de autovacuum_max_workers, o mecanismo processa o próximo banco de dados ou tabela somente quando um processamento se torna disponível. Em ambientes com altas taxas de transação, podem ocorrer problemas de wraparound vacuum.

Para resolver esse problema, aumente o parâmetro autovacuum_max_workers para que haja mais processamentos de autovacuum simultâneos.

Se sua tabela tiver índices grandes, é uma prática recomendada remover índices não utilizados.

Aumente a memória de vacuum

Para garantir que o mecanismo aplique a alocação de memória ajustada aos novos autovacuums, aumente o parâmetro autovacuum_work_mem.

Para aumentar o maintenance_work_mem para operações manuais de vacuum, como vacuum freeze, execute o seguinte comando:

SET maintenance_work_mem TO '2GB';

Observação: o valor padrão de maintenance_work_mem é 64 MB. Depois que o vacuum for concluído, altere o valor para o valor anterior. Se você não alterar o valor, poderão ocorrer problemas de memória em instâncias menores.

Para iniciar a operação de vacuum, execute o seguinte comando:

VACUUM FREEZE VERBOSE table_name;

Se houver índices grandes que exijam limpeza, o autovacuum deverá realizar várias passagens. No PostgreSQL 16 e versões anteriores, há uma cota de 1 GB de maintenance_work_mem que a operação de vacuum usa. O PostgreSQL 17 e versões posteriores usam o TidStore que aloca memória dinamicamente e não usa uma matriz de alocação única para que o vacuum possa usar maintenance_work_mem com mais eficiência.

Ative o vacuuming paralelo

Em um vacuum manual, é possível ativar o vacuuming paralelo, para que o PostgreSQL atribua um processamento de vacuum a cada índice e execute vaccums neles em paralelo.

Para ativar o vacuum paralelo, ajuste a configuração max_parallel_maintenance_workers com base no número de vCPUs disponíveis em sua instância de banco de dados e no número de índices na tabela.

Para obter mais informações, consulte Parallel vacuuming in Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL (Vacuuming paralelo no Amazon RDS para PostgreSQL e Amazon Aurora PostgreSQL).

Monitore o número de transações que seu banco de dados usou

Quando a operação de autovacuum não consegue concluir um vacuum, o banco de dados pode continuar aumentando. É possível usar a métrica MaximumUsedTransactionIDs do Amazon CloudWatch para obter o número máximo de transações que o banco de dados usou.

Além disso, crie um alarme do CloudWatch para a métrica MaximumUsedTransactionIDs para receber uma notificação quando o número de transações atingir o valor que você especificou. Para obter mais informações, consulte Implement an early warning system for transaction ID wraparound in Amazon RDS for PostgreSQL (Implementar um sistema de alerta antecipado para o wraparound do ID de transação no Amazon RDS para PostgreSQL).

Para monitorar MaximumUsedTransactionIDs no console do Amazon RDS, conclua as seguintes etapas:

1. Abra o console do Amazon RDS.
2. No painel de navegação, clique em Bancos de dados e selecione seu banco de dados.
3. Na guia Logs e eventos, é possível visualizar MaximumUsedTransactionIDs na seção Eventos recentes.

Se você estiver atingindo a cota de MaximumUsedTransactionIDs, será necessário realizar o vacuum na tabela.

Para identificar os bloqueadores de vacuum, consulte Identificar e resolver bloqueadores de limpeza agressivos no RDS para PostgreSQL.