Installation et configuration d'InnoDB

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é 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 :

./configure --with-innodb

Contrairement aux tables de types MyIsam, InnoDB utilise un ou plusieurs fichiers pour stocker les données et les index 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 (i.e. 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.

MySQL AB donne quelques exemples de fichiers configuration :

my-medium.cnf, my-large.cnf, my-huge.cnf, my-small.cnf

# 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.

Seule la section [mysqld] permet de configurer le serveur.

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.

innodb_data_file_path = ibdata1:400M

Le format est donc nom_du_fichier1:taille;nom_du_fichier2:taille;…

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».

innodb_data_home_dir = /usr/inodb/data/

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.

innodb_data_home_dir =
innodb_data_file_path = /usr/inodb/data/ibdata1:400M;/users/inodb/ibdata2:400M

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) accessible(s) 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 »

innodb_log_group_home_dir = /usr/innodb/logs/

Les fichiers journaux sont gérés en pool circulaire. Le nombre de pools de fichiers logs peut être paramétré par « innodb_mirrored_log_groups ». Cependant MySQL AB recommande de n'utiliser qu'un seul groupe de fichiers 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 trois fichiers de logs par pool, mais certaines utilisations, ne nécessitant que peu d'utilisation du moteur transactionnel, permettent d'en utiliser que deux.

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 ».

innodb_log_arch_dir  = /usr/innodb/logs/

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és. 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 tampons 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.

Après 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

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

Si une erreur survient, vous devez vérifier les points suivants :

• les répertoires des données et des logs n'ont pas été créés ;
• l'utilisateur exécutant MySqld ne dispose pas des droits nécessaires pour créer/lire les fichiers et les répertoires en question ;
• les répertoires d'archives et les répertoires de logs ne sont pas les mêmes ;
• votre disque dur est plein ;
• Le nom d'un répertoire de données ou de log est le même qu'un des noms des fichiers (log ou data).

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

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

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'accès. 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. À 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 à chaque début de session. Il est possible de fixer cette valeur dans le fichier de config du client MySQL.

SET AUTOCOMMIT = 0;

La création de table d'InnoDB se fait en ajoutant type = INNODB à la fin de l'instruction de création de la table :

CREATE TABLE nom_table
(. . .)
TYPE = INNODB;

Il est aussi possible de modifier une table existante d'un autre type vers InnoDB :

ALTER TABLE nom table type = 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 d'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 tables 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 au niveau 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;

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.

SET [SESSION | GLOBAL]
TRANSACTION ISOLATION LEVEL

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.

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

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

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.

SELECT … LOCK IN SHARE MODE

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.

SELECT … FOR UPDATE

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.

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;

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