Using mytop to Interactively Monitor MySQL Processes

Published November 8th, 2008

Problem Statement:

When you are developing Web applications that uses a MySQL database, sometimes you need to monitor MySQL processes (threads) to see if any query is taking unusally long time or not. In such a case, SHOW FULL PROCESSLIST works but having to run it manually again and again, becomes a chore. As more and more professional developers use multiple monitors, it is nice to run utility called mytop on a separate screen to automatically monitor MySQL activity just as you would do with a the Linux top command. In this article, we will introduce you to mytop and discuss how you can install, configure and use it.

Time Estimate (15 ~ 60 min)

We estimate that the entire process to get mytop up and running may require anywhere from fifteen minutes to an hour. Most of the time will be spent on getting the pre-requisite Perl modules installed and setup right.

Step 1: Installing mytop pre-requisites

Unfortunately, one or more Perl CPAN modules required for mytop might make it a painful experience to install mytop on your system. It all depends on what is already installed on your system. So lets start there.

1. Check if you already have perl DBI module installed by running:  perldoc DBI
2. If you see manual pages for DBI, you are likely to have DBI installed
3. You can also run: rpm -qa | grep DBI and see if rpm reports an installed package such as perl-DBI-[version], which is good news
4. In case you do not have Perl DBI installed, you have two options. First try the easy way, run:  yum -y install perl-DBI and install it via yum.
5. If yum fails to install it, you have to do it the hard way from CPAN directly. Run:  perl -MCPAN -e ‘install DBI’ and let it install. Of course, if you have never configured CPAN module on your system, you will be asked a zillion questions and so you have to go through the whole process of configuring CPAN first. Most of the time, you can accept the default answers and be on your way
6. Once Perl DBI is installed, you need to install Perl DBD::mysql the mysql driver that goes with the DBI module. This is is particularly nasty to install.  Check if you already have it by running:  rpm -qa | grep DBD; if you got it you are in good shape.
7. If you do not have Perl DBD installed, you can try to install it the easy way using yum -y install perl-DBD-MySQL but sometimes this will conflict with your MySQL installation. If you get a message saying this won’t work because of conflict. You have to do it the hard way, sorry.
8. The hard way is to run: perl -MCPAN -e 'shell' and once you are in the CPAN shell run: force install DBD::mysql and pray, really!
9. Most of the time, the CPAN module will complain that the make test failed, which is because of its wrong guesses about your test database access info or not having the grant setup the way it wants. In any case, if make test fails thats OK
10. Now check if perldoc DBD::mysql shows manual pages; if you get man pages for DBD::mysql module, you should be good to go to mytop installation step but we encourage you to try the next step below.
11. You can optionally download and run the dbd_mysql_test.pl script by changing the host, username, password values to see if your system has DBI and DBD::mysql modules functioning properly. After you change the host, username, and password settings, run this Perl script using  perl dbd_mysql_test.pl and you should see a output that shows the version of your MySQL server. An example output is shown below

Your MySQL version is: 5.1.26-rc

Download dbd_mysql_test.pl

Step 2: Installing mytop

Once you have installed Perl DBI and DBD::mysql modules, follow the steps below to install mytop:

1. Download the mytop source from: http://jeremy.zawodny.com/mysql/mytop
2. Extract the source tar ball in /usr/local/src
3. Change directory to the newly created sub directory [mytop-version]
4. Run:  perl Makefile.PL
5. Run: make
6. Run: make test
7. Run: make install

Step 3: Using mytop

Once you have installed mytop, you can run it from the command-line as:

 $ mytop

First time you will see output similar to what is shown below

Cannot connect to MySQL server. Please check the:

  * database you specified "test" (default is "test")
  * username you specified "root" (default is "root")
  * password you specified "" (default is "")
  * hostname you specified "localhost" (default is "localhost")
  * port you specified "3306" (default is 3306)
  * socket you specified "" (default is "")

The options my be specified on the command-line or in a ~/.mytop
config file. See the manual (perldoc mytop) for details.

Here's the exact error from DBI. It might help you debug:

Access denied for user 'root'@'localhost' (using password: NO)

Now create a mytop configuration file in your home directory, ~/.mytop with following lines:

user=[your_mysql_username]
pass=[your_mysql_password]
host=localhost
delay=5
port=3306
batchmode=0
header=1
color=1
idle=1

Make sure you change the [your_mysql_username] and [your_mysql_password] values to match your access information.

Now run mytop from the command-line and you should see a top-like screen as shown in Figure 1:

Figure 1: Running mytop

You can press ? key and get help on how to work with the mytop screen.

Using ajaxMyTop Web Based mytop clone

If you fancy running a Web-based mytop-like application that uses AJAX to do the trick, you can download ajaxMyTop from http://sourceforge.net/projects/ajaxmytop and install it in a Web directory. By editing the simple config.php configuration file, you can get it up and running in minutes. Access the web directory via a Web browser and you are going to see a mytop like screen as shown below.

Figure 2: Running ajaxMyTop on Google Chrome Browser

Securing ajaxMyTop

It is very important that you do not allow anyone but authorized people to access your ajaxMyTop page, so you should setup access restrictions for the web directory using Apache access control mechanism. For example, to restrict access to the ajaxMyTop folder in your Web site to your LAN with IP address in the range of 192.168.1.1-254, you can add a .htaccess file in your Web folder as follows:

   Order deny,allow
   Deny from all
   # Allow access from any IP in 192.168.1.0 network:
   Allow from 192.168.1.

Of course, make sure that your Apache Web server honors your .htaccess file, which is done by configuring AllowOverride All or AllowOverride Limit directives.

Category: DBA, Debugging, PHP, Programming | Tags: