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

System Monitoring

One of the main task of a system administrator is system monitoring, system monitoring usually involves monitoring the ram & disk space usage of the system …. In this blog I’ll be talking about my experience as a system admin & how I do it.

Usually system monitoring is divided into 2 parts Continuous system monitoring and troubleshooting system issues when system crosses a threshold value & you have to figure out the issue & try to resolve it.

In continuous system monitoring a system is put under continuous monitoring i.e the system ram usage is within defined limit or not, the disk space occupancy should not cross a predefined threshold …. To achieve continuous monitoring you can use couple of tools available in market such as nagios, omd we are primarily using these tools their would be other tools available also for this purpose.

Continuous system monitoring serves one purpose where they notify about any deviation from the expected state of the system, the next step is to troubleshoot this issue & resolve it accordingly. As a first step I usually execute top command, top is a very powerful command apart from just viewing the processes activity in real you can do a lot of things i.e

  • If you want to add/remove fields : press f & then you can choose the fields to add/remove
  • If you want to change ordering of  fields : press O & then you can move fields
  • If you want to change the sort order : press F or O
there are lot of other options available as well, if you want to explore them pressing h will provide you a list of all the options.

You can also read about htop, htop is an advanced form of top where you can view some graphs as well though I’ven’t used htop so much but I’m planning to 🙂

One thing to note sometimes you are not able ot run top command due to high resource utilization, in that case you have to use cat /proc/loadavg to view the load on the system & cat /proc/meminfo to view current memory state of the system.

One of the useful command if top doesn’t work
ps -eo pmem,pcpu,vsize,pid,cmd | sort -k 1 -nr | head -5
This command will give the top 5 processes by memory usage.

Also there are couple of other commands that you can use
free : To view the memory usage of system
df : To view the file system information
du : To view the disk usage

One tip : To increase the memory of system you can create a swap memory & it is always recommended to create a swap on a partition only. Another best practice for swap area is if your system RAM is below 8 gb your swap area should be double of your ram otherwise it should be half of your RAM size