To sidebar

jeudi 8 octobre 2020

Le réchauffement climatique & PostgreSQL : interdire la glaciation

Par Christophe - Lien permanent

Un des défauts choix de l’implémentation de PostgreSQL est le numéro de transactions sur 32 bits, qui oblige à nettoyer périodiquement les vieux numéros de transaction en marquant les lignes comme « gelées ». Cette opération se déroule lors d’un VACUUM FREEZE (doc), et elle est parfois assez embêtante.

Que se passe-t-il quand on croit bien faire en le désactivant ?

Parce que la tentation est grande de ne plus faire de VACUUM FREEZE ! SI le VACUUM simple, qui récupère les lignes mortes, ne pose généralement pas de problème, c’est une autre paire de manche avec le FREEZE.

Avant PostgreSQL 9.6, même une table déjà complètement gelée est régulièrement re-parcourue pour ce gel, et cela peut être long. De plus, avant PostgreSQL 12, les paramètres par défaut de l’autovacuum (le démon) sont très conservateurs (cf mon vieux billet sur la vitesse du VACUUM simple). Si on ne (veu|sai)t pas les changer, un VACUUM FREEZE peut être beaucoup plus lent que nécessaire. Il est aussi connu qu’après un pg_restore (restauration, migration…) il vaut mieux passer un VACUUM FREEZE à titre préventif dans une période calme, pas trop longtemps après, pour éviter que toutes les données chargées ensemble soient gelées en même temps à un moment qui, évidemment, sera le pire possible. À cette occasion, on voit généralement une grosse partie de la base de données réécrite. Enfin, si le VACUUM simple ne pose guère de verrou gênant, le VACUUM FREEZE lui, empêche par exemple des LOCK TABLE, chose que l’on voit encore trop souvent, mais légitime.

Bref, que se passe-t-il si on ne veut plus se faire embêter et qu’on débraye le FREEZE ? Et qu’on reste sourd à ses plaintes ?

Rappelons que, par défaut et si rien ne l’empêche, les lignes sont gelées au bout de 200 millions de transactions (autovacuum_freeze_max_age), une valeur relativement basse, que l’on peut éventuellement monter un peu. Les lignes gelées sont taggées comme « infiniment vieilles ». Quand le compteur sur 32 bits (~4 milliards) bouclera, il n’y aura donc plus de confusion possible avec les nouveaux numéros de transaction identiques aux anciens.

Script

D’abord il faut simuler la création de millions de transactions. La procédure suivante crée le nombre de transactions demandé en paramètres, et indique l’âge des différentes bases (en gros, l’âge de la plus ancienne table de la base, soit l’âge de la plus ancienne ligne non encore gelée dans cette table).

CREATE OR REPLACE PROCEDURE burn_transactions (nbtrx int) 
LANGUAGE plpgsql 
AS $$
DECLARE
    d   record ;
BEGIN
    SET synchronous_commit TO off ;
    FOR i IN 1..nbtrx LOOP
        PERFORM txid_current() ;
        COMMIT ;
    END LOOP ;
    RAISE NOTICE 'Burned % million transactions until %', round((nbtrx/1e6),1), txid_current() ;

    FOR d IN (
        SELECT datname, datfrozenxid, txid_current(),
        round(age(datfrozenxid) / 1e6,1) AS age_Mtrx
        FROM pg_database
        ORDER BY 4 DESC LIMIT 2
        ) LOOP
        RAISE NOTICE 'DB % - datfrozenxid % - age % Mtrx', d.datname, d.datfrozenxid, d.age_Mtrx;
    END LOOP ;
END
$$ ;

Sur mon portable, brûler un million de transactions dure 14 secondes :

# CALL burn_transactions (1000000) ;
NOTICE:  Burned 1.0 million transactions until 295202758s
NOTICE:  DB partitions_100 - datfrozenxid 150649456 - age 145.0 Mtrx
NOTICE:  DB partitions_0 - datfrozenxid 150851915 - age 144.9 Mtrx
CALL
Durée : 14732,587 ms (00:14,733)

Or, il faudra en brûler rapidement 4 milliards. On pourra paralléliser les appels grâce à ce script pgbench :

-- burn_transactions_pgbench.sql
-- A exécuter par pgbench
-- Suppose que la procédure burn_transactions a été installée
--
SET application_name='burning transactions' ;
SELECT txid_current();
SET log_min_duration_statement TO 0 ;
CALL burn_transactions (1000*:scale);
SELECT txid_current();

Test

Sur une machine à 8 processeurs, j’ai lancé ce script sur 6 processus en parallèle pendant 2 h :

$ pgbench -s1000 -c6 -f burn_transactions_pgbench.sql -n -T7200

On constate alors que le facteur limitant devient le walwriter qui s’escrime à écrire les journaux de transactions, et monopolise quasiment un processeur (sur un SSD). Pour accélérer, le plus simple est de passer le paramètre fsync à off (rappel de principe : ne faites jamais ça en prod : corruption garantie au premier crash). Cette machine atteint alors 500 000 transactions/s. Les 4 milliards de transactions sont « grillées » en seulement 2 heures !

Dès le seuil de 200 millions atteint, l’autovacuum déclenche un FREEZE sur toutes les tables, ce qui peut prendre du temps. 200 millions de transactions plus tard, il recommence, ce qui est bref si les données n’ont pas été modifiées (sur PostgreSQL 9.6 et suivants). Et ainsi de suite. Sur une base peu sollicitée, cette torture n’a pas de raison de poser de problème.

À l’approche de l’horizon

Que se passe-t-il quand on franchit le numéro fatidique des 4 milliards ? En fait, pas grand-chose. Avant la limite, on peut comparer le numéro de transaction visible au sein de la base :

# SELECT txid_current();
 txid_current 
--------------
    295202931

avec celui visible au niveau système (celui du dernier CHECKPOINT) :

$ sudo -iu postgres /usr/lib/postgresql/12/bin/pg_controldata -D /var/lib/postgresql/12/pagode/
pg_control version number:            1201
...
Latest checkpoint's NextXID:          0:295202926
...
Latest checkpoint's oldestXID:        150649456
Latest checkpoint's oldestXID's DB:   3563452
Latest checkpoint's oldestActiveXID:  295202926

(SELECT next_xid FROM pg_control_checkpoint() est une option équivalente.) À une poignée de transaction de retard près (le CHECKPOINT est récent), le 0:295202926 correspond au 295202931 plus haut.

Par la suite, une fois franchi le cap des 4 milliards, on pourra trouver :

postgres=# checkpoint; select txid_current ();
CHECKPOINT
 txid_current 
--------------
   7605939660

et :

Latest checkpoint's NextXID:          1:3310972364

Or, 3310972364 + 2³² = 7605939660. Le 1 indique le nombre de bouclages ou l’époque. Un FullTransactionId dans le code de PostgreSQL, est donc bien sur 64 bits, c’est le stockage dans les lignes qui est sur 32.

Comment plomber le FREEZE

Peu de bases arrivent à 2³² transactions, mais cela arrive. Et la transition n’a aucun effet. Le problème est ailleurs, dans un éventuel retard de milliards de transactions. Et pour provoquer un retard, il faut éviter que le VACUUM nettoie des transactions anciennes. Cela se rencontre en production de trois manières différentes :

Une transaction idle in transaction (qui fait BEGIN, modifie des lignes, ne fait ni COMMIT ni ROLLBACK)

Les lignes concernées ne seront pas nettoyées puisqu’elles sont encore visibles des autres transactions. Les idle in transaction fréquents sont un moyen sûr d’encourager le bloat (« boursouflure », je n’aime pas traduire par « fragmentation »).

Au pire, ces transactions pendantes disparaissent lors d’un redémarrage de l’instance. Le réflexe à la Windows en mode panique a parfois du bon.

Les slots de réplication

Dans ce cas, un serveur secondaire (généralement) indique au serveur primaire où il en est dans la réplication. Pour éviter que des blocs non encore répliqués soient vacuumés alors que le secondaire les utilise, le primaire retient son VACUUM sur les lignes de transactions antérieures à celles notées dans le slot (je simplifie).

Un problème est qu’un slot survit, lui, à un reboot. Problème classique : on détruit un secondaire, on ne pense pas à supprimer le slot, et le serveur primaire attend indéfiniment que ce secondaire lui dise où il en est de la réplication. En général, le premier symptôme sera plutôt l’explosion de la taille des journaux de transaction que le serveur primaire croit devoir garder sous la main. (C’est une raison pour laquelle on préférera sécuriser un secondaire plutôt par du log shipping ; mais je digresse.) Le paramètre hot_standby_feedback mène un peu au même phénomène.

Transactions préparées

(Ne pas confondre avec les requêtes préparées, très classiques, elles.) Les transactions préparées permettent des commits en 2 phases. La transaction est faite normalement mais le COMMIT est remplacé par PREPARE TRANSACTION 'unnomarbitraire', ce qui libère la session, mais conserve les verrous. Pour être visible, la transaction doit être committée définitivement avec COMMIT PREPARED après que l’applicatif a fait sa tambouille sur un autre serveur.

Une transaction préparée 1) survit aussi à un redémarrage et 2) est rarement utilisée (déjà le paramètre max_prepared_transactions est par défaut à 0), donc personne ne pense à ce piège.

Premier blocage avec idle in transaction

Pour commencer on va se limiter à une simple transaction qui oubliera de faire COMMIT ou ROLLBACK (donc idle in transaction). Qu’importe la base, qu’importe la volumétrie. Pendant que cette transaction reste indécise, les scripts ci-dessus brûlent des numéros.

Au bout d’un moment, il devient impossible de rentrer un nouvel ordre :

ERROR:  database is not accepting commands to avoid wraparound data loss in database "pgbench_1"
ASTUCE : Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

Une fois la transaction coupable committée ou rollbackée, ou l’instance redémarrée, le FREEZE redevient possible. Comme il faut le faire sur toutes les bases (les numéros de transaction sont partagés), le plus rapide est de le faire avec l’outil vacuumdb :

$ vacuumdb --freeze -a

Ça ne prend pas forcément beaucoup de temps, même sur une grosse base, si les données sont assez statiques (les blocs déjà complètement gelés sont stockés dans la visibility map).

Blocage durable avec une transaction préparée

Sur une base qui a déjà du vécu, poussons le bouchon un peu plus loin :

BEGIN ;
PREPARE TRANSACTION 'trx42' ;
-- la transaction préparée est enregistrée ; ce qui suit sera une nouvelle transaction

postgres=# select * from pg_prepared_xacts ;
 transaction |  gid  |           prepared            |  owner   | database 
-------------+-------+-------------------------------+----------+----------
  3208972058 | trx42 | 2020-09-14 09:24:37.058837+02 | postgres | postgres

DELETE  FROM une_table;  
-- NB les lignes effacées existaient avant le PREPARE

Oublions-la (c’est courant avec une transaction préparée) et relançons pgbench -f burn_transactions_pgbench.sql. Il va à nouveau arriver un moment où PostgreSQL va se plaindre :

postgres=# DELETE FROM t_une_ligne ;
WARNING:  database "pgbench_1" must be vacuumed within 9597770 transactions
ASTUCE : To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

Un VACUUM FREEZE génère erreurs et avertissements (non bloquants), dont celui qu’il ne peut tout nettoyer (nonremovable), car des lignes datent de la transaction 3 208 972 058 :

postgres=# VACUUM (FREEZE, VERBOSE) t_un_million_int;
WARNING:  oldest xmin is far in the past
ASTUCE : Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
INFO:  aggressively vacuuming "public.t_un_million_int"
INFO:  "t_un_million_int": found 0 removable, 176 nonremovable row versions in 1 out of 4425 pages
DÉTAIL : 0 dead row versions cannot be removed yet, oldest xmin: 3208972058
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 4424 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
WARNING:  database "postgres" must be vacuumed within 9597769 transactions
ASTUCE : To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

À première vue, il y a une incohérence avec la transaction en cours qui est déjà très loin, à 9 milliards :

postgres=# select txid_current () ;
WARNING:  database "pgbench_1" must be vacuumed within 9597769 transactions
ASTUCE : To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
 txid_current 
--------------
   9641825232

Le pg_controldata renvoie bien 2:1051890641, correspondant à ce 9 milliards. Il n’y a pas 6 milliards d’écart entre la transaction courante et celle de la transaction préparée, car en fait la transaction préparée n’est pas à 3208972058, mais à 3208972058 + 2³² (7,5 milliards). C’est donc le numéro de la transaction modulo 2³² qui est affiché dans le DÉTAIL.[1]

PostgreSQL annonce un VACUUM à faire dans 9,6 millions de transactions. Pourquoi ?

Parce qu’une fois ce délai achevé, on arrivera à la transaction problématique + 2 milliards. Ces 2 milliards (en fait 2³¹) sont en fait l’écart maximal toléré par PostgreSQL, bien que la transaction soit stockée sur 32 bits (4 milliards de valeurs). Les 4 milliards sont en fait coupés en 2 pour séparer passé et futur. Une transaction peut rencontrer des lignes créées bien après son propre démarrage, elle doit donc savoir discerner passé et futur, alors que le cycle sur 32 bits a pu boucler entre temps. La convention est donc que des numéros de transaction sur les lignes entre le numéro courant et lui + 2³¹ (maximum 2³²) correspondent au futur, ainsi éventuellement que ceux entre 0 et (le numéro courant + 2³¹) modulo 2³². Et entre ce dernier chiffre et le numéro courant, c’est le passé. (En réalité, 1 et 2 sont réservés, notamment pour indiquer que la ligne est gelée ; si ce n’est plus utile après PostgreSQL 9.4, des bases migrées par pg_upgrade peuvent encore contenir ces identifiants). Le code est étonnamment basique.

Ici, 9 641 825 232 (transaction en cours) correspond en 32 bits à 9 641 825 232 modulo 2³² = 1 051 890 640. Donc le futur de cette transaction va jusquà 1 051 890 640 + 2³¹ = 3 199 374 288. Et son passé va de 3 199 374 289 à 2³²-1, puis de 3 à 1 051 890 640. La valeur 3 208 972 058 de notre transaction préparée est donc bien dans le passé… mais dans 9 597 770 transactions, la transaction préparée sera dans le futur des nouvelles transactions.

Et, en 64 bits, on a bien 9 641 825 232 (transaction en cours) + 9 597 769 (délai de grâce) + 2³¹ (tout le futur) = 3 208 972 058 (où est la transaction préparée en 32 bits) + 2 × 2³² (2 cycles) = 11 798 906 650 (la transaction où on mélangerait le passé et le futur).

Il faut donc impérativement que le FREEZE passe avant cette catastrophe. Pendant ce temps, la trace se remplit de messages d’alerte. (Donc la saturation de /var/log, / ou PGDATA est un autre danger.)

Jouer avec le feu (suite)

Accordons à ces avertissements la même attention que Donald Trump à un rapport du GIEC, et continuons de griller du charbon des transactions. Car rien n’est bloqué, et il reste possible de lancer des CALL burn_transactions (1000000). Les WARNING défilent, on ne pourra pas dire que l’on n’était pas au courant (enfin, si l’on n’est pas Donald Trump.)

Approchons du moment fatal :

WARNING:  database "pgbench_1" must be vacuumed within 4587234 transactions
...
WARNING::  database "pgbench_1" must be vacuumed within 1000000 transaction

Finalement, tout s’arrête 1 million de transactions avant la transaction fatale :

CALL burn_transactions (10000);
ERROR:  database is not accepting commands to avoid wraparound data loss in database "pgbench_1"
ASTUCE : Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
CONTEXTE : SQL statement "SELECT txid_current()"
PL/pgSQL function burn_transactions(integer) line 7 at PERFORM

Toute commande consommant des transactions est alors purement et simplement interdite. La limite de 1 million est en dur. La doc dit :

Si ces avertissements sont ignorés, le système s’arrêtera et refusera de commencer toute nouvelle transaction dès qu’il n’en restera qu’un million avant la réinitialisation (…) La marge de sécurité de un million de transactions existe pour permettre à l’administrateur de récupérer ces données sans perte en exécutant manuellement les commandes VACUUM requises. Néanmoins, comme le système n’exécute pas de commandes tant qu’il n’est pas sorti du mode d’arrêt par sécurité, la seule façon de le faire est de stopper le serveur et de démarrer le serveur en mode simple utilisateur pour exécuter le VACUUM.

La transaction préparée est toujours là. Non, PostgreSQL ne la fait pas sauter de lui-même : l’intégrité des données prime sur la continuité de service. (Spoiler : c’est faux, voir plus bas.)

À ce stade, avec les outils habituels, plus aucune transaction ne peut être ouverte.

Et on débloque comment ?

Heureusement sont encore possibles des opérations d’administration qui ne consomment pas de transaction, comme VACUUM, les fonctions de gestion des slots de réplication… ou le drop de transaction préparée :

ROLLBACK PREPARED 'trx42' ;

Ensuite, on peut lancer les VACUUM FREEZE, et, dans mon cas, c’est passé sans problème sur la plupart des bases.

Sauf une, où le gel échoue :

$ vacuumdb -e --freeze -d postgres
VACUUM (FREEZE);
vacuumdb : l'exécution de VACUUM sur la base de données « postgres » a échoué : 
ERROR:  database is not accepting commands to avoid wraparound data loss in database "postgres"

Dans les traces, l’erreur contient plus de détails :

... ERROR:  database is not accepting commands to avoid wraparound data loss in database "postgres"
... HINT:  Stop the postmaster and vacuum that database in single-user mode.
        You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
... CONTEXT:  while truncating relation "public.t_une_ligne" to 0 blocks
        automatic vacuum of table "postgres.public.t_une_ligne"

Le while truncating relation livre la clé. Certes, la table est à présent vide, mais sa ligne n’a jamais été nettoyée (on avait vidé la table t_une_ligne après avoir créé la transaction préparée). Quand il veut nettoyer cette ligne, VACUUM veut aussi tronquer la fin de la table pour récupérer l’espace (normalement un VACUUM simple ne rend pas d’espace au système, sauf en fin de table). Cela échoue, le gel est alors impossible ! (Détails plus bas)

Si on passe en mode panique, il faut savoir qu’à ce stade, un pg_dump reste possible pour sauver l’essentiel (les données, pas la continuité de service).

Une astuce pour débloquer la situation : renoncer au troncage de la table vide. C’est une option récente de VACUUM (PostgreSQL 12) :

VACUUM (FREEZE, TRUNCATE off);

Et le VACUUM peut passer, le gel se fait, toute la base se rouvre !

Forcer le gel avec le mode single

Reproduisons le problème dans une autre instance avec moins de vécu, pour que ce soit plus lisible. La transaction préparée qu’on laissera pendante va consister à vider la seule ligne d’une table :

# CREATE TABLE videmoi as SELECT 1 ;
# BEGIN ;
# SELECT  txid_current();
 txid_current 
--------------
   71889

#* DELETE FROM videmoi ;
DELETE 1

#* PREPARE TRANSACTION 'trx99';
PREPARE TRANSACTION

Dans la session, c’est comme si la transaction était terminée. Mais la transaction préparée reste en arrière-plan.

On ne peut pas nettoyer videmoi, son unique ligne est encore susceptible d’être visible par la transaction préparée (c’est le principe de fonctionnement du MVCC de PostgreSQL).

VACUUM (VERBOSE, FREEZE) videmoi ;

INFO:  aggressively vacuuming "public.videmoi"
INFO:  "videmoi": found 0 removable, 1 nonremovable row versions in 1 out of 1 pages
DÉTAIL : 0 dead row versions cannot be removed yet, oldest xmin: 71889
...

Une fois de plus, brûlons les transactions avec notre script pgbench jusqu’au blocage :

WARNING:  database "postgres" must be vacuumed within 999995 transactions
ASTUCE : To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

(Ici, on arrête la base et on fait une sauvegarde physique pour pouvoir refaire ce qui suit plusieurs fois.)

On débloque :

COMMIT PREPARED 'trx99' ;

La table vidée pose bien à nouveau problème, d’autres tables ont été gelées sans problème :

VACUUM FREEZE ;

SELECT relname, relfrozenxid, age(relfrozenxid)
FROM pg_class
WHERE relname LIKE 'pgbench%' OR relname = 'videmoi';

        relname        | relfrozenxid |    age     
-----------------------+--------------+------------
 pgbench_accounts      |   2146555541 |          0
 pgbench_branches      |   2146555541 |          0
 pgbench_history       |   2146555541 |          0
 pgbench_tellers       |   2146555541 |          0
 pgbench_branches_pkey |            0 | 2147483647
 pgbench_tellers_pkey  |            0 | 2147483647
 pgbench_accounts_pkey |            0 | 2147483647
 videmoi               |        71889 | 2146483652

On a donc bien reproduit le phénomène précédent, avec le VACUUM qui renvoie une erreur :

VACUUM (verbose, freeze) videmoi;

INFO:  aggressively vacuuming "public.videmoi"
INFO:  "videmoi": found 0 removable, 0 nonremovable row versions in 1 out of 1 pages
DÉTAIL : 0 dead row versions cannot be removed yet, oldest xmin: 2146555541
There were 1 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
ERROR:  database is not accepting commands to avoid wraparound data loss in database "postgres"
ASTUCE : Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
CONTEXTE : while truncating relation "public.videmoi" to 0 blocks

L’option TRUNCATE = OFF pourrait à nouveau nous débloquer, dans une version assez récente de PostgreSQL. D’autre part, il y a une possibilité que nous n’avons pas explorée : le mode single.

Il faut arrêter PostgreSQL proprement, puis le relancer d’une manière bien précise. Les outils du système et même pg_ctl sont ici mis de côté. Il faut lancer l’exécutable principal de PostgreSQL en lui indiquant le PGDATA (en fait, le répertoire du postgresql.conf, ici en packaging Debian).

$ /usr/lib/postgresql/13/bin/postgres --single -D /etc/postgresql/13/cycling/
... WARNING:  database with OID 13431 must be vacuumed within 999995 transactions
... HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
        You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
PostgreSQL stand-alone backend 13.0 (Ubuntu 13.0-1.pgdg18.04+1)

Le mode single est particulièrement pénible à utiliser : pas de complétion automatique, pas de mémorisation des commandes, ordres sur une seule ligne, affichage ligne à ligne, traces dans la console même… (Je ne ferai pas la comparaison avec le triste sqlplus d’Oracle, parce que c’est tout de même abusif et ça montre mon âge, mais oui, ça y fait penser et rouvre quelques vieilles cicatrices.) Et on peut oublier les commandes psql habituelles, puisque l’on est pas sous psql :

backend> \d+
... ERROR:  syntax error at or near "" at character 1
... STATEMENT:  \d+

En mode single donc, lançons le nettoyage :

backend> VACUUM (FREEZE,VERBOSE) videmoi;

... WARNING:  database "postgres" must be vacuumed within 999995 transactions
... HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
        You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
... CONTEXT:  while truncating relation "public.videmoi" to 0 blocks
... WARNING:  database "pgbench" must be vacuumed within 999994 transactions
... HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
        You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

Noter qu’il n’y a plus aucune erreur. Regardons notre table de plus près : son relfrozenxid correspond à présent à la transaction en cours, son âge est de 1 transaction :

backend> SELECT relname, relfrozenxid, age(relfrozenxid) FROM pg_class WHERE relname = 'videmoi';
        ...
        ----
         1: relname = "videmoi" (typeid = 19, len = 64, typmod = -1, byval = f)
         2: relfrozenxid = "2146555541" (typeid = 28, len = 4, typmod = -1, byval = t)
         3: age = "1"   (typeid = 23, len = 4, typmod = -1, byval = t)
        ----

La situation a donc bien été débloquée pour la table.

Il reste à nettoyer les bases : leur age(datfrozenxid) vaut toujours 2³¹. On quitte le mode single (avec Ctrl-D, car exit ou \q seraient trop simples), on redémarre de manière classique, et après un vacuumdb -a --freeze  :

#  SELECT datname, datfrozenxid, round(age(datfrozenxid) / 1e6,1) AS age_Mtrx  FROM pg_database  ORDER BY 3 DESC ;
  datname  | datfrozenxid | age_mtrx 
-----------+--------------+----------
 postgres  |   2146555542 |      0.0
 pgbench   |   2146555542 |      0.0
 template1 |   2146555542 |      0.0
 template0 |   2146555542 |      0.0

Tout fonctionne à nouveau normalement. Alleluia !

Pourquoi le VACUUM FREEZE est-il gêné par les lignes vides en fin de table ?

NB : Dans pg_locks, on ne trouvera rien qui concerne notre table. Personne n’a posé de verrou qui gêne le VACUUM.

Le lien entre l’opération (un VACUUM FREEZE) et le verrou est un peu étonnant. Celui-ci semble réclamer la création d’une transaction, ce qui est effectivement incompatible avec notre mode restreint, mais n’est (intuitivement) pourtant pas nécessaire.

Le détail est dans le code source PostgreSQL (on y revient toujours). Résumé du jeu de piste auquel je me suis livré : la fonction qui nettoie la table ne pose qu’un verrou ShareUpdateExclusiveLock. Très indirectement , elle finit par appeler lazy_truncate_heap qui coupe les blocs vides des tables, et pour cela demande à augmenter le niveau de verrou à AccessExclusiveLock, ce qui indirectement arrive dans GetCurrentTransactionId, qui va, tenter de créer une transaction via AssignTransactionId, qui finit dans GetNewTransactionId, fonction qui vérifie qu’il n’y a pas de risque de wraparound… et lève l’erreur. Le commentaire dans cette dernière fonction est clair, et c’est d’ailleurs elle qui a pour mission de nous agonir d’injures si on s’approche trop du wraparound. La mention teste explicitement si le mode est single (en fait, hors de postmaster et de ses enfants) :

      * Check to see if it's safe to assign another XID.  This protects against
      * catastrophic data loss due to XID wraparound.  The basic rules are:
      *
      * If we're past xidVacLimit, start trying to force autovacuum cycles.
      * If we're past xidWarnLimit, start issuing warnings.
      * If we're past xidStopLimit, refuse to execute transactions, unless
      * we are running in single-user mode (which gives an escape hatch
      * to the DBA who somehow got past the earlier defenses).

Résumé : le VACUUM (dans sa partie qui n’a rien à voir avec le FREEZE) peut décider de supprimer la fin de la table, ce qui nécessite un verrou exclusif, qui nécessite la création d’un nouveau numéro de transaction pour d’obscures raisons internes, ce qui n’est pas possible dans la limite du dernier million, sauf en mode single.

On peut reproduire cela manuellement en mode normal, quand la base est bloquée dans le dernier million :

# BEGIN ;
BEGIN

#* LOCK TABLE videmoi IN ACCESS SHARE MODE ;
LOCK TABLE

#* LOCK TABLE videmoi IN ACCESS EXCLUSIVE MODE ;
ERROR:  database is not accepting commands to avoid wraparound data loss in database "postgres"
ASTUCE : Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

Cependant, je ne vois même pas comment on pourrait arriver à un tel problème en production de nos jours. Mais après tout il peut y avoir des bugs : les commentaires évoquent pg_upgrade (voir le code).

Un ordre FREEZE uniquement avait été proposé par les Russes de Postgres Pro, mais cela a dégénéré en options de VACUUM.

Pousser le bouchon encore un peu plus loin grâce au mode single

En mode single, on peut aussi continuer à faire tourner le compteur des transactions, alors que la transaction préparée bloque toujours le gel. Que se passe-t-il alors ? C’est comme utiliser des SUVs en plein réchauffement climatique, donc pourquoi se priver ?

En repartant de la sauvegarde physique faite plus haut, en « oubliant » de committer notre transaction préparée et en repassant en mode single :

backend> SELECT txid_current () ;
         1: txid_current        (typeid = 20, len = 8, typmod = -1, byval = t)
        ----
... WARNING:  database "postgres" must be vacuumed within 999994 transactions
... HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
        You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
         1: txid_current = "2146555542" (typeid = 20, len = 8, typmod = -1, byval = t)
        ----

Notre procédure de cramage de transactions fonctionne toujours :

backend>  CALL burn_transactions (999990);
...
...WARNING:  database "postgres" must be vacuumed within 898980 transactions
...
...WARNING:  database "postgres" must be vacuumed within 98979 transactions
...
...WARNING:  database "postgres" must be vacuumed within 3 transactions

À 3 transactions près, on est bien à présent à la transaction la plus âgée + 2³¹ :

backend> SELECT transaction, age (transaction), mod (age (transaction), (2^32)::bigint)-2^31, gid FROM pg_prepared_xacts ;
        ...
        ----
         1: transaction = "71889"       (typeid = 28, len = 4, typmod = -1, byval = t)
         2: age = "2147483645"  (typeid = 23, len = 4, typmod = -1, byval = t)
         3: ?column? = "-3"     (typeid = 701, len = 8, typmod = -1, byval = t)
         4: gid = "trx99"       (typeid = 25, len = -1, typmod = -1, byval = f)
        ----

Ce qui correspond logiquement à l’âge de la table, et donc de la base, et même de toutes les bases de l’instance :

SELECT age (relfrozenxid), mod (age (relfrozenxid), (2^32)::bigint)-2^31 FROM pg_class WHERE relname = 'videmoi' ;
        ...
        ----
         1: age = "2147483645"  (typeid = 23, len = 4, typmod = -1, byval = t)
         2: ?column? = "-3"     (typeid = 701, len = 8, typmod = -1, byval = t)

backend> SELECT datname, datfrozenxid, age(datfrozenxid) AS age, mod (age (datfrozenxid), (2^32)::bigint) - 2^31  FROM pg_database ORDER BY 3 DESC ;
        ...
        ----
         1: datname = "postgres"        (typeid = 19, len = 64, typmod = -1, byval = f)
         2: datfrozenxid = "71889"      (typeid = 28, len = 4, typmod = -1, byval = t)
         3: age = "2147483645"  (typeid = 23, len = 4, typmod = -1, byval = t)
         4: ?column? = "-3"     (typeid = 701, len = 8, typmod = -1, byval = t)
        ... 
         1: datname = "template0"       (typeid = 19, len = 64, typmod = -1, byval = f)
         2: datfrozenxid = "71889"      (typeid = 28, len = 4, typmod = -1, byval = t)
         3: age = "2147483645"  (typeid = 23, len = 4, typmod = -1, byval = t)
         4: ?column? = "-3"     (typeid = 701, len = 8, typmod = -1, byval = t)
        ----

Dans la table videmoi existent des colonnes système xmin et xmax : xmax (71889) correspond là aussi à la transaction qui a effacé la ligne, mais qui n’a toujours pas été committée (donc la ligne reste visible). Mais xmin a un âge négatif, car 30957 est à présent considéré dans le futur, puisque ce numéro a déjà été frappé par le wraparound !

backend> SELECT xmin, age(xmin) AS xmin_age, xmax, age(xmax) AS xmax_age, * FROM videmoi ;
        ...
        ----
         1: xmin = "30957"      (typeid = 28, len = 4, typmod = -1, byval = t)
         2: xmin_age = "-2147442719"    (typeid = 23, len = 4, typmod = -1, byval = t)
         3: xmax = "71889"      (typeid = 28, len = 4, typmod = -1, byval = t)
         4: xmax_age = "2147483645"     (typeid = 23, len = 4, typmod = -1, byval = t)
         5: ?column? = "1"      (typeid = 23, len = 4, typmod = -1, byval = t)
        ----

backend> SELECT txid_status(71889);
         1: txid_status (typeid = 25, len = -1, typmod = -1, byval = f)
        ----
         1: txid_status = "in progress" (typeid = 25, len = -1, typmod = -1, byval = f)
        ----

Dans une interprétation naïve du MVCC de PostgreSQL, la ligne ne devrait plus apparaître ! La requête devrait voir un xmin dans le futur et en déduire que la ligne a été créée par une autre transaction qui a démarré après son début, et qu’il ne faut pas en tenir compte. C’est donc plus compliqué. Ne serait-ce que parce que PostgreSQL a oublié le statut de la transaction de création de la ligne :

 
SELECT txid_status(30957);
         1: txid_status (typeid = 25, len = -1, typmod = -1, byval = f)
        ----
        ----

Le statut committé est sans doute stocké dans les hint bits présents sur chaque ligne (Soyons honnête : ce billet est trop long, et je n’ai pas cherché à vérifier ; je me serais laissé tenter si je connaissais un moyen simple de réinitialiser les hint bits ). Edit : en fait, ioguix m’apprend que la fonction pg_xact_status renvoie NULL si la transaction est plus vieille que 2³². Et sans considérations pour les clog qui stocke les statuts.

Continuons :

backend> SELECT txid_current () ;
...WARNING:  database "postgres" must be vacuumed within 0 transactions
backend> SELECT txid_current () ;
...WARNING:  database "postgres" must be vacuumed within 4294967295 transactions
...
backend> SELECT txid_current () ;
...WARNING:  database "postgres" must be vacuumed within 4294967294 transactions
...
)

Au-delà de la frontière du gel

Il est donc possible de forcer le wraparound manuellement, et les âges deviennent tous allègrement négatifs :

backend> SELECT xmin, age(xmin) AS xmin_age, xmax, age(xmax) AS xmax_age, * FROM videmoi ;
        ...
        ----
         1: xmin = "30957"      (typeid = 28, len = 4, typmod = -1, byval = t)
         2: xmin_age = "-2147442714"    (typeid = 23, len = 4, typmod = -1, byval = t)
         3: xmax = "71889"      (typeid = 28, len = 4, typmod = -1, byval = t)
         4: xmax_age = "-2147483646"    (typeid = 23, len = 4, typmod = -1, byval = t)
         5: ?column? = "1"      (typeid = 23, len = 4, typmod = -1, byval = t)
        ----

backend> SELECT datname, datfrozenxid, age(datfrozenxid) AS age, mod (age (datfrozenxid), (2^32)::bigint) FROM pg_database ;
        ...
        ----
         1: datname = "postgres"        (typeid = 19, len = 64, typmod = -1, byval = f)
         2: datfrozenxid = "71889"      (typeid = 28, len = 4, typmod = -1, byval = t)
         3: age = "-2147483646" (typeid = 23, len = 4, typmod = -1, byval = t)
         4: mod = "-2147483646" (typeid = 20, len = 8, typmod = -1, byval = t)

backend> SELECT transaction, age (transaction), gid FROM pg_prepared_xacts ;
        ...
        ----
         1: transaction = "71889"       (typeid = 28, len = 4, typmod = -1, byval = t)
         2: age = "-2147483646" (typeid = 23, len = 4, typmod = -1, byval = t)
         3: gid = "trx99"       (typeid = 25, len = -1, typmod = -1, byval = f)
        ----

Retenez bien ce dernier résultat : notre transaction préparée est toujours là, et dans le futur.

Cependant, pour le moment, rien ne change : si on redémarre la base en mode normal, les transactions restent bloquées, et videmoi ne peut être gelée.

Comment se comporte un PostgreSQL alors qu’il y a eu un wraparound ? Si les lignes contenant les plus anciennes transactions se retrouvent dans le futur des nouvelles, il va y avoir de la perte !

Fin du mode single et retour au mode normal. Dans postgresql.log, on lit :

... WARNING:  database with OID 13431 must be vacuumed within 4294967293 transactions
... HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
        You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

On a pris l’habitude. La suite fait plus peur :

... WARNING:  removing future two-phase state file for transaction 71889
... LOG:  database system is ready to accept connections

Effectivement, on a bien :

# SELECT transaction, age (transaction), mod (age (transaction), (2^32)::bigint)-2^31, gid FROM pg_prepared_xacts ;
 transaction | age | ?column? | gid 
-------------+-----+----------+-----
(0 ligne)

Dans le code, on trouve la raison dans twophase.c : la transaction est identifiée dans le futur, PostgreSQL ne cherche pas plus loin et vire donc carrément le fichier de pg_xact !

Plus bas dans les traces, on voit qu’un autovacuum s’est déclenché et a des problèmes :

... ERROR:  cannot freeze committed xmax 71890
... CONTEXT:  while scanning block 416 of relation "public.pgbench_accounts"
        automatic vacuum of table "postgres.public.pgbench_accounts"
.. .ERROR:  cannot freeze committed xmax 71890
... CONTEXT:  while scanning block 416 of relation "public.pgbench_accounts"
        automatic vacuum of table "postgres.public.pgbench_accounts"

Le message vient de la fonction heap_prepare_freeze_tuple, qui croit que notre transaction préparée est bien committée, ce qui n’est pourtant pas le cas.

Notre table a changé aussi de statut : le xmax a purement et simplement été nettoyé.

# SELECT xmin, age(xmin) AS xmin_age, xmax, age(xmax) AS xmax_age, * FROM videmoi ;
 xmin  |  xmin_age   | xmax |  xmax_age  | ?column? 
-------+-------------+------+------------+----------
 30957 | -2147442714 |    0 | 2147483647 |        1

Je n’ai pas pris le temps de regarder quelle partie du code a pris cette initiative. Mais pg_stat_user_tables indique que l’autovacuum a traité la table peu après le redémarrage, sans émettre de trace.

Bref : notre transaction préparée finalement jamais committée a été dégagée, mais est considérée comme committée, ce qui pourrait être une catastrophe. Cependant, grâce au VACUUM, on voit toujours la ligne.

Même autovacuum = off ne peut éviter cela : le VACUUM FREEZE se déclenche même dans ce cas, vu le retard énorme dans le gel des tables.

Une fois « débloqué », on peut lancer un vacuumdb -e -a --freeze :

...WARNING:  some databases have not been vacuumed in over 2 billion transactions
...DETAIL:  You might have already suffered transaction-wraparound data loss.

Oui, c’est un peu effrayant.

Effrayant aussi le nombre de fois lors de mes essais où le FREEZE a purement et simplement échoué, le processus semblant complètement figé et inactif (y compris en mode single). Je n’ai pas creusé : il est normal que rien ne soit testé et que plus rien ne soit garanti une fois passé du côté obscur.

Bref, personnellement, j’aurais préféré que PostgreSQL refuse tout net de franchir le wraparound et se bloque définitivement, avec un message « faites un pg_dump ou appelez Dalibo ». Ou exige une option bien planquée (genre SET donald_trump TO on).

Morale

De la même manière qu’il ne faut pas jouer avec le climat de sa planète, il est très imprudent de repousser le FREEZE indéfiniment. Au bout d’un moment, on a de sérieux problèmes, puis plus rien ne va.

Avant d’augmenter la valeur de autovacuum_freeze_max_age au-delà des 200 millions, il faut se demander si cela va vraiment éviter des réécritures de blocs. Ce peut être le cas si tous les blocs vont être modifiés pendant le nouveau délai. Pour les données plus statiques, il faut savoir qu’un bloc gelé non modifié ne sera même plus relu par un FREEZE par la suite (PostgreSQL >= 9.6), donc ne posera plus de problème sur ce point.

Le nettoyage des plus vieux identifiants de transaction permet aussi d’oublier le statut de ces transactions : cela peut représenter quelques centaines de Mo dans pg_xactet éventuellement pg_commit_ts.

Bref :

  • après un gros import, penser à un VACUUM FREEZE manuel à l’occasion, dans un moment calme (sinon soyez sûr qu’il tabassera vos disques au pire moment) ;
  • surveiller l’aĝe des plus vieilles tables (champ pgclass.relfrozenxid) ;
  • surveiller le retard des slots de réplication, et surtout que l’on n’en pas oublié qui traîne (vue pg_replication_slots) ;
  • éviter des idle in transaction trop longs (plus pour la fragmentation que pour ce problème de FREEZE, mais ça ne va pas aider) ;
  • surveiller les transactions préparées anciennes comme le lait sur le feu (pg_prepared_xacts) ;
  • quand PostgreSQL fait son FREEZE, le laisser finir ;
  • ou plutôt, s’il y a urgence, procéder à l’opération soi-même, car le paramétrage de l’autovacuum est moins agressif qu’une commande VACUUM ou VACUUM FREEZE manuelle, surtout avant la v12 ;
  • le mode single est une astuce qu’il faut savoir dégainer, si VACUUM (FREEZE,TRUNCATE off) n’est pas disponible ;
  • forcer le mur du wraparound relève du sabotage.

Pour la supervision des 4 points ci-dessus, la sonde Nagios check_pgactivity propose des sondes : max_freeze_age, replication_slots, oldest_idlexact, oldest_2pc.

PS : Merci à mes collègues relecteurs !

Note

[1] Au passage, le mélange d’anglais et de français provient d’une traduction encore incomplète de PostgreSQL 13.0.

Administration PostgreSQL

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