Comprendre son fichier de configuration – 3è partie

17 février 2012 par stephane

Dernier volet dans notre série sur la configuration de MySQL, cet article va vous donner les clés pour paramétrer correctement et simplement InnoDB.

InnoDB est un moteur extrêmement complexe qui mériterait un livre complet pour expliquer son fonctionnement. Selon les versions, il peut exister plus de 80 paramètres pour contrôler son fonctionnement : pas question de les examiner tous ! Je vais me concentrer sur les 2 principaux, qui devraient suffir dans la majorité des cas.

Le 1er, innodb_buffer_pool_size, contrôle la taille du cache mémoire pour les données et les index. Il s’agit assurément du paramètre de base pour obtenir de bonnes performances InnoDB puisqu’il vous permet de remplacer de nombreuses lectures ou écritures sur disque par des lectures ou écritures en mémoire. Pour le configurer, c’est simple, essayez de mettre la valeur la plus élevée possible… Evidemment, il faut penser à garder un peu de mémoire pour les connexions, les tables temporaires, les éventuelles tables MyISAM… et l’OS !

Le 2nd, innodb_log_file_size, contrôle la taille des redo logs. A quoi servent ces redo logs en quelques mots ? Pour des raisons de performance, quand un client fait une modification dans une table, InnoDB exécute la modification en mémoire instantanément (dans le fameux buffer pool que nous avons appris à configurer ci-dessus) mais garde pour plus tard l’exécution de la modification sur le disque.
Que se passe-t-il si le serveur subit un crash entre le moment où la donnée est écrite en mémoire et le moment où la donnée est écrite sur le disque ? La donnée est perdue tout simplement. Il faut donc un mécanisme supplémentaire pour assurer la persistence des données en cas de crash : ce sont les fameux redo logs. En réalité, au moment où on exécute une modification, InnoDB l’exécute en mémoire et la note également dans ses redo logs avant de signaler au client que la modification est bien enregistrée. En cas de crash, la lecture des redo logs va permettre de reconstituer les données qui étaient en mémoire mais pas encore sauvegardées sur le disque.

Quel est l’intérêt des redo logs par rapport à une écriture directe dans le fichier de données ? En général, les modifications de données successives ont lieu à des endroits radicalement différents, ce qui se traduit pour le disque par de nombreuses écritures aléatoires, extrêmement lentes. Au contraire, écrire des modifications successives dans les redo logs consiste à écrire chaque modification à la fin du fichier : il s’agit là d’écritures séquentielles, nettement plus rapides. Au moment de retranscrire le contenu des redo logs vers les tables, InnoDB pourra en profiter pour regrouper les écritures pour les rendre les plus séquentielles possibles. Ce mécanisme permet donc d’éviter de nombreuses et coûteuses écritures aléatoires.

Et que se passe-t-il quand les redo logs sont pleins ? Il n’est tout simplement plus possible de faire de modifications ! InnoDB tente donc de nettoyer régulièrement ces logs pour qu’il reste toujours de la place.

Bref, si vous m’avez suivi, les redo logs permettent de gagner en performance en écriture, on a donc tout intérêt à configurer une valeur aussi grande que possible. Cependant, il faut également garder à l’esprit qu’en cas de crash, les redo logs vont être utilisés pour restaurer les données, et cette restauration sera d’autant plus longue que la taille des redo logs sera grande… Il va donc falloir trouver un compromis entre ces 2 exigences contradictoires.

Baron de Percona a écrit un très bon article sur le sujet. Sans reprendre dans le détail ce qu’il explique, le principe de son calcul est de déterminer le volume d’écriture en période de pleine charge et de faire en sorte que les redo logs puisse contenir 1 heure d’écriture à pleine charge.

J’en profite au passage pour vous inciter à la méfiance si vous lisez des articles vous parlant de fixer cette variable à 25% de la taille du buffer pool par exemple. C’est faux et d’ailleurs bien souvent irréalisable : à part en MySQL 5.6 et sur les versions récentes de Percona Server, la taille des redo logs est limitée à 4 Go. Avec un buffer pool de 32 Go ou plus, difficile de respecter cette « règle » des 25 %…

Je fais une petite parenthèse sur le paramètre innodb_file_per_table. Ce paramètre fait en sorte que les données de chaque table soient stockées dans un tablespace spécifique au lieu du tablespace principal. Il ne faut pas attendre de cette option des gains en performance, mais plutôt en flexibilité : si vous avez 200 Go de données, au lieu d’avoir un énorme fichier ibdata de 200 Go, vous aurez autant de fichiers que de tables. De plus, si jamais vous supprimez une table, le tablespace spécifique à la table sera supprimé et vous récupérerez l’espace disque correspondant, ce qui ne sera jamais le cas si vous stockez toutes vos données dans le tablespace principal.

A noter que Percona a mis en ligne un outil vous permettant d’obtenir un fichier de configuration basique (c’est-à-dire sans doute pas optimisé au micro-poil près, mais en tout cas sans grossière erreur pouvant coûter très cher).

Et voilà ! En configurant correctement ces 2 paramètres, vous aurez réalisé l’essentiel du paramétrage d’InnoDB ! Evidemment, ensuite, il est possible d’aller un peu plus loin en jouant sur d’autres paramètres comme innodb_flush_log_at_trx_commit ou innodb_flush_method. Mais ce sera peut-être pour un prochain article !

Mots-clefs : , ,

Laisser une réponse