Learn how to – Monitoring MySQL and MongoDB with Prometheus and Grafana – PMM

Learn how to – Monitoring MySQL and MongoDB with Prometheus and Grafana – PMM.

In this guide, we’ll look at Monitoring MySQL and MongoDB servers with Prometheus and Grafana. This is part of our monitoring series guides. We’ve already covered:

• Monitor Linux System with Grafana and Telegraf
• Monitoring Ceph Cluster with Prometheus and Grafana

For the setup guides, you can refer to below articles:

• Install Grafana and InfluxDB on CentOS 7
• Install Prometheus Server on CentOS 7
• Grafana behind Nginx and Apache Proxy

This guide is not directly related to above since we’re going to use a readily packaged Prometheus server and exporters for Linux, MySQL and MongoDB metrics (PMM server and PMM client)

What is PMM?

Percona Monitoring and Management (PMM) is an open-source platform developed by Percona for managing and monitoring MySQL and MongoDB performance. You can install PMM server in your Infrastructure and be start collecting metrics from pmm-client and visualize them on Grafana.

PMM provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible.

PMM system diagram

Below is a PMM system architecture obtained from Percona web site

The PMM server comes with:

• Prometheus server
• Grafana server
• Consul API and Web UI
• QAN API and App for Analytics

The PMM client  is bundled with:

• Prometheus  node_exporter
• Prometheus mysql_exporter
• Prometheus mongodb_exporter
• Prometheus proxysql_exporter
• pmm-mysql and pmm-mongodb queries

Install PMM Server and Configure PMM Clients

There are various ways of running PMM server on your server.

• Using docker
• Using ready OVA which can be imported to VMware VirtualBox or VMware Infrastructure
• On AWS using a ready AMI (Amazon Machine Image).

I prefer installing PMM server using Docker. The process is straightforward and doesn’t require any special environment/prerequisites since you can run it on any Linux Distribution.

Step 1: Install Docker Engine

You need to start by installing Docker Engine on your machine, we have a well-written guide on How to Install Docker on Linux.

Once docker is installed ensure it’s started

sudo systemctl start docker
sudo systemctl enable docker

Also add your user account to docker group.

sudo usermod -aG docker $USER
newgrp docker

Step 2: Download PMM server docker image

Let’s download docker image that we’ll use to create our pmm server container.

docker pull percona/pmm-server:latest

This may take a while depending on your internet speed,

Step 3: Create pmm data container

To make our data persistent, we’re going to create a container to use as data store:

docker create --volume /data --name pmm-data percona/pmm-server:latest /bin/true

Note that this container is not to be started, we just need to make it exist. Do not remove or re-create this container, unless you intend to wipe out all PMM data and start over.

Step 3: Create PMM server container

Once you have pmm data container created, you can create PMM server container. For this, We’re going to create a systemd service unit file, this will make it easy to manage the container. You can use docker-compose if you like.

sudo tee /etc/systemd/system/pmm-server.service<<EOF
[Unit]
Description=Manage PMM Server in container

[Install]
WantedBy=multi-user.target

[Service]
Restart=always
TimeoutStartSec=0
ExecStartPre=-/usr/bin/docker kill pmm-server
ExecStartPre=-/usr/bin/docker rm pmm-server

ExecStart=/usr/bin/docker run 
 --name pmm-server 
 --volumes-from pmm-data 
 --name pmm-server 
 --restart always 
 -p=443:443 
 percona/pmm-server

ExecStop=-/usr/bin/docker kill pmm-server
ExecStop=-/usr/bin/docker rm pmm-server
EOF

This will start container with the name pmm-server whose volume is pmm-data. Make sure the name of volume container matches the one you created. Start the service with systemctl command if you’re running this on a systemd init system.

sudo systemctl daemon-reload
sudo systemctl restart pmm-server

You can enable the service to start on boot using:

sudo systemctl enable pmm-server

Since we have  -p=443:443, the service will be accessible from port 443 on the host system.

$ ss -tunelp | grep 443
tcp   LISTEN 0      4096                    0.0.0.0:443        0.0.0.0:*    ino:729893 sk:5c cgroup:/system.slice/docker.service <->
tcp   LISTEN 0      4096                       [::]:443           [::]:*    ino:729900 sk:5d cgroup:/system.slice/docker.service v6only:1 <->

If you have firewall service, ensure the port is open. I have firewalld enabled:

sudo firewall-cmd --add-service=https --permanent
sudo firewall-cmd --reload

You can  check if PMM Server is available requesting the /ping URL as in the following example:

$ curl -ik https://localhost/ping
HTTP/2 200
server: nginx
date: Fri, 14 Oct 2022 08:35:29 GMT
content-type: application/json
content-length: 2
grpc-metadata-content-type: application/grpc
x-frame-options: DENY
x-content-type-options: nosniff
x-xss-protection: 1; mode=block
cache-control: no-cache
pragma: no-cache
strict-transport-security: max-age=63072000; includeSubdomains;

Access Grafana dashboard using: https://serverip . You should get an interface like this:

Step 4: Install PMM client on MySQL server.

Percona provides PMM Client packages through software repositories of popular Linux distributions. The client collects metrics from the server to be used by Prometheus for monitoring. Add Percona repositories:

For Ubuntu/Debian:

sudo apt update && sudo apt install gnupg2 curl
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo apt update
sudo apt install pmm2-client

For CentOS/RedHat

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

Step 5: Connecting PMM Clients to the PMM Server

With your server and clients set up, you need to establish connection from clients to the server by specifying the IP address of the server as a parameter to the pmm-admin config –server command.

$ sudo pmm-admin config --server-insecure-tls --server-url https://admin:admin@serverIP:443
Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/local/percona/pmm2/config/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.
Checking local pmm-agent status...
pmm-agent is running.

Where:

• serverIP is the IP address of your PMM Server.
• 443 is the default port number used by PMM Server
• admin/admin is the default PMM username and password. This is the same account you use to log into the PMM user interface, which you had the option to change when first logging in.

Add services to PMM

You must configure and adding services according to the service type.

• MySQL (and variants Percona Server for MySQL, Percona XtraDB Cluster, MariaDB)
• MongoDB
• PostgreSQL
• ProxySQL
• Amazon RDS
• Microsoft Azure
• Google Cloud Platform (MySQL and PostgreSQL)
• Linux
• External services
• HAProxy
• Remote instances

Visit your grafana UI to view various MySQL stats, for example, the overview looks like below: