Remarque : Cet article a ete initialement publie en 2014. Certaines etapes, commandes ou versions de logiciels peuvent avoir change. Consultez la documentation actuelle de MySQL pour les informations les plus recentes.
Dans ce guide etape par etape, vous apprendrez a installer et utiliser MySQLTuner pour optimiser votre configuration / performance MySQL. MySQL est un systeme de gestion de bases de donnees relationnelles open source largement utilise dans les applications web, en particulier avec PHP et WordPress.
Comment : Installer et utiliser MySQLTuner pour optimiser votre configuration / performance MySQL
J’ecris cet article principalement parce qu’il existe a mon avis une bien meilleure facon d’installer MySQLTuner sur votre machine que le habituel apt-get install MySQLTuner. Comme beaucoup d’entre vous le savent probablement deja, MySQLTuner est un outil bien connu et largement utilise. Il vous donne un tres bon resume de la performance de votre configuration et vous indique ou vous pouvez apporter des ameliorations pour obtenir des performances encore meilleures de votre serveur de base de donnees. Je recommande generalement aux gens d’installer Percona (qui est un remplacement direct de MySQL). Percona est base sur le code MySQL mais c’est plus cool (ils ajoutent plus de fonctionnalites de performance que vous ne pourriez trouver que dans la version commerciale de MySQL et ils font un meilleur travail pour rester a jour qu’Oracle). Pour cette raison, j’ai meme ecrit dans l’article : (</databases/mysql/install-mysql-ubuntu-server/> “How to: Install MySQL in an Ubuntu Server”) des instructions sur comment installer Percona au lieu de MySQL a la fin comme alternative suggeree.
Quoi qu’il en soit, comme je l’ai mentionne plus tot, la raison principale de cet article est de se concentrer sur une alternative au simple apt-get install MySQLTuner. Et pourquoi ? Eh bien, voyons ce que l’execution de cette commande a dire sur l’une de mes machines serveur :
apt-get install MySQLTuner Reading package lists… Done Building dependency tree Reading state information… Done The following packages were automatically installed and are no longer required : libperconaserverclient18.1 percona-server-common-5.6 Use ‘apt-get autoremove’ to remove them. The following extra packages will be installed : libhtml-template-perl libterm-readkey-perl mysql-client-5.5 mysql-client-core-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5 Suggested packages: libipc-sharedcache-perl tinyca mailx The following packages will be REMOVED : percona-server-client-5.6 percona-server-server-5.6 The following NEW packages will be installed: libhtml-template-perl libterm-readkey-perl mysql-client-5.5 mysql-client-core-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5 mysqltuner 0 upgraded, 9 newly installed, 2 to remove and 0 not upgraded. Need to get 24.8 MB/24.8 MB of archives. After this operation, 20.4 MB disk space will be freed. Do you want to continue ? N Abort.
Comme souligne en gras, l’installateur essaiera de supprimer le serveur Percona, installera mysql a la place et un tas d’autres bibliotheques. La plupart du temps, c’est bien, mais dans de nombreux cas, vous ne voulez que l’outil et vous etes satisfait de Percona comme je le suis. Alors que faire ?
Heureusement, MySQLTuner est un outil ecrit en Perl et ne necessite que des bibliotheques client pour se connecter a MySQL, ce qui n’est pas un gros probleme (le client Percona est disponible). Allez simplement sur : https://github.com/major/MySQLTuner-perl et telechargez le fichier mysqltuner.pl pour obtenir la derniere version (faites un wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl). Une fois que vous avez telecharge le script, tout ce que vous avez a faire est d’aller a l’emplacement ou vous l’avez sauvegarde et d’executer perl mysqltuner.pl et voila !
Maintenant, si vous cherchez quelques conseils sur la facon d’utiliser cet outil et les modifications recommandees, voici quelques points importants :
I. Si vous n’utilisez que PHP (pensez WordPress), changez definitivement le wait_timeout.
Par defaut, il est de 8 heures, mais si pour une raison quelconque l’application ne ferme pas la connexion, elle pourrait rester la a attendre jusqu’a 8 heures (vous commencez donc a avoir beaucoup de connexions ouvertes en veille qui consomment des ressources).
wait_timeout = 30 #default is 28800
Vous pouvez laisser le interactive timeout tel quel. Il s’agit generalement d’une valeur plus elevee que wait_timeout et comme vous pouvez le deviner, cela necessite une interaction pour rester en etat d’attente.
II. Modifiez le nombre maximum de connexions.
Sur mon installation, c’etait 500 ! Chaque connexion utilise un peu de ressources meme si elle n’est pas utilisee. MySQLTuner peut vous aider a voir au fil du temps quel est le nombre maximum de connexions que vous finissez par utiliser afin que vous puissiez ajuster en consequence. Par exemple, si vous utilisez PHP-FPM, vous n’utiliserez probablement pas plus de connexions que le nombre maximum de processus enfants que vous avez. Tenez compte de cela et examinez la sortie de MySQLTuner pour decider quel est le meilleur nombre. Laissez votre nombre maximum de connexions (apres avoir fait tourner MySQL pendant au moins 24 heures, de preference 7 jours pour obtenir des metriques pour une semaine entiere) a environ 80 % (augmentez donc l’utilisation maximale de 25 % lors de la configuration de cette variable).
max_connections = 50 #default is 500
III. Executez MySQLTuner
Examinons une sortie :
perl mysqltuner.pl >> MySQLTuner 1.3.0 - Major Hayden major@mhtx.net >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with ‘—help’ for additional options and output filtering -------- Security Recommendations ------------------------------------------- All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- Up for: 4h 51m 32s (354K q , 3K conn, TX: 2B, RX: 45M) Reads / Writes: 90% / 10% Total buffers: 420.0M global + 1.1M per thread (26 max threads) Maximum possible memory usage: 447.6M (14% of installed RAM) Slow queries: 0% (56/354K) Highest usage of available connections: 42% (11/26) Key buffer size / total MyISAM indexes: 16.0M/100.0K Key buffer hit rate: 99.0% (100 cached / 1 reads) Query cache efficiency: 78.9% (260K cached / 330K selects) Query cache prunes per day: 0 Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts) Temporary tables created on disk: 36% (3K on disk / 8K total) Thread cache hit rate: 99% (11 created / 3K connections) Table cache hit rate: 28% (363 open / 1K opened) Open file limit used: 0% (48/65K) Table locks acquired immediately: 100% (85K immediate / 85K locks) InnoDB buffer pool / data size: 128.0M/49.3M InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: tmp_table_size (> 30M) max_heap_table_size (> 30M)
Comme je l’ai mentionne plus tot, vous voulez executer cela contre votre serveur au moins 24 heures apres avoir demarre MySQL pour que les recommandations soient les plus precises (je recommanderais de l’executer apres 7 jours, 30 jours, etc. car vous pourriez obtenir plus d’informations avec une utilisation plus importante du serveur). Mais ne pensez pas que vous n’obtiendrez aucune information importante si vous l’executez, disons, une heure apres ou immediatement apres le demarrage. Immediatement apres le demarrage, vous pouvez voir si vous avez fait une erreur avec la memoire. Tandis qu’apres une heure, vous pourriez avoir des informations precieuses si vous commencez juste a optimiser votre configuration. Par exemple, ici j’ai deja atteint la taille maximale pour mes tables temporaires, donc cette erreur ne disparaitra probablement pas, quelle que soit la duree d’execution de MySQL. Avec le temps, vous apprendrez quelles recommandations vous devriez attendre pour suivre et lesquelles vous pouvez implementer immediatement.
En faisant bon usage de cette recommandation, notez egalement la precedente “Lors des ajustements, rendez tmp_table_size et max_heap_table_size egaux”.
Toutes les modifications de votre /etc/mysql/my.cnf necessitent generalement un redemarrage du service. J’ai essaye le parametre reload mais mysqltuner ne semble pas noter le changement.
Mon dernier conseil est de vous concentrer sur les metriques de performance et la section des recommandations :
- Si vous partagez votre machine serveur avec d’autres applications, essayez d’avoir l’utilisation maximale possible de la memoire dans vos estimations de la quantite de RAM que vous etes pret a consacrer a MySQL.
- Comme je l’ai mentionne, assurez-vous que votre nombre maximum de connexions est a environ 80 % de celles disponibles. Faites tourner MySQL pendant un certain temps et ajoutez 25 % de connexions supplementaires au maximum que vous obtenez.
- Configurez les requetes lentes. Indiquez combien de secondes constituent une requete lente et voyez si vous en obtenez quelques-unes ou trop.
- Assurez-vous que tous les tampons ont suffisamment de RAM.
- Configurez votre swappiness a une valeur basse. Le swap de la RAM enleve un peu de performance a un serveur MySQL. N’oubliez pas que vous utilisez la plupart de ces tampons / cache pour eviter d’aller chercher des informations sur le disque. Pour plus d’informations sur la configuration de l’agressivite du swap, consultez cet article : (</linux/ubuntu/configure-swappiness-ubuntu/> “How to: Configure Swappiness in Ubuntu”)
- Restez calme et continuez a ameliorer votre configuration ;). Vous constaterez que tout droit sorti de la boite, aucun serveur n’est le meme. Avec le temps et en regardant les metriques, vous verrez si vous ecrivez ou lisez principalement, votre activite et vos besoins en memoire, et vous dimensionnerez mieux le serveur et ajusterez les parametres a la charge que vous recevez. Percona dispose d’un outil pour vous aider a trouver des valeurs de depart a : https://tools.percona.com/wizard. Gardez a l’esprit que vous devriez les comprendre et les appliquer peut-etre individuellement au lieu de simplement copier-coller le tout. Si votre serveur a ete utilise auparavant, certains des nouveaux parametres pourraient le faire planter car il a cree des tables avec des parametres differents. De plus, l’utilisation de certains parametres pourrait casser des applications comme WordPress.
(http://img.zemanta.com/zemified_h.png?x-id=c89eacd2-c247-4ece-ba8a-028aabcefe70)](http://www.zemanta.com/?px “Enhanced by Zemanta”)
Resume
Vous avez appris avec succes a installer et utiliser MySQLTuner pour optimiser votre configuration / performance MySQL. Si vous rencontrez des problemes, verifiez les prerequis et assurez-vous que votre environnement MySQL est correctement configure.