MySQL Innodb nouvelle notes de configuration et d’optimisation

Pour compléter une note précédente sur Innodb et UTF8 et le billet sur les premières notes de configuration et d’optimisation de MySQL Innodb voici quelques notes de configuration de MySQL supplémentaire et complémentaire avec le moteur de base de donnée Innodb. Note toujours valable _ pour toute solution d’hébergement ou architecture MySQL est dédier eZ Publish, Dotclear 2 et autre CMS.

INNODB_BUFFER_POOL_SIZE

  • Espace mémoire de mise en cache des bases de données.
  • Ne pas hésiter à allouer 80 ~ 90% de la mémoire disponible sur le serveur si celui ci est dédier DB
  • Dans la mesure du possible doit être légèrement supérieur en taille à /var/lib/mysql

INNODB_FLUSH_METHOD

  • Instruction de gestion des accès disque
  • CODE_FS par défaut, laisse à l’OS toute possibilité de gérer les accès selon ces principes. L’OS peux donc utilisé son propre cache qui viendrais ce superposer à celui de innodb
  • O_DIRECT informe l’OS d’écrire sans délai sans tenir compte de son cache.
  • A rapprocher de l’instruction gérant le scheduler de l’OS cf: /sys/block/sda/queue/scheduler

QUERY-CACHE

  • Concerne les instructions query_cache_limit, query_cache_size, query_cache_type
  • A utilise seulement en cas de requêtes uniforme et répétitive, inutile si celle ci sont déporter sur un memcache
  • Effacer dans ce cache et y réécrire impose un MUTEX et donc un lock de tout les thread.
  • A utilisé seulement si le gain d’utilisation VS lock mutex est positif, rarement le cas sur une architecture qui utilise du memcache

SINGLE-TRANSACTION

  • mysqldump single-transaction all-databases > dump.sql
  • Instruction –single-transaction determine que le dump est a réaliser dans le contexte de l’instant T (snapshot style)
  • Ne génère pas de lock, lecture, écriture toujours possible
  • Génère une charge en lecture. Charge quasi nul sur tout le contenue de base dumper est en mémoire
  • Génère une charge en écriture. Penser à écrire sur une FileSystem non sollicité.

12 réflexions sur “ MySQL Innodb nouvelle notes de configuration et d’optimisation ”

  1. Hello,

    As-tu essayé d’avoir un paramètre innodb_buffer_pool_size de plus de 2 Go ? Dans la doc de MySQL, il était indiqué que cela pouvait poser problème sur certains systèmes et sur un socle Debian Etch / MySqL 4.1, cela conduisait à des crashs.

    Je n’ai pas eu l’occasion de tester à ce jour sous RHEL 5.3 64bits.

    Pour innodb_flush_method, c’est pas plutôt fdatasync par défaut et comme valeur O_DIRECT, O_DSYNC

    Cf : http://dev.mysql.com/doc/refman/5.1

    Sinon c’est plutot /sys/block/sda/queue/scheduler (sda au lieu de sda1)

    ++
    Nicolas

  2. Bonjour Nicolas, j’ai des innodb_buffer_pool à 5 ou 6 Go sans problème avec Mysql 5.0 sous Ubuntu

    Pour innodb_flush_method, sur l’image Ubuntu 8.0 AWS, mon cas d’étude, j’ai systématiquement CODE_FS

    J’ai corriger pour le scheduler

    Merci

  3. Hmm c’est louche quand je fais une recherche Google sur CODE_FS, j’ai rien du tout qui remonte hormis ton billet 😉

    Merci pour le retour d’XP sur innodb_buffer_pool_size 🙂

  4. Nicolas tu a raison, je viens de relire mes notes gribouille et de vérifier mes serveurs, j’ai fais une erreur de transcription au pire j’ai la variable vide donc avec le paramètre par défaut fdatasync. Je vois pas ou l’ai pris CODE_FS, une fuite mémoire sans doute.

  5. C’est sur des systèmes avec un noyau Linux 32bits qu’on ne peut pas allouer plus de 2Go par processus. Aucun souci avec du 64bits, et bien meilleures perfs.

    Karles, si ça t’intéresse, j’ai pas mal d’optis InnoDB.

    Déjà : 64 bits, MySQL 5.1, plugin InnoDB plutôt que la version built-in, compilation avec les atomiques gcc, utilisation de tcmalloc. C’est un minimum vital pour ne pas être à la ramasse.

    Ensuite, pour tout le tuning InnoDB, il faudra me payer un café ou une bière 🙂

  6. Oui gui, 64bits obligatoire, j’abonde.

    Mysql 5.1 ok, pour le reste je suis au fraise.

    Pour la biere passe demain midi si tu veux, y’a Kathryl déjà qui s’invite.

  7. Merci karles pour ces astuces, si tu en as d’autres, n’hésites pas 🙂
    Bon, comme tu payes ta tournée, je peux venir aussi ? Et si j’abuse pas trop, tu peux me payer le train ? Je n’habite pas à côté 😉

  8. Olivierje paye pas ma tourné j’ai rendez vous avec un ancien padawan et j’invite un ancien fournisseur…nuance :p

  9. Démonstration de mes précédents propos : comparaison tpc-c entre la version embarquée de InnoDB et le plugin InnoDB sur une instance Amazon c1.xlarge et du MySQL 5.1.43. Pas de tuning particulier, donc il y a encore à gagner…

    http://twitpic.com/140sy9

    • en abscisses, le temps : 20 minutes de mesure avec 10 minutes de chauffe
    • en ordonnées, le nombre de transactions

    +100% !!!

  10. J’ai pris une version 5.1.43 de MySQL comme référence. version amd64 de Dotdeb.

    L’instance, c’est une c1.xlarge donc 7Go de RAM, 20 unités CPU EC2, et de bonnes I/O. Je n’ai pas du tout optimisé la configuration pour tirer au maximum parti de la machine, notamment la RAM. Le query_cache et le buffer_pool ne sont pas correctement exploités.

    Le gain est certes impressionnant, juste avec un point de configuration, mais il y a encore moyen de gagner des perfs.

    Je suis disponible pour vous faire une petite mission de conseil, si vous voulez :-p En attendant, je pousse un peu plus loin mes benchs.

Laisser un commentaire