Étiquette : carnet d’admin

Avec InnoDB comme avec les autres moteurs, une base de données relationnelle donne l’illusion de tables, de lignes et de colonnes. Sur le disque, il n’y a que des fichiers découpés en blocs de taille fixe. Comprendre comment ces blocs sont organisés explique beaucoup de comportements qui semblent mystérieux : pourquoi un fichier ne rétrécit pas après un DELETE, pourquoi une clé primaire en UUID ralentit les insertions, pourquoi un index secondaire coûte plus cher qu’on ne le pense, ou ce que protège vraiment le « doublewrite buffer ».

Cette série de trois billets ouvre le capot de trois moteurs : InnoDB (le moteur de stockage de MySQL et de MariaDB), PostgreSQL et Oracle. Le même fil conducteur les traverse : les fichiers, les pages, les lignes, et l’endroit où vivent les anciennes versions des données. Premier arrêt : InnoDB.

Le répertoire de données

Chaque fichier a un rôle précis :

• ibdata1, le tablespace système. Historiquement, il contenait tout : tables, index, undo, dictionnaire. Aujourd’hui, il ne garde plus que le change buffer (sur MySQL) et, sur MariaDB, le doublewrite et parfois l’undo. Il ne rétrécit jamais : un ibdata1 de 50 Go gonflé par une vieille transaction le restera, et la seule façon de le réduire est un export complet suivi d’une réimportation ;
• un fichier .ibd par table, grâce à innodb_file_per_table=ON (le défaut depuis MySQL 5.6). C’est ce qui permet de récupérer l’espace d’une table supprimée ou vidée (DROP, TRUNCATE), de déplacer une table seule, ou de restaurer un seul tablespace ;
• les tablespaces d’undo (undo_001…) : les images d’avant des lignes modifiées, pour les ROLLBACK et pour la lecture cohérente des autres transactions ;
• le journal redo : chaque modification de page y est décrite avant que la page elle-même soit écrite sur le disque. Après un crash, InnoDB rejoue ce journal ;
• le doublewrite buffer, expliqué plus bas ;
• les binlogs, qui n’appartiennent pas à InnoDB mais au serveur : ils décrivent les changements au niveau SQL, pour la réplication et la restauration à un instant donné.

Une base de données SQL (CREATE DATABASE shop) n’est rien d’autre qu’un répertoire. Les noms exacts des fichiers globaux varient : MySQL et MariaDB ont divergé sur le dictionnaire (MySQL 8 a remplacé les fichiers .frm par un dictionnaire transactionnel dans mysql.ibd, MariaDB a gardé les .frm), sur le redo et sur le doublewrite. Le format interne des pages, lui, est commun. Pour voir les tablespaces d’une instance :

-- MySQL 8
SELECT name, file_size, allocated_size FROM information_schema.INNODB_TABLESPACES;
-- MariaDB
SELECT name, file_size, allocated_size FROM information_schema.INNODB_SYS_TABLESPACES;

Pages, extents, segments

L’unité de base est la page, de 16 Ko par défaut. Sa taille (innodb_page_size) est fixée à la création de l’instance et ne peut plus changer ensuite. Tout ce qu’InnoDB lit ou écrit, il le lit ou l’écrit par pages entières : lire une ligne de 100 octets, c’est charger 16 Ko en mémoire, dans le buffer pool.

Les pages sont regroupées en extents de 64 pages, soit 1 Mo. Allouer par extents plutôt que page par page garde les pages d’un même index physiquement proches, ce qui rend les lectures séquentielles efficaces.

Chaque index possède deux segments : un pour ses pages feuilles, un pour ses pages intermédiaires. Les séparer garde les feuilles (là où sont les données) contiguës, pour les parcours par plage. Pour ne pas réserver 1 Mo à chaque petite table, un segment reçoit d’abord jusqu’à 32 pages isolées, et ne passe aux extents complets qu’ensuite. C’est pourquoi une table vide occupe une centaine de kilo-octets, et pas plusieurs mégaoctets.

Les premières pages d’un .ibd sont réservées à la gestion de l’espace : la page 0 (FSP_HDR) décrit l’état des extents, la page 1 le bitmap du change buffer, la page 2 les « inodes » des segments. Sur MySQL 8, la page 3 contient la description de la table en JSON (SDI) et la racine de l’index cluster est en page 4. Sur MariaDB, qui garde des .frm, la racine est en page 3.

Anatomie d’une page InnoDB

Chaque zone a une raison d’être :

• Le FIL header est commun à toutes les pages, quel que soit leur type. Il contient le numéro de page, le type de page et le LSN (Log Sequence Number) de la dernière modification. Dans l’ancienne disposition, il contient aussi la somme de contrôle, qui détecte une corruption à la lecture ; sur MariaDB 10.11, la disposition par défaut (full_crc32) laisse les octets 0 à 3 à zéro et place la somme de contrôle tout à la fin de la page (voir les tests en fin de billet). Après un crash, InnoDB compare ce LSN à celui de chaque entrée du redo : il ne rejoue que ce que la page n’a pas encore reçu. Les pointeurs vers les pages précédente et suivante relient toutes les pages d’un même niveau de l’arbre ;
• l’INDEX header donne le niveau de la page dans l’arbre (0 pour une feuille), le nombre de lignes et l’identifiant de l’index auquel elle appartient ;
• infimum et supremum sont deux fausses lignes, la plus petite et la plus grande possibles. Elles servent de début et de fin à la liste chaînée des lignes, ce qui simplifie le code d’insertion ;
• les lignes sont écrites dans l’ordre où elles arrivent, mais chacune pointe vers la suivante dans l’ordre de la clé. Insérer au milieu ne déplace rien : il suffit de modifier deux pointeurs ;
• le page directory part de la fin de la page et grandit vers le haut. Il contient un « slot » pour 4 à 8 lignes. Pour trouver une clé, InnoDB fait une recherche dichotomique dans ces slots, puis parcourt au plus quelques lignes de la liste chaînée ;
• le FIL trailer occupe les 8 derniers octets de la page. Dans l’ancienne disposition, il répète la somme de contrôle et les 32 bits de poids faible du LSN : une page dont le début et la fin ne correspondent pas n’a été écrite qu’à moitié. Sur MariaDB 10.11 (full_crc32), les 4 derniers octets sont la somme de contrôle elle-même, un CRC-32C du reste de la page, et les 4 octets qui les précèdent sont les 32 bits de poids faible du LSN.

Quand une page est pleine et qu’une ligne doit y entrer, InnoDB la coupe en deux (page split) et met à jour la page parente. Quand deux pages voisines descendent sous la moitié de leur remplissage, il peut les fusionner. Ces opérations sont coûteuses et laissent des pages à moitié vides : c’est la fragmentation.

Le doublewrite buffer : pourquoi écrire deux fois

Un disque garantit l’atomicité d’écriture de 512 octets ou 4 Ko, pas de 16 Ko. Si le serveur s’arrête au milieu de l’écriture d’une page, celle-ci peut se retrouver à moitié ancienne, à moitié nouvelle : une torn page. Le redo ne peut pas la réparer, car il décrit des modifications à appliquer sur une page cohérente. InnoDB écrit donc chaque page d’abord dans le doublewrite buffer, puis à son emplacement définitif. Après un crash, une page déchirée est remplacée par sa copie intacte, puis le redo s’applique. Sur un système de fichiers qui garantit lui-même des écritures atomiques de 16 Ko (ZFS, certains SSD), on peut le désactiver.

Une ligne

• Les colonnes cachées : DB_TRX_ID est l’identifiant de la dernière transaction qui a modifié la ligne, DB_ROLL_PTR pointe vers l’image précédente dans l’undo. Une transaction qui ne doit pas voir la modification suit ce pointeur pour reconstruire l’ancienne version ;
• sans clé primaire, InnoDB utilise la première clé unique non nulle, ou à défaut ajoute une colonne cachée DB_ROW_ID de 6 octets, alimentée par un compteur global partagé par toutes ces tables. Définissez toujours une clé primaire ;
• le bit « deleted » : un DELETE ne fait que marquer la ligne. Elle est réellement retirée plus tard par le purge thread, une fois qu’aucune transaction ne peut plus la voir ;
• les NULL ne coûtent qu’un bit dans le bitmap : aucune place dans les données ;
• les colonnes longues (TEXT, BLOB, gros VARCHAR) : une ligne doit tenir dans environ la moitié d’une page, pour qu’une page contienne au moins deux lignes. Au format DYNAMIC, une valeur trop longue part entière dans des pages de débordement, et la ligne ne garde qu’un pointeur de 20 octets.

L’index cluster d’InnoDB : la table est un arbre

C’est la décision de conception la plus importante d’InnoDB : la table est l’index de la clé primaire. Il n’existe pas de « tas » de lignes à côté : les lignes complètes sont stockées dans les feuilles d’un arbre B+ trié par clé primaire. Plusieurs conséquences pratiques en découlent :

• une recherche par clé primaire descend l’arbre et trouve directement la ligne. Avec des pages de 16 Ko et une clé BIGINT, une page intermédiaire référence plus d’un millier de pages filles : trois niveaux suffisent pour des centaines de millions de lignes ;
• une clé primaire croissante (AUTO_INCREMENT) ajoute toujours à droite de l’arbre : les pages se remplissent proprement, sans split au milieu. Une clé aléatoire (UUID v4) insère n’importe où, provoque des splits partout et laisse des pages à moitié pleines. Les UUID ordonnés dans le temps (UUID v7) évitent ce problème ;
• un index secondaire ne contient pas l’adresse physique de la ligne mais sa clé primaire. Une recherche par index secondaire coûte donc deux descentes d’arbre. En contrepartie, déplacer une ligne lors d’un split ne demande pas de mettre à jour les index secondaires ;
• une clé primaire longue est recopiée dans chaque index secondaire : une clé de 36 caractères multiplie la taille de tous les index ;
• un index couvrant, qui contient toutes les colonnes dont la requête a besoin, évite la seconde descente : EXPLAIN affiche alors Using index.

Pourquoi le fichier ne rétrécit pas

Un DELETE massif libère de la place dans les pages, que les insertions suivantes réutiliseront, mais ne rend rien au système de fichiers. Pour récupérer l’espace, il faut reconstruire la table :

OPTIMIZE TABLE shop.orders;          -- équivaut, pour InnoDB, à ALTER TABLE … FORCE
-- data_free n'est qu'une estimation, voir les tests plus bas
SELECT table_name, data_free/1024/1024 AS free_mb
FROM information_schema.tables WHERE table_schema='shop' ORDER BY data_free DESC;

N’utilisez pas data_free pour décider si une reconstruction vaut le coup : dans mon test, il est resté à 7,0 Mo après la suppression de 90 % d’une table dont le fichier faisait 80 Mio, et la reconstruction a ramené le fichier à 9 Mio. C’est la taille du fichier sur disque que la reconstruction récupère.

La reconstruction copie toutes les lignes dans un nouveau fichier, compact, puis remplace l’ancien. Elle se fait en ligne sur les versions récentes, mais demande temporairement l’espace d’une seconde copie de la table.

Prochain billet : PostgreSQL, qui a fait le choix inverse sur presque tous ces points : une table en tas, des pages de 8 Ko, et des anciennes versions gardées dans la table elle-même.

Ce que j’ai mesuré (mise à jour du 2026-11-04)

J’ai d’abord écrit ce billet à partir de la documentation. Je l’ai ensuite testé sur MariaDB 10.11.18, Rocky Linux 10.2, WSL2, sur une instance jetable avec un buffer pool de 256 Mo. Ces tests ont corrigé deux affirmations plus haut (la position de la somme de contrôle et data_free) et confirmé la disposition des pages, les colonnes cachées, le comportement d’OPTIMIZE TABLE et l’emplacement du doublewrite buffer. Je n’ai pas testé MySQL 8 : les numéros de page 3 (SDI) et 4 (racine) pour MySQL viennent de la documentation.

Les deux vues de l’information schema, et les fichiers d’une base d’une seule table :

-rw-rw---- 1 mysql mysql  1055 Nov  4 17:21 orders.frm
-rw-rw---- 1 mysql mysql 65536 Nov  4 17:21 orders.ibd
| name             | file_size | allocated_size |
| innodb_system    |  12582912 |       12587008 |
| shop/orders      |     65536 |          65536 |
| innodb_temporary |  12582912 |       12582912 |
ERROR 1109 (42S02) at line 1: Unknown table 'INNODB_TABLESPACES' in information_schema

Les premières pages de orders.ibd (4 pages de 16 Kio), après un arrêt propre. La page 0 est l’en-tête de l’espace, la 1 le bitmap du change buffer, la 2 les inodes de segments, et la page 3 est la racine de l’index clustered :

page 0: type=8 (FSP_HDR) page_no=0 space_id=5 lsn=46034 prev=0xffffffff next=0xffffffff
page 1: type=5 (IBUF_BITMAP) page_no=1 space_id=5 lsn=45760 prev=0xffffffff next=0xffffffff
page 2: type=3 (INODE) page_no=2 space_id=5 lsn=46034 prev=0xffffffff next=0xffffffff
page 3: type=17855 (INDEX) page_no=3 space_id=5 lsn=47160 prev=0xffffffff next=0xffffffff

Où se trouve la somme de contrôle. Avec innodb_checksum_algorithm = full_crc32, les octets 0 à 3 sont à zéro, et les 4 derniers octets égalent le CRC-32C des 16380 premiers octets de la page, pour chacune des 4 pages :

innodb_checksum_algorithm = full_crc32
page 0: bytes 0-3=0x00000000  last 4 bytes=0x34c1c1a7  crc32c(page without last 4 bytes)=0x34c1c1a7  match=True
page 1: bytes 0-3=0x00000000  last 4 bytes=0x248d385f  crc32c(page without last 4 bytes)=0x248d385f  match=True
page 2: bytes 0-3=0x00000000  last 4 bytes=0x85d98b4a  crc32c(page without last 4 bytes)=0x85d98b4a  match=True
page 3: bytes 0-3=0x00000000  last 4 bytes=0x3e60cc66  crc32c(page without last 4 bytes)=0x3e60cc66  match=True

Les colonnes cachées, lues directement dans la page 3. Après la clé primaire sur 4 octets (80000001, bit de signe inversé) viennent DB_TRX_ID sur 6 octets et DB_ROLL_PTR sur 7 octets, puis les colonnes utilisateur :

id=1 at page offset 128: pk=80000001 DB_TRX_ID=000000000013 DB_ROLL_PTR=84000001340110 columns: 616c6963656669727374060300000018
id=2 at page offset 163: pk=80000002 DB_TRX_ID=000000000013 DB_ROLL_PTR=8400000134011c columns: 626f627365636f6e64050500000020ff
id=3 at page offset 197: pk=80000003 DB_TRX_ID=000000000013 DB_ROLL_PTR=84000001340128 columns: 6361726f6c7468697264000000000000

Un DELETE ne réduit pas le fichier, data_free ne voit pas l’espace libre, et OPTIMIZE TABLE reconstruit la table. ALTER TABLE ... FORCE donne la même taille finale (9437184 octets). ibdata1 est passé de 12 à 76 Mio pendant le chargement (les undo logs sont ici dans le tablespace système, innodb_undo_tablespaces = 0) et n’a pas diminué ensuite. Dans les lignes de tableau ci-dessous, les colonnes sont table_rows, data_mb, index_mb et data_free_mb :

-- after loading 300000 rows
   big.ibd: 83886080 bytes
   ibdata1: 79691776 bytes
|     299999 |     0.0 |      0.0 |          7.0 |
-- after DELETE of 90% of the rows
   big.ibd: 83886080 bytes
   ibdata1: 79691776 bytes
|      29999 |     0.0 |      0.0 |          7.0 |
| shop.big | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
-- after OPTIMIZE TABLE
   big.ibd: 9437184 bytes
   ibdata1: 79691776 bytes
|      29614 |     8.5 |      0.0 |          0.0 |
-- before ALTER TABLE FORCE
   big.ibd: 146800640 bytes
   ibdata1: 79691776 bytes
|      29614 |     8.5 |      0.0 |          7.0 |
-- after ALTER TABLE FORCE
   big.ibd: 9437184 bytes
   ibdata1: 79691776 bytes
|      29614 |     8.5 |      0.0 |          0.0 |

Les clés aléatoires. 500 000 lignes, buffer pool de 256 Mo, tout en mémoire. L’UUID() horodaté (version 1) et la clé aléatoire de type version 4 donnent des fichiers plus gros qu’un BIGINT séquentiel. Les largeurs de clé diffèrent (8 et 36 octets) : comparez plutôt les deux variantes d’UUID entre elles, la clé aléatoire est 40 % plus grosse, ce qui est cohérent avec des pages moins remplies (je n’ai pas mesuré le remplissage). Je n’ai pas pu montrer de ralentissement des insertions avec tout en mémoire, et la dernière ligne inclut le coût de RANDOM_BYTES() :

insert 500000 rows, sequential bigint : 0.8 s
insert 500000 rows, random UUID (v1)  : 1.1 s
| shop/pk_seq  |    32.0 |
| shop/pk_uuid |    48.0 |
insert 500000 rows, random v4-like UUID : 2.3 s
| shop/pk_rand |    68.0 |

Où se trouve le doublewrite buffer. J’ai démarré MariaDB 10.11.18 deux fois, avec la valeur par défaut innodb_doublewrite=ON puis avec OFF, chargé 20 000 lignes dans une table et tracé les écritures de pages avec strace. Les deux essais utilisaient innodb_use_native_aio=OFF, sinon les écritures ne passent pas par pwrite64 et strace ne les voit pas. Avec le doublewrite activé, ibdata1 reçoit deux écritures groupées qui commencent à la page 64 (160 Kio et 64 Kio), et Innodb_dblwr_pages_written passe de 0 à 39. Désactivé, rien n’est écrit à la page 64 d’ibdata1 et le compteur reste à 0. Sur MariaDB 10.11, le doublewrite buffer se trouve donc dans le tablespace système. Je n’ai pas regardé sa disposition exacte au-delà de la page de départ.

################ innodb_doublewrite = ON (default), native AIO off
| innodb_doublewrite | ON    |
| Innodb_dblwr_pages_written | 0     |
| Innodb_dblwr_pages_written | 39    |
  ibdata1             4 writes,     262144 bytes, pages 0..316
first alternations (file, consecutive writes): [['dw.ibd', 10], ['ibdata1', 1], ['dw.ibd', 39], ['ibdata1', 1], ['dw.ibd', 1], ['ibdata1', 2]]
ibdata1 writes as (first page, KiB): [(316, 16), (64, 160), (64, 64), (0, 16)]
################ innodb_doublewrite = OFF, native AIO off
| innodb_doublewrite | OFF   |
| Innodb_dblwr_pages_written | 0     |
| Innodb_dblwr_pages_written | 0     |
  ibdata1             2 writes,      32768 bytes, pages 0..49
first alternations (file, consecutive writes): [['dw.ibd', 5], ['ibdata1', 1], ['dw.ibd', 20], ['ibdata1', 1]]
ibdata1 writes as (first page, KiB): [(49, 16), (0, 16)]

Les compteurs sont ceux de SHOW GLOBAL STATUS LIKE 'Innodb_dblwr%', avant et après le chargement.

---

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 1/3. · Billet suivant → · English version

Articles liés

• Les fichiers de PostgreSQL
• Les fichiers d’Oracle, blocs et ROWID
• Les réglages MariaDB qui comptent