Watching a Database with mytop

Monitoring a MySQL Database

Just a quick posting today about a handy tool I though I would write a little bit about. It is a very simple tool which is database specific we use MySQL version 5.5.x as the database for our websites.

Although our code is database agnostic by using a lightweight in house abstraction layer and using good SQL queries we could port to any relational database for example PostgreSQL.

Using mytop

mytop is an easy to use program for monitoring a MySQL database it is a clone of the program top which is used for monitoring system tasks, giving a view of whats happing on the system it is running on.

Installing mytop is simple on our test server which is a Debian minimal install we use apt to install the latest package in the repository so as root you run the following.

root@chic:~# apt-get install mytop

To run mytop you need to pass it some switches/arguments setting some database configuration details for your install,

chris@chic:~# mytop -u dbuser -p dbpassword -d dbname

When you run the above command it will start running mytop an example of the user interface is the image below.

mytop screenshot

Once you are in mytop you can watch the queries that are currently being executed on the database you specified using the -d switch in our example that would be dbname. It also gives you some at a glance information about the database statistics. If you press ? it will display a list of available commands.

You can filter the display and various other commands using the keyboard you can also see the query count in the upper left corner of the screen the slow query count is displayed in the header as well as key efficiency.

For a full list of commands and switches you can view the mytop documentation here or you could search Google for some more examples.

Conclusion

mytop is a handy little Linux program which can help when doing some system administration on the database side of things.

Our news system has gone through the first set of acceptance tests and we have also finished writing our test unit for all the classes we use to create the working system, its core set of functionality.

We are hoping to publish a blog posting about its development very soon we will be writing a bit on how we test our classes using PHPUnit and xDebug among other tools that we have in our testing stack.

Also we will be writing about profiling some of our features to try and improve our script execution times and reduce the amount of resources our programs use.

Finally there will be a short article on some minor tweaks we have been making to our Apache 2 server configuration.

Appendix

Source for mytop screen shot http://jeremy.zawodny.com/mysql/mytop/mytop.gif