mytop (1) - Linux Manuals
mytop: display MySQL server performance info like `top'
NAME
mytop - display MySQL server performance info like `top'
SYNOPSIS
mytop [options]AVAILABILITY
The latest version of mytop is available from http://jeremy.zawodny.com/mysql/mytop/ it might also be on CPAN as well.REQUIREMENTS
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:
http://search.cpan.org/search?dist=TermReadKey
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 modules.
Optional Color Support
In additon, if you want a color mytop (recommended), install Term::ANSIColor from the CPAN:
http://search.cpan.org/search?dist=ANSIColor
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.Platforms
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.
DESCRIPTION
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 [23:29:11]
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
queries.
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:
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:
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.
Default: unset.
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.
Default: header.
Default: If you have color support, mytop will try color unless you
tell it not to.
Default: idle.
Default: noprompt.
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.
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.
More modes may be added in the future.
The s key has a command-line counterpart: -s.
The h key has two command-line counterparts: -header and
-noheader.
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
bug.
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.
If you wish to e-mail me regarding this software, PLEASE subscribe
to the mytop mailing list. See the mytop homepage for details.
Many thanks go to these fine folks:
See the Changes file on the mytop distribution page for more
details on what has changed.
Id User Host Dbase Time Cmd Query or State
-- ---- ---- ----- ---- --- --------------
61 jzawodn localhost music 0 Query show processlist
Query - Yellow
Sleep - White
Connect - Green
Arguments
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.
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.
user=root
pass=
host=localhost
db=test
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1
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.
BUGS
This is more of a BUGS + WishList.
AUTHOR
mytop was developed and is maintained by Jeremy D. Zawodny
(Jeremy [at] Zawodny.com).
DISCLAIMER
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 job.
RECRUITING
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. :-(
COPYRIGHT
Copyright (C) 2000-2001, Jeremy D. Zawodny.
CREDITS
Fix a bug. Add a feature. See your name here!
LICENSE
mytop is licensed under the GNU General Public License version
2. For the full license information, please visit
http://www.gnu.org/copyleft/gpl.html
SEE ALSO
Please check the MySQL manual if you're not sure where some of the
output of mytop is coming from.