MySQL buffer pool: come calibrarlo davvero (senza magia)

Se hai googlato "MySQL buffer pool size", avrai trovato la stessa raccomandazione ovunque: imposta innodb_buffer_pool_size al 70-80% della RAM disponibile. Questa regola è sbagliata.

Non perché il numero sia sbagliato in sé, ma perché è una semplificazione che ignora il contesto reale. Un server con 8 GB di RAM e un database da 2 GB ha bisogni diversi da uno con 64 GB di RAM e 80 GB di dati.

Il buffer pool si calcola con dati, non con formule.

Se il sintomo iniziale è "il sito è lento", prima passa da Sito lento: 5 cause che il 90% delle agenzie ignora e poi torna qui per il tuning mirato. Per interventi su stack già in produzione trovi anche il servizio Performance Tuning.

Cos'è il buffer pool (e perché conta)

Il buffer pool è la cache principale di InnoDB. Quando MySQL legge una pagina di dati, la mette nel buffer pool. Le letture successive vengono dalla RAM invece che dal disco.

La differenza tra leggere da RAM e da disco è 1000x. Se il buffer pool è calibrato bene, il 99%+ delle letture viene dalla cache. Se è calibrato male, MySQL legge continuamente da disco e il sito diventa lento.

Step 1: Quanto grande è il tuo database?

Prima di toccare qualsiasi configurazione, misura:

-- Dimensione totale dei dati InnoDB
SELECT 
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb,
  ROUND(SUM(data_length) / 1024 / 1024, 2) AS data_mb,
  ROUND(SUM(index_length) / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables 
WHERE engine = 'InnoDB';

Esempio output:

+----------+---------+-----------+
| size_mb  | data_mb | index_mb  |
+----------+---------+-----------+
| 1247.83  | 892.41  | 355.42    |
+----------+---------+-----------+

Il nostro database è ~1.2 GB.

Step 2: Quanta RAM hai disponibile?

free -h

              total        used        free      shared  buff/cache   available
Mem:           15Gi       3.2Gi       8.1Gi       256Mi       4.1Gi        11Gi

RAM totale: 16 GB. Disponibile per MySQL: ~11 GB (considerando OS + altri servizi).

Step 3: La formula reale (non quella magica)

La formula corretta è:

buffer_pool = min(
  database_size * 1.1,           -- 110% dei dati (per working set + crescita)
  available_ram * 0.8,           -- 80% della RAM disponibile
  total_ram - os_reserved        -- RAM meno riserva OS
)

Dove os_reserved è:

• Server dedicato MySQL: 1-2 GB
• Server condiviso (web + db): 4-6 GB
• Server con altri servizi pesanti: calcola caso per caso

Nel nostro esempio:

• Database: 1.2 GB → 1.2 * 1.1 = 1.32 GB

Quando non devi toccare il buffer pool

Se il collo di bottiglia è PHP-FPM, disco saturo, query non indicizzate o replica in ritardo, aumentare innodb_buffer_pool_size non risolve nulla. Il rischio è peggiorare la situazione e spostare il problema sulla RAM. Se non hai ancora isolato la causa, fai prima la checklist di Server down o una diagnosi su audit gratuito.

• RAM disponibile: 11 GB → 11 * 0.8 = 8.8 GB
• OS riserva: ~2 GB → 16 - 2 = 14 GB

Risultato: 1.32 GB (il minore dei tre). La regola del 80% avrebbe dato 8.8 GB, 6x più del necessario.

Step 4: Monitora il hit rate

Dopo aver cambiato la configurazione, monitora:

-- Hit rate del buffer pool
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

-- Calcola il hit rate
SELECT 
  (1 - (
    VARIABLE_VALUE / 
    (SELECT VARIABLE_VALUE FROM global_status 
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
  )) * 100 AS hit_rate_pct
FROM global_status 
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads';

Un hit rate sopra il 99% è ottimo. Sotto il 95% indica che il buffer pool è troppo piccolo.

Step 5: Controlla la working set

-- Pagine nel buffer pool vs pagine totali
SELECT 
  VARIABLE_VALUE AS pages_in_pool
FROM global_status 
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data';

SELECT 
  VARIABLE_VALUE AS total_pages
FROM global_status 
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';

-- Se pages_in_pool ≈ total_pages, il buffer pool potrebbe essere troppo grande
-- Se pages_in_pool << total_pages, potrebbe essere troppo piccolo

Configurazione pratica

Per un server con 16 GB RAM e database da 1.2 GB:

[mysqld]
innodb_buffer_pool_size = 1408M      # ~1.3 GB (110% di 1.2 GB)
innodb_buffer_pool_instances = 1     # Solo se buffer pool > 1 GB
innodb_log_file_size = 256M          # Log transaction
innodb_flush_log_at_trx_commit = 1   # Sicurezza (default)
innodb_flush_method = O_DIRECT       # Evita doppia cache

Quando la regola del 80% funziona

La regola funziona quando:

• Il database è più grande della RAM
• Il server è dedicato a MySQL
• Non hai tempo per fare tuning

In quel caso, 80% è un compromesso ragionevole. Ma se hai tempo per misurare, misura.

Caso pratico: WordPress multisite

Per un tipico WordPress multisite con 20 siti:

-- Trova le tabelle più grandi
SELECT 
  table_schema,
  table_name,
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables 
WHERE table_schema = 'wordpress_multisite'
ORDER BY (data_length + index_length) DESC 
LIMIT 10;

Di solito le tabelle più grandi sono wp_posts, wp_postmeta, wp_options. Se il 90% dei dati è in 3-4 tabelle, il working set è gestibile con buffer pool ridotto.

Errori comuni

Checklist di tuning

Prima di cambiare qualsiasi cosa:

• [ ] Misurato dimensione database reale
• [ ] Verificato RAM disponibile (dopo OS + altri servizi)
• [ ] Calcolato buffer pool con formula evidence-based
• [ ] Monitorato hit rate per 24-48 ore dopo il cambio
• [ ] Verificato che non c'è swap
• [ ] Documentato il cambio e i risultati

Il tuning di MySQL è un processo iterativo: misura, cambia, misura di nuovo. Non un set-it-and-forget-it.

La differenza tra una configurazione ottimale e una "abbastanza buona" può essere 10x in performance. Il tempo investito nel tuning si ripaga con siti più veloci e clienti più felici.