Usare Mytop per il monitoring delle prestazioni di MySQL
Introduzione
Mytop è uno strumento open source a riga di comando usato per il monitoring delle prestazioni di MySQL. È stato ispirato dallo strumento di monitoring di Linux chiamato top e gli assomiglia anche nell’aspetto. Mytop si connette ad un server MySQL ed esegue periodicamente i comandi show processlist e show global status. Quindi riassume le informazioni in un formato pratico. Tramite l’utilizzo di mytop possiamo monitorare (in tempo reale) i thread MySQL, le query ed i tempi di uptime, nonché vedere quale utente sta eseguendo delle query, su quale database lo sta facendo, quali sono le query lente ed altro ancora. Tutte queste informazioni possono essere utilizzate per ottimizzare le prestazioni di MySQL.
In questo tutorial vedremo come installare, configurare ed utilizzare mytop.
Prerequisiti
Prima di iniziare il tutorial, dovreste avere sotto mano quanto segue:
- Una Droplet [la VPS di DigitalOcean, NdT] con CentOS 7 64-bit (funziona pure con CentOS 6)
- Un utente non root con privilegi sudo. Per impostare un utente di questo tipo, seguite il tutorial Configurazione iniziale del server con CentOS 7. Tutti i comandi saranno eseguiti da questo utente.
- Un server MySQL in esecuzione sulla Droplet. Per installare MySQL seguite il passo #2 dell’articolo Come installare lo stack Linux, Apache, MySQL, PHP (LAMP) su CentOS.
Fase 1 – Installare Mytop
Andiamo ad installare i pacchetti necessari per mytop.
In primo luogo, abbiamo bisogno di installare sul server il repository yum EPEL (Extra Packages for Enterprise Linux [Pacchetti aggiuntivi per Enterprise Linux]). EPEL è un gruppo all’interno di Fedora che crea, mantiene e gestisce un insieme di pacchetti open source aggiuntivi, di alta qualità, per Enterprise Linux. Eseguite il seguente comando per installare ed attivare il repository EPEL sul server:
Su CentOS 7:
$ sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpmSu CentOS 6:
$ sudo rpm -ivh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
Prima di procedere, verificate che il repository EPEL venga abilitato tramite il comando:
$ sudo yum repolistSe viene abilitato, nell’output si vedrà anche il seguente repository:
epel/x86_64 Extra Packages for Enterprise Linux 7 - x86_64
Cerchiamo quindi di proteggere i pacchetti base da EPEL utilizzando il plugin di yum protectbase.
$ sudo yum install yum-plugin-protectbase.noarch -yLo scopo del plugin protectbase è quello di proteggere alcuni repository yum da aggiornamenti di altri repository. I pacchetti dei repository protetti non verranno aggiornati o sovrascritti dai pacchetti nei repository non protetti anche se il repository non protetto ne contiene una versione successiva.
Ora siamo pronti ad installare il pacchetto mytop. Per installarlo, eseguite il seguente comando:
$ sudo yum install mytop -yQuesto installerà il pacchetto mytop così come tutte le sue dipendenze, per lo più moduli Perl.
Fase 2 – Configurare Mytop
Prima di utilizzare mytop, va creato un file di configurazione personalizzato chiamato .mytop . Eseguite il comando:
$ sudo nano /root/.mytope aggiungete nel file ciò che segue, salvate ed uscite.
host=localhost
db=mysql
delay=5
port=3306
socket=
batchmode=0
color=1
idle=1 Questo file di configurazione verrà utilizzato quando si esegue mytop direttamente come root e quando lo si esegue con il comando sudo anteposto ad esso, come un utente sudo non root.
È possibile apportare modifiche al file di configurazione a seconda delle esigenze. Ad esempio, l’opzione delay specifica la quantità di tempo in secondi che intercorre tra gli aggiornamenti nella visualizzazione. Se si desidera effettuare il refresh di mytop ogni 3 secondi, è possibile modificare il file /root/.mytop utilizzando
$ sudo nano /root/.mytope modificare ciò che segue:
delay= 3
Il parametro idle specifica se far apparire nell’elenco i thread idle (in sleep) nel display di mytop. Il valore predefinito è di mostrare i thread idle. Se i thread idle vengono omessi l’ordinamento di default è invertito, in modo che le query in esecuzione da più tempo appaiano in cima alla lista. Se si desidera farlo, modificate il file /root/.mytop così:
idle= 0
È possibile fare riferimento alle pagine del manuale di mytop per le informazioni su tutti i parametri del file di configurazione, esse contengono la descrizione di ogni parametro. Per accedere alle manpage, utilizzate il comando:
$ man mytop È possibile digitare q per uscire dal manuale.
Fase 3 – Connettersi a Mytop
In questa sezione si discuterà il modo per connettersi a mytop e come utilizzare quest’ultimo per visualizzare le query MySQL.
Mytop richiede le credenziali per accedere al database, che possono essere fornite attraverso un prompt sulla riga di comando oppure memorizzati nel file di configurazione. Per maggiore sicurezza useremo l’opzione --prompt di mytop, che ogni volta chiede la password.
Cerchiamo di connetterci a mytop utilizzando:
$ sudo mytop --prompt ed inseriamo la password di root di MySQL al prompt. Con mytop è possibile inoltre utilizzare da riga di comando diversi altri argomenti. Per l’elenco completo consultate la manpage. Ad esempio se si desidera connettersi a mytop utilizzando un diverso utente MySQL, poniamo sia Sammy, eseguite il comando:
$ sudo mytop -u sammy --promptPer connettere e monitorare un solo specifico database, è possibile utilizzare il comando:
$ sudo mytop -d nomedatabase --prompt Per uscire da mytop e tornare al prompt della shell, digitate q .
Fase 4 – Visualizzare ed interpretare il Display di Mytop
In questa sezione vedremo come interpretare il display di mytop e le diverse funzionalità offerte da questo strumento.
Una volta che ci si è collegati a mytop utilizzando mytop --prompt ci verrà mostrata la visualizzazione dei thread. Vedremo qualcosa di simile a:
MySQL on localhost (5.5.41-MariaDB) up 0+00:05:52 [01:33:15]
Queries: 148 qps: 0 Slow: 0.0 Se/In/Up/De(%): 09/00/00/00
qps now: 2 Slow qps: 0.0 Threads: 6 ( 5/ 0) 67/00/00/00
Key Efficiency: 2.0% Bps in/out: 14.7/320.7k Now in/out: 192.5/731.8k
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
2 root localhost mysql 0 Query show full processlist
16 root localhost 0 Sleep
17 root localhost testdb 0 Query SELECT * FROM dept_emp
18 root localhost testdb 0 Query SELECT * FROM dept_emp
19 root localhost testdb 0 Query SELECT * FROM dept_emp
20 root localhost testdb 0 Query SELECT * FROM dept_emp Si può tornare a questo tipo di visualizzazione, se ci si trova in un’altra, digitando t .
L’output qui sopra è suddiviso in due parti. Le prime quattro linee comprendono l’intestazione che può essere attivata o disattivata premendo SHIFT-H. L’intestazione contiene informazioni riassuntive su MySQL.
- La prima riga identifica l’hostname del server e la versione di MySQL in esecuzione. Il lato destro mostra il tempo di attività del processo del server MySQL nel formato giorni+ore:minuti:secondi formato oltre all’ora corrente.
- La seconda riga mostra il numero totale di query che il server sta eseguendo (nel nostro caso 148), il numero medio di query al secondo, il numero di query lente e la percentuale delle query di SELECT, INSERT, UPDATE e DELETE.
- La terza linea mostra valori in tempo reale dall’ultimo refresh di mytop. Il tipico tempo di aggiornamento (ritardo) per mytop è di 5 secondi; quindi se sono state eseguite 100 query negli ultimi 5 secondi dall’ultimo aggiornamento, allora il numero
qps nowsarà 20. Il primo campo è il numero di query al secondo (qps now: 2). Il secondo valore è il numero di query lente al secondo. Il segmentoThreads: 6 ( 5/ 0)indica che ci sono un totale di 6 thread connessi, 5 sono attivi (uno è in sleep) e ci sono 0 thread nella cache dei thread. L’ultimo campo nella terza riga mostra le percentuali di query, come nella riga precedente, ma dall’ultimo refresh di mytop. - La quarta riga mostra l’efficienza del buffer delle chiavi (quanto spesso le chiavi vengono lette dal buffer piuttosto che dal disco) e il numero di byte che MySQL ha inviato e ricevuto, sia globalmente che nel corso dell’ultimo ciclo di mytop.
Key Efficiency: 2.0%mostra che il 2% delle chiavi vengono lette dal buffer, non dal disco.Bps in/out: 14.7/320.7kmostra che dall’avvio, MySQL ha avuto in media 14.7kbps di traffico in entrata e 320.7kbps di traffico in uscita.Now in/outmostra ancora una volta il traffico, ma dall’ultimo refresh di mytop.
La seconda parte del display elenca i thread MySQL correnti, ordinati in base al loro tempo di inattività (per prima il meno inattivo). Se necessario è possibile invertire l’ordine premendo O. Qui vengono visualizzati il thread id, il nome utente, l’host da cui l’utente si connette, il database a cui è collegato l’utente, il numero di secondi di inattività, il comando che sta eseguendo il thread (oppure lo stato del thread) e la prima parte delle informazioni sulla query. Se il thread è in uno stato di Query (cioè Cmd mostra Query) allora la colonna successiva Query or State mostrerà la prima parte della query in esecuzione. Se lo stato del comando è Sleep o Idle allora la colonna Query or State di solito è vuota. Nel nostro esempio qui sopra, il thread con id 2 è in realtà mytop che sta eseguendo la query show processlist per raccogliere informazioni. Il thread con id 16 è in sleep (non sta eseguendo una query ma è ancora connesso). Il thread con id 17 sta eseguendo una query SELECT sul database testdb.
Ora che abbiamo capito le basi del display di mytop, vedremo come usarlo per raccogliere ulteriori informazioni sui thread MySQL e sulle query. Diamo un’occhiata al seguente display mytop:
[secondary_output Output of mytop]
MySQL on localhost (5.5.41-MariaDB) up 0+00:13:10 [23:54:45]
Queries: 2.8k qps: 4 Slow: 51.0 Se/In/Up/De(%): 45/00/00/00
qps now: 17 Slow qps: 0.0 Threads: 52 ( 51/ 0) 96/00/00/00
Key Efficiency: 100.0% Bps in/out: 215.4/ 7.6M Now in/out: 2.0k/16.2M
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
34 root localhost testdb 0 Query show full processlist
1241 root localhost 1 Sleep
1242 root localhost testdb 1 Query SELECT * FROM dept_emp
1243 root localhost testdb 1 Query SELECT * FROM dept_emp
1244 root localhost testdb 1 Query SELECT * FROM dept_emp
1245 root localhost testdb 1 Query SELECT * FROM dept_emp
1246 root localhost testdb 1 Query SELECT * FROM dept_emp
1247 root localhost testdb 1 Query SELECT * FROM dept_empNella visualizzazione dei thread di mytop (visualizzazione predefinita) qui sopra, le query vengono troncate. Per visualizzare l’intera query è possibile premere F e vi verrà chiesto:
Full query for which thread id: [la query completa per quale thread id] Inserite l’id per la query che si desidera vedere. Ad esempio digitate 1244 . Di seguito mostrerà:
Thread 1244 was executing following query:
SELECT * FROM dept_emp WHERE ...
-- paused. press any key to resume or (e) to explain --Possiamo digitare e per fare un explain della query. Ciò spiegherà la query in esecuzione in modo da poter capire se tale query sia ottimizzata. EXPLAIN è uno dei più potenti strumenti per la comprensione e l’ottimizzazione delle query MySQL che danno problemi. Per esempio:
EXPLAIN SELECT * FROM dept_emp:
*** row 1 ***
table: dept_emp
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 332289
Extra: NULL
-- paused. press any key to resume -- È possibile premere un tasto qualsiasi per uscire da questa modalità o digitare t per tornare alla visualizzazione dei thread predefinita.
Un’altra visualizzazione utile disponibile in mytop è la visualizzazione dei comandi. Per accedere alla visualizzazione dei comandi digitate c . Sarà simile a ciò che segue:
Command Total Pct | Last Pct
------- ----- --- | ---- ---
select 1782 55% | 100 8%
show status 723 22% | 533 45%
show processlist 708 22% | 532 45%
change db 2 0% | 0 0%
show variables 1 0% | 0 0%
Compression 0 0% | 0 0%La colonna Command indica il tipo di comando oppure una query in esecuzione. La colonna Total indica il numero totale di quel tipo di comando eseguito a partire dall’avvio del server e la colonna Pct mostra la stessa cosa in percentuale. Dall’altro lato della linea verticale abbiamo la colonna Last che ci dice il numero di quel tipo di comando eseguito dall’ultimo refresh di mytop. Queste informazioni ci forniscono una visione di quello che il server MySQL sta facendo a breve e a lungo termine.
In questo tutorial abbiamo discusso acune delle caratteristiche essenziali ed utili di mytop. Ce ne sono molte altre disponibili. Per visualizzare un elenco completo delle opzioni è possibile premere il tasto ? quando mytop è in esecuzione.
Conclusioni
Ora dovreste avere avere una buona comprensione di come usare di mytop per monitorare il vostro server MySQL. È anche un punto di partenza per trovare le query SQL problematiche ed ottimizzarle, aumentando così le prestazioni globali del server. È possibile ottenere ulteriori informazioni su come ottimizzare le query MySQL e le tabelle sul server in questo tutorial .
Autore: Veena K John
Redattore: Tammy Fox
Traduzione dell’articolo https://www.digitalocean.com/community/tutorials/how-to-use-mytop-to-monitor-mysql-performance Copyright © 2016 DigitalOcean™ Inc.
