Using Mytop to Monitor MySQL Performance

Today, one of the most critical parts of any application is how the databases which power the backend of the applications scale in difficult situations. While most of the teams focus on establishing a controlled number of connections from applications, optimizing database queries at the application level so that it runs fast on the Database, many times, the cause of bad database performance can be a query or bad configuration as well. Mytop is an excellent tool for investigating MySQL performance issues. This quick post guides you through its installation and configuration.

What is Mytop?

Mytop is an Open-Source monitoring tool which can investigate performance issues in MySQL and MariaDB. This tool was written by Jeremy Zawodny using Perl language. It provides a command-line interface through which it is easy to monitor the following things:

Threads in execution
Queries being executed per second
Process list
Performance of Database

With all these metrics available, DB administrators can take better decisions related to DB configurations and optimize it respectively.

Installation

By default, the Mytop tool is already included in the Fedora and Debian/Ubuntu repositories, so we just need to install it using the default package manager.

As a non-root user, execute the following command to install mytop on Ubuntu 16.04 machine:

sudo apt install mytop

Once this installation is complete, mytop will be ready for usage, but before that, we will have to do some configuration for our usage.

Loaded plugins: changelog, fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.linode.com
* epel: mirror.freethought-internet.co.uk
* extras: mirrors.linode.com
* updates: mirrors.linode.com
Resolving Dependencies
—> Running transaction check
—> Package mytop.noarch 0:1.7–10.b737f60.el7 will be installed
—> Finished Dependency Resolution
Dependencies Resolved

Configuring Mytop for our MySQL DB

Mytopp configuration parameters are stored in /root/.mytop file. If this is not present at the location, feel free to make it and inpt the following parameters (these can change based on your MySQL configuration):

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

Note that all of these parameters can be passed as command-line arguments as well, in that case, command-line arguments will take priority over the arguments present in this config file.

Find the meaning of each argument with a simple command as well:

man mytop

Monitoring a Database

Now that we’re done with the installation and configuration for Mytop, we can move to monitoring our database. Here is how we can monitor a database “linuxhint_db”:

sudo mytop -d linuxhint_db –prompt

The command-line interface will switch to Mytop interface with following information:

MySQL on localhost (5.6.27-log) up 3+08:22:19 [22:13:29]
Queries: 721.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: 90.3% Bps in/out: 0.8/140.7 Now in/out: 9.7/ 1.9k

Id User Host/IP DB Time Cmd Query or State
— —- ——- — —- — ———-
991 linux localhost mysql 0 Query show full processlist

This is the default thread view of mytop, you can always switch to this view by pressing t.

The top four lines provide general information about the MySQL server below which, we can see the currently active Threads and users using the program.

Press q to quit this interface.

Conclusion

In this lesson, we looked at how we can install MyTop on Ubuntu and use it to monitor the performance of MySQL Database on an Ubuntu machine. Whenever you face issues related to MySQL performance, we have an excellent tool to look at the insights of MySQL resources being used.