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 :

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 :

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 :

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.

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.

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)        
    

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.

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 :

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 :

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.