Operating Systems

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

http://jeremy.zawodny.com/mysql/mytop/

tar -zxvf mytop-<version>.tar.gz
cd mytop-<version>
perl Makefile.PL
make
make test
make install

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

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

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.