mytop
-- A top-like Clone for MySQL
Werner Klauser
mytop is a console-based (non-GUI) tool
that allows you to monitor MySQL 3.22.x, 3.23.x, and 4.x servers
by viewing active threads, queries, and overall server performance
numbers. mytop was inspired by the popular system monitoring tool
"top". It runs on most UNIX systems (including Mac OS
X) that have Perl and the DBI and DBD::mysql modules installed.
Basically, it connects with the MySQL server, periodically runs
the SHOW PROCESSLIST and SHOW STATUS commands and
attempts to summarize this information in a useful and informative
format. A goal of mytop is to show SQL statements that persist longer
than expected. Often these can indicate where MySQL might be having
problems, where a query is not optimally set up, or where the creation
of indexes might help speed the query.
This useful tool was developed, copyrighted, and is maintained
by Jeremy D. Zawodny, a MySQL expert, and is licensed under the
GNU General Public License version 2. For full license information,
see:
http://www.gnu.org/copyleft/gpl.html
Availability and Installation
The latest version of mytop is available from:
http://jeremy.zawodny.com/mysql/mytop/
After downloading it, you must execute the following steps to install
it:
tar -zxvf mytop-<version>.tar.gz
cd mytop-<version>
perl Makefile.PL
make
make test
make install
Optional Color Support
If you want color support, install Term::ANSIColor from the CPAN:
http://search.cpan.org/search?dist=ANSIColor. mytop will
then automatically use colors to display its output.
mytop's Display
The mytop display screen is broken into two parts (Figure 1).
The top four lines (header) contain summary information about your
MySQL server:
- The first line identifies the hostname of your server (localhost)
and the version of your MySQL. The right-hand side shows the uptime
of the MySQL server process in days+hours:minutes:seconds format,
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, and the
number of slow queries.
- The third line deals with threads. Versions of MySQL before
3.23.x did not provide this information, which means that only
zeros are seen.
- The fourth line displays key buffer efficiency (how often keys
are read from the more efficient buffer rather than the disk)
and the number of bytes that your MySQL has sent and received.
You can toggle the header by using the "h" key when
running mytop.
The second part of the display lists as many threads as can fit
on the screen. By default, they are sorted according to their idle
time (least idle first). A display line looks like this:
ID User Host Dbase Idle Command Query Info
-- ---- ---- ----- ---- ------- ----------
220526 apache localhost ACholding 1 Query SELECT * FROM parts WHER
The user, apache (Web server), running locally is running the
SQL query beginning with SELECT * FROM parts WHER. Because
the query information is often the information in which you're
interested, and because it is limited to the screen's width,
it is best to run mytop in an xterm window that is as wide as possible.
Arguments and Configuration File
A list of mytop's arguments shows both the tool's flexibilities
and capabilities. See Figure 2. To avoid having to use bulky command-line
parameters, mytop also supports a configuration file named .mytop
found in your home directory. mytop automatically reads this configuration
file before command-line arguments are processed, which means that
command-line arguments will override directives found in the configuration
file.
The following ~/.mytop configuration file implements the
default command-line arguments:
user=root
pass=
host=localhost
db=test
delay=5
port=3306
batchmode=0
header=1
color=1
idle=1
It is best to use a configuration file when a database password is
used. This prevents users from seeing it on the command-line when
viewing the running processes using ps -ef or something similar.
Be sure that the permissions on ~/.mytop are adequate so that
inquisitive eyes cannot read it.
Another feature of mytop is its shortcut keys, which work while
mytop is running. See Figure 3.
Conclusion
You can use mytop not only to see what your system is doing properly,
but also to see what it could be doing better. Seeing a query for
too long might indicate a source of performance problems. mytop's
informative output not only shows how MySQL is working for you,
it might help it work better.
Werner Klauser is an independent UNIX consultant working near
Zurich, Switzerland. While not paragliding, enjoying his girls,
or roarin' around on his Harley chopper, he can be reached
at klauser@klauser.ch or via his Web page www.klauser.ch.
|