Diagnostic du gonflement de la table et de l'index

Vous pouvez utiliser le contrôle de simultanéité multiversion (MVCC) PostgreSQL pour préserver l'intégrité des données. PostgreSQL MVCC fonctionne en enregistrant une copie interne des lignes mises à jour ou supprimées (également appelées tuples) jusqu'à ce qu'une transaction soit validée ou annulée. Cette copie interne enregistrée est invisible pour les utilisateurs. Toutefois, le gonflement de la table peut se produire lorsque ces copies invisibles ne sont pas nettoyées régulièrement par les utilitaires VACUUM ou AUTOVACUUM. S'il n'est pas vérifié, le gonflement de la table peut entraîner une augmentation des coûts de stockage et ralentir votre vitesse de traitement.

Dans de nombreux cas, les paramètres par défaut pour VACUUM ou AUTOVACUUM sur Aurora sont suffisants pour gérer les gonflements de table indésirables. Toutefois, vous pouvez vérifier qu'il n'y a pas de gonflement si votre application présente les conditions suivantes :

• Traite un grand nombre de transactions en un temps relativement court entre les processus VACUUM.

• Offre des performances médiocres et manque d'espace de stockage.

Pour commencer, collectez les informations les plus précises sur l'espace utilisé par les tuples morts et sur la quantité que vous pouvez récupérer en nettoyant le gonflement de la table et de l'index. Pour ce faire, utilisez l'extension pgstattuple pour recueillir des statistiques sur votre cluster Aurora. Pour plus d'informations, consultez pgstattuple. Les privilèges d'utilisation de l'extension pgstattuple sont limités au rôle pg_stat_scan_tables et aux super-utilisateurs de la base de données.

Pour créer l'extension pgstattuple sur Aurora, connectez une session client au cluster, par exemple psql ou pgAdmin, et utilisez la commande suivante :

CREATE EXTENSION pgstattuple;

Créez l'extension dans chaque base de données que vous souhaitez profiler. Après avoir créé l'extension, utilisez l'interface de ligne de commande (CLI) pour mesurer la quantité d'espace inutilisable que vous pouvez récupérer. Avant de recueillir des statistiques, modifiez le groupe de paramètres du cluster en définissant AUTOVACUUM sur 0. Un paramètre de 0 empêche Aurora de nettoyer automatiquement les tuples morts laissés par votre application, ce qui peut avoir une incidence sur la précision des résultats. Saisissez la commande suivante pour créer une table simple :

postgres=> CREATE TABLE lab AS SELECT generate_series (0,100000);
SELECT 100001

Dans l'exemple suivant, nous exécutons la requête avec AUTOVACUUM activé pour le cluster de bases de données. Le paramètre dead_tuple_count est 0, ce qui indique que AUTOVACUUM a supprimé les données ou les tuples obsolètes de la base de données PostgreSQL.

Pour utiliser pgstattuple afin de recueillir des informations sur la table, spécifiez le nom d'une table ou un identifiant d'objet (OID) dans la requête :

postgres=> SELECT * FROM pgstattuple('lab');

    
    
table_len  | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056    | 100001      | 2800028   | 77.16         | 0                | 0              | 0                  | 16616      | 0.46
(1 row)
        
    

Dans la requête suivante, nous désactivons AUTOVACUUM et saisissons une commande qui supprime 25 000 lignes de la table. En conséquence, dead_tuple_count passe à 25 000.

postgres=> DELETE FROM lab WHERE generate_series < 25000;    

DELETE 25000  
   

SELECT * FROM pgstattuple('lab');
    

        
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056 | 75001 | 2100028 | 57.87 | 25000 | 700000 | 19.29 | 16616 | 0.46
(1 row)        
    
    

Pour récupérer ces tuples morts, lancez un processus VACUUM.

Observation des gonflements sans interrompre votre application

Les paramètres d'un cluster Aurora sont optimisés pour fournir les bonnes pratiques pour la plupart des charges de travail. Toutefois, vous souhaiterez peut-être optimiser un cluster pour mieux l'adapter à vos applications et à vos modèles d'utilisation. Dans ce cas, vous pouvez utiliser l'extension pgstattuple sans perturber une application active. Pour ce faire, effectuez les étapes suivantes :

1. Clonez votre instance Aurora.

2. Modifiez le fichier de paramètres pour désactiver AUTOVACUUM dans le clone.

3. Exécutez une requête pgstattuple tout en testant le clone avec un exemple de charge de travail ou avec pgbench, un programme permettant d'exécuter des tests de référence sur PostgreSQL. Pour plus d'informations, consultez pgbench.

Après avoir lancé vos applications et consulté le résultat, utilisez pg_repack ou VACUUM FULL sur la copie restaurée et comparez les différences. Si vous constatez une baisse significative de dead_tuple_count, dead_tuple_len ou dead_tuple_percent, ajustez le calendrier de mise à vide de votre cluster de production afin de minimiser le gonflement.

Prévention du gonflement dans les tables temporaires

Si votre application crée des tables temporaires, assurez-vous qu'elle supprime ces tables temporaires lorsqu'elles ne sont plus nécessaires. Les processus de mise à vide automatique ne localisent pas les tables temporaires. Si elles ne sont pas vérifiées, les tables temporaires peuvent rapidement entraîner un gonflement de la base de données. De plus, le gonflement peut s'étendre aux tables système, qui sont les tables internes qui suivent les objets et les attributs de PostgreSQL, tels que pg_attribute et pg_depend.

Lorsqu'une table temporaire n'est plus nécessaire, vous pouvez utiliser une instruction TRUNCATE pour vider la table et libérer de l'espace. Ensuite, videz manuellement les tables pg_attribute et pg_depend. La mise à vide de ces tables garantit que la création et la troncation/la suppression continue de tables temporaires n'ajoute pas de tuples et ne contribue pas au gonflement du système.

Vous pouvez éviter ce problème lors de la création d'une table temporaire en incluant la syntaxe suivante qui supprime les nouvelles lignes lorsque le contenu est validé :

CREATE TEMP TABLE IF NOT EXISTS table_name(table_description) ON COMMIT DELETE ROWS;

La clause ON COMMIT DELETE ROWS tronque la table temporaire lorsque la transaction est validée.

Prévention du gonflement dans les index

Lorsque vous modifiez un champ indexé dans une table, la mise à jour de l'index entraîne la suppression d'un ou de plusieurs tuples dans cet index. Par défaut, le processus de mise à vide automatique élimine le gonflement des index, mais ce nettoyage nécessite beaucoup de temps et de ressources. Pour spécifier les préférences de nettoyage d'index lorsque vous créez une table, incluez la clause vacuum_index_cleanup. Par défaut, au moment de la création de la table, la clause est définie sur AUTO, ce qui signifie que le serveur décide si votre index doit être nettoyé lorsqu'il vide la table. Vous pouvez définir la clause sur ON pour activer le nettoyage de l'index pour une table spécifique, ou sur OFF pour désactiver le nettoyage de l'index pour cette table. N'oubliez pas que la désactivation du nettoyage des index peut vous faire gagner du temps, mais peut entraîner un gonflement de l'index.

Vous pouvez contrôler manuellement le nettoyage de l'index lorsque vous VACUUM une table dans la ligne de commande. Pour vider une table et supprimer les tuples morts des index, incluez la clause INDEX_CLEANUP avec la valeur ON et le nom de la table :

acctg=> VACUUM (INDEX_CLEANUP ON) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM

Pour vider une table sans nettoyer les index, spécifiez la valeur OFF :

acctg=> VACUUM (INDEX_CLEANUP OFF) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM