MySQL Monitoring

In recent time, I invested a good amount of time in learning and working on monitoring esp. Database Monitoring. So I found this medium the best way to share my journey, findings and obviously spectacular dashboards.

This blog will help you understand why we need MySQL monitoring and how we can do it.

Let’s start with the need to implement MySQL monitoring. There are multiple areas which we can monitor, here I am enlightening some important ones.

1. Resource Utilization
First of all, you have no idea what’s going on with MySQL, you can not know if it’s in a haywire state if there is no monitoring.
An ample number of queries run through it. Some of them are lightweight and some of them are very heavy which makes CPU over-utilized or overload. In that case, if we talk about production, a number of requests can be flushed out making it a business loss.

2. Database Connections
Sometimes the number of connections run out and no further connections are left for application to communicate with DB. In the absence of monitoring, it’s really hard to figure out the root cause.

3. Replication Lag
When we use MySQL as a master-slave cluster, real-time replication of data from the master to slave is a key factor to monitor. The lag between master and slave should be zero.

In my scenario, the slave is being used for DB replication from the master and also serving read queries to avoid overburden on the master. Now if replication lag is high and at the same time if any read query is triggered for the slave, what will happen? The same data which is on the master will not get replicated on the slave because of replication lag!
That read query will show an unexpected or erroneous result.

4. Query Analytics
Monitoring DB also helps in identify what queries are taking a long time. It helps in identify and optimize slow queries. At the end its all about being fast.

Ok, so how to monitor MySQL. There are multiple enterprise solutions available to monitor Database with a single click solution, but I didn’t have the luck to go for paid solutions. So, I have started exploring open source solutions which will cover all my requirements. Finally, I got one.

Its Percona Monitoring And management (PMM)Tool

PMM is an open-source platform to monitor and manage MySQL Database, that we can run in own environment. It provides a time-series database which ensures reliable and real-time data.

Installing PMM Server

curl -fsSL https://raw.githubusercontent.com/percona/pmm/master/get-pmm.sh -o get-pmm.sh

Change permission to make it executable

chmod +x get-pmm.sh

Now run pmm script to install it.

./ get-pmm.sh

This will run a docker container. Once docker container is up and running we will install PMM client and will bind the port

Installing PMM Client

Add the below repo

wget https://repo.percona.com/apt/percona-release_0.1-6.$(lsb_release -sc)_all.deb

Install the package from added repository

dpkg -i percona-release_0.1-6.$(lsb_release -sc)_all.deb

Update your ubuntu follow below steps:

apt-get update
pmm-admin config --server <server_ip>:<port>-get update
pmm-admin add mysql

It’s not only MySQL you can monitor, in fact, but pmm also allows to integrate it with other databases as well like Amazon RDS, Postgres, and MongoDB.

There are many alternatives for MySQL monitoring in the market like Nagios, VividCortex Analyser, SolarWinds server and application monitor, LogicMonitor and Management tool, MySQL OpsPack etc. But exploring open-source tools has its pros and cons but the level of learning you get from it, that makes it worth using. So anyone out there reading this blog I would suggest to give it a try.

Happy monitoring!!

Image Source: https://www.kisspng.com/png-clip-art-brand-line-technology-text-messaging-6462820/preview.html

Originally published at http://blog.opstree.com on July 23, 2019.