mytopp - Online in the Cloud

PROGRAM:

NAME

mytop - display MySQL server performance info like `top'

SYNOPSIS

mytop [options]

AVAILABILITY

The latest version of mytop is available from http://www.mysqlfanboy.com/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
* Config::IniFiles;
* 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 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
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]
Queries: 19.3M qps: 160 Slow: 1.0 Se/In/Up/De(%): 00/80/03/17
qps now: 219 Slow qps: 0.0 Threads: 1 ( 1/ 16) 00/74/00/25
Key Efficiency: 99.3% Bps in/out: 30.5k/162.8 Now in/out: 32.7k/ 3.3k

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:

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
Slow - Bright
Long - Magenta

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.

Arguments
mytop handles long and short command-line arguments. Not all options have both long and
short formats, however. The long arguments have two dashes `--'. Short arguments only have
one '-'.

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

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

WARNING: This is insecure as the password is visible for anyone. See --prompt
instead!

-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: none.

-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
Specify 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 --noi 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.

--resolve
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

--long or --nolong
For large numbers print all digits (e.g. 10.000) instead of using a more compact
approximation (e.g. 10.0k).

Default: nolong.

-m or --mode mode
Specify initial mode qps(queries/second), top(overview), cmd(command summary),
innodb(InnoDB status) or status().

Default: top

--sort or --nosort
Reverse sort order from ascending to descending using Idle time.

Default: nosort.

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.

user=root
pass=
host=localhost
db=test
delay=5
port=3306
slow=10
socket=
batchmode=0
header=1
color=1
idle=1
long=120

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.

If present, mytop will also read mysql's default config file "~/.my.cnf". Values from
both the [client] and [mytop] section are read, in this order. The long options database
and password are preferred over the short versions db and pass to match mysql's syntax.
These settings take lowest precedence and will be overwritten by either values in
"~/.mytop" or command-line options.

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 STATUS. This is a new
feature. Feedback welcome.

C Turn display color on and off. Default is on.

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.

R Togle IP reverse lookup. Default is on.

s Change the sleep time (number of seconds between display refreshes).

S Set the number of seconds a query will need to run before it is considered old and
will be highlighted.

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.

Use mytopp online using onworks.net services