Monitoring MySQL performance with mytop on cPanel server

You’re probably already aware of a great alternative to native ‘top’ utility called ‘htop’. In short – it provides a better top-like experience. Now what if one needs a similar tool for MySQL specifically? The answer is called ‘mytop’.

Background

cPanel does not provide any tools for monitoring MySQL. In fact, phpMyAdmin has monitoring tools built in, but phpMyAdmin itself may be lagging considerably on large amounts of data thus even causing additional server load.

In WHM you can also navigate to SQL Services >> Show MySQL Processes page that will show a static snapshot of MySQL process list. That’s about it. Obviously, there’s no lightweight console-based alternative out of the box.

Installing mytop

The latest version of mytop (1.9.1 at the moment) is available here and is maintained by Mark Grennan. We recommend to install mytop from source, so make sure the following pre-requisites are ready before proceeding:

1. Ensure that perl-TermReadKey package is installed.

Use the following command:

yum install perl-TermReadKey

The necessary package will be fetched and installed, or you’ll be notified that it’s already installed on your server. We’re good in either case.

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.

Now we are ready to install mytop. Execute the following commands one-by-one. Note, that you have to be logged into your server via SSH under root user.

cd /root

wget http://www.mysqlfanboy.com/mytop/mytop-1.9.1.tar.gz

tar zxpfv mytop-1.9.1.tar.gz

cd ./mytop-1.9.1

perl Makefile.PL

make install

cd ..

Done. mytop is now installed and ready to go.

Usage

Starting mytop is slightly more tricky than doing the same with top and htop where you just type the command and hit ‘Enter’. With mytop we need to connect to a database on start. So the general usage will look like this:

mytop -d database -u user -p password

Don’t forget to replace ‘database’, ‘user’ and ‘password’ with actual values. Now you should see something similar to this:

mytop in action

mytop in action

Of course, the output will look different on your server. Above I’ve connected to one of WHM’s databases (horde) on my VPS.

Advanced usage

mytop has quite a few command-line arguments, shortcuts and even supports its own configuration file. All these are perfectly documented on project website.

Go read through, and you can also say cheers to Mark Grennan for maintaining the great piece of software called ‘mytop’.


Was this article helpful?
Spread the word!