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.
Gestion de la fonction autovacuum avec de grands index
Dans le cadre de son fonctionnement, la fonction autovacuum effectue plusieurs phases de mise à vide
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_indexesidx_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
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)
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 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 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.
