Ubuntu Tips n Tricks

Live Performance Monitor for Your MySQL Server

Posted: 22 Feb 2011 09:01 PM PST

If you are a Linux system administrator or have a Linux server running a LAMP stack, you might want to be able to keep an eye on the different elements that contribute to keeping your applications and websites alive. This would include your web server, database server, the operating system itself, among other things. Let's take a look at how you can monitor the performance of your MySQL server using Mytop, a free and open source network/server monitoring software. Mytop is a clone of the popular UNIX application, 'top'.

The Installation:

On Red Hat Linux based systems such as Fedora you can execute the command "# yum -y install mytop". For Ubuntu Linux users you can just execute the standard "# sudo apt-get install mytop". Jeremy Zawodny, the project's author, also has a source release if you can't find the binary version for your distribution. You can find it at the Mytop protect page at http://jeremy.zawodny.com/mysql/mytop/.

Running Mytop:

The simplest way to run Mytop is to execute the command directly from the command line interface. Execute the command "# mytop". This may not work if you have a password for your MySQL users, which is a very good idea. In such a case execute the command "# mytop -u username -p[password] -h hostname". Replace the username, [password], and hostname fields with the respective settings on your computer. This would look like "# mytop -u calvin -ph0bb3s -h 192.168.0.25".

You will get an output that would look something like the following:

MySQL on hobbes03 (5.0.37) up 50+17:53:46 [13:20:38]

Queries: 2.5G qps: 609 Slow: 0.0 Se/In/Up/De(%): 01/00/00/00

Key Efficiency: 99.8% Bps in/out: 0.0/ 0.0

Id User Host/IP DB Time Cmd Query or State

— —- ——- — —- — ———-

9570443 dbuser 192.168.10.9 appdb 0 Prepar

9575575 dbuser 192.168.10.9 appdb 0 Sleep

9576920 dbuser 192.168.10.9 appdb 0 Sleep

9593343 dbuser 192.168.10.4 appdb 0 Sleep

9601720 dbuser 192.168.10.3 appdb 0 Query show full process

9574728 dbuser 192.168.10.9 appdb 1 Sleep

9575771 dbuser 192.168.10.9 appdb 1 Sleep

9579103 dbuser 192.168.10.9 appdb 1 Sleep

9579962 dbuser 192.168.10.4 appdb 1 Sleep

9581070 dbuser 192.168.10.4 appdb 1 Sleep

9585516 dbuser 192.168.10.4 appdb 1 Sleep

9585518 dbuser 192.168.10.4 appdb 1 Sleep

9597503 dbuser 192.168.10.4 appdb 1 Sleep

9597952 dbuser 192.168.10.4 appdb 1 Sleep

9599442 dbuser 192.168.10.9 appdb 1 Sleep

More Features:

If you would like to pimp out the output of mytop a bit you can have the display in colors rather than the boring black and white default. This is also quite useful if you plan to watch a mytop session. To do this you need to install the Perl module Term::ANSIColor. You can do this using the CPAN interface. Your distribution might have a readymade binary for it, which you could install using apt-get or YUM. Use the "-color" or "-nocolor" options to enable or disable the color display.

A file called ~/.mytop will be created in your user's home directory which will contain the settings for mytop. You can configure options that you want mytop to use by default. For example, if you want a color output by default make an entry in the file with "color=1". If you want to use a different port from MySQL other than the default 3306, add the line "port=3307" with the correct port number. You can setup things like your hostname (host=localhost), username and password also. This way, you don't have to write out these things in your command.

Another useful setting is the "delay=5". You can change this to a higher value if you are running a load test for a long period of time and don't want too much data. The default value is five seconds. You can also set this on the fly using the -s setting when you execute the mytop command.