¿Cómo soluciono los problemas relacionados con la conversión de ID de transacción en Aurora PostgreSQL?

Resolución

Supervisión de las métricas y las alarmas de CloudWatch para evitar problemas

Para supervisar la antigüedad de las transacciones sin autovacuum más antiguas, consulta la métrica MaximumUsedTransactionIDs en Amazon CloudWatch. Para evitar problemas relacionados con los ID de transacción, crea una alarma de CloudWatch. Para obtener más información, consulta Implementación de un sistema de alerta temprana para el almacenamiento de ID de transacción en Amazon Relational Database Service (Amazon RDS) para PostgreSQL.

Ejecución de consultas de diagnóstico

Utiliza psql o pgAdmin para conectarte a la instancia de base de datos de PostgreSQL.

Para comprobar la antigüedad de la base de datos, ejecuta el siguiente comando:

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

Nota: Si el resultado muestra que la base de datos rdsadmin o template0 tiene el ID de transacción más antiguo, ponte en contacto con AWS Support.

Para conectarte a la base de datos con la mayor antigüedad e identificar posibles problemas con las tablas, ejecuta el siguiente 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 revisar el historial de autovacuum de las tablas con valores de antigüedad altos, ejecuta el siguiente 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: Sustituye table_name por el nombre de tu tabla.

Para supervisar las operaciones de vacuum activas, ejecuta el siguiente 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;

Resolución de los bloqueadores de procesos de autovacuum

Si usas una de las siguientes versiones de Aurora compatible con PostgreSQL, consulta Identificación y resolución de los bloqueadores de vacuum agresivos en Aurora PostgreSQL:

• 13.19 o posterior
• 14.16 o posterior
• 15.11 o posterior
• 16.7 o posterior
• 17.2 o posterior

Resolución de transacciones de larga duración

Las transacciones de larga duración o las sesiones de transición que están inactivas pueden bloquear autovacuum. 

Aparece el siguiente mensaje de advertencia en los registros de errores de PostgreSQL:

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

Para identificar las transacciones que bloquean autovacuum, ejecuta el siguiente 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 este problema, puedes cancelar las consultas activas o terminar las conexiones.

Para cancelar las consultas activas, ejecuta el siguiente comando:

SELECT pg_cancel_backend(pid);

Nota: Sustituye pid por el ID de proceso de los resultados de consulta.

Para terminar las conexiones inactivas, ejecuta el siguiente comando:

SELECT pg_terminate_backend(pid);

Nota: Sustituye pid por el ID de proceso de los resultados de consulta.

Para asegurarte de que las transacciones de larga duración no vuelvan a bloquear autovacuum, define los parámetros statement_timeout, idle_in_transaction_session_timeout y log_min_duration_statement.

Resolución de las ranuras de replicación lógica inactivas

Para identificar las ranuras inactivas, ejecuta el siguiente 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;

Advertencia: Antes de eliminar una ranura de replicación, confirma que no tiene ninguna replicación en curso, que está inactiva y que se encuentra en un estado irrecuperable. Si dejas caer una ranura que aún se está replicando, activa y recuperable, podrías interrumpir la replicación o provocar la pérdida de datos.

Para eliminar las ranuras que no se utilicen, ejecuta el siguiente comando:

SELECT pg_drop_replication_slot('slot_name');

Nota: Sustituye slot_name por el nombre de tu ranura de replicación.

Resolución de problemas con las instancias del lector

Para identificar los lectores de un clúster de Aurora que podrían estar impidiendo los comentarios en modo de espera activo y bloqueando autovacuum, ejecuta la siguiente consulta:

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

Ejecuta la siguiente consulta para los lectores que impiden los comentarios en espera activa en los clústeres de bases de datos globales:

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

Ejecuta la siguiente consulta en las instancias del lector con el valor combinado más bajo de feedback_epoch y 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: Sustituye feedback_xmin por el valor de feedback_xmin de los resultados de consulta anteriores.

Si puedes hacerlo de forma segura, utiliza pg_terminate_backend(pid) para terminar las transacciones de larga duración.

Resolución de transacciones preparadas no confirmadas

Para identificar las transacciones preparadas, ejecuta el siguiente 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 las transacciones preparadas, puedes confirmar la transacción preparada o revertirla.

Para confirmar la transacción preparada, ejecuta el siguiente comando:

COMMIT PREPARED 'gid'

Nota: Sustituye gid por el ID de transacción global de los resultados de consulta.

Advertencia: Antes de deshacer una transacción preparada, asegúrate de que la transacción no forma parte de una secuencia de transacciones distribuidas.

Para restaurar la transacción preparada, ejecuta el siguiente comando:

ROLLBACK PREPARED 'gid'

Nota: Sustituye gid por el ID de transacción global de los resultados de consulta.

Resolución de tablas temporales huérfanas

Cuando el proceso de autovacuum de PostgreSQL encuentra tablas temporales huérfanas, registra el siguiente evento:

REGISTRO: autovacuum: se encontró una tabla temporal huérfana «%s».«%s» en la base de datos «%s»

Para identificar las tablas temporales huérfanas, ejecuta el siguiente comando:

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

Para eliminar la tabla temporal, ejecuta el siguiente comando:

DROP TABLE temporary_table

Nota: Sustituye temporary_table por el nombre de tu tabla temporal.

Resolución de los problemas de rendimiento de vacuum

Cuando autovacuum funciona con lentitud, las operaciones de congelación pueden retrasarse.

Para resolver este problema, primero aumenta el parámetro autovacuum_max_workers para realizar más operaciones de vacuum simultáneas. A continuación, utiliza la siguiente fórmula para aumentar por igual el parámetro autovacuum_vacuum_cost_limit:

Coste individual por trabajador = autovacuum_vacuum_cost_limit / autovacuum_max_workers

Si el rendimiento de vacuum sigue siendo insuficiente después de aumentar los parámetros, actualiza la instancia de Amazon RDS a una clase que tenga más vCPU. Puedes modificar el parámetro autovacuum_vacuum_cost_delay a nivel de tabla para acelerar las operaciones de autovacuum.

Para modificar el parámetro autovacuum_vacuum_cost_delay a nivel de tabla, ejecuta el siguiente comando:

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

Nota: Sustituye mytable por el nombre de tu tabla y value por los valores de parámetros que desees.

Rendimiento de vacuum de índice grande

Al vaciar índices grandes, las restricciones de memoria pueden forzar varias operaciones sobre el mismo índice y aumentar significativamente la duración total de VACUUM.

Si las operaciones de VACUUM son lentas en índices grandes de PostgreSQL 16 o versiones anteriores, comprueba las siguientes causas posibles:

• El tamaño del índice supera 1 GB.
• Se están produciendo múltiples operaciones de VACUUM. Para ver el número de operaciones, consulta index_vacuum_count desde la vista pg_stat_progress_vacuum.
• Has configurado incorrectamente maintenance_work_mem o autovacuum_work_mem.

Para solucionar este problema, toma las siguientes medidas:

• Elimina los índices duplicados o no utilizados.
• Aumenta maintenance_work_mem o autovacuum_work_mem. Para que la nueva configuración de memoria surta efecto, finaliza los procesos que actualmente están ejecutando autovacuum.
• Realiza manualmente una operación VACUUM FREEZE y aumenta maintenance_work_mem durante la sesión.

Si usas la versión 12 o posterior de PostgreSQL, ejecuta el siguiente comando para revisar los posibles vacuum manuales con exclusiones de índice:

VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) table_name;

Nota: Sustituye table_name por el nombre de tu tabla.

Incoherencia lógica

Si un índice lógicamente incoherente bloquea el proceso de autovacuum, es posible que recibas uno de los siguientes mensajes de error:

• 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 este problema, reconstruye el índice. O para las versiones 12 y posteriores de PostgreSQL, puedes omitir la limpieza del índice.

Para reconstruir el índice, ejecuta el siguiente comando:

REINDEX INDEX ix_name CONCURRENTLY;

Nota: Sustituye ix_name por el nombre de tu índice.

O bien:

Para omitir la limpieza del índice de PostgreSQL 12 y versiones posteriores, ejecuta el siguiente comando:

VACUUM FREEZE INDEX_CLEANUP FALSE table_name;

Nota: Sustituye table_name por el nombre de tu tabla.

Información relacionada

Preventing transaction ID wraparound failures (Prevención de errores relacionados con los ID de transacción) en el sitio web de PostgreSQL

Uso de autovacuum de PostgreSQL en Amazon Aurora PostgreSQL

Understanding autovacuum in Amazon RDS for PostgreSQL environments (Descripción de autovacuum en entornos de Amazon RDS para PostgreSQL)

How do I end long-running queries in my Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible DB instance? (¿Cómo finalizo las consultas de larga duración en mi instancia de base de datos de Amazon RDS para PostgreSQL o de Aurora compatible con PostgreSQL?)