How to Install Mytop on cPanel to Improve MySQL Server Monitoring

Mytop is one of the best MySQL monitoring tools I’ve ever used. It was written by one of the best MySQL monitoring tools available, written by the ex Yahoo MySQL Guru: Jeremy Zawodny. Today we will explore how to install mytop on cPanel / WHM servers.

mytop is a multi-platform (runs onLinux, BSD, Mac OS and even on Windows) terminal based monitoring tool. It works the same as the famous ‘top linux command and it helps system administrators and developers to monitor MYSQL health by watching the SQL threads and MYSQL overall performance.

This tool has become a monitoring standart for all kind of web hosting enviroments, but specially for shared hosting providers, who need to keep in control the resource usage of all their MySQL databases.

Let’s see what are mytop requirements and how to install mytop on cPanel

Mytop Requirements

This is how you can install this required perl modules on a normal cPanel box. Run the commands as you see below:

/scripts/perlinstaller --force Getopt::Long
/scripts/perlinstaller --force DBI
/scripts/perlinstaller --force DBD::mysql
/scripts/perlinstaller --force Term::ReadKey

I Install Mytop on cPanel Servers

While on Jeremy’s website the latest version available for download is mytop-1.6 (from 2007), you can get an updated version (a ported one by Mark Grennan) following the next steps:

cd /usr/local/src
wget http://fossies.org/linux/privat/mytop-1.9.1.tar.gz
tar -xvpzf mytop-1.9.1.tar.gz
cd mytop-1.9.1
perl Makefile.PL
make 
make install

Running Mytop for the first time

One common problem on most mytop installations is this error while you are running mytop for the first time:

Error in option spec: “long|!”

This error prevents mytop from starting normally. But don’t worry, you can fix it easily.

nano -w /usr/local/bin/mytop

Search for “long|!” by pressing:

CTRL + W

You will find this line:

"long|!" => \$config{long_nums},

Place a # at the begginning of that line, it should look like this:

"#long|!" => \$config{long_nums},

If that doesn’t work, you can try to replace the line with this new line:

"long|long_nums|l!" => \$config{long_nums},

Save your file by pressing:

CTRL + X, then press Y

Now run ‘mytop’ and you should see something like this

install mytop on cpanel and this should be the expected output at your Linux terminal
Screenshot of mytop command running on a cPanel server

Reading Mytop Stats

This is a sample output from mytop command:

MySQL on localhost (5.6.34) up 15+07:10:21 [19:12:45]
Queries: 4.0 qps: 0 Slow: 0.0 Se/In/Up/De(%): 1805914325/00/00/00
qps now: 0 Slow qps: 0.0 Threads: 3 ( 1/ 38) 8050/00/00/00
Key Efficiency: 98.8% Bps in/out: 0.0/ 0.0 Now in/out: 8.3/ 1.9k

The first line shows the MySQL version and hostname (localhost), as well as the MySQL server uptime (15+07:10:21) in this sintax: days+hours:minutes:seconds, and the last information is the current server time.

The second line shows the total number of MySQL queries processeed by the server, average # of queries/second, slow queries, and percentage of Select / Insert / Update / Delete queries.

Third line shows query stats in real time: # of queries/second, slow queries and last it shows query porcentages.

Fourth line shows the percentage of key buffer efficiency plus the # of bytes MYSQL has sent / received over all and in the last cycle.

The other part of Mytop results show the list of active threads (as many as your screen can show you) sorted by idle time

Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
3264218 root localhost mysql 0 Query show full processlist
3264223 user1_ localhost db1_p 0 Sleep
3264224 user2_ localhost db2_p 0 Sleep

This second part is much clear to understand than the first one, we have:

  • Thead ID
  • MySQL Username
  • Host Connected
  • Number of seconds
  • The executed SQL query
  • The Query state

That’s all. At this time now you should know how to install mytop on cPanel, as well as fixing common problems and understand how to read the mytop stats.

References:

About the Author: Martin Keler

Leave a Reply

Your email address will not be published. Required fields are marked *