Personal blog of Yzmir Ramirez

Want 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 https://www.percona.com/downloads/pmm2/2.1.0/binary/tarball/pmm2-client-2.1.0.tar.gz

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 172.17.0.2 and the monitored host IP is 172.17.0.1.

$ ./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=172.17.0.2:443 \
--server-username=admin  --server-password="admin" 172.17.0.1 generic node8.ca

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            127.0.0.1:3306    /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:

PMM 1	PMM 2

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 https://repo.percona.com/yum/percona-release-latest.noarch.rpm 
$ 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
...
Installed:
  pmm2-client.x86_64 0:2.1.0-5.el6                                                                                                                                                           

Complete!

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.

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:

Compare this to

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:

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.

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:

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.

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:

Summary

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!

Percona Monitoring and Management (PMM) is a free and open-source platform for managing and monitoring your database performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL®, MariaDB®, MongoDB®, and PostgreSQL® servers to ensure that your data works as efficiently as possible.

Percona Monitoring and Management (PMM) is a free and open-source platform for managing and monitoring MySQL®, MongoDB®, and PostgreSQL® performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL, MongoDB, and PostgreSQL servers to ensure that your data works as efficiently as possible.

Please see our previous blog for full information on the new features included in PMM2, our latest software release.

To use PMM2 you will need to remove any earlier versions of PMM Server and Client as there is no in-place upgrade path for historical data, and then download and run our latest software.

Install PMM Server with docker

The easiest way to install PMM Server is to deploy it with Docker. Running the PMM2 Docker container with PMM Server can be done by the following commands (note the version tag of 2):

docker create -v /srv --name pmm-data percona/pmm-server:2 /bin/true
docker run -d -p 80:80 -p 443:443 --volumes-from pmm-data --name pmm-server --restart always percona/pmm-server:2

Now you can check the newly installed PMM Server in your browser, going to your server by its IP address and using the default credentials:

username: admin

password: admin

You will be prompted to change the admin password after logging in.

Install PMM Client

You can get PMM2 Client package from the official download page on the Percona web site, or use the official percona-release package from Percona to enable the tools repository, as follows (please see percona-release official documentation for further details on this new tool).

Ubuntu/Debian instructions

Install the Percona repository:

wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb

Install the pmm2-client package:

apt-get update
apt-get install pmm2-client

Red Hat/CentOS Instructions

Install the Percona repository:

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

Install the pmm2-client package:

sudo yum update
sudo yum install pmm2-client

Users who have previously installed pmm2-client pre-release versions should fully remove the package and then install the latest available version.

Configure PMM

Once PMM Client is installed, run the pmm-admin config command with your PMM Server IP address in order to register with the PMM Server:

Note that you need to pass the authentication string as part of the --server-url. These credentials are the same ones you used to login to PMM Server.

sudo pmm-admin config --server-insecure-tls --server-url=https://admin:admin@<IP Address>:443

You should see the following:

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.

Adding MySQL Metrics and Query Analytics

MySQL server can be added for the monitoring in its normal way. Here is a command which adds it using the PERFORMANCE_SCHEMA source:

pmm-admin add mysql --query-source=perfschema --username=pmm --password=pmm

where username and password are credentials for the monitored MySQL access, which will be used locally on the database host.

The syntax to add MySQL services (Metrics and Query Analytics) using the Slow Log source is the following:

pmm-admin add mysql --query-source=slowlog --username=pmm --password=pmm

When the server is added, you can check your MySQL dashboards and Query Analytics in order to view its performance information!

Adding MongoDB Metrics and Query Analytics

Please refer to PMM 2 documentation to find out how to set up the required permissions and enable profiling with MongoDB.

You can add MongoDB services (Metrics and Query Analytics) with a similar command:

pmm-admin add mongodb --username=pmm --password=pmm

Adding PostgreSQL Metrics and Query Analytics

Please refer to PMM2 documentation to find out how to add PostgreSQL extension for queries monitoring, as well as set up the required user permissions and authentication.

Add PostgreSQL monitoring to your PMM Server with the following command:

pmm-admin add postgresql --username=pmm --password=pmm

Where username and password parameters should contain actual PostgreSQL user credentials.

Add ProxySQL monitoring service

You can add ProxySQL service as follows:

pmm-admin add proxysql --username=admin --password=admin

Please help us improve our software quality by reporting any Percona Monitoring and Management bugs you encounter using our bug tracking system.

We are pleased to announce the General Availability of PMM2, our latest software release!

Created specifically to support open source software users, Percona Monitoring and Management (PMM) is a leading, free, open-source platform that allows you to actively manage and monitor the performance of your MySQL, MongoDB, and PostgreSQL database environments.

A recent Percona survey revealed that 41% of enterprises are running databases on multiple cloud providers and 61% of enterprises are running a combination of hybrid on-premises and cloud setups. Additionally, 92% of companies surveyed have more than one database in their environment, and 89% run more than one open-source database. Complex, multi-database, hybrid, and multi-cloud environments are a reality for many companies.

PMM provides a single pane of glass to manage your complex database environments in public, private, or on-premise environments. Our award-winning database monitoring tool has been built by database performance and scalability experts using best-of-breed tools. It is specifically designed to help DBAs and developers gain deep insight into their applications and databases and is used by thousands of organizations around the globe.

PMM allows users to do the following:

PMM2 Feature Focus

Query Analytics featuring PostgreSQL

In response to demand, we’ve invested a lot of effort into bringing you an enhanced Query Analytics experience. With Query Analytics you’re able to examine the queries consuming the most amount of time across your entire fleet of servers, and then using filters and labels, drill down to find and isolate problematic queries requiring additional review. We’ve also added several long-awaited features such as:

• Add multiple columns – see more than just load, query response time – want to examine MySQL InnoDB Rows Read, or PostgreSQL rows returned? You can add these columns, and more, to the table layout and see the aggregated values per query.
• Sorting – you can now sort by any column. Want to know which query is reading the most amount of data via MySQL InnoDB Read Operations? Add the column, then sort by it.
• Labeling – benefit from a variety of standard and custom labels in order to better categorize your servers. For example use Cluster or Replication Set to unify servers based on your topology. Or take advantage of the ability to define custom labels, for example, Availability Zone or Rack to define a physical location for your server instances.
• PostgreSQL – In PMM2 you can visualize queries from PostgreSQL alongside your MySQL and MongoDB queries, and isolate them by selecting relevant filter options.

…or the detailed information about the selected query:

Rearranged dashboards and menu structure

PMM2 has the new taxonomy of the dashboards, which are now split up into the following groups:

• Overview Dashboards provides a general view of all objects for the selected type (MySQL, OS, MongoDB, etc.).
• Summary Dashboards are used for a more detailed view of the object.
• Compare Dashboards show objects of the same type placed side-by-side allowing you to spot differences in their parameters, if any.
• Details Dashboards supply you with more object-specific information.

Along with this rearrangement, we have a new simplified main menu. Below is the new look for its top-level:

Selecting a specific service (MySQL, MongoDB, PostgreSQL, etc.) will guide you to the next level of the hierarchy, where you can access a Service Compare Dashboard or one of the Details Dashboards related to this Service:

$ curl -k -u admin:admin -X POST "https://192.168.1.3/v1/inventory/Nodes/List"

{
  "generic": [
    {
      "node_id": "/node_id/c9672fa5-d9b4-4db0-9b40-ea8c2e3c800a",
      "node_name": "osboxes",
      "address": "10.0.2.15",
      "machine_id": "/machine_id/8b5f6f78e736e3e29ba6f1505c797297",
      "distro": "linux"
    },
    {
      "node_id": "pmm-server",
      "node_name": "pmm-server",
      "address": "127.0.0.1"
    }
  ]
}

Read our guide on installing PMM2 to learn more.

Contacting Percona

We welcome any thoughts, comments, and suggestions you have on this latest software version.

Percona offers a range of open source software and services to help your business thrive and grow. Organizations interested in ensuring their databases perform at the highest level can also benefit from Percona Managed Services, Support, and Consulting.

To learn how Percona can help you make the most of your data, please contact us at 1-888-316-9775 or 0-800-051-8984 in Europe, or email sales@percona.com

We are pleased to announce our 7^th Beta release of PMM 2! Percona Monitoring and Management (PMM) is a free and open-source platform for managing and monitoring MySQL, MongoDB, and PostgreSQL performance. This release includes the following improvements:

Query Analytics Improvements

The filter panel now only shows labels that have meaningful select options. Compare the old layout (on the left) with the new one (on the right):

In cases where a field is blank but we know activity is occurring i.e. Client Host is not available from MySQL Performance Schema data source, we use n/a as a placeholder, and we don’t display the option (see example above – Client Host on left, not displayed on the right). In cases where actual values exist as well as n/a, we display all available options.

Also, the details section was also improved to display % from Total for every Metric of the selected query:

More detailed information about PostgreSQL queries

Rearranged dashboards and menu structure

In this release, we introduce a restructured menu with a correspondent change in the dashboards’ taxonomy. Now dashboards belong to one of the following groups:

• Overview Dashboard. This provides a general view of all objects for the selected type (MySQL, OS, MongoDB, etc.). Dashboards of this type provide an easy and fast way to see all available objects and to spot problems.
• Summary Dashboard. This is used for a more detailed view of the object. It provides information about the single object and its main parameters.
• Compare Dashboard. This allows you to compare parameters for the object. Objects of the same type are placed side-by-side to allow you to spot differences.
• Details Dashboard. This contains a more detailed view of some components of the object, thus allowing you to get more in-depth information about the object components. Actually, Details Dashboards are Summary Dashboards for the next level of the hierarchy.

Along with this rearrangement, we have a new main menu. Below is the new look for its top-level:

Selecting a specific service (MySQL, MongoDB, PostgreSQL, etc.) will guide you to the next level of the hierarchy, where you can access Details Dashboards related to this Service, or a Service Compare Dashboard:

Services Compare dashboards

As already noted, we have introduced Services Compare Dashboards for all of the services supported by PMM, with the “Compare” menu item available when a specific service is selected.

The following image shows the dashboard which allows you to compare available PostgreSQL Services:

Services details in MongoDB Cluster Summary dashboard

MongoDB Cluster Summary dashboard has got an update, showing metrics data split by services they are related to in addition to the total metrics data:

Services details in PostgreSQL Services Overview dashboard

PostgreSQL Services Overview dashboard has got an update, showing metrics data split by services they are related to in addition to the total metrics data:

Other dashboard improvements

Several small dashboard improvements were made in this release. The following two improvements are the most noticeable.

The System Overview Dashboard has obtained two new elements describing available service types and database service connections:

The ProxySQL Overview Dashboard now contains a chart with information about client connections:

We’re also incrementally improving our Dashboards with the addition of Tooltips. This release we’ve updated the InnoDB Compression dashboard.

sudo pmm-admin config  --server-insecure-tls --server-url=https://admin:admin@<IP Address>

Installation and configuration

Install PMM Server with docker

The easiest way to install PMM Server is to deploy it with Docker. Running the PMM 2 Docker container with PMM Server can be done by the following commands (note the version tag of 2.0.0-beta7):

docker create -v /srv --name pmm-data-2-0-0-beta7 perconalab/pmm-server:2.0.0-beta7 /bin/true
docker run -d -p 80:80 -p 443:443 --volumes-from pmm-data-2-0-0-beta7 --name pmm-server-2.0.0-beta7 --restart always perconalab/pmm-server:2.0.0-beta7

Now you can check the newly installed PMM Server in your browser, going to your server by its IP address. PMM Server provides user access control, and therefore you will need user credentials to access it:

The default PMM Server credentials are:

username: admin
password: admin

You will be prompted to change the admin password after logging in.

Install PMM Client

Since PMM 2 is still not GA, you’ll need to leverage our experimental release of the Percona repository. You’ll need to download and install the official percona-release package from Percona and use it to enable the Percona experimental component of the original repository. See percona-release official documentation for further details on this new tool.

Specific instructions for a Debian system are as follows:

wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb

Now enable the experimental repo:

sudo percona-release disable all
sudo percona-release enable original experimental

Install pmm2-client package:

apt-get update
apt-get install pmm2-client

Users who have previously installed pmm2-client alpha version should remove the package and install a new one in order to update to beta1.

Please note that leaving experimental repository enabled may affect further package installation operations with bleeding-edge software that may not be suitable for Production. You can revert by disabling experimental via the following commands:

sudo percona-release disable original experimental
sudo apt-get update

Configure Percona Monitoring and Management

Once PMM Client is installed, run the pmm-admin config command with your PMM Server IP address to register your Node:

# pmm-admin config --server-insecure-tls --server-url=https://admin:admin@<IP Address>

You should see the following:

Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/local/percona/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.

Adding MySQL Metrics and Query Analytics

MySQL server can be added for monitoring in its normal way. Here is a command which adds it using the PERFORMANCE_SCHEMA source:

pmm-admin add mysql --query-source='perfschema' --username=pmm --password=pmm

where username and password are credentials for the monitored MySQL access, which will be used locally on the database host.

The syntax to add MySQL services (Metrics and Query Analytics) using the Slow Log source is the following:

pmm-admin add mysql --query-source='slowlog' --username=pmm --password=pmm

When the server is added, you can check your MySQL dashboards and Query Analytics in order to view its performance information!

Adding MongoDB Metrics and Query Analytics

You can add MongoDB services (Metrics and Query Analytics) with a similar command:

pmm-admin add mongodb --use-profiler --username=pmm --password=pmm

Adding PostgreSQL Metrics and Query Analytics

You can add PostgreSQL service as follows:

pmm-admin add postgresql --username=pmm --password=pmm

You can then check your PostgreSQL Overview dashboard.

Add ProxySQL monitoring service

You can add ProxySQL service as follows:

pmm-admin add proxysql --username=admin --password=admin

You can then check your ProxySQL Overview dashboard.

About Percona Monitoring and Management

Percona Monitoring and Management (PMM) is a free and open-source platform for managing and monitoring MySQL®, MongoDB®, and PostgreSQL® performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL, MongoDB, and PostgreSQL servers to ensure that your data works as efficiently as possible.

Help us improve our software quality by reporting any Percona Monitoring and Management bugs you encounter using our bug tracking system.

As a Solutions Engineer at Percona, one of my responsibilities is to support our customer-facing roles such as the sales and customer success teams, which affords me the opportunity to speak to many current and new customers who partner with Percona. I often find that many people are interested in Percona Monitoring and Management (PMM) as a free and open-source monitoring solution due to its robust monitoring capabilities when compared to many SaaS-based monitoring solutions. They are interested in installing PMM for MongoDB for the first time and want a “quick start guide” with a brief overview to get their feet wet. I have included the commands to get started for both PMM 1 and PMM 2 (PMM2 is still in beta).

Overview and Architecture

PMM is an open-source platform for out-of-the-box management and monitoring of MySQL, MongoDB, and PostgreSQL performance, on-premise and in the cloud. It is developed by Percona in collaboration with experts in the field of managed database services, support, and consulting. PMM is built off of Prometheus, a powerful open-source monitoring and alerting platform, and supports any other service that has an exporter. An exporter is an endpoint that collects data on the instance being monitored and is polled by Prometheus to collect metrics. For more information on how to use your own exporters, read the documentation here.

When deployed on-premises, the PMM platform is based on a client-server model that enables scalability. It includes the following modules:

• PMM Client– installed on every database host that you want to monitor. It collects server metrics, general system metrics, and Query Analytics data for a complete performance overview.
• PMM Server – the central part of PMM that aggregates collected data and presents it in the form of tables, dashboards, and graphs in a web interface.

PMM can also be deployed to support DBaaS instances for remoting monitoring. Instructions can be found here, under the Advanced section. The drawback of this approach is that you will not have visibility of host-level metrics (CPU, memory, and disk activity will not be captured nor displayed in PMM). There are currently 3 different deployment options:

• Running PMM Server via Docker
• Running PMM Server as a Virtual Appliance
• Running PMM Server Using AWS Marketplace

For a more detailed overview of the PMM Architecture please read the Overview of PMM Architecture.

Demonstration Environment

When deploying PMM in this example, I am making the following assumptions about the environment:

• MongoDB and the monitoring host are running on Debian based operating systems. (For information on installing as an RPM instead please read Deploying Percona Monitoring and Management.)
• MongoDB is already installed and setup. The username and password for the MongoDB user are percona:percona.
• The PMM server will be installed within a docker container on a dedicated host.

Installing PMM Server

This process will consist of two steps:

1. Create the docker container – docker will automatically pull the PMM Server image from the Percona docker repository.
2. Start (or run) the docker container – docker will bring up the PMM Server in the container

Create the Docker Container

The code below illustrates the command for creating the docker container for PMM 1:

docker create \
  -v /opt/prometheus/data \
  -v /opt/consul-data \
  -v /var/lib/mysql \
  -v /var/lib/grafana \
  --name pmm-data \
  percona/pmm-server:1 /bin/true

The code below illustrates the command for creating the docker container for PMM 2:

docker create -v /srv --name pmm-data-2-0-0-beta1 perconalab/pmm-server:2.0.0-beta1 /bin/true

This is the expected output from the code:

Use the following command to start the PMM 1 docker container:

docker run -d \
   -p 80:80 \
   --volumes-from pmm-data \
   --name pmm-server \
   --restart always \
   percona/pmm-server:1

Use the following command to start the PMM 2 docker container:

docker run -d -p 80:80 -p 443:443 --volumes-from pmm-data-2-0-0-beta1 --name pmm-server-2.0.0-beta1 --restart always perconalab/pmm-server:2.0.0-beta1

This is the expected output from the code:

The PMM Server should now be installed! Yes, it IS that easy. In order to check that you can access PMM, navigate in a browser to the IP address of the monitoring host. If you are using PMM 2, the default username and password for viewing PMM is admin:admin. You should arrive at a page that looks like https://pmmdemo.percona.com.

Installing PMM Client for MongoDB

Setting up DB permissions

PMM Query Analytics for MongoDB requires the user of the mongodb_exporter to have the clusterMonitor role assigned for the admin database and the read role for the local database. If you do not have these set up already, please read Configuring MongoDB for Monitoring in PMM Query Analytics.

Download the Percona repo package

We must first enable the Percona package repository on our MongoDB instance and install the PMM Client. We can run the following commands in order to accomplish this:

$ wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
$ sudo dpkg -i percona-release_latest.generic_all.deb
$ sudo apt-get update

Since PMM 2 is still not GA, you’ll need to leverage our experimental release of the Percona repository. You’ll need to download and install the official percona-release package from Percona and use it to enable the Percona experimental component of the original repository. See percona-release official documentation for further details on this new tool. The following commands can be used for PMM 2:

$ wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
$ sudo dpkg -i percona-release_latest.generic_all.deb
$ sudo percona-release disable all
$ sudo percona-release enable original experimental
$ sudo apt-get update

Now that we have the MongoDB database server configured with the Percona software repository, we can download the agent software with the local package manager.  Enter the following command to automatically download and install the PMM Client package on the MongoDB server:

$ sudo apt-get install pmm-client

To download and install the PMM 2 Client:

$ apt-get install pmm2-client

Next, we will configure the PMM client by telling it where to find the PMM server.  Execute the following command to configure the PMM client:

$ sudo pmm-admin config --server=<pmm_server_ip>:80

To configure the PMM 2 Client:

$ pmm-admin config --server-insecure-tls --server-url=https://<pmm_server_ip>:443

You should get a similar output as below if it was successful:

Now we provide the PMM Client credentials necessary for monitoring the MongoDB database.  Execute the following command to start monitoring and communicating with the PMM server:

$ sudo pmm-admin add mongodb --uri mongodb://percona:percona@127.0.0.1:27017

To start monitoring and communicating with the PMM 2 Server:

$ sudo pmm-admin add mongodb --use-profiler  --server-insecure-tls --username=percona  --password=percona --server-url=https://<pmm_ip>:443

You should get a similar output as below if it was successful:

Great! We have successfully installed PMM for MongoDB and are ready to take a look at the dashboards.

PMM for MongoDB Dashboards Overview

Navigate to the IP address of your monitoring host. http://<pmm_server_ip>.

PMM Home Dashboard – The Home Dashboard for PMM gives an overview of your entire environment to include all the systems you have connected and configured for monitoring under PMM. It provides useful metrics such as CPU utilization, RAM availability, database connections, and uptime.

Cluster Summary – it shows the statistics for the selected MongoDB cluster such as counts of sharded and un-sharded databases, shard and chunk statistics, and various mongos statistics.

MongoDB Overview – this provides basic information about MongoDB instances such as connections, command operations, and document operations.

ReplSet – provides information about replica sets and their members such as replication operations, replication lag, and member state uptime.

WiredTiger/MMAPv1/In-Memory/RocksDB – it contains metrics that describe the performance of the selected host storage engine.

Query Analytics – this allows you to analyze database queries over periods of time. This can help you optimize database performance by ensuring queries are executed as expected and within the shortest amount of time. If you are having performance issues, this is a great place to see which queries may be the cause of your performance issues and get detailed metrics for them.

What Now?

Now that you have PMM for MongoDB up and running, I encourage you to explore in-depth more of the graphs and features. A few other MongoDB PMM blog posts which may be of interest:

• PMM for MongoDB: Basic Graphs for a Good System Overview
• PMM Query Analytics Support for MongoDB
• PMM Graphs Explained: MongoDB MMAPv1
• PMM Graphs Explained: WiredTiger and Percona Memory Engine
• PMM Graphs Explained: MongoDB with RocksDS
• MongoDB Explain – Using PMM-QAN for MongoDB Query Analytics

If you run into issues during the install process, a good place to start is Peter Zaitsev’s blog post on PMM Troubleshooting.

We are pleased to announce our 4^th Beta release of PMM 2! PMM (Percona Monitoring and Management) is a free and open-source platform for managing and monitoring MySQL, MongoDB, and PostgreSQL performance. With this release we’ve made the following improvements since our last public Beta at the end of May:

• Query Analytics
  • PostgreSQL – Aggregate & identify slow queries from pg_stat_statements data source
  • Interface updates – label improvements, sparkline updates, tooltips
• PMM Server Monitoring  – look for pmm-server in Dashboards and Query Analytics
• ProxySQL monitoring – now available using pmm-admin add proxysql
• Environment Overview Dashboard – Updated layout and colours – take a look!
• Debian 10 support we now have pmm2-client deb packages for Debian 10

Query Analytics Dashboard Enhancements

Query Analytics for PostgreSQL

We’re excited to provide Query Analytics for PostgreSQL in this release, where you can now visualize query activity for PostgreSQL. Monitoring PostgreSQL queries is achieved via the popular pg_stat_statements extension.

Query Analytics Interface Updates

We spent some time updating Sparklines logic to be more accurate:

We now wrap long labels, and shorten in some cases with a hint to show the full label name:

ProxySQL support

You can now add ProxySQL instance to PMM and take advantage of the ProxySQL Overview Dashboard, which resides under the HA group in the system menu.

We’ve categorized the dashboard into several sections. The first two sections show total information for the entire environment as well as Top and Min values:

We also display the label name and current value, and you can click each object in order to drill down for greater detail:

We’ve started collapsing some rows by default, in order to minimize the visual clutter. Opening each category automatically refreshes the dashboard:

Installation and configuration

The default PMM Server credentials are:

username: admin
password: admin

Install PMM Server with docker

The easiest way to install PMM Server is to deploy it with Docker. Running the PMM 2 Docker container with PMM Server can be done by the following commands (note the version tag of 2.0.0-beta4):

docker create -v /srv --name pmm-data-2-0-0-beta4 perconalab/pmm-server:2.0.0-beta4 /bin/true
docker run -d -p 80:80 -p 443:443 --volumes-from pmm-data-2-0-0-beta4 --name pmm-server-2.0.0-beta4 --restart always perconalab/pmm-server:2.0.0-beta4

Install PMM Client

Since PMM 2 is still not GA, you’ll need to leverage our experimental release of the Percona repository. You’ll need to download and install the official percona-release package from Percona and use it to enable the Percona experimental component of the original repository. See percona-release official documentation for further details on this new tool.

Specific instructions for a Debian system are as follows:

wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb

Now enable the experimental repo:

sudo percona-release disable all
sudo percona-release enable original experimental

Install pmm2-client package:

apt-get update
apt-get install pmm2-client

Users who have previously installed pmm2-client alpha version should remove the package and install a new one in order to update to beta1.

Please note that leaving experimental repository enabled may affect further package installation operations with bleeding-edge software that may not be suitable for Production. You can revert by disabling experimental via the following commands:

sudo percona-release disable original experimental
sudo apt-get update

Configure PMM

Once PMM Client is installed, run the pmm-admin config command with your PMM Server IP address to register your Node:

# pmm-admin config --server-insecure-tls --server-url=https://<IP Address>:443

You should see the following:

Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/local/percona/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.

Adding MySQL Metrics and Query Analytics

MySQL server can be added for the monitoring in its normal way. Here is a command which adds it using the PERFORMANCE_SCHEMA source:

pmm-admin add mysql --query-source='perfschema' --username=pmm --password=pmm

where username and password are credentials for the monitored MySQL access, which will be used locally on the database host.

The syntax to add MySQL services (Metrics and Query Analytics) using the Slow Log source is the following:

pmm-admin add mysql --query-source='slowlog' --username=pmm --password=pmm

When the server is added, you can check your MySQL dashboards and Query Analytics in order to view its performance information!

Adding MongoDB Metrics and Query Analytics

You can add MongoDB services (Metrics and Query Analytics) with a similar command:

pmm-admin add mongodb --use-profiler --username=pmm --password=pmm

Adding PostgreSQL monitoring service

You can add PostgreSQL service as follows:

pmm-admin add postgresql --username=pmm --password=pmm

You can then check your PostgreSQL Overview dashboard.

Add ProxySQL monitoring service

You can add ProxySQL service as follows:

pmm-admin add proxysql --username=admin --password=admin

You can then check your ProxySQL Overview dashboard.

About Percona Monitoring and Management

Percona Monitoring and Management (PMM) is a free and open-source platform for managing and monitoring MySQL®, MongoDB®, and PostgreSQL® performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL®, MongoDB®, and PostgreSQL® servers to ensure that your data works as efficiently as possible.

Help us improve our software quality by reporting any Percona Monitoring and Management bugs you encounter using our bug tracking system.

Google Summer of Code (GSoC) is a program that focuses on bringing young talented students from universities into open source projects. This year, Percona became a mentor organization, so two student interns will join us as GSoC interns. They’ll be working with us over the next four months, and we’re delighted to welcome them to the Percona team.

Preparations

As this is our first year of participation, the Percona Monitoring and Management (PMM) team began preparing our application for GSoC in December, 2018. We defined the list of projects that would help us improve PMM and introduce new features provided by the GSoC interns. With help from our HR and Marketing teams, we prepared a landing page on the Percona website with the relevant information about each project, along with information about proposal templates, and a mailing list for potentially interested students.

Details about the students and projects can also be found on Percona’s GSoC organization page.

The students and their projects

The two students who will be working with us are Kartikeya Gokhale and Mrinal Dutta, who are both from the same university, Dhirubhai Ambani Institute of Information and Communication Technology (DAIICT) in Gandhinagar, India.

Kartikeya Gokhale

Kartikeya is a computer enthusiast, who enjoys working on amazing technologies with interesting teams and people. He has worked as a full-stack developer at Quze (www.quze.co), an ed-tech startup aiming to revolutionize the field of education.

Kartikeya’s project is to enhance and improve the UI test helper for PMM. codeceptjs-resemblehelper is a UI testing helper which will be used by the PMM team to verify data consistency in metric plots and provide image comparison.

The Percona mentors for this project are Puneet Kala, Frontend/Web QA Automation Engineer, Nailya Kutlubaeva, QA Engineer, and Roma Novikov, Director of Platform Engineering.

Mrinal Dutta

A final year student of the university’s MSc-IT cohort, this project represents Mrinal’s first contribution towards open source. Mrinal says he has a strong drive to have an impact on anything and everything he participates in, whether that be work or extra-curricular activities such as sport and other pursuits. Mrinal is looking forward to have a great summer with Percona and GSoC Program.

Mrinal’s project is the refactoring of our Grafana dashboards. The aim of the project is to convert the existing dashboards’ static JSON files to Jsonnet files. This will help us to dynamically generate JSON for PMM according to needs.

The Percona mentors for Mrinal’s project are Vadim Yalovets,  Software Engineer, and Roma Novikov.

Summing up

The GSoC team at Percona is thankful to everyone involved in this year’s application and selection process. We are excited to have a team of mentors helping students learn about our products and working in open source. We’re looking forward to enjoying the two-way dialogue and guiding the students to hone their skills as they experience working on these valuable PMM developments.

If you have any questions about GSoC Program please feel free to write to us on gsoc@percona.com

We are pleased to announce the release of PMM 2 Beta!  PMM (Percona Monitoring and Management) is a free and open-source platform for managing and monitoring MySQL, MongoDB, and PostgreSQL performance.

• Query Analytics:
  • MySQL and MongoDB – Slow log, PERFORMANCE_SCHEMA, and Profiler data sources
  • Support for large environments – default view all queries from all instances
  • Filtering – display only the results matching filters such as the schema name or the server instance
  • Sorting and more columns – now sort by any column.
  • Modify Columns – Add one or more columns for any field exposed by the data source
  • Sparklines –  restyled sparkline targeted at making data representation more accurate
• Labels – Prometheus now supports auto-discovered and custom labels
• Inventory Overview Dashboard – Displays the agents, services, and nodes which are registered with PMM Server
• Environment Overview Dashboard – See issues at a glance across multiple servers
• API – View versions and list hosts using the API
• MySQL, MongoDB, and PostgreSQL Metrics – Visualize database metrics over time
• pmm-agent – Provides secure remote management of the exporter processes and data collectors on the client

Query Analytics Dashboard

Query Analytics Dashboard now defaults to display all queries on each of the systems that are configured for MySQL PERFORMANCE_SCHEMA, Slow Log, and MongoDB Profiler, and includes comprehensive filtering capabilities.

Query Analytics Overview

You’ll recognize some of the common elements in PMM 2 Query Analytics such as the Load, Count, and Latency columns. However, there are new elements such as the filter box and more arrows on the columns:

Query Detail

Query Analytics continues to deliver detailed information regarding individual query performance

Filter and Search By

There is a filtering panel on the left, or use the search by bar to set filters using key:value syntax. For example, I’m interested in just the queries related to mysql-sl2 server, I could then type d_server:mysql-sl2:

Sort by any column

This is a much-requested feature from PMM Query Analytics and we’re glad to announce that you can now sort by any column! Just click the small arrow to the right of the column name and:

Sparklines

As you may have already noticed, we have changed the sparkline representation. New sparklines are not points-based lines, but are interval-based, and look like a staircase line with flat values for each of the displayed period:

We also position a single sparkline for only the left-most column and render numeric values for all remaining columns.

Add extra columns

Now you can add a column for each additional field which is exposed by the data source. For example, you can add Rows Examined by clicking the + sign and typing or selecting from the available list of fields:

MySQL Query Analytics Slow Log source

We’ve increased our MySQL support to include both PERFORMANCE_SCHEMA and Slow log – and if you’re using Percona Server with the Extended Slow Log format, you’ll be able to gain deep insight into the performance of individual queries, for example, InnoDB behavior.  Note the difference between the detail available from PERFORMANCE_SCHEMA vs Slow Log:

PERFORMANCE_SCHEMA:

Slow Log:

MongoDB Metrics

Support for MongoDB Metrics included in this release means you can add a local or remote MongoDB instance to PMM 2 and take advantage of the following view of MongoDB performance:

PostgreSQL Metrics

In this release, we’re also including support for PostgreSQL Metrics. We’re launching PMM 2 Beta with just the PostgreSQL Overview dashboard, but we have others under development, so watch for new Dashboards to appear in subsequent releases!

Environment Overview Dashboard

This new dashboard provides a bird’s-eye view, showing a large number of hosts at once. It allows you to easily figure out the hosts which have issues, and move onto other dashboards for a deeper investigation.

The charts presented show the top five hosts by different parameters:

The eye-catching colored hexagons with statistical data show the current values of parameters and allow you to drill-down to a dashboard which has further details on a specific host.

API

We are exposing an API for PMM Server! You can view versions, list hosts, and more…

The API is not guaranteed to work until GA release – so be prepared for some errors during Beta release.

Browse the API using Swagger at /swagger

Installation and configuration

The default PMM Server credentials are:

username: admin
password: admin

Install PMM Server with docker

The easiest way to install PMM Server is to deploy it with Docker. Running the PMM 2 Docker container with PMM Server can be done by the following commands (note the version tag of 2.0.0-beta1):

docker create -v /srv --name pmm-data-2-0-0-beta1 perconalab/pmm-server:2.0.0-beta1 /bin/true
docker run -d -p 80:80 -p 443:443 --volumes-from pmm-data-2-0-0-beta1 --name pmm-server-2.0.0-beta1 --restart always perconalab/pmm-server:2.0.0-beta1

Install PMM Client

Since PMM 2 is still not GA, you’ll need to leverage our experimental release of the Percona repository. You’ll need to download and install the official percona-release package from Percona, and use it to enable the Percona experimental component of the original repository. See percona-release official documentation for further details on this new tool.

Specific instructions for a Debian system are as follows:

wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb

Now enable the experimental repo:

sudo percona-release disable all
sudo percona-release enable original experimental

Install pmm2-client package:

apt-get update
apt-get install pmm2-client

Users who have previously installed pmm2-client alpha version should remove the package and install a new one in order to update to beta1.

Please note that leaving experimental repository enabled may affect further package installation operations with bleeding edge software that may not be suitable for Production. You can revert by disabling experimental via the following commands:

sudo percona-release disable original experimental
sudo apt-get update

Configure PMM

Once PMM Client is installed, run the pmm-admin config command with your PMM Server IP address to register your Node:

# pmm-admin config --server-insecure-tls --server-address=<IP Address>:443

You should see the following:

Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/local/percona/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.

Adding MySQL Metrics and Query Analytics

MySQL server can be added for the monitoring in its normal way. Here is a command which adds it using the PERFORMANCE_SCHEMA source:

sudo pmm-admin add mysql --use-perfschema --username=pmm --password=pmm

where username and password are credentials for accessing MySQL.

The syntax to add MySQL services (Metrics and Query Analytics) using the Slow Log source is the following:

sudo pmm-admin add mysql --use-slowlog --username=pmm --password=pmm

When the server is added, you can check your MySQL dashboards and Query Analytics in order to view its performance information!

pmm-admin add mongodb --use-profiler --use-exporter  --username=pmm  --password=pmm

pmm-admin add postgresql --username=pmm --password=pmm

About PMM

Percona Monitoring and Management (PMM) is a free and open-source platform for managing and monitoring MySQL®, MongoDB®, and PostgreSQL® performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL®, MongoDB®, and PostgreSQL® servers to ensure that your data works as efficiently as possible.

Help us improve our software quality by reporting any Percona Monitoring and Management bugs you encounter using our bug tracking system.