Quelques pensées

Dernier volet de la série, Oracle. Je ne fais pas tourner Oracle chez moi : ce billet s’appuie sur mon métier de DBA et sur la documentation d’Oracle, pas sur mes serveurs. Oracle est le plus ancien des trois moteurs, et le plus explicite sur sa structure : il distingue soigneusement la vue logique (tablespaces, segments, extents, blocs) des fichiers physiques, et il expose presque tout dans des vues du dictionnaire.

Structure logique et fichiers physiques

Une base Oracle se compose de plusieurs familles de fichiers :

• les datafiles contiennent les données. Un tablespace regroupe un ou plusieurs datafiles, et un datafile n’appartient qu’à un seul tablespace ;
• les control files sont la carte de la base : la liste des datafiles et des journaux, le SCN (le numéro de version global de la base) du dernier checkpoint, l’historique des sauvegardes RMAN. On les multiplexe sur plusieurs disques, car sans eux la base ne démarre pas ;
• les online redo logs forment un journal circulaire, organisé en groupes de fichiers identiques (membres). Quand un groupe est plein, Oracle passe au suivant (log switch). En mode ARCHIVELOG, chaque groupe plein est copié en archived log avant d’être réutilisé : c’est ce qui permet une restauration à un instant précis ;
• le tablespace d’undo garde les images d’avant des données modifiées ;
• les tempfiles servent aux tris et aux jointures qui ne tiennent pas en mémoire ; ils ne sont pas journalisés ni sauvegardés ;
• le spfile contient les paramètres de l’instance, et le fichier de mots de passe permet les connexions administrateur à distance.

SELECT tablespace_name, file_name, bytes/1024/1024 AS mb, autoextensible FROM dba_data_files;
SELECT group#, members, bytes/1024/1024 AS mb, status FROM v$log;
SELECT name FROM v$controlfile;

Côté logique, un segment correspond à un objet qui occupe de la place : une table, un index, une partition, un LOB. Un segment est composé d’extents, chacun formé de blocs contigus dans un seul datafile. Un segment peut donc s’étaler sur plusieurs fichiers, extent par extent. Enfin, le bloc est l’unité d’entrée-sortie, 8 Ko par défaut (DB_BLOCK_SIZE, fixé à la création de la base).

SELECT segment_name, segment_type, extents, blocks FROM dba_segments WHERE owner = 'SHOP';
SELECT extent_id, file_id, block_id, blocks FROM dba_extents
WHERE owner = 'SHOP' AND segment_name = 'ORDERS' ORDER BY extent_id;

Dans un datafile

Le début de chaque datafile contient son en-tête (identité du fichier, SCN du dernier checkpoint, état de sauvegarde), puis, dans un tablespace géré localement (le cas normal aujourd’hui), un bitmap qui indique quels extents sont occupés. Avant, cette information vivait dans des tables du dictionnaire, ce qui créait de la contention lors des allocations. Deux politiques d’allocation existent : UNIFORM (tous les extents ont la même taille) et AUTOALLOCATE, où Oracle choisit des extents de plus en plus grands à mesure que le segment grossit (64 Ko, puis 1 Mo, puis 8 Mo…).

Dans un segment en ASSM (Automatic Segment Space Management, le défaut), des blocs bitmap à trois niveaux décrivent le remplissage de chaque bloc de données, par tranches de 25 %. Une session qui insère cherche un bloc assez libre dans ces bitmaps, et des sessions différentes sont orientées vers des blocs différents, ce qui évite qu’elles se battent pour le même bloc. L’en-tête de segment garde la liste des extents et la High Water Mark (HWM), la limite des blocs déjà formatés. Un parcours complet de la table lit tous les blocs jusqu’à la HWM, même vides : après un gros DELETE, un ALTER TABLE orders SHRINK SPACE (ou MOVE) fait redescendre la HWM.

Anatomie d’un bloc

• L’en-tête cache (20 octets) identifie le bloc : type, adresse (le RDBA, numéro de fichier et de bloc), SCN de la dernière modification, somme de contrôle. Avec DB_BLOCK_CHECKSUM=TYPICAL, la somme est vérifiée à chaque lecture depuis le disque ;
• l’ITL (Interested Transaction List) est propre à Oracle : une entrée de 24 octets par transaction qui modifie le bloc, avec son identifiant (XID), l’adresse de son undo et son état. INITRANS fixe le nombre d’entrées réservées à la création du bloc. Si toutes sont prises et que le bloc n’a plus de place pour en ajouter, une nouvelle transaction attend : c’est l’attente enq: TX - allocate ITL entry, qu’on corrige en augmentant INITRANS sur les tables très concurrentes ;
• le répertoire des lignes donne le décalage de chaque ligne dans le bloc, deux octets par ligne. Un ROWID désigne une entrée de ce répertoire, ce qui permet, comme chez PostgreSQL, de déplacer une ligne dans le bloc sans changer son adresse ;
• PCTFREE (10 % par défaut) réserve de l’espace libre pour les UPDATE qui allongent les lignes. Sans cette réserve, une ligne qui grandit ne tient plus dans son bloc et doit migrer : elle part dans un autre bloc et laisse à sa place un pointeur. Chaque lecture par ROWID coûte alors deux blocs. Une ligne trop grande pour n’importe quel bloc est chaînée sur plusieurs blocs. La statistique table fetch continued row mesure les deux ;
• la queue (4 octets) répète une partie du SCN, du type et de la séquence de l’en-tête : un bloc dont la tête et la queue ne correspondent pas a été écrit à moitié (fractured block). Oracle n’a pas de doublewrite : ce cas est détecté à la lecture, et le bloc se répare depuis une sauvegarde RMAN et les journaux (RECOVER ... BLOCK). Pendant une sauvegarde à chaud manuelle (BEGIN BACKUP), Oracle écrit l’image complète des blocs dans le redo à leur première modification, comme le full_page_writes de PostgreSQL.

Une ligne et son ROWID

Une ligne commence par trois octets : des indicateurs, l’octet de verrou et le nombre de colonnes. Viennent ensuite, pour chaque colonne, sa longueur et sa valeur. Les NULL au milieu de la ligne coûtent un octet (0xFF), et les NULL en fin de ligne ne sont pas stockés du tout. Placer les colonnes souvent nulles en dernier économise donc de la place.

L’octet de verrou est le détail le plus élégant du format : quand une transaction modifie une ligne, elle y inscrit le numéro de son entrée dans l’ITL du bloc. Le verrou de ligne est donc stocké dans le bloc lui-même, sans table de verrous en mémoire : verrouiller un million de lignes ne coûte pas plus de mémoire que d’en verrouiller une. Une autre transaction qui veut la même ligne lit l’ITL, trouve la transaction propriétaire et attend la fin de celle-ci.

Le ROWID est l’adresse physique d’une ligne : numéro d’objet, numéro de fichier relatif au tablespace, numéro de bloc, numéro de ligne dans le répertoire. C’est l’accès le plus rapide qui soit, et c’est ce que contiennent les index B-tree. Le paquet DBMS_ROWID le décode :

SELECT rowid,
       dbms_rowid.rowid_object(rowid)       AS obj,
       dbms_rowid.rowid_relative_fno(rowid) AS file#,
       dbms_rowid.rowid_block_number(rowid) AS block#,
       dbms_rowid.rowid_row_number(rowid)   AS row#
FROM shop.orders WHERE id = 7;

Pour voir le contenu brut d’un bloc, ALTER SYSTEM DUMP DATAFILE 4 BLOCK 151; écrit sa description complète (en-têtes, ITL, chaque ligne en hexadécimal) dans le fichier de trace de la session. À réserver à une base de test : c’est très instructif, mais ce n’est pas un outil de production.

Lecture cohérente : l’undo

Comme InnoDB, Oracle modifie les blocs sur place et range l’image d’avant dans l’undo. Quand une requête démarre, elle note le SCN courant. Si elle tombe sur un bloc modifié après ce SCN, elle en fabrique une copie en mémoire et lui applique à rebours les enregistrements d’undo désignés par l’ITL, jusqu’à obtenir le bloc tel qu’il était à son SCN de départ : c’est le consistent read. Les lecteurs ne bloquent jamais les écrivains, ni l’inverse.

Le revers : si l’undo nécessaire a déjà été écrasé par des transactions plus récentes, la requête échoue avec ORA-01555: snapshot too old. UNDO_RETENTION indique combien de temps Oracle doit essayer de garder l’undo, à condition que le tablespace d’undo soit assez grand. Autre phénomène, cousin des hint bits de PostgreSQL : le delayed block cleanout. Après un gros COMMIT, Oracle ne revisite pas tous les blocs modifiés, et c’est le premier SELECT suivant qui nettoie les entrées ITL… et génère du redo, à la surprise générale.

Les trois moteurs côte à côte

Aucun de ces choix n’est meilleur dans l’absolu. L’index cluster d’InnoDB rend les recherches par clé primaire imbattables mais pénalise les clés aléatoires. Le tas de PostgreSQL rend les mises à jour simples et les retours en arrière instantanés, au prix de VACUUM. L’undo séparé d’Oracle et d’InnoDB garde les tables compactes, mais impose de dimensionner l’undo. Savoir comment les données sont rangées sur le disque, c’est savoir lequel de ces compromis on paie, et à quel moment.

Ce billet fait partie d’une série de notes d’administration système (tag « carnet d’admin »).
Série « Dans les fichiers d’une base de données », partie 3/3. · ← Billet précédent · English version

Après InnoDB, PostgreSQL. Les deux moteurs résolvent les mêmes problèmes (stocker des lignes, les retrouver vite, survivre à un crash, laisser lire pendant qu’on écrit) mais ont fait des choix presque opposés. Les connaître explique le besoin de VACUUM, le gonflement des tables (bloat), ou pourquoi le premier SELECT après un gros chargement écrit sur le disque.

Le répertoire de données

Premier réflexe à perdre : chercher ses tables par leur nom. PostgreSQL désigne tout par des numéros. Chaque base a un OID, et c’est le nom de son répertoire sous base/. Chaque table, index ou séquence a un relfilenode, et c’est le nom de son fichier. Ce numéro change quand la table est réécrite (TRUNCATE, VACUUM FULL, CLUSTER, certains ALTER TABLE) : PostgreSQL crée un nouveau fichier et bascule, ce qui rend ces opérations sûres en cas de crash.

SELECT oid, datname FROM pg_database;
SELECT pg_relation_filepath('orders');        -- base/16384/24576
SELECT pg_size_pretty(pg_total_relation_size('orders'));   -- table + index + TOAST

• global/ contient les catalogues partagés par toutes les bases (rôles, liste des bases) et pg_control, le petit fichier qui dit où reprendre après un crash ;
• pg_wal/ est le journal d’écriture anticipée (WAL), en segments de 16 Mo : l’équivalent du redo d’InnoDB ;
• pg_xact/ garde l’état de chaque transaction sur deux bits : en cours, validée, annulée. C’est lui qui permet de savoir si une version de ligne est visible ;
• pg_tblspc/ contient des liens symboliques vers les tablespaces créés ailleurs. Chez PostgreSQL, un tablespace n’est qu’un répertoire sur un autre disque.

Une relation : des forks et des segments

Chaque table est en réalité un petit ensemble de fichiers. Le main fork contient les données, découpé en fichiers de 1 Go au plus (24576, 24576.1…). Cette limite date d’une époque où tous les systèmes de fichiers ne géraient pas les gros fichiers, et elle simplifie toujours la sauvegarde et la copie. La free space map indique approximativement la place libre de chaque page, pour qu’un INSERT trouve vite une page où écrire. La visibility map garde deux bits par page : « toutes les lignes sont visibles par tout le monde » et « toutes les lignes sont gelées ». Elle permet à VACUUM de sauter les pages déjà propres, et aux index-only scans de ne pas lire la table.

Anatomie d’une page

Les pages font 8 Ko (une constante de compilation, BLCKSZ). Leur structure est la même pour les tables et les index :

• l’en-tête de 24 octets contient le pd_lsn, la position dans le WAL de la dernière modification. PostgreSQL l’utilise pour une règle simple : une page ne peut pas être écrite sur le disque avant que le WAL qui la décrit le soit. Il contient aussi la somme de contrôle (activée par défaut à l’initialisation depuis PostgreSQL 18, à demander avec initdb -k avant), et deux décalages, pd_lower et pd_upper, qui bornent l’espace libre ;
• les line pointers forment un tableau en début de page. Chacun contient le décalage et la longueur d’un tuple. L’adresse d’une ligne, son ctid, vaut (numéro de page, numéro de pointeur), par exemple (0,2). Cette indirection permet de déplacer un tuple dans la page pour la défragmenter sans changer son adresse, donc sans toucher aux index ;
• les tuples sont écrits depuis la fin de la page, vers le haut. Quand pd_lower (fin des pointeurs) rejoint pd_upper (début des tuples), la page est pleine ;
• l’espace spécial en fin de page est vide pour une table. Les index B-tree y rangent les liens vers les pages voisines et le niveau dans l’arbre.

On peut tout regarder avec l’extension pageinspect, sur une base de test :

CREATE EXTENSION pageinspect;
SELECT lower, upper, special, pagesize FROM page_header(get_raw_page('orders', 0));
SELECT lp, lp_off, lp_len, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('orders', 0));

Un tuple

Chaque version de ligne commence par 23 octets d’en-tête, arrondis à 24 par l’alignement. Les champs essentiels sont t_xmin (la transaction qui a créé cette version), t_xmax (celle qui l’a supprimée ou remplacée) et t_ctid (l’adresse de la version suivante). Trois détails pratiques :

• l’alignement : chaque colonne est alignée selon son type (8 octets pour bigint et timestamp). Une colonne boolean suivie d’un bigint laisse 7 octets perdus. Ordonner les colonnes de la plus grande à la plus petite peut réduire une grosse table de 10 à 20 % ;
• les NULL ne prennent qu’un bit dans le bitmap, présent seulement si la ligne contient au moins un NULL ;
• TOAST : quand une ligne dépasse environ 2 Ko, PostgreSQL compresse d’abord les grandes valeurs (pglz, ou lz4 si on le demande), puis, si ça ne suffit pas, les déplace dans une table annexe, pg_toast.pg_toast_<oid>, découpées en morceaux. La ligne ne garde qu’un pointeur de 18 octets. C’est transparent, mais un SELECT * qui lit une colonne TOAST va chercher ces morceaux : ne sélectionnez que les colonnes utiles.

Un UPDATE ne modifie rien sur place

C’est la différence fondamentale avec InnoDB et Oracle. Chez PostgreSQL, un UPDATE est un DELETE suivi d’un INSERT : la transaction écrit une nouvelle version complète de la ligne, et inscrit son numéro dans le xmax de l’ancienne. Il n’y a pas de journal d’undo. Les anciennes versions sont l’undo, et elles vivent dans la table.

Pour décider si une version est visible, une transaction compare xmin et xmax à son instantané (snapshot) : la version est visible si sa création est validée et antérieure à l’instantané, et si sa suppression n’a pas eu lieu, a été annulée ou est postérieure. Consulter pg_xact à chaque fois coûterait cher. La première transaction qui lit une version note donc le résultat dans des hint bits de l’en-tête (« xmin validé », « xmax annulé »…). Cela explique un phénomène qui surprend souvent : le premier SELECT après un gros chargement écrit sur le disque, parce qu’il pose ces bits sur chaque page.

Ce que ça implique : VACUUM, HOT et le gel

• VACUUM retire les versions que plus aucune transaction ne peut voir, rend leur place réutilisable (la free space map est mise à jour) et met à jour la visibility map. Il ne rend pas l’espace au système de fichiers, sauf pour les pages vides en fin de fichier. Seul VACUUM FULL (ou l’extension pg_repack, sans verrou long) réécrit la table ;
• une transaction ouverte trop longtemps bloque le nettoyage de toutes les versions plus récentes qu’elle, dans toute la base. C’est la première chose à vérifier quand une table gonfle : SELECT pid, xact_start, state FROM pg_stat_activity ORDER BY xact_start; ;
• les mises à jour HOT (Heap-Only Tuple) : si aucune colonne indexée ne change et que la nouvelle version tient dans la même page, PostgreSQL ne touche pas aux index. L’index pointe toujours vers l’ancien pointeur, qui mène à la nouvelle version par la chaîne de ctid. Laisser de la place dans les pages augmente les chances de HOT : ALTER TABLE orders SET (fillfactor = 90); ;
• le gel (freezing) : les identifiants de transaction tiennent sur 32 bits et finissent par boucler, après environ deux milliards de transactions. Pour qu’une très vieille ligne ne paraisse pas soudain « dans le futur », VACUUM la marque comme gelée, visible par tous. Si autovacuum prend trop de retard, PostgreSQL finit par refuser les écritures pour se protéger : c’est pourquoi on ne désactive jamais autovacuum.

Les index et le WAL

Un index B-tree utilise les mêmes pages de 8 Ko. Ses entrées contiennent la clé et le ctid du tuple, c’est-à-dire une adresse physique, contrairement à InnoDB qui stocke la clé primaire. Une recherche par index est donc une seule descente plus une lecture de page. Mais l’index ne sait rien de la visibilité : il faut lire la table pour savoir si la version trouvée est visible, sauf si la visibility map dit que la page est entièrement visible. C’est ce qui rend les index-only scans efficaces sur une table bien nettoyée.

Enfin, la protection contre les pages déchirées : le problème est le même que chez InnoDB (8 Ko écrits par un disque qui garantit 4 Ko), la solution est différente. Avec full_page_writes = on, la première modification d’une page après chaque checkpoint écrit l’image complète de la page dans le WAL. Après un crash, PostgreSQL repart de cette image intacte. C’est l’équivalent du doublewrite buffer, et la raison pour laquelle le volume de WAL grimpe juste après chaque checkpoint.

Dernier billet de la série : Oracle, qui partage avec InnoDB l’undo séparé, mais pousse la logique plus loin, avec des verrous rangés dans les blocs eux-mêmes.

Ce billet fait partie d’une série de notes d’administration système (tag « carnet d’admin »).
Série « Dans les fichiers d’une base de données », partie 2/3. · ← Billet précédent · Billet suivant → · English version