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
- Perl
- DBI
- Term::ReadKey
- Term::ANSIColor (if you want some fancy colors)
- Time::HiRes (if you want real-time queries per second statistics)
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
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: