Présentation des tables InnoDB
<<<		 Options de démarrage InnoDB Créer des bases InnoDB
>>>
7.5 Tables InnoDB
7 Types de tables MySQL
 Manuel de Référence MySQL 4.1 : Version Française
. Présentation des tables InnoDB
->Options de démarrage InnoDB
. Créer des bases InnoDB
. Créer des tables InnoDB
. Ajouter et retirer des données et des logs InnoDB
. Sauver et restaurer une base InnoDB
. Transférer une base de données InnoDB vers une autre machine
. Modèle transactionnel de InnoDB
. Implémentation du multi-versionnage
. Structures de tables et d'index
. Gestion de l'espace fichiers et des entrées/sorties disque
. Gestion des erreurs
. Restrictions sur les tables InnoDB
. Historique de l'évolution InnoDB
. Informations de contact InnoDB

7.5.2 Options de démarrage InnoDB

Pour utiliser les tables InnoDB en MySQL-Max-3.23, vous devez spécifier des paramètres de configuration dans la section [mysqld] du fichier de configuration my.cnf , ou optionnelle, dans le fichier my.ini sous Windows.

Au minimum, en 3.23, vous devez spécifier innodb_data_file_path pour spécifier les noms et tailles de fichiers de données. Si vous décidez de ne pas mentionner innodb_data_home_dir dans my.cnf , le comportement par défaut est de créer ces fichiers dans le dossier de données de MySQL. Si vous ne spécifiez pas innodb_data_home_dir sous la forme d'une chaîne vide, vous pouvez donner un chemin absolu jusqu'au stockage de vos données dans innodb_data_file_path . En MySQL 4.0, vous n'avez même pas à spécifier l'option innodb_data_file_path : le comportement par défaut est de créer un fichier de données auto-croissant de 10 Mo appelé ibdata1 dans le dossier de données de MySQL. En MySQL 4.0.0 et 4.0.1, le fichier de données était de 64 Mo et de taille fixe.

Si vous ne voulez pas utiliser les tables InnoDB , vous pouvez ajouter l'option skip-innodb dans le fichier d'options de MySQL.

Mais pour obtenir de bonnes performances, vous devez explicitement choisir les paramètres InnoDB listés dans les exemples suivants : Depuis les versions 3.23.50 et 4.0.2, InnoDB fait que le dernier fichier spécifié dans l'option innodb_data_file_path peut être auto-croissant ( auto-extending ). La syntaxe pour la ligne innodb_data_file_path est alors la suivante : 

pathtodatafile:sizespecification;pathtodatafile:sizespecification;...
...  ;pathtodatafile:sizespecification[:autoextend[:max:sizespecification]]
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. Par exemple : 

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend
indique à InnoDB de créer un fichier de données unique, de taille initiale de 100 Mo et qui sera agrandi de 8Mo jusqu'à ce qu'il n'y ait plus de place. Si le disque se remplit, vous placerez le prochain fichier de données sur un autre disque. Vous devez alors regarder la taille du fichier de données ibdata1 , arrondir sa taille au Mo précédent (multiple de 1024 * 1024 octets (= 1 Mo)) et spécifier la taille du fichier ibdata1 explicitement dans l'option innodb_data_file_path .Après cela, vous pouvez spécifier un autre fichier de données : 

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
Soyez prudents avec les systèmes de fichiers où la taille maximale de fichier est de 2 Go! InnoDB n'est pas capable de détecter la taille maximale de fichier pour votre système d'exploitation. Sur d'autres systèmes, vous devrez spécifier la taille maximale du fichier : 

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M
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.

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 (typiquement /mysql/data ). 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 doivent 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
#                                  Utilisez  ..flush_log_at_trx_commit
#                                  à 0 si vous pouvez accepter de perdre
#                                  quelques transactions 
innodb_flush_log_at_trx_commit=1
Vérifiez que le serveur MySQL a les droits de créer ces fichiers dans le datadir .

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

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

chemin-jusqu-a-mysqld>mysqld-max --console
Oz mettre le fichier my.cnf ou my.ini sous Windows? Les règles sous Windows sont les suivantes :
  • Un seul des deux fichiers my.cnf ou my.ini doit être créé.
  • The my.cnf doit être placé dans le dossier racine du disque C: .
  • Le fichier my.ini doit être placé dans le dossier WINDIR , e.g, C:\WINDOWS ou C:\WINNT . Vous pouvez utiliser la commande SET de MS-DOS pour afficher la valeur de WINDIR .
  • Si votre PC utilise un gestionnaire de démarrage où le C: n'est pas votre disque de démarrage, alors votre seule option est d'utilise le fichier my.ini .
Oz placer les fichiers d'options sous Unix? Sous Unix, mysqld lit les options dans les fichiers suivants, si ils existent, et dans cet ordre :
  • /etc/my.cnf Options globales.
  • COMPILATION_DATADIR/my.cnf Options spécifiques au serveur.
  • defaults-extra-file Le fichier spécifié avec --defaults-extra-file=... .
  • ~/.my.cnf User-specific options.
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 n'êtes pas sûr des chemins où mysqld lit les données my.cnf et my.ini , vous pouvez indiquer le chemin, avec la première option du serveur : mysqld --defaults-file=your_path_to_my_cnf .

InnoDB forme le chemin de dossier en concaténant innodb_data_home_dir avec les noms de fichiers, ou le chemin de innodb_data_file_path , en ajoutant les slash nécessaires. Si le mot clé innodb_data_home_dir n'est pas mentionné dans my.cnf , la valeur par défaut est 'point' ./ , ce qui signifie le dossier de données de MySQL.

Exemple de fichier my.cnf avancé 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.

Notez que InnoDB ne crée pas de dossier : vous devez le créer vous même. Utilisez la commande Unix ou MS-DOS mkdir pour créer les répertoires de données et de logs. 


[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.
#                                  Assurez vous que vous avez l'espace disque
#                                  nécessaire.
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 à 2Go
set-variable = innodb_buffer_pool_size=1G
set-variable = innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs
#                                  .._log_arch_dir doit être le même que 
#                                  .._log_group_home_dir
innodb_log_arch_dir = /dr3/iblogs
set-variable = innodb_log_files_in_group=3
#                                  Utiisez un fichier de log de taille
#                                  15 % du buffer mémoire
set-variable = innodb_log_file_size=150M
set-variable = innodb_log_buffer_size=8M
#                                  Utilisez  ..flush_log_at_trx_commit
#                                  à 0 si vous pouvez accepter de perdre
#                                  quelques transactions 
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
#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. Sur certains Unix, cela va accélérer les accès disques. Voyez le manuel de InnoDB, pour savoir comment les spécifier dans my.cnf .

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 crasher. Cela représente un risque réel si la valeur de 


innodb_buffer_pool_size + key_buffer +
max_connections * (sort_buffer + read_buffer_size) + max_connections * 2 MB
est proche de 2 Go ou excède 2 Go. Chaque thread va utiliser une pile (souvent 2Mo, 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. Comment optimiser d'autres paramètres du serveur mysqld ? Les valeurs qui conviennent à la majorité des utilisateurs sont : 

skip-locking
set-variable = max_connections=200
set-variable = read_buffer_size=1M
set-variable = sort_buffer=1M
#                                  key_buffer vaut de 5 à 50%
#                                  de la RAM disponible, suivant l'utilisation des
#                                  tables MyISAM, mais garder
#                                  key_buffer + InnoDB
#                                  en deça de < 80% de votre RAM
set-variable = key_buffer=...
Notez que certains paramètres sont données au format numérique dans my.cnf : set-variable = innodb... = 123 , d'autres (chaînes et booléens) sont données dans un autre format : innodb_... = ... . Les significations des paramètres de configuration sont les suivantes :
Option Description
innodb_data_home_dir La partie commune du chemin de tous les fichiers de données InnoDB. Si vous ne mentionnez pas cette option dans my.cnf , la valeur par défaut sera celle du dossier de données MySQL. Vous pouvez aussi spécifier une chaîne vide, et dans ce cas, les chemins spécifiés dans innodb_data_file_path seront des chemins absolus.
innodb_data_file_path Chemin individuel vers les fichiers de données, et leur taill. Le chemin complet de chaque fichier de données est créé en concaténant innodb_data_home_dir avec les chemins spécifiés ici. La taille du fichier est spécifiée en méga-octets, ce qui explique la présence du 'M' après les spécifications ci-dessus. Depuis la version 3.23.44, vous pouvez donner au fichier une taille supérieure à 4 Go sur les systèmes d'exploitation qui acceptent les gros fichiers. Sur certains systèmes, la taille doit être inférieure à 2 Go. La somme des tailles des fichiers doit faire au moins 10 Mo.
innodb_mirrored_log_groups Nombre de copies identiques de groupe de log que nous conservons. Actuellement, cette valeur doit être au minimum de 1.
innodb_log_group_home_dir Le dossier pour les fichiers de logs.
innodb_log_files_in_group Nombre de fichier de logs dans un groupe. InnoDB écrit les logs de manière circulaire. Une valeur de 3 est recommandée ici.
innodb_log_file_size Taille de chaque fichier de log dans un groupe de log, exprimé en méga-octets. Les valeurs pratiques vont de 1Mo à une fraction de la taille du buffer de log (1 / le nombre de logs, en fait). Plus la taille est grande, moins de points de contrôles seront utilisés, réduisant les accès disques. La taille combinée des logs doit être inférieure à 4 Go sur les systèmes 32 bits.
innodb_log_buffer_size La taille du buffer que InnoDB utilise pour écrire les log dans les fichiers de logs, sur le disque. Les valeurs utiles vont de 1 Mo à 8 Mo. Un grand buffer de log permet aux grandes transactions de s'exécuter sans avoir à écrire de données dans le fichier de log jusqu'à la validation. Par conséquent, si vous avez de grandes transactions, augmenter cette taille va réduire les accès disques.
innodb_flush_log_at_trx_commit Normalement, cette option vaut 1, ce qui signifie que lors de la validation de la transaction, les logs sont écrits sur le disque, et les modifications faites par la transaction deviennent permanentes, et survivront un crash de base. Si vous souhaitez réduire la sécurité de vos données, et que vous exécutez de petites transactions, vous pouvez donner une valeur de 0 à cette option, pour réduire les accès disques.
innodb_log_arch_dir Le dossier où les logs complétés doivent être archivés, si nous utilisons l'archivage de logs. La valeur de ce paramètre doit être actuellement la même que la valeur de innodb_log_group_home_dir .
innodb_log_archive Cette valeur doit être actuellement de 0. Au moment de la restauration de données à partir d'une sauvegarde, à l'aide des log binaires de MySQL, il n'y a actuellement pas besoin d'archiver les fichiers de log InnoDB.
innodb_buffer_pool_size La taille de buffer mémoire que InnoDB utiliser pour mettre en cache les données et les index de tables. Plus cette valeur est grand, et moins vous ferez d'accès disques. Sur un serveur dédiés, vous pouvez monter cette valeur jusqu'à 80% de la mémoire physique de la machine. Ne lui donnez pas une valeur trop grande, car cela peut engendrer l'utilisation de mémoire sur le disque par votre serveur.
innodb_additional_mem_pool_size La taille du buffer mémoire d'InnoDB, pour ses dictionnaires d'informations, et ses structures internes de données. Une valeur pratique est 2Mo, mais plus vous aurez de tables dans votre application, plus vous devrez augmenter cette valeur. Si InnoDB est à court de mémoire, il va allouer de la mémoire auprès du système, et écrire des messages dans le fichier de logs MySQL.
innodb_file_io_threads Nombre de pointeurs de fichier de InnoDB. Normalement, cette valeur doit être de 4, mais sur des disques Windows, les accès peuvent être améliorés en augmentant cette valeur.
innodb_lock_wait_timeout Le délai d'expiration des transactions InnoDB, en cas de blocage de verrou, avant d'annuler. InnoDB détecte automatiquement les blocages de verrous et annule alors les transactions. Si vous utilisez la commande LOCK TABLES , ou un autre gestionnaire de table transactionnelles que InnoDB dans la même transaction, un blocage de verrou peut survenir, et InnoDB ne pourra pas le détecter. Ce délai est donc pratique pour résoudre ces situations.
innodb_flush_method (Disponible depuis 3.23.40 et plus récent) La valeur par défaut pour cette option est fdatasync . Une autre option est O_DSYNC .

<< Options de démarrage InnoDB >>
Présentation des tables InnoDB Tables InnoDB Créer des bases InnoDB