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.
• (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 focntion 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;

  et après vos commandes :

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

Sommaire :

• Le moniteur InnoDB