Il buffer pool è dove InnoDB tiene dati e indici in memoria. Se il tuo insieme di lavoro ci sta, le query vengono servite dalla RAM. Se non ci sta, le stesse query leggono il disco — cioè da cento a mille volte più lentamente, per lo stesso lavoro.

Guarda che cosa hai

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

Il valore predefinito è 128 MB. Su un server con 8 GB di RAM, è l'impostazione che lascia inutilizzate più prestazioni di qualunque altra cosa sulla macchina.

Quanto sono grandi davvero i tuoi dati

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

Scegli il numero

  • Server di database dedicato — dal 60 al 70% della RAM.
  • Condiviso con PHP e il server web — dal 25 al 40%, e tieni d'occhio lo swap.
  • Dati più piccoli di così — dimensionalo sui dati più un poco; il resto è sprecato.
# /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

Verifica se è abbastanza grande

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

Dividi le letture che hanno dovuto toccare il disco per il totale delle richieste di lettura. Sotto l'1% circa è sano; stabilmente più alto significa che il pool è troppo piccolo per l'insieme di lavoro.

Non dimensionarlo al punto da far andare la macchina in swap. Un database in swap è molto più lento di un database con un buffer pool piccolo — vedi RAM, swap e quando lo swap è un sintomo.
Prima di comprare altra memoria, verifica che le query siano ragionevoli. Un solo indice mancante può far sembrare un insieme di lavoro dieci volte più grande di quello che è — vedi Quando aggiungere un indice.