Éviter la réinitialisation des ID de transaction (TXID)

Cette page explique ce que vous pouvez faire lorsque votre base de données déclenche la protection contre la réinitialisation des ID de transaction dans PostgreSQL. Celle-ci se manifeste sous la forme d'un message ERROR tel que :

database is not accepting commands to avoid wraparound data loss in database
dbname.

Stop the postmaster and vacuum that database in single-user mode.

You might also need to commit or roll back old prepared transactions, or drop
stale replication slots.

Sinon, un message WARNING semblable à celui-ci peut s'afficher :

database dbname must be vacuumed within 10985967 transactions.

To avoid a database shutdown, execute a database-wide VACUUM in that database.

Vue d'ensemble de la procédure

  • Identifiez la base de données et les tables à l'origine de la réinitialisation.
  • Vérifiez que rien n'empêche l'exécution de (AUTO)VACUUM), par exemple un ID de transaction bloqué.
  • Mesurez la vitesse de AUTOVACUUM. Si elle est lente, vous pouvez éventuellement essayer de l'accélérer.
  • Si nécessaire, exécutez manuellement quelques commandes VACUUM supplémentaires.
  • Envisagez d'autres méthodes pour accélérer l'exécution de VACUUM. Parfois, le moyen le plus rapide consiste à supprimer la table ou certains index.

La plupart des valeurs d'options recommandées sont volontairement imprécises, car elles dépendent de nombreux paramètres de la base de données. Lisez les documents en lien à la fin de cette page pour une analyse plus approfondie du sujet.

Identifier la base de données et la table à l'origine de la réinitialisation

Trouver la base de données

Pour identifier la ou les bases de données contenant les tables qui entraînent la réinitialisation, exécutez la requête suivante :

SELECT datname,
       age(datfrozenxid),
       2^31-1000000-age(datfrozenxid) as remaining
  FROM pg_database
 ORDER BY 3

La base de données avec une valeur remaining proche de 0 est celle à l'origine du problème.

Trouver la table

Connectez-vous à la base de données et exécutez la requête suivante :

SELECT c.relnamespace::regnamespace as schema_name,
       c.relname as table_name,
       greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age,
       2^31-1000000-greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as remaining
  FROM pg_class c
  LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
 WHERE c.relkind IN ('r', 'm')
 ORDER BY 4;

Cette requête renvoie la ou les tables à l'origine du problème.

Pour les tables TEMPORARY

Si le point de terminaison schema_name commence par pg_temp_, la seule façon de résoudre le problème consiste à supprimer la table, car PostgreSQL ne vous permet pas de mettre les tables temporaires à disposition des autres sessions. Parfois, si cette session est ouverte et accessible, vous pourrez utiliser VACUUM sur la table à partir de la session, mais ce n'est souvent pas le cas. Utilisez les instructions SQL suivantes pour supprimer la table temporaire :

SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
DROP TABLE pg_temp_<N>.<tablename>;

Si ce paramètre était le seul blocage, en l'espace d'une minute environ, autovacuum récupère cette modification et déplace datfrozenxid vers pg_database. Cela résout l'état de lecture seule de la protection de réinitialisation.

Tables normales

Pour les tables normales (non temporaires), passez aux étapes suivantes pour vérifier que rien ne bloque le nettoyage, que VACUUM s'exécute assez rapidement et que la table la plus importante est bien nettoyée.

Rechercher un ID de transaction bloqué

Si le système ne dispose plus d'ID de transaction, il est possible que PostgreSQL ne puisse pas geler (c'est-à-dire marquer toutes les transactions comme visibles) les ID de transaction créés après le démarrage de la plus ancienne transaction en cours d'exécution. Cela est dû aux règles de contrôle de simultanéité multiversion (MVCC, multi-version concurrency control). Dans des cas extrêmes, ces transactions peuvent devenir si anciennes qu'elles empêchent VACUUM de nettoyer les anciennes transactions pour l'ensemble de la limite de réinitialisation de deux milliards d'ID de transaction et font que le système entier n'accepte plus de nouveaux LMD. Généralement, des avertissements s'affichent également dans le fichier journal, indiquant WARNING: oldest xmin is far in the past.

Vous ne devez passer à l'optimisation qu'après avoir remédié au blocage de l'ID de transaction.

Voici quatre raisons potentielles pour lesquelles un ID de transaction peut être bloqué et comment atténuer chacune d'entre elles :

  • Transactions de longue durée : identifiez-les, puis annulez ou arrêtez le backend pour débloquer l'ingestion de données.
  • Préparation de transactions orphelines : effectuez un rollback de ces transactions.
  • Emplacements de réplication obsolètes : supprime les emplacements abandonnés.
  • Transaction de longue durée sur l'instance dupliquée avec hot_standby_feedback = on : identifiez-les, puis annulez ou arrêtez le backend pour débloquer l'ingestion de données.

Pour ces scénarios, la requête suivante renvoie l'âge de la transaction la plus ancienne et le nombre de transactions restantes jusqu'à réinitialisation :

 WITH q AS (
SELECT
  (SELECT max(age(backend_xmin))
      FROM pg_stat_activity  WHERE state != 'idle' )       AS oldest_running_xact_age,
  (SELECT max(age(transaction)) FROM pg_prepared_xacts)    AS oldest_prepared_xact_age,
  (SELECT max(greatest(age(catalog_xmin),age(xmin))) FROM pg_replication_slots)        AS oldest_replication_slot_age,
  (SELECT max(age(backend_xmin)) FROM pg_stat_replication) AS oldest_replica_xact_age
)
SELECT *,
       2^31 - oldest_running_xact_age AS oldest_running_xact_left,
       2^31 - oldest_prepared_xact_age AS oldest_prepared_xact_left,
       2^31 - oldest_replication_slot_age AS oldest_replication_slot_left,
       2^31 - oldest_replica_xact_age AS oldest_replica_xact_left
FROM q;

Cette requête peut renvoyer l'une des valeurs *_left signalées à proximité ou à moins d'un million de la fonction de réinitialisation. Cette valeur correspond à la limite de la protection contre la réinitialisation lorsque PostgreSQL cesse d'accepter de nouvelles commandes d'écriture. Dans ce cas, consultez la section Supprimer les blocages VACUUM ou Ajuster VACUUM.

Par exemple, la requête précédente peut renvoyer la réponse suivante :

┌─[ RECORD 1 ]─────────────────┬────────────┐
│ oldest_running_xact_age      │ 2146483655 │
│ oldest_prepared_xact_age     │ 2146483655 │
│ oldest_replication_slot_age  │ ¤          │
│ oldest_replica_xact_age      │ ¤          │
│ oldest_running_xact_left     │ 999993     │
│ oldest_prepared_xact_left    │ 999993     │
│ oldest_replication_slot_left │ ¤          │
│ oldest_replica_xact_left     │ ¤          │
└──────────────────────────────┴────────────┘

où oldest_running_xact_left et oldest_prepared_xact_left sont compris dans la limite de protection d'un million contre la réinitialisation. Dans ce cas, vous devez d'abord supprimer les points de blocage pour que VACUUM fonctionne.

Supprimer les blocages de VACUUM

Transactions de longue durée

Dans la requête ci-dessus, si oldest_running_xact est égal à oldest_prepared_xact, accédez à la section Préparation d'une transaction orpheline, car la dernière valeur inclut également les transactions préparées.

Vous devrez peut-être d'abord exécuter la commande suivante en tant qu'utilisateur postgres : 

GRANT pg_signal_backend TO postgres;

Si la transaction en cause appartient à l'un des utilisateurs système (à partir de cloudsql...), vous ne pouvez pas l'annuler directement. Vous devez redémarrer la base de données pour l'annuler.

Pour identifier une requête de longue durée, puis l'annuler ou l'arrêter pour débloquer l'opération VACUUM, commencez par sélectionner quelques-unes des requêtes les plus anciennes. La ligne LIMIT 10 permet d'ajuster le résultat à l'écran. Vous devrez peut-être répéter cette opération après avoir résolu les requêtes les plus anciennes.

SELECT pid,
       age(backend_xid) AS age_in_xids,
       now() - xact_start AS xact_age,
       now() - query_start AS query_age,
       state,
       query
 FROM pg_stat_activity
 WHERE state != 'idle'
 ORDER BY 2 DESC
 LIMIT 10;

Si age_in_xids renvoie NULL, cela signifie que la transaction n'a pas été attribuée à un ID de transaction permanent et peut être ignorée.

Annulez les requêtes pour lesquelles xids_left_to_wraparound approche un million.

Si state correspond à active, la requête peut être annulée à l'aide de SELECT pg_cancel_backend(pid);. Sinon, vous devez mettre fin à la connexion entière à l'aide de SELECT pg_terminate_backend(pid);, où pid correspond à la valeur pid de la requête précédente.

Préparation de transactions orphelines

Répertoriez toutes les transactions préparées :

DB_NAME=> SELECT age(transaction),* FROM pg_prepared_xacts ;
┌─[ RECORD 1 ]┬───────────────────────────────┐
│ age         │ 2146483656                    │
│ transaction │ 2455493932                    │
│ gid         │ trx_id_pin                    │
│ prepared    │ 2021-03-03 16:54:07.923158+00 │
│ owner       │ postgres                      │
│ database    │ DB_NAME                       │
└─────────────┴───────────────────────────────┘

Effectuez le rollback de la ou des transactions préparées orphelines les plus anciennes en utilisant gid dans la dernière requête (dans ce cas, trx_id_pin) comme ID de transaction :

ROLLBACK PREPARED trx_id_pin;

Vous pouvez également effectuer le commit des éléments suivants : 

COMMIT PREPARED trx_id_pin;

Consultez la documentation SQL ROLLBACK PREPARED pour obtenir une explication complète.

Emplacements de réplication abandonnés

Si l'emplacement de réplication est abandonné parce que l'instance dupliquée existante est arrêtée, mise en pause ou rencontre un problème, vous pouvez supprimer l'instance dupliquée depuis gcloud ou depuis Google Cloud Console.

Tout d'abord, vérifiez que l'instance dupliquée n'est pas désactivée, comme décrit dans la section Gérer les instances dupliquées avec accès en lecture. Si l'instance dupliquée est désactivée, réactivez-la. Si le temps de latence reste élevé, supprimez l'instance dupliquée.

Les emplacements de réplication sont visibles dans la vue système pg_replication_slots.

La requête suivante permet d'extraire les informations pertinentes :

SELECT *, age(xmin) AS age FROM pg_replication_slots;
┌─[ RECORD 1 ]────────┬─────────────────────────────────────────────────┐
│ slot_name           │ cloudsql_1_355b114b_9ff4_4bc3_ac88_6a80199bd738 │
│ plugin              │ ¤                                               │
│ slot_type           │ physical                                        │
│ datoid              │ ¤                                               │
│ database            │ ¤                                               │
│ active              │ t                                               │
│ active_pid          │ 1126                                            │
│ xmin                │ 2453745071                                      │
│ catalog_xmin        │ ¤                                               │
│ restart_lsn         │ C0/BEF7C2D0                                     │
│ confirmed_flush_lsn │ ¤                                               │
│ age                 │ 59                                              │
└─────────────────────┴─────────────────────────────────────────────────┘

Dans cet exemple, la valeur pg_replication_slots est opérationnelle (âge == 59). Si l'âge est proche de 2 milliards, vous devez supprimer l'emplacement. Il n'existe pas de moyen simple d'identifier l'instance dupliquée dans le cas où la requête renvoie plusieurs enregistrements. Vous devez donc toutes les vérifier dans le cas où une transaction de longue durée s'exécuterait sur une instance dupliquée.

Transactions de longue durée sur les instances dupliquées

Vérifiez sur les instances dupliquées la transaction en cours d'exécution avec hot_standby_feedback défini sur on, puis désactivez-la sur l'instance dupliquée.

La colonne backend_xmin de la vue pg_stat_replication contient la valeur TXID la plus ancienne nécessaire sur l'instance dupliquée.

Pour la déplacer, arrêtez la requête qui la bloque sur l'instance dupliquée. Pour savoir quelle requête la bloque, utilisez la requête de la section Transactions de longue durée, mais cette fois, exécutez-la sur l'instance dupliquée.

Une autre option consiste à redémarrer l'instance dupliquée.

Configurer VACUUM

Définissez les deux options suivantes :

  • autovacuum_vacuum_cost_delay = 0
  • autovacuum_work_mem = 1048576

La première désactive toute limitation de disque lors de l'ingestion de données par PostgreSQL afin que VACUUM s'exécute à une vitesse maximale. Par défaut, l'ingestion de données automatique est limitée, de sorte qu'elle n'utilise pas tous les E/S disque sur les serveurs les plus lents.

La deuxième option, autovacuum_work_mem, réduit le nombre de passes de nettoyage des index. Si possible, sa capacité doit être suffisante pour stocker tous les ID de lignes mortes dans une table que VACUUM va nettoyer. Lorsque vous définissez cette valeur, gardez à l'esprit qu'il s'agit de la quantité maximale de mémoire locale qu'il est possible d'allouer à chaque VACUUM en cours d'exécution. Assurez-vous de ne pas allouer davantage que le quota disponible, en gardant un peu de réserve. Si vous laissez la base de données s'exécuter en mode lecture seule, pensez également à la mémoire locale utilisée pour les requêtes en lecture seule.

Sur la plupart des systèmes, utilisez la valeur maximale (1 Go ou 1 048 576 Ko, comme indiqué dans l'exemple). Cela correspond à environ 178 millions de tuples morts. Un nombre supérieur entraîne de multiples passes d'analyse supplémentaires de l'index.

Ces indicateurs et d'autres sont expliqués plus en détail sur la page Optimiser, surveiller et dépanner les opérations VACUUM dans PostgreSQL.

Après avoir défini ces indicateurs, redémarrez la base de données afin que l'ingestion automatique de données commence par les nouvelles valeurs.

Vous pouvez utiliser la vue pg_stat_progress_vacuum pour surveiller la progression des opérations VACUUM lancées automatiquement par l'ingestion automatique de données. Cette vue affiche les VACUUM en cours d'exécution dans toutes les bases de données et les tables (relations) d'autres bases de données que vous ne pouvez pas rechercher via la colonne relid.

Pour identifier les bases de données et les tables à nettoyer ensuite, utilisez les requêtes de la page Optimiser, surveiller et dépanner les opérations VACUUM dans PostgreSQL. Si la VM du serveur dispose de suffisamment de puissance et de bande passante pour lancer davantage de processus VACUUM en parallèle que la fonction d'ingestion automatique de données, vous pouvez lancer certaines opérations vacuum manuelles.

Vérifier la vitesse de VACUUM

Cette section explique comment contrôler la vitesse de VACUUM et comment l'augmenter si nécessaire.

Contrôler les ingestions de données automatiques en cours d'exécution

Tous les backends qui exécutent VACUUM sont visibles dans la vue système pg_stat_progress_vacuum.

Si la phase en cours est scanning heap, vous pouvez surveiller sa progression en consultant les modifications dans la colonne heap_blks_scanned. Il n'existe malheureusement pas de méthode simple pour déterminer la vitesse d'analyse au cours d'autres phases.

Estimer la vitesse d'analyse de VACUUM

Pour estimer la vitesse d'analyse, vous devez d'abord stocker les valeurs de base, puis calculer la modification au fil du temps pour estimer le temps d'exécution. Tout d'abord, vous devez enregistrer un instantané de heap_blks_scanned horodaté à l'aide de la requête d'instantané suivante :

SELECT set_config('save.ts', clock_timestamp()::text, false),
       set_config('save.heap_blks_scanned', heap_blks_scanned::text, false)
FROM pg_stat_progress_vacuum
WHERE datname = 'DB_NAME';

Étant donné qu'il n'est pas possible d'enregistrer quoi que ce soit dans des tables qui sont déjà en réinitialisation, utilisez set_config(flag, value) pour définir deux indicateurs définis par l'utilisateur (save.ts et save.heap_blks_scanned) sur les valeurs actuelles à partir de pg_stat_progress_vacuum.

Dans la requête suivante, on utilise ces deux éléments comme base de comparaison pour estimer la vitesse et la durée d'exécution.

REMARQUE : WHERE datname = DB_NAME restreint la recherche à une seule base de données à la fois, ce qui est suffisant s'il n'existe qu'une seule ingestion automatique de données en cours d'exécution sur cette base de données, avec plusieurs lignes par base de données. Des conditions de filtre supplémentaires ('AND relid= …'') doivent être ajoutées à WHERE pour cibler une seule ligne d'ingestion automatique. Il en va de même pour la requête suivante.

Une fois que vous avez enregistré les valeurs de base, vous pouvez exécuter la requête suivante :

with q as (
    SELECT datname,
           phase,
           heap_blks_total,
           heap_blks_scanned,
           clock_timestamp() - current_setting('save.ts')::timestamp AS ts_delta,
           heap_blks_scanned - current_setting('save.heap_blks_scanned')::bigint AS scanned_delta
     FROM pg_stat_progress_vacuum
     WHERE datname = DB_NAME
), q2 AS (
SELECT *,
       scanned_delta / extract('epoch' FROM ts_delta) AS pages_per_second
  FROM q
)
SELECT *,
       (heap_blks_total - heap_blks_scanned) / pages_per_second AS remaining_time
  FROM q2
;

┌─[ RECORD 1 ]──────┬──────────────────┐
│ datname           │ DB_NAME          │
│ phase             │ scanning heap    │
│ heap_blks_total   │ 9497174          │
│ heap_blks_scanned │ 18016            │
│ ts_delta          │ 00:00:40.30126   │
│ as_scanned_delta  │ 11642            │
│ pages_per_second  │ 288.87434288655  │
│ remaining_time    │ 32814.1222418038 │
└───────────────────┴──────────────────┘

Cette requête compare les valeurs actuelles aux valeurs de base de sauvegarde et calcule pages_per_second et remaining_time, ce qui permet de décider si l'opération VACUUM s'exécute suffisamment rapidement ou si le processus peut êtres accéléré. La valeur remaining_time ne concerne que la phase scanning heap. D'autres phases prennent aussi du temps, parfois même plus. Vous pouvez lire des informations supplémentaires sur l'ingestion de données. De nombreux articles de blog sur Internet abordent en détail divers aspects complexes des opérations VACUUM.

Accélérer VACUUM

Le moyen le plus simple et le plus rapide d'accélérer l'analyse VACUUM est de définir le paramètre autovacuum_vacuum_cost_delay=0. Cela peut être fait via Google Cloud Console.

Malheureusement, l'analyse VACUUM en cours d'exécution ne récupère pas cette valeur et vous devrez peut-être redémarrer la base de données.

Après un redémarrage, un résultat similaire s'affiche :

┌─[ RECORD 1 ]──────┬──────────────────┐
│ datname           │ DB_NAME          │
│ phase             │ scanning heap    │
│ heap_blks_total   │ 9497174          │
│ heap_blks_scanned │ 222382           │
│ ts_delta          │ 00:00:21.422615  │
│ as_scanned_delta  │ 138235           │
│ pages_per_second  │ 6452.76031894332 │
│ remaining_time    │ 1437.33713040171 │
└───────────────────┴──────────────────┘

Dans cet exemple, la vitesse a augmenté. Elle est passée de moins de 300 pages/s à 6 500 pages/s. Le délai restant prévu pour la phase d'analyse des tas de mémoire est passé de 9 heures à 23 minutes.

La vitesse d'analyse des autres phases n'est pas aussi facile à estimer, mais celles-ci devraient connaître une accélération similaire.

Vous pouvez également faire en sorte que autovacuum_work_mem soit aussi grand que possible afin d'éviter de multiples passes sur les index. Une passe d'index se produit chaque fois que la mémoire est remplie avec des pointeurs de tuple morts.

Si la base de données n'est pas utilisée par ailleurs, définissez autovacuum_work_mem de sorte à disposer d'environ 80 % de mémoire après avoir alloué la quantité requise aux shared_buffers. Il s'agit de la limite supérieure pour chacun des processus VACUUM démarrés automatiquement. Si vous souhaitez continuer à exécuter des charges de travail en lecture seule, utilisez moins de mémoire.

Autres moyens d'améliorer la vitesse

Éviter le nettoyage des index

Pour les tables lourdes, VACUUM passe la plupart du temps à nettoyer les index.

PostgreSQL 14 bénéficie d'optimisations spéciales pour éviter le nettoyage des index si le système présente un risque de réintialisation.

Dans PostgreSQL 12 et 13, vous pouvez exécuter manuellement l'instruction suivante :

VACUUM (INDEX_CLEANUP OFF, TRUNCATE OFF) <tablename>;

Dans les versions 11 et antérieures, vous pouvez DROP l'index avant d'exécuter la fonction vacuum, puis le recréer ultérieurement.

La suppression de l'index lorsqu'une fonction autovacuum est déjà en cours d'exécution sur cette table nécessite d'annuler la fonction vacuum en cours d'exécution, puis d'exécuter immédiatement la commande "drop index" avant que la fonction autovacuum ne parvienne à redémarrer l'opération vacuum sur cette table.

Tout d'abord, exécutez l'instruction suivante pour trouver le PID du processus autovacuum que vous devez arrêter :

SELECT pid, query
  FROM pg_stat_activity
 WHERE state != 'idle'
   AND query ilike '%vacuum%';

Exécutez ensuite les instructions suivantes pour mettre fin à l'exécution de la fonction vacuum et supprimer un ou plusieurs index :

SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
SELECT pg_terminate_backend(<pid>);DROP INDEX <index1>;DROP INDEX <index2>; ...

Supprimer la table mise en cause

Dans de rares cas, vous pouvez supprimer la table. Par exemple, s'il s'agit d'une table facile à restaurer à partir d'une autre source, telle qu'une sauvegarde ou une autre base de données.

Vous devrez toujours utiliser la commande cloudsql.enable_maintenance_mode = 'on' et, probablement, arrêter le VACUUM sur cette table, comme indiqué dans la section précédente.

VACUUM FULL

Dans de rares cas, il est plus rapide d'exécuter VACUUM FULL FREEZE, généralement lorsque la table ne comporte qu'une petite proportion de tuples actifs. Ceci peut être vérifié à partir de la vue pg_stat_user_tables (sauf si un plantage a effacé les statistiques).

La commande VACUUM FULL copie les tuples actifs dans un nouveau fichier. Il faut donc disposer de suffisamment d'espace pour le nouveau fichier et ses index.

Étape suivante