Observations on Better Resource Usage with Percona Monitoring and Management v2.12.0

Better Resource Usage with Percona Monitoring and Management

Better Resource Usage with Percona Monitoring and ManagementPercona Monitoring and Management (PMM) v2.12.0 comes with a lot of improvements and one of the most talked-about is the usage of VictoriaMetricsDB. The reason we are doing this comparison is that PMM 2.12.0 is a release in which we integrate VictoriaMetricsDB and replace Prometheus as its default method of data ingestion.

A reason for this change was also driven by motivation for improved performance for PMM Server, and here we will look at an overview of why users must definitely consider using the 2.12.0 version if they have always been looking for a less resource-intensive PMM. This post will try to address some of those concerns.

Benchmark Setup Details

The benchmark was performed using a virtualized system with PMM Server running on an ec2 instance, with 8 cores, 32 GB of memory, and SSD Storage. The duration of the observation is 24 hours, and for clients, we set up 25 virtualized client instances on Linode with each emulating 10 Nodes running MySQL with real workloads using Sysbench TPC-C Test.

Percona Monitoring and Management benchmark

Both PMM 2.11.1 and PMM 2.12.0 were set up in the same way, with client instances running the exact same load, and to monitor the difference in performance, we used the default metrics mode for 2.12.0 for this observation.

Sample Ingestion rate for the load was around 96.1k samples/sec, with around 8.5 billion samples received in 24 hours. 

A more detailed benchmark for Prometheus vs. VictoriaMetrics was done by the VM team and it clearly shows how efficient VictoriaMetrics is and how better performance can be achieved with Victoria Metrics.

Disk Space Usage

VictoriaMetrics has really good efficiency when it comes to disk usage of the host system, and we found that 2.11.1 generates a lot of disk usage spikes with the maximum storage touching around 23.11 GB of storage space. If we compare the same for PMM 2.12.0, the disk usage spikes are not as high as 2.11.1 while the maximum disk usage is around 8.44 GB.

It is clear that PMM 2.12.0 needs 2.7 times less disk space for monitoring the same number of services for the same duration as compared to PMM 2.11.1.

Disk Usage PMM 2.11.1

Disk Usage 1: PMM 2.11.1 

Disk Usage PMM 2.12.0

Disk Usage 2: PMM 2.12.0

Memory Utilization

Another parameter on which PMM 2.12.0 performs better is Memory Utilization. During our testing, we found that PMM 2.11.1 was using two times more memory for monitoring the same number of services. This is indeed a significant improvement in terms of performance.

The memory usage clearly shows several spikes for PMM 2.11.1, which is not the case with PMM 2.12.0.

Memory Utilization PMM 2.11.1

Memory Utilization: PMM 2.11.1

Free Memory PMM 2.11.1
Free Memory PMM 2.11.1 

Memory Utilization 2.11.1
Memory Utilization PMM 2.12.0

The Memory Utilization for 2.12.0 clearly shows more than 55% of memory is available across the 24 hours of our observation, which is a significant improvement over 2.11.1.

Free Memory 2.12.0

Free Memory PMM 2.12.0

CPU Usage

During the observation we noticed a slight increase in CPU usage for PMM 2.12.0, the average CPU usage was about 2.6% more than PMM 2.11.1, but the max CPU usage for both versions did not have any significant difference.

CPU Usage 2.11.1

CPU Usage: PMM 2.11.1

CPU Usage 2.12.0

CPU Usage: PMM 2.12.0



The overall performance improvements are around the Memory Utilization and Disk Usage, and we also observed a significantly less disk I/O bandwidth with far fewer spikes in the write operations for PMM 2.12.0. This behavior is observed and articulated in the VictoriaMetrics benchmarking. CPU Usage and Memory are two important resource factors when planning to setup PMM Server, and with PMM 2.12.0 we can safely say that it will cost about half in terms of Memory and Disk resources when compared to any other previously released PMM versions. This would also likely encourage current users to be able to add more instances for monitoring without the need to think about the cost of extra infrastructure. 


Enabling HTTPS Connections to Percona Monitoring and Management Using Custom Certificates

HTTPS Connections to Percona Monitoring and Management

HTTPS Connections to Percona Monitoring and ManagementWhichever way you installed Percona Monitoring and Management 2 (PMM2), using the docker image or an OVF image for your supported virtualized environment, PMM2 enables, by default, two ports for the web connections: 80 for HTTP and 443 for HTTPS. Using HTTPS certificates are requested for encrypting the connection for better security.

All the installation images contain self-signed certificates already configured, so every PMM2 deployment should work properly when using HTTPS.

This is cool, but sometimes self-signed certificates are not permitted, based on the security policy adopted by your company. If your company uses a Certification Authority to sign certificates and keys for encryption, most probably you are forced to use the files provided by the CA for all your services, even for PMM2 monitoring.

In this article, we’ll show how to use your custom certificates to enable HTTPS connections to PMM2, according to your security policy.

PMM2 Deployed as a Docker Image

If PMM Server is running as a Docker image, use docker cp to copy certificates. This example copies certificate files from the current working directory to a running PMM Server docker container.

docker cp certificate.crt pmm-server:/srv/nginx/certificate.crt
docker cp certificate.key pmm-server:/srv/nginx/certificate.key
docker cp ca-certs.pem pmm-server:/srv/nginx/ca-certs.pem
docker cp dhparam.pem pmm-server:/srv/nginx/dhparam.pem

If you’re going to deploy the container, you can use the following to use your own certificates instead of the built-in ones. Let’s suppose your certificates are in /etc/pmm-certs:

docker run -d -p 443:443 --volumes-from pmm-data \
  --name pmm-server -v /etc/pmm-certs:/srv/nginx \
  --restart always percona/pmm-server:2

  • The certificates must be owned by root.
  • The mounted certificate directory must contain the files certificate.crt, certificate.key, ca-certs.pem and dhparam.pem.
  • For SSL encryption, the container must publish on port 443 instead of 80.

PMM2 Deployed Using a Virtual Appliance Image

In such cases, you need to connect to the virtual machine and replace the certificate files in /srv/nginx:

  • connect to the virtual machine
    $> ssh
  • place CA, certificate, and key files into the /srv/nginx directory. The file must be named certificate.crt, certificate.key, ca-certs.pem and dhparam.pem
  • if you would like to use different file names you can modify the nginx configuration file /etc/nginx/conf.d/pmm.conf. The following variables must be set:
    ssl_certificate /srv/nginx/my_custom_certificate.crt;
    ssl_certificate_key /srv/nginx/my_custom_certificate.key;
    ssl_trusted_certificate /srv/nginx/my_custom_ca_certs.pem;
    ssl_dhparam /srv/nginx/my_dhparam.pem
  • restart nginx
    [root@pmm2]> supervisorctl restart nginx


Percona Monitoring and Management is widely used for monitoring MySQL, Proxysql, MongoDB, PostgreSQL, and OSes. Setting up customer certificates for the connection encryption, according to the security policy adopted by your company, is quite simple. You can rely on PMM2 for troubleshooting your environments in a secure way.

Take a look at the demo site:


How to Find Query Slowdowns Using Percona Monitoring and Management

Query Slowdowns Using Percona Monitoring and Management

Visibility is a blessing, and with databases, visibility is a must. That’s true not only for metrics but for the queries themselves. Having info on all the stats around query execution is priceless, and Percona Monitoring and Management (PMM) offers that in the form of the Query Analytics dashboard (QAN).

But where to start? QAN helps you with that by calculating the query profile. What is the profile? It’s a rank of queries, ordered by Load, so it is easy to spot the heaviest queries hitting your database. The Load is defined as the “Average Active Queries” but can also be defined as a mix of Query Execution Time Plus Query count. In other words, all the time the query was alive and kicking.

The Profile in PMM 2.10.0 looks like this:

percona monitoring and management

The purpose of this profile is to facilitate the task of finding the queries that are worth improving, or at least the ones that will have a bigger impact on the performance when optimized.

However, how do you know that a slow query has been always slow or it has come down the road from good performance to painfully slow? That’s where the graph on the “Load” column comes handy.

There’s a method for doing this. The first step is to have a wide view. That means: check a time range long enough so you can see patterns. Personally, I like to check the last 7 days.

The second step is to find irregularities like spikes or increasing patterns. For example, in the above profile, we can see that the “SHOW BINARY LOGS” command is the top #4 of queries adding more load to the database. In this case, it’s because the binlogs are not being purged, so every day there are more and more binlog files to read and that adds to the executing time. But the amount of times that the “SHOW BINARY LOGS” query is executed remains the same.

Another query with an “anomaly” in the load graph is the top #3 one. Let’s isolate it and see what happened:

Query Analytics dashboard percona

The third step will be to reduce the time to a range involving the event so we can isolate it even more:

Query Analytics dashboard percona monitoring and management

The event happened between 8 AM and 9 AM. To discard or confirm that is an isolated event only related to this query, let’s see again all the queries running at that same moment.

So this is a generic situation, common to several queries. Most likely it was an event with the server that made queries to stall.

By looking at the threads graph, we can confirm that hypothesis:

MySQL Active Client Threads

After some digging, the source cause was detected to be a Disk problem:

Query Analytics dashboard disk latency

It’s confirmed that it is not an issue with the query itself, so no need to “optimize” due to this spike.

In conclusion, with the new QAN dashboard available since PMM 2.10.0, finding query slowdowns is easier thanks to the Load graph that can give us context pretty fast.

Try Percona Monitoring and Management today, for free!


Running PMM1 and PMM2 Clients on the Same Host

Running PMM1 and PMM2 Clients

Running PMM1 and PMM2 ClientsWant to try out Percona Monitoring and Management 2 (PMM 2) but you’re not ready to turn off your PMM 1 environment?  This blog is for you! Keep in mind that the methods described are not intended to be a long-term migration strategy, but rather, simply a way to deploy a few clients in order to sample PMM 2 before you commit to the upgrade. ?

Here are step-by-step instructions for deploying PMM 1 & 2 client functionality i.e. pmm-client and pmm2-client, on the same host.

  1. Deploy PMM 1 on Server1 (you’ve probably already done this)
  2. Install and setup pmm-client for connectivity to Server1
  3. Deploy PMM 2 on Server2
  4. Install and setup pmm2-client for connectivity to Server2
  5. Remove pmm-client and switched completely to pmm2-client

The first few steps are already described in our PMM1 documentation so we are simply providing links to those documents.  Here we’ll focus on steps 4 and 5.

Install and Setup pmm2-client Connectivity to Server2

It’s not possible to install both clients from a repository at the same time. So you’ll need to download a tarball of pmm2-client. Here’s a link to the latest version directly from our site.

Download pmm2-client Tarball

* Note that depending on when you’re seeing this, the commands below may not be for the latest version, so the commands may need to be updated for the version you downloaded.

$ wget

Extract Files From pmm2-client Tarball

$ tar -zxvf pmm2-client-2.1.0.tar.gz 
$ cd pmm2-client-2.1.0

Register and Generate Configuration File

Now it’s time to set up a PMM 2 client. In our example, the PMM2 server IP is and the monitored host IP is

$ ./bin/pmm-agent setup --config-file=config/pmm-agent.yaml \
--paths-node_exporter="$PWD/pmm2-client-2.1.0/bin/node_exporter" \
--paths-mysqld_exporter="$PWD/pmm2-client-2.1.0/bin/mysqld_exporter" \
--paths-mongodb_exporter="$PWD/pmm2-client-2.1.0/bin/mongodb_exporter" \
--paths-postgres_exporter="$PWD/pmm2-client-2.1.0/bin/postgres_exporter" \
--paths-proxysql_exporter="$PWD/pmm2-client-2.1.0/bin/proxysql_exporter" \
--server-insecure-tls --server-address= \
--server-username=admin  --server-password="admin" generic

Start pmm-agent

Let’s run the pmm-agent using a screen.  There’s no service manager integration when deploying alongside pmm-client, so if your server restarts, pmm-agent won’t automatically resume.

# screen -S pmm-agent

$ ./bin/pmm-agent --config-file="$PWD/config/pmm-agent.yaml"

Check the Current State of the Agent

$ ./bin/pmm-admin list
Service type  Service name         Address and port  Service ID

Agent type                  Status     Agent ID                                        Service ID
pmm-agent                   connected  /agent_id/805db700-3607-40a9-a1fa-be61c76fe755  
node_exporter               running    /agent_id/805eb8f6-3514-4c9b-a05e-c5705755a4be

Add MySQL Service

Detach the screen, then add the mysql service:

$ ./bin/pmm-admin add mysql --use-perfschema --username=root mysqltest
MySQL Service added.
Service ID  : /service_id/28c4a4cd-7f4a-4abd-a999-86528e38992b
Service name: mysqltest

Here is the state of pmm-agent:

$ ./bin/pmm-admin list
Service type  Service name         Address and port  Service ID
MySQL         mysqltest      /service_id/28c4a4cd-7f4a-4abd-a999-86528e38992b

Agent type                  Status     Agent ID                                        Service ID
pmm-agent                   connected  /agent_id/805db700-3607-40a9-a1fa-be61c76fe755   
node_exporter               running    /agent_id/805eb8f6-3514-4c9b-a05e-c5705755a4be   
mysqld_exporter             running    /agent_id/efb01d86-58a3-401e-ae65-fa8417f9feb2  /service_id/28c4a4cd-7f4a-4abd-a999-86528e38992b
qan-mysql-perfschema-agent  running    /agent_id/26836ca9-0fc7-4991-af23-730e6d282d8d  /service_id/28c4a4cd-7f4a-4abd-a999-86528e38992b

Confirm you can see activity in each of the two PMM Servers:


Remove pmm-client and Switch Completely to pmm2-client

Once you’ve decided to move over completely to PMM2, it’s better to make a switch from the tarball version to installation from the repository. It will allow you to perform client updates much easier as well as register the new agent as a service for automatically starting with the server. Also, we will show you how to make a switch without re-adding monitored instances.

Configure Percona Repositories

$ sudo yum install 
$ sudo percona-release disable all 
$ sudo percona-release enable original release 
$ yum list | grep pmm 
pmm-client.x86_64                    1.17.2-1.el6                  percona-release-x86_64
pmm2-client.x86_64                   2.1.0-1.el6                   percona-release-x86_64

Here is a link to the apt variant.

Remove pmm-client

yum remove pmm-client

Install pmm2-client

$ yum install pmm2-client
Loaded plugins: priorities, update-motd, upgrade-helper
4 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package pmm2-client.x86_64 0:2.1.0-5.el6 will be installed
  pmm2-client.x86_64 0:2.1.0-5.el6                                                                                                                                                           


Configure pmm2-client

Let’s copy the currently used pmm2-client configuration file in order to omit re-adding monitored instances.

$ cp pmm2-client-2.1.0/config/pmm-agent.yaml /tmp

It’s required to set the new location of exporters (/usr/local/percona/pmm2/exporters/) in the file.

$ sed -i 's|node_exporter:.*|node_exporter: /usr/local/percona/pmm2/exporters/node_exporter|g' /tmp/pmm-agent.yaml
$ sed -i 's|mysqld_exporter:.*|mysqld_exporter: /usr/local/percona/pmm2/exporters/mysqld_exporter|g' /tmp/pmm-agent.yaml
$ sed -i 's|mongodb_exporter:.*|mongodb_exporter: /usr/local/percona/pmm2/exporters/mongodb_exporter|g' /tmp/pmm-agent.yaml 
$ sed -i 's|postgres_exporter:.*|postgres_exporter: /usr/local/percona/pmm2/exporters/postgres_exporter|g' /tmp/pmm-agent.yaml
$ sed -i 's|proxysql_exporter:.*|proxysql_exporter: /usr/local/percona/pmm2/exporters/proxysql_exporter|g' /tmp/pmm-agent.yaml

The default configuration file has to be replaced by our file and the service pmm-agent has to be restarted.

$ cp /tmp/pmm-agent.yaml /usr/local/percona/pmm2/config/
$ systemctl restart pmm-agent

Check Monitored Services

So now we can verify the current state of monitored instances.

$ pmm-admin list

Also, it can be checked on PMM server-side.


Tips for Designing Grafana Dashboards

Designing Grafana Dashboards

As Grafana powers our star product – Percona Monitoring and Management (PMM) – we have developed a lot of experience creating Grafana Dashboards over the last few years.   In this article, I will share some of the considerations for designing Grafana Dashboards. As usual, when it comes to questions of design they are quite subjective, and I do not expect you to chose to apply all of them to your dashboards, but I hope they will help you to think through your dashboard design better.

Design Practical Dashboards

Grafana features many panel types, and even more are available as plugins. It may be very attractive to use many of them in your dashboards using many different visualization options. Do not!  Stick to a few data visualization patterns and only add additional visualizations when they provide additional practical value not because they are cool.  Graph and Singlestat panel types probably cover 80% of use cases.

Do Not Place Too Many Graphs Side by Side

This probably will depend a lot on how your dashboards are used.  If your dashboard is designed for large screens placed on the wall you may be able to fit more graphs side by side, if your dashboard needs to scale down to lower resolution small laptop screen I would suggest sticking to 2-3 graphs in a row.

Use Proper Units

Grafana allows you to specify a unit for the data type displayed. Use it! Without type set values will not be properly shortened and very hard to read:

Grafana Dashboards

Compare this to

Grafana Dashboards2

Mind Decimals

You can specify the number of values after decimal points you want to display or leave it default.  I found default picking does not always work very well, for example here:

Grafana Dashboards3

For some reason on the panel Axis, we have way too many values displayed after the decimal point.  Grafana also often picks three values after decimal points as in the table below which I find inconvenient – from the glance view, it is hard to understand if we’re dealing with a decimal point or with “,” as a “thousands” separator, so I may be looking at 2462 GiB there.  While it is not feasible in this case, there are cases such as data rate where a 1000x value difference is quite possible.  Instead, I prefer setting it to one decimal (or one if it is enough) which makes it clear that we’re not looking at thousands.

Label your Axis

You can label your axis (which especially makes sense) if the presentation is something not as obvious as in this example; we’re using a negative value to lot writes to a swap file.

Grafana Dashboards4

Use Shared Crosshair or Tooltip

In Dashboard Settings, you will find “Graph Tooltip” option and set it to “Default”,
“Shared Crosshair” or “Share Tooltip”  This is how these will look:

Grafana Dashboards5

Grafana Dashboards 6

Grafana Dashboards 6


Shared crosshair shows the line matching the same time on all dashboards while Tooltip shows the tooltip value on all panels at the same time.  You can pick what makes sense for you; my favorite is using the tooltip setting because it allows me to visually compare the same time without making the dashboard too slow and busy.

Note there is handy shortcut CTRL-O which allows you to cycle between these settings for any dashboard.

Pick Colors

If you’re displaying truly dynamic information you will likely have to rely on Grafana’s automatic color assignment, but if not, you can pick specific colors for all values being plotted.  This will prevent colors from potentially being re-assigned to different values without you planning to do so.

Grafana Dashboards 7

Picking colors you also want to make sure you pick colors that make logical sense. For example, I think for free memory “green” is a better color than “red”.  As you pick the colors, use the same colors for the same type of information when you show it on the different panels if possible, because it makes it easier to understand.

I would even suggest sticking to the same (or similar) color for the Same Kind of Data – if you have many panels which show disk Input and Output using similar colors, this can be a good idea.

Fill Stacking Graphs

Grafana does not require it, but I would suggest you use filling when you display stacking data and don’t use filling when you’re plotting multiple independent values.  Take a look at these graphs:

In the first graph, I need to look at the actual value of the plotted value to understand what I’m looking at. At the same time, in the second graph, that value is meaningless and what is valuable is the filled amount. I can see on the second graph what amount of the Cache, blue value, has shrunk.

I prefer using a fill factor of 6+ so it is easier to match the fill colors with colors in the table.   For the same reason, I prefer not to use the fill gradient on such graphs as it makes it much harder to see the color and the filled volume.

Do Not Abuse Double Axis

Graphs that use double axis are much harder to understand.  I used to use it very often, but now I avoid it when possible, only using it when I absolutely want to limit the number of panels.

Note in this case I think gradient fits OK because there is only one value displayed as the line, so you can’t get confused if you need to look at total value or “filled volume”.

Separate Data of Different Scales on Different Graphs

I used to plot Innodb Rows Read and Written at the same graph. It is quite common to have reads to be 100x higher in volume than writes, crowding them out and making even significant changes in writes very hard to see.  Splitting them to different graphs solved this issue.

Consider Staircase Graphs

In the monitoring applications, we often display average rates computed over a period of time.  If this is the case, we do not know how the rate was changing within that period and it would be misleading to show that. This especially makes sense if you’re displaying only a few data points.

Let’s look at this graph which is being viewed with one-hour resolution:

This visually shows what amount of rows read was falling from 16:00 to 18:00, and if we compare it to the staircase graph:

It simply shows us that the value at 18 am was higher than 17 am, but does not make any claim about the change.

This display, however, has another issue. Let’s look at the same data set with 5min resolution:

We can see the average value from 16:00 to 17:00 was lower than from 17:00 to 18:00, but this is however NOT what the lower resolution staircase graph shows – the value for 17 to 18 is actually lower!

The reason for that is if we compute on Prometheus side rate() for 1 hour at 17:00 it will be returned as a data point for 17:00 where this average rate is really for 16:00 to 17:00, while staircase graph will plot it from 17:00 to 18:00 until a new value is available.  It is off by one hour.

To fix it, you need to shift the data appropriately. In Prometheus, which we use in PMM, I can use an offset operator to shift the data to be displayed correctly:

Provide Multiple Resolutions

I’m a big fan of being able to see the data on the same dashboard with different resolutions, which can be done through a special dashboard variable of type “Interval”.  High-resolution data can provide a great level of detail but can be very volatile.

While lower resolution can hide this level of detail, it does show trends better.

Multiple Aggregates for the Same Metrics

To get even more insights, you can consider plotting the same metrics with different aggregates applied to it:

In this case, we are looking at the same variable – threads_running – but at its average value over a period of time versus max (peak) value. Both of them are meaningful in a different way.

You can also notice here that points are used for the Max value instead of a line. This is in general good practice for highly volatile data, as a plottings line for something which changes wildly is messy and does not provide much value.

Use Help and Panel Links

If you fill out a description for the panel, it will be visible if you place your mouse over the tiny “i” sign. This is very helpful to explain what the panel shows and how to use this data.  You can use Markup for formatting.  You can also provide one or more panel links, that you can use for additional help or drill down.

With newer Grafana versions, you can even define a more advanced drill-down, which can contain different URLs based on the series you are looking at, as well as other templating variables:


This list of considerations for designing Grafana Dashboards and best practices is by no means complete, but I hope you pick up an idea or two which will allow you to create better dashboards!


Monitoring PostgreSQL Databases Using PMM

Monitoring PostgreSQL with Percona Monitoring Management

PostgreSQLPostgreSQL is a widely-used Open Source database and has been the DBMS of the year for the past 2 years in DB-Engine rankings. As such, there is always a need for reliable and robust monitoring solutions. While there are some commercial monitoring tools, there is an equally good number of open source tools available for monitoring PostgreSQL. Percona Monitoring and Management (PMM) is one of those open source solutions that have continuous improvements and is maintained forever by Percona. It is simple to set up and easy to use.

PMM can monitor not only PostgreSQL but also MySQL and MongoDB databases, so it is a simple monitoring solution for monitoring multiple types of databases. In this blog post, you will see all the steps involved in monitoring PostgreSQL databases using PMM.

This is what we will be discussing:

  1. Using the PMM docker image to create a PMM server.
  2. Installing PMM client on a Remote PostgreSQL server and connecting the PostgreSQL Client to PMM Server.
  3. Creating required users and permissions on the PostgreSQL server.
  4. Enabling PostgreSQL Monitoring with and without QAN (Query Analytics)

If you already know how to create a PMM Server, please skip the PMM server setup and proceed to the PostgreSQL client setup.

Using the PMM docker image to create a PMM server

PMM is a client-server architecture where clients are the PostgreSQL, MySQL, or MongoDB databases and the server is the PMM Server. We see a list of metrics on the Grafana dashboard by connecting to the PMM server on the UI. In order to demonstrate this setup, I have created 2 virtual machines where one of them is the PMM Server and the second server is the PostgreSQL database server. is my PMM-Server is my PG 11 Server

Step 1 : 

On the PMM Server, install and start docker.

# yum install docker -y
# systemctl start docker

Here are the installation instructions of PMM Server.

Step 2 :

Pull the pmm-server docker image. I am using the latest PMM2 docker image for this setup.

$ docker pull percona/pmm-server:2

You see a docker image of size 1.48 GB downloaded after the above step.

$ docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE 2 cd30e7343bb1 2 weeks ago 1.48 GB

Step 3 :

Create a container for persistent PMM data.

$ docker create \
-v /srv \
--name pmm-data \
percona/pmm-server:2 /bin/true

Step 4 :

Create and launch the PMM Server. In the following step, you can see that we are binding the port 80 of the container to the port 80 of the host machine. Likewise for port 443.

$ docker run -d \
-p 80:80 \
-p 443:443 \
--volumes-from pmm-data \
--name pmm-server \
--restart always \

At this stage, you can modify certain settings such as the memory you wish to allocate to the container or the CPU share, etc. You can also see more such configurable options using

docker run --help

. The following is just an example of how you can modify the above step with some memory or CPU allocations.

$ docker run -d \
-p 80:80 \
-p 443:443 \
--volumes-from pmm-data \
--name pmm-server \
--cpu-shares 100 \
--memory 1024m \
--restart always \

You can list the containers started for validation using

docker ps


$ docker ps
bb6043082d3b percona/pmm-server:2 "/opt/" About a minute ago Up About a minute>80/tcp,>443/tcp pmm-server

Step 5 : 

You can now see the PMM Server Dashboard in the browser using the Host IP address. For my setup, the PMM Server’s IP Address is

. As soon as you put the IP in the browser, you will be asked to enter the credentials as seen in the image below. Default user and password are both:


create a PMM server

And then you will be asked to change the password or skip.

PMM Server setup is completed after this step.

Installing PMM client on a Remote PostgreSQL server

I have a PostgreSQL 11.5 Server running on

. The following steps demonstrate how we can install and configure the PMM client to enable monitoring from the PMM server (


Before you proceed further, you must ensure that ports 80 and 443 are both enabled on the PMM server for the PG 11 Server to connect. In order to test that, I have used telnet to validate whether ports 80 and 443 are open on the PMM Server for the pg11 server.

[root@pg11]$ hostname -I

[root@pg11]$ telnet 80
Connected to
Escape character is '^]'.

[root@pg11]$ telnet 443
Connected to
Escape character is '^]'.

Step 6 :

There are very few steps you need to perform on the PostgreSQL server to enable it as a client for PMM server. The first step is to install the PMM Client on the PostgreSQL Database server as follows. Based on the current PMM release, I am installing


today. But, this may change once we have a new PMM release.

$ sudo yum install
$ sudo yum install pmm2-client -y

Step 7 :

The next step is to connect the client (PostgreSQL server) to the PMM Server. We could use

pmm-admin config

in order to achieve that. Following is a simple syntax that you could use in general.

$ pmm-admin config [<flags>] [<node-address>] [<node-type>] [<node-name>]

The following are the flags and other options I could use with my setup.

flags        : --server-insecure-tls
               (--server-url should contain the PMM Server Host information)

node-address :
               (My PostgreSQL Server)

node-type    : generic
               (As I am running my PostgreSQL database on a Virtual Machine but not on a Container, it is generic.)

node-name    : pg-client
               (Can be any nodename you could use to uniquely identify this database server on your PMM Server Dashboard)

So the final syntax for my setup looks like the below. We can run this command as root or by using the sudo command.

Syntax : 7a

$ pmm-admin config --server-insecure-tls --server-url=https://admin:admin@ generic pg-client

$ pmm-admin config --server-insecure-tls --server-url=https://admin:admin@ generic pg-client
Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
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.

Syntax : 7b

You could also use a simple syntax such as following without

node-address, node-type, node-name


$ pmm-admin config --server-insecure-tls --server-url=https://admin:admin@

But when you use such a simple syntax as above,

node-address, node-type, node-name

are defaulted to certain values. If the defaults are incorrect due to your server configuration, you may better pass these details explicitly like I have done in the

syntax : 7a

. In order to validate whether the defaults are correct, you can simply use

# pmm-admin config --help

. In the following log, you see that the


  defaults to

which is incorrect for my setup. It should be


# pmm-admin config --help
usage: pmm-admin config [<flags>] [<node-address>] [<node-type>] [<node-name>]

Configure local pmm-agent

  -h, --help                   Show context-sensitive help (also try --help-long and --help-man)
      --version                Show application version
  [<node-address>]  Node address (autodetected default:

Below is an example where the default settings were perfect because I had configured my database server the right way.

# pmm-admin config --help
usage: pmm-admin config [<flags>] [<node-address>] [<node-type>] [<node-name>]

Configure local pmm-agent

  -h, --help                   Show context-sensitive help (also try --help-long and --help-man)
  [<node-address>]  Node address (autodetected default:
  [<node-type>]     Node type, one of: generic, container (default: generic)
  [<node-name>]     Node name (autodetected default: pg-client)

Using steps 6 and 7a, I have finished installing the PMM client on the PostgreSQL server and also connected it to the PMM Server. If the above steps are successful, you should see the client listed under Nodes, as seen in the following image. Else, something went wrong.

Creating required users and permissions on the PostgreSQL server

In order to monitor your PostgreSQL server using PMM, you need to create a user *using* which the database stats can be collected by the PMM agent. However, starting from PostgreSQL 10, you do not need to grant SUPERUSER or use SECURITY DEFINER (to avoid granting SUPERUSER). You can simply grant the role


to a user (monitoring user). In my next blog post, you will see how we could use SECURITY DEFINER to avoid granting SUPERUSER for monitoring PostgreSQL databases with 9.6 or older.

Assuming that your PostgreSQL Version is 10 or higher, you can use the following steps.

Step 1 : 

Create a postgres user that can be used for monitoring. You could choose any username;


in the following command is just an example.

$ psql -c "CREATE USER pmm_user WITH ENCRYPTED PASSWORD 'secret'"

Step 2 : 



role to the



$ psql -c "GRANT pg_monitor to pmm_user"

Step 3 : 

If you are not using localhost, but using the IP address of the PostgreSQL server while enabling monitoring in the next steps, you should ensure to add appropriate entries to enable connections from the


and the


 in the



$ echo "host    all             pmm_user        md5" >> $PGDATA/pg_hba.conf
$ psql -c "select pg_reload_conf()"

In the above step, replace

with the appropriate PostgreSQL Server’s IP address.

Step 4 : 

Validate whether you are able to connect as


to the postgres database from the postgres server itself.

# psql -h -p 5432 -U pmm_user -d postgres
Password for user pmm_user: 
psql (11.5)
Type "help" for help.


Enabling PostgreSQL Monitoring with and without QAN (Query Analytics)

Using PMM, we can monitor several metrics in PostgreSQL such as database connections, locks, checkpoint stats, transactions, temp usage, etc. However, you could additionally enable Query Analytics to look at the query performance and understand the queries that need some tuning. Let us see how we can simply enable PostgreSQL monitoring with and without QAN.

Without QAN

Step 1 :

In order to start monitoring PostgreSQL, we could simply use

pmm-admin add postgresql

. It accepts additional arguments such as the service name and PostgreSQL address and port. As we are talking about enabling monitoring without QAN, we could use the flag:


to disable QAN.

# pmm-admin add postgresql --query-source=none --username=pmm_user --password=secret postgres
PostgreSQL Service added.
Service ID  : /service_id/b2ca71cf-a2a4-48e3-9c5b-6ecd1a596aea
Service name: postgres

Step 2 :

Once you have enabled monitoring, you could validate the same using

pmm-admin list


# pmm-admin list
Service type  Service name         Address and port  Service ID
PostgreSQL    postgres    /service_id/b2ca71cf-a2a4-48e3-9c5b-6ecd1a596aea

Agent type                  Status     Agent ID                                        Service ID
pmm-agent                   connected  /agent_id/13fd2e0a-a01a-4ac2-909a-cae533eba72e  
node_exporter               running    /agent_id/f6ba099c-b7ba-43dd-a3b3-f9d65394976d  
postgres_exporter           running    /agent_id/1d046311-dad7-467e-b024-d2c8cb7f33c2  /service_id/b2ca71cf-a2a4-48e3-9c5b-6ecd1a596aea

You can now access the PostgreSQL Dashboards and see several metrics being monitored.

With QAN

With PMM2, there is an additional step needed to enable QAN. You should create a database with the same name as the monitoring user (


here). And then, you should create the extension:


in that database. This behavior is going to change on the next release so that you can avoid creating the database.

Step 1 : 

Create the database with the same name as the monitoring user. Create the extension:


in the database.

$ psql -c "CREATE DATABASE pmm_user"
$ psql -c -d pmm_user "CREATE EXTENSION pg_stat_statements"

Step 2 : 



has not been set to


, we need to set it and restart PostgreSQL.

$ psql -c "ALTER SYSTEM SET shared_preload_libraries TO 'pg_stat_statements'"
$ pg_ctl -D $PGDATA restart -mf
waiting for server to shut down.... done
server stopped
server started

Step 3 :

In the previous steps, we used the flag:


to disable QAN. In order to enable QAN, you could just remove this flag and use

pmm-admin add postgresql

without the flag.

# pmm-admin add postgresql --username=pmm_user --password=secret postgres
PostgreSQL Service added.
Service ID  : /service_id/24efa8b2-02c2-4a39-8543-d5fd54314f73
Service name: postgres

Step 4 : 

Once the above step is completed, you could validate the same again using

pmm-admin list

. But this time, you should see an additional service:



# pmm-admin list
Service type  Service name         Address and port  Service ID
PostgreSQL    postgres    /service_id/24efa8b2-02c2-4a39-8543-d5fd54314f73

Agent type                  Status     Agent ID                                        Service ID
pmm-agent                   connected  /agent_id/13fd2e0a-a01a-4ac2-909a-cae533eba72e  
node_exporter               running    /agent_id/f6ba099c-b7ba-43dd-a3b3-f9d65394976d  
postgres_exporter           running    /agent_id/7039f7c4-1431-4518-9cbd-880c679513fb  /service_id/24efa8b2-02c2-4a39-8543-d5fd54314f73
qan-postgresql-pgstatements-agent running    /agent_id/7f0c2a30-6710-4191-9373-fec179726422  /service_id/24efa8b2-02c2-4a39-8543-d5fd54314f73

After this step, you can now see the Queries and their statistics captured on the

Query Analytics Dashboard


Meanwhile, have you tried Percona Distribution for PostgreSQL? It is a collection of finely-tested and implemented open source tools and extensions along with PostgreSQL 11, maintained by Percona. PMM works for both Community PostgreSQL and also the Percona Distribution for PostgreSQL. Please subscribe to our blog posts to learn more interesting features in PostgreSQL.


PMM Server + podman: Running a Container Without root Privileges

PMM server and podman

PMM server and podmanIn this article, we will take a look at how to run Percona Monitoring and Management (PMM) Server in a container without root privileges.

Some of the concerns companies have about using Docker relate to the security risks that exist due to the requirement for root privileges in order to run the service and therefore the containers. If processes inside the container are running as root then they are also running as such outside of the container, which means that you should take measures to mitigate privilege escalation issues that could occur from container breakout. Recently, Docker added experimental support for running in rootless mode which requires a number of extra changes.

From a personal perspective, it seems that Docker tries to do it “all in one”, which has its issues. While I was looking to overcome some gripes with Docker, reduce requirements, and also run containers as a chosen user regardless of the process users inside the container, podman and buildah caught my attention, especially as podman also supports Kubernetes. To quote the docs:

Podman is a daemonless container engine for developing, managing, and running OCI Containers on your Linux System. Containers can either be run as root or in rootless mode.

Configuring user namespaces

In order to use containers without the need for root privileges, some initial configuration is likely to be needed to set the mapping of namespaces, as well as a suitable limit. Using the shadow-utils package on CentOS, or uidmap package on Debian, it is possible to assign subordinate user (man subuid) and group IDs (man subgid) to allow the mapping of IDs inside a container to a dedicated set of IDs outside the container. This allows us to resolve the issue where a process is running as root both inside and outside of a container. 

The following is an Ansible task snippet that, on a dedicated CentOS machine, makes the necessary changes, although you may need/want to adjust this if already using namespaces:

- name: install packages
      - runc
      - podman
      - podman-docker
      - buildah
      - shadow-utils
      - slirp4netns
      update_cache: yes
      state: latest
    become: yes
    - packages

- name: set user namespace
     content: '{{ ansible_user }}:100000:65536'
     dest: '/etc/{{ item }}'
   become: yes
     - subuid
     - subgid
   register: set_user_namespace_map
   - namespace

 - name: set sysctl user.max_user_namespaces
     name: user.max_user_namespaces
     value: 100000
     state: present
     sysctl_file: /etc/sysctl.d/01-namespaces.conf
     sysctl_set: yes
     reload: yes
   become: yes
   register: set_user_namespace_sysctl
   - namespace

 - name: reboot to apply
     post_reboot_delay: 60
   become: yes
   when: set_user_namespace_map.changed or set_user_namespace_sysctl.changed
   - namespace

This set of tasks will:

  1. Install some packages that are either required or useful when using podman.
  2. Configure the 2 files used to map IDs (



     ); we allow the user to have 65536 user IDs mapped, starting the mapping from 100000 and likewise for group IDs.

  3. Set

      to ensure that you can allocate sufficient IDs, making it persistent after a reboot. If you have needed to configure the namespaces then it is likely that you need to reboot to make sure that the changes are active.

Look Ma, PMM — and no root!

OK, so if all is well, then the system is configured to support user namespaces and we can run a container without needing to be root (or a member of a special group). The commands for podman are identical, or very close to those that you would use for Docker, and you can even set

alias docker=podman

  if you wish! Here is how you could test PMM Server (v2) out, mapping port 8443 to the NGINX port inside the container:

$ whoami

$ podman run -d --name pmm2-test -p 8443:443

In the previous command, the path to the registry is explicitly stated as being a Docker one, but if you were to simply specify percona/pmm-server:2 then by default a number of registries are checked and the first match will win. The list and order of the registries differ per distro, e.g.

  • Ubuntu:
    registries = ['', '']
  • RedHat:
    registries = ['', '', '']

Checking the processes

The container for PMM server is now running as the user that executed the podman command, so we can take a look and see what the processes look like. First, let’s take a peek inside the container:

$ podman exec pmm-server bash -c "ps -e -o pid,ppid,pgrp,user,comm --forest"
  862     0   862 root     ps
    1     0     1 root     supervisord
    9     1     9 postgres postgres
   88     9    88 postgres  \_ postgres
   98     9    98 postgres  \_ postgres
  100     9   100 postgres  \_ postgres
  101     9   101 postgres  \_ postgres
  102     9   102 postgres  \_ postgres
  103     9   103 postgres  \_ postgres
  104     9   104 postgres  \_ postgres
  154     9   154 postgres  \_ postgres
  173     9   173 postgres  \_ postgres
  183     9   183 postgres  \_ postgres
   10     1    10 root     clickhouse-serv
   11     1    11 grafana  grafana-server
   12     1    12 root     nginx
   23    12    12 nginx     \_ nginx
   13     1    13 root     crond
   14     1    14 pmm      prometheus
   17     1    17 root     pmm-managed
   51    17    17 root      \_ supervisorctl
   18     1    18 root     pmm-agent
  172    18    18 root      \_ node_exporter
  174    18    18 root      \_ postgres_export
  153     1   153 pmm      percona-qan-api

We can see all of the processes that run as part of PMM, plus the ps command that was executed to look at the processlist.

Now, let’s take a look outside the container to see what these processes show up as:

$ pgrep -f 'postgres|supervisord|clickhouse-serv|pmm-managed' | xargs ps -o pid,ppid,pgrp,user,comm --forest
32020 32003 32003 percona  node_exporter
23532 23358 23358 percona  postgres_export
23530 23358 23358 percona  node_exporter
23332 23321 23332 percona  supervisord
23349 23332 23349 100025    \_ postgres
23440 23349 23440 100025    |   \_ postgres
23456 23349 23456 100025    |   \_ postgres
23458 23349 23458 100025    |   \_ postgres
23459 23349 23459 100025    |   \_ postgres
23460 23349 23460 100025    |   \_ postgres
23461 23349 23461 100025    |   \_ postgres
23462 23349 23462 100025    |   \_ postgres
23512 23349 23512 100025    |   \_ postgres
23531 23349 23531 100025    |   \_ postgres
23541 23349 23541 100025    |   \_ postgres
23350 23332 23350 percona   \_ clickhouse-serv
23357 23332 23357 percona   \_ pmm-managed

Great! As we can see from the processlist, none of the processes that we checked are running as root, the ones that are running as root inside the container are running as percona, and the remainder are using unknown user IDs generated by the subordinate mapping.

Using persistent volumes for your data

The documented way to use PMM with persistent volumes does not work in the same manner with podman. However, it is easily fixed as follows (plus we get to try out buildah):

$ cat <<EOS > PMM2.Dockerfile

VOLUMES ['/srv']

$ buildah bud -f PMM2.Dockerfile --tag localhost/pmm-server:custom .
$ podman create --name pmm-data -v /srv localhost/pmm-server:custom 
$ podman run -d --name pmm-server -v pmm-data:/srv -p 8443:443 localhost/pmm-server:custom


It is easy to get PMM server running in a container with podman and buildah while also increasing the security from the outside by restricting the user privileges for the processes.

And while you may not be able to replace Docker usage in certain circumstances, taking a look at podman and buildah is definitely recommended… and you can still use the Docker registry if that is the only location for the image that you want to run.

If you haven’t tried PMM2 yet then you can easily test it out or take a look at the online demo.


Webinar 10/16: What’s New in Percona Monitoring and Management 2?

Percona Monitoring and Management 2

How can you ensure you are properly managing and optimizing the performance of your database environment?

Join Percona’s Product Manager Michael Coburn as he presents “What’s New in Percona Monitoring and Management 2?” and walks you through practical demonstration. This will be taking place on Wednesday, October 16, 2019, at 11:00 AM EDT.

Register Now

Percona Monitoring and Management (PMM) is a free, open source platform that supports MySQL, MariaDB, MongoDB, and PostgreSQL environments, providing detailed time-based analysis of your data. PMM allows you to embrace multiple database options and can be used on-premises and in the cloud.

Our recent major upgrade to PMM2 gives you far greater Query Analytics performance and usability and enables you to monitor much larger environments. Key features of PMM2 include:

•    New performance and usability query improvements.
•    New query analytics for PostgreSQL.
•    New ability to tag queries.
•    New administrative API.
•    New service-level dashboards.
•    Enhanced security protocols to ensure your data is safe.

Michael Coburn, Product Manager, Percona will provide an overview of these new features and a working demonstration of PMM2. You will also have the opportunity to ask questions in the chat window.

If you can’t attend, sign up anyways we’ll send you the slides and recording afterward.


Explore Percona Monitoring and Management on Your Own with Linode

Percona Monitoring Management Linode

Percona Monitoring Management LinodeAs the saying goes, a picture is worth 1000 words – and this is why we don’t just talk about Percona Monitoring and Management (PMM) but also have a demo which allows you to explore many PMM features without having to install it.

Though there is only so much you can do in a public demo, if you want to play with customizing graphs or another functionality which requires read-write access, or otherwise just dive deeper into PMM operation, you may want to get your own instance to do so with.

As we’re releasing Percona Monitoring and Management 2,  we have created a very simple way to do it on Linode using its StackScripts framework.

This StackScript will deploy PMM Server as well as Percona Server for MySQL 8 and kickoff some light query workload using Sysbench-TPCC.

You can deploy this stackscript using linode-cli in the following way:

linode-cli linodes create --type g6-standard-1 --label pmm2demo  --root_pass MySecret  --stackscript_id  587359

If you prefer to do it from the GUI instead, you can go to One-Click Apps Menu, click on “Community Stack Scripts”,  search for PMM, and pick PMM2-MySQL-Demo.


Choose Region, Node Size, and root password, and hit create. It will take a few minutes for it to be fully deployed, and you will have your own PMM Demo to play with.

I would suggest selecting the plan with at least 2GB of memory for best results.


Login with admin:admin credentials and proceed to explore. I would especially suggest you check out the “Node Summary” dashboard.


The MySQL Instance Summary Dashboard


MySQL InnoDB Details


Also, check out that you can see how you can examine your query load by clicking on “Query Analytics”.


Enjoy PMM2, and let us know your feedback on the forums or through JIRA.


Which Indexes are Cached? Discover with PMM.

Which Indexes are Cached

One of the great things about working at Percona is the constant innovation that occurs as a result of a deep level of technical expertise. A more recent conversation about the Information Schema table: innodb_cached_indexes led to the desire to produce this information in an easy to digest and useful format. Enter PMM.

Which Indexes are Cached

Our goal with creating this dashboard was to help bring further insight into how your MySQL database cache is being used. Why is this important? Data is accessed significantly faster when it is cached, so indexes that are cached will allow for an increase in query performance. Until now there has not been an easy way to see which indexes are cached and which are not. We want to take the guesswork out of the equation and present this information quickly in an easy to read format. What other information can we learn from using this dashboard?

  • Does your cache store the indexes you are expecting or is there interesting behavior that needs to be looked into?
  • Where are your largest indexes and which indexes take up the most space?
  • Which indexes are changing over time?
  • Which indexes are not in cache?

The MySQL InnoDB Cached Indexes dashboard displays the top 20 indexes that are memory resident and the sizes of each index. The pie chart displays the same information in a different view but combines all indexes that take up 3% or less of the total size of all memory-resident indexes, in a pie slice labeled Others. The naming convention is schema_name.table_name.index_name. You are able to display any combination of tables and indexes as you see fit. The five statistics displayed at the bottom are the total size of cached indexes on your MySQL instance, the size of the InnoDB buffer pool, the ratio of cached indexes to the size of the InnoDB buffer pool, the size of the selected schema’s cached indexes, and the size of the selected table’s cached indexes.

To install this dashboard you can do one of 2 options:

  1. Download the files here. The has installation instructions.
  2. Download the queries-mysqld.yml file from here. The has installation instructions for queries-mysqld.yml. Instead of importing the .json, paste the ID 10815 for the dashboard ID, and select Load.

Feel free to provide your thoughts and feedback in the comments below.

For more information, check out Custom Queries in PMM by Daniel Guzmán Burgos, one of our Managed Services Technical Leads.

Powered by WordPress | Theme: Aeros 2.0 by