On the 15th of April, Percona introduced the general availability of Percona Platform, and with that step, our company started a new, interesting period.

Popular questions we have received after this date include: 

• What is Percona Platform, and how does it differ from Pecona Account? 
• What is the difference between Percona Platform and Percona Portal? 
• Why should Percona users connect to Percona Platform?

In this blog post, I will answer these questions and provide a summary of the benefits of a Percona Account. 

What is Percona Platform, and how does it differ from Pecona Account? 

First, let’s make sure we understand the following concepts:

Percona Platform – A unified experience for developers and database administrators to monitor, manage, secure, and optimize database environments on any infrastructure. It includes open source software and tools, access to Percona Expert support, automated insights, and self-service knowledge base articles.

Percona Account – A single login provides users with seamless access across all Percona properties, as well as insights into your database environment, knowledge base, support tickets, and guidance from Percona Experts. By creating a Percona Account, you get access to Percona resources like Forums, Percona Portal, ServiceNow (for current customers), and, in the future, other Percona resources (www.percona.com, Jira, etc.).

Percona Portal – One location for your Percona relationship, with a high-level overview of your subscription and key resources like the knowledge base, support tickets, and ability to access your account team from anywhere.

Next, let’s try to understand the complete flow users will have to go through when they decide to start using the Percona Platform. A Percona Account is foundational to Percona Platform. Users can create their own account here on Percona Portal. There are two options for Percona Account creation, either via an email and password or by using a social login from Google or Github.  

What is the difference between Percona Platform and Percona Portal? 

From the description above Percona Platform is a great experience in open source software, services, tools, and resources Percona is offering to all users. Percona Portal is just a part of Percona Platform and could be thought of as the core component of Percona Platform to discover the value Percona is offering to registered users.

Why should Percona users connect to Percona Platform?

Percona Platform brings together enterprise-ready distributions of MySQL, PostgreSQL, and MongoDB and a range of open source tools for database monitoring, backup, and management, making it easier to run complex database environments.

Percona Platform consists of several essential units: 

• Percona Account, 
• Percona Monitoring & Management, which can assure alerting, advisor insight for users’ environments, backups, private DBaaS, etc.
• Percona Portal, 
• Support and services.

Percona Monitoring and Management, DBaaS 

The heart of Percona Platform is Percona Monitoring and Management (PMM), which provides the observability required to understand database health while offering actionable insights to remediate database incidents or performance issues. 

PMM has already won the hearts of millions of DBAs around the world with its Query Analytics (QAN) and Advisors and now with Percona Platform release we made it even cooler! 

The new technical preview feature that I’m talking about is Private DBaaS. 

With less than 20 minutes to configure, Percona Private DBaaS enables you to provide self-service databases to your internal teams in any environment. 

Percona Private DBaaS enables Developers to create and manage database clusters through familiar UI and well-documented API, providing completely open source and ready-to-use clusters with MySQL or MongoDB that contain all necessary Kubernetes settings. Each cluster is set up with high availability, load balancing, and essential monitoring and management.

Private DBaaS feature of PMM is free from vendor lock-in and does not enforce any cloud or on-prem infrastructure usage restrictions.

Learn more about DBaaS functionality in our Documentation

Percona Portal 

Percona Portal is a simple UI to check and validate your relationship with Percona software and services. Stay tuned for updates. 

To start getting the value from Percona Portal, just create an organization in the Portal and connect Percona Monitoring & Management using a Percona account. Optionally, you can also invite other members to this group in order to share a common view of your organization, any PMM connection details, a list of current organization members, etc. 

When existing Percona customers create their accounts with corporate email, Percona Portal already knows about their organization. They are automatically added as members and can see additional details about their account, like opened support tickets, entitlements, and contact details.

Percona Account and SSO

Percona Platform is a combination of open source software and services. You can start discovering software advantages right from installing PMM and start monitoring and managing your environments. You could ask yourself what role of the Platform connection is there? Let’s briefly clarify this.

SSO is the first advantage you should see upon connecting your PMM instance to Percona Platform. All users in the organization are able to sign in to PMM using their Percona Account credentials. They also will be granted the same roles in PMM and Portal.  For example, if a user is an admin on Percona Portal, they do not need to create a new user in PMM, but will instead be automatically added as an admin in PMM. Portal Technical users will be granted a Viewer role in PMM.

Let’s assume a Percona Platform user has already created an account, has his own organization with a couple of members in it, and has also connected PMM to the Platform with a Percona Account. Then, he is also able to use SSO for the current organization. 

Percona Advisors

Advisors provide automated insights and recommendations within Percona Monitoring and Management, ensuring your database performs at its best. Constantly evolving with technology and user feedback, the Advisors check for:

• Availability at risk,
• Replication inconsistencies,
• Durability at risk,
• Passwordless users,
• Unsecure connections,
• Unstable OS configuration,
• Available performance improvements and more.

 Advisors workflow consists of the following:

1. If the user has just started PMM and set up monitoring of some databases and environments, we provide him with a set of basic Advisors.  To check the list the user should go to the Advisors section in PMM.
   
   
2. After the user connects PMM and Platform on Percona Portal, this list gets bigger and the set of advisors is updated. 
3. The final step happens with a Percona Platform subscription. If a user subscribes to Percona Platform, we provide the most advanced list of Advisors, which cover a lot of edge cases and check database environments.

See the full list of Advisors and various tiers in our documentation here. 

As an open source company, we are always eager to engage the community and invite them to innovate with us. 

Here you can find the developer guides on how to create your own Advisors.

How to register a Percona Account?

There are several options to create a Percona account. The first way is to specify your basic data – email, last name, first name, and password.

1. Go to “Don’t have an account? Create one” on the landing page for Percona Portal. 
2. Enter a minimal set of information (work email, password, first and last name), confirm that you agree with Percona’s Terms of Service and the Privacy Policy agreement, and hit Create.
3. To complete your registration, check your email and follow the steps provided there. Click the confirmation link in order to activate your account. You may see messages coming from Okta, this is because Percona Platform uses Okta as an identity provider.
4. After you have confirmed your account creation, you can log in to Percona Portal.

You can also register a Percona Account if you already have a Google or Github account created.

When you create a Percona account with Google and Github, Percona will store only your email and authenticate you using data from the given identity providers.

1. Make sure you have an account on Google/Github.
2. On the login page, there are dedicated buttons that will instruct you to confirm account information usage by Percona.
3. Confirm you are signing in to your account if 2FA for your Google is enabled.
4. Hurray! Now you have an active account on Percona Portal.

The next option to register a Percona Account is to Continue with Github. The process is similar to Google. 

1.  An important precondition has to be met prior to account usage and registration: Set your email address in Github to Public.
2. From Settings, go to the Emails section and uncheck the Keep my email address private option. These settings will be saved automatically. If you do not want to change this option in Github, so you could use other accounts like Google, or create your own with email.
3. If you already registered an account, you are ready to go with Percona Portal. Use your Google/Github credentials every time you log in to your account.

Conclusions

Start your Percona Platform experience with Percona Monitoring & Management – open source database monitoring, management, and observability solution for MySQL, PostgreSQL, and MongoDB.

Set up Percona Monitoring and Management (version >2.27.0) following PMM Documentation. 

The next step is to start exploring Percona Platform and get more value and experience from Percona database experts by creating a Percona Account on Percona Portal. The key benefits of Percona  Platform include: 

• A Percona Account as a single authentication mechanism to use across all Percona resources, 
• Access to Percona content like blogs and forums, knowledge base.
• Percona Advisors that help optimize, manage and monitor database environments.

Once you create a Percona account, you will get basic advisors, open source software, and access to community support and forums.

When connecting Percona Monitoring and Management to Percona Account you will get a more advanced set of advisors for your systems security, configuration, performance, and data design.

Percona Platform subscription is offering even more advanced advisors for Percona customers and also a fully supported software experience, private DBaaS, and an assigned Percona Expert to ensure your success with the Platform. 

Please also consider sharing your feedback and experience with Percona on our Forums.

Visit Percona Platform online help to view Platform-related documentation, and follow our updates on the Platform what’s new page.

Just the other day, one of my Percona Monitoring and Management (PMM) instances failed to update and ended up in a funny state:

 

No matter how many times I clicked the “check for updates” button in the lower right, PMM still showed “no updates available”. Basically, PMM forgot what version it is and because of that it thinks there are no updates available

If you run into this (or possibly other PMM  update problems), you may find the following helpful to troubleshoot or resolve the problem. 

I have deployed PMM through docker, so the first step is to get inside the docker container to be able to investigate and troubleshoot the installation.

docker exec -it pmm-server bash

Percona Monitoring and Management (as of version 2.26) learns its own version from the version of “pmm-update” package.  In my case, this did not work due to a corrupted rpm database:

[root@812fb43b3a96 opt]# yum info pmm-update
error: rpmdb: BDB0113 Thread/process 13841/140435865503552 failed: BDB1507 Thread died in Berkeley DB library
error: db5 error(-30973) from dbenv->failchk: BDB0087 DB_RUNRECOVERY: Fatal error, run database recovery
error: cannot open Packages index using db5 -  (-30973)
error: cannot open Packages database in /var/lib/rpm
CRITICAL:yum.main:

Error: rpmdb open failed

It is not clear to me why the database has become corrupted in my case, but unfortunately, it did and needed to be repaired:

mv /var/lib/rpm/__db* /tmp/
rpm --rebuilddb
rm -rf /var/cache/yum

These commands backup the current database to /tmp/ (just in case)  to rebuild the current database as well as remove already downloaded packages (it has nothing to do with database corruption but reduces reliance on the local state).

# supervisorctl start pmm-update-perform
pmm-update-perform: started

This command starts the update process.  You probably could also trigger an update through the Web GUI at this point but I want you to be aware of this option, too.

As this simply starts the background update process you do not get much information about what is going on. To find that out, you need to check the log:

tail /srv/logs/pmm-update-perform.log

Note while the update is going, the PMM instance may not be available at all. You could see this message:

Do not freak out – wait for the update to finish before trying to use PMM again. 

The update log does not really have a very clear message which states what update was completed successfully.  Here is, however, an example of how the end of a log from a successful update may look:

PLAY RECAP *********************************************************************
localhost                  : ok=79   changed=31   unreachable=0    failed=0    skipped=16   rescued=0    ignored=0

time="2022-04-01T15:36:39Z" level=info msg="Waiting for Grafana dashboards update to finish..."

With that, the update has been completed and my PMM instance has successfully updated to the recent version.

I hope this blog post will help you to troubleshoot your PMM update problems too if you happen to run into one!

Percona Monitoring and Management (PMM) is a multi-faceted tool that includes the Security Threat Tool which provides the ability to check your databases for potential configuration or performance problems.  And boy was I shocked to find I had problems when I installed PMM for testing.

The complete list of checks that PMM runs daily can be found at https://docs.percona.com/percona-platform/checks.html and they range from unsecured log file permissions to low cache rates, depending on the database.  PMM checks MySQL, MongoDB, and PostgreSQL instances. These checks are categorized as critical, major, or trivial depending on their respective impact and you can silence them if the issue is chronic but has been decided as something that can be tolerated. 

I installed PMM and Percona Distribution for MySQL on a test server and enabled PMM security.  On the home screen, the alert menu was instantly displayed.  

Yup, my test system had problems!  I clicked on the above section of the PMM home page fearful of what horrors awaited me.  

There were no ‘critical’ problems but there were two ‘major’ and three ‘trivial’ issues. The first of the ‘major’ problems was that the master_verify_checksum is disabled.  The master_verify_checksum variable is used in replication. Since this was a test machine and not part of any replication topology, there really is not a need to have a replication source verify events read from the binary log by examining checksums, stopping in the case of a mismatch. BTW master_verify_checksum is disabled by default.

The last ‘major’ issue is that the binary log files are rotated too quickly and PMM suggested a value for this setting. Once again for an ephemeral test system, I could live with this issue as nobody else was dependent on this system.

The ‘trivial’ issues were somethings that may not be considered trivial by all.  The first of these is the InnoDB flush method.  My test system was set up to use fsync while PMM recommends O_DIRECT which is usually the choice for use with local disks. I will not review all the options and opinions (and there are many) but it was nice to get a gentle prodding from PMM about this issue. If this test system was going to be around for a while, I would definitely change the current setting to the recommended. 

My second ‘trivial’ problem was more of a warning about a user who had DBA privileges. And the last problem was a recommendation to change the binlog_row_image being set to full when minimal would provide better performance.  You might consider these nagging by PMM but both are issues a DBA or a Reliability Engineer would gladly be reminded of.  

To enable the Security threat Tool, select the Gear Icon on the left side of the main PMM display and click on the gear icon for the Configuration option and then the second gear icon for Settings.

 

And finally, enable the Security Threat Tool and I would stick with the default values on intervals when you begin to explore this tool.

Conclusion

The Percona Monitoring and Management Security Threat Tool is a handy way to gain insight into your MySQL, PostgreSQL, or MongoDB database.  It provides information that general security tools will not provide and is packed with Percona’s easy-to-use PMM interface.  This is an open source tool that you need to have at your disposal.

Percona Monitoring and Management 2 (PMM2) is the database monitoring suite assembled and developed by Percona. It is based on standard open source components and custom-made software integrations. PMM2 helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

This blog post will describe a method to test PMM2 using your laptop’s VirtualBox, ssh tunnels, and without installing any agents on the database servers. This is a testing and evaluation environment, not intended for production. If you want to perform a full-fledged test of PMM2, we recommend an environment as similar as possible to your final production setup: enterprise virtualization, docker containers, or AWS. We assume that your laptop doesn’t have direct connectivity to the databases, this is why we use ssh tunnels.

PMM2 architecture consists of 2+1 components:
Two components run on your infrastructure: PMM Agents and PMM Server. The agents gather the metrics at the database and operating system levels. PMM Server takes care of processing, storing, grouping, and displaying these metrics. It can also perform additional operations like capturing serverless databases metrics, backups, and sending alerts (the last two features are in technical preview as of this writing). The other component to complete the formula is the Percona Platform, which adds more features to PMM, from advisors to DBaaS. Disclaimer: Percona Platform is in preview release with limited functionality – suitable for early adopters, development, and testing. Besides the extended features added to PMM, the Percona Platform brings together distributions of MySQL, PostgreSQL, and MongoDB including a range of open-source tools for data backup, availability, and management. You can learn more about the Platform here.

To make setup easier, PMM2 Server can be run either as a docker container or importing an OVA image, executed using VMWare VSphere, VirtualBox, or any other hypervisor. If you run your infrastructure in AWS, you can deploy PMM from the AWS Marketplace.

To run the agents, you need a Linux box. We recommend running the agents and the database on the same node. PMM can also gather the metrics using a direct connection to a server-less database or running an operating system that does not support the agent.

Often, installing the agents is a stopper for some DBAs who would like to test PMM2. Also, while containers are frequent in large organizations, we find virtualization and containers relegated to development and quality assurance environments. These environments usually don’t have direct access to production databases.

TCP Forwarding Across SSH Connections

AllowTcpForwarding is the ssh daemon configuration option that allows forwarding TCP ports across the ssh connection. At first sight, this may seem a security risk, but as the ssh documentation states: “disabling TCP forwarding does not improve security unless users are also denied shell access, as they can always install their forwarders.”

If your system administrators do not allow TCP forwarding, other options available to accomplish the same results are socat or netcat. But we will not cover them here.

If your laptop has direct access to the databases, you can skip all the ssh tunnels and use the direct access method described later in this post.

Install PMM 2 Ova

Download the Open Virtualization Format compatible image from https://www.percona.com/downloads/pmm2/ or use the command line:

$ wget https://www.percona.com/downloads/pmm2/2.25.0/ova/pmm-server-2.25.0.ova

You can import the OVA file using the UI, with the import option from the file menu, or using the command line:

$ VBoxManage import pmm-server-2.25.0.ova --vsys 0 --vmname "PMM Testing"
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Interpreting /Users/pep/Downloads/pmm-server-2.25.0.ova...
OK.
Disks:
vmdisk1 42949672960 -1 http://www.vmware.com/interfaces/specifications/vmdk.html#streamOptimized PMM2-Server-2021-12-13-1012-disk001.vmdk -1 -1
vmdisk2 429496729600 -1 http://www.vmware.com/interfaces/specifications/vmdk.html#streamOptimized PMM2-Server-2021-12-13-1012-disk002.vmdk -1 -1

Virtual system 0:
0: Suggested OS type: "RedHat_64"
(change with "--vsys 0 --ostype "; use "list ostypes" to list all possible values)
1: VM name specified with --vmname: "PMM Testing"
2: Suggested VM group "/"
(change with "--vsys 0 --group ")
3: Suggested VM settings file name "/Users/Pep/VirtualBox VMs/PMM2-Server-2021-12-13-1012/PMM2-Server-2021-12-13-1012.vbox"
(change with "--vsys 0 --settingsfile ")
4: Suggested VM base folder "/Users/Pep/VirtualBox VMs"
(change with "--vsys 0 --basefolder ")
5: Product (ignored): Percona Monitoring and Management
6: Vendor (ignored): Percona
7: Version (ignored): 2021-12-13
8: ProductUrl (ignored): https://www.percona.com/software/database-tools/percona-monitoring-and-management
9: VendorUrl (ignored): https://www.percona.com
10: Description "Percona Monitoring and Management (PMM) is an open-source platform for managing and monitoring MySQL and MongoDB performance"
(change with "--vsys 0 --description ")
11: Number of CPUs: 1
(change with "--vsys 0 --cpus ")
12: Guest memory: 4096 MB
(change with "--vsys 0 --memory ")
13: Network adapter: orig NAT, config 3, extra slot=0;type=NAT
14: SCSI controller, type LsiLogic
(change with "--vsys 0 --unit 14 --scsitype {BusLogic|LsiLogic}";
disable with "--vsys 0 --unit 14 --ignore")
15: Hard disk image: source image=PMM2-Server-2021-12-13-1012-disk001.vmdk, target path=PMM2-Server-2021-12-13-1012-disk001.vmdk, controller=14;channel=0
(change target path with "--vsys 0 --unit 15 --disk path";
disable with "--vsys 0 --unit 15 --ignore")
16: Hard disk image: source image=PMM2-Server-2021-12-13-1012-disk002.vmdk, target path=PMM2-Server-2021-12-13-1012-disk002.vmdk, controller=14;channel=1
(change target path with "--vsys 0 --unit 16 --disk path";
disable with "--vsys 0 --unit 16 --ignore")
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Successfully imported the appliance.

Once the machine is imported, we will connect it to a host-only network. This network restricts network traffic only between the host and the virtual machines. But first, let’s find a suitable network:

$ VBoxManage list hostonlyifs
Name: vboxnet0
GUID: 786f6276-656e-4074-8000-0a0027000000
DHCP: Disabled
IPAddress: 192.168.56.1
NetworkMask: 255.255.255.0
IPV6Address:
IPV6NetworkMaskPrefixLength: 0
HardwareAddress: 0a:00:27:00:00:00
MediumType: Ethernet
Wireless: No
Status: Up
VBoxNetworkName: HostInterfaceNetworking-vboxnet0

Select the first one that has Status up, write down the name and IP address. Then make sure that there is a DHCP server assigned to that interface:

$ VBoxManage list dhcpservers
NetworkName: HostInterfaceNetworking-vboxnet0
Dhcpd IP: 192.168.56.100
LowerIPAddress: 192.168.56.101
UpperIPAddress: 192.168.56.254
NetworkMask: 255.255.255.0
Enabled: Yes
Global Configuration:
minLeaseTime: default
defaultLeaseTime: default
maxLeaseTime: default
Forced options: None
Suppressed opts.: None
1/legacy: 255.255.255.0
Groups: None
Individual Configs: None

Now we will assign two network interfaces to our PMM virtual machine. One is allocated to the internal network, and the other uses NAT to connect to the internet and, for example, check for upgrades.

$ VBoxManage modifyvm "PMM Testing" --nic1 hostonly --hostonlyadapter1 vboxnet0
$ VBoxManage modifyvm "PMM Testing" --nic2 natnetwork

Once networking is configured, we may start the virtual machine.

$ VBoxManage startvm "PMM Testing"

The next step is to retrieve the IP address assigned to our PMM box. First, we will obtain the MAC address of the network card we recently added:

$ VBoxManage showvminfo "PMM Testing" | grep -i vboxnet0
NIC 1: MAC: 08002772600D, Attachment: Host-only Interface 'vboxnet0', Cable connected: on, Trace: off (file: none), Type: 82540EM, Reported speed: 0 Mbps, Boot priority: 0, Promisc Policy: deny, Bandwidth group: none

Using the retrieved MAC address we can look for the DHCP leases:

$ VBoxManage dhcpserver findlease --interface=vboxnet0 --mac-address=08002772600D
IP Address: 192.168.56.112
MAC Address: 08:00:27:72:60:0d
State: acked
Issued: 2021-12-21T22:15:54Z (1640124954)
Expire: 2021-12-21T22:25:54Z (1640125554)
TTL: 600 sec, currently 444 sec left

This is the IP address we will use to access the PMM server. Open a browser to connect to https://192.168.56.112 with the default credentials: admin/admin.

The following step configures the tunnels to connect to the databases we monitor.

Set Up the SSH Tunnels

This is the topology of our network:

We will open two ssh connections per server we want to access from PMM. Open a terminal session and execute the following command, replacing it with the username you normally use to connect to your jump host:

$ ssh -L 192.168.56.1:3306:10.0.0.1:3306 @192.168.55.100

This creates a tunnel that connects the MySQL Server port 3306 with our local internal address in the same port. If you want to connect to more than one MySQL instance, you must use different ports. To open the tunnel for the MongoDB server, use the following command:

$ ssh -L 192.168.56.1:27017:10.0.0.2:27017 @192.168.55.100

Test the tunnel connectivity to the MySQL host using netcat:

$ nc -z 192.168.56.1 3306
Connection to 192.168.56.1 port 3306 [tcp/mysql] succeeded!

And also test the connectivity to the MongoDB host:

$ nc -z 192.168.56.1 27017
Connection to 192.168.56.1 port 27017 [tcp/*] succeeded!

This is the topology of our network including the ssh tunnels.

Configure Accounts

Follow the PMM documentation and create a MySQL account (or use an already existing account) with the required privileges:

CREATE USER 'pmm'@'10.0.0.100' IDENTIFIED BY '' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'pmm'@'10.0.0.100';

Note that we need to use the internal IP address for the jump host. If you don’t know the IP address, use the wildcard ‘%’.

Add also the credentials for MongoDB, run this in a Mongo session:

db.getSiblingDB("admin").createRole({
role: "explainRole",
privileges: [{
resource: {
db: "",
collection: ""
},
actions: [
"listIndexes",
"listCollections",
"dbStats",
"dbHash",
"collStats",
"find"
]
}],
roles:[]
})

db.getSiblingDB("admin").createUser({
user: "pmm_mongodb",
pwd: "",
roles: [
{ role: "explainRole", db: "admin" },
{ role: "clusterMonitor", db: "admin" },
{ role: "read", db: "local" }
]
})

Add the Services to PMM

We can’t install the agents because we don’t have access to our PMM testing environment from the database servers. Instead, we will configure both services as remote instances. Go to the “Configuration” menu , select “PMM Inventory” , then “Add instance” . Then choose MySQL Add a remote instance.
Complete the following fields:
Hostname: 192.168.56.1 (This is the internal Host-Only VirtualBox address)
Service name: MySQL8
Port: 3306
Username: pmm
Password: <password>

And press the button. It will check the connectivity and, if everything is correct, the MySQL service will be added to the inventory. If there is an error, double-check that the ssh connection is still open and you entered the correct credentials. Make sure that the host that you specified to create the MySQL user is correct.

We will use a similar process for MongoDB:

These are the fields you have to complete with the correct information:
Hostname: 192.168.56.1 (Again, the internal Host-Only VirtualBox address)
Service name: MongoDB
Port: 27017
Username: pmm_mongodb
Password: <password>

And press the button. It will check the connectivity and, if everything is correct, the MongoDB service will be added to the inventory. If there is an error, double-check again that the ssh connection is open and you entered the correct credentials. You can use also the MongoDB client application to check access.

Once you added both services, you just need to wait for a few minutes to give time to collect data and start testing PMM2.

TL;DR

The table below summarizes the message in this blog post. For more details and reasoning, please continue reading.

What	What is happening and when
Percona Monitoring and Management v1	End of Life – May 2022
Percona Server for MongoDB v4.0 Percona Distribution for MongoDB v4.0	End of Life – April 2022
Packaging and support for Ubuntu 16.04	No new releases, effective immediately

Percona Monitoring and Management v1 EOL

Percona Monitoring and Management (PMM) started its history in April 2016 when PMM Beta 1.0.0 was released. Almost 3 years later, in September 2019, we have released PMM 2.0.0 – a successor and feature-rich replacement for v1. Since that time, all our engineering efforts have been focused on version 2, but we were still shipping minor updates and security patches to version 1.

Six months from now, on May 1, 2022, Percona Monitoring and Management version 1 will enter the End of Life stage, which means it is not going to receive any security updates, bug fixes, and improvements.

Because of the significant architectural changes between PMM v1 and PMM v2, there is no direct upgrade path. Please review Running PMM1 and PMM2 Clients on the Same Host for how to perform a smooth transition from one version to another.

 

Ubuntu Linux 16.04 EOL

Ubuntu Linux 16.04 has reached its end of life on April 30, 2021. Even though Canonical announced that they will provide paid Extended Security Maintenance (ESM) for 16.04 for five more years, we have decided to stop providing releases of our software for this platform in favor of focusing our efforts on more recent versions of Ubuntu. There are two reasons:

• ESM addresses security issues only, but the latest packages, libraries, and software are not shipped. Such an approach would slow down the innovation for our engineering teams and complicate the release process. 
• We see community dropping support for Ubuntu 16.04 as well – MySQL, MongoDB (5.0), PostgreSQL

MongoDB 4.0 EOL

As per official MongoDB Software Lifecycle Schedules, MongoDB 4.0 is going End of Life in April 2022. At Percona we are going to follow the same schedule, which means that starting in April 2022:

• No new versions are going to be released for Percona Server for MongoDB 4.0 and Percona Distribution for MongoDB 4.0 after April 2022. This also means no more security fixes will be provided.
• New releases of Percona Distribution for MongoDB Operator and Percona Backup for MongoDB will not be tested against version 4.0 of MongoDB and corresponding Percona software.

Our support team will continue to provide operational support (bug fixes and software builds will no longer be generated) for 4.0 even after April 2022, but we strongly encourage our users and customers to prepare for the upgrade beforehand.  At the time of writing this blog post, the recommended version to upgrade to is Percona Server for MongoDB 4.2.17-17. Please read this how-to on how to perform a major version upgrade from 4.0 to 4.2.

If you need assistance with the upgrade, please get in touch with us by raising a support ticket, contacting our sales team, or raising a topic on our Community Forum.

Call for Action

We understand that upgrading the software might be challenging, especially for mid- and large-sized environments. We would like to hear your story and concerns in the corresponding forum threads and see if we can help you with addressing them:

• PMM v1 EOL
• Ubuntu 16.04 EOL
• MongoDB 4.0 EOL

Percona has been named a finalist in the Computing Technology Product Awards for Best Use of Open Source Technologies. If you’re a customer, partner, or just a fan of Percona and what we stand for, we’d love your vote.

With Great Power…

You know the phrase. We’re leaving it to you and your peers in the tech world to push us to the top.

Computing’s Technology Product Awards are open to a public vote until October 29. Vote Here!

Thank you for supporting excellence in the open source database industry. We look forward to the awards ceremony on Friday, November 26, 2021.

Why We’re an Open Source Finalist

A contributing factor to our success has been Percona Monitoring and Management (PMM), an open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical MySQL, MongoDB, PostgreSQL, and MariaDB database environments, no matter where they are located or deployed. It’s impressing customers, and even competitors, in the industry.

If you want to see how Percona became a finalist, learn more about Percona Monitoring and Management, and be sure to follow @Percona on all platforms.

Vote Today!

In this short blog post, we are going to review how to avoid using credentials in the Percona Monitoring and Management (PMM) client command line when adding new exporters. We will use an example with the MySQL exporter, but it is extensible to others (PostgreSQL, MongoDB, etc.).

In the online documentation we can see the basic steps for adding a new MySQL exporter:

1.  Configure the PMM client 
   1. pmm-admin config ...
2. Add the MySQL exporter
   1. pmm-admin add mysql --username=pmm --password=pass

The issue with this approach is that the user and password are there in plain sight for anyone to see, be it through the shell history or via commands like ps aux.

The PMM client uses kingpin to parse the arguments given, so we can use its feature for reading them from a file to do it in a more secure way. We just need to create the files with the arguments we want to hide from the commands, like:

shell> cat <<EOF >/home/agustin/pmm-admin-config.conf
--server-insecure-tls
--server-url=https://admin:admin@X.X.X.X:443
EOF

shell> cat <<EOF >/home/agustin/pmm-admin-mysql.conf
--username=pmm
--password=pmmpassword
EOF

Note that the above commands were used for simplicity in showing how they can be created. If you are worried about leaving traces in the shell command history use vim (or your editor of choice) to actually create the files and their contents.

We can use these files in the following way, instead:

shell> pmm-admin config @/home/agustin/pmm-admin-config.conf

shell> pmm-admin add mysql @/home/agustin/pmm-admin-mysql.conf

We can still use other arguments in the command directly. For example, for the MySQL command:

shell> pmm-admin add mysql --port=6033 @/home/agustin/pmm-admin.conf

PMM clients will not store database credentials within themselves, but will instead request this data from the PMM server. After the exporters are added and running, remove the pmm-admin conf files.

Using Shell Variables

Another way of achieving this is to use “hidden” variables, like:

shell> read -s pmm_mysql_pass
[type_the_password_here]
shell> pmm-admin add mysql --username=pmm --password=${pmm_mysql_pass}

You can then even wipe the variable out if you want:

shell> pmm_mysql_pass=""

Complete the 2021 Percona Open Source Data Management Software Survey

Have Your Say!

As a DBA, it is important to monitor a database to help us troubleshoot or to understand the health of an instance. Percona Monitoring and Management (PMM v2) is open-source and does a great job in monitoring the databases like MongoDB, MySQL, PostgreSQL, etc.

In this blog post, we will see how to configure a sharded cluster in PMM2. This is a part two version of the previous one which was done with PMM v1, titled Configuring PMM Monitoring for MongoDB Cluster. I have listed the steps to configure the sharded cluster into PMM2 below:

Prepare DB for Monitoring

Before configuring with PMM2, we will need to create a USER for monitoring from the database side. If you need to enable QAN (query analytics), then you will need to enable profiler and some more custom permission like “explainRole”  to the user as well. Adding profiler adds up some more little load to the database, so it is better you do prior tests to analyze the load if you want to assess the extra load.

1. Add PMM Users to the DB

// Change role name / user / password as required

db.getSiblingDB("admin").createRole({
    role: "explainRole",
    privileges: [{
        resource: {
            db: "",
            collection: ""
            },
        actions: [
            "listIndexes",
            "listCollections",
            "dbStats",
            "dbHash",
            "collStats",
            "find"
            ]
        }],
    roles:[]
})


db.getSiblingDB("admin").createUser({
   user: "pmm_mongodb",
   pwd: "password",
   roles: [
      { role: "explainRole", db: "admin" },
      { role: "clusterMonitor", db: "admin" },
      { role: "read", db: "local" }
   ]
})

2. Enabling Profiler

This is optional. Run the instance with the profiler or add profiling at the database level to monitor queries in QAN (not applicable for mongos).

To start at the instance level (enables profiling for all databases):

mongod <other options> --profile 2 --slowms 200 --rateLimit 100

or in mongod.conf:

operationProfiling:
  mode: all
  slowOpThresholdMs: 200
# (Below variable is available only with Percona Server for MongoDB.)
  rateLimit: 100

To enable p[rofiling at DB level:

use dbname
db.setProfilingLevel(2)

3. Add MongoDB Instance to the pmm-client

Here use the same –cluster option name for all members from the same cluster and provide service-name to identify it:

sudo pmm-admin add mongodb \
--username=pmm_mongodb --password=password \
--query-source=profiler \
--cluster=mycluster \
--service-name=myc_mongoc2 \
--host=127.0.0.1 --port=37061

4. Check the Inventory Service

Then check whether the service was added successfully or not:

$ sudo pmm-admin list
Service type        Service name                   Address and port        Service ID
MongoDB             myc_mongoc2                    127.0.0.1:37061         /service_id/02e261a1-e8e0-4eb4-8043-8616424500de

Agent type                    Status           Metrics Mode        Agent ID                                              Service ID
pmm_agent                     Connected                            /agent_id/281b4046-4f4b-4897-bd2e-b771d3e97922         
node_exporter                 Running          push                /agent_id/5e9b17a8-ecb9-47c3-8477-ce322047c4d9         
mongodb_exporter              Running          push                /agent_id/0067dd85-9a0a-47dd-976e-ae779deb982b        /service_id/5c92f132-3005-45ab-84df-7541c286c34a
mongodb_profiler_agent        Running                              /agent_id/18d3d87a-9bb9-48c1-8e3e-d8bae3f043bb        /service_id/02e261a1-e8e0-4eb4-8043-8616424500de

From My Test

I used localhost to deploy the sharded cluster for the testing purpose as below:

Members list:

1 mongos (37050), 
3 shards consist of 3 member replicaSet each (37051-37059), 
3 config members(37060-37062)

Listing one mongod instance from the ps command:

balaguru@vinodh-UbuntuPC:~/mongodb/testshard$ ps -ef | grep mongod -w | head -1
balaguru   41883    2846  1 13:01 ?        00:04:04 mongod --replSet configRepl --dbpath /home/balaguru/mongodb/testshard/data/configRepl/rs1/db --logpath /home/balaguru/mongodb/testshard/data/configRepl/rs1/mongod.log --port 37060 --fork --configsvr --wiredTigerCacheSizeGB 1 --profile 2 --slowms 200 --rateLimit 100 --logappend

Adding mongodb services to pmm-admin:

balaguru@vinodh-UbuntuPC:~/mongodb/testshard$ pmm-admin add mongodb --username=pmm_mongodb --password=password \
--query-source=profiler --cluster=mycluster --service-name=myc_s11 --host=127.0.0.1 --port=37051
MongoDB Service added.
Service ID  : /service_id/cc6b3fed-ee16-494e-93f0-0d2e8f60a136
Service name: myc_s11--host=127.0.0.1

balaguru@vinodh-UbuntuPC:~/mongodb/testshard$ pmm-admin add mongodb --username=pmm_mongodb --password=password \
--query-source=profiler --cluster=mycluster --service-name=myc_s12 --host=127.0.0.1 --port=37052
MongoDB Service added.
Service ID  : /service_id/235494d8-aaee-4ca0-bd3a-bf2259e87ecc
Service name: myc_s12

balaguru@vinodh-UbuntuPC:~/mongodb/testshard$ pmm-admin add mongodb --username=pmm_mongodb --password=password \
--query-source=profiler --cluster=mycluster --service-name=myc_s13 --host=127.0.0.1 --port=37053
MongoDB Service added.
Service ID  : /service_id/55261675-41e7-40f1-95c9-08cac25c4f64
Service name: myc_s13

balaguru@vinodh-UbuntuPC:~/mongodb/testshard$ pmm-admin add mongodb --username=pmm_mongodb --password=password \
--query-source=profiler --cluster=mycluster --service-name=myc_s21 --host=127.0.0.1 --port=37054
MongoDB Service added.
Service ID  : /service_id/5c92f132-3005-45ab-84df-7541c286c34a
Service name: myc_s21

balaguru@vinodh-UbuntuPC:~/mongodb/testshard$ pmm-admin add mongodb --username=pmm_mongodb --password=password \
--query-source=profiler --cluster=mycluster --service-name=myc_s22 --host=127.0.0.1 --port=37055
MongoDB Service added.
Service ID  : /service_id/4de07a5b-5a47-4126-8824-80570bd72cef
Service name: myc_s22--host=127.0.0.1

balaguru@vinodh-UbuntuPC:~/mongodb/testshard$ pmm-admin add mongodb --username=pmm_mongodb --password=password \
--query-source=profiler --cluster=mycluster --service-name=myc_s23 --host=127.0.0.1 --port=37056
MongoDB Service added.
Service ID  : /service_id/7bdaaa72-6e00-4f46-a2a9-5205d5f3fff5
Service name: myc_s23

balaguru@vinodh-UbuntuPC:~/mongodb/testshard$ pmm-admin add mongodb --username=pmm_mongodb --password=password \
--query-source=profiler --cluster=mycluster --service-name=myc_s31 --host=127.0.0.1 --port=37057
MongoDB Service added.
Service ID  : /service_id/2028e075-bc65-4aae-bcdd-ec616b36e81b
Service name: myc_s31

balaguru@vinodh-UbuntuPC:~/mongodb/testshard$ pmm-admin add mongodb --username=pmm_mongodb --password=password \
--query-source=profiler --cluster=mycluster --service-name=myc_s32 --host=127.0.0.1 --port=37058
MongoDB Service added.
Service ID  : /service_id/7659231c-f48f-4a65-b651-585ac1f058cd
Service name: myc_s32

balaguru@vinodh-UbuntuPC:~/mongodb/testshard$ pmm-admin add mongodb --username=pmm_mongodb --password=password \
--query-source=profiler --cluster=mycluster --service-name=myc_s33 --host=127.0.0.1 --port=37059
MongoDB Service added.
Service ID  : /service_id/2c224eaf-c0f1-482b-b23c-8ea4b914c8e5
Service name: myc_s33

balaguru@vinodh-UbuntuPC:~/mongodb/testshard$ pmm-admin add mongodb --username=pmm_mongodb --password=password \
--query-source=profiler --cluster=mycluster --service-name=myc_mongoc1 --host=127.0.0.1 --port=37060
MongoDB Service added.
Service ID  : /service_id/09e95cc5-40b7-4a53-9e35-2937ca23395f
Service name: myc_mongoc1

balaguru@vinodh-UbuntuPC:~/mongodb/testshard$ pmm-admin add mongodb --username=pmm_mongodb --password=password \
--query-source=profiler --cluster=mycluster --service-name=myc_mongoc2 --host=127.0.0.1 --port=37061
MongoDB Service added.
Service ID  : /service_id/02e261a1-e8e0-4eb4-8043-8616424500de
Service name: myc_mongoc2

balaguru@vinodh-UbuntuPC:~/mongodb/testshard$ pmm-admin add mongodb --username=pmm_mongodb --password=password \
--query-source=profiler --cluster=mycluster --service-name=myc_mongoc3 --host=127.0.0.1 --port=37062
MongoDB Service added.
Service ID  : /service_id/421449d9-8ada-46dd-9c8a-84c0847a8742
Service name: myc_mongoc3

Listing the services added:

balaguru@vinodh-UbuntuPC:~/mongodb/testshard$ pmm-admin list
Service type        Service name                   Address and port        Service ID
MongoDB             myc_mongoc2                    127.0.0.1:37061         /service_id/02e261a1-e8e0-4eb4-8043-8616424500de
MongoDB             myc_mongoc1                    127.0.0.1:37060         /service_id/09e95cc5-40b7-4a53-9e35-2937ca23395f
MongoDB             myc_s31                        127.0.0.1:37057         /service_id/2028e075-bc65-4aae-bcdd-ec616b36e81b
MongoDB             myc_s12                        127.0.0.1:37052         /service_id/235494d8-aaee-4ca0-bd3a-bf2259e87ecc
MongoDB             myc_s33                        127.0.0.1:37059         /service_id/2c224eaf-c0f1-482b-b23c-8ea4b914c8e5
MongoDB             myc_mongos                     127.0.0.1:37050         /service_id/3f4f56be-6259-4579-88b7-bb4d0c29204b
MongoDB             myc_mongoc3                    127.0.0.1:37062         /service_id/421449d9-8ada-46dd-9c8a-84c0847a8742
MongoDB             myc_s22                        127.0.0.1:37055         /service_id/4de07a5b-5a47-4126-8824-80570bd72cef
MongoDB             myc_s13                        127.0.0.1:37053         /service_id/55261675-41e7-40f1-95c9-08cac25c4f64
MongoDB             myc_s21                        127.0.0.1:37054         /service_id/5c92f132-3005-45ab-84df-7541c286c34a
MongoDB             myc_s32                        127.0.0.1:37058         /service_id/7659231c-f48f-4a65-b651-585ac1f058cd
MongoDB             myc_s23                        127.0.0.1:37056         /service_id/7bdaaa72-6e00-4f46-a2a9-5205d5f3fff5
MongoDB             myc_s11                        127.0.0.1:37051         /service_id/cc6b3fed-ee16-494e-93f0-0d2e8f60a136

Agent type                    Status           Metrics Mode        Agent ID                                              Service ID
pmm_agent                     Connected                            /agent_id/281b4046-4f4b-4897-bd2e-b771d3e97922         
node_exporter                 Running          push                /agent_id/5e9b17a8-ecb9-47c3-8477-ce322047c4d9         
mongodb_exporter              Running          push                /agent_id/0067dd85-9a0a-47dd-976e-ae779deb982b        /service_id/5c92f132-3005-45ab-84df-7541c286c34a 
mongodb_exporter              Running          push                /agent_id/071ec1ae-ff35-4fa1-a4c9-4d5bca705131        /service_id/09e95cc5-40b7-4a53-9e35-2937ca23395f 
mongodb_exporter              Running          push                /agent_id/5e045290-36c2-410b-86e9-b4945cd7ecfb        /service_id/3f4f56be-6259-4579-88b7-bb4d0c29204b 
mongodb_exporter              Running          push                /agent_id/6331b519-da6e-47c0-be7e-92f2ac142fa5        /service_id/2c224eaf-c0f1-482b-b23c-8ea4b914c8e5 
mongodb_exporter              Running          push                /agent_id/6ce78e1c-be6a-4ffd-844b-8afdc0ee5700        /service_id/235494d8-aaee-4ca0-bd3a-bf2259e87ecc 
mongodb_exporter              Running          push                /agent_id/6ed1bcc2-3561-4c65-95e1-11b3cc051194        /service_id/cc6b3fed-ee16-494e-93f0-0d2e8f60a136 
mongodb_exporter              Running          push                /agent_id/7721bd24-7408-431d-abcb-3239459df75a        /service_id/7659231c-f48f-4a65-b651-585ac1f058cd 
mongodb_exporter              Running          push                /agent_id/999c0152-656e-4941-a1fb-003df2dbfbf6        /service_id/2028e075-bc65-4aae-bcdd-ec616b36e81b 
mongodb_exporter              Running          push                /agent_id/9e63f2d9-7e75-45ee-927d-b1406d4797e0        /service_id/55261675-41e7-40f1-95c9-08cac25c4f64 
mongodb_exporter              Running          push                /agent_id/ca3ab511-29eb-4c68-b037-23ab13fa92ff        /service_id/4de07a5b-5a47-4126-8824-80570bd72cef 
mongodb_exporter              Running          push                /agent_id/cd1066eb-f917-4d7e-b284-8d8a8bc7c652        /service_id/7bdaaa72-6e00-4f46-a2a9-5205d5f3fff5 
mongodb_exporter              Running          push                /agent_id/e2ef230a-d84b-428c-921b-b6da7c3180f3        /service_id/421449d9-8ada-46dd-9c8a-84c0847a8742 
mongodb_exporter              Running          push                /agent_id/e3f7ba25-6592-4cb4-aae6-7431b3b6a6da        /service_id/02e261a1-e8e0-4eb4-8043-8616424500de 
mongodb_profiler_agent        Running                              /agent_id/18d3d87a-9bb9-48c1-8e3e-d8bae3f043bb        /service_id/02e261a1-e8e0-4eb4-8043-8616424500de 
mongodb_profiler_agent        Running                              /agent_id/1cf5ee8a-b5b5-4133-896c-fafccc164f54        /service_id/5c92f132-3005-45ab-84df-7541c286c34a 
mongodb_profiler_agent        Running                              /agent_id/4b13cc24-fbd2-47cc-955d-c2a65624d2be        /service_id/55261675-41e7-40f1-95c9-08cac25c4f64 
mongodb_profiler_agent        Running                              /agent_id/4de795cf-f047-49e6-a3bc-dc2ab1b2bc86        /service_id/cc6b3fed-ee16-494e-93f0-0d2e8f60a136 
mongodb_profiler_agent        Running                              /agent_id/89ae83c7-e62c-48f6-9e8c-597ce978c8ce        /service_id/4de07a5b-5a47-4126-8824-80570bd72cef 
mongodb_profiler_agent        Running                              /agent_id/98343388-a246-4767-8838-ded8f8de5191        /service_id/235494d8-aaee-4ca0-bd3a-bf2259e87ecc 
mongodb_profiler_agent        Running                              /agent_id/a5df9e6b-037e-486a-bc95-afe20095cf98        /service_id/7bdaaa72-6e00-4f46-a2a9-5205d5f3fff5 
mongodb_profiler_agent        Running                              /agent_id/a6bda9b4-989a-427b-ae64-5deffc2b9ba2        /service_id/7659231c-f48f-4a65-b651-585ac1f058cd 
mongodb_profiler_agent        Running                              /agent_id/c59c40ca-63ee-4497-b297-403faa9d4ec0        /service_id/2c224eaf-c0f1-482b-b23c-8ea4b914c8e5 
mongodb_profiler_agent        Running                              /agent_id/c7f84a08-4823-455b-93a3-168eee19329b        /service_id/3f4f56be-6259-4579-88b7-bb4d0c29204b 
mongodb_profiler_agent        Running                              /agent_id/e85d0757-7542-4b38-bfed-81ded8bf309c        /service_id/421449d9-8ada-46dd-9c8a-84c0847a8742 
mongodb_profiler_agent        Running                              /agent_id/ed81849a-6fc9-46f3-a5dc-e6c288409009        /service_id/09e95cc5-40b7-4a53-9e35-2937ca23395f 
mongodb_profiler_agent        Running                              /agent_id/f9d26161-4827-4bed-a85f-cbe3ce9478ab        /service_id/2028e075-bc65-4aae-bcdd-ec616b36e81b 
vmagent                       Running          push                /agent_id/a662e1f6-31d3-4514-8f83-ea31e0165d61

PMM Dashboards

From PMM Dashboards, you can then view the replSet summary as well as the sharded cluster summary.

Cluster Summary

This dashboard gives information about the sharded/unsharded databases, shards, chunks, cursor details, etc.

 

ReplSet Summary:

This dashboard tells about the replication information like replica lag, operations, heartbeat, ping time, etc.

 

MongoDB Instance Overview:

This is the general dashboard for a MongoDB instance which provides generic information about the connections, memory usage, latency, etc

 

WiredTiger Details:

This is the main dashboard that you’ll need most to analyze the problems here as it shows the wiredTiger information. The main metrics that you need to monitor here are the WT cache utilization, evictions of modified or unmodified pages, write/read tickets utilization, index/objects scans, etc.

 

QAN:

If you enable the profiling, then you could see the queries used in the database here. You can filter them easily as shown in the screenshot below. Also, you can get the explain plan to check whether they utilize the COLLSCAN (disk reads) or IXSCAN (uses index). Also, you can check the counts, load, etc.

 

Conclusion

As said, Percona Monitoring and Management 2 is very easy to configure to monitor the databases and it is recommended too. It’s better now rather than late to configure the monitoring. PMM2 is managed by Percona which is totally free and you can raise any bugs here – https://jira.percona.com/. If you have doubts, you can leave your questions here – https://forums.percona.com.

Complete the 2021 Percona Open Source Data Management Software Survey

Have Your Say!

As Technical Product Manager, I get a lot of user feedback, both positive and negative. In recent months many people have complained about the Home dashboard. It went something like this:

“Hey, the Home page is useless! We have several hundred services monitored by a single Percona Monitoring and Management (PMM), so this list of metrics is not providing any value when I have this many servers”.

We were happy to note that people were using PMM for big deployments, and we decided to create new types of dashboards for these users. I will provide their description below. But what exactly is the problem with the Home dashboard now? Let’s take a look.

Percona Monitoring and Management Dashboard

The red box represents the visible part of the screen on my laptop. So to see any data, I need to scroll to the bottom of the page. But even if I do, I will see a set of small graphs not related to one another.

If I have more than three nodes, I get something like the example below: (https://pmmdemo.percona.com/)

Remember, on the laptop screen, you can only see this much:

You can’t see the complete picture and compare the performance of individual servers; there is nothing actionable.

This dashboard is clearly not meeting the goal of giving the user a high-level overview of their infrastructure. To achieve this goal, we have created two new experimental dashboards – Environment Overview and Environment Summary.

The Environment label is already present in the current version of the dashboard. It lets users specify different groups of the Service. We have the particular flag to add an environment label when you add a Service to PMM.

# pmm-admin add mongodb … --environment=environment1 ..

Here are some ideas of what you can use as an environment:

• “production”, “development”, “testing”
• “departmentA”, “departmentB”, “subDepartmentC”
• “Datacenter1”, “datacenter2”, “cloud-region-east2”
• <Your ideas here>

Setting the environment for your services will simplify the search/selection on all dashboards. The new dashboards let you group your nodes by their environment label.

Environment Overview Dashboard

The Environment Overview Dashboard is designed to be a possible replacement for the default Home dashboard. This dashboard aims to give the user a high-level view of all Environments and how they are behaving.

This dashboard presents the main parameters of all environments. It shows six graphs – three for Node metrics (CPU, Memory, Disk) per environment and three for Services metrics (Used Connections, QPS, Latency) per environment. The Service metrics are inspired by RED Method for MySQL Performance Analyses – Percona Database Performance Blog.

You get some valuable data on the first screen. It provides you a summary of the parameters of your environments and highlights apparent problems. The second screen shows the relationships between the main metrics and how they’ve changed over a selected period of time and allows you to click on graphs and drill down for more detailed information. This will help you spot the environments where you might have a problem and know where to dig deeper to investigate it.

Environment Summary Dashboard

The Environment Summary Dashboard is designed to give you information about one specific Environment and an overview of the activities and behaviors of the Services and Nodes inside that particular environment.

You can also drill down to the specific Services, Nodes, and their parameters for more details from this dashboard. This will help you see the unhealthy Service or Node.

How to Install Dashboards

1. Get dashboards:
   • https://grafana.com/grafana/dashboards/14373
   • https://grafana.com/grafana/dashboards/14372
2. Import dashboards to PMM2 https://grafana.com/docs/grafana/latest/dashboards/export-import/

What’s Next?

These two experimental dashboards are not yet ready to be a part of the standard PMM release, but we would LOVE to make them the default. We would appreciate your feedback on making them not just a step forward from the current home dashboard but a HUGE step forward.

If you have many servers, please test the dashboards and let us know if these dashboards provide better visibility over your infrastructure. If not, we would love to hear what sort of data you want to see in these dashboards to speed up decision-making.

You can leave your feedback on our Percona Community Forum. Please help us make Percona Monitoring and Management more useful!

Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

Download Percona Monitoring and Management Today

Thanks to everyone who attended last week’s webinar on Using Open Source Software to Optimize and Troubleshoot Your MySQL Environment; hopefully you’ve found the time we spent in Percona Monitoring and Management (PMM) useful.

We had a record-breaking number of questions during the talk and unfortunately weren’t able to answer them all live, so we decided to answer them separately. Also, there were several requests for best practices around installation and configuration. This is something we are considering for the next webinar in this series, so stay tuned!

If you weren’t able to attend, the recording is available for viewing. But now, without further ado, here are the questions that we didn’t have time to cover during the presentation.

 

Q: Can PMM also be used for a web hosting server (Cpanel, Directadminetc)?

PMM by default can monitor a node to provide vital statistics on the health of the host.  From there, you can use external exporters to monitor other applications and send the data to PMM to visualize and create alerts.

 

Q: Does it provide any query optimization suggestions if my query is bad? 

Not at present…that’s planned for the future query advisor

 

Q: How soon we will be able to use the alerting manager in production?

We are looking at late Sept to early Oct. When it’s ready, you will hear about it!

 

Q: Capturing Queries Data for performance checking can be costly and some monitoring systems capture data every few seconds. At what level of data is captured here and analyzed…live systems with lots of database traffic? What percentage (all of it,  2 seconds, 1 second, etc.)?

We adhere to ‘do no harm’ so the impact of PMM  is typically 1-4% of the busiest systems.  We offer custom resolutions to adjust the scrape frequency to balance the need for information with the need for performance.

 

Q: Are long-running queries captured that potentially slow down the system over time & shown as graph/alert? Also, is there potentially more than one instance of these types running over again by a user.?

This is something we are going to include in our Alerting capabilities (coming soon, see above).

 

Q: Can more than one of the metrics be compared against each other to gain more insight into a problem in graphical form? Can you in effect play with these graphs?

Yes, you can, this is in fact how most of the dashboards are designed, where we connect different metric series together to drive graphs that explain system performance.  While you may be able to edit the existing graphs, Percona recommends that you instead make a copy of the dashboard you’d like to modify and make your changes on the copy.  The reason for this is if you modify a dashboard distributed by PMM, it will be overwritten on the next upgrade, and you’ll lose your changes.

 

Q: Could you list what can be monitored using PMM? And explain what recommended plugins are available and what they are used for? 

Natively, any Linux system and pretty much all flavors of MySQL, MariaDB, MongoDB, and PostgreSQL. You can use external exporters to gather even more data than default and using Grafana as the basis for visualization of PMM allows you to create custom dashboards and a wealth of community plugins.

 

Q: Can you choose to monitor a particular set of users? Set of queries? Set of schema? 

You can filter it down to view based on username, particular schema, and then filter those results by particular query strings.  We can monitor as much or as little about your database as the user you define to pull data.

 

Q: How can we work on optimization when using cloud-based services like RDS where we have limited access?

PMM can monitor RDS instances and has simplified the connection and selection process of its remote monitoring capabilities.  We can provide nearly the same data as an on-prem database however we don’t have access to the node level statistics.

 

Q: For Oracle MySQL 5.7.29, if you have many tables/objects in the database, will the PMM query information_schema and load the DB?

We have a predefined limit of 1000 tables that will disable polling information schema but you can configure this to your liking both with the client and with remote monitoring. This CAN have a more significant impact on your system though especially with large table and row counts.

 

Q: At what point do I know I’ve done enough optimization? 

HA! It’s a never-ending game of cat and mouse considering the sheer volume of variables in play. It’s these times where monitoring data for before and after become vital.

 

Q: Can a database monitoring package be the source of database performance issues? In particular, mysqld_exporter is installed as a docker container, as I’m seeing “out of resources” on a trace on mysqld_exporter.

Of course, there are plenty of ways to generate database performance issues and it’s possible monitoring can result in some overhead. For an extreme example, here’s one way to replicate some overhead: start the pmm-client on a MySQL database and restore a blank DB from mysqldump. A few million rows at a time should generate LOTS of chaos and load between QAN and exporters. Our pmm client runs the exporter natively so no need to use a container.

 

Q: Is the query analytics somehow slowing down the database server as well? Or is it save to enable/use it without further impact?

The impact is minimal.  Most of the Query Analytics processing is done at the PMM server, the only impact to the client is retrieving the queries from slowlog or performance schema so this can have a bigger impact for the most extremely active DB’s but still should remain below 5% CPU hit.

 

Q: Did I understand correctly that PMM is not for RDS users and that AWS tools are available?

PMM certainly is for RDS! Since RDS is managed by AWS, PMM cannot collect CPU/Disk/Memory metrics but all MySQL metrics are still available even in RDS.

 

Q: Do you have any instructions/steps to install PMM to monitor MySQL RDS? 

• Gear icon ? PMM Inventory ? Add Instance
• Choose AWS/RDS Add Remote Instance
• Use your AWS credentials to view your available RDS & Aurora nodes
• Ensure that performance_schema is enabled

 

Watch the Recording