MySQL slow queries: playbook operativo per trovare il collo di bottiglia vero

Quando un database rallenta, molti partono dalla configurazione: buffer pool, cache, connessioni. Spesso è il posto sbagliato da cui cominciare. Se hai slow query gravi, puoi mettere tutta la RAM che vuoi: il problema resterà una query pessima eseguita troppe volte.

Questo playbook serve a trovare il collo di bottiglia vero, non quello più comodo da raccontare.

Se il sintomo è un sito lento in generale, affianca questa guida a Sito lento: 5 cause server-side e come risolverle. Se stai già lavorando su buffer pool e RAM, completa con MySQL buffer pool tuning: guida basata sui dati. Se il problema ha già impatto utente o commerciale, il servizio corretto è Performance Tuning.

TL;DR

Ordine corretto:

1. attiva e leggi lo slow log
2. raggruppa per fingerprint, non per singola occorrenza
3. misura frequenza, tempo totale e rows examined
4. usa EXPLAIN sulle query peggiori
5. intervieni prima su indici e query, poi sulla configurazione

Se salti il punto 2, perdi tempo. Se salti il punto 5, peggiori il server senza risolvere il problema.

Step 1: attiva lo slow log correttamente

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Controlla dove sta scrivendo:

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

In file di config, valori tipici:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

Per workload WordPress o Magento, 2s spesso è troppo alto. Parti più basso, poi regola.

Step 2: non leggere il log a occhio

Leggere lo slow log riga per riga è utile solo per capire se sta funzionando. Per analizzarlo davvero, usa aggregazione.

mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

Meglio ancora:

pt-query-digest /var/log/mysql/slow.log

Cose da guardare subito:

• query time totale
• count
• query time medio
• lock time
• rows sent
• rows examined

La query peggiore non è sempre la più lenta. Spesso è quella che dura “solo” 300 ms ma gira 30.000 volte l'ora.

Step 3: trova la top 3 vera

Classifica le query con questo criterio:

• impatto totale sul sistema
• frequenza
• rischio di ottimizzazione rapida

Priorità alta

• query che esaminano milioni di righe
• query senza indice su tabelle grandi
• query eseguite migliaia di volte
• query che bloccano altre query

Priorità bassa

• query lente ma rarissime
• query amministrative non critiche
• query batch eseguite fuori orario e sotto controllo

Step 4: usa EXPLAIN come strumento, non come rituale

Prendi una query reale e lanciala con EXPLAIN.

EXPLAIN SELECT *
FROM ordini
WHERE cliente_id = 123
ORDER BY created_at DESC
LIMIT 20;

Cosa guardare:

• type
• possible_keys
• key
• rows
• Extra

Campanelli d'allarme

• type = ALL
• Using filesort
• Using temporary
• rows altissimo rispetto al risultato atteso
• nessun indice scelto (key = NULL)

Se vedi type = ALL su una tabella grossa, stai quasi certamente facendo full scan dove non dovresti.

Step 5: rows examined conta più di quanto pensi

Una query che restituisce 10 righe ma ne esamina 500.000 è una query costosa, anche se non sempre appare “drammatica” nella singola esecuzione.

Con pt-query-digest o performance schema puoi identificare proprio questo pattern.

Se rows_examined >> rows_sent, di solito hai uno di questi problemi:

• indice mancante
• indice non usato per ordine delle colonne
• filtro poco selettivo
• ORDER BY o GROUP BY costoso
• query scritta male dall'applicazione

Step 6: l'indice giusto, non dieci indici inutili

Errore comune: aggiungere indici a caso finché la query non “sembra andare”.

Approccio corretto:

• capisci i filtri reali della query
• considera ordine, where e limit
• usa indici composti quando serve

Esempio:

SELECT id, totale, created_at
FROM ordini
WHERE stato = 'paid' AND created_at >= '2026-04-01'
ORDER BY created_at DESC
LIMIT 50;

Indice sensato:

ALTER TABLE ordini ADD INDEX idx_stato_created_at (stato, created_at);

Mettere solo created_at può non bastare. Mettere stato, cliente_id, created_at, totale, id, qualunque_cosa spesso è solo spreco e costo di scrittura.

Step 7: guarda anche l'applicazione

Non tutte le slow query si sistemano nel database.

Controlla se il problema nasce da:

• query duplicate da plugin o ORM
• loop applicativi che interrogano il db troppe volte
• SELECT * inutili
• cron o job che partono tutti insieme
• pagine admin molto pesanti

Questo è tipico in WordPress con plugin mal progettati e in Magento con estensioni invasive.

Step 8: lock e concorrenza

A volte la query non è lenta in sé. È bloccata da altro.

SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G

Cerca:

• lock wait
• transazioni aperte troppo a lungo
• update massivi
• import che tengono tabelle occupate

Se ottimizzi una query in lettura ma il vero problema è contesa su scrittura, stai lavorando sulla cosa sbagliata.

Step 9: errori classici

1. Aumentare max_connections

Se il database è già sotto stress, più connessioni spesso significano più caos.

2. Alzare il buffer pool prima di analizzare il log

Può aiutare, ma non rimuove query pessime.

3. Lasciare long_query_time troppo alto

Non vedi il degradamento progressivo.

4. Guardare solo il tempo medio

Le code e i picchi ti fregano.

5. Non ripulire il log dopo l'analisi

Analizzi rumore storico invece di comportamento corrente.

Procedura pratica da riusare

# 1. Ruota slow log
mv /var/log/mysql/slow.log /var/log/mysql/slow.log.$(date +%Y%m%d-%H%M)
touch /var/log/mysql/slow.log
chown mysql:mysql /var/log/mysql/slow.log

# 2. Attendi finestra di traffico reale
sleep 3600

# 3. Analizza
pt-query-digest /var/log/mysql/slow.log > /tmp/slow-report.txt

Poi prendi le top query e lavori in questo ordine:

1. indice
2. query
3. pattern applicativo
4. solo alla fine configurazione db

Quando questa guida non basta

Questa guida basta per analisi serie su un singolo server o stack lineare. Non basta se:

• hai replica complessa
• hai cluster con proxy o sharding
• hai multi-tenant con centinaia di database
• non puoi modificare query o schema
• il problema coinvolge insieme PHP-FPM, cache e database

In questi casi serve un intervento trasversale. Parti da Performance Tuning o da un audit gratuito.

Checklist finale

• slow log attivo
• top query aggregate identificate
• EXPLAIN eseguito
• rows_examined analizzato
• lock e concorrenza verificati
• indice o query ottimizzati
• log ruotato e nuova misurazione eseguita

Le slow query non sono “un problema MySQL”. Sono un problema di osservabilità, ordine e priorità. Quando lo affronti così, il tuning smette di essere superstizione.