Mytop - Tool for Monitoring MySQL/MariaDB Performance in Linux

Mytop is a completely free and open-source tool that is used for monitoring MySQL and MariaDB databases. It was written in Perl by Jeremy Zawodny. It is almost similar to its counterpart top which is also used for monitoring purposes.

Mytop provides the user with a shell through which the user can monitor different activities of the MySQL/MariaDB database. The shell gives information about the threads, queries per second, the performance of the database, etc. Mytop is included in Fedora and Debian distros by default, so the user just has to install it. For RHEL the user must enable the EPEL repository.

The Linux distribution that we are using in this article will be Centos, you can use any distro of your choice. Make sure you have an active service of the Mysql database. 

Getting started with Mytop

Step 1: Installing Mytop on the system

Use the below command to install mytop on your system.

sudo yum install mytop

Step 2. Confirm the installation

To see if Mytop is installed or not use the -whereis command, if you get a location then Mytop is installed successfully.

whereis mytop

Step 3. Using Mytop to monitor databases

Let us see how to monitor a database using Mytop. For the purpose of this article, we will create a sample database named mukul.  

Note: If a database is not specified then Mytop will use a "test" database.

mytop --prompt -d mukul

After you write the above command, you will be asked to provide the root password for MySQL. Enter it and you will see the Mytop monitoring shell.  

Step 4. Changing the config file

Writing the entire command while running Mytop every time can be very tedious. For saving time we can edit the Mytop config file, and add our username and database there. Add the below lines of code to the config file. 

nano ~/.mytop (text editor is nano)
user=root
pass=<custom_password>
host=localhost
db=<your_database>
delay=4
port=3306
socket=

Now we can use Mytop directly without writing any additional commands:

mytop

Note: The Mytop shell can also be opened by mentioning the specific admin and database name, but that is not recommended as during this command the password can be stored in the shell's command history file which can be used by an unauthorized person. 

mytop -u root -p <password> -d sample_db (Not recommended)

Step 5. Viewing and Interpreting the Mytop Display 

Let us now see the meaning of the display that we get after using the below command. 

mytop --prompt

The above view which is opened after writing the command is known as the thread view. The first 4 lines of the view are known as the header, which contains summarized information about the MYSQL server.

Linewise information about the headers:

1. First line: This line shows the hostname and the version of the MYSQL server. Along with that, the server uptime can also be seen. The format of the uptime is: days + hours:minutes:seconds
2. Second line: This line shows the total number of queries processed, the number of slow queries, average queries, and the percentage of Select, Insert, Delete and Update queries.
3. Third line: All the real-time values are shown in this line since the last Mytop refresh. The first field shows the number of queries per second (qps). The second field is the number of slow queries per second. The last and final field in this line shows the percentage of queries since the last mytop refresh. 
4. Fourth line: This line shows the key buffer efficiency which is the number of times the keys are read from the buffer. Along with this it also shows the number of bytes that MYSQL has sent and received.

The second part:

The second part of the view shows the current MYSQL threads which are sorted on the basis of the time they have been idle ( in ascending order). The information shown is as follows:

1. The id of the thread.
2. Username and the host to which the user is connected.
3. Database which is connected to the user.
4. Idle time (in seconds).
5. The command which is being executed by the thread.
6. The first part of the query.

Note: If the command is in a sleep state then its corresponding query or state column will be blank. 

More info about Mytop can be found on its man page.

man mytop

Conclusion:

So this was how can use Mytop on Linux to monitor the performance of your database. Tools like these can surely increase productivity. You can learn more about Mytop by exploring its features and by gaining practical experience with it. 

mukulojha

Follow

Improve

Article Tags :

• Technical Scripter
• Linux-Unix
• Technical Scripter 2022