Résolution des bloqueurs de vacuum identifiables dans RDS pour PostgreSQL - Amazon Relational Database Service
Instruction activeÉtat Idle in transaction (Transaction inactive)Transaction préparéeEmplacement de réplication logiqueRéplicas en lectureTables temporaires

Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

Résolution des bloqueurs de vacuum identifiables dans RDS pour PostgreSQL

L’autovacuum effectue des opérations vacuum agressives et abaisse l’âge des ID de transaction en dessous du seuil spécifié par le paramètre autovacuum_freeze_max_age de votre instance RDS. Vous pouvez suivre cet âge à l’aide de la métrique MaximumUsedTransactionIDs d’Amazon CloudWatch.

Pour trouver le paramètre autovacuum_freeze_max_age (dont la valeur par défaut est de 200 millions d’ID de transaction) pour votre instance Amazon RDS, vous pouvez utiliser la requête suivante :

SELECT
    TO_CHAR(setting::bigint, 'FM9,999,999,999') autovacuum_freeze_max_age
FROM
    pg_settings
WHERE
    name = 'autovacuum_freeze_max_age';

Notez que postgres_get_av_diag() recherche uniquement les bloqueurs de vacuum agressifs quand l’âge dépasse le seuil de 500 millions d’ID de transaction pour l’autovacuum adaptatif d’Amazon RDS. Pour que postgres_get_av_diag() détecte un bloqueur, celui-ci doit dater d’au moins 500 millions de transactions.

La fonction postgres_get_av_diag() identifie les types de bloqueurs suivants :

Instruction active

Dans PostgreSQL, une instruction active est une instruction SQL qui est exécutée par la base de données. Cela inclut les requêtes, les transactions ou les opérations en cours. Lors de la surveillance via pg_stat_activity, la colonne d’état indique que le processus avec le PID correspondant est actif.

La fonction postgres_get_av_diag() affiche une sortie similaire à la suivante lorsqu’elle identifie une instruction active.

blocker               | Active statement
database              | my_database
blocker_identifier    | SELECT pg_sleep(20000);
wait_event            | Timeout:PgSleep
autovacuum_lagging_by | 568,600,871
suggestion            | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_terminate_backend (29621);"}

Action suggérée

En suivant les instructions de la colonne suggestion, l’utilisateur peut se connecter à la base de données dans laquelle l’instruction active se trouve et, comme indiqué dans la colonne suggested_action, il est conseillé de vérifier attentivement l’option permettant de mettre fin à la session. Si la résiliation est sûre, vous pouvez utiliser la fonction pg_terminate_backend() pour résilier la session. Cette action peut être effectuée par un administrateur (tel que le compte principal RDS) ou par un utilisateur disposant du privilège pg_terminate_backend() requis.

Avertissement

Une session résiliée annule (ROLLBACK) les modifications apportées. En fonction de vos besoins, vous souhaiterez peut-être réexécuter l’instruction. Cependant, il est recommandé de ne le faire qu’une fois que le processus d’autovacuum a terminé son opération de vacuum agressif.

État Idle in transaction (Transaction inactive)

Une instruction idle in transaction fait référence à toute session qui a ouvert une transaction explicite (par exemple en émettant une instruction BEGIN), qui a effectué un certain travail et qui attend maintenant que le client transmettre plus de travail ou signale la fin de la transaction en émettant une instruction COMMIT, ROLLBACK, ou END (ce qui se traduirait par un COMMIT implicite).

La fonction postgres_get_av_diag() affiche une sortie similaire à la suivante lorsqu’elle identifie une instruction idle in transaction comme un bloqueur.

blocker               | idle in transaction
database              | my_database
blocker_identifier    | INSERT INTO tt SELECT * FROM tt;
wait_event            | Client:ClientRead
autovacuum_lagging_by | 1,237,201,759
suggestion            | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_terminate_backend (28438);"}

Action suggérée

Comme indiqué dans la colonne suggestion, vous pouvez vous connecter à la base de données dans laquelle la session idle in transaction est présente et mettre fin à la session à l’aide de la fonction pg_terminate_backend(). L’utilisateur peut être votre administrateur (compte principal RDS) ou un utilisateur disposant du privilège pg_terminate_backend().

Avertissement

Une session résiliée annule (ROLLBACK) les modifications apportées. En fonction de vos besoins, vous souhaiterez peut-être réexécuter l’instruction. Cependant, il est recommandé de ne le faire qu’une fois que le processus d’autovacuum a terminé son opération de vacuum agressif.

Transaction préparée

PostgreSQL autorise les transactions qui font partie d’une stratégie de validation en deux phases appelée transactions préparées. Elles sont activées en définissant une valeur différente de zéro pour le paramètre max_prepared_transactions. Les transactions préparées sont conçues pour garantir qu’une transaction est durable et reste disponible même après une panne de base de données, un redémarrage ou une déconnexion du client. Comme les transactions régulières, elles reçoivent un ID de transaction et peuvent affecter l’autovacuum. Si elles restent à l’état préparé, l’autovacuum ne peut pas les geler et cela peut entraîner un bouclage de l’ID de transaction.

Lorsque les transactions restent à l’état préparé indéfiniment sans être résolues par un gestionnaire de transactions, elles deviennent des transactions préparées orphelines. La seule façon de résoudre ce problème est de valider ou d’annuler la transaction à l’aide des commandes COMMIT PREPARED ou ROLLBACK PREPARED, respectivement.

Note

Sachez qu’une sauvegarde effectuée lors d’une transaction préparée contiendra toujours cette transaction après restauration. Reportez-vous aux informations suivantes pour savoir comment localiser et clôturer de telles transactions.

La fonction postgres_get_av_diag() affiche le résultat suivant lorsqu’elle identifie un bloqueur qui est une transaction préparée.

blocker               | Prepared transaction
database              | my_database
blocker_identifier    | myptx
wait_event            | Not applicable
autovacuum_lagging_by | 1,805,802,632
suggestion            | Connect to database "my_database" and consider either COMMIT or ROLLBACK the prepared transaction using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"COMMIT PREPARED 'myptx';",[OR],"ROLLBACK PREPARED 'myptx';"}

Action suggérée

Comme indiqué dans la colonne de suggestions, connectez-vous à la base de données où se trouve la transaction préparée. Sur la base de la colonne suggested_action, examinez attentivement s’il faut exécuter COMMIT ou ROLLBACK, et quelle est l’action appropriée.

Pour surveiller les transactions préparées en général, PostgreSQL propose une vue de catalogue appelée pg_prepared_xacts. Vous pouvez utiliser la requête suivante pour rechercher les transactions préparées.

SELECT
    gid,
    prepared,
    owner,
    database,
    transaction AS oldest_xmin
FROM
    pg_prepared_xacts
ORDER BY
    age(transaction) DESC;

Emplacement de réplication logique

L’objectif d’un emplacement de réplication est de conserver les modifications non consommées jusqu’à ce qu’elles soient répliquées sur un serveur cible. Pour plus d’informations, consultez Réplication logique de PostgreSQL.

Il existe deux types d’emplacement de réplication logique.

Emplacements de réplication logique inactifs

Lorsque la réplication est interrompue, les journaux de transactions non consommés ne peuvent pas être supprimés et l’emplacement de réplication devient inactif. Même si un emplacement de réplication logique inactif n’est pas utilisé par un abonné, il reste sur le serveur, ce qui entraîne la conservation des fichiers WAL et empêche la suppression des anciens journaux de transactions. Cela peut augmenter l’utilisation du disque et empêcher spécifiquement l’autovacuum de nettoyer les tables de catalogue internes, car le système doit empêcher le remplacement des informations LSN. Si rien n’est fait, cela peut entraîner un gonflement du catalogue, une dégradation des performances et un risque accru de vacuum de bouclage, ce qui peut entraîner une durée d’indisponibilité des transactions.

Emplacements de réplication logique actifs mais lents

Parfois, la suppression des tuples inactifs du catalogue est retardée en raison de la dégradation des performances de la réplication logique. Ce retard de réplication ralentit la mise à jour de catalog_xmin et peut entraîner un gonflement du catalogue et un vacuum de bouclage.

La fonction postgres_get_av_diag() affiche une sortie similaire à la suivante lorsqu’elle trouve un emplacement de réplication logique en tant que bloqueur.

blocker               | Logical replication slot
database              | my_database
blocker_identifier    | slot1
wait_event            | Not applicable
autovacuum_lagging_by | 1,940,103,068
suggestion            | Ensure replication is active and resolve any lag for the slot if active. If inactive, consider dropping it using the command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_drop_replication_slot('slot1') FROM pg_replication_slots WHERE active = 'f';"}

Action suggérée

Pour résoudre ce problème, vérifiez la configuration de réplication afin de détecter tout problème lié au schéma cible ou aux données susceptibles de mettre fin au processus d’application. Voici les raisons les plus courantes de ce problème :

  • Colonnes manquantes

  • Types de données incompatibles

  • Non-correspondance des données

  • Table manquante

Si le problème est lié à des problèmes d’infrastructure :

Si votre instance se trouve en dehors du réseau AWS ou sur AWS EC2, demandez à votre administrateur comment résoudre les problèmes liés à la disponibilité ou à l’infrastructure.

Suppression de l’emplacement inactif

Avertissement

Attention : avant de supprimer un emplacement de réplication, assurez-vous qu’il n’est pas en cours de réplication, qu’il est inactif et qu’il est dans un état irrécupérable. La suppression prématurée d’un emplacement peut perturber la réplication ou entraîner une perte de données.

Après avoir confirmé que l’emplacement de réplication n’est plus nécessaire, supprimez-le pour permettre à l’autovacuum de continuer. La condition active = 'f' garantit que seul un emplacement inactif est supprimé.

SELECT pg_drop_replication_slot('slot1') WHERE active ='f'

Réplicas en lecture

Lorsque le paramètre hot_standby_feedback est activé pour les réplicas en lecture Amazon RDS, il empêche l’autovacuum sur la base de données principale de supprimer les lignes inactives qui pourraient encore être nécessaires aux requêtes exécutées sur le réplica en lecture. Cela concerne tous les types de réplicas en lecture physiques, y compris ceux gérés avec ou sans emplacements de réplication. Ce comportement est nécessaire, car les requêtes exécutées sur le réplica de secours nécessitent que ces lignes restent disponibles sur le réplica principal pour éviter les conflits de requêtes et les annulations.

Réplica en lecture avec emplacement de réplication physique

Les réplicas en lecture avec des emplacements de réplication physiques améliorent considérablement la fiabilité et la stabilité de la réplication dans RDS pour PostgreSQL. Ces emplacements garantissent que la base de données principale conserve les fichiers journaux d’écriture anticipée essentiels jusqu’à ce que le réplica les traite, préservant ainsi la cohérence des données même en cas de perturbation du réseau.

À partir de RDS pour PostgreSQL version 14, tous les réplicas utilisent des emplacements de réplication. Dans les versions antérieures, seuls les réplicas entre régions utilisaient des emplacements de réplication.

La fonction postgres_get_av_diag() affiche une sortie similaire à la suivante lorsqu’elle trouve un réplica en lecture avec un emplacement de réplication logique en tant que bloqueur.

blocker               | Read replica with physical replication slot
database              |
blocker_identifier    | rds_us_west_2_db_xxxxxxxxxxxxxxxxxxxxx
wait_event            | Not applicable
autovacuum_lagging_by | 554,080,689
suggestion            | Run the following query on the replica "rds_us_west_2_db_xxxxxxxxxxxxxxxxxxxx" to find the long running query:                           
                      | SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 757989377;                                                       
                      | Review carefully and you may consdier terminating the query on read replica using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.                                 +                      |
suggested_action      | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 757989377;","                                                                                 +
                      | [OR]                                                                                                                                                                                                 +
                      | ","Disable hot_standby_feedback","                                                                                                                                                                   +
                      | [OR]                                                                                                                                                                                                 +
                      | ","Delete the read replica if not needed"}
Réplica en lecture avec réplication de streaming

Amazon RDS permet de configurer des réplicas en lecture sans emplacement de réplication physique dans les anciennes versions, jusqu’à la version 13. Cette approche réduit la surcharge en permettant au système principal de recycler les fichiers WAL de manière plus agressive, ce qui est avantageux dans les environnements où l’espace disque est limité et peut tolérer des retards de réplica occasionnels. Cependant, sans emplacement, le réplica de secours doit rester synchronisé pour éviter de manquer des fichiers WAL. Amazon RDS utilise des fichiers WAL archivés pour aider le réplica à rattraper son retard, mais ce processus nécessite une surveillance attentive et peut être lent.

La fonction postgres_get_av_diag() affiche une sortie similaire à la suivante lorsqu’elle trouve un réplica en lecture de diffusion avec un emplacement de réplication logique en tant que bloqueur.

blocker               | Read replica with streaming replication slot
database              | Not applicable
blocker_identifier    | xx.x.x.xxx/xx
wait_event            | Not applicable
autovacuum_lagging_by | 610,146,760
suggestion            | Run the following query on the replica "xx.x.x.xxx" to find the long running query:                                                                                                                                                         +
                      | SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 348319343;                                                                                                                                                     +
                      | Review carefully and you may consdier terminating the query on read replica using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.                                       +
                      |
suggested_action      | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 348319343;","                                                                                                                        +
                      | [OR]                                                                                                                                                                                                                                        +
                      | ","Disable hot_standby_feedback","                                                                                                                                                                                                          +
                      | [OR]                                                                                                                                                                                                                                        +
                      | ","Delete the read replica if not needed"}

Action suggérée

Comme recommandé dans la colonne suggested_action, examinez attentivement ces options pour débloquer l’autovacuum.

  • Résilier la requête : en suivant les instructions de la colonne de suggestions, vous pouvez vous connecter au réplica en lecture, comme indiqué dans la colonne suggested_action. Il est conseillé de vérifier attentivement l’option permettant de résilier la session. Si la résiliation est considérée comme sûre, vous pouvez utiliser la fonction pg_terminate_backend() pour résilier la session. Cette action peut être effectuée par un administrateur (tel que le compte principal RDS) ou par un utilisateur disposant du privilège pg_terminate_backend() requis.

    Vous pouvez exécuter la commande SQL suivante sur le réplica en lecture pour résilier la requête qui empêche le vacuum sur la base de données principale de nettoyer les anciennes lignes. La valeur de backend_xmin est indiquée dans la sortie de la fonction :

    SELECT
    pg_terminate_backend(pid)
FROM
    pg_catalog.pg_stat_activity
WHERE
    backend_xmin::text::bigint = backend_xmin;

  • Désactiver les commentaires de veille permanente : pensez à désactiver le paramètre hot_standby_feedback s’il entraîne des retards de vacuum importants.

    Le paramètre hot_standby_feedback permet à un réplica en lecture d’informer la base de données principale de son activité de requête, empêchant ainsi la base de données principale d’effectuer une opération de vacuum sur les tables ou les lignes utilisées sur le réplica de secours. Bien que cela garantisse la stabilité des requêtes sur le réplica de secours, cela peut retarder considérablement l’opération de vacuum sur la base de données principale. La désactivation de cette fonction permet à la base de données principale d’effectuer l’opération de vacuum sans attendre que le réplica de secours rattrape le retard. Toutefois, cela peut entraîner des annulations de requêtes ou des échecs sur le réplica de secours s’il tente d’accéder à des lignes qui ont été supprimées par la base de données principale.

  • Supprimer les réplicas en lecture s’ils ne sont pas nécessaires : si le réplica en lecture n’est plus nécessaire, vous pouvez le supprimer. Cela supprime la surcharge de réplication associée et permet au système principal de recycler les journaux de transactions sans être freiné par le réplica.

Tables temporaires

Les tables temporaires, créées à l’aide du mot-clé TEMPORARY, résident dans le schéma temporaire, par exemple pg_temp_xxx, et ne sont accessibles qu’à la session qui les a créées. Les tables temporaires sont supprimées à la fin de la session. Cependant, ces tables sont invisibles pour le processus autovacuum de PostgreSQL et doivent être nettoyées manuellement par la session qui les a créées. Essayer d’effectuer un vacuum sur la table temporaire d’une autre session n’a aucun effet.

Dans des circonstances exceptionnelles, une table temporaire peut exister sans qu’elle appartienne à une session active. Si la session liée à une table temporaire se termine de façon inattendue en raison d’un incident fatal, d’un problème réseau ou d’un événement similaire, il est possible que cette table ne soit pas nettoyée et qu’elle devienne ainsi une table « orpheline ». Lorsque le processus autovacuum de PostgreSQL détecte une table temporaire orpheline, il consigne le message suivant :

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

La fonction postgres_get_av_diag() affiche une sortie similaire à la suivante lorsqu’elle identifie une table temporaire comme un bloqueur. Pour que cette fonction affiche correctement la sortie relative aux tables temporaires, elle doit être exécutée dans la base de données dans laquelle se trouvent ces tables.

blocker               | Temporary table
database              | my_database
blocker_identifier    | pg_temp_14.ttemp
wait_event            | Not applicable
autovacuum_lagging_by | 1,805,802,632
suggestion            | Connect to database "my_database". Review carefully, you may consider dropping temporary table using command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"DROP TABLE ttemp;"}

Action suggérée

Suivez les instructions fournies dans la colonne suggestion de sortie pour identifier et supprimer la table temporaire qui empêche l’exécution de l’autovacuum. Utilisez la commande suivante pour supprimer la table temporaire signalée par postgres_get_av_diag(). Remplacez le nom de la table en fonction de la sortie fournie par la fonction postgres_get_av_diag().

DROP TABLE my_temp_schema.my_temp_table;

La requête suivante peut être utilisée pour identifier les tables temporaires :

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