Le modèle MVCC de PostgreSQL a un avantage : une session ne s’embête pas à supprimer physiquement les données qu’elle a modifiées, et gagne ainsi du temps. De plus, ces lignes pourraient encore être utiles à une autre transaction. L’inconvénient, c’est qu’il va bien falloir s’en occuper plus tard — en théorie à un moment moins chargé.
Il faut donc effectuer régulièrement un VACUUM
sur les tables modifiées pour éviter le bloat, la boursouflure, le gonflement, l’embonpoint né de ces lignes mortes qu’aucune transaction ne peut plus voir. Dans les versions antiques de PostgreSQL, le VACUUM
devait être passé manuellement ; à présent il est déclenché en arrière-plan par le démon autovacuum
.
La configuration par défaut suffit en général, mais quand il faut y toucher, les nombreux paramètres ne rendent pas la chose très lisible. Il y a bien sûr la doc que l’on peut déjà trouver en ligne, l’officielle ou chez Dalibo, ou encore ce billet chez 2nd Quadrant. Mais rien ne vaut un bon test.
Je laisse de côté VACUUM FREEZE
, VACUUM ANALYZE
, VACUUM FULL
pour me limiter ici au VACUUM
basique, purgeur de lignes mortes.
VACUUM manuel
Créons une table et passons dessus le VACUUM
:
DROP TABLE IF EXISTS vc ; CREATE TABLE vc (i int) ; INSERT INTO vc SELECT i FROM generate_series (1,10000000) i ; VACUUM VERBOSE vc ;
(Au passage il peut sembler idiot de vacuumer une table fraîchement créée. Cependant à cette occasion PostgreSQL crée la visibility map : elle permet de savoir si une ligne trouvée dans un index est dans un bloc assez récemment modifié pour qu’il soit besoin d’aller vérifier dans la table si la ligne est visible par la transaction en cours. De plus cela va accélérer les VACUUM
suivants. Finissez donc vos chargements massifs par VACUUM ANALYZE
et pas juste ANALYZE
.)
Sur mon portable avec SSD le VACUUM
avec option VERBOSE
renvoie ceci :
INFO: vacuuming "public.vc" INFO: "vc": found 0 removable, 10000000 nonremovable row versions in 44248 out of 44248 pages DÉTAIL : 0 dead row versions cannot be removed yet, oldest xmin: 8570578 There were 0 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 4.16 s, system: 0.04 s, elapsed: 4.27 s. VACUUM Durée : 5296,537 ms (00:05,297)
Aucune ligne nettoyée (normal). 44 248 pages (362 Mo) en 5,3 s nous donnent 68 Mo/s.
Si on repasse le VACUUM
dessus, il ne traite plus qu’un bloc en quelques millisecondes car la visibility map a déjà été créée et lui permet de savoir qu’il n’y a pas besoin de repasser nulle part.
Paramètres
Le VACUUM
incrémente un compteur avec les valeurs suivantes pour chaque bloc selon qu’il est en mémoire cache de PostgreSQL, qu’il n’y est pas et/ou qu’il est modifié :
vacuum_cost_page_hit = 1 vacuum_cost_page_miss = 10 vacuum_cost_page_dirty =20
jusqu’à atteindre ce seuil :
vacuum_cost_limit = 200
et alors le VACUUM
fait une pause de :
vacuum_cost_delay = 0 ms
C’est-à-dire que par défaut le VACUUM
va le plus vite possible et tant pis pour les autres sessions. C’est surtout au niveau des accès disques qu’un VACUUM
manuel peut être gênant. Mais pour paraphraser mes collègues : quand on lance un VACUUM
explicite, c’est en général la nuit (planification régulière ou au milieu d’un batch) ou c’est une urgence.
Autovacuum
L’autovacuum cherche à être discret et va étaler son activité.
Il attend qu’un certain seuil de modification soit atteint avant de lancer un VACUUM
(paramètres autovacuumvacuumscale_factor
, autovacuum_vacuum_threshold
) ou un ANALYZE
(autovacuum_analyze_threshold
, autovacuum_analyze_scale_factor
) sur une table. On touche souvent ces paramètres pour qu’il s’intéresse plus souvent aux grosses tables.
De plus, quand l’autovacuum lance un « worker » sur une table, il suit ces paramètres :
autovacuum_vacuum_cost_limit = -1 (donc 200, valeur de vacuum_cost_limit) autovacuum_vacuum_cost_delay = 20 ms
en plus des vacuumcostpage_hit
, vacuumcostpage_miss
, vacuumcostpage_dirty
déjà évoqués.
Donc, dans le cas le meilleur, en travaillant dans le cache (vacuum_cost_page_hit
à 1), le worker atteint la limite avec 200 blocs de 8 ko (1,6 Mo), puis attend 20 ms avant de traiter la suite. Mais fréquemment les blocs seront à écrire (pénalité : vacuum_cost_page_dirty
= 20), soit 10 blocs (82 ko) au maximum avant la pause, donc un temps total pour ma table d’exemple de 44 248 blocs / 10 blocs * 20 ms = 88 s, ou encore un débit de 10 blocs /20 ms = 4 Mo/s… au mieux.
Simulation
Pour tester, on va lancer un VACUUM
mais auparavant on pose dans la session la valeur par défaut de l’autovacuum :
SET vacuum_cost_delay = '20 ms';
Pour ne pas perturber le test, j’ajoute la mention que l’autovacuum ne doit pas s’occuper de la table (il peut être assez rapide à intervenir pour fausser le test) :
ALTER TABLE vc SET ( autovacuum_enabled = off ) ;
Je rajoute un DELETE
presque complet pour forcer le VACUUM
à réécrire tous les blocs :
DELETE FROM vc WHERE i<10000000 ;
Un VACUUM VERBOSE vc
renvoie ensuite :
INFO: vacuuming "public.vc" INFO: "vc": removed 9999999 row versions in 44248 pages INFO: "vc": found 9999999 removable, 1 nonremovable row versions in 44248 out of 44248 pages DÉTAIL : 0 dead row versions cannot be removed yet, oldest xmin: 8570610 There were 0 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 4.52 s, system: 0.06 s, elapsed: 85.65 s. VACUUM Durée : 85681,197 ms (01:25,681)
On est un rien en-dessous des 88 secondes calculées. En toute rigueur, le test peut souvent donner des valeurs inférieures. Le mécanisme n’est sans doute pas aussi primaire que décrit ci-dessus. Mais on est bien deux ordres de grandeur au-dessus de la seconde que dure un VACUUM
non freiné par vacuum_cost_delay
.
Un autre moyen de vérifier ce début est iotop
(ici sur une bien plus grosse table) : on s’approche des 4 Mo/s calculés.
TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND 23336 be/4 postgres 2.33 M/s 3.94 M/s 0.00 % 0.59 % postgres: 10/maousse: postgres vac 192.168.74.150(46240) VACUUM
Nombre de workers
Dans le cas d’une énorme table, le VACUUM
(lancé par l’autovacuum) peut être très long : par exemple il a eu besoin de 8 h pour une table de 100 Go (2 milliards de lignes) sur mon autre PC doté d’un disque classique.
On pourrait être tenté d’augmenter autovacuum_max_workers
, par défaut à 3. Cela n’a pas d’intérêt ici : une table n’est en pratique traitée que par un seul et unique worker à la fois. La doc mériterait d’être plus explicite sur le sujet.
Là encore, est-ce un problème si le VACUUM
est lent ? Le bloat augmente-t-il indéfiniment parce que les écritures sont intensives ? Si oui, on peut soit le rendre plus agressif en jouant sur vacuum_cost_delay
ou vacuum_cost_limit
, soit le lancer manuellement (à priori avec une agressivité maximale) à des périodes de moindre activité.
Par contre, dans le cas d’un schéma avec de nombreuses tables toutes fréquemment mises à jour, ou de tables avec des partitions toutes mises à jour, augmenter le nombre de workers est envisageable, et là effectivement on parallélise… mais le débit global reste constant ! Heureusement, sinon un grand nombre de workers entraînerait la saturation des accès disques lors de mises à jour réparties.
Ajout de 2020 : À partir de PostgreSQL 12, vacuum_cost_delay
descend par défaut à 2 ms, pour un débit donc bien plus élevé.