Utilisation de l'SQLaspirateur automatique Postgre sur Amazon RDS pour Postgre SQL - 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 l'SQLaspirateur automatique Postgre sur Amazon RDS pour Postgre SQL

Nous vous recommandons vivement d'utiliser la fonction autovacuum pour maintenir l'intégrité de votre instance de SQL base de données Postgre. Autovacuum automatise le démarrage 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, il récupère de l'espace de stockage en supprimant les données ou les tuples obsolètes de la base de données SQL Postgre.

Par défaut, l'autovacuum est activé pour les SQL instances de base de données Amazon RDS pour Postgre que vous créez à l'aide de l'un des groupes de paramètres de SQL base de données Postgre par défaut. Il s'agit notamment de default.postgres10, default.postgres11, etc. Tous les groupes de paramètres de SQL base de données Postgre par défaut ont un rds.adaptive_autovacuum paramètre défini sur1, activant ainsi la fonctionnalité. 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 plus d'informations sur l'autovacuum et sur la façon de régler certains de ses paramètres sur votre instance de SQL base RDS de données Postgre. Pour obtenir des informations de haut niveau, veuillez consulter Bonnes pratiques pour travailler avec Postgre SQL.

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 pour contenir toutes les lignes IDs qui seront aspirées. 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 afin qu'il soit calculé en kilo-octets 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 Postgre SQL propose un mécanisme qui adapte automatiquement les valeurs des paramètres d'autovacuum. Le réglage adaptatif des paramètres d'autovacuum est une fonctionnalité RDS de SQL Postgre. Une explication détaillée de l'encapsulation de TransactionID se trouve dans la documentation Postgre. SQL

Le réglage adaptatif des paramètres d'autovacuum est activé par défaut RDS pour les SQL instances Postgre dont le paramètre dynamique est 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 de l'aspirateur automatique, définissez le rds.adaptive_autovacuum paramètre sur 0 ouOFF.

L'encapsulation des identifiants de transaction est toujours possible même lorsque 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 Postgre SQL sur le AWS Blog de base de données.

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 d'ajuster les paramètres de l'aspirateur automatique si une table continue de suivre une tendance à l'encapsulation des identifiants 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 relatifs à l'aspirateur automatique :

RDSmodifie ces paramètres uniquement si la nouvelle valeur rend l'autovacuum plus agressif. 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 Postgre SQL SHOWSQL.

Lorsqu'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 par le biais d'Amazon RDSAPI. 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 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 transactionsIDs, 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 (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é. Selon la rapidité avec laquelle votre base de données utilise les transactionsIDs, 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 VACUUM manuelle (qui permet de désactiver les délais basés sur les coûts) est plus agressive que l'utilisation de l'aspirateur automatique par défaut, mais elle est également plus intrusive pour le système dans son ensemble.

Nous vous recommandons la procédure suivante :

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 devenus obsolètes depuis le dernier VACUUM dépasse le seuil de vide, la table est mise sous vide.

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 rendre plus efficace, ou désactiver temporairement l'aspirateur automatique afin de pouvoir l'exécuter VACUUM manuellement.

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 l'ID de processus (PID) de la session autovacuum en cours d'exécution sur la table.

    Exécutez la requête suivante pour obtenir le résultat 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 détaillé est utile car, bien qu'il n'existe SQL actuellement aucun rapport d'avancement à ce sujet dans Postgre, vous pouvez voir l'activité.

    \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 lancez une REINDEXcommande, vous déverrouillez exclusivement 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, PID lancez la session Autovacuum sur la table.

    Exécutez la requête suivante pour obtenir le résultat 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

RDSpour Postgre SQL 12 et versions ultérieures

Si vous avez trop d'index dans une grande table, votre instance de base de données est peut-être proche de l'ID de transaction wraparound (XID), c'est-à-dire lorsque le XID compteur est ramené à 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 Postgre SQL 12 et versions ultérieures, vous pouvez utiliser la INDEX_CLEANUPclause VACUUM with.

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 Autovacuum est déjà en cours, vous devez y mettre fin pour commencer le manuelVACUUM. 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.

RDSpour Postgre SQL 11 et versions antérieures

Cependant, dans RDS les versions SQL 11 et antérieures de Postgre, le seul moyen de permettre au vide 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 supprimer les index lorsque XID Wraparound 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étail 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 de détails, consultez la SQL documentation de Postgre sur l'aspiration basée 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 SQL versions de Postgre pour lesquelles cette valeur est le paramètre par défaut :

  • disabled(Postger SQL 10, Postger 9.6) SQL

  • debug5, debug4, debug3, debug2, debug1

  • info(Poster SQL 12, Poster 11) SQL

  • notice

  • warning(Postgre SQL 13 et versions 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— Postgre SQL 14, Postgre SQL 13, Postgre SQL 12 et Postgre 11 SQL

  • (empty)— Aucune valeur par défaut pour Postgre SQL 10 et SQL Postgre 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, consultez la section Aspiration automatique dans la documentation de SQL Postgre.

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, consultez Quand se connecter dans la SQL documentation de Postgre.

Note

Postgre SQL permet au rds_superuser compte de visualiser les sessions d'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.