Permettez-moi de vous rappeler que nous avons examiné les problèmes liés à l'
isolement , fait une digression sur l'
organisation des données à un bas niveau , puis parlé en détail des
versions de ligne et de la façon dont les
instantanés sont obtenus à partir des versions.
Aujourd'hui, nous traiterons de deux questions assez étroitement liées: le
nettoyage intra-page et les
mises à jour HOT . Les deux mécanismes peuvent être classés comme optimisations; ils sont importants, mais ne sont presque pas traités dans la documentation utilisateur.
Nettoyage sur la page avec mises à jour régulières
Lors de l'accès à une page - à la fois pendant la mise à jour et pendant la lecture - un nettoyage intra-page rapide peut se produire si PostgreSQL comprend que la page manque d'espace. Cela se produit dans deux cas.
- Une mise à jour précédemment effectuée sur cette page (UPDATE) n'a pas trouvé suffisamment d'espace pour placer une nouvelle version de la ligne sur la même page. Cette situation est mémorisée dans le titre de la page et la prochaine fois que la page sera effacée.
- La page est remplie plus que sur fillfactor. Dans ce cas, le nettoyage s'effectue immédiatement, sans retarder la prochaine fois.
Fillfactor est un paramètre de stockage qui peut être défini pour la table (et pour l'index). PostgreSQL insère une nouvelle ligne (INSERT) sur la page uniquement si cette page est inférieure au pourcentage de remplissage ou pleine. L'espace restant est réservé aux nouvelles versions de chaînes résultant de mises à jour (UPDATE). La valeur par défaut des tables est 100, c'est-à-dire que l'espace n'est pas réservé (et la valeur des index est 90).
Le nettoyage intra-page supprime les versions de lignes qui ne sont visibles dans aucune image (situées au-delà de "l'horizon des événements" de la base de données, nous en avons parlé la
dernière fois ), mais cela fonctionne strictement dans la même page tabulaire. Les pointeurs vers des versions de chaînes nettoyées ne sont pas libérés, car ils peuvent être référencés à partir d'index, et l'index est une autre page. Le nettoyage sur la page ne dépasse jamais une page tabulaire, mais il est très rapide.
Pour les mêmes raisons, la carte d'espace libre n'est pas mise à jour; il économise également de l'espace pour les mises à jour, pas pour les insertions. La carte de visibilité n'est pas non plus mise à jour.
Le fait qu'une page puisse être effacée lors de la lecture signifie qu'une demande de lecture (SELECT) peut entraîner la modification des pages. C'est un autre cas de ce genre, en plus du changement de bits d'indication précédemment différé.
Voyons comment cela fonctionne, à l'aide d'un exemple. Créez une table et des index sur les deux colonnes.
=> CREATE TABLE hot(id integer, s char(2000)) WITH (fillfactor = 75); => CREATE INDEX hot_id ON hot(id); => CREATE INDEX hot_s ON hot(s);
Si seules les lettres latines sont stockées dans la colonne s, chaque version de la ligne occupera 2004 octets plus 24 octets de l'en-tête. Nous avons défini le paramètre de stockage fillfactor à 75% - il y aura suffisamment d'espace pour trois lignes.
Pour plus de commodité, nous recréons une fonction déjà familière, complétant la sortie avec deux champs:
=> CREATE FUNCTION heap_page(relname text, pageno integer) RETURNS TABLE(ctid tid, state text, xmin text, xmax text, hhu text, hot text, t_ctid tid) AS $$ SELECT (pageno,lp)::text::tid AS ctid, CASE lp_flags WHEN 0 THEN 'unused' WHEN 1 THEN 'normal' WHEN 2 THEN 'redirect to '||lp_off WHEN 3 THEN 'dead' END AS state, t_xmin || CASE WHEN (t_infomask & 256) > 0 THEN ' (c)' WHEN (t_infomask & 512) > 0 THEN ' (a)' ELSE '' END AS xmin, t_xmax || CASE WHEN (t_infomask & 1024) > 0 THEN ' (c)' WHEN (t_infomask & 2048) > 0 THEN ' (a)' ELSE '' END AS xmax, CASE WHEN (t_infomask2 & 16384) > 0 THEN 't' END AS hhu, CASE WHEN (t_infomask2 & 32768) > 0 THEN 't' END AS hot, t_ctid FROM heap_page_items(get_raw_page(relname,pageno)) ORDER BY lp; $$ LANGUAGE SQL;
Et créons une fonction pour regarder à l'intérieur de la page d'index:
=> CREATE FUNCTION index_page(relname text, pageno integer) RETURNS TABLE(itemoffset smallint, ctid tid) AS $$ SELECT itemoffset, ctid FROM bt_page_items(relname,pageno); $$ LANGUAGE SQL;
Nous vérifierons le fonctionnement du nettoyage intra-page. Pour ce faire, insérez une ligne et modifiez-la plusieurs fois:
=> INSERT INTO hot VALUES (1, 'A'); => UPDATE hot SET s = 'B'; => UPDATE hot SET s = 'C'; => UPDATE hot SET s = 'D';
Il existe quatre versions de la ligne dans la page:
=> SELECT * FROM heap_page('hot',0);
ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+----------+-----+-----+-------- (0,1) | normal | 3979 (c) | 3980 (c) | | | (0,2) (0,2) | normal | 3980 (c) | 3981 (c) | | | (0,3) (0,3) | normal | 3981 (c) | 3982 | | | (0,4) (0,4) | normal | 3982 | 0 (a) | | | (0,4) (4 rows)
Comme prévu, nous venons de dépasser le seuil fillfactor. Ceci est indiqué par la différence entre la taille de la page et les valeurs supérieures: elle dépasse le seuil de 75% de la taille de la page, qui est de 6144 octets.
=> SELECT lower, upper, pagesize FROM page_header(get_raw_page('hot',0));
lower | upper | pagesize -------+-------+---------- 40 | 64 | 8192 (1 row)
Ainsi, la prochaine fois que vous accéderez à la page, un nettoyage sur la page devrait se produire. Regardez ça.
=> UPDATE hot SET s = 'E'; => SELECT * FROM heap_page('hot',0);
ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+-------+-----+-----+-------- (0,1) | dead | | | | | (0,2) | dead | | | | | (0,3) | dead | | | | | (0,4) | normal | 3982 (c) | 3983 | | | (0,5) (0,5) | normal | 3983 | 0 (a) | | | (0,5) (5 rows)
Toutes les versions non pertinentes des lignes (0,1), (0,2) et (0,3) sont supprimées; après cela, une nouvelle version de la ligne (0.5) est ajoutée à l'espace libéré.
Les versions des lignes restantes après le nettoyage sont physiquement déplacées vers le côté des adresses de la page principale afin que tout l'espace libre soit représenté par un fragment continu. Les valeurs des pointeurs changent en conséquence. Grâce à cela, il n'y a aucun problème de fragmentation de l'espace libre dans la page.
Les pointeurs vers des versions supprimées de chaînes ne peuvent pas être libérés car ils sont référencés à partir d'une page d'index. Regardons la première page de l'index hot_s (car le zéro est occupé par des méta-informations):
=> SELECT * FROM index_page('hot_s',1);
itemoffset | ctid ------------+------- 1 | (0,1) 2 | (0,2) 3 | (0,3) 4 | (0,4) 5 | (0,5) (5 rows)
Nous verrons la même image dans un autre index:
=> SELECT * FROM index_page('hot_id',1);
itemoffset | ctid ------------+------- 1 | (0,5) 2 | (0,4) 3 | (0,3) 4 | (0,2) 5 | (0,1) (5 rows)
Vous pouvez remarquer que les pointeurs vers les lignes du tableau vont ici «en arrière», mais cela n'a pas d'importance, car dans toutes les versions des lignes, la même valeur est id = 1. Mais dans l'index précédent, les pointeurs sont classés par valeurs s, et cela sensiblement.
Avec l'accès à l'index, PostgreSQL peut obtenir (0,1), (0,2) ou (0,3) comme identifiant de version de ligne. Ensuite, il essaiera d'obtenir la ligne correspondante de la page du tableau, mais grâce au statut mort du pointeur, il constatera qu'une telle version n'existe plus et l'ignorera. (En fait, la première fois qu'il détecte un manque de version d'une ligne de table, PostgreSQL changera également le statut du pointeur dans la page d'index afin qu'il n'accède plus à la page de table.)
Il est important que le nettoyage intra-page ne fonctionne que dans une seule page tabulaire et n'efface pas les pages d'index.
Mises à jour CHAUDES
Pourquoi est-il mauvais de conserver des liens vers toutes les versions d'une chaîne dans l'index?
Tout d'abord, à chaque changement de ligne, vous devez mettre à jour tous les index créés pour la table: depuis qu'une nouvelle version est apparue, vous devez avoir des liens vers celle-ci. Et vous devez le faire dans tous les cas, même si les champs qui ne sont pas inclus dans l'index changent. De toute évidence, ce n'est pas très efficace.
Deuxièmement, les index accumulent des liens vers des versions historiques de la chaîne, qui doivent ensuite être effacées avec les versions elles-mêmes (nous verrons cela un peu plus tard).
De plus, il existe une caractéristique de l'implémentation de l'arbre B dans PostgreSQL. S'il n'y a pas assez d'espace sur la page d'index pour insérer une nouvelle ligne, la page est divisée en deux et toutes les données sont redistribuées entre elles. C'est ce qu'on appelle une page partagée. Cependant, lors de la suppression de lignes, deux pages d'index ne «collent plus» en une seule. Pour cette raison, la taille de l'index peut ne pas diminuer même si une partie substantielle des données est supprimée.
Naturellement, plus il y a d'index créés sur la table, plus vous rencontrez de difficultés.
Cependant, si la valeur d'une colonne qui n'appartient à aucun index change, il est inutile de créer un enregistrement supplémentaire dans l'arborescence B contenant la même valeur de clé. C'est ainsi que fonctionne l'optimisation, appelée la mise à jour HOT - la mise à jour de tuple uniquement en tas.
Avec cette mise à jour, il n'y a qu'une seule entrée dans la page d'index qui fait référence à la toute première version de la ligne de la page de table. Et déjà à l'intérieur de cette page tabulaire une chaîne de versions est organisée:
- les chaînes modifiées et incluses dans la chaîne sont marquées avec le bit Heap Hot Updated;
- les lignes qui ne sont pas référencées à partir de l'index sont marquées avec le bit Heap Only Tuple (c'est-à-dire «uniquement la version tabulaire de la ligne»);
- la liaison régulière des versions de chaîne via le champ ctid est prise en charge.
Si, lors de l'analyse d'un index, PostgreSQL pénètre dans une page de table et découvre une version marquée comme Heap Hot Updated, il comprend qu'il n'a pas besoin de s'arrêter et va plus loin dans toute la chaîne de mise à jour. Bien entendu, pour toutes les versions de chaînes obtenues de cette manière, la visibilité est vérifiée avant leur restitution au client.
Pour consulter le fonctionnement d'une mise à jour HOT, supprimez un index et effacez la table.
=> DROP INDEX hot_s; => TRUNCATE TABLE hot;
Répétez l'insertion et mettez à jour la ligne.
=> INSERT INTO hot VALUES (1, 'A'); => UPDATE hot SET s = 'B';
Voici ce que nous voyons dans la page du tableau:
=> SELECT * FROM heap_page('hot',0);
ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+-------+-----+-----+-------- (0,1) | normal | 3986 (c) | 3987 | t | | (0,2) (0,2) | normal | 3987 | 0 (a) | | t | (0,2) (2 rows)
Dans la page, il y a une chaîne de changements:
- l'indicateur Heap Hot Updated indique que vous devez suivre la chaîne ctid,
- l'indicateur Heap Only Tuple indique qu'il n'existe aucun lien d'index vers cette version de la ligne.
Avec d'autres modifications, la chaîne se développera (dans la page):
=> UPDATE hot SET s = 'C'; => UPDATE hot SET s = 'D'; => SELECT * FROM heap_page('hot',0);
ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+----------+-----+-----+-------- (0,1) | normal | 3986 (c) | 3987 (c) | t | | (0,2) (0,2) | normal | 3987 (c) | 3988 (c) | t | t | (0,3) (0,3) | normal | 3988 (c) | 3989 | t | t | (0,4) (0,4) | normal | 3989 | 0 (a) | | t | (0,4) (4 rows)
De plus, dans l'indice, il n'y a qu'une seule référence à la «tête» de la chaîne:
=> SELECT * FROM index_page('hot_id',1);
itemoffset | ctid ------------+------- 1 | (0,1) (1 row)
Nous soulignons que les mises à jour HOT fonctionnent si les champs mis à jour ne sont inclus dans aucun index. Sinon, dans certains index, il y aurait un lien directement vers la nouvelle version de la chaîne, ce qui contredit l'idée de cette optimisation.
L'optimisation ne fonctionne que dans les limites d'une page; par conséquent, un contournement supplémentaire de la chaîne ne nécessite pas l'accès à d'autres pages et n'altère pas les performances.
Nettoyage sur la page avec des mises à jour CHAUDES
Un cas spécial mais important de nettoyage intra-page est le nettoyage lors des mises à jour CHAUDES.
Comme la dernière fois, nous avons déjà dépassé le seuil fillfactor, donc la prochaine mise à jour devrait conduire à un nettoyage sur la page. Mais cette fois dans la page est une chaîne de mises à jour. La "tête" de cette chaîne HOT doit toujours rester à sa place, puisque l'index s'y réfère, et les autres pointeurs peuvent être libérés: on sait qu'ils ne sont pas référencés de l'extérieur.
Afin de ne pas toucher la "tête", un double adressage est utilisé: le pointeur auquel se réfère l'index - dans ce cas (0,1) - reçoit le statut de "redirection", redirigeant vers la version souhaitée de la chaîne.
=> UPDATE hot SET s = 'E'; => SELECT * FROM heap_page('hot',0);
ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+-------+-----+-----+-------- (0,1) | redirect to 4 | | | | | (0,2) | normal | 3990 | 0 (a) | | t | (0,2) (0,3) | unused | | | | | (0,4) | normal | 3989 (c) | 3990 | t | t | (0,2) (4 rows)
Veuillez noter que:
- les versions (0,1), (0,2) et (0,3) ont été effacées,
- Le pointeur de tête (0,1) est resté, mais a reçu le statut de redirection,
- la nouvelle version de la ligne est écrite en place (0.2), car cette version était garantie de ne pas avoir de liens à partir des index et le pointeur était libéré (inutilisé).
Effectuez la mise à jour plusieurs fois:
=> UPDATE hot SET s = 'F'; => UPDATE hot SET s = 'G'; => SELECT * FROM heap_page('hot',0);
ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+----------+-----+-----+-------- (0,1) | redirect to 4 | | | | | (0,2) | normal | 3990 (c) | 3991 (c) | t | t | (0,3) (0,3) | normal | 3991 (c) | 3992 | t | t | (0,5) (0,4) | normal | 3989 (c) | 3990 (c) | t | t | (0,2) (0,5) | normal | 3992 | 0 (a) | | t | (0,5) (5 rows)
La mise à jour suivante provoque à nouveau le nettoyage intra-page:
=> UPDATE hot SET s = 'H'; => SELECT * FROM heap_page('hot',0);
ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+-------+-----+-----+-------- (0,1) | redirect to 5 | | | | | (0,2) | normal | 3993 | 0 (a) | | t | (0,2) (0,3) | unused | | | | | (0,4) | unused | | | | | (0,5) | normal | 3992 (c) | 3993 | t | t | (0,2) (5 rows)
Encore une fois, certaines versions sont effacées et le pointeur sur la "tête" est en conséquence déplacé.
Conclusion: avec des mises à jour fréquentes des colonnes en dehors des index, il peut être judicieux de réduire le paramètre fillfactor pour réserver un espace sur la page pour les mises à jour. Bien sûr, nous devons tenir compte du fait que plus le facteur de remplissage est bas, plus l'espace non alloué reste sur la page et, en conséquence, la taille physique de la table augmente.
Rupture de chaîne CHAUDE
S'il n'y a pas assez d'espace libre sur la page pour publier une nouvelle version d'une ligne, la chaîne se cassera. La version de la ligne publiée sur une autre page devra faire un lien distinct de l'index.
Pour obtenir cette situation, nous démarrons une transaction parallèle et y créons un instantané de données.
| => BEGIN ISOLATION LEVEL REPEATABLE READ; | => SELECT count(*) FROM hot;
| count | ------- | 1 | (1 row)
Un instantané n'effacera pas la version des lignes de la page. Maintenant, nous effectuons la mise à jour dans la première session:
=> UPDATE hot SET s = 'I'; => UPDATE hot SET s = 'J'; => UPDATE hot SET s = 'K'; => SELECT * FROM heap_page('hot',0);
ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+----------+-----+-----+-------- (0,1) | redirect to 2 | | | | | (0,2) | normal | 3993 (c) | 3994 (c) | t | t | (0,3) (0,3) | normal | 3994 (c) | 3995 (c) | t | t | (0,4) (0,4) | normal | 3995 (c) | 3996 | t | t | (0,5) (0,5) | normal | 3996 | 0 (a) | | t | (0,5) (5 rows)
La prochaine fois que la page sera actualisée, il n'y aura pas assez d'espace sur la page, mais le nettoyage sur la page ne pourra rien libérer:
=> UPDATE hot SET s = 'L';
| => COMMIT;
=> SELECT * FROM heap_page('hot',0);
ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+----------+-----+-----+-------- (0,1) | redirect to 2 | | | | | (0,2) | normal | 3993 (c) | 3994 (c) | t | t | (0,3) (0,3) | normal | 3994 (c) | 3995 (c) | t | t | (0,4) (0,4) | normal | 3995 (c) | 3996 (c) | t | t | (0,5) (0,5) | normal | 3996 (c) | 3997 | | t | (1,1) (5 rows)
Dans la version (0.5), nous voyons un lien vers (1.1) menant à la page 1.
=> SELECT * FROM heap_page('hot',1);
ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+------+-------+-----+-----+-------- (1,1) | normal | 3997 | 0 (a) | | | (1,1) (1 row)
Il y a maintenant deux lignes dans l'index, chacune pointant vers le début de sa chaîne HOT:
=> SELECT * FROM index_page('hot_id',1);
itemoffset | ctid ------------+------- 1 | (1,1) 2 | (0,1) (2 rows)
Malheureusement, les informations sur le nettoyage sur la page et les mises à jour HOT sont pratiquement absentes dans la documentation, et la vérité doit être recherchée dans le code source. Je recommande de commencer par README.HOT .
À suivre .