How To Set Up Open-Source DB Monitoring Using PMM?

Setting up open-source DB monitoring using PMM

There are plenty of proprietary DB monitoring services, but did you know there is a great open-source alternative – Percona Monitoring and Management (PMM)? PMM provides monitoring, alerts, and query analysis for MySQL, PostgreSQL, MongoDB, and others. The package is open source, has a large community, and can be set up in minutes.

What do you get once the Percona PMM is set up?

There are 3 main features of Percona Monitoring and Management: monitoring, alerts, and query analytics.

Monitoring

Once you complete the PMM setup, you already have plenty of preset Grafana-based dashboards. These dashboards display the info about:

Your host machine
Your database

PMM Server UI

These default dashboards are already customisable dashboards that help you keep an eye on the infrastructure.

Alerts

Real-time alerts notify you if something goes wrong.

Query analysis

PMM helps you with troubleshooting by providing data from a high-level overview to query-level analytics.

5 Steps To Set Up DB Monitoring With PMM

The PMM consists of 2 parts:

Client (Agent)
Server

PMM Agent should be installed on your database host machine. It will be collecting telemetry data about the host machine and database(s) and sending it to the PMM Server.

PMM Arcitecture

Unlike most cloud-based monitoring services, the PMM Server is a part you should also care about. It will store and process all the telemetry data received from agents, and provide UI for analysis.

There are 5 steps to set up PMM for DB monitoring:

Set up a PMM Server
Set up a PMM Agent
Connect the Agent to the Server
Connect DB to the Agent
Customise Dashboards and Alerts

Let’s look into the details of each step.

1. Set up a PMM Server

You can get a fully managed PMM Server at hostedpmm.com in several clicks or deploy it by yourself.

If you want to host a PMM server by yourself, you need a VPS that meets PMM Server requirements. It will be processing all the telemetry data from your infrastructure, so it needs up to 2 GB per monitored DB. The PMM Server will also be storing all the telemetry data for some time. It requires around 1 GB of storage per monitored database per week.

Once you have a VPS, you can install PMM Server. For example, you can use an easy-install script by running

wget -O - <https://www.percona.com/get/pmm> | /bin/bash

This script:

Installs Docker if it is not already installed on your system.
Stops and backs up any PMM Server Docker containers that are currently running.
Pulls and runs the latest PMM Server Docker image.

After the deployment, you need to properly set up your network. If everything is correct you can access the PMM Server web interface.

PMM login screen

Use admin/admin to log in.

PMM default veiw

As you can see, it already shows the info about the PMM Server host machine and its DB. Your PMM server is set up and works properly.

2. Set up a PMM Agent

You need ssh access to your DB host machine.

There are several ways to set up a PMM server:

Docker
Package manager
Binary package

Not everyone wants to set up docker to their DB host machine just for monitoring. The binary package is hard to update compared to the package manager way to install. So we advise using package manager installation.

To do this you need to run these commands:

Debian-based

wget <https://repo.percona.com/apt/percona-release_latest.generic_all.deb> dpkg -i percona-release_latest.generic_all.deb apt update apt install -y pmm2-client

Red Hat-based

yum install -y <https://repo.percona.com/yum/percona-release-latest.noarch.rpm> yum install -y pmm2-client

In most cases, there should not be any problems. At this point, the PMM Agent is set up and we need to connect it to the PMM Server.

3. Connect the Agent to the Server

Run one command:

pmm-admin config --server-insecure-tls --server-url=https://admin:admin@X.X.X.X:443

The better way to authorise the agent is to create a token via UI and run this command instead:

pmm-admin config --server-insecure-tls --server-url=https://api_key:token@X.X.X.X:443

An option –server-insecure-tls should be used if you haven’t installed SSL certificates to the PMM Server.

💡 This process might be tricky but is recommended for security reasons. You can avoid this routine by getting a fully managed PMM server at hostedpmm.com. There you can get SSL ready PMM server in a few clicks.

After connecting the PMM agent to the PMM Server, you are already able to see your host machine metrics.

PMM Server metrics UI

But as you can see, there is N/A for all the DB-related metrics. So we need to connect DB to the agent.

4. Connect DB to the Agent

It is good practice to create a dedicated non-superuser account to connect the database instance to PMM Agent. E.g. user with name pmm, password pass, and the necessary permissions.

Connecting your DB to the PMM Agent depends on your DB. Basically, you need to run a command in DB. For example, MySQL commands look like this:

CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'pass' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'pmm'@'localhost';

Then you should add a service using PMM Server UI

After that, you should be able to see DB metrics in your PMM Server web UI.

The basic DB monitoring setup is done! We can improve it by customising PMM for your needs.

5. Customise Dashboards and Alerts

There are a lot of preset dashboards but you can customize them for your tasks and workflows.

E.g. if you want an alert for some condition, you can easily set it up.

That’s it! 🎉

Now you have great monitoring for your infrastructure setup. You can keep an eye on a load, check drive usage forecast, and scale in advance. Get alerts if something goes wrong and increase the availability of your service. Use query analysis to find out which requests need to be optimised and improve your service responsiveness.The easiest way to get PMM Server for DB monitoring is HostedPMM.

The easiest way to get PMM Server for DB monitoring is HostedPMM.