How to use Mytop to monitor MySQL/MariaDB performance in Linux

Mytop is a console-based (non-gui) tool that display MySQL server performance information like “top”.

It will connect to a MySQL server and periodically run the SHOW PROCESSLISTand SHOW STATUS commands and attempt to summarize the information from them in a useful format, it was inspired by the system monitoring tool top.

Mytop display screen has two parts.

The first part contain summary information about your MySQL server, called header (Top 6 lines) and the second part display list of active threads currently running on MySQL.

Suggested Read :
(#) MySQL Tutorials
(#) MariaDB Tutorials
(#) phpMyAdmin Tutorials

After successful installation, just run mytop command in terminal and you will be getting the output similar to below.

# mytop

MySQL on localhost (5.5.55-cll)                              up 6+10:52:09 [21:02:55]
 Queries: 29.9M  qps:   56 Slow:    26.0         Se/In/Up/De(%):    89/00/01/00
             qps now:    9 Slow qps: 0.0  Threads:    4 (   1/   0) 66/02/00/00
 Key Efficiency: 100.0%  Bps in/out:  7.1k/271.7k   Now in/out: 826.8/201.1k

      Id      User         Host/IP         DB      Time    Cmd Query or State
       --      ----         -------         --      ----    --- ----------
   743938      root       localhost      mysql         0  Query show full processlist
   744404 daygeekc_       localhost daygeekc_t         0  Sleep
   744405 mageshmm_       localhost mageshmm_m         0  Sleep
   744406 daygeekc_       localhost daygeekc_t         0  Query SELECT * FROM daygeekc_wp

First line identifies the hostname of the server (localhost) and running version of MySQL. The right hand side shows the uptime of the MySQL server process in days+hours:minutes:seconds format as well as the current time.

MySQL on localhost (5.5.55-cll)                       up 6+10:17:54 [20:28:40]

The second line displays the total number of queries the server has processed, the average number of queries per second, the average number of slow queries per second, and percentage of SELECT, INSERT, UPDATE and DELETE queries.

Queries: 29.8M  qps:   56 Slow:    26.0         Se/In/Up/De(%):    89/00/01/00

The third line displays real-time average number of queries per second, real-time average number of slow queries per second, and alos shows the active & cached threads.

qps now:   66 Slow qps: 0.0  Threads:    2 (   1/   0) 89/01/00/00

Fourth line displays key buffer efficiency percentage, incoming buffer average & out coming buffer average in bytes, also it shows the same real-time information.

Key Efficiency: 100.0%  Bps in/out:  7.1k/272.0k   Now in/out:  8.9k/305.5k

The second part displays the list of active thread (thread id, username, host, database name, number of seconds of idle time, running command name, and query info) is currently running. By default they are sorted according to their idle time (least idle first).

      Id      User         Host/IP         DB      Time    Cmd Query or State
       --      ----         -------         --      ----    --- ----------
   742360      root       localhost      mysql         0  Query show full processlist
   742368   cphulkd       localhost    cphulkd         4  Sleep

How to install Mytop in Linux ?

Mytop is available in the repositories of most major distributions. So, use your distribution package manager to get installed.

For Debian based systems, use apt-get or apt package manager to install Mytop.

$ sudo apt-get install mytop

For RHEL/CentOS based systems, enable EPEL repository in order to install Mytop and use yum package manager.

$ sudo yum install mytop

For Fedora system, use dnf package manager to install Mytop.

$ sudo dnf install mytop

For openSUSE system, use zypper package manager to install Mytop.

$ sudo zypper in mytop

For Arch Linux based systems, use pacman package manager to install Mytop.

$ sudo pacman -S mytop

How to configure Mytop ?

Create a customized configuration file /root/.mytop in order to use Mytop. Refer sample configuration file.

$ sudo nano /root/.mytop
host=localhost
db=mysql
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1

How to use Mytop in Linux ?

Just fire the mytop command alone without any option, you will get the output like below.

# mytop

MySQL on localhost (5.5.55-cll)                              up 6+10:52:09 [21:02:55]
 Queries: 29.9M  qps:   56 Slow:    26.0         Se/In/Up/De(%):    89/00/01/00
             qps now:    9 Slow qps: 0.0  Threads:    4 (   1/   0) 66/02/00/00
 Key Efficiency: 100.0%  Bps in/out:  7.1k/271.7k   Now in/out: 826.8/201.1k

      Id      User         Host/IP         DB      Time    Cmd Query or State
       --      ----         -------         --      ----    --- ----------
   743938      root       localhost      mysql         0  Query show full processlist
   744404 daygeekc_       localhost daygeekc_t         0  Sleep
   744405 mageshmm_       localhost mageshmm_m         0  Sleep
   744406 daygeekc_       localhost daygeekc_t         0  Query SELECT * FROM daygeekc_wp

Run the following command to connect and monitor only a specific database.

# mytop -d daygeekc_than

MySQL on localhost (5.5.55-cll)                              up 6+11:43:13 [21:53:59]
 Queries: 30.0M  qps:   56 Slow:    26.0         Se/In/Up/De(%):    89/00/01/00
             qps now:   61 Slow qps: 0.0  Threads:    1 (   1/   0) 89/00/00/00
 Key Efficiency: 100.0%  Bps in/out:  7.1k/271.2k   Now in/out:  8.1k/357.7k

      Id      User         Host/IP         DB      Time    Cmd Query or State
       --      ----         -------         --      ----    --- ----------
   747197      root       localhost daygeekc_m         0  Query show full processlist
   747199      root       localhost daygeekc_m         0  Sleep

In the mytop thread view, press c to show “command counters” based on the Com_* values in SHOW GLOBAL STATUS.

           Command      Total  Pct  |  Last  Pct
           -------      -----  ---  |  ----  ---
            select   28015620  90%  |     1   2%
        set option    1086765   3%  |     1   2%
         change db     684749   2%  |     1   2%
       show tables     384118   1%  |     1   2%
            update     369023   1%  |     1   2%
       show fields     125676   0%  |     1   2%
            insert      85198   0%  |     1   2%
            delete      62374   0%  |     1   2%
      stmt execute      12991   0%  |     1   2%
      stmt prepare      12991   0%  |     1   2%
        stmt close      12991   0%  |     1   2%
       empty query       5447   0%  |     1   2%
  show processlist       3467   0%  |     1   2%
       show status       1586   0%  |     1   2%

In the mytop thread view, press f to display the entire query that thread was (and still may be) running.

Full query for which thread id: 744406

Enter the thread id for the query you want to see. In our case the thread id 744406.

Thread 751271 was executing following query:

SELECT * FROM daygeekc_wp WHERE ...

-- paused. press any key to resume or (e) to explain --

To see the detailed information about given id press e.

EXPLAIN SELECT * FROM dept_emp:

*** row 1 ***
          table:  daygeekc_wp
           type:  ALL
  possible_keys:  NULL
            key:  NULL
        key_len:  NULL
            ref:  NULL
           rows:  50000
          Extra:  NULL
-- paused. press any key to resume --

To know more option about Mytop, press ? to print the option view.

Help for mytop version 1.7 by Jeremy D. Zawodny <[email protected]>

  ? - display this screen
  # - toggle short/long numbers (not yet implemented)
  c - command summary view (based on Com_* counters)
  d - show only a specific database
  e - explain the query that a thread is running
  f - show full query info for a given thread
  F - unFilter the dispaly
  h - show only a specifc host's connections
  H - toggle the mytop header
  i - toggle the display of idle (sleeping) threads
  I - show innodb status
  k - kill a thread
  p - pause the display
  m - switch [mode] to qps (queries/sec) scrolling view
  o - reverse the sort order (toggle)
  q - quit
  r - reset the status counters (via FLUSH STATUS on your server)
  s - change the delay between screen updates
  t - switch to thread view (default)
  u - show only a specific user
  : - enter a command (not yet implemented)

http://jeremy.zawodny.com/mysql/mytop/

Leave a Reply

Your email address will not be published. Required fields are marked *