Chapitre 15 Le moteur de tables InnoDB

Table des matières

15.1 Présentation des tables InnoDB
15.2 Informations de contact InnoDB
15.3 InnoDB avec MySQL version 3.23
15.4 Configuration InnoDB
15.5 Options de démarrage InnoDB
15.6 Créer des bases InnoDB
15.6.1 Si quelque chose se passe mal à la création de la base de données
15.7 Créer des tables InnoDB
15.7.1 Comment utiliser les transactions de InnoDB avec différentes API
15.7.2 Convertir des tables MyISAM vers InnoDB
15.7.3 Comment les colonnes AUTO_INCREMENT fonctionnent avec InnoDB
15.7.4 Contraintes de clés étrangères FOREIGN KEY
15.7.5 InnoDB et la réplication MySQL
15.7.6 Espaces de tables multiples : chaque table InnoDB a son fichier .ibd
15.8 Ajouter et retirer des données et des logs InnoDB
15.9 Sauver et restaurer une base InnoDB
15.9.1 Forcer la restauration
15.9.2 Points de contrôle
15.10 Transférer une base de données InnoDB vers une autre machine
15.11 Modèle de transactions et verrouillage InnoDB
15.11.1 InnoDB et AUTOCOMMIT
15.11.2 InnoDB et SET ... TRANSACTION ISOLATION LEVEL ...
15.11.3 Lecture cohérente non-bloquante
15.11.4 Verrous de lecture SELECT ... FOR UPDATE et SELECT ... LOCK IN SHARE MODE
15.11.5 Verrou de clé suivante : éviter le problème des lignes fantômes
15.11.6 Un exemple de lecture cohérente avec InnoDB
15.11.7 Les verrous posés par différentes requêtes SQL avec InnoDB
15.11.8 Quand est-ce que MySQL valide ou annule implicitement une transaction?
15.11.9 Détection des blocages et annulation
15.11.10 Comment gérer les blocages de verrous?
15.12 Conseils pour l'amélioration des performances InnoDB
15.12.1 Le moniteur InnoDB
15.13 Implémentation du multi-versionnage
15.14 Structures de tables et d'index
15.14.1 Structure physique d'un index
15.14.2 Bufferisation des insertions
15.14.3 Index hash adaptatifs
15.14.4 Structure physique d'une ligne
15.15 Gestion de l'espace fichiers et des entrées/sorties disque
15.15.1 Accès disques
15.15.2 Utiliser les raw devices pour l'espace de tables
15.15.3 Gestion de l'espace fichier
15.15.4 Défragmentation des tables
15.16 Gestion des erreurs InnoDB
15.16.1 Codes d'erreurs InnoDB
15.16.2 Codes d'erreur sytème
15.17 Restrictions sur les tables InnoDB
15.18 Résolution de problèmes avec InnoDB
15.18.1 Solutions pour le dictionnaire de données InnoDB

15.1 Présentation des tables InnoDB

InnoDB fournit à MySQL un gestionnaire de table transactionnelle (compatible ACID), avec validation (commits), annulations (rollback) et capacités de restauration après crash. InnoDB utilise un verrouillage de lignes, et fournit des lectures cohérentes comme Oracle, sans verrous. Ces fonctionnalités accroissent les possibilités d'utilisation simultanées des tables, et les performances. Il n'y a pas de problème de queue de verrous avec InnoDB, car les verrous de lignes utilisent très peu de place. Les tables InnoDB sont les premières tables MySQL qui supportent les contraintes de clés étrangères (FOREIGN KEY).

InnoDB a été con¸u pour maximiser les performances lors du traitement de grandes quantités de données. Son efficacité processeur n'est égalée par aucun autre moteur de base de données.

Techniquement, InnoDB est un gestionnaire de table placé sous MySQL. InnoDB dispose de son propre buffer pour mettre en cache les données et les index en mémoire centrale. InnoDB stocke les tables et index dans un espace de table, qui peut être réparti dans plusieurs fichiers. Ceci diffère des tables comme, par exemple, MyISAM où chaque table est stockée dans un fichier différent. Les tables InnoDB peuvent prendre n'importe quelle taille, même sur les systèmes d'exploitation dont la limite est de 2 Go par fichier.

InnoDB est inclus dans les distributions binaires par défaut depuis MySQL 4.0. Pour des informations sur le support InnoDB en MySQL 3.23, voyez la section Section 15.3, « InnoDB avec MySQL version 3.23 ».

InnoDB est utilisé en production dans plusieurs sites où de grandes capacités de stockages et des performances accrues sont nécessaires. Le fameux site web Slashdot.org utilise InnoDB. Mytrix, Inc. stocke plus de 1 To de données dans une base InnoDB, et un autre site gère une moyenne de 800 insertions/modifications par secondes avec InnoDB.

InnoDB est sous licence GNU GPL License Version 2 (de Juin 1991). Si vous distribuez MySQL et InnoDB, et que votre application ne satisfait pas les restrictions de la licence GPL, vous devez acheter une licence commerciale MySQL Pro sur https://order.mysql.com/?sub=pg&pg_no=1.

15.2 Informations de contact InnoDB

Informations de contact de Innobase Oy, producteur de InnoDB. Site web : http://www.innodb.com/. Courrier électronique :

phone: 358-9-6969 3250 (bureau) 358-40-5617367 (portable)
Innobase Oy Inc.
World Trade Center Helsinki
Aleksanterinkatu 17
P.O.Box 800
00101 Helsinki
Finland

15.3 InnoDB avec MySQL version 3.23

Depuis MySQL version 4.0, InnoDB est activé par défaut. Les informations suivantes ne s'appliquent qu'à la série 3.23.

Les tables InnoDB sont inclues dans la distribution source de MySQL depuis la version 3.23.34a et sont activées dans les exécutables MySQL-Max de la série 3.23. Pour Windows, les exécutables MySQL-Max sont inclus dans la distribution standard.

Si vous avez téléchargé la version binaire de MySQL qui inclut le support d'InnoDB, suivez simplement les instructions du manuel MySQL pour installer la version binaire de MySQL. Si vous avez déjà MySQL-3.23 d'installé, le plus simple pour installer MySQL-Max est de remplacer l'exécutable serveur mysqld avec l'exécutable correspondant de la distribution MySQL-Max. MySQL et MySQL-Max ne diffèrent qu'au niveau de ce programme. Voyez Section 2.3, « Installer MySQL sur d'autres systèmes type Linux » et Section 5.1.2, « mysqld-max, la version étendue du serveur mysqld ».

Pour compiler MySQL avec le support InnoDB, téléchargez MySQL MySQL-3.23.34a ou plus récent sur le site de http://www.mysql.com/ et configurez MySQL avec l'option --with-innodb. Voyez le manuel MySQL pour les instructions d'installation de MySQL. See Section 2.4, « Installation de MySQL avec une distribution source ».

Pour utiliser les tables InnoDB dans MySQL-Max version 3.23 vous devez spécifier des paramètres dans la section [mysqld] du fichier de configuration my.cnf, ou, sous Windows, dans my.ini. Si vous ne configurez pas InnoDB dans le fichier d'options, InnoDB ne démarrera pas. Depuis MySQL 4.0, InnoDB utilise des valeurs par défaut pour ces paramètres si vous n'en spécifiez pas. Cependant, il est recommandé d'utiliser des valeurs appropriées pour votre système afin d'obtenir performances valables. Le paramétrage est détaillé dans la section Section 15.4, « Configuration InnoDB ».)

Au minimum, en version 3.23, vous devez spécifier avec innodb_data_file_path où seront les fichiers de données, et les tailles de fichiers. Si vous ne mentionnez pas innodb_data_home_dir dans my.cnf, le comportement par défaut est de créer ces fichiers dans le dossier de données datadir de MySQL. Si vous spécifiez innodb_data_home_dir comme une chaîne vide, vous pouvez donner des chemins absolus à vos fichiers de données dans innodb_data_file_path.

Le minimum est de modifier la section [mysqld] à la ligne

innodb_data_file_path=ibdata:30M

Mais pour obtenir de bonnes performances, il est recommandé de spécifier des options supplémentaires. See Section 15.5, « Options de démarrage InnoDB ».

15.4 Configuration InnoDB

Pour utiliser les tables InnoDB en MySQL versions 3.23, voyez Section 15.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 15.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
#
# Utilisez 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 15.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 :

  • Un seul des deux fichiers my.cnf ou my.ini doit être créé.

  • Le fichier 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 :

    C:\> SET WINDIR
    windir=C:\WINNT
    
  • 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.

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 :

  • /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 Options spécifiques à l'utilisateur.

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 15.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 15.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=...

15.5 Options de démarrage InnoDB

Cette section décrit les options d'InnoDB. Depuis MySQL 4.0, toutes les options peuvent être spécifiées sous la forme --opt_name=value en ligne de commande, ou dans les fichiers d'options. Avant MySQL 4.0, les options numériques devaient être spécifiée avec --set-variable=opt_name=value ou la syntaxe -O opt_name=value.

  • 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_buffer_pool_awe_mem_mb

    Taille du pool de buffer, en Mo, s'il est placé dans la mémoire AWE de Windows 32 bits. Disponible depuis MySQL version 4.1.0, et uniquement utile sur Windows 32 bits. Si votre système Windows 32 bits supporte plus de 4 Go de mémoire, dite aussi Address Windowing Extensions, vous pouvez allouer le pool de buffer InnoDB dans la mémoire physique AWE en utilisant ce paramètre. La valeur maximal est de 64000. Si ce paramètre est spécifié, alors innodb_buffer_pool_size est la fenêtre dans l'espace d'adresse 32 bits de mysqld où InnoDB place la mémoire AWE. Une bonne valeur pour innodb_buffer_pool_size est alors 500M.

  • 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_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. Si vous ne spécifiez pas innodb_data_file_path, le comportement par défaut depuis la version 4.0 est de créer un fichier auto-croissant de 10 Mo, appelé ibdata1. Depuis la version 3.23.44, vous pouvez donner une taille de fichier de plus de 4Go sur les systèmes d'exploitation qui supportent les grands fichiers. Vous pouvez aussi utiliser les partition raw. See Section 15.15.2, « Utiliser les raw devices pour l'espace de tables ».

  • innodb_data_home_dir

    La partie commune du chemin de tous les fichiers de données InnoDB. Si vous ne mentionnez pas cette option, 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_fast_shutdown

    Par défaut, InnoDB fait une purge complète et vide le buffer d'insertion avant une extinction. Ces opérations peuvent prendre beaucoup de temps. Si vous donnez à ce paramètre la valeur de 1, InnoDB ignore ces opérations d'extinction. Cette option est valable depuis MySQL 3.23.44 et 4.0.1. Sa valeur par défaut est 1 depuis la version 3.23.50.

  • 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_file_per_table

    Cette option fait que InnoDB va stocker chaque table dans un fichier .ibd indépendant. Voyez la section sur les espaces de tables multiples.See Section 15.7.6, « Espaces de tables multiples : chaque table InnoDB a son fichier .ibd ». Cette option a été ajoutée en MySQL 4.1.1.

  • 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_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.

  • innodb_force_recovery

    Attention : cette option ne doit être définie que dans les cas où vous voulez exporter les données d'une base corrompue, dans une situation d'urgence. Les valeurs possibles de cette option vont de 1 à 6. La signification des valeurs est décrite dans Section 15.9.1, « Forcer la restauration ». Par mesure de sécurité, InnoDB empêche les modifications de données si la valeur de cette option est supérieure à 0. Cette option est disponible depuis MySQL 3.23.44.

  • 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_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_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_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_files_in_group

    Nombre de fichiers de logs dans le groupe de log. InnoDB écrit dans ces fichiers de manière circulaire. Une valeur de 2 est recommandée. C'est la valeur par défaut.

  • innodb_log_group_home_dir

    Le dossier pour les fichiers de logs. Il doit avoir la même valeur que innodb_log_arch_dir. Si vous ne spécifiez pas de paramètre de log InnoDB, la configuration par défaut va créer deux fichiers de logs de 5 Mo, appelés ib_logfile0 et ib_logfile1 dans le dossier de données MySQL.

  • innodb_max_dirty_pages_pct

    Cette entier va de 0 à 100. Par défaut, il vaut 90. Le thread principal de InnoDB essaie de transmettre les pages au pool de buffer, pour qu'un pourcentage maximal de innodb_max_dirty_pages_pct soit encore en attente de flush. Cette option est disponible depuis 4.0.13 et 4.1.1. Si vous avez le droit de SUPER, ce pourcentage peut être changée durant l'exécution du serveur :

    SET GLOBAL innodb_max_dirty_pages_pct = value;
    
  • 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_open_files

    Ce n'est utile que si vous utilisez les espaces de tables multiples. Cette option spécifie que le nombre maximal de fichier .ibd que InnoDB peut garder ouvert simultanément. La valeur minimum est de 10. La valeur maximum est de 300. Disponible depuis MySQL version 4.1.1.

    Les pointeurs de fichiers utilisés par .ibd sont réservés pour InnoDB. Ils sont indépendants de ceux spécifiés par --open-files-limit, et n'affectent pas les opérations de cache.

  • innodb_thread_concurrency

    InnoDB essaie de garder le nombre de thread système concurents inférieur à la limite de ce paramètre. La valeur par défaut est 8. Si vous avez des problèmes de performances, et que SHOW INNODB STATUS revèle que des threads attendent des sémaphores, essayez de diminuer ou augmenter ce paramètre. Si vous avez un serveur avec de nombreux processeurs et disques, vous pouvez essayer d'augmenter la valeur, pour utiliser au mieux les ressources disponibles. Une valeur recommandée est la somme du nombre de processeurs et de disques que vous avez. Une valeur de 500 ou supérieur, supprime la vérification de concurence. Cette option a été ajoutée depuis MySQL 3.23.44 et 4.0.1.

15.6 Créer des bases InnoDB

Supposons que vous avez installé MySQL et que vous avez édité le fichier d'options de fa¸on à ce qu'il contiennent les paramètres de configuration nécessaires de InnoDB. Avant de démarrer MySQL, vous devez vérifier que les dossiers que vous avez spécifié pour les fichiers de données InnoDB et les fichiers de logs existent, et que vous avez des accès suffisants dans ces dossiers. InnoDB ne peut pas créer de dossiers, uniquement des fichiers. Vérifiez aussi que vous avez d'espace disque pour les données et les logs.

Lorsque vous démarrez MySQL, InnoDB va commencer à créer vos fichiers de données et vos fichiers de log. InnoDB va afficher ceci :

~/mysqlm/sql > mysqld
InnoDB: The first specified datafile /home/heikki/data/ibdata1
did not exist:
InnoDB: a new database to be created!
InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728
InnoDB: Database physically writes the file full: wait...
InnoDB: datafile /home/heikki/data/ibdata2 did not exist:
new to be created
InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000
InnoDB: Database physically writes the file full: wait...
InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist:
new to be created
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size to 5242880
InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist:
new to be created
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size to 5242880
InnoDB: Log file /home/heikki/data/logs/ib_logfile2 did not exist:
new to be created
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile2 size to 5242880
InnoDB: Started
mysqld: ready for connections

Une nouvelle base de données InnoDB a été créée. Vous pouvez vous connecter au serveur MySQL avec votre client MySQL habituel, comme mysql. Lorsque vous arrêtez le serveur MySQL avec mysqladmin shutdown, InnoDB va afficher :

010321 18:33:34  mysqld: Normal shutdown
010321 18:33:34  mysqld: Shutdown Complete
InnoDB: Starting shutdown...
InnoDB: Shutdown completed

Vous pouvez observer vos fichiers de données et de logs, et vous apercevrez les fichiers créés. Le dossier de log va aussi contenir un petit fichier appelé ib_arch_log_0000000000. Ce fichier est le résultat de la création de base, à partir duquel InnoDB a désactivé l'archivage des logs. Lorsque MySQL va être redémarré, l'affichage sera :

~/mysqlm/sql > mysqld
InnoDB: Started
mysqld: ready for connections

15.6.1 Si quelque chose se passe mal à la création de la base de données

Si InnoDB renvoie une erreur de système d'exploitation lors d'une opération sur fichier, habituellement le problème est l'un des suivants :

  • Vous n'avez pas crée les dossiers de données ou de logs InnoDB.

  • mysqld n'a pas le droit de créer des fichiers dans ces dossiers.

  • mysqld ne lit pas le bon fichier my.cnf ou my.ini, et donc ne voit pas les options que vous spécifiez.

  • Le disque ou l'espace disque alloué est plein.

  • Vous avez crée un sous-dossier dont le nom est le même que celui d'un fichier de données que vous avez spécifié.

  • Il y a une erreur de syntaxe dans innodb_data_home_dir ou innodb_data_file_path.

Si quelque chose se passe mal lors de la création d'une base de données InnoDB, vous devez effacer tous les fichiers créés par InnoDB. Cela inclut tous les fichiers de données, tous les journaux, les archives. Dans le cas où vous avez déjà crées des tables InnoDB, effacez aussi les fichiers .frm (et tous les fichiers .ibd si vous utilisez les espaces de tables multiples) concernés dans le dossier de données de MySQL. Vous pourrez alors essayer une nouvelle création de base de données InnoDB.

15.7 Créer des tables InnoDB

Supposons que vous avez démarré le client MySQL avec la commande mysql test. Pour créer une table au format InnoDB vous devez spécifier le type TYPE = InnoDB lors de la création de table, dans la commande SQL :

CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB;

Cette commande SQL va créer une table et un index sur la colonne A dans la base InnoDB constituée par les fichiers de données que vous avez spécifié dans le fichier de configuration my.cnf. De plus, MySQL va créer un fichier CUSTOMER.frm dans le dossier de données de MySQL test. En interne, InnoDB va ajouter une entrée dans son propre dictionnaire de données une entrée pour la table 'test/CUSTOMER'. De cette fa¸on, vous pouvez créer plusieurs table avec le même nom de CUSTOMER, mais dans d'autres bases MySQL, et les noms de seront pas en conflit avec InnoDB.

Vous pouvez demander la quantité d'espace disponible dans l'espace de tables InnoDB avec la commande de statut de MySQL pour toutes les tables de type TYPE = InnoDB. La quantité d'espace disponible apparaît dans la section de commentaire de la commande SHOW. Par exemple :

SHOW TABLE STATUS FROM test LIKE 'CUSTOMER'

Notez que les statistiques que SHOW vous donne sur les tables InnoDB ne sont que des approximations : elles sont utilisées pour les optimisations SQL par MySQL. Les tailles réservées d'index et de table, exprimées en octets, sont précises.

15.7.1 Comment utiliser les transactions de InnoDB avec différentes API

Par défaut, chaque client qui se connecte à MySQL commence avec le mode d'auto-validation activé, ce qui valide automatiquement toutes les requêtes que vous soumettez. Pour utiliser des requêtes multi-commandes, vous pouvez désactiver l'auto-validation avec la commande SET AUTOCOMMIT = 0 et utiliser les commandes COMMIT et ROLLBACK pour valider ou annuler vos transactions. Si vous voulez laisser le mode d'auto-validation tranquille, vous pouvez placer vos commandes entre START TRANSACTION et COMMIT ou ROLLBACK. Avant MySQL 4.0.11, vous deviez utiliser la commande BEGIN au lieu de START TRANSACTION. L'exemple suivant montre deux transactions. La première est valideé, et la seconde est annulée.

shell> mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.50-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
    -> TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A    | B      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>

Avec des interfaces comme PHP, Perl DBI/DBD, JDBC, ODBC ou l'interface C, vous pouvez envoyer des commandes de contrôle de transaction comme COMMIT au serveur sous la forme de chaîne, tout comme une autre commande SELECT et INSERT. Certaines interfaces proposent des fonctions spécifiques pour les validations et annulations de transactions.

15.7.2 Convertir des tables MyISAM vers InnoDB

Important : vous ne devez pas convertir les tables de la base mysql, telles que user ou host) en type InnoDB. Ces tables doivent conserver le moteur MyISAM.

Si vous voulez que toutes les tables non-système soient crées directement en MySQL, depuis MySQL 3.23.43, ajoutez la ligne default-table-type=innodb dans la section [mysqld] de votre fichier my.cnf ou my.ini.

InnoDB n'a pas d'optimisation particulière pour séparer la création d'index. Par conséquent, cela ne sert à rien d'exporter et de réimporter les données de la table pour créer les index après. Le moyen le plus rapide pour mettre la table au format InnoDB est d'insérer directement les lignes dans une table InnoDB, c'est à dire, utiliser ALTER TABLE ... TYPE=INNODB, ou créer un table InnoDB vide, avec la même définition et de faire l'insertion de toutes les lignes avec INSERT INTO ... SELECT * FROM ....

Si vous avez une contrainte UNIQUE sur des clés secondaires, depuis MySQL 3.23.52, vous pouvez accélérer l'importation dans les tables en désactivant la vérification de cette contrainte durant l'insertion : SET UNIQUE_CHECKS=0; Pour les grosses tables, cela économise beaucoup d'accès disques car InnoDB peut alors utiliser un buffer d'insertion pour écrire les lignes par bloc.

Pour avoir un meilleur contrôle sur le processus d'insertion, il est mieux de faire les insertions des grosses tables par blocs :

INSERT INTO newtable SELECT * FROM oldtable
   WHERE yourkey > something AND yourkey <= somethingelse;

Une fois que toutes les données ont été insérées dans la table, vous pouvez la renommer.

Durant la conversion des grosses tables, vous pouvez donner à InnoDB un gros buffer pour réduire les accès disques. Ne le portez pas au-delà de 80% de votre mémoire physique. Donnez aussi de gros fichiers de logs, et un buffer de log important.

Assurez vous que vous n'allez pas manquer d'espace : les tables InnoDB prennent beaucoup plus d'espace que les tables MyISAM. Si la commande ALTER TABLE manque d'espace, elle va lancer une annulation, et cela peut prendre des heures sur un disque plein. Durant les insertions, InnoDB utiliser un buffer d'insertion pour rassembler les lignes d'index secondaires avec les index, en groupe. Cela économise beaucoup d'accès disques. Durant l'annulation, ce mécanisme n'est pas utilisé, et elle peut prendre jusqu'à 30 fois plus de temps.

Dans le cas d'un annulation immensément longue, si vous n'avez pas de données critiques dans votre base, il est mieux de tuer le processus, d'effacer tous les fichiers, et de recommencer, plutôt que d'attendre la fin de l'annulation. Pour la procédure complète, voyez Section 15.9.1, « Forcer la restauration ».

15.7.3 Comment les colonnes AUTO_INCREMENT fonctionnent avec InnoDB

Si vous spécifiez une colonne AUTO_INCREMENT dans une table, la table InnoDB va ajouter dans le dictionnaire de données un compteur spécial appelé le compteur auto-incrément, qui est utilisé pour assigner les nouvelles valeurs de la colonne. Le compteur est stocké uniquement en mémoire, et non pas sur le disque.

InnoDB utilise l'algorithme suivant pour initialiser le compteur auto-incrément pour la table T qui contient la colonne de type AUTO_INCREMENT appelée ai_col : après le démarrage du serveur, lorsqu'un utilisateur fait une insertion dans la table T, InnoDB exécute la commande suivante :

SELECT MAX(ai_col) FROM T FOR UPDATE;

La valeur lue par la commande est incrémenté d'une unité, et assignée à la colonne auto-incrément et au compteur de table. Si la table est vide, la valeur de 1 est assignée. Si le compteur n'est pas initialisé et que l'utilisateur appelle la commande SHOW TABLE STATUS qui affiche les informations de la table T, le compteur est initialisé mais pas incrémenté, et stocké pour être utilisé ultérieurement. Notez que dant cette initialisation, nous posons un verrou exclusif en lecture sur la table, et le verrou durera jusqu'à la fin de la transaction.

InnoDB suit la même procédure pour initialiser un compteur auto-increment avec une table fraîchement créée.

Notez que si l'utilisateur spécifie la valeur NULL ou 0 pour la colonne AUTO_INCREMENT dans la commande INSERT, InnoDB traitera la ligne comme si aucune valeur n'avait été spécifiée, et générera une nouvelle valeur.

Après l'initialisation du compteur d'auto-incrémentation, si un utilisateur insère une ligne qui définit explicitement la valeur de la colonne, et que cette valeur est plus grande que la valeur courante du compteur, le compteur prend alors cette valeur. Si l'utilisateur ne spécifie pas de valeur, InnoDB incrémente le compteur d'une unité, et assigne une nouvelle valeur à cette colonne.

Lorsque vous accédez au compteur d'auto-incrémentation, InnoDB utilise un verrou de table spécial, AUTO-INC, qui reste actif jusqu'à la fin de la commande SQL, et non pas la fin de la transaction. Le verrou spécial a été créé pour éviter les accès concurrents dans la table qui contient la colonne AUTO_INCREMENT. Deux transactions ne peuvent pas avoir le même verrou AUTO-INC simultanément.

Notez que vous pourriez voir des trous dans la séquence de valeur générée par AUTO_INCREMENT si vous annulez des transactions après avoir obtenu une valeur automatiquement.

Le comportement du mécanisme auto-increment n'est pas défini si vous assignez une valeur négative à la colonne, ou si cette dernière dépasse la capacité de la colonne.

15.7.4 Contraintes de clés étrangères FOREIGN KEY

Depuis la version 3.23.43b, InnoDB respecte les contraintes de clé étrangères.

La syntaxe des définitions de contraintes de clés étrangères de InnoDB est la suivante :

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

Les deux tables doivent être de type InnoDB, dans la table, il doit y avoir un INDEX où les clés étrangères sont listées comme première colonne, dans le même ordre, et dans la table référencée, il doit y avoir un INDEX où les colonnes référencées sont listées comme premières colonnes, dans le même ordre. Les préfixes d'index ne sont pas supportés pour les clés de contrainte.

InnoDB ne crée pas automatiquement les index nécessaires pour les clés étrangères : vous devez ls créer vous-même. Les index sont nécessaires pour accélérer les vérifications de contrainte, et éviter un scan de table.

Les colonnes correspondantes de la contrainte dans la table et la table de référence doivent avoir le même type, pour éviter les conversions lors des comparaisons. La taille et la présente du signe pour les entiers doit être les mêmes. La taille des chaînes doivent être les mêmes. Si vous spécifiez une action SET NULL, assurez vous que vous n'avez pas déclaré les colonnes de la table fille NOT NULL.

Si MySQL vous retourne une numéro d'erreur 1005 lors de la commande CREATE TABLE, et un message d'erreur de numéro 150, alors la création de la table a échoué à cause de la contrainte de clé étrangère, qui n'a pas été correctement formulée. Similairement, si une commande ALTER TABLE échoue et indique une erreur 150, c'est que la définition de la clé étrangère est incorrectement formulée dans la table modifiée. Depuis la version 4.0.13, vous pouvez utiliser la commande SHOW INNODB STATUS pour avoir une explication détaillée de la dernière erreur de clé étrangère InnoDB sur le serveur.

Depuis la version 3.23.50, InnoDB ne vérifie pas la clé étrangère pour les clés étrangères ou les clés référencées qui contienent des valeurs NULL.

Une entorse aux standards : si dans la table parente, il y a plusieurs lignes qui ont la même valeur de clé référencée, alors InnoDB effectue les vérifications de clé étrangères comme si les autres parents avec la même valeur de clé n'existaient pas. Par exemple, si vous avez défini une contrainte de type RESTRICT et qu'il y a une ligne fille avec plusieurs lignes parente, InnoDB n'acceptera pas l'effacement d'aucun des parents.

Depuis la version 3.23.50, vous pouvez aussi associer la clause ON DELETE CASCADE ou ON DELETE SET NULL avec la contrainte de clé étrangère. Les options correspondante ON UPDATE sont disponibles depuis la version 4.0.8. Si ON DELETE CASCADE est spécifiée, et qu'une ligne de la table parente est effacée, alors InnoDB va automatiquement effacer toute les lignes qui sont dans la table fille et dont les valeurs de clé étrangère sont celles référencées dans la ligne parente. Si ON DELETE SET NULL est spécifiée, les lignes filles sont automatiquement modifiée pour que la colonne de la clé étrangère prenne la valeur de NULL.

Une entorse aux standards : si ON UPDATE CASCADE ou ON UPDATE SET NULL cascade récursivement jusqu'à la même table, elle agira comme pour un RESTRICT. Cela est fait pour éviter les boucles infinies des modifications en cascade. Une clause ON DELETE SET NULL auto-référente, d'un autre coté, fonctionne depuis la version 4.0.13. La clause ON DELETE CASCADE auto-référente à toujours fonctionné.

Un exemle :

CREATE TABLE parent(id INT NOT NULL,
                    PRIMARY KEY (id)
) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT,
                   INDEX par_ind (parent_id),
                   FOREIGN KEY (parent_id) REFERENCES parent(id)
                     ON DELETE CASCADE
) TYPE=INNODB;

Voici un exemple plus complexe où la table product_order a des clés étrangères sur deux tables. La première clé est un index à deux colonnes, dans la table product. Les autres clés sont mono-colonnes, dans la table customer :

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                      PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                      product_category INT NOT NULL,
                      product_id INT NOT NULL,
                      customer_id INT NOT NULL,
                      PRIMARY KEY(no),
                      INDEX (product_category, product_id),
                      FOREIGN KEY (product_category, product_id)
                        REFERENCES product(category, id)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
                      INDEX (customer_id),
                      FOREIGN KEY (customer_id)
                        REFERENCES customer(id)) TYPE=INNODB;

Depuis la version 3.23.50, InnoDB vous permet d'ajouter une nouvelle clé à une table, grâce à la syntaxe

ALTER TABLE yourtablename
    ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

N'oubliez pas de commencer par créer les index nécessaires en premier!. Vous pouvez aussi ajouter des clés étrangères reflexives, en utilisant la commande ALTER TABLE.

Depuis la version 4.0.13, InnoDB supporte la syntaxe ALTER TABLE pour supprimer une clé étrangère :

ALTER TABLE yourtablename
    DROP FOREIGN KEY fk_symbol

Si la clause FOREIGN KEY inclut un nom de contraite CONSTRAINT lors de la création, vous pouvez utiliser ce nom pour effacer la clé. Les contraintes peuvent porter un nom depuis MySQL 4.0.18. SInon, la valeur fk_symbol est généré en interne par InnoDB lorsque la clé étrangère est créée. Pour savoir quel symbole utiliser pour effacer une clé étrangère, utilisez la commande SHOW CREATE TABLE. Par exemple :

mysql> SHOW CREATE TABLE ibtest11c\G
*************************** 1. row ***************************
       Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
  `A` int(11) NOT NULL auto_increment,
  `D` int(11) NOT NULL default '0',
  `B` varchar(200) NOT NULL default '',
  `C` varchar(175) default NULL,
  PRIMARY KEY  (`A`,`D`,`B`),
  KEY `B` (`B`,`C`),
  KEY `C` (`C`),
  CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB CHARSET=latin1
1 row in set (0.01 sec)

mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;

Depuis MySQL version 3.23.50, l'analyseur InnoDB autorise l'utilisation des guillemets obliques autour des noms de tables et colonnes dans une clause FOREIGN KEY ... REFERENCES .... Depuis MySQL 4.0.5, l'analyseur InnoDB prend aussi en compte la variable système lower_case_table_names.

Dans InnoDB en versions < 3.23.50, ALTER TABLE et CREATE INDEX ne doivent pas être utilisé avec des tables qui ont des contraintes de clés étrangères, ou qui sont référencées dans des clés étrangères : une commande ALTER TABLE supprime toutes les clés étrangères qui sont définies pour cette table. Vous ne devriez pas utiliser ALTER TABLE sur la table référencée, mais utiliser DROP TABLE puis CREATE TABLE pour modifier le schéma. Lorsque MySQL exécute la commande ALTER TABLE, il risque d'utiliser en interne la commande RENAME TABLE, et cela va poser des problèmes pour les clés étrangères qui reposent sur cette table. Une commande CREATE INDEX est traitée par MySQL comme une commande ALTER TABLE, et ces restrictions s'appliquent aussi.

Lorsqu'il vérifie les clés étrangères, InnoDB pose des verrous de lignes partagées sur les lignes des tables qu'il utilise. InnoDB vérifie immédiatement les contraintes de clés étrangères : la vérification n'attend pas la validation de la transaction.

Si vous voulez ignorer les contraintes de clés étrangères durant, par exemple, une opération de LOAD DATA, vous pouvez utiliser la commande SET FOREIGN_KEY_CHECKS=0.

InnoDB vous permet d'effacer n'importe quelle table, même si cela va casser les contraintes de clés étranègres qui référence cette table. Lorsque vous supprimez une table, la contrainte de clé étrangère qui y était attachée est aussi supprimée.

Si vous recréez une table qui a été supprimée, sa définition doit se conformer aux contraintes des clés étrangères qui la référencent. Elle doit avoir les bons types et noms de colonnes, et doit avoir les bonnes clés, comme indiqué ci-dessus. Si ces contraintes ne sont pas vérifiées, MySQL vous gratifiera d'une erreur 1005, et vous enverra lire le message numéro 150.

Depuis la version 3.23.50 InnoDB retourne la définition de clé étrangère lorsque vous utilisez la commande

SHOW CREATE TABLE tbl_name;

De plus, mysqldump produit aussi les définitions correctes de tables, sans oublier les clés étrangères.

Vous pouvez aussi lister les clés étrangères d'une table T avec

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name'

Les contraintes de clés étragnères sont listées dans les commentaires de la table.

Lors des vérifications des contraintes, InnoDB pose des verrous de lignes sur les lignes parents ou enfants qu'il utilise. InnoDB vérifie immédiatement les contraintes de clés : la vérification n'est pas reportée jusqu'à la validation de la transaction.

Pour simplifier l'importation de données dans des tables qui ont des contraintes, mysqldump ajoute automatiquement la commande qui met la variable FOREIGN_KEY_CHECKS à 0, depuis MySQL version 4.1.1. Cela évite des problèmes spécifiques avec les tables qui doivent être chargées dans un ordre particulier. Pour les versions antérieures, vous pouvez désactiver manuellement la variable depuis mysql lors du chargement du fichier comme ceci :

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;

Cela vous permet de faire l'importation des données des tables dans n'importe quel ordre. Cela accélère aussi l'opération d'importation. FOREIGN_KEY_CHECKS est disponible depuis MySQL 3.23.52 et 4.0.3.

Mettre FOREIGN_KEY_CHECKS à 0 peut aussi être utile pour les opérations de LOAD DATA.

InnoDB permet l'effacement de n'importe quelle table, même si cela casse les contraintes de clés étrangères. Lorsque vous effacez une table, les contraintes définies sur cette table sont aussi effacées.

Si vous recréez une table qui a été effacée, elle doit avoir une définition qui est compatible avec les clés étrangères qui l'utilise. Elle doit avoir les bonnes colonnes et les index. Si cela n'est pas vrai, MySQL retourne une erreur 1005, et fait référence à un message d'erreur numéro 150.

15.7.5 InnoDB et la réplication MySQL

La réplication MySQL fonctionne pour les tables InnoDB comme pour les tables MyISAM. Il est aussi possible d'utiliser la réplication pour que les tables de l'esclave ne soient pas les mêmes que les tables du maître. Par exemple, vous pouvez répliquer les modifications d'une table InnoDB sur le maître dans une table MyISAM sur l'esclave.

Pour configurer un nouvel esclave sur le maître, vous devez faire une copie de l'espace de table InnoDB, des fichiers de log, ainsi que les fichiers .frm des tables InnoDB, et les placer sur l'esclave. Pour une procédure à suivre pour réaliser cela, voyez Section 15.10, « Transférer une base de données InnoDB vers une autre machine ».

Si vous pouvez arrêter le maître ou l'esclave, faîtes une sauvegarde à l'arrêt de l'espace de table InnoDB et des fichiers de logs, puis utilisez les pour redémarrer l'esclave. Pour faire un nouvel esclave sans arrêter le serveur, utilisez le logiciel commercial InnoDB Hot Backup tool.

Il y a des limitations mineures à la réplication InnoDB :

  • LOAD TABLE FROM MASTER ne fonctionne pas pour les tables InnoDB. Il y a des palliatifs : 1) exportez la table du maître, et envoyez la sur l'esclave, ou, 2) utilisez ALTER TABLE tbl_name TYPE=MyISAM sur le maître avant de configurer la réplication avec LOAD TABLE tbl_name FROM MASTER, et ensuite, ALTER TABLE pour remettre les tables en mode InnoDB après cela.

  • Avant MySQL 4.0.6, SLAVE STOP ne respectait la limite de transaction. Une transaction incomplète était annulée, et la prochaine commande SLAVE START n'exécutait que le reste de la transaction, ce qui conduisait à un échec.

  • Avant MySQL 4.0.6, un crash de l'esclave au milieu de d'une transaction multi-commande causait le même problème que SLAVE STOP.

  • Avant MySQL 4.0.11, la réplication de la commande SET FOREIGN_KEY_CHECKS=0 ne fonctionnait pas correctement.

La plupart de ces limitations peuvent être levées en utilisant un serveur récent, pour lequel les limitations n'existent pas.

Les transactions qui échouent sur le serveur n'affectent pas la réplication. La réplication MySQL est basée sur le log binaire où MySQL écrit les requêtes SQL qui modifient des données. Un esclave lit le log binaire du maître, et exécute les mêmes commandes SQLO. Cependant, les commandes d'une transaction ne sont pas écrite avant la fin de la transaction, où toutes les commandes sont écrites d'un coup. Si une transaction échoue, par exemple, à cause d'une clé étrangère, ou si la transaction est annulée, aucune requête ne sera écrite dans le log binaire, et la transaction ne sera pas du tout exécutée sur le serveur.

15.7.6 Espaces de tables multiples : chaque table InnoDB a son fichier .ibd

Depuis MySQL 4.1.1, vous pouvez stocker chaque table InnoDB et ses index dans son propre fichier. Cette fonctionnalité est appelée ``espaces de tables multiples'', car chaque table dispose de son propre espace de table.

Note importante : si vous passez en version InnoDB 4.1.1 ou plus récent, il devient très difficile de retourner en versions 4.0 ou 4.1.0! Ceci est dû au fait que les versions antérieures de InnoDB ne sont pas compatibles avec les espaces de tables multiples.

Si vous devez revenir à une vieille version 4.0, vous devez faire des exports des tables, et recréer tout votre espace de tables InnoDB. Si vous n'avez pas créé de nouvelles tables sous InnoDB >= 4.1.1, et que vous devez revenir rapidement en arrière, vous pouvez passer directement en versions 4.0.18, ou plus récent. Avant de faire un retour en arrière direct en versions 4.0, vous devez terminer toutes les connexions, et laisser mysqld vider les buffers d'insertion, jusqu'à ce que SHOW INNODB STATUS indique que le thread principal soit dans un état de waiting for server activity. Alors, vous pouvez éteindre le serveur mysqld et démarrer votre version 4.0.18 ou plus récent. Un retour en arrière direct n'est pas recommandé, car il n'a pas été totalement testé.

Depuis MySQL version 4.1.1, vous pouvez stocker chaque table InnoDB et ses index dans son propre fichier. Cette fonctionnalité est appelé espaces de tables multiples, car chaque table a son propre espace de table.

Vous pouvez activer cette fonctionnalité en ajoutant une ligne dans le groupe [mysqld] du fichier my.cnf :

[mysqld]
innodb_file_per_table

Après redémarrage du serveur, InnoDB va stocker chaque table dans son propre fichier tablename.ibd du dossier de données, où les tables sont stockées. C'est la même méthode que MyISAM, mais si MySQL divise les tables en un fichier de données et un fichier d'index, tablename.MYD et tablename.MYI, InnoDB place les données et les index dans le même fichier .ibd. Le fichier tbl_name.frm est toujours créé, comme d'habitude.

Si vous supprimez la ligne innodb_file_per_table du fichier my.cnf, alors InnoDB créera les tables dans le fichier de données.

innodb_file_per_table affecte seulement la création de tables. Si vous démarrez le serveur avec cette option, les nouvelles tables sont créées avec le fichier .ibd, mais vous pouvez toujours accéder aux tables qui existent dans l'espace de table partagées ibdata. Si vous supprimez cette option, les nouvelles tables seront crées dans l'espace de tables, mais vous pouvez toujours aux tables qui ont été créées indépendamment.

InnoDB a toujours besoin du système d'espace de tables, les fichiers .ibd ne sont pas suffisants. Le système d'espaces de table est constitué des fichiers classiques ibdata. InnoDB y place son dictionnaire de données interne, et ses historiques d'annulation.

Vous ne pouvez pas déplacer les fichiers .ibd librement, comme vous pouvez le faire avec les tables MyISAM. Ceci est dû au fait que les définitions de tables sont stockées dans le système d'espace de tables InnoDB, et aussi, parce que InnoDB doit préserver la cohérence des identifiants de transactions et les numéros de séquence des logs.

Vous pouvez déplacer le fichier .ibd et la table associée d'une base à l'autre (dans la même installation MySQL/InnoDB) avec la classique commande RENAME :

RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name;

Si vous avez une sauvegarde ``propre'' du fichier .ibd, vous pouvez restaurer l'installation MySQL comme ceci :

  1. Utilisez cette commande ALTER TABLE :

    ALTER TABLE tbl_name DISCARD TABLESPACE;
    

    Attention : cette commande efface le fichier .ibd.

  2. Placez le fichier de sauvegarde .ibd dans le bon dossier de base de données.

  3. Utilisez cette commande ALTER TABLE :

    ALTER TABLE tbl_name IMPORT TABLESPACE;
    

Dans ce contexte, un fichier .ibd ``propre'' signifie :

  • Il n'y a pas de modifications non validées par des transactions dans le fichier .ibd file.

  • Il n'y a pas de buffer d'insertion non intégrés dans le fichier .ibd.

  • La purge a supprimé toutes les lignes marquées pour l'effacement dans le fichier .ibd.

  • mysqld a envoyé toute les pages modifiées au fichier .ibd depuis le buffer de fichier.

Vous pouvez faire une sauvegarde propre du fichier .ibd en suivant la méthode :

  1. Cessez toute activité sur le serveur mysqld et validez toutes les transactions.

  2. Attendez que SHOW INNODB STATUS\G indique qu'il n'y a plus de transaction active dans la base, et que le thread principal de InnoDB est dans l'état Waiting for server activity. Vous pouvez alors faire une copie du fichier .ibd.

Une autre méthode (non libre) de faire une sauvegarde propre du fichier .ibd est de :

  1. Utilisez InnoDB Hot Backup pour faire une sauvegarde de votre installation InnoDB.

  2. Lancez un second serveur mysqld sur la sauvegarde, et laissez le nettoyer les fichiers .ibd de la sauvegarde.

La liste de tâche inclut la possibilité de déplacer les fichiers .ibd vers une autre installation MySQL/InnoDB. Cela impose la remise à zéro des numéros de transactions et des séquences de fichiers de log du fichier .ibd.

15.8 Ajouter et retirer des données et des logs InnoDB

Cette section décrit ce que vous pouvez faire lors que votre espace de tables InnoDB n'a plus d'espace, ou que vous voulez changer la taille des fichiers de logs.

Depuis la version 3.23.50 et 4.0.2, le moyen le plus facile pour augmenter la taille de l'espace de tables InnoDB est de le configurer immédiatement comme auto-croissant. Spécifiez l'attribut autoextend pour le dernier fichier de données dans la définition de l'espace de table. InnoDB va augmenter la taille du fichier automatiquement de 8 Mo dès qu'il n'a plus d'espace dans le fichier.

Alternativement, vous pouvez augmenter la taille de votre espace de tables en ajoutant un autre fichier de données. Pour cela, vous devez éteindre le serveur MySQL, éditer le fichier my.cnf pour ajouter votre nouveau fichier de données à la fin de la ligne innodb_data_file_path, et relancer le serveur MySQL.

Si votre dernier fichier a déjà été configuré avec le mot-clé autoextend, la procédure d'édition du fichier my.cnf doit prendre en compte la taille que le fichier occupe actuellement. Vous devez lire la taille du fichier, l'arrondir au megaoctet inférieur le plus proche (1024 * 1024 octets), et spécifier la taille arrondie dans la ligne d'options innodb_data_file_path. Puis, vous pouvez ajouter un autre fichier de données. N'oubliez pas que seul le dernier fichier de données de la ligne innodb_data_file_path peut être spécifié comme auto-croissant.

Par exemple, supposons que l'espace de tables dispose juste d'un fichier de données auto-croissant, appelé ibdata1 :

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

Supposons qu'au cours du temps, le fichier a atteint la taille de 988 Mo. Ci-dessous, la ligne de configuration va ajouter un autre fichier auto-croissant après celui qui existe.

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

Lorsque vous ajoutez un nouveau fichier de données, assurez vous qu'il n'existe pas, pour que InnoDB puisse le créer et l'initialiser, lorsque vous redémarrerez le serveur.

Actuellement, vous ne pouvez pas supprimer de fichiers de données. Pour réduire la taille de votre espace de table, utilisez cette procédure :

  1. Utilisez mysqldump pour exportez toutes vos tables InnoDB.

  2. Stoppez le serveur.

  3. Supprimez tous les fichiers de tables existants.

  4. Configurez un nouvel espace de table.

  5. Relancez le serveur.

  6. Importez les fichiers de vos tables.

Si vous voulez changer le nombre ou la taille de vos fichiers de log InnoDB, vous devez éteindre le serveur MYSQL et vous assurer qu'il s'est arrêté sans erreur. Puis, copiez les anciens fichiers de log dans une archive, car si vous rencontrez un problème ultérieurement, vous en aurez besoin pour restaurer votre base. Effacer les anciens fichiers de log du dossier de logs, éditez le fichier my.cnf, et redémarrez le serveur MySQL. InnoDB vous indiquera au démarrage qu'il va créer de nouveaux fichiers de log.

15.9 Sauver et restaurer une base InnoDB

La clé d'une gestion prudente de votre serveur est la sauvegarde régulière des données.

InnoDB Hot Backup est un utilitaire de sauvegarde en ligne, qui vous permet de faire des sauvegardes pendant que InnoDB fonctionne. InnoDB Hot Backup ne vous impose pas l'arrêt de votre serveur, et il ne pose pas de verrous qui vont perturber le fonctionnement normal de votre serveur. InnoDB Hot Backup est un utilitaire propriétaire, qui n'est pas inclut dans la distribution MySQL. Voyez le site web de InnoDB Hot Backup : http://www.innodb.com/manual.php, pour plus d'informations.

Si vous pouvez arrêter votre serveur MySQL, alors faites une sauvegarde binaire de votre base comme ceci :

  1. Arrêtez le serveur MySQL et assurez vous qu'il s'est bien arrêté sans erreur.

  2. Copiez tous les fichiers de données dans votre entrepôt.

  3. Copiez tous les fichiers de log InnoDB dans votre entrepôt.

  4. Copiez vos fichiers de configuration my.cnf dans l'entrepôt.

  5. Copiez tous les fichiers .frm de vos tables InnoDB dans votre entrepôt.

En plus de prendre des sauvegardes binaires comme décrit ci-dessus, vous devriez aussi prendre des exports de vos tables avec mysqldump. La raison à cela est que le fichier binaire peut être corrompu sans que vous vous en rendiez compte. Les tables exportées sont stockées sous forme de fichier texte, lisible à l'oeil, et bien plus simple à sauver que les fichiers binaires. Repérer la corruption d'une table dans les fichiers exportés est bien plus facile, et comme le format est bien plus simple, il y a moins de chances que les données soient corrompues.

En fait, c'est une bonne idée que de faire une exportation des tables au moment où vous sauvez les fichiers binaires des bases. Vous devez arrêter tous les clients qui utilisent le serveur. Puis, vous pouvez faire la sauvegarde binaire et l'export : vous aurez ainsi une archive cohérente en deux formats.

Pour restaurer une base InnoDB à partir d'une sauvegarde binaire, vous devez utiliser le serveur MySQL avec les logs général et d'archive activés. Par log général, nous entendons le log de MySQL, et non pas le log spécifique d'InnoDB.

mysqlbinlog yourhostname-bin.123 | mysql

Pour restaurer les données après un crash MySQL, la seule chose à faire est de relancer le serveur. InnoDB va automatiquement vérifier les historiques, et reprendre toutes les opérations qui ont eu lieu jusqu'à présent. InnoDB va automatiquement annuler les transactions qui n'ont pas été achevées. Durant la restauration, InnoDB va afficher des séquences semblables à celle-ci :

~/mysqlm/sql > mysqld
InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections

Si votre base ou vos disques se corrompent, vous devez faire la restauration à partir de sauvegardes. En cas de corruption, vous devriez trouver une sauvegarde qui n'est pas corrompue. A partir de la sauvegarde, faites une restauration à partir des fichiers de logs généraux, en suivant les instructions du manuel.

15.9.1 Forcer la restauration

S'il survient une corruption de base de données, vous souhaiterez exporter vos tables de la base avec SELECT INTO OUTFILE, et généralement, la plupart des données seront intactes et correctes. Mais la correction peut faire que SELECT * FROM table ou une opération en tâche de fond d'InnoDB crashe, ou même, que le processus de récupération de InnoDB crashe. Depuis InnoDB version 3.23.44, il y a une option du fichier d'options qui vous permet de forcer InnoDB a démarrer, et vous permet d'éviter le lancement des opérations en tâche de fond, pour que vous puissiez exporter vos tables. Par exemple, vous pouvez configurer :

[mysqld]
innodb_force_recovery = 4

Avant MySQL 4.0, utilisez cette syntaxe :

[mysqld]
set-variable = innodb_force_recovery=4

Les autres possibilités pour innodb_force_recovery sont listées ci-dessous. La base ne doit pas être utilisée lorsque vous utilisez ces options. Comme mesure de sécurité, InnoDB empêchera un utilisateur de faire des commandes INSERT, UPDATE et DELETE lorsque cette option est supérieure à 0.

Depuis la version 3.23.53 et 4.0.4, vous êtes autorisés à utiliser les commandes DROP et CREATE sur une table, même si la restauration forcée est active. Si vous savez qu'une table particulière vous pose des problèmes, vous pouvez l'effacer. Vous pouvez aussi utiliser cette commande pour stopper une annulation sauvage, qui seraient causée par des importations de masse ou ALTER TABLE. Vous pouvez tuer le processu mysqld et utiliser l'option innodb_force_recovery=3 pour relancer votre base sans l'annulation. Alors, la suppression de la table avec DROP vous aidera.

Un nombre plus grand signifie que toutes les précautions des nombres inférieurs sont inclus. Si vous êtes capables d'exporter vos tables avec une option au maximum de 4, alors vous êtes sûr que très peu de données sont perdues. L'option 6 est plus dramatique, car les pages de la base de données sont laissées dans un état obsolète, et cela va introduire encore plus de corruption dans les arbres B-tree et les structures de la base.

  • 1 (SRV_FORCE_IGNORE_CORRUPT) laisse le serveur fonctionner même s'il détecte une page corrompue. Essaie d'éviter les index et pages avec SELECT * FROM table, ce qui aide à l'export.

  • 2 (SRV_FORCE_NO_BACKGROUND) empêche le thread principal de fonctionner. Si un crash survenait durant la purge, cela l'évitera.

  • 3 (SRV_FORCE_NO_TRX_UNDO) ne lance pas les annulations de transactions après la restauration.

  • 4 (SRV_FORCE_NO_IBUF_MERGE) empêche les opérations de vidange du buffer d'insertion. Si ce sont eux qui causent le crash, il vaut mieux les ignorer. Ne recalcule pas les statisttiques de tables.

  • 5 (SRV_FORCE_NO_UNDO_LOG_SCAN) ne regarde pas les logs d'annulations lors du lancement de la base. InnoDB va traiter les transactions, même incomplètes, comme validées.

  • 6 (SRV_FORCE_NO_LOG_REDO) ne lance pas le rattrapage des opérations avec le log, après la restauration.

15.9.2 Points de contrôle

InnoDB utilise un mécanisme de contrôle appelé points de contrôle flou. InnoDB va écrire des blocs de données modifiées depuis un buffer vers le disque par petits paquets : il n'y a pas besoin de tout écrire en une seule fois, ce qui en général, conduit à l'arrêt du traitement des autres instructions pour quelques instants.

Durant une restauration de base, InnoDB recherche un point de contrôle écrit dans les fichiers de log. Il sait que toutes les modifications de la base placées avant ce point de contrôle sont aussi présentes sur le disque de la base. Puis, InnoDB analyse le fichier de log, et applique les modifications qui ont eu lieu depuis le point de contrôle.

InnoDB écrit dans les fichiers de log en mode circulaire. Toutes les modifications validées qui font que le buffer de MySQL est différent de la version sur le disque doivent être disponibles dans les fichiers de log, au cas où InnoDB aurait besoin pour une restauration. Cela signifie que lorsque InnoDB commence à réutiliser le fichier d'historique, il doit commencer par s'assurer que le disque a re¸u les modifications qui sont dans le buffer. En d'autres termes, InnoDB doit placer un point de contrôle, et souvent, cela se traduit par l'écriture de données du buffer sur le disque.

Ceci explique pourquoi utiliser de grands fichiers de log peut éviter des accès disques pour les points de contrôle. Il est recommandé d'utiliser une taille de fichier d'historique aussi grande que le buffer, voire même plus grande. L'inconvénient des grands fichiers est que la restauration dure alors plus longtemps, puisqu'il y a plus de modifications à appliquer.

15.10 Transférer une base de données InnoDB vers une autre machine

Sur Windows, InnoDB stocke les noms de bases et de tables en interne, et toujours en minuscules. Pour déplacer des bases au format binaire, entre Unix et Windows, ou le contraire, vous devez donner des noms en minuscules à toutes vos bases et tables. Un moyen simple de faire cela sous Unix est d'ajouter la ligne suivante dans la section [mysqld] de votre fichier d'options my.cnf avant de démarrer la création de tables.

set-variable=lower_case_table_names=1

Sous Windows, cette option vaut 1 par défaut.

Les fichiers de données et de logs de InnoDB sont compatibles en mode binaire sur toutes les plates-formes si le format des nombre à virgule flottante est le même. Vous pouvez déplacer une base de données InnoDB en copiant tous les fichiers concernés, que nous avons déjà listés dans la section Section 15.9, « Sauver et restaurer une base InnoDB ». Si les formats des nombres à virgules flottantes sont différents mais que vous n'avez pas utilisé les types de données FLOAT ou DOUBLE dans vos tables alors la procédure est là même : copiez juste les fichiers concernés. Si les formats sont différents et que vous utilisez de tels types de données, vous devez utiliser mysqldump et mysqlimport pour transférer les tables.

Un bon moyen d'avoir de bonnes performances est de couper le mode auto-commit quand vous importez des données dans votre base de données, en supposant que votre espace de tables possède assez d'espace pour la grande partie d'annulations (rollback) que la grande transaction importée génère. Ne faites le commit qu'après avoir importé une table entière, ou un segment de table.

15.11 Modèle de transactions et verrouillage InnoDB

Le modèle transactionnel d'InnoDB a pour but de combiner les avantages des bases de données multi-version aux verrouillages traditionnels en deux phases. InnoDB fait un verrouillage de ligne, et exécute les requêtes par défaut avec des lectures cohérentes non bloquante, de la même fa¸on qu'Oracle. Les verrous InnoDB sont stockés de manière efficace, pour que l'escalade de transaction ne soit pas nécessaire : typiquement, plusieurs utilisateurs sont autorisés à verrouiller toutes les lignes dans une base, ou un sous ensemble aléatoire de ligne, sans que InnoDB ne soit à court de mémoire.

15.11.1 InnoDB et AUTOCOMMIT

Avec InnoDB, toutes les opérations sont placées dans une transaction. Si le mode d'auto-validation est activé, chaque commande SQL est une transaction à part entière. MySQL démarre toujours une nouvelle transaction lorsque le mode d'auto-validation est activé.

Si l'auto-validation est désactivée avec SET AUTOCOMMIT = 0, alors nous pouvons considérer qu'une transaction est toujours commencée. Une commande SQL COMMIT ou ROLLBACK termine la transaction courante et en commence une autre. Ces deux commandes vont libérer tous les verrous InnoDB qui étaient posés durant la transaction. Un COMMIT signifie que les modifications durant la transaction seront enregistrés, et rendus visibles aux autres. Un ROLLBACK, d'un autre coté, annule toutes les modifications.

Si la connexion a activé l'auto-validation, l'utilisateur peut faire une transaction multi-commandes en commen¸ant la transaction avec la commande START TRANSACTION ou BEGIN et en la terminant avec COMMIT ou ROLLBACK.

15.11.2 InnoDB et SET ... TRANSACTION ISOLATION LEVEL ...

En terme de niveau d'isolation des transactions SQL-92, le comportement par défaut de InnoDB est REPEATABLE READ. Depuis la version 4.0.5, InnoDB offre 4 niveaux différents d'isolation de transactions, tels que décrit dans la norme SQL-92. Vous pouvez configurer le niveau d'isolation par défaut dans le groupe [mysqld] du fichier my.cnf:

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
                         | REPEATABLE-READ | SERIALIZABLE}

Un utilisateur peut changer le niveau d'isolation d'une session ou des nouvelles connexion avec la commande SET TRANSACTION. La syntaxe est la suivante :

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
                       {READ UNCOMMITTED | READ COMMITTED
                        | REPEATABLE READ | SERIALIZABLE}

Notez qu'il n'y a pas de tiret dans les noms de niveaux de la syntaxe SQL.

Le comportement par défaut est de configurer le niveau d'isolation de la prochaine transaction non démarrée. Si vous utilisez le mot clé GLOBAL, la commande configure le niveau d'isolation globalement, pour toutes les nouvelles connexions, mais pas pour les connexions existantes. Vous devez avoir les droits de SUPER pour faire cela. En utilisant le mot clé SESSION, vous allez configurer le niveau d'isolation des prochaines transactions de la session courante. Tous les clients sont autorisés à changer le niveau d'isolation de la session, même au milieu d'une transaction, ainsi que le niveau d'isolation de la prochaine transaction. Dans les versions 3.23.50 et plus anciennes, SET TRANSACTION n'avait pas d'effet sur les tables InnoDB. Dans les versions < 4.0.5, seules REPEATABLE READ et SERIALIZABLE étaient disponibles.

Vous pouvez connaître les niveaux d'isolation de transaction de session et global avec ces commandes :

SELECT @@global.tx_isolation;
SELECT @@tx_isolation;

Pour le verrouillage de ligne, InnoDB utilise le système dit de verrouillage de la prochaine clé. Cela signifie qu'en plus des lignes d'index, InnoDB peut aussi verrouiller le ``trou'' après une ligne d'index pour éviter les insertions des autres utilisateurs. Un verrou de prochaine clé représente un verrou qui bloque la ligne d'index et le trou qui le précède. Un verrou de trou représente un verrou qui ne bloque que le trou avant la ligne.

Voici une description détaillée de chaque niveau d'isolation de InnoDB :

  • READ UNCOMMITTED This is also called ``dirty read'' : Les commandes SELECT non-verrouillantes sont effectuées sans rechercher de versions plus récente de la ligne. Elles sont donc non-cohérentes, sous ce niveau d'isolation. SInon, ce niveau fonctionne comme le niveau READ COMMITTED.

  • READ COMMITTED Proche du niveau d'isolation d'Oracle. Toutes les commandes SELECT ... FOR UPDATE et SELECT ... LOCK IN SHARE MODE ne verrouille que les lignes d'index, et non pas les trous entre elles, ce qui permet l'insertion de nouvelles lignes, adjacentes aux lignes verrouillées. UPDATE et DELETE qui utilisent un seul index avec une condition de recherche unique ne verrouille que la ligne d'index trouvée, par le trou qui la précède. Mais, pour un intervalle de lignes traitées par UPDATE et DELETE, InnoDB doit méler des verrous de prochaine clé ou des verrous de trous et bloquer les insertions des autres utilisateurs dans les espaces couvert par l'intervalle. Ceci est nécessaire car les ``lignes fantômes'' doivent être bloquées pour la réplication MySQL et la restauration. La lecture cohérente se comporte comme avec Oracle : chaque lecture cohérente, même dans une transaction, lit et modifie son propre contexte. See Section 15.11.3, « Lecture cohérente non-bloquante ».

  • REPEATABLE READ

    C'est le niveau d'isolation par défaut d'InnoDB. Les commandes SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE et DELETE qui utilisent un index unique dans une condition de recherche, ne verrouille que la ligne d'index trouvée, et non pas le trou précédent. SInon, ces opérations utilisent le verrouillage de prochaine clé, en verrouillant l'intervalle utilisé, et bloque les insertions des utilisateurs. Avec les lectures cohérentes il y a une importante différence avec le niveau d'isolation précédent : dans ce mode, toutes les lectures cohérentes d'une même transaction liront les mêmes données, établies par la première lecture. Cette convention signifie que si vous émettez plusieurs commandes SELECT dans la même transaction, ces SELECT seront cohérents les uns avec les autres. See Section 15.11.3, « Lecture cohérente non-bloquante ».

  • SERIALIZABLE Ce niveau est identique au précédent, mais toutes les lectures SELECT sont implicitement converties en SELECT ... LOCK IN SHARE MODE.

15.11.3 Lecture cohérente non-bloquante

Une lecture cohérente signifie que InnoDB utilise son système de multi-versionnage pour présenter à une requête, une photo de la base à un moment donné. La requête va alors voir les différentes modifications apportées par les transactions qui ont eu lieu avant cette date, et masquera les transactions ont eu lieu depuis, ou n'ont pas été archivées. L'exception à cette règle est que la requête verra les modification apportées la requête qui a émis cette commande.

Si vous utilisez le niveau d'isolation REPEATABLE READ, alors les lectures cohérentes dans une même transaction liront le même bilan. Vous pouvez obtenir un bilan plus récent pour vos requêtes en archivant la requête courante, et en démarrant une autre.

Les lectures cohérentes sont le mode par défaut de traitement des commandes SELECT par InnoDB avec les niveaux d'isolation READ COMMITTED et REPEATABLE READ. Une lecture cohérente ne pose aucun verrou sur les tables auxquelles elle accède, et par conséquent, les autres utilisateurs peuvent librement modifier ces tables en même temps qu'une lecture cohérente est exécutée.

15.11.4 Verrous de lecture SELECT ... FOR UPDATE et SELECT ... LOCK IN SHARE MODE

Une lecture cohérente n'est pas toujours pratique, dans certaines circonstances. Supposons que vous voulez ajouter une ligne dans votre table CHILD, et vous assurer que l'enfant a déjà un parent dans la table PARENT.

Supposez que vous utilisiez une lecture cohérente, pour lire la table PARENT, et que vous découvrez le parent de l'enfant dans cette table. Pouvez vous ajouter tranquillement la ligne fille dans la table CHILD? Non, car il peut arriver que durant ce temps, un autre utilisateur a effacé la ligne parente dans la table PARENT, et vous n'en êtes pas conscient.

La solution est d'exécuter la commande SELECT en mode verrouillage, avec LOCK IN SHARE MODE.

SELECT * FROM PARENT WHERE NAME = 'Jones' LOCK IN SHARE MODE;

Effectuer une lecture en mode partagé signifie que vous allons lire les dernières données disponibles, et que nous allons poser un verrou sur les lignes que nous lisons. Si les dernières données appartiennent à une transaction non validée d'un autre utilisateur, nous allons attendre que ce soit fait. Un verrou partagé évite que les autres utilisateurs ne modifient ou n'effacent la ligne que nous lisons. Après que nous ayons obtenu le nom du parent, nous pouvons tranquillement ajouter le nom du fils dans la table CHILD, et valider notre transaction. Cet exemple montre comment implenter l'intégrité référentielle dans votre application.

Ajoutons un autre exemple : nous avons un champs compteur dans la table CHILD_CODES que nous utilisons pour assigner un identifiant unique à chaque enfant que nous ajoutons dans la table CHILD. Evidemment, en utilisant une lecture cohérente ou une lecture partagée pour lire la valeur courante du compteur n'est pas une bonne idée, car deux utilisateurs de la base peuvent simultanément lire la même valeur de compteur, et nous allons obtenir une erreur de clé doublon lorsque nous ajouterons le second des deux fils.

Ici, LOCK IN SHARE MODE n'est pas une bonne solutionm, car si deux utilisateurs lisent le compteur en même temps, au moins l'un des deux sera bloqué lorsqu'il tentera de modifier le compteur.

Dans ce cas, il y a deux bonnes méthodes pour implémenter la lecture et l'incrémentation du compteur : (1) modifiez le compteur d'une unité, et lisez le après cela ou (2) lisez le compteur d'abord, avec un verrou en mode FOR UPDATE, puis incrémentez le :

SELECT COUNTER_FIELD FROM CHILD_CODES FOR UPDATE;
UPDATE CHILD_CODES SET COUNTER_FIELD = COUNTER_FIELD + 1;

Une commande SELECT ... FOR UPDATE va lire les dernières données disponibles pour chaque ligne, et pose un verrou dessus en même tant qu'il lit. De cette fa¸on, il pose le même verrou que la commande UPDATE.

Notez que la commande ci-dessus est simplement un exemple de fonctionnement de SELECT ... FOR UPDATE. En MySQL, la tâche spécifique de création d'un identifiant unique peut être réalisée avec un seul accès à la table :

UPDATE child_codes
       SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

La comande SELECT ne fait que lire l'identifiant spécifique à la connexion. Il ne fait aucun accès à la table.

15.11.5 Verrou de clé suivante : éviter le problème des lignes fantômes

Avec le verrouillage de ligne, InnoDB utilise un algorithme appelé le verrouillage de la clé suivante. InnoDB fait un verrouillage de telle sorte que lorsqu'il fait une recherche ou un scan d'index, il pose des verrous partagés ou exclusifs sur les lignes d'index qu'il rencontre. Par conséquent, les verrous de lignes sont plus exactement appelés des verrous d'index.

Les verrous que InnoDB posent affectent aussi l'espace qui le sépare de la ligne suivante. Si un utilisateur a un verrou partagé ou exclusif sur une ligne L dans un index, alors un autre utilisateur ne peut faire d'insertion immédiatement avant la ligne L, dans l'ordre de l'index. Ce verrouillage est fait pour éviter le problème de la ligne fantôme. Supposons que je veuille lire et verrouiller tous les enfants ayant un identifiant supérieur à 100 dans la table CHILD, puis modifier certains champs des lignes ainsi identifiées :

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

Supposons qu'il y ait un index sur la table CHILD, sur la colonne ID. Notre requête va scanner l'index à partir de la première ligne où ID est plus grand que 100. Maintenant, si le verrou posé sur les lignes d'index n'empêche pas l'utilisation des intervalles entre les lignes d'index, un nouvel enfant peut être inséré dans la table durant la lecture. Et maintenant, si ma transaction exécute la commande :

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

je vais trouver un nouvel enfant dans le résultat de ma requête. Ceci va à l'encontre du principe d'isolation des transactions : une transaction doit être capable de s'exécuter sans que les lectures soient affectées durant ce temps. Si vous considérons un intervalle de données, alors la nouvelle ligne 'fantôme' va casser le principe d'isolation.

Lorsque InnoDB scanne un index, il va aussi verrouille l'espace après la dernière ligne de l'index. C'est ce qui est arrivé dans l'exemple ci-dessus : le verrou posé par InnoDB va éviter qu'une insertion n'intervienne dans la table où ID serait plus grand que 100.

Vous pouvez utiliser le verrouillage de la clé suivant pour implémenter des vérifications d'unicité dans votre application : si vous lisez des données en mode partagé, et que vous ne voyez pas de duplicata de la ligne que vous allez insérer, alors vous pouvez l'insérer sans problème, en sachant que le verrou de clé suivante va vous garantir que durant ce temps, personne ne pourra insérer de ligne, qui déboucherait sur un duplicata de la votre. Le verrou de clé suivante permet de verrouiller aussi la non-existence de ligne dans votre table.

15.11.6 Un exemple de lecture cohérente avec InnoDB

Supposons que vous exécutez MySQL avec le niveau d'isolation des transactions de REPEATABLE READ. Lorsque vous demandez une lecture cohérente avec une commande SELECT ordinaire, InnoDB va donner à votre transaction une date jalon, en fonction de laquelle votre requête va voir la base. Ainsi, si une transaction B efface une ligne après l'assignation de ce jalon, vous ne verrez pas cette ligne. De même pour une insertion ou une modification.

Vous pouvez faire avancer votre date jalon en validant votre transaction et en exécutant un autre SELECT.

Cela s'appelle le contrôle simultané multi-versions.

                  User A                 User B

              SET AUTOCOMMIT=0;      SET AUTOCOMMIT=0;
time
|             SELECT * FROM t;
|             empty set
|                                    INSERT INTO t VALUES (1, 2);
|
v             SELECT * FROM t;
              empty set
                                     COMMIT;

              SELECT * FROM t;
              empty set;

              COMMIT;

              SELECT * FROM t;
              ---------------------
              |    1    |    2    |
              ---------------------

De cette fa¸on, A voir la ligne insérée par B uniquement lorsque B a validé son insertion, et que A a validé sa propre transaction, de fa¸on à ce que la date jalon soit plus récente que la validation de B.

Si vous voulez avoir une vision aussi ``fraîche'' que possible de votre base, vous devez utiliser le verrou en lecture :

SELECT * FROM t LOCK IN SHARE MODE;

15.11.7 Les verrous posés par différentes requêtes SQL avec InnoDB

Un verrou de lecture, une commande UPDATE ou DELETE pose généralement des verrous sur toutes les lignes qui sont analysée durant l'opération. La présence d'une clause WHERE n'a pas d'importance. InnoDB ne se souviens pas de la condition WHERE exacte, mais sait quel intervalles d'index ont été scannés. Les verrous sont du type 'prochaine clé', et cela empêche aussi les insertions dans l'``espace'' immédiatement après la ligne.

Si le verrou est exclusif, alors InnoDB essaie de lire les groupes d'index et de poser un verrou dessus.

Si vous n'avez d'index valable pour votre requête, et que MySQL scanne toute la table, chaque ligne sera verrouillée, et bloquera ainsi toutes les insertions. Il est important de bien configurer ses index, poru que les requêtes ne fasse pas de scan de table inutiles.

  • SELECT ... FROM ... : ceci est une lecture cohérente, qui lit un bilan de la base, et ne pose aucun verrou.

  • SELECT ... FROM ... LOCK IN SHARE MODE : pose un verrou partagé sur la prochaine clé sur tous les index que la lecture rencontre.

  • SELECT ... FROM ... FOR UPDATE : pose un verrou exclusif sur la prochaine clé sur tous les index que la lecture rencontre.

  • INSERT INTO ... VALUES (...) : pose un verrou exclusif sur la ligne insérée. Notez que ce verrou n'est pas un verrou de clé, et il n'empêche pas les autres utilisateurs d'insérer des lignes. Si une erreur de clé double apparaît, un verrou sera posé partagé sera posé sur la ligne doublon.

  • Durant l'initialisation d'une colonne AUTO_INCREMENT dans une table, InnoDB pose un verrou exclusif à la fin de l'index associé à la colonne AUTO_INCREMENT. Lors de l'accession au compteur d'incrémentation, InnoDB utilise un verrou spécifique, en mode AUTO-INC où le verrou ne dure que jusqu'à la fin de la requête SQL courante, au lieu de la fin de la transaction. See Section 15.11.1, « InnoDB et AUTOCOMMIT ».

    Avant MySQL 3.23.50, SHOW TABLE STATUS posait aussi un verrou exclusif sur les tables ayant une colonne AUTO_INCREMENT. Cela signifie que la commande SHOW TABLE STATUS pouvait aussi causer un blocage de verrou, ce qui surprenait beaucoup les utilisateurs. Depuis MySQL 3.23.50, InnoDB lit la valeur d'une table dont la colonne AUTO_INCREMENT a été initialisée, sans poser de verrou.

  • INSERT INTO T SELECT ... FROM S WHERE ... : pose un verrou exclusif sur chaque ligne inséré dans T. Effectue la recherche sur S sous la forme d'une lecture cohérente, mais pose un verrou partagé sur l'index de prochaine clé de S si MySQL a activé le log. InnoDB doit poser un verrou dans cette dernière situation, car en cas d'exécution des instructions dans une phase de restauration, toutes les requêtes doivent être exécutées dans le même ordre.

  • CREATE TABLE ... SELECT ... effectue une commande SELECT sous la forme d'une lecture cohérente, ou avec des verrous partagés, comme précédemment.

  • REPLACE est similaire à une insertion, si il n'y a pas de collision sur la clé unique. Sinon, une verrou exclusif sur l'index de prochaine clé est posé sur la ligne qui sera modifiée.

  • UPDATE ... SET ... WHERE ... : pose un verrou exclusif sur l'index de prochaine clé, à chaque ligne que la recherche trouve.

  • DELETE FROM ... WHERE ... : pose un verrou exclusif sur l'index de prochaine clé à chaque ligne que la recherche trouve.

  • Si la contrainte de FOREIGN KEY est définie sur une table, toute insertion, modification ou effacement qui requiert la vérification de la contrainte va poser un verrou de ligne sur la ligne dont il doit vérifier la contrainte. De plus, dans certains cas où la contrainte échoue, InnoDB pose ces verrous.

  • LOCK TABLES ...  : pose un verrou de table. L'implémentation de la couche MySQL pose ce verrou. La détection automatique des blocages de InnoDB ne peut détecter les blocages lorsque de tels verrous sont posés. Voyez la section suivante. See Section 15.11.9, « Détection des blocages et annulation ».

    De plus, comme MySQL ne connaît pas le verrouillage de lignes, il est possible que vous posiez un verrou sur une table où un autre utilisateur a déjà posé un verrou. Mais cela ne pose pas de problème quant à l'intégrité de la requête. See Section 15.17, « Restrictions sur les tables InnoDB ».

15.11.8 Quand est-ce que MySQL valide ou annule implicitement une transaction?

MySQL ouvre les connexions des clients en mode d'auto-validation, par défaut. Lorsque l'auto-validation est activée, MySQL fait une validation après chaque commande SQL, si la commande n'a pas retourné d'erreur.

Si vous n'avez pas de mode d'auto-validation, et que vous fermez une connexion sans valider explicitement vos transactions, alors MySQL annule votre transaction.

Si une erreur est retournée par une commande SQL, le comportement de la transaction dépend de l'erreur. See Section 15.16, « Gestion des erreurs InnoDB ».

Les commandes SQL suivantes causent une validation implicite de la transaction courante :

  • ALTER TABLE, BEGIN, CREATE INDEX, DROP DATABASE, DROP INDEX, DROP TABLE, LOAD MASTER DATA, LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, TRUNCATE, UNLOCK TABLES.

  • CREATE TABLE (elle valide uniquement avant MySQL 4.0.13 et si le log binaire MySQL est utilisé).

  • La commande CREATE TABLE de InnoDB est traitée comme une seule transaction. Cela signifie qu'une commande ROLLBACK ne va pas annuler la commande CREATE TABLE qui a été faite dans la transaction.

15.11.9 Détection des blocages et annulation

InnoDB détecte automatiquement les blocages de transactions et annule une ou plusieurs transactions pour l'éviter. Depuis la version 4.0.5, InnoDB va essayer d'annuler les petites transactions. La taille de la transaction est déterminée par le nombre de lignes qu'elle a inséré, modifié ou effacé. Avant la version 4.0.5, InnoDB annulait toujours la transaction qui avait posé le dernier verrou avant le blocage, c'est à dire, un cycle dans le graphe des transactions.

InnoDB ne peut pas détecter les blocages causés par la commande MySQL LOCK TABLES, ou si un verrou est posé par un autre gestionnaire de table que InnoDB. Vous devez résoudre ces situations avec l'option innodb_lock_wait_timeout du fichier de configuration.

Lorsque InnoDB effectue une annulation de transaction, tous les verrous de cette transaction sont libérés. Cependant, si une commande SQL est annulée pour cause d'erreur, certains verrous de la transaction peuvent être conservés. Deci est dû au fait que InnoDB enregistre les verrous dans un format qui ne permet pas de savoir qui l'a posé.

15.11.10 Comment gérer les blocages de verrous?

Les blocages de verrous sont un problème classique des bases de données transactionnelles, mais ils ne sont pas dangeureux, à moins qu'ils ne se répètent si souvent que vous ne puissiez pas exécuter tranquillement certaines transactions. Normalement, vous devriez écrire vos applications de manière à ce qu'elles soient prêtes à tenter à nouveau une transaction si la transaction est annulée pour cause de blocage.

InnoDB utilise un verrouillage de lignes automatique. Vous pouvez obtenir des blocages sur une ligne, même si votre transactions ne fait que modifier ou insérer une seule ligne. Cela est dû au fait que les opérations ne sont pas réellement 'atomiques' : elles posent automatiquement des verrous (éventuellement plusieurs) sur les lignes d'index de l'enregistrement concerné.

Vous pouvez gérer ces blocages et réduire leur nombre avec les trucs suivants :

  • Utilisez la commande SHOW INNODB STATUS avec MySQL version supérieure à 3.23.52 et 4.0.3, pour déterminer la cause du dernier blocage. Cela peut vous aider à optimiser votre application pour les éviter.

  • Soyez toujours prêts à tenter une nouvelle fois une transaction si elle échoue à cause d'un blocage. Les verrous ne sont pas dangeureux. Essayez juste une autre fois.

  • Validez souvent vos transactions. Les petites transactions sont moins sujettes aux blocages.

  • Si vous utilisez des verrous en lectures avec SELECT ... FOR UPDATE ou ... LOCK IN SHARE MODE, essayez d'utiliser un niveau d'isolation plus bas comme READ COMMITTED.

  • Accédez à vos tables et lignes dans un ordre fixé. Les transactions vont alors former des queues, et non pas des blocages.

  • Ajoutez de bons index à vos tables. Vos requêtes devront scanner moins souvent les tables, et poseront donc moins de verrous. Utilisez EXPLAIN SELECT pour déterminer si MySQL choisit les bons index pour vos requêtes.

  • Limitez votre utilisation des verrous. Si vous pouvez vous permettre de faire retourner à une commande SELECT des données un peu anciennes, n'ajoutez pas la clause FOR UPDATE ou LOCK IN SHARE MODE. Utiliser le niveau d'isolation READ COMMITTED est bon ici, car chaque lecture cohérente dans la même transaction lira avec des données aussi fraîches que possible à chaque fois.

  • En dernier recours, vous pouvez forcer les verrous avec la commande :

    LOCK TABLES t1 WRITE, t2 READ, ...;
    [faire quelquechose avec les tables t1 et t2]; 
    UNLOCK TABLES;
    

    Les verrous de niveau de table forcent les transactions à se mettre en ligne, et les blocages sont évités. Notez que LOCK TABLES démarre implicitement une transaction, tout comme BEGIN, et UNLOCK TABLES termine une transaction avec un COMMIT.

  • Une dernière solution est de créer un sémaphore auxiliaire sous la forme d'une table avec une seule ligne. Chaque transaction modifie cette table avant d'accéder aux autres tables. Dans ce cas, toutes les transactions se font en ordre séquentiel. Notez que dans cette configuration, même l'algorithme InnoDB de détection des blocages fonctionne, car le sémaphore est un verrou de ligne. Avec les verrous de niveau de table de MySQL, nous devons nous résoudre à une méthode de délai d'expiration pour résoudre un verrou.

15.12 Conseils pour l'amélioration des performances InnoDB

  • Si l'outil Unix top ou si le Task Manager de Windows montre que l'utilisation du CPU, lors de la charge de travail, est inférieure à 70%, votre calcul est probablement limité par les disques. Vous faites peut être trop d'écriture de transaction, ou le tampon de traitement ("buffer pool") est peut être trop petit. Augmenter la taille du tampon peut aider, mais il ne faut pas qu'il dépasse 80% de la mémoire physique.

  • Regrouper les modifications dans une seule transaction. InnoDB doit écrire les données de log sur le disque à chaque validation de transaction, si la transaction a fait des modifications dans la base. Comme la vitesse de rotation d'un disque est typiquement de 167 revolutions/seconde au mieux, cela réduit le nombre de validations à 167 par seconde, si le disque ne veut pas induire le système d'exploitation en erreur.

  • Si vous pouvez accepter la perte des toutes dernières transactions validées, vous pouvez modifier dans le fichier my.cnf le paramètre innodb_flush_log_at_trx_commit à 0. InnoDB essaie d'écrire sur le disque au moins une fois par seconde, mais cette écriture n'est plus garantie.

  • Utilisez de gros fichiers de log, même aussi grand que le pool de buffer. Lorsque InnoDB a écrit les fichiers de log, il doit écrire les contenus modifiés du pool de buffer sur le disque, avec un jalon. Les fichiers de logs de petites tailles imposeront des écritures inutiles. L'inconvénient d'un gros fichier de log est que le temps de restauration sera plus long.

  • Le buffer de logs doit être assez grand, au moins 8 Mo.

  • Utilisez le type de colonne VARCHAR au lieu de CHAR si vous stockez des chaînes de taille variable, ou si une colonne peut contenir plusieurs valeurs NULL. Une colonne CHAR(N) prend toujours N octets pour stocker les données, même si la chaîne est plus petite, ou que sa valeur est NULL. Des tables plus petites rentrent plus facilement dans les buffers et réduisent les accès disques.

  • (Valable depuis MySQL version 3.23.39 et plus récent) Dans certaines versions de MySQL et Unix, l'écriture des fichiers sur les disques avec fdatasync et d'autres commandes similaires sont étonnament lentes. La méthode par défaut de InnoDB est la fonction fdatasync. Si vous n'êtes pas satisfaits avec les performances en écriture de la base, vous pouvez essayer d'utiliser l'option innodb_flush_method dans le fichier my.cnf avec la valeur O_DSYNC, même si O_DSYNC semble être la méthode la plus lente sur la plupart des systèmes.

  • Lors de l'importation de données avec InnoDB, assurez vous que MySQL n'a pas autocommit=1. Sinon, chaque insertion implique une écriture sur le disque. Ajoutez cette commande dans votre fichier SQL d'import :

    SET AUTOCOMMIT=0;
    /* commandes d'importation SQL ... */
    COMMIT;
    

    Si vous utilisez l'option --opt de l'utilitaire mysqldump, vous allez obtenir des fichiers d'export qui sont rapides à importer dans une table InnoDB, même sans utiliser l'astuce de la transaction ci-dessus : SET AUTOCOMMIT=0; ... COMMIT;.

  • Attention aux annulations lors des insertions de masse : InnoDB utilise une buffer d'insertion pour éviter les accès disques, mais la fonction d'annulation correspondante n'utilise pas ce mécanisme. Une annulation qui utilise beaucoup d'accès disque est environ 30 fois plus lente que l'insertion équivalent. Interrompre la base ne va pas aider, car l'annulation reprendra lors du redémarrage de la base. La seule solution pour ce débarasser de cette annulation lénifiante est d'augmenter la taille du pool de buffer, pour que l'annulation soit limitée par le processeur et non plus par le disque. Ou alors, effacez toute la base InnoDB. See Section 15.9.1, « Forcer la restauration ».

  • Attention aux opérations qui ont de gros impacts sur le disque : utilisez DROP TABLE ou TRUNCATE depuis MySQL 4.0 et, pour vider une table, et non pas DELETE FROM votre_table.

  • Utilisez les INSERT multiples pour réduire les communications entre le client et le serveur, si vous devez insérer plusieurs lignes :

    INSERT INTO yourtable VALUES (1, 2), (5, 5);
    

    Ce conseil est valable pour tous les types des tables, pas seulement InnoDB.

  • Si vous avez une contrainte UNIQUE sur les clés secondaires, depuis MySQL version 3.23.52 et 4.0.3, vous pouvez accélérer les imports de tables en désactivant temporairement les vérifications d'unicité durant l'importation :

    SET UNIQUE_CHECKS=0;
    

    Pour les grandes tables, cela économise de nombreux accès disques, car les tables InnoDB peuvent utiliser le buffer d'insertion pour écrire des index secondaires par paquets.

  • Si vous avez des contraîntes FOREIGN KEY dans vos tables, depuis MySQL 3.23.52 et 4.0.3, vous pouvez accélérer les imports de tables en désactivant temporairement les vérifications d'unicité durant l'importation :

    SET FOREIGN_KEY_CHECKS=0;
    

    Pour les grandes tables, cela économise beaucoup d'accès disques.

  • Si vous avez des requêtes récurrentes dans vos tables, qui ne sont pas modifiées souvent, vous pouvez utiliser le cache de requêtes depuis MySQL 4.0 :

    [mysqld]
    query_cache_type = ON
    query_cache_size = 10M
    

    En MySQL 4.0, le cache temporaire fonctionne uniquement si l'auto-commit est activé. Cette restriction a été levée depuis MySQL 4.1.1.

15.12.1 Le moniteur InnoDB

Depuis la version 3.23.42, InnoDB inclut le moniteur InnoDB (InnoDB Monitor) qui affiche des informations sur l'état interne des tables InnoDB. Depuis les versions 3.23.52 et 4.0.3 vous pouvez aussi utiliser la commande SQL SHOW INNODB STATUS pour lire les informations de InnoDB Monitor depuis le client SQL. Les données lue sont importantes pour les réglages de performances. Si vous utilisez le client mysql interactif, le résultat est plus lisible si vous remplacez le point virgule classique par \G :

SHOW INNODB STATUS\G

Une autre méthode pour utiliser le InnoDB Monitors est de le laisser écrit continuellement des données dans la sortie de mysqld : (note : le client MySQL n'affichera rien). Lorsqu'il est activé, InnoDB peut afficher des données toutes les 15 secondes. Si vous utilisez mysqld comme démon, alors ces données sont dirigées vers le fichier de log .err dans le dossier datadir. Les données lue sont importantes pour les réglages de performances. Sous Windows, vous devez lancer mysqld-max depuis une fenêtre MS-DOS, avec l'option --console, si vous voulez voir le résultat affiché dans la fenête.

Il existe aussi innodb_lock_monitor qui affiche les mêmes informations que innodb_monitor, mais qui indique aussi les verrous posés par les transactions.

  • Les verrous de tables et de ligne de chaque transaction,

  • les attentes de verrous par les transactions,

  • Les attentes de sémaphores pour les threads,

  • les requêtes en attente d'accès aux fichiers,

  • les statistiques sur les buffers, et

  • les statistiques sur les purges et buffers d'insertion sur le thread principal InnoDB.

Vous pouvez démarrer le moniteur InnoDB avec la commande SQL suivante :

CREATE TABLE innodb_monitor(a int) type = innodb;

et le stopper avec

DROP TABLE innodb_monitor;

La syntaxe CREATE TABLE est simplement un moyen de passer une commande à une table InnoDB, via l'analyseur MySQL : la table créée n'est pas importante pour le moniteur InnoDB. Si vous interrompez le serveur lorsque le moniteur fonctionne, et que vous voulez redémarrer le moniteur, vous devez alors effacer la table avant de pouvoir exécuter la même commande CREATE TABLE pour démarrer le moniteur. Cette syntaxe est susceptible de changer dans le futur.

Un exemple de résultat du moniteur InnoDB :

mysql> SHOW INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
030709 13:00:59 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 413452, signal count 378357
--Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds the semaphore:
X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135
a writer (thread id 32782) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file btr0sea.c line 731
Last time write locked in file btr0sea.c line 1347
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 108462, OS waits 37964; RW-excl spins 681824, OS waits 375485
------------------------
LATEST FOREIGN KEY ERROR
------------------------
030709 13:00:59 Transaction:
TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 inser
ting
15 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 25, query id 4668733 localhost heikki update
insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
Foreign key constraint fails for table test/ibtest11a:
,
  CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`, `D`)
 ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index PRIMARY tuple:
 0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2: len 4;
 hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4: len 7; hex
 00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;;
But in parent table test/ibtest11b, in index PRIMARY,
the closest match we can find is record:
RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex 80000005; a
sc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex 0000111ef3eb; asc ......
;; 4: len 7; hex 800001001e0084; asc .......;; 5: len 3; hex 6b6864; asc khd;;
------------------------
LATEST DETECTED DEADLOCK
------------------------
030709 12:59:58
*** (1) TRANSACTION:
TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185, OS thread id 30733 inser
ting
LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146
MySQL thread id 21, query id 4553379 localhost heikki update
INSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t','e187358f'
,'g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),7
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
trx id 0 290252780 lock mode S waiting
Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a
a35818;; 1:
*** (2) TRANSACTION:
TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190, OS thread id 32782 inser
ting
130 lock struct(s), heap size 11584, undo log entries 437
MySQL thread id 23, query id 4554396 localhost heikki update
REPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','', NULL,'
h396', NULL, NULL, 7.31,7.31,7.31,200)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
trx id 0 290251546 lock_mode X locks rec but not gap
Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a
a35818;; 1:
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
trx id 0 290251546 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230; asc aa
35720;; 1:
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 290328385
Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
Total number of lock structs in row lock hash table 70
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 3491, OS thread id 42002
MySQL thread id 32, query id 4668737 localhost heikki
show innodb status
---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 3205, OS thread id 38929 in
serting
1 lock struct(s), heap size 320
MySQL thread id 29, query id 4668736 localhost heikki update
insert into speedc values (1519229,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlhh
gghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh
---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 3180, OS thread id 28684 co
mmitting
1 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 19, query id 4668734 localhost heikki update
insert into speedcm values (1603393,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlh
hgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf
---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 3200, OS thread id 36880 st
arting index read
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 27, query id 4668644 localhost heikki Searching rows for update
update ibtest11a set B = 'kHdkkkk' where A = 89572
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a index PRIM
ARY trx id 0 290328327 lock_mode X waiting
Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc
 supremum.;;
------------------
---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 ro
llback of SQL statement
ROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 25, query id 4668733 localhost heikki update
insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 3190, OS thread id 32782
58 lock struct(s), heap size 5504, undo log entries 159
MySQL thread id 23, query id 4668732 localhost heikki update
REPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t','e200498f
','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),
---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 3185, OS thread id 30733 in
serting
4 lock struct(s), heap size 1024, undo log entries 165
MySQL thread id 21, query id 4668735 localhost heikki update
INSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','', NULL,
'h321', NULL, NULL, 7.31,7.31,7.31,200)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs
25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 19, seg size 21,
85004 inserts, 85004 merged recs, 26669 merges
Hash table size 207619, used cells 14461, node heap has 16 buffer(s)
1877.67 hash searches/s, 5121.10 non-hash searches/s
---
LOG
---
Log sequence number 18 1212842764
Log flushed up to   18 1212665295
Last checkpoint at  18 1135877290
0 pending log writes, 0 pending chkp writes
4341 log i/o's done, 1.22 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 84966343; in additional pool allocated 1402624
Buffer pool size   3200
Free buffers       110
Database pages     3074
Modified db pages  2674
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 171380, created 51968, written 194688
28.72 reads/s, 20.72 creates/s, 47.55 writes/s
Buffer pool hit rate 999 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 3004, id 7176, state: purging
Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779
1586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.05 sec)

Quelques notes sur le résultat :

  • Si la section TRANSACTIONS rapporte des attentes de verrous, alors votre application a des problèmes de rétention de verrous. Le rapport peut vous aider à trouver les blocages de verrous, et les transactions bloquantes.

  • La section SEMAPHORES rapporte les threads en attente d'un sémaphore, et les statistiques d'attentes pour un mutex ou un verrou en lecture/écriture. Les longues attentes peuvent être dues à des requêtes paralèlles, ou des problèmes avec la programmation des threads. Donner à la variable innodb_thread_concurrency une valeur plus petite que le 8 par défaut peut réduire ce type de situations.

  • La section BUFFER POOL AND MEMORY vous donne des statistiques sur les pages qui ont été lues et écrites. Vous pouvez calculer depuis ces nombres combien d'accès disques vos requêtes réalisent actuellement.

  • La section ROW OPERATIONS montre ce que le thread principal fait.

15.13 Implémentation du multi-versionnage

Comme InnoDB est une base de donnes multi-versionnée, elle doit conserver des informations des vieilles versions des lignes dans l'espace de tables. Cette information est stockée dans la structure de données que nous appelons un segment d'annulation, d'après la structure similaire d'Oracle.

En interne, InnoDB ajoute deux champs à chaque ligne stockée dans la base. Un champ de 6 octets note l'identifiant de la dernière transaction qui a inséré ou modifier la ligne. De plus, un effacement est traité en interne comme une modification, où un bit spécial dans la ligne sert à marquer la ligne comme effacée. Chaque ligne contient aussi une colonne de 7 octets appelé un pointeur d'annulation. Ce pointeur fait référence à une ligne dans un fichier d'annulation qui contient les informations nécessaires pour reconstruire le contenu de la ligne qui a été modifiée.

InnoDB utilise les informations dans le segment d'annulation pour effectuer les opérations d'annulation nécessaires dans une annulation de transaction. Il utilise aussi ces informations pour reconstruire les anciennes versions d'une ligne lors d'une lecture cohérente.

Le log d'annulation dans le segment d'annulation est divisé entre les insertions et les modifications. Le log d'insertion n'est nécessaire que lors des annulations de transactions, et peut être vidé dès que la requête est validée. Le log de modification est utilisé lors des lectures cohérentes, et les informations y sont supprimées une fois que toutes les transactions qui font une lecture cohérente sont terminées.

Vous devez vous rappeler que valider vos transactions régulièrement, même ces transactions qui ne font que des lectures cohérentes. Sinon, InnoDB ne peut pas vider le log de modification, et le segment d'annulation va grossir énormément.

La taille physique d'une ligne du log d'annulation dans le segment d'annulation est typiquement plus petite que la ligne correspondante insérée ou modifiée. Vous pouvez utiliser ces informations pour calculer l'espace nécessaire à vos segments d'annulation.

Dans nos schémas de multi-versionnage, une ligne n'est pas physiquement supprimée de la table immédiatement lorsque vous l'effacez avec une requête SQL. Uniquement lorsque InnoDB va supprimer les lignes du log de modification, il vaut aussi supprimer physiquement la ligne, et les index. Cette opération d'effacement est appelée une purge, et elle est plutôt rapide, et aussi rapide que la requête SQL elle-même.

15.14 Structures de tables et d'index

MySQL enregistre la structure de table dans le fichier .frm, du dossier de données. Mais les tables InnoDB ont aussi leur propre entrée dans les tables internes InnoDB. Lorsque MySQL efface une table ou une base, il efface le ou les fichiers .frm et aussi les lignes correspondantes dans les tables d'administration InnoDB. C'est la raison qui fait que vous ne pouvez pas déplacer les tables InnoDB entre les bases simplement en dépla¸ant le fichier .frm, et pourquoi DROP DATABASE ne fonctionnait pas sous InnoDB en MySQL versions <= 3.23.43.

Chaque table InnoDB a un index spécial appelé un index en grappe, où les données des lignes sont enregistrées. Si vous définissez une clé primaire pour votre table PRIMARY KEY, alors l'index de la clé primaire de la table sera un index en grappe.

Si vous ne définissez pas de clé primaire pour votre table, InnoDB va générer un index en grappe, ou les lignes sont ordonnées dans l'ordre des identifiants que InnoDB assigne aux lignes de la table. L'identifiant de ligne vaut 6 octets, et s'accroît au fur et à mesure que les lignes sont ajoutées. Les lignes sont alors ordonnées dans leur ordre d'insertion.

Accéder à une ligne via l'index en grappe est rapide, car la ligne de données sera dans la même page que l'index. Dans de nombreuses bases, les données sont traditionnellement stockées dans un autre endroit. Si la table est grand, l'index en grappe économise de nombreux accès disques, comparativement aux solutions traditionnelles.

Les lignes des index qui ne sont pas en grappe (ce sont les index secondaires) dans InnoDB, contiennent la valeur de la clé primaire de la ligne. InnoDB utilise cette clé primaire pour rechercher la valeur de la ligne dans l'index en grappe. Notez que si la clé primaire est longue, les index secondaires utiliseront plus de place.

InnoDB compare les chaînes CHAR et VARCHAR de différentes longues en complétant la chaîne la plus courte avec des espaces.

15.14.1 Structure physique d'un index

Tous les index de InnoDB sont des index B-tree où les lignes d'index sont stockées dans un noeud terminal de l'arbre. La taille par défaut d'une page d'index est de 16ko. Lorsque de nouvelles lignes sont insérées, InnoDB essaie de laisser 1 / 16 de la page de libre pour les prochaines insertions et modifications dans les lignes d'index

Si les lignes d'index sont insérées dans un ordre séquentiel (croissant ou décroissant), les pages d'index résultantes seront environs pleines à 15/16. Si les lignes sont insérées dans un ordre aléatoire, les pages seront pleines de 1/2 à 15/16. Si le taux de remplissage d'une page d'index tombe à 1/2, InnoDB va essayer de contracter l'arbre d'index pour libérer la page.

15.14.2 Bufferisation des insertions

Une situation courante dans les applications de base de données apparaît lorsque la clé primaire est un identifiant unique, et que les nouvelles lignes sont insérées dans un ordre ascendant. Par conséquent, les insertions dans l'index en grappe ne nécessitent pas de lectures aléatoires dans le disque.

D'un autre coté, les index secondaires sont généralement non-uniques, et les insertions surviennent dans un ordre aléatoire. Cela causerait de nombreux accès disques aléatoire, si InnoDB de disposait pas d'un mécanisme spécial.

Si une ligne doit être insérée dans un index secondaire non unique, InnoDB vérifie si la page d'index fait partie du buffer. Dans ce cas, InnoDB va faire directement l'insertion dans une structure de buffer destinée à l'insertion. Le buffer d'insertion est conservé petit, pour qu'il reste dans le buffer général, et les insertions sont faites très vite.

Le buffer d'insertion est périodiquement fusionné avec l'arbre d'index secondaires dans la base. Souvent, nous fusionnons plusieurs insertions dans la même page de l'arbre d'index, et donc, nous économisons des accès disques. Il a été mesuré que les insertions sont jusqu'à 15 fois plus rapides de cette fa¸on.

15.14.3 Index hash adaptatifs

Si une base de données est suffisamment petite pour tenir en mémoire, alors le plus rapide pour faire des requêtes est d'utiliser les index hash. InnoDB a un mécanisme automatique pour surveiller les recherches utilisant les index d'une table, et si InnoDB remarque que la requête pourrait profiter d'un index hash, un tel index est automatiquement constitué.

Mais notez que les index hash sont toujours bâtis à partir d'un index B-tree existant. InnoDB peut bâtir un index hash sur un préfixe de taille arbitraire de clé B-tree, suivant le modèle de recherche que InnoDB remarque dans l'index B-tree. Un index hash peut être partiel : il n'est pas obligatoire que tout l'index B-tree soit mis en cache dans le pool. InnoDB va bâtir des index hash à la demande pour les tables dont les index sont souvent sollicités.

En un sens, grâce au mécanisme d'index hash adaptatif, InnoDB s'adapte tout seul à la mémoire interne, et se rapproche des architectures de bases en mémoire vive.

15.14.4 Structure physique d'une ligne

  • Chaque ligne d'index de InnoDB contient un entête de 6 octets. L'entête est utilisé pour lier des lignes consécutives ensembles, et aussi pour le verrouillage de ligne.

  • Les lignes dans un index en grappe contient des champs pour toutes les colonnes définies par l'utilisateur. De plus, il y a un champ de 6 octets pour l'identification de transaction, et un champs de 7 octets pour le pointeur d'annulation.

  • Si l'utilisateur n'a pas défini de clé primaire pour la table, chaque ligne de l'index en grappe contient aussi une colonne supplémentaire de 6 octets, qui sert d'identification.

  • Chaque ligne d'index secondaire contient aussi les champs définis pour la clé de l'index en grappe.

  • Une ligne contient aussi un pointeur pour chaque champs de la ligne. Si la taille totale des champs représentent moins de 128 octets, alors le pointeur fait 1 octets, sinon 2.

  • En interne, InnoDB stocke les colonnes de taille fixe comme CHAR(10) dans un format à taille fixe. InnoDB supprime les espaces terminaux des colonnes VARCHAR. Notez que MySQL peut convertir en interne les colonnes CHAR en VARCHAR. See Section 13.2.5.1, « Modification automatique du type de colonnes ».

  • Une valeur SQL NULL réserve 0 octets si elle est stockée dans une colonne à taille variable. Dans une colonne à taille fixe, elle utilise toute la largeur de la colonne. La raison de la réservation de toute la colonne pour les valeurs NULL est que lors de la mise à jour de la colonne depuis la valeur NULL vers une valeur non-nulle, il n'y aura pas de fragementation sur le disque.

15.15 Gestion de l'espace fichiers et des entrées/sorties disque

15.15.1 Accès disques

InnoDB utilise des accès disques asynchrones : InnoDB crée quelques threads pour s'occuper des opérations de lecture, notamment les lectures anticipées.

Voici deux heuristiques de lectures anticipées de InnoDB :

  • Dans une lecture anticipée séquentielle, si InnoDB remarque que l'accès à une partie de l'espace de table est séquentiel, il fait plusieurs lectures à l'avance.

  • Dans une lecture aléatoire, si InnoDB remarque qu'une partie de l'espace de tables semble être totalement lu dans le buffer, il va aussi faire une lecture à l'avance.

Depuis la version 3.23.40b, InnoDB utilise une nouvelle technique de flush de fichier, appelée doublewrite. Elle apporte de la sécurité lors de la restauration après crash du système d'exploitation, ou un problème électrique, et améliore les performances sous Unix, pour plusieurs distributions, en réduisant le besoin de synchronisation.

Doublewrite (Double écriture, en fran¸ais) signifie que InnoDB, avant d'écrire les pages dans le fichier de données, les écrits dans une zone continue d'espace, appelée un buffer de double écriture. Une fois que cette écriture est faite, que le buffer de double écriture a été vidé, InnoDB écrit les pages à leur destination finale. Si le système d'exploitation crashe entre temps, InnoDB va trouver une bonne copie des données dans le buffer, lors de la restauration.

15.15.2 Utiliser les raw devices pour l'espace de tables

Depuis MySQL 3.23.41, vous pouvez utiliser des partitions raw disk pour stocker les fichiers d'espace de tables. En utilisant un raw disk, vous pouvez faire des E/S non bufferisée sous Windows et sans couche de contrôle sous Unix, ce qui améliore les performances.

Lorsque vous créer un nouveau fichier de données, vous devez mettre le mot newraw immédiatement après la taille du fichier de données dans innodb_data_file_path. La partition doit être aussi grande que la taille que vous spécifiez. Notez que 1Mo avec InnoDB vaut 1024 * 1024 octets, alors que 1Mo signifie généralement 1000000 d'octets pour un disque.

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

Lors de votre prochain redémararge du serveur, InnoDB remarque le mot newraw et initialise un nouvelle partition. Cependant, ne créez ou ne modifiez pas les tables InnoDB pour le moment. SInon, lors de votre prochain redémarrage serveur, InnoDB va reinitialiser la partition et vous aurez tout perdu. Depuis la version 3.23.44, par mesure de sécurité, InnoDB empêche les utilisateurs de modifier des données dans une partition newraw.

Une fois que InnoDB a inialisé la nouvelle partition, stoppez le serveur, remplacez newraw dans le fichier d'options par raw :

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:5Graw;/dev/hdd2:2Graw

Puis relancez le serveur et InnoDB va permettre les modifications dans cet espace de table.

Sous Windows, depuis la version 4.1.1, vous pouvez allouer une partition de disque comme ceci :

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=//./D::10Gnewraw

Le //./ correspond à la syntaxe Windows de \\.\, pour accéder aux disques physiques.

Lorsque vous utilisez une partition raw disque, assurez vous que vous avez les permissions qui permettent de lire et écrire avec le compte qui fait tourner le serveur.

15.15.3 Gestion de l'espace fichier

Les fichiers de données que vous définissez dans le fichier de configuration forment l'espace de données InnoDB. Les fichiers sont simplement concaténés pour former un espace de données, et il n'y a pas de parallélisme utilisé. Actuellement, vous ne pouvez pas spécifier où l'espace est alloué pour vos tables, hormis en utilisant la méthode suivante : pour chaque nouvel espace de données créé, InnoDB va allouer de l'espace en partant depuis la fin.

L'espace de tables est constitué de pages de taille 16 ko. Les pages sont groupées en ensembles de 64 pages consécutives. Les 'fichiers' dans un espace de tables sont appelés des segments en InnoDB. Le nom du segment d'annulation est un peu trompeur, car il contient en fait de nombreux segments dans l'espace de table.

Pour chaque index de InnoDB, nous allons créer deux segments : un pour les noeuds non terminaux du B tree, et un autre pour les noeuds terminaux. L'idée ici est d'améliorer la séquence des noeuds terminaux, qui contiennent les données.

Lorsqu'un segment croit dans un espace de table, InnoDB alloue les 32 premières pages spécifiquement pour un segment. Après cela, InnoDB commence à allouer des extensions au segment. InnoDB peut ajouter aux grands segments jusqu'à 4 extension en même temps, pour améliorer la séquence de données.

Certaines pages dans l'espace de table contiennent des cartes des autres pages, et donc, quelques extensions dans un espace de table InnoDB ne pourront pas être allouées en tant que segment, mais comme pages individuelles.

Lorsque vous exécutez une commande SHOW TABLE STATUS FROM ... LIKE ... pour demander quel est l'espace libre dans la table, InnoDB va rapporter les extensions qui sont encore totalement libre. InnoDB se réserve toujours quelques extensions pour la gestion interne. Ces extensions réservées ne sont pas inclues dans l'espace libre.

Lorsque vous effacez des donnes d'une table, InnoDB va contracter les index B-tree correspondants. Suivant les effacements qui vont libérer des pages individuelles ou des extensions, de l'espace sera rendu aux autres tables. Effacer une table ou en effacer toutes les lignes va rendre obligatoirement de l'espace aux autres tables, mais souvenez vous que les lignes effacées ne pourront être physiquement effacée qu'après une opération de purge, si aucune transaction n'en a plus besoin.

15.15.4 Défragmentation des tables

S'il y a plusieurs insertions et suppressions dans les index d'une table, les index peuvent devenir fragmentés. Par fragmentation, nous voulons dire que l'ordre physique de la page d'index n'est pas proche de l'ordre alphabétique des enregistrements dans les pages, ou qu'il y a plusieurs pages non-utilisées dans le bloc de 64 pages qui ont été allouées à l'index.

Une manière d'accélérer les index est d'extraire périodiquement les données de la table dans un fichier avec mysqldump, d'effacer la table puis de la recréer.

Une autre manière de défragmenter consiste à exécuter un ALTER sur le type de la table pour le changer en MyISAM puis de le repasser en InnoDB.

ALTER TABLE tbl_name TYPE=InnoDB

Cela fait que MySQL va reconstruire la table. Un autre moyen de réaliser la défragmentation est d'utiliser l'utilitaire mysqldump pour exporter la table vers un fichier texte, effacer la table, la recréer et importer le fichier de données.

Si les insertions dans un index sont toujours ascendantes et que les lignes supprimées le sont à la fin du fichier, alors l'algorithme de gestion de l'espace fichiers de InnoDB garantit qu'aucune fragmentation n'aura lieu dans l'index.

15.16 Gestion des erreurs InnoDB

La gestion des erreurs avec InnoDB n'est pas toujours la même que celle spécifiée par les standards ANSI SQL. En accord avec les standards ANSI, toute erreur survenant durant une commande SQL devrait provoquer l'annulation de celle-ci. InnoDB n'annule qu'une partie de la commande de temps en temps, ou la totalité de la transaction. La liste suivante spécifie la gestion des erreurs de InnoDB.

  • Si vous dépassez l'espace d'un fichier dans l'espace des tables, vous obtiendrez l'erreur MySQL 'Table is full' et InnoDB annulera la requête SQL.

  • Un deadlock de transaction ou un dépassement de temps dans un attente de verrouillage fait annuler toute la transaction à InnoDB.

  • Une erreur de clef dupliquée annule l'insertion de la ligne concernée, même dans une requête du genre INSERT INTO ... SELECT .... Cela fera probablement en sorte que la commande SQL sera annulée si vous n'avez pas spécifié l'option IGNORE dans votre requête.

  • Une erreur 'row too long' annule la commande SQL.

  • Les autres erreurs sont la plupart du temps détectées au niveau de la couche MySQL du code et annulent la commande SQL correspondante.

15.16.1 Codes d'erreurs InnoDB

Voici une liste non-exhaustive des erreurs courantes et spécifiques à InnoDB que vous pouvez rencontrer, avec des détails pour les corriger.

  • 1005 (ER_CANT_CREATE_TABLE)

    Impossible de créer la table. Si le message d'erreur fait référence à une erreur de code errno 150, la création de la table a échoué à cause d'une contrainte de clé étrangère, qui n'est pas correctement formée.

  • 1016 (ER_CANT_OPEN_FILE)

    Impossible de trouver le fichier de table InnoDB dans les fichiers de données InnoDB alors que le fichier .frms. See Section 15.18.1, « Solutions pour le dictionnaire de données InnoDB ».

  • 1114 (ER_RECORD_FILE_FULL)

    InnoDB n'a plus d'espace libre dans l'espace de table. Essayez de reconfigurer l'espace de table, et d'ajouter un nouveau fichier de données.

  • 1205 (ER_LOCK_WAIT_TIMEOUT)

    Le délai d'expiration du verrou a été dépassé. La transaction a été annulée.

  • 1213 (ER_LOCK_DEADLOCK)

    Blocage de transactions. Vous devriez relancer la transaction.

  • 1216 (ER_NO_REFERENCED_ROW)

    Vous essayez d'ajouter une ligne, mais il n'y a pas de ligne parente, et une contrainte de clé étrangère échoue. Vous devez ajouter le parent en premier.

  • 1217 (ER_ROW_IS_REFERENCED)

    Vous essayez d'effacer une ligne parent qui a des enfants, et une contrainte de clé étrangère échoue. Vous devez effacer la ligne fille en premier.

15.16.2 Codes d'erreur sytème

Sous Unix, pour connaître la signification d'un message d'erreur systè,e, utilisez le programme perror qui est livré avec la distribution MySQL.

La table suivante fournit une liste de certains code d'erreur système Linux. Pour une version complète, voyez Linux source code.

  • 1 (EPERM)

    Operation not permitted

  • 2 (ENOENT)

    No such file or directory

  • 3 (ESRCH)

    No such process

  • 4 (EINTR)

    Interrupted system call

  • 5 (EIO)

    I/O error

  • 6 (ENXIO)

    No such device or address

  • 7 (E2BIG)

    Arg list too long

  • 8 (ENOEXEC)

    Exec format error

  • 9 (EBADF)

    Bad file number

  • 10 (ECHILD)

    No child processes

  • 11 (EAGAIN)

    Try again

  • 12 (ENOMEM)

    Out of memory

  • 13 (EACCES)

    Permission denied

  • 14 (EFAULT)

    Bad address

  • 15 (ENOTBLK)

    Block device required

  • 16 (EBUSY)

    Device or resource busy

  • 17 (EEXIST)

    File exists

  • 18 (EXDEV)

    Cross-device link

  • 19 (ENODEV)

    No such device

  • 20 (ENOTDIR)

    Not a directory

  • 21 (EISDIR)

    Is a directory

  • 22 (EINVAL)

    Invalid argument

  • 23 (ENFILE)

    File table overflow

  • 24 (EMFILE)

    Too many open files

  • 25 (ENOTTY)

    Inappropriate ioctl for device

  • 26 (ETXTBSY)

    Text file busy

  • 27 (EFBIG)

    File too large

  • 28 (ENOSPC)

    No space left on device

  • 29 (ESPIPE)

    Illegal seek

  • 30 (EROFS)

    Read-only file system

  • 31 (EMLINK)

    Too many links

La table suivante fournit une liste de certains code d'erreur système Windows. Pour une version complète, voyez Microsoft website.

  • 1 (ERROR_INVALID_FUNCTION)

    Incorrect function.

  • 2 (ERROR_FILE_NOT_FOUND)

    The system cannot find the file specified.

  • 3 (ERROR_PATH_NOT_FOUND)

    The system cannot find the path specified.

  • 4 (ERROR_TOO_MANY_OPEN_FILES)

    The system cannot open the file.

  • 5 (ERROR_ACCESS_DENIED)

    Access is denied.

  • 6 (ERROR_INVALID_HANDLE)

    The handle is invalid.

  • 7 (ERROR_ARENA_TRASHED)

    The storage control blocks were destroyed.

  • 8 (ERROR_NOT_ENOUGH_MEMORY)

    Not enough storage is available to process this command.

  • 9 (ERROR_INVALID_BLOCK)

    The storage control block address is invalid.

  • 10 (ERROR_BAD_ENVIRONMENT)

    The environment is incorrect.

  • 11 (ERROR_BAD_FORMAT)

    An attempt was made to load a program with an incorrect format.

  • 12 (ERROR_INVALID_ACCESS)

    The access code is invalid.

  • 13 (ERROR_INVALID_DATA)

    The data is invalid.

  • 14 (ERROR_OUTOFMEMORY)

    Not enough storage is available to complete this operation.

  • 15 (ERROR_INVALID_DRIVE)

    The system cannot find the drive specified.

  • 16 (ERROR_CURRENT_DIRECTORY)

    The directory cannot be removed.

  • 17 (ERROR_NOT_SAME_DEVICE)

    The system cannot move the file to a different disk drive.

  • 18 (ERROR_NO_MORE_FILES)

    There are no more files.

  • 19 (ERROR_WRITE_PROTECT)

    The media is write protected.

  • 20 (ERROR_BAD_UNIT)

    The system cannot find the device specified.

  • 21 (ERROR_NOT_READY)

    The device is not ready.

  • 22 (ERROR_BAD_COMMAND)

    The device does not recognize the command.

  • 23 (ERROR_CRC)

    Data error (cyclic redundancy check).

  • 24 (ERROR_BAD_LENGTH)

    The program issued a command but the command length is incorrect.

  • 25 (ERROR_SEEK)

    The drive cannot locate a specific area or track on the disk.

  • 26 (ERROR_NOT_DOS_DISK)

    The specified disk or diskette cannot be accessed.

  • 27 (ERROR_SECTOR_NOT_FOUND)

    The drive cannot find the sector requested.

  • 28 (ERROR_OUT_OF_PAPER)

    The printer is out of paper.

  • 29 (ERROR_WRITE_FAULT)

    The system cannot write to the specified device.

  • 30 (ERROR_READ_FAULT)

    The system cannot read from the specified device.

  • 31 (ERROR_GEN_FAILURE)

    A device attached to the system is not functioning.

  • 32 (ERROR_SHARING_VIOLATION)

    The process cannot access the file because it is being used by another process.

  • 33 (ERROR_LOCK_VIOLATION)

    The process cannot access the file because another process has locked a portion of the file.

  • 34 (ERROR_WRONG_DISK)

    The wrong diskette is in the drive. Insert %2 (Volume Serial Number: %3) into drive %1.

  • 36 (ERROR_SHARING_BUFFER_EXCEEDED)

    Too many files opened for sharing.

  • 38 (ERROR_HANDLE_EOF)

    Reached the end of the file.

  • 39 (ERROR_HANDLE_DISK_FULL)

    The disk is full.

  • 112 (ERROR_DISK_FULL)

    The disk is full.

  • 123 (ERROR_INVALID_NAME)

    The filename, directory name, or volume label syntax is incorrect.

  • 1450 (ERROR_NO_SYSTEM_RESOURCES)

    Insufficient system resources exist to complete the requested service.

15.17 Restrictions sur les tables InnoDB

  • Une table ne peut pas contenir plus de 1000 colonnes.

  • La taille maximale d'une clé est de 1024 octets.

  • La taille maximale d'une ligne, hormis pour les colonnes de type BLOB et TEXT, et légèrement inférieure à la moitié d'une page de base, c'est à dire, que la taille maximale d'une ligne est d'environs 8000 octets. Les colonnes LONGBLOB et LONGTEXT doivent être un peut plus petite que 4Go, et la taille totale d'une ligne, incluant les colonnes BLOB et TEXT doivent être de 4 Go. InnoDB stocke les 512 premiers octets des valeurs BLOB et TEXT dans la ligne, et le reste dans une page séparée.

  • Sur certains systèmes d'exploitation, le fichier de données est limité à 2 Go.

  • La taille combinée des fichiers de log doit être inférieure à 4 Go.

  • La taille minimale d'un espace de tables est de 10Mo. La taille maximale d'un espace de tables est de 4 milliards de pages de bases (64To). C'est aussi la taille maximal d'une table.

  • Les tables InnoDB ne supportent pas les index FULLTEXT.

  • Sous Windows, InnoDB stocke les noms de bases de données et les noms de tables en interne, et en minuscule. Pour passer une base au format binaire de Unix à Windows, ou le contraire, vous devez créer toutes vos bases et tables en minuscules.

  • Attention : NE convertissez PAS les tables de droits du format MyISAM en InnoDB! Cela n'est pas supporté. Si vous faîtes cela, MySQL ne va pas redémarrer jusqu'à ce que vous restauriez vos données avec une vieille sauvegarde, ou que vous regénériez ces tables avec le script mysql_install_db.

  • InnoDB ne conserve pas de compte interne de ligne pour une table. Cela serait en fait compliqué, à cause du multi-versionnage. Pour traiter une commande SELECT COUNT(*) FROM T, InnoDB doit scanner l'index de la table, ce qui prendra du temps si la table n'est pas enregistrée dans le buffer. Pour accélérer le compte, vous devez créer un compteur de table vous-même, et votre application le modifiera à chaque ajout ou suppression. Si votre table ne change pas souvent, l'utilisation du cache sera une bonne solution. SHOW TABLE STATUS peut aussi être utilisé pour obtenir un décompte approximatif des lignes. See Section 15.12, « Conseils pour l'amélioration des performances InnoDB ».

  • Pour une colonne AUTO_INCREMENT, vous devez toujours définir un index pour la table, et cet index doit contenir uniquement la colonne AUTO_INCREMENT. Dans les tables MyISAM, la colonne AUTO_INCREMENT peut faire partie d'un index multi-colonne.

  • InnoDB ne supporte pas l'option de configuration initiale des colonnes AUTO_INCREMENT dans les commandes CREATE TABLE ou ALTER TABLE. Pour configurer cette valeur avec une table InnoDB, insérez une ligne avec une valeur inférieure d'une unité à votre valeur de départ, ou bien insérez la première ligne en spécifiant la première valeur.

  • Lorsque vous redémarrez le serveur MySQL, InnoDB peut réutiliser une ancienne valeur de la séquence AUTO_INCREMENT (c'est à dire, une valeur qui a été assignée à une transaction annulée).

  • Lorsque la colonne AUTO_INCREMENT n'a plus de valeurs, InnoDB passe le BIGINT à -9223372036854775808 et les BIGINT UNSIGNED à 1. Cependant, les valeurs BIGINT sont codées sur 64 bits, alors même si vous insérez 1 million lignes par seconde, cela vous prendra un million d'années avant d'atteindre la limite des BIGINT. Avec les autres types de colonnes, une erreur de clé doublon sera émise. C'est identique au fonctionnement des tables MyISAM, le comportement générale de MySQL, et ce n'est pas caractéristique d'un moteur spécifique.

  • DELETE FROM TABLE ne regénère pas la table, mais au lieu de cela, il efface les lignes une à une, ce qui est bien plus lent. Dans les prochaines versions, MySQL va pouvoir utiliser la commande TRUNCATE qui est très rapide.

  • TRUNCATE tbl_name est synonyme de DELETE FROM tbl_name pour InnoDB et ne remet pas à zéro le compteur de AUTO_INCREMENT.

  • SHOW TABLE STATUS ne donne pas de statistiques exactes pour les tables InnoDB, hormis pour la taille physique réservée par la table. Le nombre de lignes n'est qu'une estimation utilisée pour les optimisations SQL.

  • Si vous essayez de créer un index unique sur un préfixe d'une colonne, vous allez obtenir une erreur :

    CREATE TABLE T (A CHAR(20), B INT, UNIQUE (A(5))) TYPE = InnoDB;
    

    Si vous créez un index non-unique sur un préfixe de colonne, InnoDB va créer un index pour toute la colonne.

    Ces restrictions sont levées depuis les versions 4.0.14 et 4.1.1.

  • INSERT DELAYED n'est pas supportés par les tables InnoDB.

  • La commande MySQL LOCK TABLES ne reconnait pas le verrouillage de ligne InnoDB réalisé dans les commandes SQL achevées : cela signifie que vous pouvez poser un verrou sur une table même si il existe une transaction qui a été posée par un autre utilisateur. Par conséquent, votre opération doit attendre que les autres tables soient libres, et elle peut aussi entrer en conflit avec une autre requête. De plus, un blocage de verrous est possible mais il ne met pas en danger l'intégrité des transactions, car le verrou de ligne posé par InnoDB se charge toujours de l'intégrité. Enfin, un verrou de table évite aux autre transactions de poser un verrou de ligne (en conflit avec le mode de verrous) sur la table.

  • Avant MySQL 3.23.52, la réplication fonctionnait toujours en mode d'auto-validation. Par conséquent, les lectures cohérentes de l'esclaves voyait aussi les transactions partiellement traitées, et la cohérence n'était pas assurée. Cette restriction a été levée en MySQL 3.23.52.

  • La commande LOAD TABLE FROM MASTER de configuration de la réplication ne fonctionne pas pour les tables InnoDB. Un palliatif consiste à modifier la table en MyISAM sur le maître, faire la configuration, puis repasser la table en format InnoDB.

  • La taille par défaut d'une page de base avec InnoDB est de 16Ko. En recompilant le code, vous pouvez donner une valeur allant de 8Ko à 64Ko. Vous mettre à jour les valeurs des constantes UNIV_PAGE_SIZE et UNIV_PAGE_SIZE_SHIFT dans le fichier univ.i.

15.18 Résolution de problèmes avec InnoDB

  • Une règle générale est que lorsqu'une opération échoue ou que vous soupsonnez un bug, vous devez lire le fichier de log d'erreurs de MySQL, qui porte généralement le nom de hostname.err, ou, sous Windows, mysql.err.

  • Lorsque vous recherchez une solution, il est généralement bon de faire tourner le serveur depuis la ligne de commande, et non pas avec le script mysqld_safe ou comme service Windows. Vous verrez ce que mysqld affiche directement dans le terminal, et vous aurez une meilleure idée de ce qui se passe. Sous Windows, vous devez lancer le serveur avec --console pour voir le résultat dans la console.

  • Utilisez le moniteur InnoDB pour avoir des informations sur un problème. Si le problème est lié aux performances, ou si votre serveur semble gelé, vous devriez utiliser innodb_monitor pour afficher les informations sur l'état interne de InnoDB. Si le problème est lié aux verrous, utilisez innodb_lock_monitor. Si le problème est lié à la création de table ou lié aux dictionnaires de données, utilisez innodb_table_monitor pour voir le contenu du dictionnaire de données internes d'InnoDB.

  • Si vous suspectez qu'une table est corrompue, utilisez la commande CHECK TABLE sur cette table.

15.18.1 Solutions pour le dictionnaire de données InnoDB

Un problème spécifique avec les tables est que le serveur MySQL garde les données dans un dictionnaire de données .frm, qu'il stocke dans le dossier de base de données, alors que InnoDB stocke aussi les informations dans son propre dossier de données dans l'espace de tables. Si vous déplacez le fichier .frm, ou si vous utilisez DROP DATABASE en MySQL versions 3.23.44 et plus anciennes, ou si le serveur crashe au milieu d'une opération dans le dictionnaire de données, le fichier .frm peut être déconnecté du dictionnaire de données interne InnoDB.

Un symptôme de ce déphasage est que les commandes CREATE TABLE échouent. Si cela arrive, vous devez lire le fichier de log d'erreurs. Si le fichier de log dit que la table existe déjà dans le dictionnaire interne d'InnoDB, vous avez un fichier de table orphelin dans l'espace de table InnoDB, qui n'a plus de fichier .frm correspondant. Le message d'erreur ressemble à ceci :

InnoDB: Error: table test/parent already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

Vous pouvez effacer le fichier orphelin en suivant les instructions du message d'erreur.

Un autre symptôme de déphasage st que MySQL affiche MySQL une erreur qui dit qu'il ne peut pas ouvrir le fichier .InnoDB :

ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)

Dans les logs d'erreurs, vous trouverez le message suivant :

InnoDB: Cannot find table test/child2 from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?

Cela signifie qu'il y a un fichier .frm orphelin sans table correspondante dans les données InnoDB. Vous pouvez effacer simplement le fichier orphelin .frm.

Si MySQL crashe au milieu d'une opération ALTER TABLE, vous pouvez vous retrouver avec un fichier temporaire de table, orphelin, dans le code InnoDB. Avec innodb_table_monitor vous pouvez voir une table dont le nom est #sql..., mais comme MySQL ne vous permet pas d'accéder à une table avec un tel nom vous ne pouvez ni l'effacer, ni la détruire. La solution est d'utiliser un mécanisme spécial, disponible depuis MySQL 3.23.48.

Lorsque vous avez une table orpheline #sql_id dans un espace de table, vous pouvez faire que InnoDB le renomme en rsql_id_recover_innodb_tmp_table avec la commande :

CREATE TABLE `rsql_id_recover_innodb_tmp_table`(...) TYPE=InnoDB;

Les guillemets obliques autour du nom de table sont nécessaires car une table temporaire contient un tiret ‘-’.

La définition de la table doit être similaire à celle de la table temporaire. Si vous ne connaissez pas la définition de la table temporire, vous pouvez utiliser une définition arbitraire dans la commande CREATE TABLE précédente, et ensuite, remplacer le fichier rsql_id.frm par le fichier #sql_id.frm de la table temporaire. Notez que pour copier ou pour renommer un fichier dans le shell, vous devez mettre le nom du fichier entre guillemets, car le nom du fichier contient ‘#’. Alors, vous pouvez exporter et effacer la table renommée.