Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
MTOP(1)		      User Contributed Perl Documentation	       MTOP(1)

NAME
       mtop - Shows top	mysql threads

SYNOPSIS
	   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

DESCRIPTION
       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

	   Filtering/display

	   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

	   Control/Detail

	   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 4.5.7.4 SHOW VARIABLES of	the MySQL
	   manual for more information.

       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.
	   (4.5.7.3)

       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.
	   (4.5.7.3)

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

       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.
	   (4.5.7.3)

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

       Statistics/Variables

       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.

       Replication

       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.

OPTIONS
       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 insensitivity.

	   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+.*\buser\b.*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:

	   #  Only look	at 'user1'
	   -fu user1
	   --seconds=1	 # refresh every one seconds

SETUP
       The most	convenient way to setup	your system to use mtop	is to create a
       database	user called mysqltop which has no password.  For security
       purposes, this user should have all privileges set to N except
       Process_priv which must be set to Y.

       To grant	these privileges, execute the following	from the MySQL command
       prompt

       For mysql 4.0.2 and greater:

	   mysql> grant	super, reload, process on *.* to mysqltop;
	   mysql> grant	super, reload, process on *.* to mysqltop@localhost;
	   mysql> flush	privileges;

       For mysql 3.x and 4.0.1:

	   mysql> grant	reload,	process	on *.* to mysqltop;
	   mysql> grant	reload,	process	on *.* to mysqltop@localhost;
	   mysql> flush	privileges;

       Notes:

       o   GRANT only works in MySQL 3.22.11 or	later, for earlier versions
	   add the user	manually and fix the permissions as noted above.

       o   The GRANT to	mysqltop and mysqltop@localhost	may be modified
	   depending upon which	hosts you want to grant	access from.  In
	   general, you	probably want to limit it to the hosts in your domain.

       o   The reload privilege	is not required	for mtop to do basic
	   monitoring.	It is only needed if you wish to issue flush commands
	   from	mtop.  The super privilege is needed if	you wish to kill
	   queries in mysql 4.0.2 versions and above.  In 3.x, the process
	   privilege allows a user to terminate	a query.

       Initially, mtop does not	connect	to a specific database.	 Most commands
       this program issues are non-database specific (SHOW FULL	PROCESSLIST,
       SHOW VARIABLES, KILL id).  However, when	database-specific commands are
       needed, mtop will try to	connect	to the the required database and
       prompt for a username/password if the default one fails.

       To install mtop,	run the	following shell	commands:

	   perl	Makefile.PL
	   make
	   make	install

       The default {install_prefix} is /usr/local which	means that mtop	is
       installed in /usr/local/bin/.  To change	this, run:

	   perl	Makefile.PL --prefix={install_prefix}

       or modify the PREFIX line in Makefile.PL.

       Requires	the following perl modules:

	   Module	 Available At
	   ------------	 --------------------------------------------------------
	   Curses	 http://www.cpan.org/authors/id/WPS
	   DBI		 Distributed as	Bundle::DBI: http://www.cpan.org/authors/id/TIMB
	   DBD::mysql	 http://www.cpan.org/authors/id/JWIED
	   Getopt::Long	 (Distributed with Perl	5)
	   Net::Domain	 Part of libnet: http://www.cpan.org/authors/id/GBARR/

AUTHOR
       Marc Prewitt, Chelsea Networks <mprewitt@chelsea.net>

       Copyright (C) 2002 Marc Prewitt/Chelsea Networks, under the GNU GPL.
       mtop comes with ABSOLUTELY NO WARRANTY. This is free software, and you
       are welcome to redistribute it under certain conditions;	see the
       COPYING file for	details.

KNOWN BUGS
       The cache hit ratio may look very low if	your server receives more than
       4 billion key_read_requests.  This is due to a roll-over	condition.  If
       that number exceeds 4 billion and change, it rolls over to 1.  The
       cache hit ratio is calculated as: 100 - (key_reads/key_read_requests) *
       100.

       If you are really trying	to focus on the	cache hit ratio, flush the
       status variables	with the mtop f	command	to reset both the key_reads
       and key_read_requests.

       Win2K telnet.exe	- If you are using the Windows 2000 telnet program, it
       defaults	to ansi	mode which doesn't work	well with curses (in my
       testing on Solaris 8).  To work around this, set	the terminal type to
       vt100.  To do this, issue the following command from the	telnet prompt
       before connecting to a host:

	   set term vt100

       Alternatively, you can manually set your	TERM environment variable to
       vt100 after you are logged in.

TODO
       Offer sorts by other columns

       For the 'More:' paging, it would	be nice	to support 'Less' behaviour.

       Add 'n' command and properly calculate number of	lines on screen.

	   $Id:	mtop.PL,v 1.61 2004/09/12 22:22:03 mdprewitt Exp $

POD ERRORS
       Hey! The	above document had some	coding errors, which are explained
       below:

       Around line 2000:
	   '=end' without a target? (Should be "=end showsatus")

perl v5.32.0			  2020-08-29			       MTOP(1)

NAME | SYNOPSIS | DESCRIPTION | OPTIONS | SETUP | AUTHOR | KNOWN BUGS | TODO | POD ERRORS

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=mtop&sektion=1&manpath=FreeBSD+12.2-RELEASE+and+Ports>

home | help