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

FreeBSD Manual Pages


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

       mytop - display MySQL server performance	info like `top'

       mytop [options]

       The latest version of mytop is available	from it might also be on CPAN as

       In order	for mytop to function properly,	you must have the following:

	 * Perl	5.005 or newer
	 * Getopt::Long
	 * DBI and DBD::mysql
	 * Term::ReadKey from CPAN

       Most systems are	likely to have all of those installed--except for
       Term::ReadKey. You will need to pick that up from the CPAN. You can
       pick up Term::ReadKey here:

       And you obviously need access to	a MySQL	server (version	3.22.x or
       3.23.x) with the	necessary security to run the SHOW PROCESSLIST and
       SHOW GLOBAL STATUS commands.

       If you are a Windows user, using	ActiveState's Perl, you	can use	PPM
       (the Perl Package Manager) to install the MySQL and Term::ReadKey

   Optional Color Support
       In additon, if you want a color mytop (recommended), install
       Term::ANSIColor from the	CPAN:

       Once you	do, mytop will automatically use it. However, color is not yet
       working on Windows. Patches welcome. :-)

   Optional Hi-Res Timing
       If you want mytop to provide more accurate real-time queries-per-second
       statistics, install the Time::HiRes module from CPAN.  mytop will
       automatically notice that you have it and use it	rather than the
       standard	timing mechanism.

       mytop is	known to work on:

	 * Linux (2.2.x, 2.4.x)
	 * FreeBSD (2.2, 3.x, 4.x)
	 * Mac OS X
	 * BSDI	4.x
	 * Solaris 2.x
	 * Windows NT 4.x (ActivePerl)

       If you find that	it works on another platform, please let me know.
       Given that it is	all Perl code, I expect	it to be rather	portable to
       Unix and	Unix-like systems. Heck, it might even work on Win32 systems.

       Help is always welcome in improving this	software. Feel free to contact
       the author (see "AUTHOR"	below) with bug	reports, fixes,	suggestions,
       and comments. Additionally "BUGS" will provide a	list of	things this
       software	is not able to do yet.

       Having said that, here are the details on how it	works and what you can
       do with it.

   The Basics
       mytop was inspired by the system	monitoring tool	top. I routinely use
       top on Linux, FreeBSD, and Solaris. You are likely to notice features
       from each of them here.

       mytop will connect to a MySQL server and	periodically run the SHOW
       PROCESSLIST and SHOW GLOBAL STATUS commands and attempt to summarize
       the information from them in a useful format.

   The Display
       The mytop display screen	is really broken into two parts. The top 4
       lines (header) contain summary information about	your MySQL server. For
       example,	you might see something	like:

       MySQL on	localhost (4.0.13-log)			      up 1+11:13:00
	Queries: 19.3M	qps:  160 Slow:	    1.0		Se/In/Up/De(%):
		    qps	now:  219 Slow qps: 0.0	 Threads:    1 (   1/  16)
	Key Efficiency:	99.3%  Bps in/out: 30.5k/162.8	 Now in/out: 32.7k/

       The first line identifies the hostname of the server (localhost)	and
       the version of MySQL it is running. The right had side shows the	uptime
       of the MySQL server process in days+hours:minutes:seconds format	(much
       like FreeBSD's top) as well as the current time.

       The second line displays	the total number of queries the	server has
       processed, the average number of	queries	per second, the	number of slow
       queries,	and the	percentage of Select, Insert, Update, and Delete

       The third real-time values. First is the	number of queries per second,
       then the	number of slow queries,	followed by query precentages (like on
       the previous line).

       And the fourth line displays key	buffer efficiency (how often keys are
       read from the buffer rather than	disk) and the number of	bytes that
       MySQL has sent and received, both over all and in the last cycle.

       You can toggle the header by hitting h when running mytop.

       The second part of the display lists as many threads as can fit on
       screen. By default they are sorted according to their idle time (least
       idle first). The	display	looks like:

	   Id	  User	     Host      Dbase   Time	 Cmd Query or State
	   --	  ----	     ----      -----   ----	 --- --------------
	   61  jzawodn	localhost      music	  0    Query show processlist

       As you can see, the thread id, username,	host from which	the user is
       connecting, database to which the user is connected, number of seconds
       of idle time, the command the thread is executing, and the query	info
       are all displayed.

       Often times the query info is what you are really interested in,	so it
       is good to run mytop in an xterm	that is	wider than the normal 80
       columns if possible.

       The thread display color-codes the threads if you have installed	color
       support.	The current color scheme only works well in a window with a
       dark (like black) background. The colors	are selected according to the
       "Command" column	of the display:

	   Query   -  Yellow
	   Sleep   -  White
	   Connect -  Green

       Those are purely	arbitrary and will be customizable in a	future
       release.	If they	annoy you just start mytop with	the -nocolor flag or
       adjust your config file appropriately.

       mytop handles long and short command-line arguments. Not	all options
       have both long and short	formats, however. The long arguments can start
       with one	or two dashes `-' or `--'. They	are shown here with just one.

       -u or -user username
	   Username to use when	logging	in to the MySQL	server.	Default:

       -p or -pass or -password	password
	   Password to use when	logging	in to the MySQL	server.	Default: none.

       -h or -host hostname[:port]
	   Hostname of the MySQL server. The hostname may be followed by an
	   option port number. Note that the port is specified separate	from
	   the host when using a config	file. Default: ``localhost''.

       -port or	-P port
	   If you're running MySQL on a	non-standard port, use this to specify
	   the port number. Default: 3306.

       -s or -delay seconds
	   How long between display refreshes. Default:	5

       -d or -db or -database database
	   Use if you'd	like mytop to connect to a specific database by
	   default. Default: ``test''.

       -b or -batch or -batchmode
	   In batch mode, mytop	runs only once,	does not clear the screen, and
	   places no limit on the number of lines it will print. This is
	   suitable for	running	periodically (perhaps from cron) to capture
	   the information into	a file for later viewing. You might use	batch
	   mode	in a CGI script	to occasionally	display	your MySQL server
	   status on the web.

	   Default: unset.

       -S or -socket /path/to/socket
	   If you're running mytop on the same host as MySQL, you may wish to
	   have	it use the MySQL socket	directly rather	than a standard	TCP/IP
	   connection. If you do,just specify one.

	   Note	that specifying	a socket will make mytop ignore	any host
	   and/or port that you	might have specified. If the socket does not
	   exist (or the file specified	is not a socket), this option will be
	   ignored and mytop will use the hostname and port number instead.

	   Default: none.

       -header or -noheader
	   Sepcify if you want the header to display or	not. You can toggle
	   this	with the h key while mytop is running.

	   Default: header.

       -color or -nocolor
	   Specify if you want a color display.	This has no effect if you
	   don't have color support available.

	   Default: If you have	color support, mytop will try color unless you
	   tell	it not to.

       -i or -idle or -noidle
	   Specify if you want idle (sleeping) threads to appear in the	list.
	   If sleeping threads are omitted, the	default	sorting	order is
	   reversed so that the	longest	running	queries	appear at the top of
	   the list.

	   Default: idle.

       -prompt or -noprompt
	   Specify if you want to be prompted to type in your database
	   password.  This provides a little bit more security since it	not
	   only	prevents the password from viewable in a process list, but
	   also	doesn't	require	the password to	be stored in plain text	in
	   your	~/.mytop config	file.  You will	only be	prompted if a password
	   has not been	specified in your config file or through another
	   command line	option.

	   Default: noprompt.

	   If you have skip-resolve set	on MySQL (to keep it from doing	a
	   reverse DNS lookup on each inbound connection), mytop can replace
	   IP addresses	with hostnames but toggling this option.

	   Default: noresolve

       Command-line arguments will always take precedence over config file
       options.	That happens because the config	file is	read BEFORE the
       command-line arguments are applied.

   Config File
       Instead of always using bulky command-line parameters, you can also use
       a config	file in	your home directory ("~/.mytop"). If present, mytop
       will read it automatically. It is read before any of your command-line
       arguments are processed,	so your	command-line arguments will override
       directives in the config	file.

       Here is a sample	config file "~/.mytop" which implements	the defaults
       described above.


       Using a config file will	help to	ensure that your database password
       isn't visible to	users on the command-line. Just	make sure that the
       permissions on "~/.mytop" are such that others cannot read it (unless
       you want	them to, of course).

       You may have white space	on either side of the "=" in lines of the
       config file.

   Shortcut Keys
       The following keys perform various actions while	mytop is running.
       Those which have	not been implemented are listed	as such. They are
       included	to give	the user idea of what is coming.

       ?   Display help.

       c   Show	"command counters" based on the	Com_* values in	SHOW GLOBAL
	   STATUS.  This is a new feature.  Feedback welcome.

       d   Show	only threads connected to a particular database.

       f   Given a thread id, display the entire query that thread was (and
	   still may be) running.

       F   Disable all filtering (host,	user, and db).

       h   Only	show queries from a particular host.

       H   Toggle the header display. You can also specify either "header=0"
	   or "header=1" in your config	file to	set the	default	behavior.

       i   Toggle the display of idle (sleeping) threads. If sleeping threads
	   are filtered, the default sorting order is reversed so that the
	   longest running queries appear at the top of	the list.

       I   Switch to InnoDB Status mode.  The output of	"SHOW INNODB STATUS"
	   will	be displayed every cycle.  In a	future version,	this may
	   actually summarize that data	rather than producing raw output.

       k   Kill	a thread.

       m   Toggle modes. Currently this	switches from `top' mode to `qps'
	   (Queries Per	Second Mode). In this mode, mytop will write out one
	   integer per second. The number written reflects the number of
	   queries executed by the server in the previous one second interval.

	   More	modes may be added in the future.

       o   Reverse the default sort order.

       p   Pause display.

       q   Quit	mytop

       r   Reset the server's status counters via a FLUSH STATUS command.

       s   Change the sleep time (number of seconds between display

       u   Show	only threads owned by a	giver user.

       The s key has a command-line counterpart: -s.

       The h key has two command-line counterparts: -header and	-noheader.

       This is more of a BUGS +	WishList.

       Some performance	information is not available when talking to a version
       3.22.x MySQL server. Additional information (about threads mostly) was
       added to	the output of SHOW STATUS in MySQL 3.23.x and mytop makes use
       of it. If the information is not	available, you will simply see zeros
       where the real numbers should be.

       Simply running this program will	increase your overall counters (such
       as the number of	queries	run). But you may or may not view that as a

       mytop consumes too much CPU time	when running (verified on older
       versions	of Linux and FreeBSD). It's likely a problem related to
       Term::ReadKey. I	haven't	had time to investigate	yet, so	mytop now
       automatically lowers its	priority when you run it. You may also think
       about running mytop on another workstation instead of your database
       server. However,	"mytop"	on Solaris does	not have this problem.	Newer
       versions	of Linux and FreeBSD seem to have fixed	this.

       You can't specify the maximum number of threads to list.	If you have
       many threads and	a tall xterm, mytop will always	try to display as many
       as it can fit.

       The size	of most	of the columns in the display has a small maximum
       width. If you have fairly long database/user/host names the display may
       appear odd. I have no good idea as to how best to deal with that	yet.
       Suggestions are welcome.

       It'd be nice if you could just add mytop	configuration directives in
       your "my.cnf" file instead of having a separate config file.

       You should be able to specify the columns you'd like to see in the
       display and the order in	which they appear. If you only have one
       username	that connects to your database,	it's probably not worth	having
       the User	column appear, for example.

       mytop was developed and is maintained by	Jeremy D. Zawodny

       If you wish to e-mail me	regarding this software, PLEASE	subscribe to
       the mytop mailing list.	See the	mytop homepage for details.

       While I use this	software in my job at Yahoo!, I	am solely responsible
       for it. Yahoo! does not necessarily support this	software in any	way.
       It is merely a personal idea which happened to be very useful in	my

       If you hack Perl	and grok MySQL,	come work at Yahoo! Contact me for
       details.	Or just	send me	your resume. Er, unless	we just	had layoffs,
       in which	case we're not hiring. :-(

       Please check the	MySQL manual if	you're not sure	where some of the
       output of mytop is coming from.

       Copyright (C) 2000-2001,	Jeremy D. Zawodny.

       Fix a bug. Add a	feature. See your name here!

       Many thanks go to these fine folks:

       Sami Ahlroos (
	   Suggested the idle/noidle stuff.

       Jan Willamowius (
	   Mirnor bug report. Documentation fixes.

       Alex Osipov (
	   Long	command-line options, Unix socket support.

       Stephane	Enten (
	   Suggested batch mode.

       Richard Ellerbrock (
	   Bug reports and usability suggestions.

       William R. Mattil (
	   Bug report about empty passwords not	working.

       Benjamin	Pflugmann (
	   Suggested -P	command-line flag as well as other changes.

       Justin Mecham <>
	   Suggested setting $0	to `mytop'.

       Thorsten	Kunz <>
	   Provided a fix for cases when we try	remove the domain name from
	   the display even if it is actually an IP address.

       Sasha Pachev <>
	   Provided the	idea of	real-time queries per second in	the main

       Paul DuBois <>
	   Pointed out some option-handling bugs.

       Mike Wexler <>
	   Suggested that we don't mangle (normalize) whitespace in query info
	   by default.

       Mark Zweifel <>
	   Make	the --idle command-line	argument negatable.

       Axel Schwenke <>
	   Noticed the inccorect formula for query cache hit percentages in
	   version 1.2.

       Steven Roussey <>
	   Supplied a patch to help filter binary junk in queries so that
	   terminals don't freak out.

       jon r. luini <>
	   Supplied a patch that formed	the basis for "-prompt"	support.  Sean
	   Leach <> submitted a similar patch.

       Yogish Baliga <>
	   Supplied a patch that formed	the basis for "-resolve" support.

       Per Andreas Buer	<>
	   Supplied an excellent patch to tidy up the top display.  This
	   includes showing most values	in short form, such as 10k rather than

       See the Changes file on the mytop distribution page for more details on
       what has	changed.

       mytop is	licensed under the GNU General Public License version 2. For
       the full	license	information, please visit

perl v5.32.0			  2020-08-27			      MYTOP(1)


Want to link to this manual page? Use this URL:

home | help