Mysql : optimisation variable "key_buffer"

Cette page de 2009 reste d'actualité (au moins pour les tables MyISAM) et les remarques et conseils peuvent s'appliquer à la variable "innodb_buffer_size" qui remplit à peu près la même fonction pour InnoDB.

Sur quelle information optimiser la valeur de ce paramètre ? 
Ce paramètre est réservé aux tables "MyIsam", de plus en plus rares au profit des tables InnoDB.

KEY_BUFFER = taille réservée au buffer des index. Sa taille doit être assez importante, l'idéal est que tous les index résident en mémoire limitant ainsi les IO. 
Vous pouvez calculer cette taille en évaluant la taille des index de votre base de données. L'utilisation de MySQL-Admin vous permet de suivre "en direct" l'évolution de l'utilisation de ce cache. Voir dans la section "Health", onglet "Memory Health" de MySQL-Admin. La partie "Key efficiency" vous permet de voir la partie du buffer effectivement utilisée ainsi que le "hitrate". 
Le "hitrate" est le pourcentage de clés effectivement lues depuis le buffer. 
Attention : Ne pas tenir compte des valeurs présentes peu après un redémarrage, il faut attendre plusieurs heures que le fonctionnement de la base se stabilise et que l'utilisation corresponde à l'utilisation moyenne normale des applications liées à cette base.

INNODB_BUFFER_POOL_SIZE = taille réservée au buffer Innodb (data + index) 
Plus la valeur est élevée, meilleur est le fonctionnement de la base ... La mémoire étant bien moins rare qu'il y a quelques années on peut mettre ce qu'il faut. 
Si on a assez de mémoire pour contenir la taille de la base ne pas hésiter, mais calculer au moins le volume des tables les plus accédées plus 20 ou 30%.

INNODB_BUFFER_POOL_INSTANCES = Nombre de "sous pools" 
Une valeur de 1 par Gigaoctet de POOL_SIZE est à considérer comme correcte, mais mettre une valeur proche de celle du nombre de coeurs de processeur est une option à ne pas négliger.

• Des buffers trop petits --> hitrate bas et :
  •     temps de réponse allongé
  •     IO plus élevées
• Des buffers trop grands --> mémoire gachée.

Il n'est pas la peine de mobiliser 8 Gigaoctets de mémoire si seulement 50% sont utilisés, la mémoire économisée ici peut avec profit être utilisée ailleurs, c'est le cas de la plupart des paramètres de mémoire, trop petits cela ralentit, trop grands cela utilise inutilement de la mémoire. 
Si votre base est "petite" par rapport à la mémoire mettez ce qu'il faut, sur une machine spécialisée "base de données" 75/80% de la mémoire est parfait pour InnoDB. 
Toutefois si l'ensemble de vos tables ne "pèse" que 30 Go il est inutile de réserver 64 Go de mémoire !

TABLE_OPEN_CACHE = nombre de tables en cache. 
Une cette valeur égale au nombre de tables si cela est possible. Note : on trouve un tas de choses à ce sujet ... 
Une taille peu différente du nombre de tables de la base semble en général favorable.

Un autre point à surveiller est la mémoire affectée aux tris et autres zones temporaires (TMP_TABLE_SIZE et MAX_HEAP_TABLE_SIZE). Si certaines requêtes ramènent beaucoup de lignes à trier cela nécessite de la place et, là aussi, affecter un peu de mémoire n'est pas un luxe. En général 64 à 320Mo sont suffisants, surveiller le pourcentage de tables temporaires sur disque. La valeur de READ_IO_THREADS et WRITE_IO_THREADS peut être au minimum de 2, mais sur une machine multiprocesseurs aller presque jusqu'au nombre de coeurs est une bonne pratique ... 
La taille des READ, SORT et JOIN BUFFER_SIZE est à suivre et des valeurs de 2 à 16M sont en général adaptées.

Mais il faut aussi penser à l'activité de création/modification de la base et affecter un minimum de mémoire au INNO_DB_LOG_BUFFER_SIZE afin de fluidifier les écritures sur le log. 
A propos de accès disques, plus ils sont rapides, mieux c'est. L'utilisation de SSD permet d'atteindre des vitesses impressionnantes et la mise en RAID 1 permet d'augmenter encore ce type de performance avec toutefois un certain risque, le RAID 10 est parfait. Voir ici l'influence de la variable INNODB_IO_CAPACITY qui permet à la base d'optimiser son fonctionnement en fonction de la vitesse des I/O, en général de 100 à 1000 pour des disques standard et > 10000 pour des SSD. 
Sur des systèmes largement multi-processeurs on peut aussi jouer sur les paramètres INNODB_READ_IO_THREADS et INNODB_WRITE_IO_THREADS et les augmenter à 2,3 ... afin d'accélérer au maximum les IO, base en lecture --> forcer le nombre de READ_IO_THREADS, si beaucoup d'écritures forcer le nombre de WRITE_IO_THREADS. Toutefois si vous n'avez pas de SSD ou de multiples disque de données cela peut être très peu efficace. 
Pour l'ajustement de ces valeurs suivre l'évolution des compteurs et ratios fournis par un système de suivi de performance tel que Shinken + Graphite/InfluxDB ou d'autres produits non libres, Mysql Workbench fournit aussi l'accès à beaucoup d'informations utiles.