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 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 is split into 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.
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.
$ sudo apt-get install mytop
$ 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 screenshot 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
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
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/