Format de stockage XtraDB/InnoDB
Contents
Le moteur XtraDB/InnoDB supporte 4 formats de stockage différents : les formats COMPACT, REDUNDANT, COMPRESSED et DYNAMIC. Ils peuvent être précisés avec l'option ROW FORMAT avec la clause CREATE TABLE. Par défaut, le format est COMPACT.
La clause SHOW TABLE STATUS permet de voir le format utilisé. Depuis la version 10.0 de MariaDB, cette information existe aussi dans la table Information Schema INNODB_SYS_TABLES.
Compact
Compact est le format par défaut et est généralement approprié pour le format de fichier Antelope. Introduit dans MySQL 5.0.
Dans ce format (comme dans Redundant) les colonnes BLOB et TEXT sont partiellement stockées dans les pages de données. Au moins 767 octets sont stockés dans la ligne; tous les débords sont stockés dans des pages dédiées. Comme les tailles de lignes de Compact et Redundant sont d'environ 8000 octets, cela limite le nombre de colonnes BLOB ou TEXT qui peuvent être utilisées dans une table. Chaque page de BLOB page contient 16Ko, sans compter les données.
Les autres colonnes peuvent être stockées dans des pages différentes si elles dépassent la taille de ligne maximum par page.
Redundant
Redundant est l'ancien, format non compressé supporté par les anciennes versions de MySQL. C'était le seul format dispobible avant la version 5.0 et a été le mode par défaut dans MySQL 5.0.3. Il est recommandé de paramétrer innodb_strict_mode lorsque vous l'utilisez.
Dynamic
Les tables en format Dynamic contiennent des enregistrements de taille variable, ce qui permet de mieux exploiter l'espace disque que Compact ou Redundant, notamment pour les tables contenant des BLOBs, mais moins que le format Compressed.
Il ne peut être utilisé qu'avec le format de fichier XtraDB Barracuda, et nécessite que les tables et index soient stockés dans leur propre tablespace, ce qui implique que les variables systèmes soient parametrées innodb_file_per_table=1 et innodb_file_format=barracuda. Il est recommandé de parametrer innodb_strict_mode lors de l'utilisation de ce format.
La taille maximum des lignes est désormais de 65535 octets.
Avec le format Dynamic (et Compressed), les colonnes BLOB et TEXT sont stockées différemment de Compact. Si les données ne peuvent être contenues dans une ligne de page, alors seulement un pointeur sera stocké et contiendra l'adresse de la page dédiée. Chaque page externe contient une partie des données et l'adresse de la page suivante, si nécessaire. Les pointeurs ont une taille de 20Ko. Cela permet de stocker un grand nombre de colonnes BLOB ou de TEXT dans une table.
Compressed
Le format Compressed permet de réduire la taille des données. Il ne peut être utilisé qu'avec le format de fichier XtraDB Barracuda, et nécessite que les tables et index soient stockés dans leur propre tablespace, ce qui implique que les variables systèmes soient parametrées innodb_file_per_table=1 et innodb_file_format=barracuda. Il est recommandé de parametrer innodb_strict_mode lors de l'utilisation de ce format.
Le fait d'utiliser le format Compressed reduit aussi le KEY_BLOCK_SIZE par défaut. Si KEY_BLOCK_SIZE est omis de la clause CREATE TABLE ou ALTER TABLE, il sera par défaut à 8Ko - traditionnellement c'est 16Ko (cf. innodb_page_size). Il est aussi possible de régler le KEY_BLOCK_SIZE à 1Ko, 2Ko, 4Ko ou 16Ko. Le fait de le régler à 16Ko, la taille standard, provoquera généralement une compression minimale à moins qu'il n'y ait beaucoup de colonnes de type BLOB, TEXT ou VARCHAR.
Notez que préciser un KEY_BLOCK_SIZE spécifique dans une définition de table provoquera automatiquement une compression - Il n'est donc pas nécessaire de préciser l'option ROW_FORMAT=COMPRESSED.
Afin d'évider trop de compression/décompression de pages, XtraDB/InnoDB essaye de garder les pages compressées et decompressées dans le buffer pool, quand il y a assez d'espace. Cela donne un cache plus gros. Lorsque la place vient à manquer, un algorithme de LRU vient décider de la suppression de pages compressées ou decompressées du buffer: pour soulager les CPUs, les pages compressées sont supprimées en priorité; pour soulager les I/O, les pages non compressées sont supprimées en priorité. Bien entendu, quand cela est nécessaire, la règle s'applique aux pages compressées et non compressées.
Chaque page compressée a une entrée de journal de modification non compressée, stockée dans la page elle-même. XtraDB/InnoDB y écrit de petits changements. Quand l'espace dans le journal vient à manquer, la page est décompressée, les changements sont appliquées, puis la page est à nouveau compressée. Ceci permet d'eviter d'avoir des opérations de compression/décompression inutiles.
Parfois, un échec de compression peut arriver, parce que les données débordent de la page. Quand cela arrive, la page (et le noeud d'index) est divisée en deux pages différentes. Ce processus peut être repeté récurisvement jusqu'à ce que les données rentrent dans les pages. Cela peut provoquer des hausses de charge CPU sur des serveurs chargés déjà par beaucoup d'opérations d'écritures.
Avant d'écrire une page compressée dans un fichier de données, XtraDB/InnoDB écrit dans le Redo Log. Cela garantit que le Redo Log pourra être utilisé pour restaurer les tables en cas de crash, même si la bibliothèque d'outils de compression est mise à jour et qu'apparaissent des incompatibilités. Mais cela implique que le Redo Log pourra grossir plus vite et nécessitera plus d'espace disque ou bien que la fréquence des Checkpoints devra être plus importante.
Les tables d'INFORMATION_SCHEMA peuvent aider à observer les performances des tables compressées par XtraDB/InnoDB:
- INNODB_CMP and INNODB_CMP_RESET
- INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET
- INNODB_CMPMEM and INNODB_CMPMEM_RESET
Exemple
SET SESSION innodb_strict_mode = ON;
CREATE TABLE compressed_table
(x INT PRIMARY KEY)
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=4;
-- Vérifier que la table est compressée
SELECT ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME = 'test/compressed_table';
+------------+
| ROW_FORMAT |
+------------+
| Compressed |
+------------+
