Come si fa il tuning di un server MySQL: buffer pool, slow query log e connessioni?

Question

IT Interview Practice · Accepted Answer

Il tuning del server segue un ciclo: **misura, trova il collo di bottiglia, correggi una cosa, rimisura.** Le tre aree a maggior leva sono l'InnoDB buffer pool, lo slow query log e la gestione delle connessioni.

## InnoDB buffer pool — la manopola numero 1

Il buffer pool mette in cache in RAM le pagine di dati e indici; quando il tuo working set ci sta dentro, le letture sono a velocità di memoria e l'I/O su disco quasi svanisce. Dimensionalo a ~50–75% della RAM su un server DB dedicato.

```sql
SET GLOBAL innodb_buffer_pool_size = 12 * 1024 * 1024 * 1024;  -- 12 GB

-- Controllo di salute: l'hit rate dovrebbe essere ~99%+
SHOW ENGINE INNODB STATUS\G
--   Buffer pool hit rate 999 / 1000     <- buono; vicino a 1000/1000 = quasi tutto in RAM
```

## Slow query log — trova cosa correggere

```sql
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;   -- registra tutto ciò che supera i 500 ms
-- Poi aggrega con pt-query-digest / mysqldumpslow per classificare le query peggiori,
-- e fai EXPLAIN di ognuna. La maggior parte dei guadagni è un indice mancante, non un'impostazione del server.
```

## Connessioni

Ogni connessione costa memoria e thread. Non impostare `max_connections` alle stelle per mascherare un leak — un'ondata di migliaia di connessioni sovraccarica il server. Usa un **connection pool** nell'app (o un proxy come ProxySQL) per mantenere un insieme piccolo e riutilizzato, e monitora `Threads_connected` vs `Threads_running`.

```sql
SHOW GLOBAL STATUS LIKE 'Threads_running';   -- quanti stanno effettivamente lavorando
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
```

## Ordine di attacco

Correggi prima le query più lente (indici) — di solito batte qualsiasi cambio di configurazione. Poi dimensiona correttamente il buffer pool. Solo dopo fai il tuning di `max_connections`, `innodb_log_file_size` e le impostazioni di flush. Cambia una variabile alla volta e rimisura.

## Perché è importante

La maggior parte degli incidenti "MySQL è lento" sono una manciata di query non indicizzate o un buffer pool troppo piccolo per contenere il working set — non un tuning esotico. Un ingegnere senior ricorre prima allo slow log e all'hit rate del buffer pool, correggendo il vero collo di bottiglia invece di girare alla cieca le manopole della configurazione.

InnoDB buffer pool — la manopola numero 1

Slow query log — trova cosa correggere

Connessioni

Ordine di attacco

Perché è importante

Domande correlate