Aller au contenu

pg_freespacemap : analyser les espaces libres

Le but de cette extension (contrib, livrée avec PostgreSQL) est de fournir le nombre d’octets disponibles dans chaque bloc d’une relation. Pour cela, elle se base sur le contenu de la structure FSM (free space map).

Cette structure est renseignée par l’opération VACUUM et permet à PostgreSQL de savoir où placer les nouvelles lignes à insérer.

Installation

Commençons par ajouter cette extension et regarder son contenu :

postgres=# CREATE DATABASE b1;
CREATE DATABASE
postgres=# \c b1
You are now connected to database "b1" as user "guillaume".
b1=# CREATE EXTENSION pg_freespacemap ;
CREATE EXTENSION
b1=# \dx+ pg_freespacemap
 Objects in extension "pg_freespacemap"
           Object Description           
----------------------------------------
 function pg_freespace(regclass)
 function pg_freespace(regclass,bigint)
(2 rows)

Cette extension contient deux fonctions. L’une, codée en C, demande deux arguments : l’identifiant de la table et le numéro du bloc pour lequel nous souhaitons connaître l’espace libre. Elle renvoie l’espace libre en un nombre représentant le nombre d’octets disponibles.

La seconde fonction est un wrapper autour de la première permettant de récupérer les informations plus simplement. L’identifiant de la table lui est fourni, et elle renvoie un ensemble de lignes à deux colonnes (les numéros de bloc et l’espace libre de chacun).

Exemple

Créons une table et ajoutons-y quelques données pour tester ces fonctions :

b1=# CREATE TABLE t1(c1 integer, c2 text);
CREATE TABLE
b1=# INSERT INTO t1 SELECT i, md5(random()::text) FROM generate_series(1, 1000000) AS i;
INSERT 0 1000000

Maintenant, vérifions l’espace disponible dans quelques blocs :

b1=# SELECT pg_freespace('t1', 10);
 pg_freespace 
--------------
            0
(1 row)

b1=# SELECT pg_freespace('t1', 9);
 pg_freespace 
--------------
            0
(1 row)

Aucun espace n’est trouvé, ce qui est logique car nous avons inséré un million de lignes mais nous n’avons rien supprimé. De plus, nous n’avons pas modifié le paramètre fillfactor, donc le facteur de remplissage est à sa valeur par défaut, soit 100%. Essayons avec une autre table avec un autre facteur de remplissage (ici 80%) :

b1=# CREATE TABLE t2(c1 integer, c2 text) with (fillfactor=80);
CREATE TABLE
b1=# INSERT INTO t2 SELECT i, md5(random()::text) FROM generate_series(1, 1000000) AS i;
INSERT 0 1000000
b1=# SELECT pg_freespace('t2', 10), pg_freespace('t2', 11);
 pg_freespace | pg_freespace 
--------------+--------------
         1632 |         1632
(1 row)

1632 correspond bien à 20% de 8192.

pg_freespacemap est donc bien précis. Essayons maintenant l’autre fonction de cette extension :

b1=# SELECT * FROM pg_freespace('t1') ;
 blkno | avail 
-------+-------
     0 |     0
     1 |     0
     2 |     0
     3 |     0
     4 |     0
     5 |     0
     6 |     0
     7 |     0
     8 |     0
     9 |     0
     …
  8330 |     0
  8331 |     0
  8332 |     0
  8333 |  5440
(8334 lignes)

Nous avons bien le numéro du bloc en première colonne et l’espace disponible en deuxième colonne. Les premiers 8333 blocs sont complètement remplis, et dans le dernier il reste 5400 octets inoccupés. Chaque bloc comprend donc à peu près 1200 lignes. On peut calculer qu’il y a 120 lignes par bloc complet et 40 dans le dernier bloc.

Si on compte le nombre de blocs et qu’on le multiplie par 8192 (taille par défaut d’un bloc), on doit retrouver la taille en octets de la table :

b1=#  SELECT count(*), count(*)*8192 AS count, pg_relation_size('t1') AS taille_t1 FROM pg_freespace('t1');
 count |  count   | taille_t1 
-------+----------+-----------
  8334 | 68272128 |  68272128
(1 row)

C’est bien cela.

Supprimons une ligne sur trois dans la table de façon uniforme :

b1=# DELETE FROM t1 WHERE c1%3=1;
DELETE 333334
b1=# SELECT * FROM pg_freespace('t1') LIMIT 10;
 blkno | avail 
-------+-------
     0 |  2560
     1 |  2560
     2 |  2560
     3 |  2560
     4 |  2560
     5 |  2560
     6 |  2560
     7 |  2560
     8 |  2560
     9 |  2560
(10 rows)
Il peut être nécessaire d’attendre une minute ou deux pour que l’autovacuum se déclenche et nettoie l’espace libre. En attendant, pg_freespace renvoie toujours 0.

Note

Les lignes mortes sont bien comptabilisées dans l’espace occupé, non libre.

Après cette suppression et le passage de l’autovacuum, nous avons 2560 octets disponibles par bloc de 8 ko. On aurait pu penser trouver 2730 octets libres (⅓ de 8192). La différence s’explique entre autres par l’existence d’informations supplémentaires pour le système dans chaque bloc.

Supprimons maintenant les lignes pour lesquelles c1 est inférieur à 1000 :

b1=# DELETE FROM t1 WHERE c1<1000;
DELETE 666
b1=# SELECT * FROM pg_freespace('t1') LIMIT 10;
 blkno | avail 
-------+-------
     0 |  2560
     1 |  2560
     2 |  2560
     3 |  2560
     4 |  2560
     5 |  2560
     6 |  2560
     7 |  2560
     8 |  2560
     9 |  2560
(10 rows)

Rien n’a changé sur la table (on se serait attendu à avoir beaucoup plus d’espace libre dans les premiers blocs) car nous avons supprimé trop peu de lignes pour déclencher un autre VACUUM avec l’autovacuum. Lançons le nous-même :

b1=# VACUUM t1;
VACUUM
b1=# SELECT * FROM pg_freespace('t1') LIMIT 10;
 blkno | avail 
-------+-------
     0 |  7680
     1 |  7680
     2 |  7680
     3 |  7680
     4 |  7680
     5 |  7680
     6 |  7680
     7 |  7680
     8 |  4224
     9 |  2560
(10 rows)

C’est clairement mieux : nous avons 8 blocs pratiquement vides, un bloc à moitié et le reste à un tiers vide.

Autrement dit, la précision de pg_freespace dépend de la fréquence des VACUUM par rapport à la fréquence des modifications sur les tables.

Cherchons maintenant la place disponible dans cette table :

b1=# SELECT sum(avail) AS place_dispo FROM pg_freespace('t1');
 place_dispo 
-------------
    21385280
(1 row)

Elle est exprimée ici en octets. Il est possible de l’avoir avec une unité en utilisant la fonction pg_size_pretty :

b1=# SELECT pg_size_pretty(sum(avail)) AS place_dispo FROM pg_freespace('t1');
 place_dispo 
-------------
 20 MB
(1 row)

Il est possible d’écrire une requête qui nous indique la place occupée et la place libre d’une table :

b1=# SELECT pg_size_pretty(sum(avail)) AS place_dispo, pg_size_pretty(pg_table_size('t1')) AS place_occupee FROM pg_freespace('t1');
 place_dispo | place_occupee 
-------------+---------------
 20 MB       | 65 MB
(1 row)

Reconstruire la table avec VACUUM FULL devrait nous permettre de récupérer ces 20 Mo. Testons cela :

b1=# VACUUM FULL t1;
VACUUM
b1=# SELECT pg_size_pretty(sum(avail)) AS place_dispo, pg_size_pretty(pg_table_size('t1')) AS place_occupe FROM pg_freespace('t1');
 place_dispo | place_occupe 
-------------+--------------
 0 bytes     | 43 MB
(1 row)

Nous avons gagné un peu plus grâce à la réorganisation des lignes.

La requête suivante permet de connaître la place disponible dans les fichiers de la table, la place occupée par les fichiers de la table, ainsi que la date du dernier VACUUM (et donc la date de la dernière actualisation de la première information) :

b1=# SELECT
  pg_size_pretty(sum(avail)) AS place_dispo,
  pg_size_pretty(pg_table_size('t1')) AS place_occupe,
  to_char(greatest(pg_stat_get_last_vacuum_time('t1'::regclass), pg_stat_get_last_autovacuum_time('t1'::regclass)), 'YYYY-MM-DD HH24:MI') AS dernier_vacuum
FROM pg_freespace('t1');

 place_dispo | place_occupe |  dernier_vacuum  
-------------+--------------+------------------
 0 bytes     | 43 MB        | 2013-06-20 07:06
(1 row)

b1=# DELETE FROM t1;
DELETE

b1=# SELECT
  pg_size_pretty(sum(avail)) AS place_dispo,
  pg_size_pretty(pg_table_size('t1')) AS place_occupe,
  to_char(greatest(pg_stat_get_last_vacuum_time('t1'::regclass), pg_stat_get_last_autovacuum_time('t1'::regclass)), 'YYYY-MM-DD HH24:MI') AS dernier_vacuum
FROM pg_freespace('t1');
 place_dispo | place_occupe |  dernier_vacuum  
-------------+--------------+------------------
             | 24 kB        | 2013-06-20 07:11
(1 row)

À 7h11, un VACUUM lancé par le démon autovacuum a nettoyé la table t1, suite à la suppression des lignes par le DELETE.

Prenons un autre exemple, avec une table t2 de 46 Go :

b1=# SELECT * FROM pg_freespace('t2') LIMIT 10;
 blkno | avail
-------+-------
     0 |    96
     1 |   128
     2 |   160
     3 |   160
     4 |    96
     5 |    64
     6 |    96
     7 |     0
     8 |    32
     9 |    32
(10 rows)

Nous savons ainsi que, dans la table t2, le bloc 0 contient 96 octets disponibles, le bloc 1 en contient 128, le bloc 2 160, etc. L’utilisation d’une fonction d’agrégat permet d’avoir une idée plus précise de la fragmentation :

b1=# SELECT pg_size_pretty(pg_table_size('s_cga.t2')) AS "table size",
  sum(avail),
  min(avail),
  avg(avail),
  max(avail)
FROM pg_freespace('t2');

 table size |     sum     | min |          avg          | max
------------+-------------+-----+-----------------------+------
 46 GB      | 20964880320 |   0 | 3444.3630446733069130 | 8064
(1 row)

La colonne sum indique la quantité totale d’espace disponible. Autrement dit, la table t2 contient 20 Go de disponible sur 46 Go (donc pratiquement la moitié). Cette table serait candidate à VACUUM FULL. Certains blocs sont très fragmentés, certains disposant même de 8064 octets libres (sur 8192).

Contrairement à pgstattuple, pg_freespacemap nécessite qu’un VACUUM ait eu lieu récemment pour que les informations statistiques soient suffisamment à jour pour être utiles. Vu la grosse taille de certaines tables, cela pourrait avoir un impact sur les performances.

Référence