man mtop Command

Man page for apt-get mtop Command

Man Page for mtop in Linux

Ubuntu Man Command : man mtop

Man Mtop  Command

This tutorial shows the man page for man mtop in linux.

Open terminal with 'su' access and type the command as shown below:
man mtop

Result of the Command Execution shown below:

MTOP(1p)                                                         User Contributed Perl Documentation                                                        MTOP(1p)

mtop Shows top mysql threads

mtop [ host={mysql_host}] [ dbuser={mysql_user}]
[ password={mysqluser_pw}] [ seconds={refresh}] [ [no]idle]
[ filter user={regex}] [ filter host={regex}] [ filter db={regex}]
[ filter command={regex}] [ filter state={regex}] [ filter info={{regex}}]
[ fold select columns]
[ user={user}] [ manualrefresh] [ slow={seconds}] [ vs|veryslow={seconds}]
[ vvs|veryveryslow={seconds}]

mtop help

mtop version

Shows the MySQL commands consuming the greatest time. By default, only non sleeping threads are shown, the idle option shows idle threads. While running
several keys will affect the operation of mtop. Hitting h or ? will show the available options.

Normally, run as a console program this will allow you to see errant or badly optimized queries as they will stay on the screen for a while. However, if you
are hunting for short lived queries, running in the manualrefresh mode with a short refresh time will allow you to catch short lived queries as well.

The following keys are active while mtop is running:

q quit
? help


s change the number of seconds to delay between updates
m toggle manual refresh mode on/off
d filter display with regular expression (user/host/db/command/state/info)
F fold/unfold column names in select statement display
h display process for only one host
u display process for only one user
i toggle all/non Sleeping process display
o reverse the sort order


k kill processes; send a kill to a list of ids
e explain a process; show query optimizer info
z zoom in on a process; show sql statement detail
f flush stats (reset show status variables)
t show mysqld stats (show status/mysqladmin ext)
T show short/important status
v show mysqld variables (show variables/mysqladmin vars)
r show replication status for master/slaves

Main Screen

The main query screen shows the following information as well as the currently active queries (explanations are from the MySQL online manual and references
refer to the section in the manual where the explanation came from):

n Threads: running, cached
The n Threads represents how many threads the mysqld has allocated. One thread is allocated for each user connection. Additional threads are allocated
for replication.

Queries/slow: Total queries / Total SLOW QUERIES
The first number is the total number of queries sent to the server since the last 'flush status' or since server start. The second number is the number
of queries that have taken more than long_query_time. See section 4.9.5 The Slow Query Log.

Cache Hit: Cache hit ratio
This is the percentage of times a key read is handled from the key buffer cache. See section SHOW VARIABLES of the MySQL manual for more

Opened tables: tables opened
MySQL has a cache for open tables. If 'opened tables' is high, your cache may be too small. Look at the MySQL manual section: 5.4.7 How MySQL Opens and
Closes Tables for further information.

RRN: Handler_read_rnd_next
Number of requests to read the next row in the datafile. This will be high if you are doing a lot of table scans. Generally this suggests that your
tables are not properly indexed or that your queries are not written to take advantage of the indexes you have. (

TLW: Table_locks_waited
Number of times a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should
first optimise your queries, and then either split your table(s) or use replication. Available after 3.23.33. (

SFJ: Select_full_join
Number of joins without keys (If this is not 0, you should carefully check the indexes of your tables). (

SMP: Sort_merge_passes
Number of merges passes the sort algoritm have had to do. If this value is large you should consider increasing sort_buffer. (

QPS: Questions per second
The total number of sql commands handled by the MySQL server since startup or the last flush status command.


When viewing the stats screen (t), the screen will refresh until a key is pressed at which point you will return to the main screen. The bottom of the stats
screen is denoted with a line containing . If you do not see that line, resize your screen until you do.

The statistics screen has the following format:

Stat: total [avg per sec / instant per sec ]

For example:

Questions: 720,672 [30/12]

The short/important status screen is a list of recommendations from the MySQL manual.

The first number is the total since startup or the last 'flush status'. The second number is the number per second since startup or flush. The last is the
number per second since the last screen refresh.

The variables screen only shows the information once and returns to the main screen as the variables do not change after server startup.


The replication monitor screen looks for a master or slave server running on the currently monitored mysqld. If a master server is found, it then tries to
connect to each slave connected to the master. Replication is shown for all masters and slaves found. Offsets from the master for each of the slaves is
shown. Note: the offset may be less than zero because the slave position is checked after the master position. The offset shown is the number of queries in
the binlog that the slave has to process before being caught up with the master.

All options can be abbreviated by their shortest unique abbreviation.

?, help
Show the help screen and exit.

v, version
Show the version number and exit.

h {mysql_host}, host={mysql_host}
By default, the mysqld on localhost is monitored. Specify an alternate host with this option.

dbu {mysql_user}, dbuser={mysql_user}
By default, the user 'mysqltop' is used to connect to the database. Specify an alternate user with this option.

p {mysqluser_pw}, password={mysqluser_pw}
By default, there is no password associated with the mysqltop user, specify a password with this option.

se {refresh}, seconds={refresh}
The default screen refresh is 5 seconds.

sl {seconds}, slow={seconds}
The number of seconds before a slow query is highlighted. The default is the server's long_query configuration variable.

vs {seconds}, veryslow={seconds}
The number of seconds before a very slow query is highlighted. The default is the the slow option * 2.

vvs {seconds}, veryveryslow={seconds}
The number of seconds before a very very slow query is highlighted. The default is the the slow option * 4.

i, [no]idle
By default, processes in the Sleep command state are not shown. This option turns on display of idle threads.

u {user}, user={user}
Show only threads owned by this user.

fu {regex_pattern}, filter user={regex_pattern}
fh {regex_pattern}, filter host={regex_pattern}
fd {regex_pattern}, filter db={regex_pattern}
fs {regex_pattern}, filter state={regex_pattern}
fc {regex_pattern}, filter command={regex_pattern}
fi {regex_pattern}, filter info={regex_pattern}
Filter the display based on the regex_pattern provided. The regex_pattern is a perl regular expression. The regular expression match is done with case

For example, to only show select statements on the user table, use the following:

filter info='select from user'

or, to be more forgiving for mutil table joins and extra spaces, use:

filter info='select\s+from\s+.*user.*where'

These same regular expression filters can be used with the interactive d command. Be careful to escape any special shell characters in the regex.

m, manualrefresh
In this mode, the screen only refreshes when the user hits a key on the keyboard. The screen will refresh automatically until a query is seen and then
wait for further input. An uppercase M will appear in the top right hand corner of the screen to indicate that you are in this mode.

All options can be stored in initialization files. Command line options override options stored in the initialization file(s). The following files are
checked for arguments: current direcotry .mtoprc, home directory .mtoprc, /usr/local/etc/mtoprc, /etc/mtoprc. Options in the former files override options
in the later files.

The format of the initialization file is one option per line. Options are specified just as they would be on the command line. They can be abbreviated and
use the one or two hyphen syntax. Comments and blank lines are ignored. The following is an exmple .mtoprc file which sets a user filter to user1 and sets
the refresh rate to one second:

Related Topics

Apt Get Commands