Diagnosi e risoluzione di connessioni lente al database MySQL su VPS senza supporto tecnico: guida operativa per Debian e Ubuntu

A gennaio 2025 il titolare di una PMI pugliese - produttore di componentistica idraulica con un gestionale web Laravel 10 usato internamente da 18 operatori - mi ha segnalato che "il gestionale è diventato lento". Lento in senso concreto: la ricerca prodotti, che un anno prima rispondeva in meno di un secondo, ora impiegava 3-4 secondi. L'inserimento di un ordine con 15-20 righe richiedeva oltre un minuto perché ogni riga scatenava una query di verifica disponibilità che restava in attesa. Il VPS era un Hetzner CPX21 - 3 vCPU AMD, 4 GB di RAM, 80 GB NVMe - con Debian 12, Nginx, PHP-FPM 8.2 e MySQL 8.0. L'hardware non era cambiato, il codice non era cambiato. Quello che era cambiato era il volume dei dati: il database era passato da 800 MB a 6.2 GB in dodici mesi, e nessuno aveva mai toccato la configurazione di MySQL dal giorno dell'installazione.

La diagnosi ha rivelato tre problemi sovrapposti: un InnoDB buffer pool configurato a 128 MB (il default di Debian) su un database da 6.2 GB - il che significava che MySQL leggeva il 95% dei dati dal disco invece che dalla RAM -, il reverse DNS lookup attivo che aggiungeva 200-500ms a ogni nuova connessione, e 47 query per pagina che facevano full table scan perché mancavano gli indici sulle colonne filtrate. In due ore di intervento ho riportato il response time della ricerca prodotti da 3.4 secondi a 180 millisecondi. In questo articolo ti racconto come, perché sono gli stessi tre problemi che trovo nel 70% delle installazioni MySQL su VPS di PMI.

Stai cercando un Consulente Informatico esperto per ottimizzare MySQL sulla tua infrastruttura? Nel mio profilo professionale trovi l'esperienza concreta su tuning database, diagnosi performance e ottimizzazione di stack LEMP per PMI. Contattami per una consulenza diretta.

Come si diagnostica un problema di performance MySQL su VPS?

La diagnosi parte sempre dalla stessa sequenza di tre passaggi: stato del servizio, slow query log e processlist. L'errore più comune è saltare la diagnosi e andare direttamente alla "soluzione" - aumentare max_connections, buttare più RAM, riavviare MySQL. Senza diagnosi, stai tirando a indovinare.

# 1. MySQL è attivo? Da quanto tempo? Quante connessioni?
systemctl status mysql
mysqladmin -u root -p status
# Output: Uptime: 2456789  Threads: 5  Questions: 14523678  Slow queries: 4521

# 2. Abilitare il slow query log se non è già attivo
mysql -u root -p -e "
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;
SHOW VARIABLES LIKE 'slow_query_log_file';
"

# 3. Vedere le query attive in questo momento
mysql -u root -p -e "
SELECT id, user, host, db, command, time, state, LEFT(info, 80) as query
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;
"

Il parametro log_queries_not_using_indexes = 1 è fondamentale: registra nel slow log anche le query che completano rapidamente ma non usano indici. Su un database piccolo queste query sono veloci; su un database che cresce, diventano il collo di bottiglia primario. Nel caso pugliese, dopo 15 minuti con il slow log attivo, avevo già 47 query diverse registrate - tutte con # Full_scan: Yes nell'header del log.

Lo strumento che uso per analizzare il slow log non è mysqldumpslow (troppo basico) ma pt-query-digest dalla Percona Toolkit - un tool che raggruppa le query per fingerprint, calcola il tempo totale speso per ciascuna, e ti mostra esattamente dove intervenire:

# Installare Percona Toolkit
apt install -y percona-toolkit

# Analizzare il slow query log
pt-query-digest /var/lib/mysql/slow.log | head -60

L'output di pt-query-digest ordina le query per tempo totale cumulativo, non per tempo di singola esecuzione. Una query che impiega 50ms ma viene eseguita 10.000 volte al giorno è un problema molto più grave di una query che impiega 5 secondi ma viene eseguita una volta alla settimana. Questa prospettiva cambia completamente le priorità di ottimizzazione.

Il primo fix: il reverse DNS che nessuno conosce

Quando MySQL riceve una connessione, per default tenta di risolvere il DNS inverso dell'IP del client - cioè cerca il nome host associato all'indirizzo IP. Se il server DNS non risponde rapidamente (timeout, DNS lento, DNS non configurato), ogni nuova connessione aggiunge 200-500ms di latenza prima ancora che la query venga eseguita. Su un'applicazione web che apre una connessione per ogni richiesta HTTP (il caso di Laravel senza persistent connections), questo ritardo si somma a ogni singolo page load.

La soluzione è una riga nel file di configurazione MySQL:

# In /etc/mysql/mysql.conf.d/mysqld.cnf sotto [mysqld]
skip-name-resolve

Dopo il restart di MySQL (systemctl restart mysql), le connessioni diventano istantanee. L'unica conseguenza è che nelle tabelle dei permessi MySQL (mysql.user) non puoi più usare hostname - devi usare indirizzi IP. Per applicazioni che si connettono da localhost (come Laravel sullo stesso server), questo non ha alcun impatto.

Nel caso pugliese, il skip-name-resolve da solo ha ridotto il tempo di connessione da 380ms a 0.2ms - un miglioramento invisibile per una singola query, ma che su 47 query per pagina significava quasi 18 secondi risparmiati per ogni sessione di lavoro degli operatori.

Il secondo fix: InnoDB buffer pool - la singola impostazione più importante

L'InnoDB buffer pool è l'area di memoria dove MySQL mantiene in cache dati e indici delle tabelle InnoDB. Più è grande, più dati MySQL riesce a servire dalla RAM invece che dal disco. La documentazione ufficiale MySQL raccomanda di impostarlo al 70-80% della RAM disponibile su server dedicati al database, e al 50-60% su server condivisi dove MySQL coesiste con altri servizi.

# Verificare la dimensione attuale
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
# Output: 134217728 (= 128MB, il default di Debian)

# Verificare il buffer pool hit ratio
mysql -u root -p -e "
SELECT
    (1 - (
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
    )) * 100 AS hit_ratio_percent;
"

Se l'hit ratio è sotto il 99%, il buffer pool è troppo piccolo. Nel caso pugliese era al 62% - MySQL leggeva dal disco quasi il 40% delle pagine richieste. Su NVMe la latenza disco è bassa (200 microsecondi), ma rispetto alla latenza RAM (100 nanosecondi) è comunque 2000 volte più lento.

La configurazione che ho applicato:

# In /etc/mysql/mysql.conf.d/mysqld.cnf sotto [mysqld]
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 512M

Su un VPS con 4 GB di RAM, 2 GB per il buffer pool lascia circa 1 GB per PHP-FPM, 500 MB per il sistema e 500 MB di margine. Dopo il restart, l'hit ratio è salito al 99.7% in meno di un'ora - il tempo necessario perché MySQL riempia il buffer pool con le pagine più richieste.

Il terzo fix: gli indici mancanti che nessuno ha aggiunto

Il slow query log aveva identificato 47 query senza indice. La più frequente era la ricerca prodotti per codice e categoria - una query che Laravel generava con Product::where('category_id', $cat)->where('code', 'LIKE', "%$term%")->paginate(20). Senza un indice composito su (category_id, code), MySQL faceva un full table scan su 340.000 righe per ogni ricerca.

-- Verificare gli indici esistenti sulla tabella
SHOW INDEX FROM products;

-- Analizzare il piano di esecuzione della query lenta
EXPLAIN SELECT * FROM products
WHERE category_id = 42 AND code LIKE '%ABC%'
ORDER BY name LIMIT 20;
-- Output: type=ALL, rows=340000, Extra=Using where; Using filesort
-- ALL = full table scan, 340K righe esaminate per restituirne 20

-- Aggiungere l'indice composito
ALTER TABLE products ADD INDEX idx_category_code (category_id, code);

-- Verificare il miglioramento
EXPLAIN SELECT * FROM products
WHERE category_id = 42 AND code LIKE '%ABC%'
ORDER BY name LIMIT 20;
-- Output: type=ref, rows=4200, Extra=Using index condition

Da 340.000 righe esaminate a 4.200 - un miglioramento dell'ordine di grandezza. La query che prima impiegava 2.8 secondi ora completa in 12 millisecondi. Ho aggiunto sei indici in totale, tutti identificati dal slow log e verificati con EXPLAIN prima dell'applicazione. Il metodo è sempre lo stesso: non indovinare gli indici - lascia che il slow log ti dica quali query sono lente, e usa EXPLAIN per capire perché.

Per un approfondimento sulla diagnosi e il fix degli indici MySQL con invisible indexes e schema refactoring su tabelle grandi, ho scritto un articolo dedicato al refactoring database MySQL su VPS.

Il quarto problema nascosto: connessioni MySQL esaurite

Un pattern che vedo spesso su applicazioni Laravel in crescita è l'errore SQLSTATE[HY000] [1040] Too many connections. MySQL ha un limite configurabile di connessioni simultanee (max_connections, default 151 su Debian 12), e quando viene raggiunto rifiuta ogni nuova connessione - comprese quelle dell'applicazione web, che restituisce errore 500 a tutti gli utenti.

La reazione istintiva è aumentare max_connections a 500 o 1000. È quasi sempre sbagliato: ogni connessione MySQL consuma circa 2-10 MB di RAM a seconda dei buffer allocati, quindi 500 connessioni possono consumare da 1 a 5 GB di RAM - su un VPS con 4 GB, il server va in OOM. Il problema reale non è il limite troppo basso, ma le connessioni che restano aperte troppo a lungo:

# Quante connessioni sono attive adesso?
mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected';"

# Quante sono in stato Sleep (aperte ma inattive)?
mysql -u root -p -e "SELECT COUNT(*) as sleeping FROM information_schema.processlist WHERE Command = 'Sleep';"

# Se la maggior parte è in Sleep, il problema è l'applicazione
# che non chiude le connessioni. Ridurre wait_timeout:
# In /etc/mysql/mysql.conf.d/mysqld.cnf:
# wait_timeout = 60        (default: 28800 = 8 ore!)
# interactive_timeout = 60

Il wait_timeout di default è 28.800 secondi - otto ore. Questo significa che una connessione aperta da un processo PHP che è terminato da ore resta allocata in MySQL fino a otto ore dopo. Su applicazioni con traffico sostenuto, queste connessioni "zombie" si accumulano fino a saturare il limite. Riducendo il wait_timeout a 60 secondi, le connessioni inattive vengono chiuse dopo un minuto, liberando slot per le nuove richieste.

Per applicazioni Laravel, il lato applicativo della soluzione è configurare il connection pooling nel file config/database.php - in particolare le opzioni options con PDO::ATTR_PERSISTENT => false per evitare connessioni persistenti che restano aperte indefinitamente, e verificare che il queue worker non accumuli connessioni stale con --max-jobs o --max-time.

Monitoring proattivo: sapere quando MySQL rallenta prima che gli utenti se ne accorgano

Il modo più efficace per evitare che un problema MySQL diventi un'emergenza è misurare continuamente tre metriche: il buffer pool hit ratio (deve restare sopra il 99%), il numero di slow query per minuto (deve restare vicino a zero), e il numero di connessioni attive rispetto al massimo (deve restare sotto il 70%).

#!/usr/bin/env bash
# /root/scripts/mysql-health-check.sh - eseguire ogni 15 minuti via cron
set -uo pipefail

ALERT_EMAIL="[email protected]"

# Buffer pool hit ratio
HIT_RATIO=$(mysql -u root -p"${MYSQL_ROOT_PASS}" -N -e "
SELECT ROUND((1 - (
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') /
    GREATEST((SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests'), 1)
)) * 100, 2);
" 2>/dev/null)

# Connessioni attive vs max
THREADS=$(mysql -u root -p"${MYSQL_ROOT_PASS}" -N -e "SHOW STATUS LIKE 'Threads_connected';" 2>/dev/null | awk '{print $2}')
MAX_CONN=$(mysql -u root -p"${MYSQL_ROOT_PASS}" -N -e "SHOW VARIABLES LIKE 'max_connections';" 2>/dev/null | awk '{print $2}')

if (( $(echo "$HIT_RATIO < 95" | bc -l) )); then
    echo "MySQL buffer pool hit ratio: ${HIT_RATIO}%" | mail -s "MySQL ALERT: low hit ratio" "$ALERT_EMAIL"
fi

USAGE_PCT=$((THREADS * 100 / MAX_CONN))
if (( USAGE_PCT > 70 )); then
    echo "MySQL connections: ${THREADS}/${MAX_CONN} (${USAGE_PCT}%)" | mail -s "MySQL ALERT: high connections" "$ALERT_EMAIL"
fi

MySQLTuner: il check rapido che installo su ogni VPS

MySQLTuner è uno script Perl che analizza la configurazione MySQL e suggerisce modifiche basate sull'uso effettivo del server. Lo installo su ogni VPS e lo eseguo dopo almeno 48 ore di attività reale (perché ha bisogno di statistiche accumulate per dare suggerimenti sensati):

apt install -y mysqltuner
mysqltuner --host localhost --user root --pass PASSWORD

L'output include percentuali di utilizzo delle connessioni, hit ratio del buffer pool, percentuale di query lente, tabelle temporanee su disco, e suggerimenti specifici su quali parametri modificare. Non è un sostituto della diagnosi manuale, ma è un eccellente primo screening che rivela i problemi più macroscopici in meno di un minuto.

La performance di MySQL su VPS non è un argomento che puoi affrontare una volta e dimenticare - il database cresce, le query cambiano, e ciò che funzionava con 100.000 righe non funziona più con un milione. Ho documentato l'approccio completo all'ottimizzazione performance PHP e MySQL su Hetzner e OVH in un articolo dedicato, e il protocollo di hardening MySQL che copre anche gli aspetti di sicurezza (bind su localhost, segregazione utenti, TLS obbligatorio).

Se il tuo gestionale o e-commerce basato su MySQL è diventato lento e non sai dove guardare, la risposta è quasi sempre nei tre punti che ho descritto: reverse DNS, buffer pool e indici mancanti. Sono fix che richiedono ore, non giorni, e l'impatto sulle performance è misurabile immediatamente. Contattami e facciamo una diagnosi.