Using Mytop for monitoring the MySQL Performance

Mytop is one of the free console based tools which is used for monitoring the performance of the MySQL. It is similar to that of the top utility but displays the MySQL queries. With the help of mytop, you will instantly be able to monitor the MySQL uptime, the threads, queries, the user behavior and other real-time status information which is helpful for the MySQL performance optimization.

Here you will be shown on how to install, configure as well as use the mytop on the HostingRaja cloud hosting which is based on the One-Click LEMP Application.


Before going forward there are some things that need to be performed:

Deploying the server instance with the help of One-Click LEMP Application.

You need to log in as the non-root user with some sudo privileges.

Installation of mytop using EPEL yum repository

With the help Extra Packages for Enterprise Linux (EPEL) yum repository, you will easily be able to install the mytop on the server. The EPEL yum repository will be installed when the One-Click LEMP Application is used.

You will be able to confirm the EPEL repository existence with:

sudo yum repolist

Here you will be able to see the epel extra packages for the enterprise linux 6 - x86_64 repository listed.

For protecting the packages in the EPEL repository from being updated or being overridden by the packages in other repositories you will have to install the "protectbase" plugin:

sudo yum install yum-plugin-protectbase.noarch -y

Then you need to edit the file /etc/yum.repos.d/epel.repo, input:

cd /etc/yum.repos.d
sudo vi epel.repo

 


Now you need to append a line protect=1 to the section [epel]:
[epel]
name=Extra Packages for Enterprise Linux 6 - $basearch
#baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-6&arch=$basearch
failovermethod=priority
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
protect=1


Now just save and close the file.

And finally, install mytop with:


sudo yum install mytop -y

 


Configuring the mytop with a customized configuration file

For simplifying the usage of mytop on a VPS hosting package, you will be able to create a customized configuration file named /root/.mytop. When you will be running the mytop as the root or as the non root user with sudo privileges the program would invoke the configuration file automatically. If you are looking to run it as the non root user without any type of sudo privileges then you will be required to put the configuration file in the non root users home directory.

Below is the sample of the configuration file /root/.mytop:

user=root
pass=
host=localhost
db=mysql
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1


Here the contents in the file will provide the default arguments to the mytop program, hence by reducing the manual input when you have been using it.
If you are running the program with the arguments which is manually inputted the command line arguments will be overriding the corresponding arguments in the configuration file.

You will be able to modify the arguments according to the specific conditions. Below are some of the meanings of the arguments which have been listed below:

User: The database username
Pass: The password for the database user. For any type of security purposes, you can leave that blank and then input the password manually.
Host: It is the database host address
Db: It is the database name
Delay: It is the display refresh interval in seconds

The full explanation for each argument could be found on the manual page:
man mytop


Using the mytop for monitoring the MySQL perfomance

As it has been mentioned before the mytop utility uses both arguments in the configuration file and command line arguments, here the latter will override the former according to their need.

Hence with a configuration file in order, you will have to input the command with few arguments. Hence if you are looking to put the password with a prompt for better secuirty you will be able to input:

sudo mytop --prompt


Then input the password for the default database and user root, which can be found in /root/.my.cnf, to enter the mytop program interface.
Here is another example. If you want to monitor a specific database, you can use:


sudo mytop -d yourdatabasename --prompt

Now you need to replace the yourdatabasename with your own domain name
In the mytop program interface, you will be able to find something like as it has been shown below:

MySQL on localhost (5.6.26-log) up 0+08:36:33 [12:07:15]
Queries: 921.0 qps: 0 Slow: 0.0 Se/In/Up/De(%): 00/00/00/00
qps now: 0 Slow qps: 0.0 Threads: 1 ( 1/ 0) 00/00/00/00
Key Efficiency: 100.0% Bps in/out: 0.8/140.7 Now in/out: 9.7/ 1.9k

Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
14 root localhost mysql 0 Query show full processlist

 


This is the default thread view of mytop, you will always switch to this by pressing t.
Here the top four lines constitute the header which can be toggled on or off by pressing the Shift +h.
Here the header contains the general information about the MySQL server.
Now beneath the header, you will be able to see the status data of the current MySQL threads.
For getting help press? (Shift + I) when the program is running.
For quitting the program press q.
For more details about the display and the usage of mytop can be found on the main page
man mytop

You can use the data that has been gathered from the mytop to implement informed MySQL optimization.