We already know that both MySQL and MariaDB are among the most popular database managers out there. They have earned this with a track record that includes stability and robustness. However, it can be interesting to have a tool that allows us to know which is the real performance. That’s why in this post, we will teach you how to use MyTop on Linux.
MyTop is a CLI utility that allows us to obtain a performance analysis of a MySQL / MariaDB server. Among other things, it allows us to monitor MySQL queries, processes, and general performance of the MySQL database server.
This tool is quite used to know how the data server is behaving. That’s why it is convenient to know how to use it to better interpret the results obtained.
Install Mytop on Linux
Mytop is included in the official repositories of all major Linux distributions. In the case of Debian, Ubuntu, Linux Mint and derivatives, just open a terminal and run:
:~$ sudo apt install mytop Reading package lists… Done Building dependency tree Reading state information… Done The following additional packages will be installed: libconfig-inifiles-perl libdbd-mysql-perl libdbi-perl libmysqlclient21 libterm-readkey-perl mysql-common Suggested packages: libclone-perl libmldbm-perl libnet-daemon-perl libsql-statement-perl The following NEW packages will be installed: libconfig-inifiles-perl libdbd-mysql-perl libdbi-perl libmysqlclient21 libterm-readkey-perl mysql-common mytop 0 upgraded, 7 newly installed, 0 to remove and 0 not upgraded. Need to get 2137 kB of archives. After this operation, 10.1 MB of additional disk space will be used. Do you want to continue? [Y/n]
Or in Fedora’s case, CentOS:
:~$ sudo dnf install mytop
So as we can see the installation is quite simple.
Configuring Mytop before using it
The way to use Mytop is by invoking it from the command line. There we have to use the credentials of the user who has permissions on the database to be analyzed.
The previous situation brings a problem and is that the credentials are exposed. For that reason, it is convenient to create a configuration file in the home directory that helps to improve the security a little more.
So, create the file:
:~$ nano .mytop
On it, add the following content:
user=angelo
pass=angelo
host=localhost
db=example
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1
All these options can be added as command options. However, this method is more efficient and only requires the command to be invoked.
So, the value user refers to the name of the user who has permissions on the database. Then, in pass, you have to write the password of that user. In host, it indicates the server where the MySQL / MariaDB instance is and also the name of the database. Then, Delay establishes a data refresh time. In this case, it would be 5 seconds. Port is the port that listens to MySQL / MariaDB. The default value of Socket is empty. This is because if the server is on the same computer you might want to use one. Batchmode if it is at 1 will show the data without refreshing but if it is at 0 it will refresh the queries removing the previous values. Header will be used to add headers to the data. Color improves the data presentation and idle allows to show queries that are sleeping.
As you can see they are very common and easy to understand.
Using Mytop on Linux
Once the file is ready, save the changes and close the editor.
Now, in the end, simply execute the mytop command.
You’ll see something like this:
There the first thing you’ll see is the queries value which refers to the amount of them processed by the server. qps is the queries per second that are processed. Being an example database they are low values. Slow is the slow queries that are processed and slows qps are the slow queries per second.
On the other hand, you will see the values se/inUp/de which are the percentage of Select, Insert, Update, and Delete that the server has done. Key Efficiency refers to percentage values that are using foreign and primary keys.
BPS in / out the amount of input and output bits to the database. Now in / out are these same amounts since the last time mytop was run.
As you can see they are all useful data showing how good MySQL / MariaDB performance is.
Conclusion
The monitoring of a service is something important that helps us to correct problems. Well, with Mytop you can know the performance of MySQL / MariaDB in a data server.
So, please share this post and join our Telegram Channel.
