Monitor' your MySQL Server

If a webserver with Apache and MySQL is having a high load or apache takes too long to respond, in the most of the cases it is a the MySQL that is causing the high load. In order to find out what is causing the Load and which of the Mysql database is taking too much time to respond, mtop (MySQL Monitoring Tools) is the perfect tool for monitoring.

As described from the Official Home page, “mtop (MySQL top) monitors a MySQL server showing the queries which are taking the most amount of time to complete. Features include ‘zooming’ in on a process to show the complete query, ‘explaining’ the query optimizer information for a query and ‘killing’ queries. In addition, server performance statistics, configuration information, and tuning tips are provided. “

Debian Installation

If you are using a Debain Server, installing mtop is easy through apt-get

apt-get install mtop

If the Debian installation complains because of the new root password! Change back to empty the root password, and reset it as below.

$ mysql -u root -p

* Enter the following MySQL command:
>SET PASSWORD FOR root@localhost=PASSWORD(”);QUIT;

* install mtop
apt-get install mtop

* Set MySQL root password to irs previous value:

$ mysql -u root -p
>SET PASSWORD FOR root@localhost=PASSWORD(‘YOURROOTPASSWORD’);QUIT;

(Credits for the solution above: https://bugs.launchpad.net/debian/+source/mtop/+bug/77980)

CentOS Installation

Installing the mtop tool on a CentOS system is a little bit more tricky as there is no official support in the main yum channels. Even though, there exists some packages from the website: http://dag.wieers.com/

Based on the architecture you are using, navigate to the http://dag.wieers.com/rpm/packages/mtop/ website and retrieve and install the mtop rpm package.

cd ~

wget http://dag.wieers.com/rpm/packages/mtop/mtop-0.6.6-1.2.el5.rf.noarch.rpm

rpm -ivh wget http://dag.wieers.com/rpm/packages/mtop/mtop-0.6.6-1.2.el5.rf.noarch.rpm

If the installation failes with the message:

warning: mtop-0.6.6-1.2.el5.rf.noarch.rpm: Header V3 DSA signature: NOKEY, key ID 6b8d79e6
error: Failed dependencies:
perl(Curses) is needed by mtop-0.6.6-1.2.el5.rf.noarch

then you need to install Curses. The good news is that http://dag.wieers.com has support also for this library. By navigating to the: http://dag.wieers.com/rpm/packages/perl-Curses/ you will be able to find the Perl Curses library that corresponds to the architecture of your system.
The installation is the same as above, wget and rpm -ivh Curses and agatin mtop.