Installation et configuration d’innodb

Dans plusieurs débats il a été reproché à MySql, de ne pas être un véritable serveur de base de données. Par défaut mysql, il est vrai ne supporte ni les transactions ni les contraintes sur les clés étrangères. Tout ce qui fait qu’un serveur puisse éviter la corruption des données lors de sessions multiples simultanées.
C’est le format de table, Myisam, utilisé par défaut dans MySql, qui est en cause. Ce type de table, même s’il permet de très bonnes performances, ne supporte ni les transactions, ni les contraintes sur les clés étrangères, ni les verrous au niveau utilisateur ….

Cette limitation peut être facilement contournée dans certains cas. Mais des environnements plus critiques demandent une exigence accrue de sécurité pour les données.

Introduit avec la version 3.23.5 de MySql, INNODB répond à cette exigence en offrant le support des transactions, des clés étrangères et les verrous dans les enregistrements. Les versions futures annoncent le support des requêtes imbriquées et des procédures stockées.

Ce tutorial va vous permettre d’installer, de configurer et d’utiliser les tables Innodb dans MySql.

INNODB est présent avec les versions binaires et sources Unix et binaires Windows depuis la version 3.23.6 ainsi que dans les versions standard, Pro et max de mysql.

L’installation de MySql avec le support INNODB ne pose pas de problème particulier à partir des binaires, ceux ci ayant étés compilés pour fournir le moteur de transaction INNODB.

Il faut cependant savoir que Mysqld-Opt n’inclut pas INNODB, préférez Mysqld-max.

L’installation à partir des sources nécessite d’ajouter un préfixe dans le script de configuration

Contrairement aux tables de types MyIsam, INNODB utilise un ou plusieurs fichiers pour stocker les données et les indexs de toutes les tables et non un fichier par table. C’est ce que l’on nomme un tablespace, la structure des tables reste stockée dans un fichier.frm. Innodb utilise aussi des fichiers journaux pour conserver les traces des transactions en cours.

L’utilisation d’INNODB suppose de pouvoir configurer les emplacements de ces fichiers.

MySql utilise un fichier de configuration pour modifier le comportement du serveur. Ce fichier est présent sur votre serveur au niveau du disque de démarrage sous Windows (ie c:/) ou dans /etc/ sous unix. Il peut aussi se situer dans le répertoire data de mysql ou peut être précisé dans les options de démarrage de mysqld.

# The MySQL server
[mysqld]
port=3306
#socket=MySQL
skip-locking
set-variable       = key_buffer=256M
set-variable       = max_allowed_packet=1M
set-variable       = table_cache=256
set-variable       = sort_buffer=1M
set-variable       = record_buffer=1M
set-variable       = myisam_sort_buffer_size=64M
set-variable       = thread_cache=8

# Try number of CPU's*2 for thread_concurrency

set-variable = thread_concurrency=8
log-bin
server-id = 1

# Uncomment the following rows if you move the MySQL distribution to another
# location
#basedir = d:/mysql/
#datadir = d:/mysql/data/

# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=64M
#set-variable = bdb_max_lock=100000
# Uncomment the following if you are using Innobase tables
#innodb_data_file_path = ibdata1:1000M
#innodb_data_home_dir = c:\ibdata
#innodb_log_group_home_dir = c:\iblogs
#innodb_log_arch_dir = c:\iblogs
#set-variable = innodb_mirrored_log_groups=1
#set-variable = innodb_log_files_in_group=3
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50

Le fichier de configuration My.cnf est divisé en plusieurs sections destinées à modifier le comportement de tel ou tel élément des outils de mysql. Les sections sont identifiées par des entêtes entre crochets.

INNODB fonctionnant avec des « tablespace(s) », il faut indiquer où ce(s) fichier(s) doit(vent) se trouver.

L’ instruction “innodb_data_file_path” spécifie le nom et la taille du/des fichier(s) « tablespace » d’INNODB. C’est l’option minimum pour l’utilisation d’innodb.

La taille des fichiers peut être limitée par le système exploitation (2 go sur certaines versions de linux, 4 go sous Windows NT/2000).

Par défaut le(s) fichier(s) sera(ont) créé(s) dans le répertoire de données de Mysql. Pour les créer dans un répertoire spécifique, il faut ajouter l’option «innodb_data_home_dir» .

L’utilisation d’une chaîne vide dans cette option permet l’emploi de chemin absolu dans l’option “innodb_data_file_path”, permettant ainsi la création de « tablespaces » sur plusieurs partitions.

Il faut cependant s’assurer que Mysqld peut avoir les droits en lecture et en écriture sur le répertoire en question. Mysql ne pouvant le créer, il est impératif de s’assurer que le(s) répertoire(s) existe(nt) et qu’il(s) soit(ent) accessibles avant de relancer MySqld.

La nature transactionnelle d’INNODB oblige mysql à gérer un certain nombre de fichiers de logs pour conserver les différentes versions des tables en cours d’utilisation.

Par défaut les fichiers logs sont situés dans le répertoire de données de MySQl. IL est possible d’utiliser un autre emplacement en utilisant l’option « innodb_log_group_home_dir »

Les fichiers journaux sont géré en pool circulaire. Le nombre de pool de fichiers logs peut être paramétré par “innodb_mirrored_log_groups ”. Cependant MySql AB recommande de n’utiliser qu’un seul groupe de fichier de logs.

Les journaux sont utilisés lors d’une validation d’une transaction, lorsque l’administrateur utilise flush log ou lorsque la mémoire dédiée aux transactions est pleine.

Le nombre de fichiers de log par groupe doit aussi être spécifié. C’est l’option « innodb_log_files_in_group » qui nous le permet. MySql AB recommande de n’utiliser que 3 fichiers de logs par pool, mais certaines utilisations, ne nécessitant que peu d’utilisation du moteur transactionnel, permettent d’en utiliser que 2.

La taille de chaque fichier journal peut être spécifiée par « innodb_log_file_size ». Ce paramètre doit être choisi en fonction de l’utilisation du serveur, du nombre de transactions et de la taille de la mémoire tampon que vous désirez allouer. Tout comme les fichiers de données, la taille est limitée aux règles du système d’exploitation. La taille doit être comprise entre 1Mo et 15 % de la taille de la mémoire tampon du pool.

Cette dernière valeur peut être fixée « innodb_buffer_pool_size ». Cette mémoire tampon est utilisée par MySql pour stocker les données. Ainsi une mémoire tampon élevée permet d’éviter des accès au disque dur trop nombreux. Il faut éviter cependant que la taille de cette mémoire tampon dépasse les 80 % de la RAM du serveur. Ces valeurs doivent être données en regard du nombre d’enregistrements dans les tables que vous utilisez ainsi que du nombre de transactions.

Cette valeur est aussi associée à « innodb_additional_mem_pool_size » qui fixe la mémoire dédiée aux informations du dictionnaire de données et aux structures internes de données. La valeur par défaut est de 2 Mo, elle doit être fixée en fonction du nombre de tables gérées.

La mémoire tampon des logs permet, elle aussi, d’influer sur les performances de votre serveur. Chaque manipulation est enregistrée dans cette mémoire tampon. Elle est vidée vers le disque lorsque la limite est atteinte. Si vous voulez limiter ces accès au disque lors de transactions importantes vous pouvez donner une valeur élevée pour faire en sorte que les transactions restent en mémoire jusqu’à leur validation.

Autre moyen d’agir sur les performances, «innodb_flush_log_at_trx_commit». Lorsque cette valeur est fixée à 1 les transactions abouties sont enregistrées dans les fichiers journaux. Fixées à 0, les transactions ne sont écrites sur le disque que lors du vidage de la mémoire tampon et non à chaque validation. Cela accroît les performances du système mais peut compromettre les dernières transactions en cas de crash. La valeur 2 procède de même, elle permet de garder en mémoire les transactions abouties.

L’archivage des fichiers journaux se fait en fixant la valeur « innodb_log_archive » à 1.

Cependant cette fonction est relativement peu utile. Les fonctions de restauration étant assurées grâce « innodb_log_arch_dir ».

La valeur doit obligatoirement être le même que pour « innodb_log_group_home_dir », du moins jusqu'à la version 4.0.6 de MySql.

D’autres paramètres peuvent être utiles lors de la configuration de votre serveur.

« innodb_file_io_threads » donne le nombre limite d’accès disque simultané. La valeur optimale pour Unix est 4. Pour Windows, suivant la vitesse des disques, ce nombre peut être augmenté jusqu'à 6 - 7.

« innodb_lock_wait_timeout » Si INNODB détecte les transactions bloquées et effectue un rollback, il se peut que certaines transactions utilisant LOCK TABLES puissent ne pas être détectées. « innodb_lock_wait_timeout » permet de fixer un timeout en secondes limitant les transactions bloquées. Si vous expérimentez un nombre important de DEAD LOCK, il est utile de baisser cette valeur de 30 s, valeur par défaut, à 10 secondes.

«innodb_flush_method” Fixe la méthode de gestion des log pour Unix et Unix seulement. Par défaut MySql utilise fsync. La valeur O_DSYNC permet d’utiliser O_SYNC. Cette valeur dépend de la config de votre serveur Unix.

« innodb_thread_concurrency » Gère les ressources en processeur de la machine. Innodb utilise ce paramètre pour garder le nombre de threads sur innodb. Suivant le nombre de processeurs du serveur, vous pouvez augmenter cette valeur au-dessus de 8, valeur par défaut. En cas de baisse des performances, vous pouvez baisser ce nombre.

« innodb_fast_shutdown » par défaut Innodb ne purge pas les mémoires tampon lors d’un arrêt normal du serveur. Pour effectuer une purge utilisez la valeur 0. Cependant l’opération de purge peut prendre plusieurs minutes suivant l’utilisation du serveur.

Apres avoir modifié le fichier de configuration, vous devez redémarrer mysql pour que les modifications soient prises en compte. Il est conseillé de lancer le dameon mysql avec la console, pour vérifier la présence d’un ou plusieurs messages d’erreur.

C’est à ce moment que MySql créée les fichiers nécessaires au fonctionnement d’innodb et tente d’initialiser le moteur transactionnel.

InnoDB: The first specified data file c:\innodb\ibdata2 did not exist:
InnoDB: a new database to be created!
030428 21:34:23 InnoDB: Setting file c:\innodb\ibdata2 size to 20 MB
InnoDB: Database physically writes the file full: wait...
030428 21:34:27 InnoDB: Log file C:\innodb\var\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file C:\innodb\var\ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
030428 21:34:28 InnoDB: Log file C:\innodb\var\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file C:\innodb\var\ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
030428 21:34:29 InnoDB: Log file C:\innodb\var\ib_logfile2 did not exist: new to be created
InnoDB: Setting log file C:\innodb\var\ib_logfile2 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
mysqld-max: ready for connections.
Version: '4.0.12-max' socket: '' port: 3306
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
030428 21:34:41 InnoDB: Started

Exemple de création de fichiers données et journeaux dans le journal de mysql

Deux process sont lancés dans mysql, mysqld le moteur traditionnel et innodb le moteur de transaction.

Pour réparer une erreur, il convient d’arrêter le deamon/service et supprimer les fichiers nouvellement créés, corrigez le problème et relancer MySqld.

Maintenance

La maintenance des tables INNODB est différente de celle des tables MYISAM.

IBMONITOR ET INNODB STATUT

Le moteur transactionnel renvoit toutes les informations d’état utiles au management d’un serveur.

Par défaut ces données sont renvoyées par la sortie standard de mysqld. Pour les voir il est possible d’exécuter MySql en tant qu’application console ou d’utiliser le client mysql et la commande :

Les informations sont séparées en plusieurs sections (SEMAPHORES, TRANSACTIONS, FILE I/O, INSERT BUFFER AND ADAPTIVE HASH INDEX, LOG, BUFFER POOL AND MEMORY, ROW OPERATIONS).

SEMAPHORES Liste les threads en attente. Un nombre important indique que la valeur donnée à « innodb_thread_concurrency » est trop forte.

TRANSACTIONS Liste les transactions en cours et les lock tables. Il est important de vérifier ici les deadlock pour vérifier les goulets d’étranglement dans votre configuration.

FILE I/O liste les demandes d’écriture et leur état. Sous Windows si le nombre est élevé vous pouvez augmenter la valeur du paramètre « innodb_file_io_threads ».

INSERT BUFFER AND ADAPTIVE HASH INDEX liste les paramètres des index.

LOG liste les paramètres des Logs.

BUFFER POOL AND MEMORY donne les statistiques sur les pages lues et écrites permettant de vérifier si la taille de la mémoire tampon est suffisante ou non.

ROW OPERATIONS Statistiques sur les requêtes.

SAUVEGARDE ET RESTAURATION

Les sauvegardes des tables innodb ne peuvent s’envisager comme la sauvegarde de tables MyIsam. En effet un simple DUMP des tables ne permet que d’extraire les données. Les transactions en cours ne sont donc pas conservées. Cette méthode ne doit être utilisée que dans les cas de corruptions totales des fichiers de données.

MySql n’inclut pas d’outils de back up pour les tables innodb. Pour effectuer un back up des tables et des transactions, il existe deux méthodes : La copie de fichiers ou la réplication de tables.

La copie des fichiers s’effectue après l’arrêt du serveur, vous devez alors copier les fichiers de données et les fichiers de logs dans un endroit sûr.

Les fichiers à copier sont :

La réplication utilise un serveur secondaire où les données sont copiées. Le support des tables innodb permet une copie propre des données et journaux.

La restauration dépend de la méthode utilisée. Il faut cependant savoir que MySql vérifie les log à chaque redémarrage du serveur. Les transactions non abouties seront donc totalement annulées.

Après avoir récupérer les fichiers venant soit d’une simple copie soit d’une réplication, remplacez et redémarrez le serveur si possible à partie de la ligne de commande, pour vérifier que les opérations se déroulent bien.

Si l’opération échoue pour cause de fichiers corrompus, tentez de trouver un jeu de fichier non corrompu. SI cela échoue, vous pouvez toujours tenter de recréer les tables et d’insérer les enregistrements que vous avez conservés.

REPLICATION

La création d’un serveur de réplication s’effectue par la copie des tablespaces, des fichiers de logs sur le serveur esclave, des fichiers.frm et l’edition de son fichier de configuration.

La réplication avec les tables innodb inclut la lecture des logs. Si une transaction a échoué, celle ci ne sera pas répliquée.

La gestion des tables n’est pas la même qu’avec MyIsam. Il n’est pas possible d’utiliser « optmize table » pour vérifier et gèrer les tables innodb. Il est préférable d’effectuer alors un dump de la table et de supprimer celle ci avant de la recréer et de réimporter les données.

L’utilisation

L’utilisation d’innodb diffère aussi de l’utilisation classique de MySql. Les tables et les index de type innodb utilisent des méthodes différentes d’acces. Chaque table possède un « clustered index ». Les données sont sur la même page que cet index.

Les index sont de type B-Trees, la taille d’une page d’index est par défaut de 16 K. Innodb peut aussi automatiquement construire un index de hash (hash index).

Avec Innodb, toute l’activité se produit dans un contexte transactionnel. A savoir que même si par défaut MySql valide automatiquement les transactions, celles ci restent des transactions et mysql effectue en réalité un commit à chaque fin de requête.

Pour éviter que mysql utilise ce comportement, on peut commencer le script par BEGIN et le finir par COMMIT ou ROLLBACK. Une autre méthode consiste à fixer la variable autocommit à 0 a chaque début de session. Il est possible de fixer cette valeur dans le fichier de config du client mysql.

La création de table d’INNODB se fait en ajoutant type = INNODB à la fin de l’instruction de création de la table.

Il est aussi possible de modifier une table existante d’un autre type vers INNODB.

Si une erreur survient lors de la modification d'une table, vérifiez si celle ci ne contient pas d'index full text. Il convient aussi non pas d'utiliser ALTER TABLE mais de supprimer la table en prenant soin d'avoir effectué un DUMP de la table, puis de importer les données dans la table en utilisant set autocommit = 0 avant l'insertion et un commit à la fin.

Il est à noter que la compression de table MYISAM n’est pas supportée sous INNODB.

Le support INNODB permet l’utilisation des transactions, des contraintes sur les clés étrangères et de gérer des verrous aux niveaux des enregistrements et des tables.

Pour illustrer notre propos nous allons créer deux tables que nous utiliserons en exemple.

set auto_commit = 1;

create database innodbtest;

CREATE TABLE users (

id int(11) NOT NULL auto_increment,

nom varchar(25) default NULL,

prenom varchar(25) default NULL,

age int(11) default NULL,

PRIMARY KEY (id)

) TYPE=InnoDB;

CREATE TABLE salles (

idsalle int(11) NOT NULL auto_increment,

num char(3) default NULL,

refuser int(11) default NULL,

PRIMARY KEY (idsalle),

index user_ref (refuser),

foreign key theuser (refuser) references users(id) on delete set null

) TYPE=InnoDB;

grant select, insert, update, delete on innodbtest.* to user1@localhost;

grant select, insert, update, delete on innodbtest.* to user2@localhost;

flush privileges;
flush tables;

Cette exemple est une gestion, simplifiée à l’extrême, de salles de classe. Les instituteurs (table users) sont liés à une et une seule salle.

Les transactions

L’un des buts d’INNODB est de pouvoir, à tout moment, revenir à l’état antérieur d’une table après une transaction. En d’autres termes, seules les transactions validées sont considérées comme durables et leurs données enregistrées dans les tables.

Par défaut et jusqu'à la version 4.0.4 le niveau d’isolation des transactions et REPEATABLE READ. Les versions supérieures permettent de changer ce niveau d’isolation vers READ UNCOMMITED, READ COMMITED, SERIALISABLE.

Niveau d’isolation

REPEATABLE READ lit et bloque les index en laissant la possibilité d’effectuer des inserts

READ UNCOMMITED lit les enregistrements sans possibilité de regarder à la dernière version

READ COMMITED Une instruction peut seulement voir les lignes validées avant qu'elle ne débute

SERIALISABLE La transaction en cours peut seulement voir les lignes validées avant qu'une première requête ou une instruction de modification de données soit exécutée dans la transaction.

User1

Set autocommit = 0 ;

Update users set age=25 where id = 1;

commit

delete from salles where idsale = 1;

select * from salles where idsale = 1;

Empty set

Rollback;

select * from salles where idsale = 1;

idsalle num refuser

1 AC3 Null

User2

Set autocommit = 0 ;

Select age from users where id = 1;

Age
23

Commit

Select age from users where id = 1;

Age
25

select * from salles where idsale = 1;

idsalle num refuser

1 AC3 Null

Contraintes sur les clé étrangères

Les contraintes sur les clés étrangères sont gérées par INNODB. Pour ce faire INNODB utilise les index. Il est donc obligatoire que ces contraintes soient gérées à partir des index des deux tables liées, la clé primaire de la table principale et un index multiple ou unique pour la table secondaire. Les deux colonnes doivent être du même type.

CREATE TABLE salles (

idsalle int(11) NOT NULL auto_increment,

num char(3) default NULL,

refuser int(11) default NULL,

PRIMARY KEY (idsalle),

index user_ref (refuser),

foreign key theuser (refuser) references users(id) on delete set null

) TYPE=InnoDB;

Par défaut, cette contrainte empêche la création d’enregistrements dans la table secondaire sans qu’aucune référence ne soit possible.

Avec ON DELETE CASCADE permet la suppression d’enregistrements de la table secondaire.

ON DELETE SET NULL permet de données une valeur nulle à la col référencée.

Si lors de la création d’une contrainte, vous avec une erreur 150, cela signifie que celle-ci est mal formée. Vous devez vérifier que les colonnes sont bien indexées.

De plus il faut savoir que l’altération d’une table ou la création d’un index sur les versions < à 3.23.50 supprime toutes les contraintes.

La suppression d’une table engendre la suppression des contraintes qui lui sont associées. Enfin certaines versions de l’utilitaire MySqlDump ne recréent pas les contraintes sur les clés étrangères dans le script de création base de base de données.

La vérification des contraintes se fait grâce à show create table nom de la table (< 3.23.50).

Les verrous

INNODB permet la gestion des verrous au niveau des enregistrements et non plus au niveau des tables.

Ces verrous sont associés à une requête de sélection. Il en existe de deux sortes.

Permet d’éviter aux autres utilisateurs de modifier ou d’effacer les enregistrements sélectionnés. Cela permet d’avoir la dernière version des enregistrements.

Même chose que pour LOCK IN SHARE MODE sauf que les autres utilisateurs ne peuvent lire les enregistrements verrouillés.

Cela peut être utile lorsque l’on doit modifier des enregistrements à partir de données dans ces mêmes enregistrements.

La validation de la transaction implicite ou explicite permet de déverrouiller les tables et les enregistrements.

Si vous projetez d’utiliser régulièrement les verrous sur les enregistrements il est judicieux de donner une valeur en secondes à innodb_lock_wait_timeout pour éviter des effets de verrous permanents. Vous pouvez d’ailleurs vérifier vos tables et l’état des derniers verrous bloqués avec innodb monitor.

User1

Set autocommit = 0 ;

Select * from salles where idsalle = 4 FOR UPDATE;

idsalle num refuser

4 BC5 Null

update salles set refuser = 2 where idsalle = 4;

commit;

User2

Set autocommit = 0 ;

Select * from salles where idsalle = 4 FOR UPDATE;

idsalle num refuser

4 BC5 Null

commit;

Select * from salles where idsalle = 4 FOR UPDATE;

idsalle num refuser

4 BC5 2

CONCLUSION

INNODB est une grande avancée dans MySql. Il permet de s’affranchir de plusieurs limites physiques (la gestion de tables supérieures à 4GO) ou conceptuelles (les transactions et les clés étrangères).

Innodb ne remplace pas les tables MyIsam. Son utilisation doit être envisager dans des cas de besoins particuliers :

Dans d’autre cas INNODB peut ralentir votre système sans y apporter une richesse fonctionnelle importante.

User2

Set autocommit = 0 ; Update salles set refuser = 1 where idsalle = 1 ; Commit;

User1

User2

Set autocommit = 0 ;
Update salles set refuser = 1 where idsalle = 1 ;
Commit;