The Sysadmin's Daily Grind: Mtop

Health Check

By Charly Kühnast

We looked at ApacheTop in this column not too long ago. ApacheTop is a tool that tells you what your web server is doing right now, pointing out potential bottlenecks. In this month's column, we will be looking at another member of the top family: Mtop, the realtime monitor for MySQL [1]. The Mtop tarball weighs in at a mere 48 kbytes. It requires a few Perl modules: Curses, DBI, DBD::mysql, and Net::Domain - but you will probably have most of these installed anyway, and CPAN will fill any gaps in next to no time. After satisfying the dependencies, enter perl Makefile.PL, make, and make install in /usr/local/bin/ to complete the Mtop build.

The next step is to allow Mtop access to your MySQL process information, as - in contrast to Apachetop - Mtop does not simply parse the server logfiles, but requires direct access. I decided to set up a user with extremely restricted privileges in MySQL and did not assign a password. The following SQL prompt should do the trick for version 4.0.2 or newer of MySQL:

grant super, reload, process on *.*
  to mysqltop;
grant super, reload, process on *.*
  to mysqltop@localhost;
flush privileges;

The only critical thing is the process privilege. You can leave out super privileges if you do not intend to terminate queries from within Mtop.

Caretaking

MySQL versions 3.22.11 through 4.0.1 use the process privilege to assign the right to terminate queries. You need the following three lines for these versions:

grant reload, process on *.* to mysqltop;
grant reload, process on *.*
  to mysqltop@localhost;
flush privileges;

Those who prefer stricter restrictions can restrict Mtop's privileges even farther by removing the reload privilege. If this privilege is missing, Mtop will not be able to issue flush commands.

On Top of the World

Now it's finally time to launch Mtop for the first time. If the database is running on another machine, you will need to specify the target machine by setting the -h host parameter. If your database is not currently handling too many queries, the Mtop user interface should look quite neat and tidy, as you can see from the picture in Figure 1. This said, Mtop buys its uncluttered view by using not entirely intuitive abbreviations. Three of the most important abbreviations used by Mtop are:

• RRN: The number of queries requesting access to the next line in the table. A large value indicates that your tables are not indexed, or that the queries are not using your indexes.

• TLW: Indicates how many times requests for exclusive access to the table (table locks) have failed. The reason for this could be an overly large table or a poorly formulated query.

• QPS: The number of SQL commands since the server was launched or the last flush command was issued.

Just like the other members of the Top family, Mtop also supports keyboard shortcuts. Check the Mtop manpage to find out which keyboard shortcuts are supported.

Figure 1: Mtop gives admins the power to monitor the activity of a MySQL database. It even gives you a neat display, as long as you don't have to deal with too many queries.