Traiter la fragmentation
Créer une table t3 avec une colonne id de
type integer.
CREATE TABLE t3(id integer );
Désactiver l’autovacuum pour la table t3.
ALTER TABLE t3 SET (autovacuum_enabled = false );
La désactivation de l’autovacuum ici a un but uniquement pédagogique.
En production, c’est une très mauvaise idée !
Insérer un million de lignes dans la table t3 avec la
fonction generate_series.
INSERT INTO t3 SELECT generate_series (1 , 1000000 );
Récupérer la taille de la table t3.
SELECT pg_size_pretty (pg_table_size('t3 ' ));
Supprimer les 500 000 premières lignes de la table
t3.
DELETE FROM t3 WHERE id <= 500000 ;
Récupérer la taille de la table t3. Que faut-il en
déduire ?
SELECT pg_size_pretty (pg_table_size('t3 ' ));DELETE seul ne permet pas de regagner de la place sur le
disque. Les lignes supprimées sont uniquement marquées comme étant
mortes. Comme l’autovacuum est ici désactivé, PostgreSQL n’a pas encore
nettoyé ces lignes.
Exécuter un VACUUM VERBOSE sur la table t3.
Quelle est l’information la plus importante ?
L’indication :
indique 500 000 lignes ont été nettoyées dans 2213 blocs (en gros, la
moitié des blocs de la table).
Pour compléter, l’indication suivante :
reprend l’indication sur 500 000 lignes mortes, et précise que
500 000 autres ne le sont pas. Les 4425 pages parcourues correspondent
bien à la totalité des 35 Mo de la table complète. C’est la première
fois que VACUUM passe sur cette table, il est normal
qu’elle soit intégralement parcourue.
Récupérer la taille de la table t3. Que faut-il en
déduire ?
SELECT pg_size_pretty (pg_table_size('t3 ' ));VACUUM ne permet pas non plus de gagner en espace
disque. Principalement, il renseigne la structure FSM (free space
map ) sur les emplacements libres dans les fichiers des tables.
Exécuter un VACUUM FULL VERBOSE sur la table
t3.
Récupérer la taille de la table t3. Que faut-il en
déduire ?
SELECT pg_size_pretty (pg_table_size('t3 ' ));Là, par contre, nous gagnons en espace disque. Le
VACUUM FULL reconstruit la table et la fragmentation
disparaît.
Créer une table t4 avec une colonne id de
type integer.
CREATE TABLE t4(id integer );
Désactiver l’autovacuum pour la table t4.
ALTER TABLE t4 SET (autovacuum_enabled = false );
Insérer un million de lignes dans la table t4 avec
generate_series.
INSERT INTO t4(id ) SELECT generate_series (1 , 1000000 );
Récupérer la taille de la table t4.
SELECT pg_size_pretty (pg_table_size('t4 ' ));
Supprimer les 500 000 dernières lignes de la table
t4.
DELETE FROM t4 WHERE id > 500000 ;
Récupérer la taille de la table t4. Que faut-il en
déduire ?
SELECT pg_size_pretty (pg_table_size('t4 ' ));Là aussi, nous n’avons rien perdu.
Exécuter un VACUUM sur la table t4.
Récupérer la taille de la table t4. Que faut-il en
déduire ?
SELECT pg_size_pretty (pg_table_size('t4 ' ));En fait, il existe un cas où il est possible de gagner de l’espace
disque suite à un VACUUM simple : quand l’espace récupéré
se trouve en fin de table et qu’il est possible de prendre rapidement un
verrou exclusif sur la table pour la tronquer. C’est assez peu fréquent
mais c’est une optimisation intéressante.
Détecter la fragmentation
Créer une table t5 avec deux colonnes : c1
de type integer et c2 de type
text.
CREATE TABLE t5 (c1 integer , c2 text );
Désactiver l’autovacuum pour la table t5.
ALTER TABLE t5 SET (autovacuum_enabled= false );
Insérer un million de lignes dans la table t5 avec
generate_series.
INSERT INTO t5(c1, c2) SELECT i, 'Ligne ' || i FROM generate_series (1 , 1000000 ) AS i;CREATE EXTENSION pg_freespacemap;Cette extension installe une fonction nommée
pg_freespace, dont la version la plus simple ne demande que
la table en argument, et renvoie l’espace libre dans chaque bloc, en
octets, connu de la Free Space Map .
SELECT count (blkno), sum (avail) FROM pg_freespace('t5 ' :: regclass);et donc 6274 blocs (soit 51,4 Mo) sans aucun espace vide.
Modifier exactement 200 000 lignes de la table
t5.
Que rapporte pg_freespace quant à
l’espace libre de la table t5 ?
UPDATE t5 SET c2 = upper (c2) WHERE c1 <= 200000 ;SELECT count (blkno), sum (avail) FROM pg_freespace('t5 ' :: regclass);La table comporte donc 20 % de blocs en plus, où sont stockées les
nouvelles versions des lignes modifiées. Le champ avail indique
qu’il n’y a quasiment pas de place libre. (Ne pas prendre la valeur de
32 octets au pied de la lettre, la Free Space Map ne cherche
pas à fournir une valeur précise.)
Exécuter un VACUUM sur la table t5.
Que rapporte pg_freespace quant à l’espace libre de la
table t5 ?
SELECT count (blkno), sum (avail) FROM pg_freespace('t5 ' :: regclass);Il y a toujours autant de blocs, mais environ 8,8 Mo sont à présent
repérés comme libres.
Il faut donc bien exécuter un VACUUM pour que PostgreSQL
nettoie les blocs et mette à jour la structure FSM, ce qui nous permet
de déduire le taux de fragmentation de la table.
Récupérer la taille de la table t5.
SELECT pg_size_pretty (pg_table_size('t5 ' ));
Exécuter un VACUUM (FULL, VERBOSE) sur la table
t5.
VACUUM (FULL , VERBOSE ) t5;
Récupérer la taille de la table t5 et l’espace libre
rapporté par pg_freespacemap. Que faut-il en déduire ?
SELECT count (blkno),sum (avail)FROM pg_freespace('t5 ' :: regclass);SELECT pg_size_pretty (pg_table_size('t5 ' ));VACUUM FULL a réécrit la table sans les espaces morts,
ce qui nous a fait gagner entre 8 et 9 Mo. La taille de la table
maintenant correspond bien à celle de l’ancienne table, moins la place
prise par les lignes mortes.
Gestion de l’autovacuum
Créer une table t6 avec une colonne id de
type integer.
CREATE TABLE t6 (id integer ) ;
Insérer un million de lignes dans la table t6 :
INSERT INTO t6(id ) SELECT generate_series (1 , 1000000 ) ;
INSERT INTO t6(id ) SELECT generate_series (1 , 1000000 ) ;
Que contient la vue pg_stat_user_tables pour la
table t6 ? Il faudra peut-être attendre une minute. (Si la
version de PostgreSQL est antérieure à la 13, il faudra lancer un
VACUUM t6.)
SELECT * FROM pg_stat_user_tables WHERE relname = 't6 ' ;Les deux dates last_autovacuum et
last_autoanalyze sont renseignées. Il faudra peut-être
attendre une minute que l’autovacuum passe sur la table (voire plus sur
une instance chargée par ailleurs).
Le seuil de déclenchement de l’autoanalyze est :autovacuum_analyze_scale_factor × nombre de lignesautovacuum_analyze_thresholdautovacuum_vacuum_insert_scale_factor × nombre de
lignesautovacuum_vacuum_insert_threshold
Avec un million de nouvelles lignes, les deux seuils sont
franchis.
Avec PostgreSQL 12 ou antérieur, seule la ligne
last_autoanalyze sera remplie. S’il n’y a que des
insertions, le démon autovacuum ne lance un VACUUM
spontanément qu’à partir de PostgreSQL 13.
Jusqu’en PostgreSQL 12, il faut donc lancer manuellement :
Vérifier le nombre de lignes dans
pg_class.reltuples.
Vérifions que le nombre de lignes est à jour dans
pg_class :
SELECT * FROM pg_class WHERE relname = 't6 ' ;L’autovacuum se base entre autres sur cette valeur pour décider s’il
doit passer ou pas. Si elle n’est pas encore à jour, il faut lancer
manuellement :
ce qui est d’ailleurs généralement conseillé après un gros
chargement.
Modifier 60 000 lignes supplémentaires de la table t6
avec :
UPDATE t6 SET id = 1 WHERE id > 940000 ;
Attendre une minute.
Que contient la vue pg_stat_user_tables pour la
table t6 ?
Que faut-il en déduire ?
UPDATE t6 SET id = 0 WHERE id <= 150000 ;Le démon autovacuum ne se déclenche pas instantanément après
les écritures, attendons un peu :
SELECT * FROM pg_stat_user_tables WHERE relname = 't6 ' ;Seul last_autoanalyze a été modifié, et il reste entre
150 000 lignes morts (n_dead_tup). En effet, le démon
autovacuum traite séparément l’ANALYZE (statistiques sur
les valeurs des données) et le VACUUM (recherche des
espaces morts). Si l’on recalcule les seuils de déclenchement, on trouve
pour l’autoanalyze :autovacuum_analyze_scale_factor × nombre de lignesautovacuum_analyze_threshold
Pour l’autovacuum, le seuil est de :autovacuum_vacuum_insert_scale_factor × nombre de
lignesautovacuum_vacuum_insert_threshold
Modifier 60 000 lignes supplémentaires de la table t6
avec :
UPDATE t6 SET id = 1 WHERE id > 940000 ;
Attendre une minute.
Que contient la vue pg_stat_user_tables pour la
table t6 ?
Que faut-il en déduire ?
UPDATE t6 SET id = 1 WHERE id > 940000 ;L’autovacuum ne passe pas tout de suite, les 210 000 lignes mortes au
total sont bien visibles :
SELECT * FROM pg_stat_user_tables WHERE relname = 't6 ' ;Mais comme le seuil de 200 050 lignes modifiées à été franchi, le
démon lance un VACUUM :
Noter que n_dead_tup est revenu à 0.
last_auto_analyze indique qu’un nouvel ANALYZE
n’a pas été exécuté : seules 60 000 lignes ont été modifiées (voir
n_mod_since_analyze), en-dessous du seuil de 100 050.
Descendre le facteur d’échelle de la table t6 à 10 %
pour le VACUUM.
ALTER TABLE t6 SET (autovacuum_vacuum_scale_factor= 0.1 );
Modifier encore 200 000 autres lignes de la table
t6 :
UPDATE t6 SET id = 1 WHERE id > 740000 ;
Attendre une minute.
Que contient la vue pg_stat_user_tables pour la
table t6 ?
Que faut-il en déduire ?
UPDATE t6 SET id = 1 WHERE id > 740000 ;SELECT * FROM pg_stat_user_tables WHERE relname= 't6 ' ;Le démon a relancé un VACUUM et un ANALYZE.
Avec un facteur d’échelle à 10 %, il ne faut plus attendre que la
modification de 100 050 lignes pour que le VACUUM soit
déclenché par le démon. C’était déjà le seuil pour
l’ANALYZE.
