Come abilitare il registro delle query lente di MySQL

Le query inaspettatamente lente sono uno dei problemi di prestazioni di MySQL più comuni . Una query che funziona in modo accettabile in fase di sviluppo può vacillare se sollecitata da un carico di lavoro di produzione.

Le applicazioni di grandi dimensioni potrebbero eseguire centinaia di query di database univoche ogni volta che viene colpito un endpoint. Ciò rende difficile individuare le query che causano ritardi nella risposta del server. Il log di query lente MySQL è un’opzione di debug che può aiutarti a identificare le istruzioni SQL sospette, fornendo un punto di partenza per le tue indagini.

Abilitazione del registro delle query lente

Il registro è un meccanismo integrato per la registrazione di query SQL di lunga durata. Le query che non vengono completate entro un tempo configurato verranno scritte nel registro. La lettura del contenuto del log mostra l’SQL che è stato eseguito e il tempo impiegato.

La registrazione lenta delle query è disattivata per impostazione predefinita. Puoi attivarlo sul tuo server eseguendo il seguente comando da una shell MySQL amministrativa:

SET GLOBAL slow_query_log_file='/var/log/mysql/mysql-slow.log';
SET GLOBAL slow_query_log=1;

La modifica si applica immediatamente. Le query lente verranno ora registrate in /var/log/mysql/mysql-slow.log. È possibile rivedere periodicamente questo file per identificare le query con prestazioni scadenti.

MySQL conta una query come “lenta” se il completamento richiede più di 10 secondi. Questo limite è generalmente troppo ridotto per le applicazioni Web rivolte agli utenti in cui sono previste risposte quasi istantanee. Puoi modificare il limite impostando la long_query_timevariabile:

SET GLOBAL long_query_time=1;

Il valore imposta la durata minima per le query lente. È importante trovare un equilibrio adatto alla propria applicazione. Una soglia troppo alta escluderà le query che influiscono effettivamente sulle prestazioni. Al contrario, valori molto bassi possono causare l’acquisizione di troppe query, creando un registro eccessivamente rumoroso.

Utilizzo del file di configurazione di MySQL

Dovresti abilitare il log della query lenta nel tuo file di configurazione MySQL se prevedi di usarlo a lungo termine. Ciò garantirà la ripresa automatica della registrazione dopo il riavvio del server MySQL.

La posizione del file di configurazione può variare in base alla distribuzione della piattaforma. Di solito è a /etc/mysql/my.cnfo /etc/mysql/mysql.conf.d/mysqld.cnf. Aggiungi le seguenti righe per replicare le impostazioni che sono state abilitate dinamicamente in precedenza:

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

Riavvia MySQL per applicare le modifiche:

$ sudo service mysql reboot

Il registro delle query lente sarà ora attivo ogni volta che si avvia il server MySQL.

Personalizzazione del contenuto del registro

Il registro normalmente include solo query SQL che escludono la soglia “lenta” e sono state inviate dalle applicazioni client. Ciò esclude qualsiasi operazione amministrativa lenta che potrebbe verificarsi, ad esempio la creazione di indici e l’ottimizzazione delle tabelle, nonché le query che potrebbero essere lente in futuro.

È possibile estendere il registro per includere queste informazioni apportando le seguenti modifiche al file di configurazione:

• log_slow_admin_statements = 1– Include istruzioni SQL amministrative come ALTER TABLE, CREATE INDEX, DROP INDEXe OPTIMIZE TABLE. Ciò è raramente auspicabile poiché queste operazioni vengono in genere eseguite durante gli script di manutenzione e migrazione. Tuttavia, questa impostazione può essere utile se anche l’applicazione esegue queste attività in modo dinamico.
• log_slow_replica_statements = 1– Questa impostazione consente la registrazione lenta delle query per le query replicate sui server di replica. Questa impostazione è disabilitata per impostazione predefinita. Utilizzare log_slow_slave_statementsinvece per le versioni MySQL 8.0.26 e precedenti.
• log_queries_not_using_indexes = 1– Quando questa impostazione è abilitata, le query che dovrebbero recuperare tutti i record dalla tabella o dalla vista di destinazione verranno registrate, anche se non escludono la soglia di query lenta. Questo può aiutare a identificare quando a una query manca un indice o non è in grado di usarlo. Le query che hanno un indice disponibile verranno comunque registrate se mancano di vincoli che limitano il numero di righe recuperate.

La registrazione delle query che non utilizzano gli indici può aumentare notevolmente la verbosità. Potrebbero verificarsi situazioni in cui è prevista o necessaria una scansione completa dell’indice. Queste query continueranno a essere visualizzate nel registro anche se non possono essere risolte.

Puoi valutare le query limit senza indici impostando la log_throttle_queries_not_using_indexesvariabile. Questo definisce il numero massimo di registri che verranno scritti in un periodo di 60 secondi. 10Verrà registrato un valore di significa fino a 10 query al minuto. Dopo il decimo evento, non verranno più registrate query fino all’apertura della finestra di 60 secondi successiva.

Interpretazione del registro delle query lente

Ogni query che viene inserita nel registro delle query lente visualizzerà una serie di righe simili alle seguenti:

# Ora: 2022-07-12T19:00:00.000000Z
# Utente@Host: demo[demo] @ mysql [] Id: 51
# Query_time: 3.514223 Lock_time: 0.000010 Rows_sent: 5143 Rows_examined: 322216
SELEZIONA * DA slow_table SINISTRA UNISCITI a un altro_table ...

Le righe commentate sopra la query contengono l’ora in cui è stata eseguita, l’utente MySQL con cui il client si è connesso e le statistiche che forniscono la durata e il numero di righe inviate. L’esempio precedente ha richiesto 3,5 secondi per essere completato e ha esaminato oltre 320.000 righe, prima di inviarne solo 5.143 al client. Questa potrebbe essere un’indicazione che gli indici mancanti stanno facendo sì che MySQL ispezioni troppi record.

È possibile includere facoltativamente più informazioni nel registro impostando la log_slow_extra = 1variabile di sistema nel file di configurazione. Ciò aggiungerà l’ ID del thread, il numero di byte ricevuti e inviati e il numero di righe considerate per l’ordinamento, nonché i conteggi delle richieste specifiche dell’istruzione che forniscono visibilità su come MySQL ha gestito la query.

Il file di registro deve essere trattato con cura in quanto il suo contenuto sarà sensibile. Le query vengono visualizzate per intero, senza alcuna mascheratura dei valori dei parametri. Ciò significa che i dati dell’utente saranno presenti se si utilizza il registro delle query lente su un server di produzione. L’accesso deve essere limitato agli sviluppatori e agli amministratori di database che stanno ottimizzando le istruzioni SQL.

Registrazione e backup delle query lenti

Una frustrazione comune con il lento registro delle query si verifica quando si utilizza MySQLDump anche per creare backup di database . SELECT * FROM ...Verranno eseguite query di lunga durata per recuperare i dati dalle tabelle e inserirli nel backup. Saranno inclusi nel registro delle query lente come qualsiasi altra istruzione SQL. Questo può inquinare il registro se esegui backup regolarmente.

È possibile risolvere questo problema disabilitando temporaneamente il registro delle query lente prima di eseguire mysqldump. È possibile riattivare il registro al termine del backup. Modifica lo script di backup in modo che appaia simile al seguente:

#!/bin/bash

# Disattiva temporaneamente la registrazione lenta delle query
mysql -uUser -pPassword -e "IMPOSTA GLOBAL slow_query_log=0";

# Esegui mysqldump
mysqldump -uUser -pPassword --single-transaction databaseName | gzip > backup.bak

# Abilita nuovamente il registro delle query lente
mysql -uUser -pPassword -e "IMPOSTA GLOBAL slow_query_log=1"

Ciò manterrà l’attività di MySQLDump fuori dal registro delle query lente, rendendo più facile concentrarsi sull’SQL eseguito dall’applicazione.

Riepilogo

Il log delle query lente di MySQL è uno dei modi più efficaci per individuare la causa dei problemi di prestazioni. Inizia stimando il ritardo che stai riscontrando e utilizza questo valore come tuo long_query_time. Riduci il valore se non viene visualizzato nulla nel registro dopo aver riprodotto il problema.

Il registro delle query lente non ti dirà esattamente come correggere il rallentamento. Tuttavia, la possibilità di visualizzare l’SQL esatto ricevuto dal server consente di ripetere le istruzioni con prestazioni scadenti e quindi di valutare l’effetto delle ottimizzazioni. L’aggiunta di un indice o di un vincolo mancante può fare la di