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