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 autovacuum_vacuum_scale_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 vacuum_cost_page_hit, vacuum_cost_page_miss, vacuum_cost_page_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.

jeudi 26 janvier 2017

Réplication avec PostgreSQL 9.6 : l'exemple minimaliste

Ce qui suit décrit le strict minimum pour mettre en place deux instances en réplication maître/esclave avec PostgreSQL 9.6.

Les chemins par défaut sont ceux sur CentOS 6, PostgreSQL étant installé depuis les dépôts PGDG. Les instances tourneront sur la même machine sur les ports 9601 et 9602.

Création du maître

sudo -iu postgres /usr/pgsql-9.6/bin/initdb -D /var/lib/pgsql/9.6/I1

Modifier les options suivantes dans postgresql.conf dans ce dernier répertoire :

port = 9601
wal_level = replica
max_wal_senders = 5
max_replication_slots = 5

Dans pg_hba.conf, autoriser la réplication à l’utilisateur postgres en connexion locale sans mot de passe (!) :

local   replication     postgres                                trust

Lancement du maître :

/usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/I1 start

Création d’un « slot de réplication » qui évitera de mettre en place l’archivage des journaux de transaction :

psql -p 9601
postgres=# select pg_create_physical_replication_slot ('slot_i2') ;

Création de l’esclave

Recopie des fichiers du maître (même si le maître est potentiellement en train de les modifier) :

 /usr/pgsql-9.6/bin/pg_basebackup -D /var/lib/pgsql/9.6/I2/ -X stream -S 'slot_i2'
--checkpoint=fast --verbose --port 9601 --write-recovery-conf

Cette recopie inclut les fichiers de configuration, mais il faut les adapter :

Dans postgresql.conf :

port 9602
hot_standby = on

Un fichier /var/lib/pgsql/9.6/I2/recovery.conf a été créé ; il faut lui rajouter la dernière ligne ;

standby_mode = 'on'
primary_conninfo = 'user=postgres port=9601 sslmode=prefer sslcompression=1 krbsrvname=postgres'
primary_slot_name = 'slot_i2'
recovery_target_timeline = 'latest'

Démarrage de l’esclave :

/usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/I2 start

Contrôler que tout va bien en consultant les logs dans /var/lib/pgsql/9.6/I2/pg_log/.

Fin de la mise en place. Toute création ou modification de données sur le maître doit se répercuter sur l’esclave, qui n’est accessible qu’en lecture seule.

Hors du labo

Pour une mise en prod en conditions réelles, il eût fallu :

  • utiliser deux serveurs : la configuration sur une machine n’offre aucun intérêt en sécurité ni performance ;
  • intégrer les scripts de démarrage et d’arrêt au système local (ici en Red Hat 6 : dans /etc/init.d/postgresql-9.6-I1 et -I2, compléter /etc/sysconfig/postgresql ou l’usine à gaz qu’est systemd) et sous Debian tenir compte des wrappers qui enrobent pg_ctl ainsi que des chemins différents (postgresql.conf dans /etc/postgresql.conf/9.6/, données dans /val/lib/postgresql, binaires dans /usr/lib/postgresql/9.6/bin/, etc. ) ;
  • dans le pg_hba.conf, utiliser un utilisateur dédié à la réplication et surtout avec mot de passe (ici j’ai juste décommenté la partie par défaut dans l’installation sur CentOS, ça ne fonctionne que parce qu’on utilise la même machine pour les deux instances) ;
  • éventuellement : configurer un archivage des journaux de transactions (ceux de pg_xlog) dans postgresql.conf : archive_mode = on, archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' (ou plus complexe et plus sûr…) ; et les récupérer dans l’esclave avec dans recovery.conf la restore_command et éventuellement archive_cleanup_command ; toute choses à intégrer aux sauvegardes PITR (via les journaux de transaction) ;
  • configurer éventuellement d’autres paramètres permettant d’utiliser plus sereinement l’esclave quand on requête dessus, notamment hot_standby_feedback = on, max_standby_archive_delay, max_standby_streaming_delay.

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