Vous êtes ici

Mysql : Optimisation de la configuration Mysql 5.*

Nous allons voir dans cet article comment rendre le processus Mysql (à partir de la version 5) plus performant en lui optimisant quelques paramètres de configurations. Nous allons se concentrer sur la configuration de son moteur transactionnel Innodb. Nous partons du principe que seul le moteur Innodb est utilisé, l'utilisation de plusieurs moteurs sur la même instance est fortement déconseillé, car il faut réserver de la mémoire pour chacun d'entre eux, effectivement, rien ne sera commun à ce niveau.

Voici une liste primordiale de paramètre à positionner dans votre fichier my.cnf avec l'explication du comment les régler :

- innodb_log_buffer_size : Ce paramètre doit être dans l'idéal positionné à 18MB. Il permet d'écrire dans les logbin tous les 18MB, si il est trop faible, il y aura beaucoup d'écritures sur disque, et, donc une montée des IOPS ce qui n'est pas bon pour les performances.

- innodb_buffer_pool_size : C'est clairement l'un des paramètres ou nous allons accorder le plus de RAM, c'est celui qui définit le cache mémoire que Mysql pourra allouer. L'idéal est de 25% de la RAM. Cependant l'idéal est que la totalité des index puissent être mis en cache. Donc une manière de calcul est donc de vérifier la taille total des index, et d'allouer suffisamment de mémoire en conséquence. (Note : c'est pour cela qu'il faut correctement définir ces index, trop d'index ne pourront pas être allouer en RAM en totalité, en règle général, les index ne doivent pas prendre plus de 10% de la taille de la table).

- innodb_flush_log_at_trx_commit = 1 : En l'état, ce paramètre signifie que chaque transaction est inscrite sur le disque (dans les fichiers journaux par exemple), même en cas de cache non rempli. Le mettre à 0 désactive cette écriture permanente, et donc, augmente considérablement le nombre de transaction par secondes en mode écriture (jusqu'à 10 fois), cependant si il est désactivé, en cas de crash serveur, les données présentent en RAM seront définitivement perdues, ce qui signifie que les dernières transactions jouées aussi, attention donc lorsque vous désactivez ce paramètre.

- innodb_log_files_in_group = 2 : Ici, signifie que nous utilisons 2 fichiers journaux, c'est la paramètre par défaut, et, il est insuffisant. Il faut au minimum en utiliser 3 ou 4 pour 2 raisons, la première est que cela limite le roulement entre les fichiers journaux et donc augmente les performance car moins d'IOPS. La seconde, est que mysql a besoin de 2 fichiers journaux au minimum pour fonctionner, donc la perte d'un fichier entraine le crash, avec plus de 2 fichiers journaux, la perte d'un fichier n'engendre pas de crash.

- innodb_log_file_size = 48MB : également le paramètre d'origine, et, également insuffisant. 48MB implique que tous les 48MB un déchargement du cache est fait sur le disque à cause du checkpoint, c'est beaucoup trop régulier, et, donc augmente dangereusement le nombre d'IOPS. Il est conseillé de mettre au minimum 150MB, sachant qu'il n'est pas un luxe de monter à 512MB sur de grosses bases fortement sollicitées.

- innodb_data_file_path = ibdata1:12M:autoextend : 12M, c'est la taille du fichier de rollback segment (les redologs). Même si en autoextend, c'est trop petit, car le temps que le système va mettre à allouer l'espace sur disque, c'est du temps perdu car des IOPS en trop.
Attention, avant la version 5.6.6 de mysql, ce fichier contient également les data, il faut donc impérativement passer le paramètre innodb_file_per_table à ON (innodb_file_per_table = ON) ce qui signifie que pour chaque table un fichier ibdata sera créé et que seul les redologs seront contenu dans le fichier iddata1. Avoir plusieurs petit fichier pour la table offre plus de performance qu'un énorme fichier qui contient toutes les tables (surtout si il y a des tables de grosse taille jamais utilisés), ça évite les points de contention.
Le fichier de redologs doit faire au moins 512MB, attention ! le modifier va entrainer un non redémarrage de l'instance, car il ne pourra pas le réallouer, 2 solutions :
* Le supprimer, dans ce cas le système va le recréer, mais vous ne pouvez pas savoir si il à déjà contenu de la données sur une vieille base, donc risquer
* en allouer un second : ibdata1:12M;ibdata2:512M:autoextend , seul l'un des 2 fichiers peut être en autoextend

- innodb_flush_method = O_DIRECT : par défaut ce paramètre n'est pas renseigné. Si pas renseigné, cela signifie qu'à chaque écriture disque, le processus mysql va attendre l'acquittement des disques pour valider l'écriture, perte de temps, notamment sur des contrôleurs performant avec cache et batterie qui gère très bien cette partie. Dans ce cas, passer le paramètre à O_DIRECT, dans ce cas le processus mysql va écrire sur le disque et ne va plus attendre l'acquittement de celui ci pour continuer. Gain de performance non négligeable sur de grosse quantité d'écriture.

- sort_buffer_size : Pour modifier ce paramètre, il faut surveiller les slow logs, si il y a une présence importante de requêtes lentes sur du GROUP BY ou du ORDER BY correctement indexés, c'est que ce paramètre est trop faible, il faut augmenter la taille du buffer.

- tmp_table_size : Définit la taille des tables temporaires, si trop faible, les tables temporaires seront écrites sur disque, et donc, perte de performance non négligeable, à régler selon votre utilisation des tables temporaires. Par exemple 300MB, si une table temporaire prend plus de place, elle passera automatiquement sur disque.

- join_buffer_size : Comme pour le sort, si trop de requêtes lentes comportant des jointures uniquement non indexés, augmenter la taille de ce buffer. Uniquement valable pour les requêtes non indéxés !

- read_buffer_size : Ce cache est uniquement utilisé pour le full scan. Dans l'idéal, ce paramètre doit prendre la taille des partitions ou tables régulièrement soumises au Full scan.

- query_cache_size : C'est le second plus gros cache que nous devrons paramétrer. Il contient le résultat des requêtes fréquentes. Il doit dans le meilleur des cas être paramétré à une quantité supérieure à 25% de la RAM.

- table_cache ou table_open_cache selon la version = 128Mo


Une fois tous ces paramètres correctement mises en places, vous devriez constater un gain de performance non négligeable. Je vous conseil d'utiliser fréquemment sysbench pour vérifier votre TPS ( transactions par secondes ) avant et après modification et également régulièrement pendant toutes la vie de votre instance pour en vérifier les performances.

Tags: 

www.le-gas.fr : Le guide de l'admin Systeme