To sidebar

dimanche 13 mai 2018

VACUUM et agressivité

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é.

© SQL & éléphant, after the WP Dusk To Dawn theme Propulsé par Dotclear