Watching Your MySQL Traffic with mtop 325

If you use MySQL, the MySQL Control Center is a handy tool to add to your collection. Check it out!

Watching Your MySQL Traffic with mtop

MySQLCC is a great monitoring tool if you’re running in a graphical environment, but if you keep your servers and data in a non-GUI environment, use mtop. mtop is a handy command line tool that lets you watch your database traffic and transactions. You can also use mtop to help find and debug slow queries.

Graphical tools are available everywhere nowadays, but you can save money and improve performance by keeping your MySQL servers in a nongraphical environment.

Gathering all the packages that mtop needs

You need a number of prerequisite packages to run mtop. Obviously, you need to be running MySQL. You also need the mysql-devel RPM package that goes with the MySQL version you’re running. Both are included on the Fedora and SuSE distributions and can be added with either the Package Manager or as RPM packages from a distribution disc. If you’re a Mandrake user, you’ll have to find the mysql-devel package on the Web.

You need to download and install a few Perl modules before you can install mtop. The easiest way to install Perl modules is to use the CPAN (Comprehensive Perl Archive Network) interface. CPAN retrieves Perl modules and installs them on your system automatically.

You need to have Internet access for the machine you’re using CPAN with.

To install Perl modules with CPAN, follow these steps:

1. Open a terminal window, give yourself superuser privileges with the su command, and enter the following command:

2. When you’re asked if you’re ready for manual configuration, type no and press Enter.

CPAN continues to work, retrieving the Perl DBI modules. Depending on your system and connection speed, this could take a few minutes.

3. Enter the following command:

# perl -MCPAN -e ‘install DBD::mysql’

If you have trouble installing the DBD::mysql module, be sure you’ve installed the mysqldevel RPM package. DBD::mysql won’t install without it.

4. Enter the following command:

$perl -MCPAN -e ‘install Getopt::Long’

5.Enter this command:

$perl -MCPAN -e ‘install Net::Domain’

The last Perl module that you need is Curses. At the time we’re writing this, the Curses module available from CPAN fails to install on Fedora (and possibly other distributions as well). To install Curses, follow these steps:

1. Open a Web browser and surf to

bastille-linux.org/perl-rpm-chart.html

2. Click the most recent link to download perl-

Curses (Text/Console).

Currently, that version is for Red Hat 9; it works fine with Fedora.

3. Save the RPM package to your home directory.

4. Open a terminal window and give yourself superuser privileges with the su command.

5. Move to your home directory.

6. Install the perl-Curses RPM package with the following command:

# rpm -Uhv perl-Curses-1.06-219.i586.rpm

Now that the program dependencies are in place, you’re ready to download and install mtop, as

# perl -MCPAN -e ‘install DBI’	described in the next section.

326 Technique 43: Keeping an Eye on Your Servers

Installing mtop

To install mtop, follow these steps:

1. Open your favorite Web browser and navigate to

sourceforge.net/projects/mtop/

2. Scroll down and click the download link for the most recent mtop release.

3. When the download page opens, click the link to download the most recent tarball.

4. On the mirror page, click the link for the download site nearest you.

5. Save the file to your home directory.

6. Open a terminal window and give yourself superuser privileges with the su command.

7. Extract the contents of the tarball with the following command:

#tar -xzvf mtop-0.6.4.tar.gz

8.Move into the mtop directory:

#cd mtop-0.6.4

9.Now you’re ready to compile mtop. Enter these commands:

#perl Makefile.PL

#make

#make install

mtop is now installed in /usr/local/bin and is ready to use!

Monitoring traffic

mtop is a MySQL client application so you must provide a MySQL user name when you run mtop. To start mtop, use the following command:

$ ./mtop --dbuser=root

You can substitute any reasonably privileged user for root in our example.

When you press Enter, the mtop display opens, showing the activity on your MySQL server (see Figure 43-12).

• Figure 43-12: mtop monitoring a MySQL Server.

Across the top of the display, you see the vital statistics on your MySQL server and its activity. The load average, the length of time that the server has been running, and performance indicators are all included in the statistical information.

Type ? while mtop is running to display a list of command options. Here are some of the more useful options:

z: Type z and enter a process ID from the process list to zoom in on a process.

e: Type e and enter a process ID from the process list to display the optimizer plan for the query that is executing.

s: Type s and enter the number of seconds between screen refreshing.

u: Type u and enter a user name to display only the server transactions for the named user.

q: Type q to quit.

If a given query runs for a while, the color progresses from white to purple, yellow, and red to indicate the length of time the query has run. You can use the k

Watching Your MySQL Traffic with mtop 327

option to kill off unwanted or runaway processes. Type k, type the process ID, and press Enter to terminate the process (see Figure 43-13).

If you’re running MySQL servers in a nongraphical environment, mtop is a definite timesaver. Getting acquainted with its features can help you monitor and manage your server with just a few keystrokes.

Enter $ ./mtop --help at the command line to display a list of command line options to

use at startup.

• Figure 43-13: mtop killing off an unwanted process.

44 Making a MySQL

Server Your SQL

Technique Server

Save Time By

Setting up replication on your MySQL servers

Backing up MySQL data with mysqldump

Using file system tools to back up MySQL data

Taking care of business with MySQL Administrator

MySQL is a fast, powerful, reliable, low-cost database that’s distributed with most Linux systems. You can use MySQL to store business records, baseball scores, user account information, network

configurations, and just about anything.

MySQL is very flexible. One of the hallmarks of MySQL is that when it comes to performance, you don’t pay for features that you don’t need. When you store data in a MySQL database, you can choose from a variety of storage engines. If you don’t need the extra reliability offered by secure transactions, you gain extra performance (and reduce multiuser contention) by using nontransactional tables. If the data that you’re storing just has to be right (maybe you’re storing payroll information or customers’ credit card numbers), use InnoDB tables. They offer ACID (that is, safe) transactions in exchange for a bit of performance.

We don’t have enough room to give you a complete overview of MySQL (we recommend Paul Dubois’s wonderful book, MySQL [New Riders/ Sams Publishing]), so we give you a few tips that can make MySQL your database. In this technique, we cover these topics:

Installing MySQL the easy way. The MySQL team has turned a complex procedure into a simple two-step process.

Pushing your data to remote offices (or just spreading a heavy user load across multiple servers) with MySQL’s replication features.

Keeping your data safe by archiving it with MySQL-specific tools or file system backup tools.

Using the up-and-coming tool MySQL Administrator. MySQL Administrator is an enterprise-level control center written by the MySQL team. It’s designed for MySQL version 4 (and above), but many features work with older servers.