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/
