Révision 23.01 (27 janvier 2023)
Dalibo SCOP
Creative Commons BY-NC-SA
VACUUM est la contrepartie de la flexibilité du modèle MVCC. Derrière
les différentes options de VACUUM
se cachent plusieurs
tâches très différentes. Malheureusement, la confusion est facile. Il
est capital de les connaître et de comprendre leur fonctionnement.
Autovacuum permet d’automatiser le VACUUM et allège considérablement le travail de l’administrateur.
Il fonctionne généralement bien, mais il faut savoir le surveiller et l’optimiser.
VACUUM
est né du besoin de nettoyer les lignes mortes.
Au fil du temps il a été couplé à d’autres ordres (ANALYZE
,
VACUUM FREEZE
) et s’est occupé d’autres opérations de
maintenance (création de la visibility map par exemple).
autovacuum
est un processus de l’instance PostgreSQL. Il
est activé par défaut, et il fortement conseillé de le conserver ainsi.
Dans le cas général, son fonctionnement convient et il ne gênera pas les
utilisateurs.
L’autovacuum ne gère pas toutes les variantes de VACUUM
(notamment pas le FULL
).
Un ordre VACUUM
vise d’abord à nettoyer les lignes
mortes.
Le traitement VACUUM
se déroule en trois passes. Cette
première passe parcourt la table à nettoyer, à la recherche
d’enregistrements morts. Un enregistrement est mort s’il possède un
xmax
qui correspond à une transaction validée, et que cet
enregistrement n’est plus visible dans l’instantané d’aucune transaction
en cours sur la base. D’autres lignes mortes portent un
xmin
d’une transaction annulée.
L’enregistrement mort ne peut pas être supprimé immédiatement : des
enregistrements d’index pointent vers lui et doivent aussi être
nettoyés. La session efffectuant le vacuum garde en mémoire la liste des
adresses des enregistrements morts, à hauteur d’une quantité indiquée
par le paramètre maintenance_work_mem
. Si cet espace est
trop petit pour contenir tous les enregistrements morts,
VACUUM
effectue plusieurs séries de ces trois passes.
La seconde passe se charge de nettoyer les entrées d’index.
VACUUM
possède une liste de tid
(tuple id
) à invalider. Il parcourt donc tous les index de
la table à la recherche de ces tid
et les supprime. En
effet, les index sont triés afin de mettre en correspondance une valeur
de clé (la colonne indexée par exemple) avec un tid
. Il
n’est par contre pas possible de trouver un tid
directement. Les pages entièrement vides sont supprimées de l’arbre et
stockées dans la liste des pages réutilisables, la Free Space
Map (FSM).
Cette phase peut être ignorée par deux mécanismes. Le premier
mécanisme apparaît en version 12 où l’option INDEX_CLEANUP
a été ajoutée. Ce mécanisme est donc manuel et permet de gagner du temps
sur l’opération de VACUUM
. Cette option s’utilise ainsi
:
off) nom_table ; VACUUM (VERBOSE, INDEX_CLEANUP
À partir de la version 14, un autre mécanisme, automatique cette
fois, a été ajouté. Le but est toujours d’exécuter rapidement le
VACUUM
, mais uniquement pour éviter le wraparound. Quand la
table atteint l’âge, très élevé, de 1,6 milliard de transactions (défaut
des paramètres vacuum_failsafe_age
et
vacuum_multixact_failsafe_age
), un VACUUM
simple va automatiquement désactiver le nettoyage des index pour
nettoyer plus rapidement la table et permettre d’avancer l’identifiant
le plus ancien de la table.
À partir de la version 13, cette phase peut être parallélisée (clause
PARALLEL
), chaque index pouvant être traité par un CPU.
Maintenant qu’il n’y a plus d’entrée d’index pointant sur les enregistrements morts identifiés, ceux-ci peuvent disparaître. C’est le rôle de cette passe. Quand un enregistrement est supprimé d’un bloc, ce bloc est réorganisé afin de consolider l’espace libre, qui est renseigné dans la Free Space Map (FSM).
Une fois cette passe terminée, si le parcours de la table n’a pas été terminé lors de la passe précédente, le travail reprend où il en était du parcours de la table.
Si les derniers blocs de la table sont vides, ils sont rendus au
système (si le verrou nécessaire peut être obtenu, et si l’option
TRUNCATE
n’est pas off
). C’est le seul cas où
VACUUM
réduit la taille de la table. Les espaces vides (et
réutilisables) au milieu de la table constituent le bloat
(littéralement « boursouflure » ou « gonflement », que l’on peut aussi
traduire par fragmentation).
Les statistiques d’activité sont aussi mises à jour.
VACUUM
Par défaut, VACUUM
procède principalement au nettoyage
des lignes mortes. Pour que cela soit efficace, il met à jour la
visibility map, et la crée au besoin. Au passage, il peut geler
certaines lignes rencontrées.
L’autovacuum le déclenchera sur les tables en fonction de l’activité.
Le verrou SHARE UPDATE EXCLUSIVE posé protège la table contre les
modifications simultanées du schéma, et ne gêne généralement pas les
opérations, sauf les plus intrusives (il empêche par exemple un
LOCK TABLE
). L’autovacuum arrêtera spontanément un
VACUUM
qu’il aurait lancé et qui gênerait ; mais un
VACUUM
lancé manuellement continuera jusqu’à la fin.
VACUUM ANALYZE
ANALYZE
existe en tant qu’ordre séparé, pour rafraîchir
les statistiques sur un échantillon des données, à destination de
l’optimiseur. L’autovacuum se charge également de lancer
des ANALYZE
en fonction de l’activité.
L’ordre VACUUM ANALYZE
(ou
VACUUM (ANALYZE)
) force le calcul des statistiques sur les
données en même temps que le VACUUM
.
VACUUM FREEZE
VACUUM FREEZE
procède au « gel » des lignes visibles par
toutes les transactions en cours sur l’instance, afin de parer au
problème du wraparound des identifiants de transaction
Concrètement, il indique dans un hint bit de chaque ligne
qu’elle est plus vieille que tous les numéros de transactions
actuellement actives (avant la 9.4, la colonne système xmin
était remplacée par un FrozenXid
).
Un ordre FREEZE
n’existe pas en tant que tel.
Préventivement, lors d’un VACUUM
simple, l’autovacuum
procède au gel de certaines des lignes rencontrées. De plus, il lancera
un VACUUM FREEZE
sur une table dont les plus vieilles
transactions dépassent un certain âge. Ce peut être très long, et très
lourd en écritures si une grosse table doit être entièrement gelée d’un
coup. Autrement, l’activité n’est qu’exceptionnellement gênée (voir plus
bas).
VACUUM FULL
L’ordre VACUUM FULL
permet de reconstruire la table sans
les espaces vides. C’est une opération très lourde, risquant de bloquer
d’autres requêtes à cause du verrou exclusif qu’elle pose (on ne peut
même plus lire la table !), mais il s’agit de la seule option qui permet
de réduire la taille de la table au niveau du système de fichiers de
façon certaine.
Il faut prévoir l’espace disque (la table est reconstruite à côté de
l’ancienne, puis l’ancienne est supprimée). Les index sont reconstruits
au passage. Un VACUUM FULL
gèle agressivement les lignes,
et effectue donc au passage l’équivalent d’un FREEZE
.
L’autovacuum ne lancera jamais un VACUUM FULL
!
Il existe aussi un ordre CLUSTER
, qui permet en plus de
trier la table suivant un des index.
VERBOSE :
Cette option affiche un grand nombre d’informations sur ce que fait la commande. En général c’est une bonne idée de l’activer :
VACUUM (VERBOSE) pgbench_accounts_5 ;
INFO: vacuuming "public.pgbench_accounts_5"
INFO: scanned index "pgbench_accounts_5_pkey" to remove 9999999 row versions
DÉTAIL : CPU: user: 12.16 s, system: 0.87 s, elapsed: 18.15 s
INFO: "pgbench_accounts_5": removed 9999999 row versions in 163935 pages
DÉTAIL : CPU: user: 0.16 s, system: 0.00 s, elapsed: 0.20 s
INFO: index "pgbench_accounts_5_pkey" now contains 100000000 row versions in 301613 pages
DÉTAIL : 9999999 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "pgbench_accounts_5": found 10000001 removable,
10000051 nonremovable row versions in 327870 out of 1803279 pages
DÉTAIL : 0 dead row versions cannot be removed yet, oldest xmin: 1071186825
There were 1 unused item identifiers.
Skipped 0 pages due to buffer pins, 1475409 frozen pages.
0 pages are entirely empty.
CPU: user: 13.77 s, system: 0.89 s, elapsed: 19.81 s.
VACUUM
PARALLEL :
Apparue avec PostgreSQL 13, l’option PARALLEL
permet le
traitement parallélisé des index. Le nombre indiqué après
PARALLEL
précise le niveau de parallélisation souhaité. Par
exemple :
PARALLEL 4) matable ; VACUUM (VERBOSE,
INFO: vacuuming "public.matable"
INFO: launched 3 parallel vacuum workers for index cleanup (planned: 3)
DISABLE_PAGE_SKIPPING :
Par défaut, PostgreSQL ne traite que les blocs modifiés depuis le
dernier VACUUM
, ce qui est un gros gain en performance
(l’information est stockée dans la Visibility Map).
À partir de la version 11, activer l’option
DISABLE_PAGE_SKIPPING
force l’analyse de tous les blocs de
la table. La table est intégralement reparcourue. Ce peut être utile en
cas de problème, notamment pour reconstruire cette Visibility
Map.
SKIP_LOCKED :
À partir de la version 12, l’option SKIP_LOCKED
permet
d’ignorer toute table pour laquelle la commande VACUUM
ne
peut pas obtenir immédiatement son verrou. Cela évite de bloquer le
VACUUM
sur une table, et peut éviter un empilement des
verrous derrière celui que le VACUUM
veut poser, surtout en
cas de VACUUM FULL
. La commande passe alors à la table
suivante à traiter. Exemple :
FULL, SKIP_LOCKED) t_un_million_int, t_cent_mille_int ; # VACUUM (
WARNING: skipping vacuum of "t_un_million_int" --- lock not available
VACUUM
Une autre technique est de paramétrer dans la session un petit délai avant abandon :
SET lock_timeout TO '100ms' ;
INDEX_CLEANUP :
L’option INDEX_CLEANUP
(par défaut à on
jusque PostgreSQL 13 compris) déclenche systématiquement le Quand il
faut nettoyer des lignes mortes urgemment dans une grosse table, la
valeur off
fait gagner beaucoup de temps :
off) unetable ; VACUUM (VERBOSE, INDEX_CLEANUP
Les index peuvent être nettoyés plus tard par un autre
VACUUM
, ou reconstruits.
Cette option existe aussi sous la forme d’un paramètre de stockage
(vacuum_index_cleanup
) propre à la table pour que
l’autovacuum en tienne aussi compte.
En version 14, le nouveau défaut est auto
, qui indique
que PostgreSQL doit décider de faire ou non le nettoyage des index
suivant la quantité d’entrées à nettoyer. Il faut au minimum 2 %
d’éléments à nettoyer pour que le nettoyage ait lieu.
PROCESS_TOAST :
Cette option active ou non le traitement de la partie TOAST associée
à la table. Elle est activée par défaut. Son utilité est la même que
pour INDEX_CLEANUP
.
TRUNCATE :
L’option TRUNCATE
(à on
par défaut) permet
de tronquer les derniers blocs vides d’une table.
TRUNCATE off
évite d’avoir à poser un verrou exclusif
certes court, mais parfois gênant.
Cette option existe aussi sous la forme d’un paramètre de stockage de
table (vacuum_truncate
).
Mélange des options :
Il est possible de mixer ces options presque à volonté et de préciser plusieurs tables à nettoyer :
ANALYZE, INDEX_CLEANUP off, TRUNCATE off,
VACUUM (VERBOSE, DISABLE_PAGE_SKIPPING) bigtable, smalltable ;
Un VACUUM
, y compris lancé par l’autovacuum, apparaît
dans pg_stat_activity
et le
processus est visible comme processus système avec top
ou
ps
:
$ ps faux
…
postgres 3470724 0.0 0.0 12985308 6544 ? Ss 13:58 0:02 \_ postgres: 13/main: autovacuum launcher
postgres 795432 7.8 0.0 14034140 13424 ? Rs 16:22 0:01 \_ postgres: 13/main: autovacuum worker
pgbench1000p10
…
Il est fréquent de se demander si l’autovacuum s’occupe suffisamment
d’une table qui grossit ou dont les statistiques semblent périmées. La
vue pg_stat_user_tables
contient quelques informations.
Dans l’exemple ci-dessous, nous distinguons les dates des
VACUUM
et ANALYZE
déclenchés automatiquement
ou manuellement (en fait par l’application pgbench
). Si
44 305 lignes ont été modifiées depuis le rafraîchissement des
statistiques, il reste 2,3 millions de lignes mortes à nettoyer (contre
10 millions vivantes).
SELECT * FROM pg_stat_user_tables WHERE relname ='pgbench_accounts' \gx #
-[ RECORD 1 ]-------+------------------------------
relid | 489050
schemaname | public
relname | pgbench_accounts
seq_scan | 1
seq_tup_read | 10
idx_scan | 686140
idx_tup_fetch | 2686136
n_tup_ins | 0
n_tup_upd | 2343090
n_tup_del | 452
n_tup_hot_upd | 118551
n_live_tup | 10044489
n_dead_tup | 2289437
n_mod_since_analyze | 44305
n_ins_since_vacuum | 452
last_vacuum | 2020-01-06 18:42:50.237146+01
last_autovacuum | 2020-01-07 14:30:30.200728+01
last_analyze | 2020-01-06 18:42:50.504248+01
last_autoanalyze | 2020-01-07 14:30:39.839482+01
vacuum_count | 1
autovacuum_count | 1
analyze_count | 1
autoanalyze_count | 1
Activer le paramètre log_autovacuum_min_duration
avec
une valeur relativement faible (dépendant des tables visées de la taille
des logs générés), voire le mettre à 0, est également courant et
conseillé.
La vue pg_stat_progress_vacuum
contient une ligne par
VACUUM
(simple ou FREEZE
) en cours
d’exécution.
Voici un exemple :
SELECT * FROM pg_stat_progress_vacuum ;
-[ RECORD 1 ]------+--------------
pid | 4299
datid | 13356
datname | postgres
relid | 16384
phase | scanning heap
heap_blks_total | 127293
heap_blks_scanned | 86665
heap_blks_vacuumed | 86664
index_vacuum_count | 0
max_dead_tuples | 291
num_dead_tuples | 53
Dans cet exemple, le VACUUM
exécuté par le PID 4299 a
parcouru 86 665 blocs (soit 68 % de la table), et en a traité
86 664.
Dans le cas d’un VACUUM ANALYZE
, la seconde partie de
recueil des statistiques pourra être suivie dans
pg_stat_progress_analyze
(à partir de PostgreSQL 13) :
SELECT * FROM pg_stat_progress_analyze ;
-[ RECORD 1 ]-------------+--------------------------------
pid | 1938258
datid | 748619
datname | grossetable
relid | 748698
phase | acquiring inherited sample rows
sample_blks_total | 1875
sample_blks_scanned | 1418
ext_stats_total | 0
ext_stats_computed | 0
child_tables_total | 16
child_tables_done | 6
current_child_table_relid | 748751
Les vues précédentes affichent aussi bien les opérations lancées manuellement que celles décidées par l’autovacuum.
Par contre, pour un VACUUM FULL
, il faudra suivre la
progression au travers de la vue pg_stat_progress_cluster
(à partir de la version 12), qui renvoie par exemple :
$ psql -c 'VACUUM FULL big' &
$ psql
postgres=# \x
Affichage étendu activé.
=# SELECT * FROM pg_stat_progress_cluster ; postgres
-[ RECORD 1 ]-------+------------------
pid | 21157
datid | 13444
datname | postgres
relid | 16384
command | VACUUM FULL
phase | seq scanning heap
cluster_index_relid | 0
heap_tuples_scanned | 13749388
heap_tuples_written | 13749388
heap_blks_total | 199105
heap_blks_scanned | 60839
index_rebuild_count | 0
Cette vue est utilisable aussi avec l’ordre CLUSTER
,
d’où le nom.
Le principe est le suivant :
Le démon autovacuum launcher
s’occupe de lancer des
workers régulièrement sur les différentes bases
Ce nouveau processus
inspecte les statistiques sur les tables (vue
pg_stat_all_tables
) : nombres de lignes insérées, modifiées
et supprimées. Quand certains seuils sont dépassés sur un objet, le
worker effectue un VACUUM
, un
ANALYZE
, voire un VACUUM FREEZE
(mais jamais,
rappelons-le, un VACUUM FULL
).
Le nombre de ces workers est limité, afin de ne pas engendrer de charge trop élevée.
autovacuum
(on
par défaut) détermine si
l’autovacuum doit être activé.
Il est fortement conseillé de laisser autovacuum
à
on
!
S’il le faut vraiment, il est possible de désactiver l’autovacuum sur une table précise :
ALTER TABLE nom_table SET (autovacuum_enabled = off);
mais cela est très rare. La valeur off
n’empêche pas le
déclenchement d’un VACUUM FREEZE
s’il devient
nécessaire.
autovacuum_naptime
est le temps d’attente entre deux
périodes de vérification sur la même base (1 minute par défaut). Le
déclenchement de l’autovacuum suite à des modifications de tables n’est
donc pas instantané.
autovacuum_max_workers
est le nombre maximum de
workers que l’autovacuum pourra déclencher simultanément,
chacun s’occupant d’une table (ou partition de table). Chaque table ne
peut être traitée simultanément que par un unique worker. La
valeur par défaut (3) est généralement suffisante. Néanmoins, s’il y a
fréquemment trois autovacuum workers travaillant en même temps,
et surtout si cela dure, il peut être nécessaire d’augmenter ce
paramètre. Cela est fréquent quand il y a de nombreuses petites tables.
Noter qu’il faudra peut-être être plus généreux avec les ressources
allouées (paramètres autovacuum_vacuum_cost_delay
ou
autovacuum_vacuum_cost_limit
), car les workers se
les partagent.
L’autovacuum déclenche un VACUUM
ou un
ANALYZE
à partir de seuils calculés sur le principe d’un
nombre de lignes minimal (threshold) et d’une proportion de la
table existante (scale factor) de lignes modifiées, insérées ou
effacées. (Pour les détails précis sur ce qui suit, voir la
documentation officielle.)
Ces seuils pourront être adaptés table par table.
Pour le VACUUM
, si on considère les enregistrements
morts (supprimés ou anciennes versions de lignes), la condition de
déclenchement est :
nb_enregistrements_morts (pg_stat_all_tables.n_dead_tup) >=
autovacuum_vacuum_threshold
+ autovacuum_vacuum_scale_factor × nb_enregs (pg_class.reltuples)
où, par défaut :
autovacuum_vacuum_threshold
vaut 50 lignes ;autovacuum_vacuum_scale_factor
vaut 0,2 soit 20 % de la
table.Donc, par exemple, dans une table d’un million de lignes, modifier
200 050 lignes provoquera le passage d’un VACUUM
.
Pour les grosses tables avec de l’historique, modifier 20 % de la
volumétrie peut être extrêmement long. Quand l’autovacuum
lance enfin un VACUUM
, celui-ci a donc beaucoup de travail
et peut durer longtemps et générer beaucoup d’écritures. Il est donc
fréquent de descendre la valeur de
vacuum_vacuum_scale_factor
à quelques pour cent sur les
grosses tables. (Une alternative est de monter
autovacuum_vacuum_threshold
à un nombre de lignes élevé et
de descendre autovacuum_vacuum_scale_factor
à 0, mais il
faut alors calculer le nombre de lignes qui déclenchera le nettoyage, et
cela dépend fortement de la table et de sa fréquence de mise à
jour.)
S’il faut modifier un paramètre, il est préférable de ne pas le faire
au niveau global mais de cibler les tables où cela est nécessaire. Par
exemple, l’ordre suivant réduit à 5 % de la table le nombre de lignes à
modifier avant que l’autovacuum
y lance un
VACUUM
:
ALTER TABLE nom_table SET (autovacuum_vacuum_scale_factor = 0.05);
À partir de PostgreSQL 13, le VACUUM
est aussi lancé
quand il n’y a que des insertions, avec deux nouveaux paramètres et un
autre seuil de déclenchement :
nb_enregistrements_insérés (pg_stat_all_tables.n_ins_since_vacuum) >=
autovacuum_vacuum_insert_threshold
+ autovacuum_vacuum_insert_scale_factor × nb_enregs (pg_class.reltuples)
Pour l’ANALYZE
, le principe est le même. Il n’y a que
deux paramètres, qui prennent en compte toutes les lignes modifiées
ou insérées, pour calculer le seuil :
nb_insert + nb_updates + nb_delete (n_mod_since_analyze) >=
autovacuum_analyze_threshold + nb_enregs × autovacuum_analyze_scale_factor
où, par défaut :
autovacuum_analyze_threshold
vaut 50 lignes ;autovacuum_analyze_scale_factor
vaut 0,1, soit
10 %.Dans notre exemple d’une table, modifier 100 050 lignes provoquera le
passage d’un ANALYZE
.
Là encore, il est fréquent de modifier les paramètres sur les grosses tables pour rafraîchir les statistiques plus fréquemment.
Les insertions ont toujours été prises en compte pour
ANALYZE
, puisqu’elles modifient le contenu de la table. Par
contre, jusque PostgreSQL 12 inclus, VACUUM
ne tenait pas
compte des lignes insérées pour déclencher son nettoyage. Or, cela avait
des conséquences pour les tables à insertion seule (gel de lignes
retardé, Index Only Scan impossibles…)
Pour
cette raison, à partir de la version 13, les insertions sont aussi
prises en compte pour déclencher un VACUUM
.
En fonction de la tâche exacte, de l’agressivité acceptable ou de l’urgence, plusieurs paramètres peuvent être mis en place.
Ces paramètres peuvent différer (par le nom ou la valeur) selon
qu’ils s’appliquent à un VACUUM
lancé manuellement ou par
script, ou à un processus lancé par l’autovacuum.
Quand on lance un ordre VACUUM
, il y a souvent urgence,
ou l’on est dans une période de maintenance, ou dans un batch. Les
paramètres que nous allons voir ne cherchent donc pas, par défaut, à
économiser des ressources.
À l’inverse, un VACUUM
lancé par l’autovacuum ne doit
pas gêner une production peut-être chargée. Il existe donc des
paramètres autovacuum_*
surchargeant les précédents, et
beaucoup plus conservateurs.
maintenance_work_mem
est la quantité de mémoire qu’un
processus effectuant une opération de maintenance (c’est-à-dire
n’exécutant pas des requêtes classiques comme SELECT
,
INSERT
, UPDATE
…) est autorisé à allouer pour
sa tâche de maintenance.
Cette mémoire est utilisée lors de la construction d’index ou l’ajout
de clés étrangères. et, dans le contexte de VACUUM
, pour
stocker les adresses des enregistrements pouvant être recyclés. Cette
mémoire est remplie pendant la phase 1 du processus de
VACUUM
, tel qu’expliqué plus haut.
Rappelons qu’une adresse d’enregistrement (tid
, pour
tuple id
) a une taille de 6 octets et est composée du
numéro dans la table, et du numéro d’enregistrement dans le bloc, par
exemple (0,1)
, (3164,98)
ou
(5351510,42)
.
Le défaut de 64 Mo est assez faible. Si tous les enregistrements
morts d’une table ne tiennent pas dans
maintenance_work_mem
, VACUUM
est obligé de
faire plusieurs passes de nettoyage, donc plusieurs parcours complets de
chaque index. Une valeur assez élevée de
maintenance_work_mem
est donc conseillée : s’il est déjà
possible de stocker plusieurs dizaines de millions d’enregistrements à
effacer dans 256 Mo, 1 Go peut être utile lors de grosses purges.
Attention, plusieurs VACUUM
peuvent tourner
simultanément.
Un maintenance_work_mem
à plus de 1 Go est inutile pour
le VACUUM
(il ne sait pas utiliser plus),
par contre il peut accélérer l’indexation de grosses
tables.
autovacuum_work_mem
permet de surcharger
maintenance_work_mem
spécifiquement pour l’autovacuum. Par
défaut les deux sont identiques.
Les paramètres suivant permettent de provoquer une pause d’un
VACUUM
pour ne pas gêner les autres sessions en saturant le
disque. Ils affectent un coût arbitraire aux trois actions
suivantes :
vacuum_cost_page_hit
: coût d’accès à une page présente
dans le cache (défaut : 1) ;vacuum_cost_page_miss
: coût d’accès à une page hors du
cache (défaut : 10 avant la v14, 2 à partir de la v14) ;vacuum_cost_page_dirty
: coût de modification d’une
page, et donc de son écriture (défaut : 20).Il est déconseillé de modifier ces paramètres de coût. Ils permettent
de « mesurer » l’activité de VACUUM
, et le mettre en pause
quand il aura atteint cette limite. Ce second point est gouverné par
deux paramètres :
vacuum_cost_limit
: coût à atteindre avant de
déclencher une pause (défaut : 200) ;vacuum_cost_delay
: temps à attendre (défaut :
0 ms !)En conséquence, les VACUUM
lancés manuellement (en ligne
de commande ou via vacuumdb
) ne sont pas
freinés par ce mécanisme et peuvent donc entraîner de fortes écritures,
du moins par défaut. Mais c’est généralement dans un batch ou en
urgence, et il vaut mieux alors être le plus rapide possible. Il est
donc conseillé de laisser vacuum_cost_limit
et
vacuum_cost_delay
ainsi, ou de ne les modifier que le temps
d’une session ainsi :
SET vacuum_cost_limit = 200 ;
SET vacuum_cost_delay = '20ms' ;
VACUUM (VERBOSE) matable ;
(Pour les urgences, rappelons que l’option
INDEX_CLEANUP off
permet en plus d’ignorer le nettoyage des
index, à partir de PostgreSQL 12.)
Les VACUUM
d’autovacuum, eux, sont par défaut limités en
débit pour ne pas gêner l’activité normale de l’instance. Deux
paramètres surchargent les précédents :
autovacuum_cost_limit
vaut par défaut -1, donc reprend
la valeur 200 de vacuum_cost_limit
;autovacuum_vacuum_cost_delay
vaut par défaut 2 ms (mais
20 ms avant la version 12, ce qui correspond à l’exemple
ci-dessus).Un (autovacuum_
)vacuum_cost_limit
de 200
correspond à traiter au plus 200 blocs lus en cache (car
vacuum_cost_page_hit
= 1), soit 1,6 Mo, avant de faire une
pause. Si ces blocs doivent être écrits, on descend en-dessous de 10
blocs traités avant chaque pause (vacuum_cost_page_dirty
=
20) avant la pause de 2 ms, d’où un débit en écriture maximal de
l’autovacuum de 40 Mo/s (avant la version 12 : 20 ms et seulement
4 Mo/s !), et d’au plus le double en lecture. Cela s’observe aisément
par exemple avec iotop
.
Ce débit est partagé équitablement entre les différents workers lancés par l’autovacuum (sauf paramétrage spécifique au niveau de la table).
Pour rendre l’autovacuum
plus agressif, on peut
augmenter la limite de coût, ou réduire le temps de pause, à condition
de pouvoir assumer le débit supplémentaire pour les disques. La version
12 a justement réduit le délai pour tenir compte de l’évolution des
disques et des volumétries.