In this article I’m explaining the usage of a smart command-line tool “mytop.” Which is similar in look to the basic resource monitoring tool “top.” By using this command, you can monitor the MySQL queries lively. There are a lot of helping switches are included with this tool. In this topic I’ll cover the installation steps and basic usage of mytop command for your Linux server.
Mytop monitors MySQL threads and the database’s overall performance, allowing system administrators or developers to get some insight on how applications are interacting with a database. That’s cool, right?
Basic information
mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL 3.22.x, 3.23.x, and 4.x server. It runs on most Unix systems (including Mac OS X) which have Perl, DBI, and Term::ReadKey installed. For more details, mytop – a top clone for MySQL
Installation procedure
RHEL/CentOS and Fedora
Installation is simple for mytop. You can use yum to install this on your RHEL/CentOS and Fedora servers. Installation steps are pasted below:
1. SSH to server as root.
2. Run the following command:
# yum install mytop -y
Debian
1. SSH to server as root.
2. Run the following command:
# apt-get install mytop
Ubuntu
You can use the same “apt-get” command to install mytop on your Ubuntu server. Please see the steps pasted below:
1. SSH to server.
2. Run the following command:
# sudo apt-get install mytop
Installation from source code
If you are facing problem with yun or apt-get, you can use the direct method, which is from source code. Please see the steps pasted below on how to do so:
1. Download source code from here.
2. Extract the tar archive.
tar -zxvf mytop-x.x.x.tar.gz (Your version number)
3. Then make and make install it.
cd mytop-x.x.x. (Your version number)
perl Makefile.PL
make
make test
make install
That’s it!!
Monitoring databases using mytop
Like the installation procedure,the usage of mytop is also simple. Please see the common usages listed below:
Run the following command to open the Mytop tool. It will ask the mysql root password to monitor the databases:
# mytop --prompt
How to monitor a particular database using mytop?
You can use the “-d” switch along with the mytop command to specify the MySQL database. Please see the command pasted below:
# mytop --prompt -d example_database
Short-Keys on ‘mytop.’
Here I’m listing some short keys which are commonly using with mytop command. Those short keys are listed below:
? : Display help. c : Show “command counters” based on the Com_* values in SHOW STATUS. d : Show only threads connected to a particular database. f : Given a thread id, display the entire query that thread was running. F : Disable all filtering (host, user, and db). h : Only show queries from a particular host. H : Toggle the header display. You can also specify either header=0 or header=1 in your config file to set the default behavior. i : Toggle the display of idle (sleeping) threads. If sleeping threads are filtered, the default sorting order is reversed so that the longest running queries appear at the top of the list. k : Kill a thread. m : Toggle modes. Currently this switches from `top’ mode to `qps’ (Queries Per Second Mode). In this mode, mytop will write out one integer per second. The number written reflects the number of queries executed by the server in the previous one second interval. More modes may be added in the future. o : Reverse the default sort order. p : Pause display. q : Quit mytop r : Reset the server’s status counters via a FLUSH STATUS command. s : Change the sleep time (number of seconds between display refreshes). u : Show only threads owned by a giver user.
That’s it.