Using Mytop to Monitor MySQL Performance

28/12/2020
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.710.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.

Read more MySQL based posts here.

ONET IDC thành lập vào năm 2012, là công ty chuyên nghiệp tại Việt Nam trong lĩnh vực cung cấp dịch vụ Hosting, VPS, máy chủ vật lý, dịch vụ Firewall Anti DDoS, SSL… Với 10 năm xây dựng và phát triển, ứng dụng nhiều công nghệ hiện đại, ONET IDC đã giúp hàng ngàn khách hàng tin tưởng lựa chọn, mang lại sự ổn định tuyệt đối cho website của khách hàng để thúc đẩy việc kinh doanh đạt được hiệu quả và thành công.
Bài viết liên quan

Connecting to MariaDB with PyMySQL

PyMySQL is a free and open source project released under the MIT License. PyMySQL is a MySQL library that can be used to...
29/12/2020

Best MySQL Clients for Ubuntu

MySQL is very popular and one of the most widely used relational database management systems. Written in C and C++, it...
29/12/2020

MySQL Commands Tutorial

MYSQL COMMANDS FROM THE LINUX TERMINAL MySQL is one of the most popular relational databases utilized in  applications...
28/12/2020
Bài Viết

Bài Viết Mới Cập Nhật

Reliable IPv4 and IPv6 Subnet Rental Services: The Perfect Solution for Global Businesses
23/12/2024

Tìm Hiểu Về Thuê Proxy US – Lợi Ích và Cách Sử Dụng Hiệu Quả
11/12/2024

Mua Proxy V6 Nuôi Facebook Spam Hiệu Quả Tại Onetcomvn
03/06/2024

Hướng dẫn cách sử dụng ProxyDroid để duyệt web ẩn danh
03/06/2024

Mua proxy Onet uy tín tại Onet.com.vn
03/06/2024