Le buffer pool est l'endroit où InnoDB garde les données et les index en mémoire. Si votre jeu de travail y tient, les requêtes sont servies depuis la RAM. Sinon, les mêmes requêtes lisent le disque — soit cent à mille fois plus lentement pour le même travail.

Voyez ce que vous avez

mysql -e "SELECT @@innodb_buffer_pool_size/1024/1024/1024 AS gb"

Par défaut, 128 Mo. Sur un serveur doté de 8 Go de RAM, c'est le réglage qui laisse le plus de performance inexploitée de toute la machine.

Quelle est vraiment la taille de vos données

SELECT ROUND(SUM(data_length + index_length)/1024/1024/1024, 2) AS gb
  FROM information_schema.tables WHERE engine = 'InnoDB';

Choisissez le chiffre

  • Serveur de base de données dédié — 60 à 70 % de la RAM.
  • Partagé avec PHP et le serveur web — 25 à 40 %, et surveillez le swap.
  • Données plus petites que cela — dimensionnez-le à la taille des données, plus un peu ; au-delà, c'est perdu.
# /etc/mysql/mysql.conf.d/mysqld.cnf
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4      # one per GB, up to 8
innodb_log_file_size = 512M

Vérifiez s'il est assez grand

mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool_read%'"

Divisez les lectures qui ont dû toucher le disque par le total des demandes de lecture. En dessous d'environ 1 %, tout va bien ; durablement au-dessus, le pool est trop petit pour le jeu de travail.

Ne le dimensionnez pas au point de faire swapper la machine. Une base qui swappe est bien plus lente qu'une base au buffer pool modeste — voir RAM, swap, et quand le swap est un symptôme.
Avant d'acheter de la mémoire, vérifiez que les requêtes sont raisonnables. Un seul index manquant peut faire paraître un jeu de travail dix fois plus gros qu'il n'est — voir Quand ajouter un index.