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

Cette page explique ce que vous pouvez faire lorsque votre base de données a déclenché ou est sur le point de déclencher la protection contre la réinitialisation des ID de transaction dans PostgreSQL. Celle-ci se manifeste sous la forme d'un message d'erreur 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.

ou d'un message d'avertissement tel que :

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

  • Vérifiez si quelque chose bloque AUTOVACUUM (par exemple, un ID de transaction bloqué).
  • Mesurez la vitesse d'AUTOVACUUM et, éventuellement, augmentez-la.
  • Si nécessaire, exécutez manuellement quelques commandes VACUUM supplémentaires

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.

Vérifier si un ID de transaction est 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 comme visible pour toutes les transactions) les ID de transaction créés après le démarrage de la plus ancienne transaction en cours d'exécution, en raison des règles de contrôle de simultanéité multiversion (MVCC). Dans des cas extrêmes, ces transactions peuvent devenir si anciennes qu'elles empêchent VACUUMde 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. En général, des avertissements apparaissent également dans le fichier journal, indiquant AVERTISSEMENT : Le plus ancien xmin est largement dépassé.

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 l'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 d'une transaction orpheline : effectue un rollback de la transaction orpheline.
  • 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.

La requête suivante renvoie l'âge de la transaction la plus ancienne pour chacun des scénarios ci-dessus, plus 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(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;

Si l'une des valeurs *_left rapportées est proche ou inférieure à un million de la réinitialisation – un million est la limite de protection contre la réinitialisation lorsque PostgreSQL cesse d'accepter de nouvelles commandes d'écriture – lisez d'abord la section Comment supprimer les blocages de VACUUM, sinon passez à la section Paramétrage de VACUUM.

Par exemple, si la requête ci-dessus renvoie :

┌─[ 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_prepare_xact_left" ne sont pas inférieurs à la limite de protection d'un million contre la réinitialisation, vous devez d'abord supprimer les blocages pour que VACUUM puisse progresser.

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 l'exécuter en tant qu'utilisateur postgres, puis exécuter la commande suivante avant que les commandes ci-dessus ne soient disponibles :

GRANT pg_signal_backend TO postgres;

Si la transaction incriminée appartient à l'un des utilisateurs système (commençant par 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 qui s'exécute depuis longtemps, et l'annuler ou la terminer pour débloquer l'ingestion de données, sélectionnez d'abord quelques-unes des requêtes les plus anciennes (LIMIT 10 est présent par commodité pour que le résultat tienne à l'écran, vous devrez peut-être répéter cette opération après avoir corrigé 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 en utilisant SELECT pg_terminate_backend(pid);, où pid correspond au pid de la requête précédente.

Préparation d'une transaction orpheline

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 un rollback ou un commit si vous êtes sûr de la ou des transactions orphelines les plus anciennes à l'aide de l'argument gid de la dernière requête – dans ce cas, trx_id_pin – en guise d'ID de transaction :

ROLLBACK PREPARED 'trx_id_pin';

ou

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 de progression, vous pouvez SUPPRIMER l'instance dupliquée (depuis gcloud ou Google Cloud Console).

Vérifiez d'abord que l'instance dupliquée n'est pas désactivée, comme décrit dans la page Gérer les instances dupliquées avec accès en lecture. Si l'instance dupliquée est désactivée, réactivez-la d'abord. Si cela résout la latence, aucune autre action n'est nécessaire. Si la latence reste élevée, 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'obtenir 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, pg_replication_slots est opérationnel (âge == 59), donc aucune action n'est requise de votre part. Si l'âge était proche de deux milliards, vous devriez supprimer l'emplacement. Il n'existe aucun moyen simple d'identifier l'instance dupliquée dans le cas où la requête renvoie plusieurs enregistrements ; il faut donc les vérifier toutes si une transaction de longue durée est effectuée sur une instance dupliquée.

Transaction de longue durée sur une instance dupliquée, avec hot_standby_feedback = on

Vérifiez sur les instances dupliquées la transaction la plus ancienne en cours d'exécution, puis annulez-la (sur l'instance dupliquée).

Dans la vue pg_stat_replication, la colonne backend_xmin contient le TXID le plus ancien 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 sur l'instance dupliquée.

Vous pouvez également redémarrer l'instance dupliquée.

Paramétrer VACUUM (version courte)

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 réussites du nettoyage de l'index. Si possible, il doit être assez grand pour stocker tous les ID de lignes mortes dans une table que la fonction vacuum nettoiera. Lorsque vous définissez cette valeur, tenez compte du fait qu'il s'agit de la quantité maximale de mémoire locale que chaque vacuum en cours d'exécution peut allouer. Veillez donc à ne pas autoriser plus que ce qui est disponible, en gardant une certaine quantité en réserve, au cas où. Si vous laissez la base de données s'exécuter en mode lecture seule, vous devez également tenir compte de la mémoire locale utilisée pour les requêtes en lecture seule.

Cela dit, sur la plupart des systèmes, il suffit d'utiliser la valeur maximale (1 Go ou 1 048 576 Ko, comme illustré 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 dans le livre blanc sur la fonction vacuum.

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 ingestions de données lancées automatiquement par l'ingestion automatique de données. Notez que cette vue présente les ingestions de données exécutées dans toutes les bases de données ; pour les tables (relations) issues d'autres bases de données, vous ne pouvez pas rechercher le nom de la table à l'aide de la colonne de vue relid.

Utilisez les requêtes du livre blanc sur Vacuum pour déterminer les bases de données et les tables qui auront besoin d'être aspirées par la suite, et peut-être même de lancer des aspirations manuelles si la VM du serveur est suffisamment puissante et dispose d'une bande passante permettant des processus d'aspiration plus parallèles que ceux lancés par l'ingestion de données automatique.

Paramétrer VACUUM (version détaillée)

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.

Estimation de la vitesse d'analyse de VACUUM

Pour estimer la vitesse d'analyse, vous devez exécuter plusieurs requêtes pour obtenir la variation dans le temps – d'abord pour stocker les valeurs de base, puis pour calculer la variation dans le temps et estimer le délai 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';

Puisque rien ne peut être sauvegardé dans les tables, qui sont déjà en réinitialisation, on utilise set_config(flag, value) pour paramétrer deux options définies par l'utilisateur – save.ts et save.heap_blks_scanned – avec les valeurs actuelles 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. Si plusieurs lignes par base de données et des conditions de filtrage supplémentaires ('AND relid= …'') doivent être ajoutées au 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'ingestion de données est suffisamment rapide ou si le processus peut être accéléré. Notez que le champ remaining_time ne concerne que la phase de recherche de tas de mémoire et que les autres phases prennent également 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 de l'ingestion de données. Beaucoup de facteurs liés à l'ingestion de données sont assez compliqués à estimer et le faire de manière approfondie ici multiplierait la longueur de ce document. La valeur " remaining_time " fournit une estimation utile du niveau de limitation de l'ingestion de données et permet de vérifier si les modifications apportées améliorent la situation.

Accélération de VACUUM

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

Malheureusement, cette valeur n'est pas récupérée par l'ingestion de données en cours. Vous devrez donc 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.

Dans cette situation, on peut aussi 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 de pointeurs de tuple morts).

Si rien d'autre n'utilise la base de données, le paramètre autovacuum_work_mem peut être défini sur ~80% de mémoire libre après shared_buffers, mais gardez à l'esprit qu'il s'agit de la limite supérieure pour chacun des processus de lancement autovacuum. Si vous souhaitez continuer à exécuter des charges de travail en lecture seule, il est probablement judicieux d'en utiliser moins.

Étape suivante