Como soluciono problemas de wraparound do ID de transação em um Aurora compatível com PostgreSQL?

Resolução

Monitore métricas e alarmes do CloudWatch para evitar problemas

Para monitorar a idade da transação mais antiga que não passou por vacuum, verifique a métrica MaximumUsedTransactionIDs no Amazon CloudWatch. Para evitar problemas de wraparound do ID da transação, crie um alarme do CloudWatch. Para obter mais informações, consulte Implement an early warning system for transaction ID wraparound in Amazon Relational Database Service (Amazon RDS) for PostgreSQL (Implementar um sistema de alerta precoce para o wraparound do ID de transação no Amazon Relational Database Service (Amazon RDS) para PostgreSQL).

Execute consultas de diagnóstico

Use psql ou pgAdmin para e conectar à instância de banco de dados do PostgreSQL.

Para verificar a idade do banco de dados, execute o seguinte comando:

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

Observação: se a saída mostrar que o banco de dados rdsadmin ou template0 tem a idade mais antiga do ID de transação, entre em contato com o AWS Support.

Para se conectar ao banco de dados com a maior idade para identificar possíveis problemas com tabelas, execute o seguinte 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;

Para analisar o histórico de autovacuum de tabelas com altos valores de idade, execute o seguinte 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';

Observação: substitua table_name pelo nome da sua tabela.

Para monitorar as operações ativas de vacuum, execute o seguinte 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;

Resolva os bloqueadores do processo de autovacuum

Se você usa uma das seguintes versões do Aurora compatíveis com o PostgreSQL, consulte Identificar e resolver bloqueadores de limpeza agressivos no Aurora PostgreSQL:

• 13,19 ou posterior
• 14,16 ou posterior
• 15,11 ou posterior
• 16,7 ou posterior
• 17,2 ou posterior

Resolva transações de longa duração

Transações de longa duração ou sessões em transição que estão inativas podem bloquear o autovacuum.

Você vê a seguinte mensagem de aviso em seus logs de erro do PostgreSQL:

"AVISO: o xmin mais antigo está muito no passado DICA: Feche as transações abertas em breve para evitar problemas de wraparound."

Para identificar transações que estão bloqueando o autovacuum, execute o seguinte 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;

Para resolver esse problema, é possível cancelar consultas ativas ou encerrar conexões.

Para cancelar consultas ativas, execute o seguinte comando:

SELECT pg_cancel_backend(pid);

Observação: substitua pid pelo ID do processo dos resultados da consulta.

Para encerrar conexões, execute o seguinte comando:

SELECT pg_terminate_backend(pid);

Observação: substitua pid pelo ID do processo dos resultados da consulta.

Para garantir que transações de longa duração não bloqueiem o autovacuum novamente, defina os parâmetros statement_timeout, idle_in_transaction_session_timeout e log_min_duration_statement.

Resolva slots de replicação lógica inativos

Para identificar slots inativos, execute o seguinte 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;

Aviso: antes de descartar um slot de replicação, verifique se ele não tem replicação em andamento, está inativo e está em um estado irrecuperável. Se você descartar um slot que ainda está replicando, ativo e recuperável, pode interromper a replicação ou causar perda de dados.

Para descartar slots não utilizados, execute o seguinte comando:

SELECT pg_drop_replication_slot('slot_name');

Observação: substitua slot_name pelo nome do seu slot de replicação.

Resolva problemas de instância do leitor

Para identificar leitores em um cluster do Aurora que possam estar impedindo o feedback do standby a quente e bloqueando o autovacuum, execute a seguinte consulta:

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

Execute a consulta a seguir em leitores que estão impedindo o feedback do standby a quente em clusters de bancos de dados globais:

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);

Execute a seguinte consulta nas instâncias do leitor com o menor valor combinado de 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

Observação: substitua feedback_xmin pelo valor de feedback_xmin dos resultados da consulta anterior.

Se você puder fazer isso com segurança, use pg_terminate_backend(pid) para encerrar as transações de longa duração.

Resolva transações preparadas não confirmadas

Para identificar transações preparadas, execute o seguinte 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;

Para resolver transações preparadas, é possível confirmar a transação preparada ou revertê-la.

Para confirmar a transação preparada, execute o seguinte comando:

COMMIT PREPARED 'gid'

Observação: substitua gid pelo ID da transação global dos resultados da consulta.

Aviso: antes de reverter uma transação preparada, certifique-se de que a transação não faça parte de uma sequência de transações distribuídas.

Para reverter a transação preparada, execute o seguinte comando:

ROLLBACK PREPARED 'gid'

Observação: substitua gid pelo ID da transação global dos resultados da consulta.

Resolva tabelas temporárias órfãs

Quando o processo de autovacuum do PostgreSQL encontra tabelas temporárias órfãs, ele registra em log o seguinte evento:

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

Para identificar tabelas temporárias órfãs, execute o seguinte comando:

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

Para descartar a tabela temporária, execute o seguinte comando:

DROP TABLE temporary_table

Observação: substitua temporary_table pelo nome da sua tabela temporária.

Resolva problemas de desempenho de vacuum

Quando o autovacuum está com desempenho lento, as operações de congelamento podem atrasar.

Para resolver esse problema, primeiro aumente o parâmetro autovacuum_max_workers para mais operações de vacuum simultâneas. Em seguida, use a fórmula a seguir para aumentar igualmente o parâmetro autovacuum_vacuum_cost_limit:

cost_limit do processamento individual = autovacuum_vacuum_cost_limit / autovacuum_max_workers

Se o desempenho do vacuum permanecer insuficiente depois de aumentar os parâmetros, faça upgrade da sua instância do Amazon RDS para uma classe que tenha mais vCPUs. É possível modificar o parâmetro autovacuum_vacuum_cost_delay no nível da tabela para acelerar as operações de autovacuum.

Para modificar o parâmetro autovacuum_vacuum_cost_delay no nível da tabela, execute o seguinte comando:

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

Observação: substitua mytable pelo nome da tabela e value pelos seus valores de parâmetros desejados.

Desempenho de vacuum de índice grande

Quando você está fazendo vacuum de índices grandes, as restrições de memória podem forçar várias passagens pelo mesmo índice e aumentar significativamente a duração total de VACUUM.

Se você estiver enfrentando operações de VACUUM lentas em índices grandes no PostgreSQL 16 ou anterior, verifique as seguintes causas possíveis:

• O tamanho do índice excede 1 GB.
• Várias passagens VACUUM estão ocorrendo. Para ver o número de passagens, consulte index_vacuum_count na visualização pg_stat_progress_vacuum.
• Você configurou incorretamente maintenance_work_mem ou autovacuum_work_mem.

Para resolver esse problema, realize as seguintes ações:

• Descarte índices não utilizados ou duplicados.
• Aumente maintenance_work_mem ou autovacuum_work_mem. Para que a nova configuração de memória entre em vigor, encerre os processos que estão executando atualmente o autovacuum
• Execute manualmente uma operação VACUUM FREEZE e aumente maintenance_work_mem em sua sessão.

Se você usa o PostgreSQL versão 12 ou posterior, execute o seguinte comando para analisar possíveis vacuums manuais com exclusões de índice:

VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) table_name;

Observação: substitua table_name pelo nome da sua tabela.

Inconsistência lógica

Se um índice logicamente inconsistente bloquear o processo de autovacuum, é possível receber uma das seguintes mensagens de erro:

• 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

Para resolver esse problema, reconstrua o índice. Ou nas versões 12 e posteriores do PostgreSQL, é possível pular a limpeza do índice.

Para reconstruir o índice, execute o seguinte comando:

REINDEX INDEX ix_name CONCURRENTLY;

Observação: substitua ix_name pelo nome do seu índice.

-ou-

Para pular a limpeza do índice do PostgreSQL 12 e versões posteriores, execute o seguinte comando:

VACUUM FREEZE INDEX_CLEANUP FALSE table_name;

Observação: substitua table_name pelo nome da sua tabela.

Informações relacionadas

Preventing transaction ID wraparound failures (Prevenção de falhas de wraparound do ID de transação) no site do PostgreSQL

Trabalhar com o autovacuum do PostgreSQL no Amazon Aurora PostgreSQL

Understanding autovacuum in Amazon RDS for PostgreSQL environments (Entendendo o autovacuum em ambientes do Amazon RDS para PostgreSQL)

How do I end long-running queries in my Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible DB instance? (Como faço para encerrar consultas de longa duração em minha instância de banco de dados do Amazon RDS para PostgreSQL ou compatível com PostgreSQL do Aurora?)