MySQL Woes?

On any hosting server, MySQL issues can quickly consume the majority of CPU cycles and impact server performance. Tracking down the source of high MySQL load on cPanel/WHM servers running multiple high-traffic websites can be a frustrating task for any system administrator.

In certain situations where performance is impacted you need real-time detail, such as tracking down particular databases or users generating high MySQL load or the queries which are directly causing high CPU load, so that you can take action. Your best option (as always) is the command line.
Every Linux system admin is familiar with ‘top’ but in situations where MySQL performance is the issue, a highly useful tool is MyTop (a ‘Top’ clone specific to MySQL). The following guide will walk you through installing MyTop on CentOS / cPanel/WHM servers.

First off we need to install two Perl modules in order for MyTop to run properly. Note: Search CPAN ( http://search.cpan.org/) for the latest versions.

cPanel/WHM adds yum exclusions for Perl & MySQL so you don’t mess things up! You can temporarily revert this and revert back once you’re done. However, it’s just as easy to install things manually.

Install TermReadKey:

wget http://search.cpan.org/CPAN/authors/id/J/JS/JSTOWE/TermReadKey-2.30.tar.gz
tar -zxf TermReadKey-2.30.tar.gz
cd TermRead*
perl Makefile.PL
make test
make
make install
cd ..

Install DBI:

wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.622.tar.gz
tar -zxf DBI-1.622.tar.gz
cd DBI*
perl Makefile.PL
make test
make
make install
cd ..

Now we can install MyTop. Note: check for latest version (http://jeremy.zawodny.com/mysql/mytop/)

wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz
tar -zxf mytop-1.6.tar.gz
cd mytop*
perl Makefile.PL
make test
make
make install

In order to run correctly, MyTop must be run as root and requires a config file stored in the root user directory, in this case:

/root/.mytop

You can create this file using your preferred txt editor and inserting the following:

user=root
pass=<yourMySQLrootpass>
host=localhost
db=mysql
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1

Note: As the root user you can find your MySQL root password with the following command:

cat /root/.my.cnf

Last but not least, once this is done simply run ‘mytop’ and go from there!