MySQL Reference Manual :: 16.4 Configuration InnoDB

Pour utiliser les tables InnoDB en MySQL versions 3.23, voyez Section 16.3, « InnoDB avec MySQL version 3.23 ».

En MySQL 4.0, vous n'avez rien à faire pour obtenir le support des tables InnoDB. Si vous ne souhaitez pas utiliser les tables InnoDB, vous pouvez ajouter l'option skip-innodb dans votre fichier d'options MySQL.

Les deux ressources disques importantes gérées par InnoDB sont sa table de données et son fichier de log.

Si vous ne spécifiez aucune options de configuration InnoDB, MySQL 4.0 et plus récent créera un fichier de données auto-croissant appelé ibdata1 et deux fichiers de log de 5 Mo appelés ib_logfile0 et ib_logfile1 dans le dossier de données MySQL. En MySQL 4.0.0 et 4.0.1, le fichier de données est de 64 Mo et pas auto-croissant. En MySQL 3.23, InnoDB ne démarrera pas si vous ne fournissez pas d'options de configuration.

Note : pour obtenir les meilleures performances, vous devez explicitement configurer les paramètres InnoDB dans les exemples ci-dessous. Naturellement, il vous faudra adapter les configurations à votre architecture.

Pour configurer le fichier de données InnoDB, utilisez l'option innodb_data_file_path dans la section [mysqld] du fichier my.cnf. Sous Windows, vous pouvez utiliser my.ini à la place. La valeur de innodb_data_file_path doit être une liste d'un ou plusieurs fichiers. Si vous indiquez plusieurs fichiers, séparez les noms par des caractères points-virgules (';') :

innodb_data_file_path=datafile_spec1[;datafile_spec2]...

Par exemple, une configuration qui crée explicitement un espace de table, avec les mêmes caractéristiques que la configuration par défaut de MySQL 4.0 est :

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend

Cette configuration crée un fichier de données de 10 Mo ibdata1, auto-croissant. Il n'y a pas de dossier de sauvegarde d'indiqué : par défaut, c'est le dossier de données de MySQL.

Les tailles sont spécifiées avec les suffixes M et G pour indiquer des megaoctets et des gigaoctets.

Une table contenant 50 Mo de données, appelée ibdata1 et un fichier 50 Mo auto-croissant, appelé ibdata2 dans le dossier de données est configuré comme ceci :

[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

La syntaxe complète de la spécification de fichier de données inclut le nom du fichier, sa taille, et différents attributs :

file_name:file_size[:autoextend[:max:max_file_size]]

L'attribut autoextend et ceux qui le suivent peuvent être utilisés uniquement pour le dernier fichier de données de la ligne innodb_data_file_path. autoextend est disponible depuis MySQL 3.23.50 et 4.0.2.

Si vous spécifiez le dernier fichier avec l'option autoextend, InnoDB va augmenter la taille du dernier fichier de données jusqu'à ce qu'il n'y ait plus de place dans l'espace de table. Les incréments se feront par bloc de 8 Mo.

Si le disque est plein, vous aurez à ajouter un autre fichier sur un autre disque. Les informations pour reconfigurer une table existante sont données dans la section Section 16.8, « Ajouter et retirer des données et des logs InnoDB ».

InnoDB ne connaît pas la taille maximale des fichiers sur votre système : il faut donc être prudent lorsque la taille des fichiers ne peut dépasser 2 Go. Pour spécifier la taille maximale des fichiers auto-croissant, utilisez l'attribut max. La ligne de configuration suivante permet au fichier ibdata1 de croître jusqu'à 500 Mo :

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend:max:500M

InnoDB crée les fichiers de données dans le dossier de données de MySQL. Pour spécifier explicitement un dossier, utilisez l'option innodb_data_home_dir. Par exemple, pour créer deux fichiers appelés ibdata1 et ibdata2 mais pour les placer dans le dossier /ibdata, configurez InnoDB comme ceci :

[mysqld]
innodb_data_home_dir = /ibdata
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

Note : InnoDB ne crée pas les dossiers : assurez vous que /ibdata existe avant de lancer le serveur. C'est aussi vrai pour les fichiers de log. Utilisez la commande Unix et DOS mkdir pour créer les dossiers nécessaires.

InnoDB forme le chemin de chaque fichier en concaténant textuellement la valeur de innodb_data_home_dir devant le nom du fichier, en ajoutant un slash si nécessaire. Si l'option innodb_data_home_dir n'est pas mentionnée dans my.cnf, la valeur par défaut est le dossier ``point'' ./, c'est à dire le dossier de données de MySQL.

Si vous spécifier l'option innodb_data_home_dir sous forme de chaîne vide, vous pouvez spécifier des noms de chemins absolus dans la valeur de innodb_data_file_path. L'exemple ci-dessous est équivalent au précédent :

[mysqld]
innodb_data_home_dir =
innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend

Exemple de fichier my.cnf simple Supposons que vous avez un serveur avec 128 Mo de RAM et un disque dur. Voici un exemple de configuration de fichier my.cnf ou my.ini pour InnoDB. Nous supposons que vous exécutez MySQL-Max-3.23.50 ou plus récent, ou MySQL-4.0.2 ou plus récent, qui utilisent l'attribut autoextend.

Cet exemple devrait convenir à une majorité d'utilisateurs, Unix et Windows, qui ne souhaitent pas répartir leur fichiers de données InnoDB et leurs logs sur plusieurs disques. Cette configuration crée un fichier de données auto-croissant, appelé ibdata1 et deux fichiers de log InnoDB ib_logfile0 et ib_logfile1 dans le dossier de données MySQL. De plus, le petit fichier d'archive InnoDB ib_arch_log_0000000000 sera placé dans datadir.

[mysqld]
# Vous pouvez placer d'autres options MYSQL ici
# ...
# Le fichier de données doit contenir vos données et index.
# Assurez vous que vous avez l'espace disque nécessaire.
innodb_data_file_path = ibdata1:10M:autoextend
#
# Utilisez un buffer de taille 50 à 80 % de votre mémoire serveur
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
#
# Utiisez un fichier de log de taille 25 % du buffer mémoire
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1

Vérifiez que le serveur MySQL a les droits de créer ces fichiers dans le datadir. Plus généralement, le serveur doit avoir les droits d'accès dans les dossiers où il va créer des fichiers, de données ou de log.

Notez que le fichier de données doit être inférieure à 2 Go sur certains systèmes d'exploitation. La taille combinée des fichiers de log doit être inférieure à 4 Go. La taille combinée des fichiers de données doit être inférieure à 10 Go.

Lorsque vous créez pour la première fois une base de données InnoDB, il est mieux de lancer le serveur depuis la commande en ligne. InnoDB va afficher des informations sur la création de la base, et vous verrez commence ¸a se passe. Voyez la section plus bas, pour une illustration. Par exemple, sous Windows, vous pouvez démarrer mysqld-max.exe, qui est stocké dans C:\mysql\bin, vous pouvez le démarrer comme ceci :

C:\> C:\mysql\bin\mysqld-max --console

Si vous n'envoyez par de données sur l'écran, vérifiez le fichier de log pour savoir ce que InnoDB a indiqué durant le lancement.

Voyez Section 16.6, « Créer des bases InnoDB » pour un exemple des informations affichées par InnoDB.

Où mettre le fichier d'options sous Windows? Les règles sous Windows sont les suivantes :

Où placer les fichiers d'options sous Unix? Sous Unix, mysqld lit les options dans les fichiers suivants, s'ils existent, et dans cet ordre :

COMPILATION_DATADIR est le dossier de données de MySQL qui a été spécifié lors de l'utilisation du script ./configure, avant la compilation de mysqld. (typiquement, /usr/local/mysql/data pour une installation binaire, ou /usr/local/var pour une installation source).

Si vous voulez vous assurer que mysqld lit les options uniquement depuis un fichier spécifique, vous pouvez utiliser l'option --defaults-option comme première option de ligne de commande, au démarrage du serveur :

mysqld --defaults-file=your_path_to_my_cnf

Exemple de fichier my.cnf complexe : supposons que vous avez un serveur Linux avec 2 Go de RAM et trois disques de 60 Go (situés dans les dossiers /, /dr2 et /dr3. Voici ci-dessous un exemple de configuration possible pour my.cnf, de InnoDB.

[mysqld]
# Vous pouvez placer d'autres options MYSQL ici
# ...
innodb_data_home_dir =
#
# Le fichier de données doivent contenir vos données et index.
innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend
#
# Utilisez un buffer de taille 50 à 80 % de votre mémoire serveur
# mais assurez vous sous Linux que l'utilisation totale est inférieure à 2 Go
set-variable = innodb_buffer_pool_size=1G
set-variable = innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs
#
# innodb_log_arch_dir doit être le même que  innodb_log_group_home_dir
# (starting from 4.0.6, you can omit it)
innodb_log_arch_dir = /dr3/iblogs
set-variable = innodb_log_files_in_group=2
#
# Utilisez un fichier de log de taille 15 % du buffer mémoire
set-variable = innodb_log_file_size=250M
set-variable = innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
#
# Décommentez les prochaines lignes, si vous voulez les utiliser
#innodb_flush_method=fdatasync
#set-variable = innodb_thread_concurrency=5

Notez que nous avons placé deux fichier de données sur des disques différents. InnoDB va remplir l'espace de tables jusqu'au maximum. Dans certains cas, les performances seront améliorées si les données ne sont pas toutes placées sur le même disque physique. Placer les fichiers de log dans des disques séparés est souvent une bonne chose. Vous pouvez aussi utiliser des partitions de disques brutes (raw devices) comme fichier de données. See Section 16.15.2, « Utiliser les raw devices pour l'espace de tables ».

Attention : en Linux x86, vous devez être très prudent, et ne pas utiliser trop de mémoire. glibc va autoriser les processus à dépasser la pile de thread, et votre système va planter. Cela représente un risque réel si la valeur de 2 Go :

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB

Chaque thread va utiliser une pile (souvent 2 Mo, mais les exécutables MySQL uniquement 256 ko) et dans le pire des scénarios, sort_buffer + read_buffer_size de mémoire supplémentaire.

Depuis MySQL 4.1, vous pouvez utiliser 64 Go de mémoire physique sur Windows 32 bits. Voyez la description de innodb_buffer_pool_awe_mem_mb dans Section 16.5, « Options de démarrage InnoDB ».

Comment optimiser d'autres paramètres du serveur mysqld? Les valeurs qui conviennent à la majorité des utilisateurs sont :

[mysqld]
skip-external-locking
set-variable = max_connections=200
set-variable = read_buffer_size=1M
set-variable = sort_buffer_size=1M
# key_buffer vaut de 5 à 50%
# de la RAM disponible, suivant l'utilisation des
# tables MyISAM, mais gardez
# key_buffer + InnoDB en de¸a de < 80% de votre RAM
set-variable = key_buffer_size=...