Comment résoudre les problèmes d'encapsulation des ID de transaction dans Aurora compatible avec PostgreSQL ?

Résolution

Surveiller les métriques et les alarmes CloudWatch pour éviter les problèmes

Pour surveiller l’ID de transaction non soumise à une opération vacuum le plus ancien, vérifiez la métrique MaximumUsedTransactionIDs dans Amazon CloudWatch. Pour éviter les problèmes d’encapsulation des ID de transaction, créez une alarme CloudWatch. Pour plus d'informations, consultez la section Implémenter un système d'alerte précoce pour l’encapsulation des ID de transaction dans Amazon Relational Database Service (Amazon RDS) pour PostgreSQL.

Exécuter des requêtes de diagnostic

Utilisez psql ou pgAdmin pour vous connecter à l'instance de base de données PostgreSQL.

Pour vérifier l'âge de la base de données, exécutez la commande suivante :

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

Remarque : si le résultat indique que la base de données rdsadmin ou template0 possède l'ID de transaction le plus ancien, contactez AWS Support.

Pour vous connecter à la base de données la plus ancienne afin d'identifier les problèmes potentiels liés aux tables, exécutez la commande suivante :

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;

Pour consulter l'historique d'autovacuum des tables présentant des valeurs d'âge élevées, exécutez la commande suivante :

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

Remarque : remplacez table_name par le nom de la table.

Pour surveiller les opérations vacuum actives, exécutez la commande suivante :

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;

Résoudre les bloqueurs du processus d'autovacuum

Si vous utilisez l'une des versions d’Aurora compatible avec PostgreSQL suivantes, consultez la section Identifier et résoudre les bloqueurs de vacuum agressifs dans Aurora PostgreSQL :

• 13.19 ou version ultérieure
• 14.16 ou version ultérieure
• 15.11 ou version ultérieure
• 16.7 ou version ultérieure
• 17.2 ou version ultérieure

Résoudre les transactions de longue durée

Les transactions de longue durée ou les sessions en transition qui sont inactives peuvent bloquer l'autovacuum.

Le message d'avertissement suivant s'affiche dans vos journaux d'erreurs PostgreSQL :

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

Pour identifier les transactions qui bloquent l'autovacuum, exécutez la commande suivante :

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;

Pour résoudre ce problème, vous pouvez annuler les requêtes actives ou mettre fin aux connexions.

Pour annuler les requêtes actives, exécutez la commande suivante :

SELECT pg_cancel_backend(pid);

Remarque : remplacez pid par l'ID de processus figurant dans les résultats de la requête.

Pour mettre fin aux connexions, exécutez la commande suivante :

SELECT pg_terminate_backend(pid);

Remarque : remplacez pid par l'ID de processus figurant dans les résultats de la requête.

Pour vous assurer que les transactions de longue durée ne bloquent pas à nouveau l'autovacuum, définissez les paramètres statement_timeout, idle_in_transaction_session_timeout et log_min_duration_statement.

Résoudre les slots de réplication logique inactifs

Pour identifier les slots inactifs, exécutez la commande suivante :

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;

Avertissement : avant de supprimer un slot de réplication, assurez-vous qu'aucune réplication n'est en cours, qu'il est inactif et qu'il se trouve dans un état irrécupérable. Si vous supprimez un slot qui est toujours en cours de réplication, actif et récupérable, vous risquez d'interrompre la réplication ou de provoquer une perte de données.

Pour supprimer les slots inutilisés, exécutez la commande suivante :

SELECT pg_drop_replication_slot('slot_name');

Remarque : remplacez slot_name par le nom de votre slot de réplication.

Résoudre les problèmes liés aux instances de lecteur

Pour identifier les lecteurs d'un cluster Aurora susceptibles d'empêcher les commentaires en mode veille permanente et de bloquer l'autovacuum, exécutez la requête suivante :

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

Exécutez la requête suivante pour les lecteurs qui empêchent les commentaires en mode veille permanente dans les clusters de bases de données 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);

Exécutez la requête suivante dans les instances de lecteur avec la valeur combinée la plus faible de feedback_epoch et 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

Remarque : remplacez feedback_xmin par la valeur feedback_xmin des résultats de la requête précédente.

Si vous pouvez effectuer cette opération en toute sécurité, utilisez pg_terminate_backend(pid) pour mettre fin aux transactions de longue durée.

Résoudre les transactions préparées non validées

Pour identifier les transactions préparées, exécutez la commande suivante :

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;

Pour résoudre les transactions préparées, vous pouvez soit valider la transaction préparée, soit l'annuler.

Pour valider la transaction préparée, exécutez la commande suivante :

COMMIT PREPARED 'gid'

Remarque : remplacez gid par l'ID de transaction global à partir des résultats de la requête.

Avertissement : avant d'annuler une transaction préparée, assurez-vous qu'elle ne fait pas partie d'une séquence de transactions distribuée.

Pour annuler la transaction préparée, exécutez la commande suivante :

ROLLBACK PREPARED 'gid'

Remarque : remplacez gid par l'ID de transaction global à partir des résultats de la requête.

Résoudre les tables temporaires orphelines

Lorsque le processus autovacuum de PostgreSQL rencontre des tables temporaires orphelines, il journalise l'événement suivant :

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

Pour identifier les tables temporaires orphelines, exécutez la commande suivante :

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

Pour supprimer la table temporaire, exécutez la commande suivante :

DROP TABLE temporary_table

Remarque : remplacez temporary_table par le nom de votre table temporaire.

Résoudre les problèmes de performance de vacuum

Lorsqu’autovacuum fonctionne lentement, les opérations de blocage peuvent être retardées.

Pour résoudre ce problème, augmentez d'abord le paramètre autovacuum_max_workers pour augmenter le nombre d'opérations de vacuum simultanées. Utilisez ensuite la formule suivante pour augmenter également le paramètre autovacuum_vacuum_cost_limit :

Individual worker's cost_limit = autovacuum_vacuum_cost_limit / autovacuum_max_workers

Si les performances de vacuum restent insuffisantes après avoir augmenté les paramètres, mettez à niveau votre instance Amazon RDS vers une classe comportant davantage de processeurs virtuels. Vous pouvez modifier le paramètre autovacuum_vacuum_cost_delay au niveau de la table pour accélérer les opérations d'autovacuum.

Pour modifier le paramètre autovacuum_vacuum_cost_delay au niveau de la table, exécutez la commande suivante :

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

Remarque : remplacez mytable par le nom de la table et value par les valeurs de paramètres souhaitées.

Performances de vacuum sur les index volumineux

Lorsque vous effectuez des opérations vacuum sur des index volumineux, les contraintes de mémoire peuvent forcer plusieurs passes sur le même index et augmenter de manière significative la durée totale de VACUUM.

Si les opérations de VACUUM sont lentes sur des index volumineux dans PostgreSQL 16 ou version antérieure, recherchez les causes potentielles suivantes :

• La taille de l'index est supérieure à 1 Go.
• De multiples passes VACUUM se produisent. Pour afficher le nombre de passes, interrogez index_vacuum_count à partir de la vue pg_stat_progress_vacuum.
• Vous avez incorrectement configuré maintenance_work_mem ou autovacuum_work_mem.

Pour résoudre ce problème, procédez comme suit :

• Supprimez les index inutilisés ou dupliqués.
• Augmentez maintenance_work_mem ou autovacuum_work_mem. Pour que le nouveau paramètre de mémoire prenne effet, arrêtez les processus en cours d'exécution d'autovacuum
• Effectuez manuellement une opération VACUUM FREEZE et augmentez maintenance_work_mem au cours de votre session.

Si vous utilisez PostgreSQL version 12 ou ultérieure, exécutez la commande suivante pour examiner les vacuums manuels potentiels avec exclusions d'index :

VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) table_name;

Remarque : remplacez table_name par le nom de la table.

Incohérence logique

Si un index logiquement incohérent bloque le processus d'autovacuum, l'un des messages d'erreur suivants peut s'afficher :

• 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

Pour résoudre ce problème, reconstruisez l'index. Ou pour les versions 12 et ultérieures de PostgreSQL, vous pouvez ignorer le nettoyage de l'index.

Pour reconstruire l’index, exécutez la commande suivante :

REINDEX INDEX ix_name CONCURRENTLY;

Remarque : remplacez ix_name par le nom de l’index.

-ou-

Pour ignorer le nettoyage de l'index pour PostgreSQL 12 et version ultérieure, exécutez la commande suivante :

VACUUM FREEZE INDEX_CLEANUP FALSE table_name;

Remarque : remplacez table_name par le nom de la table.

Informations connexes

Prévention des échecs d'encapsulation des ID de transaction sur le site Web de PostgreSQL

Utilisation de l’autovacuum PostgreSQL sur Amazon Aurora PostgreSQL

Comprendre la fonction autovacuum dans les environnements Amazon RDS pour PostgreSQL

Comment puis-je mettre fin aux requêtes de longue durée dans mon instance de base de données Amazon RDS pour PostrgreSQL ou Aurora compatible avec PostgreSQL ?