(Last Updated On: May 19, 2018)

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

Installing PMM Server

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.

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 \
 -v /opt/prometheus/data \
 -v /opt/consul-data \
 -v /var/lib/mysql \
 -v /var/lib/grafana \
 --name pmm-data \
 percona/pmm-server:latest /bin/true

88f682d55daa5ebf9ca3581bfb3e71fbcab29a57b883a07a94dfe476bffcdd73

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.

# cat /etc/systemd/system/pmm-server.service 

[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=8080:80 \
 percona/pmm-server

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

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.

#  systemctl start pmm-server

You can enable the service to start on boot using:

# systemctl enable pmm-server

Since we have  -p=8080:80, the service will be accessible from port 8080 on the host system. If you have firewall service, ensure the port is open. I have firewalld enabled:

# firewall-cmd --add-port=8080/tcp --permanent
# firewall-cmd --reload

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

$ curl http://192.168.18.10/ping
{'version': '1.8.0'}

Access Grafana dashboard using: http://serverip:8080 . 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:

$ wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
$ sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
$ sudo apt-get update
$ sudo apt-get install pmm-client

For CentOS/RedHat

$ sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
$ sudo yum -y install pmm-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.

# pmm-admin config --server 192.168.18.10:8080
OK, PMM server is alive.

PMM Server      | 192.168.18.10:8080 
Client Name     | ubuntu1604.localdomain
Client Address  | 192.168.18.12 

Replace 192.168.18.10 with your PMM server IP address. To enable general system metrics, MySQL metrics, as well as MySQL query analytics, run pmm-admin add as follows.

# pmm-admin add mysql --user root --password password     
[linux:metrics] OK, now monitoring this system.
[mysql:metrics] OK, now monitoring MySQL metrics using DSN root:***@unix(/var/run/mysqld/mysqld.sock)
[mysql:queries] OK, now monitoring MySQL queries from slowlog using DSN root:***@unix(/var/run/mysqld/mysqld.sock)

To enable general system metrics, MongoDB metrics, and MongoDB query analytics, run:

$ sudo pmm-admin add mongodb

To enable ProxySQL performance metrics, run:

$ sudo pmm-admin add proxysql:metrics

To see what is being monitored, run:

# sudo pmm-admin list
pmm-admin 1.10.0

PMM Server      | 192.168.18.10:8080 
Client Name     | ubuntu1604.localdomain
Client Address  | 192.168.18.12 
Service Manager | linux-systemd

-------------- ----------------------- ----------- -------- ------------------------------------------- ------------------------------------------
SERVICE TYPE   NAME                    LOCAL PORT  RUNNING  DATA SOURCE                                 OPTIONS                                   
-------------- ----------------------- ----------- -------- ------------------------------------------- ------------------------------------------
mysql:queries  ubuntu1604.localdomain  -           YES      root:***@unix(/var/run/mysqld/mysqld.sock)  query_source=slowlog, query_examples=true 
linux:metrics  ubuntu1604.localdomain  42000       YES      -                                                                                     
mysql:metrics  ubuntu1604.localdomain  42002       YES      root:***@unix(/var/run/mysqld/mysqld.sock)  

For more information about the available commands of pmm-admin add, run:

# pmm-admin add --help

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