Monitoraggio delle Performance di MySQL: Utilizzo di SHOW STATUS e SHOW PROCESSLIST

Quando un'applicazione basata su MySQL rallenta, SHOW STATUS e SHOW PROCESSLIST sono i controlli integrati più rapidi che puoi eseguire prima di aprire un dashboard. Non spiegheranno da soli ogni problema, ma rispondono a due domande pratiche: cosa ha fatto il server e cosa sta eseguendo in questo momento?

Comprendere lo Stato del Sistema in Tempo Reale con SHOW STATUS

Il comando SHOW STATUS, spesso usato in modo intercambiabile con SHOW GLOBAL STATUS o SHOW SESSION STATUS, fornisce una grande quantità di informazioni riguardanti l'attività del server dall'ultimo riavvio o dall'inizio della sessione corrente. Queste variabili di stato fungono da contatori, tracciando tutto, dai tentativi di connessione all'efficienza della cache e agli attese per i lock.

Stato Globale vs. di Sessione

Quando si esegue questo comando, è cruciale comprendere l'ambito:

SHOW GLOBAL STATUS: Mostra i contatori accumulati dall'avvio dell'istanza del server MySQL. Questo fornisce una visione d'insieme della salute generale del server e delle tendenze a lungo termine.
SHOW SESSION STATUS: Mostra i contatori specifici solo per la connessione (sessione) che stai attualmente utilizzando. Questo è utile per isolare l'impatto sulle performance di transazioni specifiche.

Indicatori Chiave di Performance (KPI) da SHOW GLOBAL STATUS

Mentre SHOW GLOBAL STATUS restituisce centinaia di variabili, alcune sono critiche per il triage iniziale delle performance. Tipicamente, vorrai reindirizzare l'output a grep o utilizzare una clausola WHERE per filtrare la rilevanza.

1. Monitoraggio delle Connessioni e dei Thread

Queste variabili ti aiutano a comprendere il carico delle connessioni:

Nome Variabile	Descrizione
`Threads_connected`	Il numero di connessioni attualmente aperte (client).
`Threads_running`	Il numero di thread attivi che stanno attualmente eseguendo query (dovrebbe essere generalmente basso).
`Max_used_connections`	Il numero massimo di connessioni simultanee dall'avvio del server. Utile per dimensionare `max_connections`.

Esempio: Verifica delle connessioni attive:

SHOW GLOBAL STATUS LIKE 'Threads_%';

2. Cache delle Query ed Efficienza

Se stai utilizzando la legacy Query Cache (disponibile in versioni precedenti di MySQL, deprecata/rimossa in quelle più recenti), queste metriche sono essenziali:

Qcache_hits: Numero di volte in cui una query è stata servita dalla cache.
Qcache_lowmem_prunes: Numero di query che hanno causato l'eliminazione di voci più vecchie dalla cache a causa di memoria insufficiente.

3. Metriche del Motore InnoDB (Più Critiche per MySQL Moderno)

Per distribuzioni moderne che utilizzano il motore di archiviazione InnoDB, monitora l'attività del buffer pool:

Innodb_buffer_pool_read_requests: Richieste di lettura totali.
Innodb_buffer_pool_reads: Numero di letture fisiche dal disco (un rapporto elevato di letture fisiche rispetto alle richieste indica la necessità di un buffer pool più grande).

Consiglio Pratico: Per valutare rapidamente l'efficienza del buffer pool, calcola il tasso di hit: (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests.

4. Tabelle Temporanee e Ordinamenti

Questi indicano quanto elaborazione interna sta facendo MySQL:

Created_tmp_tables: Numero di tabelle temporanee in memoria create.
Created_tmp_disk_tables: Numero di tabelle temporanee che hanno dovuto essere scritte su disco (lento).

Se Created_tmp_disk_tables è alto, potresti dover aumentare tmp_table_size o max_heap_table_size.

Diagnosticare il Carico di Lavoro Attivo con SHOW PROCESSLIST

Mentre SHOW STATUS ti dice cosa è successo, SHOW PROCESSLIST ti dice cosa sta succedendo in questo momento. Mostra informazioni sui thread attualmente in esecuzione all'interno del server, permettendoti di identificare query lunghe o bloccate.

La Struttura della Process List

Il comando restituisce diverse colonne, ognuna delle quali fornisce contesto su una connessione attiva:

Colonna	Descrizione
Id	L'ID univoco della connessione (usato per killare il processo).
User	L'account utente connesso.
Host	L'host da cui ha avuto origine la connessione.
db	Il database attualmente utilizzato dal thread.
Command	Il tipo di comando in esecuzione (es. Query, Sleep, Connect).
Time	Il numero di secondi in cui il thread è rimasto nel suo stato corrente.
State	L'azione specifica che il thread sta eseguendo (es. Sending data, Copying to tmp table).
Info	L'istruzione SQL effettiva in esecuzione (o troncata se lunga).

Filtrare e Interpretare l'Output

Per sistemi di produzione di grandi dimensioni, la process list completa può essere travolgente. È pratica comune utilizzare la parola chiave FULL per assicurarti di vedere l'intero testo della query, e poi filtrare per le colonne Time o State.

1. Visualizzare il Testo Completo del Comando

Usa sempre FULL se sospetti query lente, poiché l'output standard spesso tronca il campo Info:

SHOW FULL PROCESSLIST;

2. Identificare Query Bloccanti o Lente

Monitora le colonne Time e Command:

Valore Alto di Time: Qualsiasi query in esecuzione per una durata prolungata (es. oltre 10 secondi, a seconda del tuo SLA) necessita di un'indagine immediata. Controlla la colonna Info corrispondente per vedere l'SQL.
Command = 'Sleep': Queste connessioni sono inattive ma consumano comunque risorse. Se si accumulano eccessivamente, considera di regolare la variabile wait_timeout.
Command = 'Query': Queste sono istruzioni attivamente in esecuzione. Presta molta attenzione al loro State.

3. Identificare Problemi di Lock

Quando le query sono bloccate in attesa di risorse, la colonna State spesso lo indica:

Waiting for table metadata lock
Waiting for table lock
Waiting for lock

Se vedi numerosi thread in stato di attesa, segnala contesa, solitamente causata da una transazione di lunga durata che tiene lock di cui altri hanno bisogno.

Azione: Terminare un Processo

Se identifichi una query fuori controllo che sta degradando gravemente le performance, puoi terminarla usando il comando KILL seguito dall'Id del processo:

KILL 12345; -- Sostituisci 12345 con l'Id effettivo dalla processlist

Attenzione: Usa KILL con cautela. Terminare una transazione attiva potrebbe lasciare il database in uno stato incoerente se la transazione era a metà di un'operazione di scrittura complessa. Cerca sempre di identificare e ottimizzare prima la query, se possibile.

Combinare le Informazioni di Stato e di Processo per la Risoluzione dei Problemi

Un monitoraggio efficace di MySQL spesso implica la correlazione tra questi due comandi:

Controllo Iniziale: Esegui SHOW FULL PROCESSLIST. Nota eventuali query con tempo elevato o connessioni eccessive.
Controllo del Contesto: Rivedi il numero di connessioni usando SHOW GLOBAL STATUS LIKE 'Threads_connected'. Stai affrontando un'ondata o solo una query difettosa?
Approfondimento: Se una query specifica è lenta, analizza il suo impatto sui contatori delle risorse rivedendo Innodb_buffer_pool_reads o i tassi di creazione di tabelle temporanee mentre la query è in esecuzione (richiede un confronto di base).

Controllando regolarmente questi output dinamici, vai oltre le supposizioni e applichi soluzioni mirate per migliorare la stabilità e la velocità di MySQL.

Una Routine di Triage Realistica

Un buon primo passaggio richiede meno di un minuto. Inizia con la process list:

SHOW FULL PROCESSLIST;

Cerca un mucchio di query attive, valori Time lunghi, attese per lock e molte connessioni Sleep inattive. Una singola query di report lenta viene gestita diversamente da centinaia di connessioni web in attesa dello stesso lock di tabella.

Poi controlla i contatori dei thread:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected',
  'Threads_running',
  'Max_used_connections'
);

Threads_connected ti dice quanti client sono connessi. Threads_running è solitamente più importante durante un rallentamento perché mostra quanti thread stanno attivamente lavorando. Molti client connessi in stato di sleep possono essere dispendiosi, ma molti thread in esecuzione possono significare che il server è sotto pressione reale.

Successivamente, verifica se il carico di lavoro sta creando tabelle temporanee su disco:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Created_tmp_tables',
  'Created_tmp_disk_tables',
  'Sort_merge_passes'
);

Questi contatori sono cumulativi dall'avvio, quindi un singolo snapshot può trarre in inganno. Fai due snapshot a distanza di qualche minuto durante l'incidente. Se le tabelle temporanee su disco aumentano rapidamente, ispeziona le query con GROUP BY, ORDER BY, join grandi, colonne di testo o indici mancanti. Aumentare tmp_table_size potrebbe aiutare in alcuni casi, ma una query o un indice migliori sono spesso la soluzione più pulita.

Esaminare la Pressione su InnoDB

La maggior parte delle distribuzioni moderne di MySQL utilizza InnoDB, quindi i contatori di InnoDB meritano attenzione:

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_read_requests conta le richieste di lettura logiche. Innodb_buffer_pool_reads conta le letture che hanno dovuto andare su disco. Se le letture fisiche aumentano rapidamente durante il traffico normale, il buffer pool potrebbe essere troppo piccolo per il working set, le query potrebbero scansionare troppi dati o un job batch potrebbe spingere pagine utili fuori dalla cache.

Le attese per lock sono un'altra fonte comune di dolore:

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_row_lock%';

L'aumento delle attese per lock di riga non significa automaticamente che InnoDB sia rotto. Di solito significa che le transazioni mantengono i lock più a lungo di quanto l'applicazione si aspetti. Cerca transazioni aperte, aggiornamenti lenti o percorsi di codice che avviano una transazione, chiamano servizi esterni e fanno commit molto più tardi.

Per maggiori dettagli su lock e transazioni, SHOW ENGINE INNODB STATUS\G può aiutare, ma il suo output è denso. Usalo quando la process list mostra attese per lock e hai bisogno di identificare il modello di transazione dietro di esse.

Uso Più Sicuro di `KILL`

KILL è utile, ma non è un pulsante di pulizia. Se killi una connessione che esegue una transazione di grandi dimensioni, MySQL potrebbe dover eseguire il rollback del lavoro, e il rollback può richiedere tempo. In alcuni incidenti è comunque la mossa giusta, ma prendi la decisione deliberatamente.

Preferisci killare prima la query quando la tua versione di MySQL e i permessi lo supportano:

KILL QUERY 12345;

Questo tenta di fermare l'istruzione corrente mantenendo viva la connessione. Se il client si comporta male o la connessione deve andare via, usa:

KILL CONNECTION 12345;

Prima di killare qualsiasi cosa, cattura la riga della process list, l'utente, l'host, il database e il testo SQL. Dopo l'incidente, quel dettaglio ti aiuta a risolvere la fonte invece di aspettare che la stessa query ritorni.

Stati Comuni della Process List e Cosa Suggeriscono

Sending data non significa sempre che MySQL stia inviando righe sulla rete. Spesso significa che il server sta leggendo, filtrando, ordinando o preparando righe. Se una query trascorre molto tempo lì, esegui EXPLAIN sull'istruzione e cerca scansioni di tabella, ordine di join scarso o indici mancanti.

Copying to tmp table o Creating sort index spesso indica ordinamenti o raggruppamenti costosi. Verifica se un indice può supportare il pattern WHERE e ORDER BY. A volte la query sta facendo esattamente ciò che il prodotto ha richiesto, ma appartiene a un report asincrono invece che a un percorso di richiesta.

Waiting for table metadata lock appare spesso quando DDL e query normali si scontrano. Un ALTER TABLE apparentemente semplice può attendere dietro una transazione aperta, mentre le query successive si accumulano dietro il DDL in sospeso. In tal caso, killare il bloccante più vecchio potrebbe essere più sicuro che killare ogni query in attesa.

Trasformare i Contatori in Prove Utili

Poiché i valori di SHOW STATUS sono per lo più contatori, i tassi sono più utili dei numeri grezzi. Cattura le stesse variabili due volte:

SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

Aspetta sessanta secondi, poi eseguile di nuovo. La differenza ti dice il tasso durante quel minuto. Questa è la stessa idea che usano i dashboard, ma farlo manualmente è utile quando hai solo accesso al terminale.

Prendi appunti durante gli incidenti. "Threads_running è passato da 8 a 90, la process list mostrava 70 query in attesa di metadata lock su orders, e Max_used_connections non è cambiato" è una diagnosi utile. "MySQL era lento" non lo è.

Quando Questi Comandi Non Sono Sufficienti

SHOW STATUS e SHOW PROCESSLIST sono strumenti di prima risposta. Non sostituiscono il slow query log, Performance Schema, i piani di esecuzione o le metriche a livello di host. Se lo stesso problema si ripresenta, abilita o rivedi il slow query log e ispeziona le peggiori istruzioni con EXPLAIN.

Per picchi di connessione ricorrenti, guarda le impostazioni del pool di connessioni dell'applicazione e il comportamento del deployment. Aumentare max_connections può far guadagnare tempo, ma può anche permettere al server di accettare più lavoro di quanto possa effettivamente eseguire. Per attese per lock ricorrenti, ispeziona i confini delle transazioni nell'applicazione. Una transazione che rimane aperta mentre il codice chiama un'API esterna può bloccare richieste non correlate e far sembrare MySQL più lento di quanto non sia.

Controlla anche l'host. Se la latenza del disco è alta, la CPU è satura, la memoria sta facendo swapping o un vicino rumoroso sta rubando risorse, i contatori di MySQL mostreranno i sintomi ma non l'intera causa. Una buona diagnosi combina comandi del database con metriche di sistema.

SHOW STATUS ti dà contatori e contesto. SHOW FULL PROCESSLIST ti dà il carico di lavoro in tempo reale. Usati insieme, ti aiutano a distinguere tra pressione delle connessioni, una singola query difettosa, contesa di lock, lavoro temporaneo pesante su disco e pressione della cache di InnoDB.