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.
# Utilisation de la fonction autovacuum de PostgreSQL sur Amazon RDS pour PostgreSQL
Nous vous conseillons vivement d’utiliser la fonction autovacuum afin de maintenir l’intégrité de votre instance de base de données PostgreSQL. La fonction autovacuum automatise le lancement des commandes VACUUM et ANALYZE. Elle vérifie les tables ayant eu un grand nombre de tuples insérés, mis à jour ou supprimés. Après cette vérification, elle récupère le stockage en supprimant les données ou les tuples obsolètes de la base de données PostgreSQL.
Par défaut, la fonction autovacuum est activée sur les instances de base de données RDS pour PostgreSQL que vous créez en utilisant l’un des groupes de paramètres de base de données PostgreSQL par défaut. Les autres paramètres de configuration associés à la fonction autovacuum sont également définis par défaut. Comme ces valeurs par défaut sont relativement génériques, vous pouvez bénéficier du réglage de certains paramètres associés à la fonction d’autovacuum pour votre charge de travail spécifique.
Vous trouverez ci-dessous de plus amples informations sur l’autovacuum et sur la façon de régler certains de ses paramètres sur votre instance de base de données RDS pour PostgreSQL. Pour obtenir des informations générales, consultez [Bonnes pratiques pour utiliser les moteurs de stockage PostgreSQL](CHAP_BestPractices.md#CHAP_BestPractices.PostgreSQL).
**Topics**
+ [Allocation de mémoire pour la fonction autovacuum](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.WorkMemory)
+ [Réduction de la probabilité de bouclage de l’ID de transaction](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AdaptiveAutoVacuuming)
+ [Déterminer si les tables de votre base de données ont besoin d’une opération VACUUM](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.NeedVacuuming.md)
+ [Déterminer les tables actuellement éligibles pour autovacuum](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.EligibleTables.md)
+ [Déterminer si autovacuum est en cours d'exécution et pour combien de temps](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AutovacuumRunning.md)
+ [Réalisation d’un gel manuel du processus vacuum](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.VacuumFreeze.md)
+ [Réindexation d'une table pendant l'exécution du processus autovacuum](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.Reindexing.md)
+ [Gestion de la fonction autovacuum avec de grands index](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.LargeIndexes.md)
+ [Autres paramètres qui affectent la fonction d'autovacuum](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.OtherParms.md)
+ [Définition des paramètres d'autovacuum au niveau de la table](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.TableParameters.md)
+ [Enregistrement des activités d'autovacuum et de vacuum](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.Logging.md)
+ [Comprendre le comportement de l’autovacuum avec les bases de données non valides](appendix.postgresql.commondbatasks.autovacuumbehavior.md)
+ [Identification et résolution des bloqueurs de vacuum agressifs dans RDS pour PostgreSQL](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.md)
## Allocation de mémoire pour la fonction autovacuum
L’un des paramètres les plus importants qui influencent les performances d’autovacuum est [https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM). Dans RDS pour PostgreSQL versions 14 et antérieures, le paramètre `autovacuum_work_mem` est défini sur -1, ce qui indique que le paramètre `maintenance_work_mem` est utilisé à la place. Pour toutes les autres versions, `autovacuum_work_mem` est déterminé par GREATEST (\$1DBInstanceClassMemory/32768\$1, 65536).
Les opérations d'aspiration manuelles utilisent toujours ce `maintenance_work_mem` réglage, le réglage par défaut étant GREATEST (\$1DBInstanceClassMemory/63963136 \$11024\$1, 65536), et il peut également être ajusté au niveau de la session à l'aide de la commande pour des opérations manuelles plus ciblées. `SET` `VACUUM`
`autovacuum_work_mem` détermine la mémoire permettant à l’autovacuum de contenir les identifiants des tuples inactifs (`pg_stat_all_tables.n_dead_tup`) pour les opérations de vacuum d’index.
Lors des calculs visant à déterminer la valeur du paramètre `autovacuum_work_mem`, tenez compte des points suivants :
+ Si vous définissez une valeur trop basse pour ce paramètre, le processus vacuum pourrait avoir à analyser la table plusieurs fois pour mener à bien sa tâche. Ces nombreuses analyses peuvent avoir un impact négatif sur les performances. Pour les instances plus grandes, le réglage de `maintenance_work_mem` ou `autovacuum_work_mem` sur au moins 1 Go peut améliorer les performances des opérations de vacuum des tables contenant un grand nombre de tuples inactifs. Cependant, dans les versions 16 et antérieures de PostgreSQL, l’utilisation de la mémoire de vacuum est limitée à 1 Go, ce qui est suffisant pour traiter environ 179 millions de tuples inactifs en un seul passage. Si une table contient plus de tuples inactifs que cela, l’opération de vacuum doit effectuer plusieurs passages dans les index de la table, ce qui augmente considérablement le temps requis. À partir de la version 17 de PostgreSQL, il n’y a pas de limite de 1 Go et l’autovacuum peut traiter plus de 179 millions de tuples en utilisant des arbres radix.
Un identifiant de tuple a une taille de 6 octets. Pour estimer la mémoire nécessaire afin d’effectuer le vacuum d’un index d’une table, interrogez `pg_stat_all_tables.n_dead_tup` pour chercher le nombre de tuples inactifs, puis multipliez ce nombre par 6 pour déterminer la mémoire requise pour effectuer le vacuum de l’index en un seul passage. Vous pouvez utiliser la requête suivante :
```
SELECT
relname AS table_name,
n_dead_tup,
pg_size_pretty(n_dead_tup * 6) AS estimated_memory
FROM
pg_stat_all_tables
WHERE
relname = 'name_of_the_table';
```
+ Le paramètre `autovacuum_work_mem` fonctionne en conjonction avec le paramètre `autovacuum_max_workers`. Chaque application de travail parmi `autovacuum_max_workers` peut utiliser la mémoire que vous allouez. Si vous avez beaucoup de petites tables, allouez plus de `autovacuum_max_workers` et moins de `autovacuum_work_mem`. Si vous avez de grandes tables (d’une taille supérieure à 100 Go), allouez plus de mémoire et moins de processus de travail. Vous devez avoir alloué suffisamment de mémoire pour pouvoir prendre en charge votre plus grande table. Assurez-vous donc que la combinaison des processus de travail et de la mémoire est égale à la mémoire totale que vous souhaitez allouer.
## Réduction de la probabilité de bouclage de l’ID de transaction
Dans certains cas, les valeurs du groupe de paramètres associées à la fonction autovacuum peuvent ne pas être suffisamment agressives pour empêcher le bouclage de l’ID de transaction. Pour résoudre ce problème, RDS pour PostgreSQL fournit un mécanisme qui adapte automatiquement les valeurs des paramètres d’autovacuum. *Autovacuum adaptatif* est une fonctionnalité pour RDS pour PostgreSQL. Une explication détaillée du [bouclage de l’ID de transaction](https://www.postgresql.org/docs/current/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND) figure dans la documentation PostgreSQL.
L’autovacuum adaptatif est activé par défaut pour les instances RDS pour PostgreSQL avec le paramètre dynamique `rds.adaptive_autovacuum` défini sur ON. Nous vous recommandons vivement de garder cette option activée. Toutefois, pour désactiver le réglage adaptatif des paramètres d’autovacuum, définissez le paramètre `rds.adaptive_autovacuum` sur 0 ou OFF.
Le bouclage de l’ID de transaction reste possible même lorsque Amazon RDS Amazon RDS ajuste les paramètres d’autovacuum. Nous vous encourageons à implémenter une CloudWatch alarme Amazon pour l'encapsulation des identifiants de transaction. Pour plus d'informations, consultez l'article [Implémenter un système d'alerte précoce pour l'encapsulation des identifiants de transaction dans RDS pour PostgreSQL](https://aws.amazon.com/blogs/database/implement-an-early-warning-system-for-transaction-id-wraparound-in-amazon-rds-for-postgresql/) sur le blog de base de données. AWS
Lorsque le réglage adaptatif des paramètres d'aspiration automatique est activé, Amazon RDS commence à ajuster les paramètres d'aspiration automatique lorsque la CloudWatch métrique `MaximumUsedTransactionIDs` atteint la valeur du `autovacuum_freeze_max_age` paramètre ou 500 000 000, la valeur la plus élevée étant retenue.
Amazon RDS continue à ajuster les paramètres pour la fonction autovacuum si une table continue à s’orienter vers le bouclage de l’ID de transaction. Chacun de ces ajustements dédie plus de ressources à la fonction d’autovacuum pour éviter le bouclage. Amazon RDS met à jour les paramètres suivants associés à la fonction d’autovacuum :
+ [autovacuum\$1vacuum\$1cost\$1delay](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY)
+ [autovacuum\$1vacuum\$1cost\$1limit](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-LIMIT)
+ [https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM)
+ [autovacuum\$1naptime](https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-NAPTIME)
RDS modifie ces paramètres seulement si la nouvelle valeur rend la fonction d’autovacuum plus agressive. Ces paramètres sont modifiés dans la mémoire sur l’instance de base de données. Les valeurs figurant dans le groupe de paramètres ne sont pas modifiées. Pour afficher les paramètres en mémoire actuels, utilisez la commande SQL [SHOW](https://www.postgresql.org/docs/current/sql-show.html) de PostgreSQL.
Chaque fois que Amazon RDS modifie l’un de ces paramètres d’autovacuum, il génère un événement pour l’instance de base de données concernée. Cet événement est visible sur AWS Management Console et via l'API Amazon RDS. Une fois que la `MaximumUsedTransactionIDs` CloudWatch métrique est revenue en dessous du seuil, Amazon RDS réinitialise les paramètres relatifs à l'autovacuum en mémoire aux valeurs spécifiées dans le groupe de paramètres. Il génère ensuite un autre événement correspondant à cette modification.
# Déterminer si les tables de votre base de données ont besoin d’une opération VACUUM
Vous pouvez utiliser la requête suivante pour afficher le nombre de transactions dégelées dans une base de données. La `datfrozenxid` colonne de la `pg_database` ligne d'une base de données est la limite inférieure de la transaction normale qui IDs apparaît dans cette base de données. Cette colonne représente le minimum des valeurs `relfrozenxid` par table au sein de la base de données.
```
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) desc limit 20;
```
Par exemple, les résultats de l’exécution de la requête précédente pourraient être les suivants.
```
datname | age
mydb | 1771757888
template0 | 1721757888
template1 | 1721757888
rdsadmin | 1694008527
postgres | 1693881061
(5 rows)
```
Lorsque l'âge d'une base de données atteint 2 milliards de transactions IDs, l'ID de transaction (XID) est contourné et la base de données passe en lecture seule. Vous pouvez utiliser cette requête pour produire une métrique et l’exécuter plusieurs fois par jour. Par défaut, autovacuum est défini pour conserver un âge de transactions inférieur à 200,000,000 ([https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE)).
Un exemple de politique de surveillance peut ressembler à ceci :
+ Définissez la valeur `autovacuum_freeze_max_age` sur 200 millions de transactions.
+ Si une table atteint les 500 millions de transactions dégelées, elle déclenche une alarme de faible gravité. Ce n’est pas une valeur déraisonnable, mais elle peut indiquer que la fonction d’autovacuum ne suit pas.
+ Si l’âge d’une table atteint 1 milliard, cela doit être considéré comme une alarme exigeant une action. En général, il est conseillé de conserver des âges plus proches de `autovacuum_freeze_max_age` pour des raisons de performances. Nous vous recommandons d’enquêter en appliquant les recommandations suivantes.
+ Si une table atteint les 1,5 million de transactions non vidées, elle déclenche une alarme de haute gravité. Selon la rapidité avec laquelle votre base de données utilise les transactions IDs, cette alarme peut indiquer que le système n'a plus le temps d'exécuter Autovacuum. Dans ce cas, nous vous recommandons une résolution immédiate.
Si une table enfreint constamment ces seuils, vous devez continuer à modifier vos paramètres d’autovacuum. Par défaut, l’utilisation manuelle de VACUUM (pour lequel les retards basés sur les coûts sont désactivés) est plus agressive que le processus d’autovacuum par défaut, mais elle est également plus intrusive pour le système dans son ensemble.
Nous vous recommandons la procédure suivante :
+ Gardez tout cela à l’esprit et activez un mécanisme de surveillance afin de connaître l’âge de vos transactions les plus anciennes.
Pour plus d'informations sur la création d'un processus qui vous avertit de l'encapsulation des identifiants de transaction, consultez le billet de blog consacré à la AWS base de données [Implémentation d'un système d'alerte précoce pour l'encapsulation des identifiants de transaction dans Amazon RDS for PostgreSQL](https://aws.amazon.com/blogs/database/implement-an-early-warning-system-for-transaction-id-wraparound-in-amazon-rds-for-postgresql/).
+ Pour les tables plus occupées, procédez régulièrement au gel manuel du processus de vacuum pendant une fenêtre de maintenance, en plus de compter sur la fonction d’autovacuum. Pour plus d’informations sur le gel manuel du processus vacuum, consultez [Réalisation d’un gel manuel du processus vacuum](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.VacuumFreeze.md).
# Déterminer les tables actuellement éligibles pour autovacuum
Souvent, une ou deux tables ont besoin d’une opération VACUUM. Les tables dont la valeur `relfrozenxid` est supérieure au nombre de transactions dans `autovacuum_freeze_max_age` sont toujours ciblées par la fonction d’autovacuum. Sinon, si le nombre de tuples rendus obsolètes depuis la dernière opération VACUUM dépasse le seuil de vacuum, la table est vidée.
Le [seuil d’autovacuum](https://www.postgresql.org/docs/current/static/routine-vacuuming.html#AUTOVACUUM) est défini comme suit :
```
Vacuum-threshold = vacuum-base-threshold + vacuum-scale-factor * number-of-tuples
```
où le `vacuum base threshold` est `autovacuum_vacuum_threshold`, le `vacuum scale factor` est `autovacuum_vacuum_scale_factor` et le `number of tuples` est `pg_class.reltuples`.
Pendant que vous êtes connecté à votre base de données, exécutez la requête suivante pour afficher la liste des tables qu’autovacuum considère comme éligibles pour une action vacuum.
```
WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM
pg_settings WHERE name = 'autovacuum_vacuum_threshold'),
vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM
pg_settings WHERE name = 'autovacuum_vacuum_scale_factor'),
fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age'),
sto AS (select opt_oid, split_part(setting, '=', 1) as param,
split_part(setting, '=', 2) as value from (select oid opt_oid, unnest(reloptions) setting from pg_class) opt)
SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation,
pg_size_pretty(pg_table_size(c.oid)) as table_size,
age(relfrozenxid) as xid_age,
coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age,
(coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) +
coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples)
AS autovacuum_vacuum_tuples, n_dead_tup as dead_tuples FROM
pg_class c join pg_namespace ns on ns.oid = c.relnamespace
join pg_stat_all_tables stat on stat.relid = c.oid join vbt on (1=1) join vsf on (1=1) join fma on (1=1)
left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and c.oid = cvbt.opt_oid
left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and c.oid = cvsf.opt_oid
left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and c.oid = cfma.opt_oid
WHERE c.relkind = 'r' and nspname <> 'pg_catalog'
AND (age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float)
OR coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) +
coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) *
c.reltuples <= n_dead_tup)
ORDER BY age(relfrozenxid) DESC LIMIT 50;
```
# Déterminer si autovacuum est en cours d'exécution et pour combien de temps
Si vous avez besoin de vider manuellement une table, vous devez déterminer si autovacuum est en cours d'exécution. Si c'est le cas, vous devrez peut-être ajuster les paramètres pour le faire fonctionner plus efficacement, ou mettre fin à autovacuum afin de pouvoir exécuter manuellement VACUUM.
Utilisez la requête suivante pour déterminer si autovacuum est en cours d'exécution, pendant combien de temps il a été en cours d'exécution et s'il est en attente sur une autre session.
```
SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query
FROM pg_stat_activity
WHERE upper(query) LIKE '%VACUUM%'
ORDER BY xact_start;
```
Après l'exécution de la requête, vous devez obtenir un résultat similaire à ce qui suit.
```
datname | usename | pid | state | wait_event | xact_runtime | query
--------+----------+-------+--------+------------+-------------------------+--------------------------------------------------------------------------------------------------------
mydb | rdsadmin | 16473 | active | | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound)
mydb | rdsadmin | 22553 | active | | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound)
mydb | rdsadmin | 41909 | active | | 3 days 02:43:54.203349 | autovacuum: VACUUM ANALYZE public.mytable3
mydb | rdsadmin | 618 | active | | 00:00:00 | SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query+
| | | | | | FROM pg_stat_activity +
| | | | | | WHERE query like '%VACUUM%' +
| | | | | | ORDER BY xact_start; +
```
Plusieurs problèmes peuvent occasionner des longueurs d'exécution (plusieurs jours) d'une session autovacuum. Le problème le plus courant est que la valeur de votre paramètre [https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM) est trop basse pour la taille de la table ou pour la fréquence des mises à jour.
Nous vous recommandons d'utiliser la formule suivante pour définir la valeur du paramètre `maintenance_work_mem`.
```
GREATEST({DBInstanceClassMemory/63963136*1024},65536)
```
De courtes sessions autovacuum peuvent également indiquer des problèmes :
+ Cela peut indiquer un nombre `autovacuum_max_workers` insuffisant pour votre charge de travail. Dans ce cas, vous devez indiquer le nombre d'exécutants.
+ Cela peut indiquer une corruption d'index (la fonction d'autovacuum se bloque et redémarre sur la même relation, mais ne progresse pas). Dans ce cas, exécutez un `vacuum freeze verbose table` manuel pour voir la cause exacte.
# Réalisation d’un gel manuel du processus vacuum
Vous pouvez effectuer un gel manuel sur une table pour laquelle un processus vacuum est déjà en cours. C’est utile si vous avez identifié une table avec un âge proche de 2 milliards de transactions (ou supérieur à tous les seuils que vous surveillez).
Les étapes suivantes sont fournies à titre informatif et il existe plusieurs variantes de ce processus. Par exemple, pendant le test, supposons que vous trouviez que la valeur du paramètre [https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM) a été définie trop bas et que vous devez agir immédiatement sur une table. Toutefois, vous ne voulez pas renvoyer l’instance à l’expéditeur pour le moment. À l’aide des requêtes des sections précédentes, vous déterminez quelle table pose problème et remarquez une session autovacuum en cours d’exécution depuis longtemps. Vous savez que vous devez modifier le paramètre `maintenance_work_mem`, mais vous devez également agir immédiatement et effectuer un processus vacuum sur la table concernée. La procédure suivante montre ce que vous devez faire dans cette situation.
**Pour procéder manuellement au gel du processus vacuum**
1. Ouvrez les deux sessions de la base de données contenant la table sur laquelle vous voulez effectuer le processus vacuum. Pour la seconde session, utilisez « écran » ou un autre utilitaire qui gère la session si votre connexion est abandonnée.
1. Dans la première session, obtenez le PID de la session autovacuum en cours d’exécution sur la table.
Exécutez la requête suivante pour obtenir le PID de la session autovacuum.
```
SELECT datname, usename, pid, current_timestamp - xact_start
AS xact_runtime, query
FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY
xact_start;
```
1. Dans la deuxième session, calculez la quantité de mémoire dont vous avez besoin pour cette opération. Dans cet exemple, nous déterminons que nous pouvons nous permettre d’utiliser jusqu’à 2 Go de mémoire pour cette opération. Nous affectons donc 2 Go à [https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM) pour la session en cours.
```
SET maintenance_work_mem='2 GB';
SET
```
1. Dans la deuxième session, émettez une commande `vacuum freeze verbose` pour la table. Le paramètre de mode détaillé est utile, car il vous permet de voir l’activité bien qu’il n’existe actuellement aucun rapport d’avancement de cette opération dans PostgreSQL.
```
\timing on
Timing is on.
vacuum freeze verbose pgbench_branches;
```
```
INFO: vacuuming "public.pgbench_branches"
INFO: index "pgbench_branches_pkey" now contains 50 row versions in 2 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pgbench_branches_test_index" now contains 50 row versions in 2 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pgbench_branches": found 0 removable, 50 nonremovable row versions
in 43 out of 43 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 9347 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Time: 2.765 ms
```
1. Dans la première session, si l’autovacuum provoquait un blocage de la session de vacuum, `pg_stat_activity` indique que l’attente a la valeur `T` pour votre session de vacuum. Dans ce cas, mettez fin au processus d’autovacuum comme suit.
```
SELECT pg_terminate_backend('the_pid');
```
**Note**
Certaines versions antérieures d’Amazon RDS ne peuvent pas mettre fin à un processus d’autovacuum à l’aide de la commande précédente et échouent avec l’erreur suivante : `ERROR: 42501: must be a superuser to terminate superuser process LOCATION: pg_terminate_backend, signalfuncs.c:227`.
À ce stade, votre session commence. L’autovacuum redémarre immédiatement parce que cette table figure probablement tout en haut de sa liste de tâches.
1. Lancez votre commande `vacuum freeze verbose` dans la session 2, puis terminez le processus autovacuum de la session 1.
# Réindexation d'une table pendant l'exécution du processus autovacuum
Si un index a été corrompu, la fonction d'autovacuum continue à traiter la table et échoue. Si vous essayez d'effectuer un processus vacuum manuel dans cette situation, vous recevez un message d'erreur similaire à ce qui suit.
```
postgres=> vacuum freeze pgbench_branches;
ERROR: index "pgbench_branches_test_index" contains unexpected
zero page at block 30521
HINT: Please REINDEX it.
```
Lorsque l'index est corrompu et que la fonction d'autovacuum tente de s'exécuter sur la table, vous vous heurtez à une session autovacuum déjà en cours d'exécution. Lorsque vous émettez une commande [REINDEX](https://www.postgresql.org/docs/current/static/sql-reindex.html), vous retirez un verrou exclusif sur la table. Les opérations d'écriture sont bloquées, ainsi que les opérations de lecture qui utilisent cet index spécifique.
**Pour réindexer une table lorsque la fonction d'autovacuum est en cours d'exécution sur la table**
1. Ouvrez les deux sessions de la base de données contenant la table sur laquelle vous voulez effectuer le processus vacuum. Pour la seconde session, utilisez « écran » ou un autre utilitaire qui gère la session si votre connexion est abandonnée.
1. Dans la première session, obtenez le PID de la session autovacuum en cours d'exécution sur la table.
Exécutez la requête suivante pour obtenir le PID de la session autovacuum.
```
SELECT datname, usename, pid, current_timestamp - xact_start
AS xact_runtime, query
FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY
xact_start;
```
1. Dans la deuxième session, émettez la commande reindex.
```
\timing on
Timing is on.
reindex index pgbench_branches_test_index;
REINDEX
Time: 9.966 ms
```
1. Dans la première session, si autovacuum provoquait un blocage du processus, vous voyez dans `pg_stat_activity` que l'attente a la valeur « T » pour votre session vacuum. Dans ce cas, vous mettrez fin au processus autovacuum.
```
SELECT pg_terminate_backend('the_pid');
```
À ce stade, votre session commence. Il est important de noter que la fonction d'autovacuum redémarre immédiatement parce que cette table figure probablement tout en haut de sa liste de tâches.
1. Lancez votre commande dans la session 2, puis terminez le processus autovacuum de la session 1.
# Gestion de la fonction autovacuum avec de grands index
Dans le cadre de son fonctionnement, la fonction *autovacuum* effectue plusieurs [phases de mise à vide](https://www.postgresql.org/docs/current/progress-reporting.html#VACUUM-PHASES) lorsqu’elle s’exécute sur une table. Avant que la table ne soit nettoyée, tous ses index sont d’abord vidés. Lorsque vous supprimez plusieurs grands index, cette phase consomme beaucoup de temps et de ressources. Par conséquent, il est recommandé de contrôler le nombre d’index d’une table et d’éliminer les index inutilisés.
Pour ce processus, vérifiez d’abord la taille globale de l’index. Déterminez ensuite s’il existe des index potentiellement inutilisés qui peuvent être supprimés comme le montrent les exemples suivants.
**Pour vérifier la taille de la table et de ses index**
```
postgres=> select pg_size_pretty(pg_relation_size('pgbench_accounts'));
pg_size_pretty
6404 MB
(1 row)
```
```
postgres=> select pg_size_pretty(pg_indexes_size('pgbench_accounts'));
pg_size_pretty
11 GB
(1 row)
```
Dans cet exemple, la taille des index est supérieure à celle de la table. Cette différence peut entraîner des problèmes de performances, car les index sont surchargés ou inutilisés, ce qui a une incidence sur la fonction autovacuum ainsi que sur les opérations d’insertion.
**Pour vérifier la présence d’index inutilisés**
À l’aide de la vue [https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW), vous pouvez vérifier la fréquence d’utilisation d’un index avec la colonne `idx_scan`. Dans l’exemple suivant, les index non utilisés ont la valeur `idx_scan` définie sur `0`.
```
postgres=> select * from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;
relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+------------------+-----------------------+----------+--------------+---------------
16433 | 16454 | public | pgbench_accounts | index_f | 6 | 6 | 0
16433 | 16450 | public | pgbench_accounts | index_b | 3 | 199999 | 0
16433 | 16447 | public | pgbench_accounts | pgbench_accounts_pkey | 0 | 0 | 0
16433 | 16452 | public | pgbench_accounts | index_d | 0 | 0 | 0
16433 | 16453 | public | pgbench_accounts | index_e | 0 | 0 | 0
16433 | 16451 | public | pgbench_accounts | index_c | 0 | 0 | 0
16433 | 16449 | public | pgbench_accounts | index_a | 0 | 0 | 0
(7 rows)
```
```
postgres=> select schemaname, relname, indexrelname, idx_scan from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;
schemaname | relname | indexrelname | idx_scan
------------+------------------+-----------------------+----------
public | pgbench_accounts | index_f | 6
public | pgbench_accounts | index_b | 3
public | pgbench_accounts | pgbench_accounts_pkey | 0
public | pgbench_accounts | index_d | 0
public | pgbench_accounts | index_e | 0
public | pgbench_accounts | index_c | 0
public | pgbench_accounts | index_a | 0
(7 rows)
```
**Note**
Ces statistiques sont incrémentielles à partir du moment où elles sont réinitialisées. Supposons que vous disposiez d’un index qui n’est utilisé qu’à la fin d’un trimestre ou uniquement pour un rapport spécifique. Il est possible que cet index n’ait pas été utilisé depuis la réinitialisation des statistiques. Pour plus d’informations, consultez [Statistics Functions](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-FUNCTIONS) (Fonctions statistiques). Les index utilisés pour renforcer l’unicité ne seront pas analysés et ne devraient pas être identifiés comme des index inutilisés. Pour identifier les index inutilisés, vous devez avoir une connaissance approfondie de l’application et de ses requêtes.
Pour vérifier quand les statistiques ont été réinitialisées pour la dernière fois pour une base de données, utilisez [ https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW]( https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW)
```
postgres=> select datname, stats_reset from pg_stat_database where datname = 'postgres';
datname | stats_reset
----------+-------------------------------
postgres | 2022-11-17 08:58:11.427224+00
(1 row)
```
## Vidage d’une table le plus rapidement possible
**RDS pour PostgreSQL versions 12 et ultérieures**
Si vous avez trop d’index dans une grande table, il se peut que votre instance de base de données soit proche du bouclage de l’ID de transaction (XID), c’est-à-dire lorsque le compteur XID revient à zéro. Si elle n’est pas vérifiée, cette situation peut entraîner une perte de données. Toutefois, vous pouvez rapidement vider la table sans nettoyer les index. Dans RDS pour PostgreSQL versions 12 et ultérieures, vous pouvez utiliser VACUUM avec la clause [https://www.postgresql.org/docs/current/sql-vacuum.html](https://www.postgresql.org/docs/current/sql-vacuum.html).
```
postgres=> VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) pgbench_accounts;
INFO: vacuuming "public.pgbench_accounts"
INFO: table "pgbench_accounts": found 0 removable, 8 nonremovable row versions in 1 out of 819673 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 7517
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
```
Si une session de mise à vide automatique est déjà en cours, vous devez y mettre fin pour démarrer le processus VACUUM manuel. Pour plus d’informations sur le gel manuel du processus vacuum, consultez [Réalisation d’un gel manuel du processus vacuum](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.VacuumFreeze.md).
**Note**
Ignorer régulièrement le nettoyage de l’index entraîne un gonflement de l’index, ce qui dégrade les performances des analyses. L’index conserve les lignes inactives et le tableau conserve les pointeurs de ligne inactive. Par conséquent, `pg_stat_all_tables.n_dead_tup` augmente jusqu’à l’exécution d’un autovacuum ou d’un VACUUM manuel avec nettoyage d’index. Une bonne pratique consiste à n’utiliser la procédure précédente que pour empêcher le bouclage de l’ID de transaction.
**RDS pour PostgreSQL versions 11 et ultérieures**
Toutefois, dans RDS pour PostgreSQL versions 11 et ultérieures, la seule façon de permettre au processus vacuum de se terminer plus rapidement est de réduire le nombre d’index sur une table. La suppression d’un index peut affecter les plans de requête. Nous vous recommandons de supprimer d’abord les index inutilisés, puis de les supprimer lorsque le bouclage de l’ID de transaction est très proche. Une fois le processus vacuum terminé, vous pouvez recréer ces index.
# Autres paramètres qui affectent la fonction d'autovacuum
Cette requête affiche les valeurs de certains des paramètres qui ont un impact direct sur la fonction d'autovacuum et son comportement. Les [paramètres d'autovacuum](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html) sont décrits en détails dans la documentation PostgreSQL.
```
SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name IN (
'autovacuum_max_workers',
'autovacuum_analyze_scale_factor',
'autovacuum_naptime',
'autovacuum_analyze_threshold',
'autovacuum_analyze_scale_factor',
'autovacuum_vacuum_threshold',
'autovacuum_vacuum_scale_factor',
'autovacuum_vacuum_threshold',
'autovacuum_vacuum_cost_delay',
'autovacuum_vacuum_cost_limit',
'vacuum_cost_limit',
'autovacuum_freeze_max_age',
'maintenance_work_mem',
'vacuum_freeze_min_age');
```
Tous ces paramètres affectent la fonction d'autovacuum, mais les plus importants sont :
+ [maintenance\$1work\$1mem](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAINTENANCE_WORK_MEM)
+ [autovacuum\$1freeze\$1max\$1age](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE)
+ [autovacuum\$1max\$1workers](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-MAX-WORKERS)
+ [autovacuum\$1vacuum\$1cost\$1delay](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY)
+ [autovacuum\$1vacuum\$1cost\$1limit](https://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-LIMIT)
# Définition des paramètres d'autovacuum au niveau de la table
Les [paramètres de stockage](https://www.postgresql.org/docs/current/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS) liés à la fonction d'autovacuum peuvent être définis au niveau de la table, ce qui peut être plus judicieux que de modifier le comportement de toute la base de données. Pour les grandes tables, vous devrez peut-être définir des paramètres agressifs et il est déconseillé de faire en sorte que la fonction d'autovacuum se comporte de cette manière pour toutes les tables.
La requête suivante affiche les tables qui ont actuellement des options en place au niveau de la table.
```
SELECT relname, reloptions
FROM pg_class
WHERE reloptions IS NOT null;
```
Par exemple, cela peut être utile sur les tables qui sont beaucoup plus grandes que le reste de vos tables. Supposez que vous avez une table de 300 Go et 30 autres tables de moins de 1 Go. Dans ce cas, vous pouvez définir des paramètres spécifiques pour votre grande table afin de ne pas modifier le comportement de l'intégralité de votre système.
```
ALTER TABLE mytable set (autovacuum_vacuum_cost_delay=0);
```
Cela permet de désactiver le retard d'autovacuum basé sur les coûts pour cette table au détriment d'une plus grande utilisation des ressources sur votre système. Normalement, l'autovacuum s'arrête pour `autovacuum_vacuum_cost_delay` à chaque fois que `autovacuum_cost_limit` est atteinte. Pour plus d'informations, veuillez consulter la documentation PostgreSQL concernant le [processus de vacuum basé sur les coûts](https://www.postgresql.org/docs/current/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST).
# Enregistrement des activités d'autovacuum et de vacuum
Les informations sur les activités d'autovacuum sont envoyées au `postgresql.log` en fonction du niveau spécifié dans le paramètre `rds.force_autovacuum_logging_level`. Voici les valeurs autorisées pour ce paramètre et les versions de PostgreSQL pour lesquelles cette valeur est le paramètre par défaut :
+ `disabled` (PostgreSQL 10, PostgreSQL 9.6)
+ `debug5`, `debug4`, `debug3`, `debug2`, `debug1`
+ `info` (PostgreSQL 12, PostgreSQL 11)
+ `notice`
+ `warning` (PostgreSQL versions 13 et ultérieures)
+ `error`, journal, `fatal`, `panic`
`rds.force_autovacuum_logging_level` fonctionne avec le paramètre `log_autovacuum_min_duration`. La valeur du paramètre `log_autovacuum_min_duration` est le seuil (en millisecondes) au-dessus duquel les actions autovacuum sont enregistrées. Une valeur de `-1` n'enregistre rien, tandis qu'une valeur de 0 enregistre toutes les actions. Comme avec `rds.force_autovacuum_logging_level`, valeurs par défaut pour `log_autovacuum_min_duration` dépendent de la version, comme suit :
+ `10000 ms` : PostgreSQL 14, PostgreSQL 13, PostgreSQL 12 et PostgreSQL 11
+ `(empty)` : aucune valeur par défaut pour PostgreSQL 10 et PostgreSQL 9.6
Nous vous recommandons de définir le `rds.force_autovacuum_logging_level` à la valeur `WARNING`. Nous vous recommandons également de définir `log_autovacuum_min_duration` à une valeur comprise entre 1000 et 5000. Un paramètre de 5000 journaux d'activité qui prend plus de 5000 millisecondes. Tout paramètre autre que -1 enregistre également les messages si l'action autovacuum est ignorée en raison d'un verrouillage en conflit ou d'une perte simultanée de relations. Pour plus d'informations, veuillez consulter [Action Vacuum automatique](https://www.postgresql.org/docs/current/runtime-config-autovacuum.html) dans la documentation PostgreSQL.
Pour résoudre les problèmes, vous pouvez modifier le paramètre `rds.force_autovacuum_logging_level` à l'un des niveaux de débogage, de `debug1` jusqu'à `debug5` pour obtenir les informations les plus détaillées. Nous vous recommandons d'utiliser les paramètres de débogage pendant de courtes périodes et à des fins de dépannage uniquement. Pour en savoir plus, veuillez consulter la rubrique [Quand journaliser](https://www.postgresql.org/docs/current/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN) dans la documentation de PostgreSQL.
**Note**
PostgreSQL permet au compte `rds_superuser` d'afficher les sessions autovacuum dans `pg_stat_activity`. Par exemple, vous pouvez identifier et mettre fin à la session qui bloque l'exécution d'une commande ou empêche la commande de s'exécuter plus lentement qu'une commande vacuum exécutée manuellement.
# Comprendre le comportement de l’autovacuum avec les bases de données non valides
Une nouvelle valeur `-2` est introduite dans la colonne `datconnlimit` du catalogue `pg_database` pour indiquer que les bases de données qui ont été interrompues au milieu de l’opération DROP DATABASE ne sont pas valides.
Cette nouvelle valeur est disponible dans les versions suivantes de RDS pour PostgreSQL :
+ Version 15.4 et toutes les versions ultérieures
+ 14.9 et versions ultérieures
+ 13.12 et versions ultérieures
+ 12.16 et versions ultérieures
+ 11.21 et versions ultérieures
Les bases de données non valides n’affectent pas la capacité de la fonction d’autovacuum à geler la fonctionnalité des bases de données valides. Autovacuum ignore les bases de données non valides. Par conséquent, les opérations de vacuum régulières continueront à fonctionner correctement et efficacement pour toutes les bases de données valides de votre environnement PostgreSQL.
**Topics**
+ [Surveillance de l’ID de transaction](#appendix.postgresql.commondbatasks.autovacuum.monitorxid)
+ [Ajustement de la requête de surveillance](#appendix.postgresql.commondbatasks.autovacuum.monitoradjust)
+ [Résolution du problème de base de données non valide](#appendix.postgresql.commondbatasks.autovacuum.connissue)
## Surveillance de l’ID de transaction
Cette fonction `age(datfrozenxid)` est couramment utilisée pour surveiller l’âge des ID de transaction (XID) des bases de données afin d’empêcher le bouclage de l’ID de transaction.
Les bases de données non valides étant exclues de l’autovacuum, leur compteur d’ID de transaction (XID) peut atteindre la valeur maximale de `2 billion`, revenir à `- 2 billion` et poursuivre ce cycle indéfiniment. Une requête typique pour surveiller le bouclage de l’ID de transaction peut ressembler à ceci :
```
SELECT max(age(datfrozenxid)) FROM pg_database;
```
Cependant, avec l’introduction de la valeur -2 pour `datconnlimit`, les bases de données non valides peuvent fausser les résultats de cette requête. Étant donné que ces bases de données ne sont pas valides et ne doivent pas faire l’objet de contrôles de maintenance réguliers, elles peuvent générer des faux positifs, ce qui vous laisse penser que `age(datfrozenxid)` est supérieur à ce qu’il est réellement.
## Ajustement de la requête de surveillance
Pour garantir une surveillance précise, vous devez ajuster votre requête de surveillance afin d’exclure les bases de données non valides. Suivez cette requête recommandée :
```
SELECT
max(age(datfrozenxid))
FROM
pg_database
WHERE
datconnlimit <> -2;
```
Cette requête garantit que seules les bases de données valides sont prises en compte dans le calcul `age(datfrozenxid)`, ce qui reflète fidèlement l’âge des ID de transaction dans l’environnement PostgreSQL.
## Résolution du problème de base de données non valide
Lorsque vous essayez de vous connecter à une base de données non valide, vous pouvez rencontrer un message d’erreur similaire à ce qui suit :
```
postgres=> \c db1
connection to server at "mydb.xxxxxxxxxx.us-west-2.rds.amazonaws.com" (xx.xx.xx.xxx), port xxxx failed: FATAL: cannot connect to invalid database "db1"
HINT: Use DROP DATABASE to drop invalid databases.
Previous connection kept
```
En outre, si le paramètre `log_min_messages` est défini sur `DEBUG2` ou supérieur, vous remarquerez peut-être que les entrées de journal suivantes indiquent que le processus autovacuum ignore la base de données non valide :
```
2024-07-30 05:59:00 UTC::@:[32000]:DEBUG: autovacuum: skipping invalid database "db6"
2024-07-30 05:59:00 UTC::@:[32000]:DEBUG: autovacuum: skipping invalid database "db1"
```
Pour résoudre le problème, suivez le `HINT` fourni lors de la tentative de connexion. Connectez-vous à n’importe quelle base de données valide à l’aide de votre compte principal RDS ou d’un compte de base de données doté du rôle `rds_superuser`, puis supprimez les bases de données non valides.
```
SELECT
'DROP DATABASE ' || quote_ident(datname) || ';'
FROM
pg_database
WHERE
datconnlimit = -2 \gexec
```
# Identification et résolution des bloqueurs de vacuum agressifs dans RDS pour PostgreSQL
Dans PostgreSQL, l’opération de vacuum est essentielle pour garantir l’intégrité de la base de données, car elle permet de récupérer de l’espace de stockage et d’éviter les problèmes de [bouclage de l’ID de transaction.](https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND) Cependant, il peut arriver que l’opération de vacuum ne fonctionne pas comme vous le souhaitez, ce qui peut entraîner une dégradation des performances ou une surcharge de l’espace de stockage et même avoir un impact sur la disponibilité de votre instance de base de données par bouclage de l’ID de transaction. Il est donc essentiel d’identifier et de résoudre ces problèmes pour optimiser les performances et la disponibilité des bases de données. Lisez [Présentation d’autovacuum dans les environnements Amazon RDS pour PostgreSQL](https://aws.amazon.com/blogs/database/understanding-autovacuum-in-amazon-rds-for-postgresql-environments/) pour en savoir plus sur l’autovacuum.
La fonction `postgres_get_av_diag()` permet d’identifier les problèmes qui empêchent ou retardent la progression du vacuum agressif. Des suggestions sont fournies, qui peuvent inclure des commandes pour résoudre le problème lorsqu’il est identifiable ou des conseils pour des diagnostics supplémentaires lorsque le problème n’est pas identifiable. Les bloqueurs d'aspiration agressifs sont signalés lorsque leur âge dépasse le seuil d'[autoaspiration adaptatif](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AdaptiveAutoVacuuming) de 500 millions de transactions fixé par RDS. IDs
**Quel est l’âge de l’ID de transaction ?**
La `age()` fonction de transaction IDs calcule le nombre de transactions survenues depuis le plus ancien identifiant de transaction dégelé pour une base de données (`pg_database.datfrozenxid`) ou une table (`pg_class.relfrozenxid`). Cette valeur indique l’activité de la base de données depuis la dernière opération de vacuum agressif et met en évidence la charge de travail probable pour les prochains processus VACUUM.
**Qu’est-ce qu’un vacuum agressif ?**
Une opération VACUUM agressive effectue une numérisation complète de toutes les pages d'un tableau, y compris celles qui sont généralement ignorées en mode normal VACUUMs. Cette analyse approfondie vise à « geler » les transactions IDs qui approchent de leur âge maximum, évitant ainsi une situation connue sous le nom d'[encapsulation des identifiants de transaction](https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND).
Pour que `postgres_get_av_diag()` signale un bloqueur, celui-ci doit dater d’au moins 500 millions de transactions.
**Topics**
+ [Installation d’outils de surveillance et de diagnostic d’autovacuum dans RDS pour PostgreSQL](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Installation.md)
+ [Fonctions de postgres\$1get\$1av\$1diag() dans RDS pour PostgreSQL](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Functions.md)
+ [Résolution des bloqueurs de vacuum identifiables dans RDS pour PostgreSQL](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md)
+ [Résolution des bloqueurs de vacuum non identifiables dans RDS pour PostgreSQL](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Unidentifiable_blockers.md)
+ [Résolution des problèmes de performance de vacuum dans RDS pour PostgreSQL](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Performance.md)
+ [Explication des messages NOTICE dans RDS pour PostgreSQL](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.NOTICE.md)
# Installation d’outils de surveillance et de diagnostic d’autovacuum dans RDS pour PostgreSQL
La fonction `postgres_get_av_diag()` est actuellement disponible dans les versions RDS pour PostgreSQL suivantes :
+ 17.2 et versions 17 ultérieures
+ 16.7 et versions 16 ultérieures
+ 15.11 et versions 15 ultérieures
+ 14.16 et versions 14 ultérieures
+ 13.19 et versions 13 ultérieures
Pour pouvoir utiliser `postgres_get_av_diag()`, créez l’extension `rds_tools`.
```
postgres=> CREATE EXTENSION rds_tools ;
CREATE EXTENSION
```
Vérifiez que l’extension est installée.
```
postgres=> \dx rds_tools
List of installed extensions
Name | Version | Schema | Description
----------+---------+-----------+----------------------------------------------------------
rds_tools | 1.8 | rds_tools | miscellaneous administrative functions for RDS PostgreSQL
1 row
```
Vérifiez que la fonction est créée.
```
postgres=> SELECT
proname function_name,
pronamespace::regnamespace function_schema,
proowner::regrole function_owner
FROM
pg_proc
WHERE
proname = 'postgres_get_av_diag';
function_name | function_schema | function_owner
----------------------+-----------------+----------------
postgres_get_av_diag | rds_tools | rds_superuser
(1 row)
```
# Fonctions de postgres\$1get\$1av\$1diag() dans RDS pour PostgreSQL
La fonction `postgres_get_av_diag()` récupère des informations de diagnostic sur les processus autovacuum qui bloquent ou sont en retard dans une base de données RDS pour PostgreSQL. Pour obtenir des résultats précis, la requête doit être exécutée dans la base de données dont l’ID de transaction est le plus ancien. Pour plus d’informations sur l’utilisation de la base de données ayant l’ID de transaction le plus ancien, consultez [Non connecté à la base de données dont l’ID de transaction est le plus ancien](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.NOTICE.md).
```
SELECT
blocker,
DATABASE,
blocker_identifier,
wait_event,
TO_CHAR(autovacuum_lagging_by, 'FM9,999,999,999') AS autovacuum_lagging_by,
suggestion,
suggested_action
FROM (
SELECT
*
FROM
rds_tools.postgres_get_av_diag ()
ORDER BY
autovacuum_lagging_by DESC) q;
```
La fonction `postgres_get_av_diag()` retourne un tableau avec les informations suivantes :
**blocker**
Spécifie la catégorie d’activité de base de données qui bloque le vacuum.
+ [Instruction active](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Active_statement)
+ [État Idle in transaction (Transaction inactive)](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Idle_in_transaction)
+ [Transaction préparée](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Prepared_transaction)
+ [Emplacement de réplication logique](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Logical_replication_slot)
+ [Réplica en lecture avec emplacement de réplication physique](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Read_replicas)
+ [Réplica en lecture avec réplication de streaming](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Read_replicas)
+ [Tables temporaires](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Identifiableblockers.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Temporary_tables)
**database**
Spécifie le nom de la base de données, le cas échéant. Il s’agit de la base de données dans laquelle l’activité est en cours et bloque ou bloquera l’autovacuum. Il s’agit de la base de données à laquelle vous devez vous connecter et sur laquelle vous devez agir.
**blocker\$1identifier**
Spécifie l’identifiant de l’activité qui bloque ou bloquera l’autovacuum. Il peut s’agir d’un ID de processus accompagné d’une instruction SQL, d’une transaction préparée, de l’adresse IP d’un réplica en lecture et du nom de l’emplacement de réplication, logique ou physique.
**wait\$1event**
Spécifie l’[événement d’attente](PostgreSQL.Tuning.md) de la session de blocage et s’applique aux bloqueurs suivants :
+ Instruction active
+ État Idle in transaction (Transaction inactive)
**autovacum\$1lagging\$1by**
Spécifie le nombre de transactions pour lesquelles l’autovacuum a pris du retard dans le traitement des éléments en attente par catégorie.
**suggestion**
Spécifie des suggestions pour résoudre le bloqueur. Ces instructions incluent le nom de la base de données dans laquelle l’activité existe, le cas échéant, l’ID de processus (PID) de la session, le cas échéant, et les actions à effectuer.
**suggested\$1action**
Suggère l’action à effectuer pour résoudre le bloqueur.
# 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](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AdaptiveAutoVacuuming) 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 :
**Topics**
+ [Instruction active](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Active_statement)
+ [État Idle in transaction (Transaction inactive)](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Idle_in_transaction)
+ [Transaction préparée](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Prepared_transaction)
+ [Emplacement de réplication logique](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Logical_replication_slot)
+ [Réplicas en lecture](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Read_replicas)
+ [Tables temporaires](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Temporary_tables)
## 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](https://www.postgresql.org/docs/current/sql-prepare-transaction.html). 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](https://www.postgresql.org/docs/current/logical-replication.html) 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 :
+ Problèmes de réseau : [comment résoudre les problèmes liés à une base de données Amazon RDS dans un état réseau incompatible](https://repost.aws/knowledge-center/rds-incompatible-network).
+ La base de données ou l’instance de base de données n’est pas disponible pour les raisons suivantes :
+ L’instance de réplica n’a plus d’espace de stockage : consultez [Instances de base de données Amazon RDS à court de stockage](https://repost.aws/knowledge-center/rds-out-of-storage) pour en savoir plus sur l’ajout d’espace de stockage.
+ Paramètres incompatibles : consultez [Comment puis-je réparer une instance de base de données Amazon RDS bloquée avec le statut de paramètres incompatibles ?](https://repost.aws/knowledge-center/rds-incompatible-parameters) pour plus d’informations sur la façon dont vous pouvez résoudre le problème.
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](USER_PostgreSQL.Replication.ReadReplicas.md), 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](https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT) 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\$1action. 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\$1terminate\$1backend() 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](https://www.postgresql.org/docs/current/sql-createtable.html), créées à l’aide du mot-clé `TEMPORARY`, résident dans le schéma temporaire, par exemple pg\$1temp\$1xxx, 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;
```
# Résolution des bloqueurs de vacuum non identifiables dans RDS pour PostgreSQL
Cette section explore d’autres raisons qui peuvent empêcher l’opération de vacuum de progresser. Ces problèmes ne sont actuellement pas directement identifiables par la fonction `postgres_get_av_diag()`.
**Topics**
+ [Pages non valides](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Invalid_pages)
+ [Incohérence de l’index](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Index_inconsistency)
+ [Taux de transaction exceptionnellement élevé](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.High_transaction_rate)
## Pages non valides
Une erreur de page non valide se produit lorsque PostgreSQL détecte une incompatibilité dans le total de contrôle d’une page lors de l’accès à cette page. Le contenu est illisible, ce qui empêche l’autovacuum de geler les tuples. Cela arrête efficacement le processus de nettoyage. L’erreur suivante est écrite dans le journal de PostgreSQL :
```
WARNING: page verification failed, calculated checksum YYYYY but expected XXXX
ERROR: invalid page in block ZZZZZ of relation base/XXXXX/XXXXX
CONTEXT: automatic vacuum of table myschema.mytable
```
**Détermination du type d’objet**
```
ERROR: invalid page in block 4305910 of relation base/16403/186752608
WARNING: page verification failed, calculated checksum 50065 but expected 60033
```
À partir du message d’erreur, le chemin `base/16403/186752608` fournit les informations suivantes :
+ « base » est le nom du répertoire situé sous le répertoire de données PostgreSQL.
+ « 16403 » est l’OID de la base de données, que vous pouvez consulter dans le catalogue système `pg_database`.
+ « 186752608 » est le `relfilenode`, que vous pouvez utiliser pour rechercher le schéma et le nom de l’objet dans le catalogue système `pg_class`.
En vérifiant le résultat de la requête suivante dans la base de données concernée, vous pouvez déterminer le type d’objet. La requête suivante récupère les informations d’objet pour l’oid 186752608. Remplacez l’OID par celui correspondant à l’erreur que vous avez rencontrée.
```
SELECT
relname AS object_name,
relkind AS object_type,
nspname AS schema_name
FROM
pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE
c.oid = 186752608;
```
Pour plus d’informations, consultez la documentation de PostgreSQL [https://www.postgresql.org/docs/current/catalog-pg-class.html](https://www.postgresql.org/docs/current/catalog-pg-class.html) pour connaître tous les types d’objets pris en charge, indiqués par la colonne `relkind` dans `pg_class`.
**Conseils**
La solution la plus efficace à ce problème dépend de la configuration de votre instance Amazon RDS spécifique et du type de données concerné par la page incohérente.
**Si le type d’objet est un index :**
Il est recommandé de reconstruire l’index.
+ **Utilisation de l’option `CONCURRENTLY`** : avant PostgreSQL version 12, la reconstruction d’un index nécessitait un verrou de table exclusif, limitant ainsi l’accès à la table. Avec PostgreSQL versions 12 et ultérieures, l’option `CONCURRENTLY` permet le verrouillage au niveau des lignes, ce qui améliore considérablement la disponibilité de la table. Voici la commande :
```
REINDEX INDEX ix_name CONCURRENTLY;
```
Bien que l’option `CONCURRENTLY` soit moins perturbatrice, elle peut être plus lente sur les tables très occupées. Envisagez de générer l’index pendant les périodes de faible trafic si possible.
Pour plus d’informations, consultez la documentation de PostgreSQL sur [REINDEX](https://www.postgresql.org/docs/current/sql-reindex.html).
+ **Utilisation de l’option `INDEX_CLEANUP FALSE`** : si les index sont volumineux et que le temps estimé pour les finaliser est significatif, vous pouvez débloquer l’autovacuum en exécutant une opération `VACUUM FREEZE` manuelle tout en excluant les index. Cette fonctionnalité est disponible dans PostgreSQL versions 12 et ultérieures.
Ignorer les index vous permet d’éviter le processus de vacuum de l’index incohérent et d’atténuer le problème de bouclage. Toutefois, cela ne résout pas le problème sous-jacent de page non valide. Pour résoudre complètement le problème de page non valide, vous devrez tout de même reconstruire l’index.
**Si le type d’objet est une vue matérialisée :**
Si une erreur de page non valide se produit sur une vue matérialisée, connectez-vous à la base de données concernée et actualisez-la pour corriger la page non valide :
Actualisez la vue matérialisée :
```
REFRESH MATERIALIZED VIEW schema_name.materialized_view_name;
```
Si l’actualisation échoue, essayez de recréer la vue :
```
DROP MATERIALIZED VIEW schema_name.materialized_view_name;
CREATE MATERIALIZED VIEW schema_name.materialized_view_name AS query;
```
L’actualisation ou la recréation de la vue matérialisée permet de la restaurer sans affecter les données de la table sous-jacente.
**Pour tous les autres types d’objets :**
Pour tous les autres types d’objets, contactez l’assistance AWS.
## Incohérence de l’index
Un index dont la logique est incohérente peut empêcher l’autovacuum de progresser. Les erreurs suivantes ou des erreurs similaires sont journalisées pendant la phase de vacuum de l’index ou lorsque des instructions SQL accèdent à l’index.
```
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
```
**Conseils**
Reconstruisez l’index ou ignorez les index en utilisant `INDEX_CLEANUP` lors d’une opération `VACUUM FREEZE` manuelle. Pour plus d’informations sur la façon de reconstruire l’index, consultez [Si le type d’objet est un index](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Invalid_pages).
+ **Utilisation de l’option CONCURRENTLY** : avant PostgreSQL version 12, la reconstruction d’un index nécessitait un verrou de table exclusif, limitant ainsi l’accès à la table. Avec PostgreSQL versions 12 et ultérieures, l’option CONCURRENTLY permet le verrouillage au niveau des lignes, ce qui améliore considérablement la disponibilité de la table. Voici la commande :
```
REINDEX INDEX ix_name CONCURRENTLY;
```
Bien que l’option CONCURRENTLY soit moins perturbatrice, elle peut être plus lente sur les tables très occupées. Envisagez de générer l’index pendant les périodes de faible trafic si possible. Pour plus d’informations, consultez [REINDEX](https://www.postgresql.org/docs/current/sql-reindex.html) dans la documentation de *PostgreSQL*.
+ **Utilisation de l’option INDEX\$1CLEANUP FALSE** : si les index sont volumineux et que le temps estimé pour les finaliser est significatif, vous pouvez débloquer l’autovacuum en exécutant une opération VACUUM FREEZE manuelle tout en excluant les index. Cette fonctionnalité est disponible dans PostgreSQL versions 12 et ultérieures.
Ignorer les index vous permet d’éviter le processus de vacuum de l’index incohérent et d’atténuer le problème de bouclage. Toutefois, cela ne résout pas le problème sous-jacent de page non valide. Pour résoudre complètement le problème de page non valide, vous devrez tout de même reconstruire l’index.
## Taux de transaction exceptionnellement élevé
Dans PostgreSQL, les taux de transactions élevés peuvent avoir un impact significatif sur les performances de l’autovacuum, ce qui ralentit le nettoyage des tuples inactifs et augmente le risque de bouclage des ID de transaction. Vous pouvez surveiller le taux de transaction en mesurant la différence de `max(age(datfrozenxid))` entre deux périodes, généralement par seconde. En outre, vous pouvez utiliser les métriques de compteur suivantes de RDS Performance Insights pour mesurer le taux de transaction (somme de xact\$1commit et xact\$1rollback), qui est le nombre total de transactions.
| Compteur | Type | Unité | Métrique |
| --- | --- | --- | --- |
| xact\$1commit | Transactions | Validations par seconde | db.Transactions.xact\$1commit |
| xact\$1rollback | Transactions | Restaurations par seconde | db.Transactions.xact\$1rollback |
Une augmentation rapide indique une charge de transactions élevée, qui peut surcharger l’autovacuum, provoquant un gonflement, des conflits de verrouillage et des problèmes de performances potentiels. Cela peut avoir un impact négatif sur le processus d’autovacuum de plusieurs manières :
+ **Activité liée à la table :** la table en question peut faire l’objet d’un volume élevé de transactions, ce qui peut entraîner des retards.
+ **Ressources système :** l’ensemble du système est peut-être surchargé, ce qui rend difficile pour l’autovacuum d’accéder aux ressources nécessaires pour fonctionner efficacement.
Envisagez les stratégies suivantes pour permettre à l’autovacuum de fonctionner plus efficacement et de mener à bien ses tâches dans le délai imparti :
1. Réduisez le taux de transaction si possible. Envisagez de traiter par lots ou de regrouper les transactions similaires dans la mesure du possible.
1. Ciblez les tables fréquemment mises à jour avec une opération `VACUUM FREEZE` manuelle tous les soirs, toutes les semaines ou toutes les deux semaines pendant les heures creuses.
1. Envisagez d’augmenter verticalement votre classe d’instance pour allouer davantage de ressources système afin de gérer le volume élevé de transactions et l’autovacuum.
# Résolution des problèmes de performance de vacuum dans RDS pour PostgreSQL
Cette section décrit les facteurs qui contribuent souvent au ralentissement des performances de vacuum et explique comment résoudre ces problèmes.
**Topics**
+ [Vacuum des index de grande taille](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Large_indexes)
+ [Trop de tables ou de bases de données pour le vacuum](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Multiple_tables)
+ [Un vacuum agressif (pour éviter tout bouclage) est en cours d’exécution](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Aggressive_vacuum)
## Vacuum des index de grande taille
VACUUM fonctionne selon des phases séquentielles : initialisation, analyse des tas, vacuum des index et des tas, nettoyage des index, troncature des tas et nettoyage final. Pendant l’analyse des tas, le processus élague les pages, les défragmente et les gèle. Une fois l’analyse des tas terminée, VACUUM nettoie les index, renvoie les pages vides au système d’exploitation et effectue les dernières tâches de nettoyage, telles que le vacuum de la carte de l’espace libre et la mise à jour des statistiques.
L’opération de vacuum de l’index peut nécessiter plusieurs passages lorsque la capacité `maintenance_work_mem` (ou `autovacuum_work_mem`) est insuffisante pour traiter l’index. Dans PostgreSQL 16 et versions antérieures, une limite de mémoire de 1 Go pour le stockage des ID de tuple inactifs imposait souvent plusieurs passages sur les index volumineux. PostgreSQL 17 introduit `TidStore`, qui alloue de la mémoire de manière dynamique au lieu d’utiliser un tableau à allocation unique. Cela permet de supprimer la contrainte de 1 Go, d’utiliser la mémoire de manière plus efficace et d’éviter d’avoir à analyser plusieurs fois chaque index.
Les index de grande taille peuvent toujours nécessiter plusieurs passages dans PostgreSQL 17 si la mémoire disponible ne peut pas prendre en charge l’intégralité du traitement des index en une seule fois. Généralement, les index de grande taille contiennent davantage de tuples inactifs qui nécessitent plusieurs passages.
**Détection des opérations de vacuum lentes**
La fonction `postgres_get_av_diag()` peut détecter les opérations de vacuum qui sont lentes en raison d’une mémoire insuffisante. Pour plus d’informations sur cette fonction, consultez [Installation d’outils de surveillance et de diagnostic d’autovacuum dans RDS pour PostgreSQL](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Installation.md).
La fonction `postgres_get_av_diag()` émet les notifications suivantes lorsque la mémoire disponible n’est pas suffisante pour terminer l’opération de vacuum de l’index en un seul passage.
**`rds_tools` 1.8**
```
NOTICE: Your database is currently running aggressive vacuum to prevent wraparound and it might be slow.
```
```
NOTICE: The current setting of autovacuum_work_mem is "XXX" and might not be sufficient. Consider increasing the setting, and if necessary, scaling up the Amazon RDS instance class for more memory.
Additionally, review the possibility of manual vacuum with exclusion of indexes using (VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) table_name;).
```
**`rds_tools` 1.9**
```
NOTICE: Your database is currently running aggressive vacuum to prevent wraparound and it might be slow.
```
```
NOTICE: The current setting of autovacuum_work_mem is XX might not be sufficient. Consider increasing the setting to XXX, and if necessary, scaling up the RDS instance class for more
memory. The suggested value is an estimate based on the current number of dead tuples for the table being vacuumed, which might not fully reflect the latest state. Additionally, review the possibility of manual
vacuum with exclusion of indexes using (VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) table_name;). For more information, see
[Working with PostgreSQL autovacuum in the Amazon Amazon RDS User Guide](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html)
.
```
**Note**
La fonction `postgres_get_av_diag()` repose sur `pg_stat_all_tables.n_dead_tup` pour l’estimation de la quantité de mémoire requise pour l’opération de vacuum de l’index.
Lorsque la fonction `postgres_get_av_diag()` identifie une opération de vacuum lente qui nécessite plusieurs analyses d’index en raison d’une capacité `autovacuum_work_mem` insuffisante, elle génère le message suivant :
```
NOTICE: Your vacuum is performing multiple index scans due to insufficient autovacuum_work_mem:XXX for index vacuuming.
For more information, see [Working with PostgreSQL autovacuum in the Amazon Amazon RDS User Guide](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html).
```
**Conseils**
Vous pouvez appliquer les solutions de contournement suivantes à l’aide d’une opération `VACUUM FREEZE` manuelle pour accélérer le gel de la table.
**Augmentation de la mémoire pour l’opération de vacuum**
Comme suggéré par la fonction `postgres_get_av_diag()`, il est conseillé d’augmenter le paramètre `autovacuum_work_mem` pour faire face aux contraintes de mémoire potentielles au niveau de l’instance. Même si `autovacuum_work_mem` est un paramètre dynamique, il est important de noter que pour que le nouveau paramètre de mémoire prenne effet, le démon autovacuum doit redémarrer ses applications de travail. Pour ce faire :
1. Vérifiez que le nouveau paramètre est en place.
1. Arrêtez les processus qui exécutent une opération d’autovacuum.
Cette approche garantit que l’allocation de mémoire ajustée est appliquée aux nouvelles opérations d’autovacuum.
Pour des résultats plus immédiats, pensez à effectuer une opération `VACUUM FREEZE` manuelle avec un paramètre `maintenance_work_mem` augmenté au cours de votre session :
```
SET maintenance_work_mem TO '1GB';
VACUUM FREEZE VERBOSE table_name;
```
Si vous utilisez Amazon RDS et que vous constatez que vous avez besoin de mémoire supplémentaire pour prendre en charge des valeurs plus élevées pour `maintenance_work_mem` ou `autovacuum_work_mem`, envisagez de passer à une classe d’instance avec plus de mémoire. Cela peut fournir les ressources nécessaires pour améliorer les opérations de vacuum manuelles et automatiques, ce qui se traduit par une amélioration des performances globales du vacuum et des bases de données.
**Désactivation d’INDEX\$1CLEANUP**
L’opération `VACUUM` manuelle de PostgreSQL 12 et versions ultérieures permet d’ignorer la phase de nettoyage de l’index, tandis que l’autovacuum d’urgence dans PostgreSQL 14 et versions ultérieures le fait automatiquement en fonction du paramètre [https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-VACUUM-FAILSAFE-AGE](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-VACUUM-FAILSAFE-AGE).
**Avertissement**
Ignorer le nettoyage de l’index peut entraîner un gonflement de l’index et avoir un impact négatif sur les performances des requêtes. Pour pallier ce problème, pensez à réindexer les index concernés ou à en effectuer le vacuum pendant une période de maintenance.
Pour obtenir des conseils supplémentaires sur la gestion des index de grande taille, reportez-vous à la documentation sur [Gestion de la fonction autovacuum avec de grands index](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.LargeIndexes.md).
**Opérations de vacuum d’index en parallèle**
À partir de PostgreSQL 13, les index peuvent faire l’objet d’un vacuum et d’un nettoyage en parallèle par défaut avec une opération `VACUUM` manuelle. Un processus de travail de vacuum est alors assigné à chaque index. Cependant, pour que PostgreSQL puisse déterminer si une opération de vacuum peut être exécutée en parallèle, des critères spécifiques doivent être remplis :
+ Il doit y avoir au moins deux index.
+ Le paramètre `max_parallel_maintenance_workers` doit être défini sur au moins 2.
+ La taille de l’index doit dépasser la limite de `min_parallel_index_scan_size`, qui est par défaut de 512 Ko.
Vous pouvez ajuster le paramètre `max_parallel_maintenance_workers` en fonction du nombre de processeurs virtuels disponibles sur votre instance Amazon RDS et du nombre d’index sur la table afin d’optimiser le délai d’exécution de l’opération de vacuum.
Pour plus d’informations, consultez [Opérations de vacuum en parallèle dans Amazon RDS pour PostgreSQL et Amazon Aurora PostgreSQL](https://aws.amazon.com/blogs/database/parallel-vacuuming-in-amazon-rds-for-postgresql-and-amazon-aurora-postgresql/).
## Trop de tables ou de bases de données pour le vacuum
Comme indiqué dans la documentation [The Autovacuum Daemon](https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM') de PostgreSQL, le démon autovacuum fonctionne par le biais de plusieurs processus. Cela inclut un lanceur d’autovacuum permanent chargé de démarrer les processus de travail d’autovacuum pour chaque base de données du système. Le lanceur programme ces applications de travail pour qu’elles soient lancées environ toutes les `autovacuum_naptime` secondes par base de données.
Avec « N » bases de données, une nouvelle application de travail commence environ toutes les [`autovacuum_naptime`/N secondes]. Cependant, le nombre total d’applications de travail simultanés est limité par le paramètre `autovacuum_max_workers`. Si le nombre de bases de données ou de tables qui nécessitent un vacuum dépasse cette limite, la base de données ou table suivante est traitée dès qu’une application de travail est disponible.
Lorsque de nombreuses tables ou bases de données volumineuses nécessitent un vacuum simultané, toutes les applications de travail d’autovacuum disponibles peuvent être occupées pendant une période prolongée, ce qui retarde la maintenance des autres tables et bases de données. Dans les environnements où les taux de transactions sont élevés, ce goulot d’étranglement peut rapidement s’aggraver et potentiellement entraîner des problèmes de vacuum de bouclage au sein de votre instance Amazon RDS.
Lorsque `postgres_get_av_diag()` détecte un nombre élevé de tables ou de bases de données, il fournit la recommandation suivante :
```
NOTICE: Your database is currently running aggressive vacuum to prevent wraparound and it might be slow.
```
```
NOTICE: The current setting of autovacuum_max_workers:3 might not be sufficient. Consider increasing the setting and, if necessary, consider scaling up the Amazon RDS instance class for more workers.
```
**Conseils**
**Augmentation d’autovacuum\$1max\$1workers**
Pour accélérer l’opération de vacuum, nous vous recommandons de régler le paramètre `autovacuum_max_workers` afin d’autoriser un plus grand nombre d’applications de travail d’autovacuum simultanées. Si les problèmes de performances persistent, envisagez d’augmenter verticalement votre instance Amazon RDS vers une classe comportant davantage de processeurs virtuels, ce qui peut améliorer encore les capacités de traitement parallèle.
## Un vacuum agressif (pour éviter tout bouclage) est en cours d’exécution
L’âge de la base de données (MaximumUsedTransactionIDs) dans PostgreSQL ne diminue que lorsqu’un vacuum agressif (pour empêcher tout bouclage) s’est terminé avec succès. Jusqu’à ce que ce vacuum se termine, l’âge continue d’augmenter en fonction du taux de transaction.
La fonction `postgres_get_av_diag()` génère le message `NOTICE` suivant lorsqu’elle détecte un vacuum agressif. Cependant, elle ne déclenche cette sortie qu’après que le vacuum a été actif pendant au moins deux minutes.
```
NOTICE: Your database is currently running aggressive vacuum to prevent wraparound, monitor autovacuum performance.
```
Pour plus d’informations sur le vacuum agressif, consultez [Lorsqu’un vacuum agressif est déjà en cours d’exécution](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.NOTICE.md).
Vous pouvez vérifier si un vacuum agressif est en cours à l’aide de la requête suivante :
```
SELECT
a.xact_start AS start_time,
v.datname "database",
a.query,
a.wait_event,
v.pid,
v.phase,
v.relid::regclass,
pg_size_pretty(pg_relation_size(v.relid)) AS heap_size,
(
SELECT
string_agg(pg_size_pretty(pg_relation_size(i.indexrelid)) || ':' || i.indexrelid::regclass || chr(10), ', ')
FROM
pg_index i
WHERE
i.indrelid = v.relid
) AS index_sizes,
trunc(v.heap_blks_scanned * 100 / NULLIF(v.heap_blks_total, 0)) AS step1_scan_pct,
v.index_vacuum_count || '/' || (
SELECT
count(*)
FROM
pg_index i
WHERE
i.indrelid = v.relid
) AS step2_vacuum_indexes,
trunc(v.heap_blks_vacuumed * 100 / NULLIF(v.heap_blks_total, 0)) AS step3_vacuum_pct,
age(CURRENT_TIMESTAMP, a.xact_start) AS total_time_spent_sofar
FROM
pg_stat_activity a
INNER JOIN pg_stat_progress_vacuum v ON v.pid = a.pid;
```
Vous pouvez déterminer s’il s’agit d’un vacuum agressif (pour éviter tout bouclage) en vérifiant la colonne de requête dans la sortie. L’expression « pour éviter tout bouclage » indique qu’il s’agit d’un vacuum agressif.
```
query | autovacuum: VACUUM public.t3 (to prevent wraparound)
```
Supposons, par exemple, que vous disposiez d’un bloqueur à l’âge d’un milliard de transactions et d’une table nécessitant un vacuum agressif pour éviter tout bouclage au même âge de transaction. De plus, il existe un autre bloqueur à l’âge de 750 millions de transactions. Après avoir éliminé le bloqueur à l’âge d’un milliard de transactions, l’âge des transactions ne tombera pas immédiatement à 750 millions. Il restera élevé jusqu’à ce que la table nécessitant un vacuum agressif ou toute transaction dont l’âge est supérieur à 750 millions soit finalisée. Au cours de cette période, l’âge des transactions de votre cluster PostgreSQL continuera d’augmenter. Une fois le processus de vacuum terminé, l’âge des transactions tombe à 750 millions, mais recommence à augmenter jusqu’à ce que l’opération de vacuum soit terminée. Ce cycle se poursuit tant que ces conditions persistent, jusqu’à ce que l’âge des transactions atteigne finalement le niveau configuré pour votre instance Amazon RDS, spécifié par `autovacuum_freeze_max_age`.
# Explication des messages NOTICE dans RDS pour PostgreSQL
La fonction `postgres_get_av_diag()` fournit les messages NOTICE suivants :
**Lorsque l’âge n’a pas encore atteint le seuil de surveillance**
Le seuil de surveillance `postgres_get_av_diag()` pour identifier les bloqueurs est de 500 millions de transactions par défaut. Si `postgres_get_av_diag()` génère le message NOTICE suivant, l’âge de la transaction n’a pas encore atteint ce seuil.
```
NOTICE: postgres_get_av_diag() checks for blockers that prevent aggressive vacuums only, it does so only after exceeding dvb_threshold which is 500,000,000 and age of this PostgreSQL cluster is currently at 2.
```
**Non connecté à la base de données dont l’ID de transaction est le plus ancien**
La fonction `postgres_get_av_diag()` fournit le résultat le plus précis lorsqu’elle est connectée à la base de données dont l’ID de transaction est le plus ancien. La base de données dont l’âge de l’ID de transaction est le plus ancien indiqué par `postgres_get_av_diag()` sera différente de « my\$1database » dans votre cas. Si vous n’êtes pas connecté à la bonne base de données, le message NOTICE suivant est généré :
```
NOTICE: You are not connected to the database with the age of oldest transaction ID. Connect to my_database database and run postgres_get_av_diag() for accurate reporting.
```
Il est important de se connecter à la base de données dont l’âge de la transaction est le plus ancien pour les raisons suivantes :
+ **Identification des bloqueurs de tables temporaires :** les métadonnées des tables temporaires étant spécifiques à chaque base de données, elles se trouvent généralement dans la base de données dans laquelle elles ont été créées. Toutefois, si une table temporaire se trouve être le principal bloqueur et qu’elle se trouve dans la base de données contenant la transaction la plus ancienne, cela peut prêter à confusion. La connexion à la base de données appropriée garantit l’identification précise du bloqueur de table temporaire.
+ **Diagnostic des vacuums lents :** les métadonnées d’index et les informations relatives au nombre de tables sont spécifiques à chaque base de données et sont nécessaires pour diagnostiquer les problèmes de lenteur du vacuum.
**La base de données dont l’âge de la transaction est le plus ancien se trouve sur une base de données rdsadmin ou template0**
Dans certains cas, les bases de données `rdsadmin` ou `template0` peuvent être identifiées comme étant celles dont l’ID de transaction est le plus ancien. Dans ce cas, `postgres_get_av_diag()` émet le message NOTICE suivant :
```
NOTICE: The database with the age of oldest transaction ID is rdsadmin or template0, reach out to support if the reported blocker is in rdsadmin or template0.
```
Vérifiez que le bloqueur répertorié ne provient d’aucune de ces deux bases de données. Si la présence du bloqueur est signalée dans `rdsadmin` ou `template0`, contactez le support, car ces bases de données ne sont pas accessibles aux utilisateurs et nécessitent une intervention.
Il est très peu probable que la base de données `rdsadmin` ou `template0` contienne un bloqueur principal.
**Lorsqu’un vacuum agressif est déjà en cours d’exécution**
La fonction `postgres_get_av_diag()` est conçue pour signaler lorsqu’un processus de vacuum agressif est en cours d’exécution, mais elle ne déclenche cette sortie que lorsque le vacuum est actif depuis au moins 1 minute. Ce délai intentionnel contribue à réduire les risques de faux positifs. En attendant, la fonction garantit que seuls les vacuums efficaces et significatifs sont signalés, ce qui permet une surveillance plus précise et plus fiable de l’activité du vacuum.
La fonction `postgres_get_av_diag()` génère le message NOTICE suivant lorsqu’elle détecte un ou plusieurs vacuums agressifs en cours.
```
NOTICE: Your database is currently running aggressive vacuum to prevent wraparound, monitor autovacuum performance.
```
Comme indiqué dans le message NOTICE, continuez à surveiller les performances du vacuum. Pour plus d’informations sur le vacuum agressif, consultez [Un vacuum agressif (pour éviter tout bouclage) est en cours d’exécution](Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Performance.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Aggressive_vacuum)
**Lorsque l’autovacuum est désactivé**
La fonction `postgres_get_av_diag()` génère le message NOTICE suivant si l’autovacuum est désactivé sur votre instance de base de données :
```
NOTICE: Autovacuum is OFF, we strongly recommend to enable it, no restart is necessary.
```
L’autovacuum est une fonctionnalité essentielle de votre instance de base de données RDS pour PostgreSQL. Elle garantit le bon fonctionnement de la base de données. Elle supprime automatiquement les anciennes versions de lignes, récupère de l’espace de stockage et évite le gonflement des tables, ce qui contribue à garantir l’efficacité des tables et des index pour des performances optimales. En outre, elle protège contre le bouclage des ID de transaction, qui peut interrompre les transactions sur votre instance Amazon RDS. La désactivation de l’autovacuum peut entraîner une baisse à long terme des performances et de la stabilité de la base de données. Nous vous conseillons de laisser cette fonctionnalité activée en permanence. Pour plus d’informations, consultez [Présentation d’autovacuum dans les environnements RDS pour PostgreSQL](https://aws.amazon.com/blogs/database/understanding-autovacuum-in-amazon-rds-for-postgresql-environments/).
La désactivation de l’autovacuum n’arrête pas les vacuums agressifs. Ils se produiront toujours une fois que vos tables auront atteint le seuil `autovacuum_freeze_max_age`.
**Le nombre de transactions restantes est extrêmement faible**
La fonction `postgres_get_av_diag()` génère le message NOTICE suivant lorsqu’un vacuum de bouclage est imminent. Ce message NOTICE est émis lorsque votre instance Amazon RDS est à 100 millions de transactions avant que de nouvelles transactions ne soient potentiellement rejetées.
```
WARNING: Number of transactions remaining is critically low, resolve issues with autovacuum or perform manual VACUUM FREEZE before your instance stops accepting transactions.
```
Une action immédiate est nécessaire pour éviter toute durée d’indisponibilité de la base de données. Vous devez surveiller de près vos opérations de vacuum et envisager de lancer manuellement une opération `VACUUM FREEZE` sur la base de données concernée afin d’éviter les échecs de transaction.