Optimiser un serveur MySQL

Je pourrais aborder ce problème important de differentes manières, soit la méthode gros geek barbare qui connais la documentation par coeur, soit petit malin futé qui sait utiliser les outils qui vont bien.

Alors le discours extremiste pourrait commencer par : de toute façon, ce n’est pas le serveur MySQL en lui meme qu’il faut d’abord optimiser, mais plutôt les requetes qui sont faites dessus. Sur ce point je suis d’accord, mais, et c’est un cas pratique qui je rencontre tous les jours, sur une plateforme mutualisée tel que celle de PlexiWeb il m’est impossible en tant qu’administrateur système d’optimiser ces requetes.
La solution passe alors par analyser les fameux SHOW STATUS de MySQL et lire beaucoup de documentations a ce propos afin d’etre capable de configurer tout cela. Une bonne piste de départ serait d’utiliser un outil comme mysqlreport capable de mettre en forme la sortie d’un SHOW STATUS, ou bien encore phpMyAdmin qui dans les dernieres versions commente également le SHOW STATUS.
Mais un administrateur système se doit d’etre faineant, c’est pourquoi une bonne solution (mais attention, elle comporte ses limites) est d’utiliser un script tel que MySQL Performance Tuning Primer Script qui va analyser tout comme un grand, et proposer des améliorations a apporter à la configuration du serveur MySQL. Ce script a quelques dependances, un client mysql ou bien encore bc (et oui, il faut bien faire quelques calculs). Attention également a ne pas lancer le script 10 minutes apres le lancement du serveur MySQL, il est de toute façon recommandé d’attendre plusieurs heures avant d’analyser les résultats ! Voici un exemple de la sortie produite lors de l’utilisation de ce script.

         MYSQL PERFORMANCE TUNING PRIMER               - By: Matthew Montgomery - MySQL Version 4.0.24_Debian-10sarge1 Uptime = 80 days 17 hrs 59 min 27 sec Avg. qps = 0 Total Questions = 5520858 Threads Connected = 16 Server has been running for over 48hrs. It should be safe to follow these recommendations To find out more information on how each of these runtime variables effects performance visit: http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html SLOW QUERIES Current long_query_time = 10 sec. You have 0 out of 5520858 that take longer than 10 sec. to complete The slow query log is NOT enabled. Your long_query_time may be too high, I typically set this under 5 sec. MAX CONNECTIONS Current max_connections = 100 Current threads_connected = 16 Historic max_used_connections = 54 The number of used connections is 54% of the configured maximum. Your max_connections variable seems to be fine. WORKER THREADS Current thread_cache_size = 0 Current threds_cached = 0 Current threads_per_sec = 1 Historic threads_per_sec = 0 Your thread_cache_size is fine KEY BUFFER Current MyISAM index space = 391 K Current key_buffer_size = 16 M Key cache miss rate is 1 / 139225 Key buffer fill ratio = Unknown % Your key_buffer_size seems to be too high. Perhaps you can use these resources elsewhere QUERY CACHE Query cache is enabled Current query_cache_size = 16 M Current query_cache_used = 7 M Current Query cache fill ratio = 48.68 % SORT OPERATIONS Current sort_buffer_size = 2 M Current record/read_rnd_buffer_size = 256.00 K No sort operations have been performed Sort buffer seems to be fine JOINS Current join_buffer_size = 128.00 K You have had 0 queries where a join could not use an index properly Your joins seem to be using indexes properly TABLE CACHE Current table_cache value = 64 tables You have a total of 73 tables You have 64 open tables. Current table_cache hit rate is 72%, while 100% of your table cache is in use You should probably increase your table_cache TEMP TABLES Current tmp_table_size = 32 M 0% of tmp tables created were disk based Created disk tmp tables ratio seems fine TABLE SCANS Current read_buffer_size = 128.00 K Current table scan ratio = 0 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 0 : 5520858 Your table locking seems to be fine

Les messages produits sont assez explicites, et il suffit de se reporter a la documentation officielle pour s’attaquer aux bonnes variables sans tout casser. Si vous connaissez/utilisez d’autres outils identiques, n’hésitez pas à le preciser en commentaire.