Utilisation de la fonction autovacuum de PostgreSQL sur Amazon RDS for PostgreSQL - Amazon Relational Database Service

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 for 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, autovacuum est activée sur les instances de base de données Amazon RDS for PostgreSQL que vous créez en utilisant l'un des groupes de paramètres de base de données PostgreSQL par défaut. Il s'agit notamment de default.postgres10, default.postgres11, etc. Tous les groupes de paramètres de base de données PostgreSQL par défaut ont un paramètre rds.adaptive_autovacuum défini sur 1, ce qui permet d'activer la fonction. 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 for PostgreSQL. Pour obtenir des informations de haut niveau, veuillez consulter Bonnes pratiques pour utiliser les moteurs de stockage PostgreSQL.

Allocation de mémoire pour la fonction autovacuum

L'un des paramètres les plus importants influençant les performances de l'autovacuum est le paramètre maintenance_work_mem. Ce paramètre détermine la quantité de mémoire que vous allouez à autovacuum pour analyser une table de base de données et conserver tous les ID des lignes qui vont faire l'objet d'une opération VACUUM. Si vous définissez une valeur trop basse du paramètre maintenance_work_mem, le processus vacuum pourrait avoir à analyser la table plusieurs fois pour effectuer son travail. Ces nombreuses analyses peuvent avoir un impact négatif sur les performances.

Lorsque vous faites des calculs pour déterminer la valeur du paramètre maintenance_work_mem, gardez les deux choses suivantes à l'esprit :

  • L'unité par défaut est le kilo-octet (Ko) pour ce paramètre.

  • Le paramètre maintenance_work_mem fonctionne en conjonction avec le paramètre autovacuum_max_workers. Si vous avez beaucoup de petites tables, allouez plus de autovacuum_max_workers et moins de maintenance_work_mem. Si vous avez de grandes tables (par exemple, 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. Chaque autovacuum_max_workers peut utiliser la mémoire que vous allouez. Ainsi, assurez-vous que la combinaison des processus de travail et de la mémoire est égale à la mémoire totale que vous souhaitez allouer.

De manière générale, pour les hôtes volumineux, affectez au paramètre maintenance_work_mem une valeur comprise entre un et deux gigaoctets (entre 1 048 576 et 2 097 152 Ko). Pour les hôtes extrêmement volumineux, affectez à ce paramètre une valeur comprise entre deux et quatre gigaoctets (entre 2 097 152 et 4 194 304 Ko). La valeur que vous définissez pour ce paramètre dépend de la charge de travail. Amazon RDS a mis à jour sa valeur par défaut pour ce paramètre en un nombre de kilo-octets calculé comme suit.

GREATEST({DBInstanceClassMemory/63963136*1024},65536).

Réduction de la probabilité de renvoi à la ligne 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 renvoi à la ligne de l'ID de transaction. Pour résoudre ce problème, RDS for PostgreSQL fournit un mécanisme qui adapte automatiquement les valeurs des paramètres de la fonction autovacuum. Le réglage adaptatif des paramètres d'autovacuum est une fonction pour RDS for PostgreSQL. Une explication détaillée du renvoi à la ligne de l'ID de transaction figure dans la documentation PostgreSQL.

Le réglage adaptatif des paramètres autovacuum est activé par défaut pour les instances RDS for PostgreSQL avec le paramètre dynamique rds.adaptive_autovacuum défini sur ON (ACTIVÉ). 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 renvoi à la ligne de l'ID de transaction est encore possible même lorsque Amazon RDS règle les paramètres d'autovacuum. Nous vous encourageons à implémenter une CloudWatch alarme Amazon pour l'encapsulation des identifiants de transaction. Pour de plus amples informations, veuillez consulter l'article Implement an early warning system for transaction ID wraparound in 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 renvoi à la ligne de l'ID de transaction. Chacun de ces ajustements dédie plus de ressources à la fonction d'autovacuum pour éviter le renvoi à la ligne. Amazon RDS met à jour les paramètres suivants associés à la fonction d'autovacuum :

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 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 l'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 non vidées dans une base de données. La colonne datfrozenxid de la ligne pg_database d'une base de données est une limite inférieure appliquée aux ID de transaction normaux qui apparaissent 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 d'ID de transactions, un renvoi à la ligne de l'ID de transaction (XID) se produit 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 (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 non vidé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é. En fonction de la vitesse à laquelle votre base de données utilise les ID de transaction, cette alarme peut indiquer que le système n'a presque plus de temps pour exécuter le processus d'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 informations sur la création d'un processus qui vous avertisse de la présence d'un encapsuleur d'ID de transaction, consultez le billet de blog de base de données AWS 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, veuillez consulter Réalisation d'un gel manuel du processus vacuum.

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 est défini comme suit :

Vacuum-threshold = vacuum-base-threshold + vacuum-scale-factor * number-of-tuples

où le vacuum base threshold estautovacuum_vacuum_threshold, l'vacuum scale factorest autovacuum_vacuum_scale_factor et l'number of tuplesestpg_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 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 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.

  2. 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;
  3. 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 à maintenance_work_mem pour la session en cours.

    SET maintenance_work_mem='2 GB'; SET
  4. 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
  5. Dans la première session, si autovacuum provoquait un blocage de la session vaccum, vous voyez dans pg_stat_activity que l'attente a la valeur « T » pour votre session vacuum. Dans ce cas, vous devez mettre fin au processus autovacuum comme suit.

    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.

  6. 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, 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.

  2. 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;
  3. 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
  4. 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.

  5. 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 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 pg_stat_user_indexes, 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 (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 pg_stat_database

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)

Mise à vide d'une table le plus rapidement possible

RDS for 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 renvoi à la ligne 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 for PostgreSQL versions 12 et ultérieures, vous pouvez utiliser VACUUM avec la clause INDEX_CLEANUP.

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.

Note

Ignorer régulièrement le nettoyage de l'index peut entraîner un gonflement de l'index, ce qui a un impact sur les performances d'analyse globales. Il est recommandé d'utiliser la procédure précédente uniquement pour empêcher le renvoi à la ligne de l'ID de transaction.

RDS for PostgreSQL versions 11 et ultérieures

Toutefois, dans RDS for 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 renvoi à la ligne 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 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 :

Définition des paramètres d'autovacuum au niveau de la table

Les paramètres de stockage 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.

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 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 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.