Everyone who runs a Linux server knows the top command for process monitoring. The mytop utility does the same thing, but for a MySQL server.
mytop is only an apt-get away:
sudo apt-get install mytop
The basic command for running mytop is as follows:
mytop -u username -p password -d databasename
Some other useful arguments include -s (time in seconds before refreshing the display), -P (specify a non-standard MySQL port to connect to), and -h (connect to a remote host). If you don’t want to have to remember your options, you can create a ~/.mytop file to store arguments in this format:
user=myuser
pass=mypassword
db=mydatabase
The mytop display is split into two sections. The first few lines show the hostname, server version, uptime, queries per second (qps) overall, current qps, number of threads, and a lot more. The lower part lists all the threads and their current query.
Press the q key to quit mytop.
I watched mytop on my own server for a while and found that the WP-Cache plugin Tombuntu runs leaves the MySQL server without much to do. But when a dynamic page is served it causes several queries. If you need to know what your database server is doing, mytop is an easy way to find out.
Tom,
Have you tried mytop with a remote database? The manpage is very vague on whether it works remotely or not.
After reading your post, I attempted to monitor a mysql db on my web server, to no avail. I specified host, port, etc…but it kept crapping out.
Just curious!
Thanks as always!
m.
I was wondering the same thing, please comment if this works to monitor remote servers.
Follow up: mytop -u -p -P -h will connect it to a remote machine.
mkwerner & coferm,
I just tried it, and I can’t get mytop to connect remotely.
‘mytop -u username -p password -d databasename -h xxx.xxx.xxx.xxx -P 3306′ results in:
‘Can’t connect to MySQL server ‘
Perhaps there is a setting in MySQL that stops this from working.
mytop works fine on remote databases.
But there’s a tweak to be done.
In the mysql database, in the user table, the Host value must be “%” for the User “root”
A simple:
update user set Host=”%” where User=”root”;
flush privileges;
will do it.
Enjoy!
Chris