Le gonflement des tables et des index se produit lorsque l'opération de nettoyage ne suit pas le rythme du langage de manipulation de données (LMD) par rapport à une table. Un certain gonflement des tables et des index est inévitable et généralement acceptable, car les transactions peuvent réutiliser cet espace. Un bloat important entraîne des problèmes de performances, tels que des temps de requête plus lents, une augmentation des E/S, une utilisation moins efficace du cache et du tampon partagé, ainsi qu'une augmentation des coûts de stockage.
La meilleure façon d'estimer le gonflement est d'utiliser le module pgstattuple. Toutefois, l'utilisation de pgstattuple est moins efficace lorsque l'extension doit lire toutes les pages d'une relation. Si vous ne pouvez pas utiliser pgstattuple, vous pouvez utiliser d'autres scripts pour estimer le bloat, mais cette approche est moins précise.
Lire le résultat de pgstattuple
Lorsque vous utilisez pgstattuple pour signaler les caractéristiques de stockage physique d'une table ou d'un index, le résultat est au format suivant :
> SELECT * FROM pgstattuple('large_table');
-[ RECORD 1 ]------+-----------
table_len | 7431888896
tuple_count | 66666666
tuple_len | 2796296271
tuple_percent | 37.63
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 3873153652
free_percent | 52.12
Voici une description du résultat :
table_len(7431888896)- Taille totale de la table en octets. Cela inclut tous les tuples actifs, les tuples morts et l'espace libre. Dans ce cas,
large_tableoccupe environ 7 088 Go. tuple_count(66666666)- Représente le nombre de tuples actifs (lignes visibles et actives) dans la table. Le tableau contient environ 66 millions de lignes actives.
tuple_len(2796296271)- Longueur totale en octets de tous les tuples actifs combinés, soit environ 2,6 Go.
tuple_percent(37.63)Pourcentage de
table_lenoccupé par les tuples actifs. Pour le calculer, procédez comme suit :(
tuple_len/table_len) × 100 = (2796296271/7431888896) × 100 ≈37.63pour centCe résultat indique que les données actives utilisent moins de 40 % de l'espace disque de la table.
dead_tuple_count(0)Nombre de tuples inactifs (lignes que les opérations de nettoyage n'ont pas encore supprimées après des mises à jour ou des suppressions). Une valeur de
0signifie que la table est propre et ne contient aucun tuple mort en attente de récupération. Cela suggère souvent queVACUUM, ou autovacuum, s'exécute efficacement sur la table.dead_tuple_len(0)Longueur totale en octets de tous les tuples morts. Comme pour
dead_tuple_count, une valeur de0signifie que les lignes mortes ne consomment aucun espace.dead_tuple_percent(0)Pourcentage de l'espace
table_lenoccupé par les tuples inactifs. Pour le calculer, procédez comme suit :(
dead_tuple_len/table_len) * 100 = (0/7431888896) * 100 =0%free_space(3873153652)Cette valeur représente la quantité d'espace libre dans les pages de données de la table. Vous pouvez réutiliser cet espace pour de nouvelles insertions ou mises à jour sans avoir à étendre la table. Cela indique une quantité importante d'espace libre, soit environ 3,7 Go.
free_percent(52.12)Cette valeur représente le pourcentage d'espace libre du
table_len. Pour le calculer, procédez comme suit :(
free_space/table_len) × 100 = (3873153652/7431888896) × 100 ≈52.12%
Estimer le gonflement à l'aide de requêtes
Vous pouvez estimer le gonflement à l'aide de requêtes sur les tables du catalogue système, bien que cette méthode soit moins précise que l'utilisation d'extensions telles que pgstattuple. Si vous utilisez des scripts tels que PostgreSQL table bloat check, les statistiques de vos tables doivent être à jour. Sinon, l'estimation sera inexacte.
Lorsque vous comparez la sortie d'une requête avec le module pgstattuple, les estimations sont comparables, mais ne sont pas des mesures exactes de l'expansion. Le module reste la mesure la plus précise de l'inflation, car la requête SQL s'appuie sur le moteur de statistiques cumulatives de PostgreSQL, tandis que le module lit les pages de données réelles.
L'exemple suivant compare la sortie de deux méthodes différentes pour analyser le gonflement des tables dans PostgreSQL pour une table nommée large_table.
> SELECT * FROM pgstattuple('large_table');
-[ RECORD 1 ]------+-----------
table_len | 2475892736
tuple_count | 22000000
tuple_len | 922925931
tuple_percent | 37.28
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 1293696900
free_percent | 52.25
Vs.
-[ RECORD 1 ]+------------
databasename | postgres
schemaname | public
tablename | large_table
can_estimate | t
est_rows | 21965400
pct_bloat | 54
mb_bloat | 1269.24
table_mb | 2361.195
Surcharge d'adresses
Vous pouvez gérer le gonflement de différentes manières, chacune présentant des avantages et des inconvénients. Il est donc important de bien planifier votre stratégie :
- Passez l'aspirateur partout. Cette opération nécessite un verrouillage complet de la table pendant toute la durée du vide et un temps d'arrêt.
- Déplacez les données vers une nouvelle table. Pour utiliser cette méthode, vous devez suspendre les modifications apportées aux tables afin de vous assurer qu'elles ne sont pas transférées à la nouvelle relation.
- Utilisez
pg_squeeze. Cette extension réduit le gonflement en ligne. L'extension crée une table fantôme, insère toutes les données, puis gère les modifications apportées à la table à l'aide d'un emplacement de réplication jusqu'à la dernière étape, qui consiste à remplacer la table volumineuse par la nouvelle table non volumineuse. Il demande ensuite un bref verrouillage de table pour déplacer la table non gonflée afin de remplacer la table gonflée. Cette méthode nécessite deux fois l'espace de l'objet pendant la durée de l'opération, mais elle est la moins intrusive pour le fonctionnement normal.