Percona Monitoring and Management

Prasad Jayashanka
Sep 30, 2017 · 5 min read

Percona is a company which makes software for MySQL, MongoDB and MariaDB and also provides managed services and consultations for businesses. They have their own Percona Server which is derived from MySQL with lots of optimizations and also another derived from MongoDB.

Percona monitoring and management(PMM) is an awesome tool from them to monitor MySQL based dbs like MySQL, ProxySQL and also for MongoDB. It generates user friendly beautiful metric monitoring thanks to Grafana like this. Ain’t that grand, eh? ;)

Not only that also comes with query analytics. This is very useful tool for sysadmins and developers to keep track of their cluster health. Also with plugins also can be used to manage replication.

This runs as twin module server and client nodes. Following diagram is an overview of the architecture.

Client is installed at database instance node and server is installed in a dedicated host. As an example if you have multiple databases client software should be installed at all server hosts and there should be another server running for installing server module for monitoring. If it’s just one mysql server you can install it in the same server but at the cost of 5–10% processor usage.

Orchestrator is a MySQL replication topology manager. At default settings orchestrator is enabled in PMM server module but can be activated with a simple argument passed to it’s init. It can make topology changes with GUI easily or with CLI or via API. And also monitors replication states.

Prometheus is an open source monitoring API. It’s what responsible of extracting metrics off of mysql, proxysql, mongodb and system.

QAN API is a Golang API for query analytics by Percona. For MySQL it uses slow query log to analyze queries.

Grafana is a open source analytic and metric software with lots of plugins for 3rd party integrations such as percona. The main UI components are composed from Grafana.

So the handcrafted package of those components presents as percona monitoring and management. Setting up is relatively easy.

To setup PMM there are few ways. The way I used was using the docker way for PMM Server and getting PMM client off of apt repository for the MySQL server node I had on a Ubuntu 14.04 instance. For full installation steps use the guide downloadable from here for all other ways included.

Following are the steps I followed for my testing environment with minimal settings required. You need to have either MySQL variant or MongoDB installed to fetch metrics.

Setting up the PMM Server

1) Install docker ( Must be version 1.2 or up )
2) docker pull percona/pmm-server:latest
3) 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
(That line creates a docker to save data.)
4) docker run -d -p 80:80 --volumes-from pmm-data --name pmm-server --restart always percona/pmm-server:latest
(If your server's 80 port is occupied use another port. ex: -p 8080:80)
5) Run "docker ps" and check if it's running

Now you must be able to check the UI of PMM server component via browser. But server is yet to be configured.

Next SSH in to the instance which your database instance is running on.

Setting up the PMM Client

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

Now PMM client is installed. Next step is to connect to server.

sudo pmm-admin config --server <PMM_server_ip:port>This should output like this.----------------------------------------------------
OK, PMM server is alive.
PMM Server | <ip>
Client Name | <hostname>
Client Address | <ip>
----------------------------------------------------

Now can start specifying which dbs should be used for monitoring.

For MySQL

$ sudo pmm-admin add mysql --password <password>

For MongoDB

$ sudo pmm-admin add mongodb --password <password>

For ProxySQL

$ sudo pmm-admin add proxysql:metrics

To recheck what are the db sources being monitored, run following command.

$ sudo pmm-admin listThis should output some the list of configs you have done so far. Something which look like this.pmm-admin 1.3.1PMM Server      | <ip>:<port>
Client Name | <hostname>
Client Address | <ip>
Service Manager | linux-upstart
-------------- --------------- ----------- -------- ------------------------------------------- ---------------------------------------------
SERVICE TYPE NAME LOCAL PORT RUNNING DATA SOURCE OPTIONS
-------------- --------------- ----------- -------- ------------------------------------------- ---------------------------------------------
mysql:queries <hostname>- YES root:***@unix(/var/run/mysqld/mysqld.sock) query_source=perfschema, query_examples=true
linux:metrics <hostname> 42000 YES -
mysql:metrics <hostname> 42002 YES root:***@unix(/var/run/mysqld/mysqld.sock)

That’s it for the configurations. Pretty easy.

Following are some of the screenshots I grabbed from my instance.

Query Analytics

From this screen can fetch information such as QPS, load and latency.
When clicked on an entry you can view more information about that specific query and the breakdown of it.
System Overview ( Screen 1 )
System Overview ( Screen 2 )
InnoDB metrics ( Transactions, deadlocks, I/O, ICP )
InnoDB metrics ( Pool pages, Pool requests, Buffer activity )
Cross Server Graphs ( Memory, MySQL transaction, traffic, network and query count )
MySQL performance schema monitoring
MySQL table stats
Summary dashboard for both system and databases
Disk load, utilization, I/O

These are few of many monitoring capabilities of Percona monitoring and management system. I’m definitely a fan of this. Other than rough 5% performance hit everything worked well. But then again it’s an agent residing on instance polling in timeouts so can’t really complaint. Refresh cycle timing can be changed as low as 5 seconds but once 30 seconds or once a month is a much better choice. Next would be testing replication management with this. If all goes well I will have something to write on as well. A day well spent anyway :)

Prasad Jayashanka

Written by

Software Engineer | Car Enthusiast | Blogger

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade