Monitoring MySQL performance with innotop on cPanel server

We’ve already covered basic monitoring of MySQL on cPanel/WHM servers. However, sometimes it may be necessary to perform advanced in-depth analysis of what’s going on behind the scenes of MySQL operations. Right, when mytop is not an answer – innotop comes to play.

What is innotop

innotop is a MySQL monitoring utility packed with advanced features for power users. It’s capable of showing different aspects of what’s going on in the server. It can monitor queries, replication, transactions, cache and many more.

Installing innotop

Installation of innotop is very similar to that of mytop. Again, we recommend installing innotop from source. Pre-requisites are also similar to those of mytop:

1. Ensure that perl-TermReadKey package is installed.

Execute the following command:

yum install perl-TermReadKey

The perl-TermReadKey package will be downloaded and installed with necessary dependencies. If the package was previously installed – yum will notify about this and no actions will be taken.

2. Ensure that DBD::mysql Perl module is installed.

In WHM navigate to Software >> Install a Perl module. Usually, you’ll see DBD::mysql in the list of installed modules. If not – use that same page to install it via ‘Search’ or ‘Show Available Perl Modules’ function. After that it’s OK to start installing innotop.

Here’re simple installation steps:

cd /root

wget http://innotop.googlecode.com/files/innotop-1.9.1.tar.gz

tar zxf innotop-1.9.1

cd ./innotop-1.9.1

perl Makefile.PL

make install

cd ..

That’s it, innotop is now installed and ready for use.

Usage

A quick start is simply executing ‘innotop’ command. For comprehensive advanced usage instructions read through Innotop User’s Manual. It’ll give you a good idea of how to utilize various aspects of innotop like shown below:

innotop index stats by reads

innotop index stats by table

innotop joined view of tables and indexes

innotop table stats