16.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 modifiations 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 . 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.

Sommaire :

• Le moniteur InnoDB