Usare Mytop per il monitoring delle prestazioni di MySQL

27 Agosto 2015 MySQL , Monitoring , Ottimizzazione del server CentOS

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:

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.rpm

Su 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 repolist

Se 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 -y

Lo 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 -y

Questo 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/.mytop

e aggiungete nel file ciò che segue, salvate ed uscite.

/root/.mytop
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/.mytop

e modificare ciò che segue:

/root/.mytop
 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ì:

/root/.mytop
 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 --prompt

Per 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:

 Output di mytop
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 now sarà 20. Il primo campo è il numero di query al secondo (qps now: 2). Il secondo valore è il numero di query lente al secondo. Il segmento Threads: 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.7k  mostra che dall’avvio, MySQL ha avuto in media 14.7kbps di traffico in entrata e 320.7kbps di traffico in uscita. Now in/out mostra 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_emp

Nella 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.

Facebooktwitterredditpinterestlinkedinmail

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.

*