5 Cool Features in Percona Monitoring and Management You Should Try!
Percona Monitoring and Management (PMM) is a free and open-source platform for managing and monitoring MySQL, MongoDB, and PostgreSQL databases, along with Load Balancing tools like ProxySQL. It is also ‘cloud ready’, meaning it has support for monitoring DBaaS (Amazon RDS, Aurora, and more). You can run PMM in your own environment for maximum security and reliability. The biggest strength of PMM is that it is highly customizable, which we will see later in this blog.
The PMM 2 release introduces a number of enhancements and additional features. Here are some improvements and features which I think are cool!
1. New Security Threat Tool
The new Security Threat tool has the goal to advise PMM users on security-related database problems they might have on their databases.
- As PMM already has a reach into databases for performance monitoring, it makes sense for it to monitor database security as well. This tool will notify about any users without a password on the servers, MongoDB authentication disabled, MySQL/Mongo new versions available, and more.
- For enabling this feature, go over the
Failed security checkspanel on PMM Homepage and click on Security Threat tool, enable it, and ‘Apply changes’.
- Once enabled, the box shows a count of the number of failed checks. These will be divided as Critical, Major, Trivial.
- For example, in the image, the Security threat tool is enabled so we can see the result of the checks:
- After clicking over these checks, we can see more details, as below for the one Critical and one Major failed check. Resolve these as per preference to secure your database.
Labeling helps to easily group instances and review these groups together. You do this by tagging the servers with Standard or Custom labels.
- Some of the standard labels available are Environments, Clusters, Replication Sets, Region.
- This can be configured while adding the server under monitoring with pmm-admin using flags
replication_set=''and so on.
- You can also have custom labels by specifying a Key-Value pair using the flag –custom-labels=’Key=Value’. For example, custom labels can be set as ‘DC=Asia’ or ‘Role=Reporting’, ‘Role=OLTP’ or anything as per your topology.
- These labels can be used in filtering in QAN as well.
- Important events related to the application can now be marked with
Annotations. Some events in the application like upgrades, patches, may impact the database. Annotations visualize these events on each dashboard of the PMM Server so that you can correlate any performance changes on the database with these events.
- Annotations can be added with
pmm-admin annotate <--tags>command on PMM Client, and passing it text which explains what event the annotation should represent. Below is an example where we can see an event, ‘Upgrade to v1.2’ represented as a vertical dotted line on the graph.
3. Query Analytics (QAN)
QAN helps to ensure database queries are executed as expected, in the shortest time possible. You can identify queries causing problems and review detailed metrics related to those queries here.
- With the new and improved QAN dashboard, you can now add multiple columns to the Query Analytics table with the
Add columnbutton, which will also show you the list of columns available. These are tagged with Service names – MySQL, MongoDB, PostgreSQL. Columns can be sorted in ascending or descending order.
- Some of the examples for additional columns are – Query Count with errors, InnoDB IO read ops, No Index Used(MySQL), Docs Scanned, Returned (MongoDB), Shared Blocks Read, Written, Dirtied (PostgreSQL).
- The query activity can now be visualized from multiple dimensions, not just the query pattern. As shown in the image, query count and load can also be viewed for Database, User, Client Hosts, etc. This will help to identify an increase in traffic from a particular user or a host, for example.
- With the new release of PMM 2.10, you now have a
Search bybar beside the dimensions as seen in the above image. It gives you the flexibility to limit the view of queries containing only the specified keywords entered in the search bar. The
Search bycan be used for other dimensions as well, like database, username.
- Hovering over the sparkline now shows the load and timestamp for that particular time under the cursor.
- The improvements will help to look for users causing the most activity, busiest schema, most active incoming client hosts, and of course problematic queries.
4. New Filter Panel for QAN
The new Filter Panel for the QAN dashboard allows you to see all your labels and gives the capability to select multiple items for filtering.
- The Filter Panel helps filtering servers with standard or custom labels like Environment, Clusters, individual nodes for faster troubleshooting.
- Filters are listed as per category in the Filter panel – Service type, Cluster, Replication Set, database, and even users.
- Selecting one reduces the overview list to those items matching the filter.
- Together with the QAN improvements mentioned before and this filter panel, you can identify users, servers that are experiencing unexpected traffic or load across a logical grouping of servers.
5. Easily Remove Services or Nodes From the PMM Inventory Dashboard
The PMM Inventory dashboard lists all Nodes, Agents, and Services that are registered against the PMM Server.
- Now, you can easily remove a service, node, or agent from this dashboard directly, unlike older versions. To remove a node/service, go to the
PMM Inventoryunder the PMM tab in the top right corner.
- On the page, there are separate tabs for Service, Agents, and Nodes. Select the tab you wish to remove from, then select the name of the service or node as required. For example, here, I wish to delete the
db2node-mysqlservice monitoring. So, I just checked the relevant box and clicked
Deletein the right corner and got the confirmation message, clicked on Force mode, and done!
- This will stop the monitoring of the service and its name will no longer appear under the list of monitored services. This can be verified from the command line after logging in to the server and executing
pmm-admin list. The mysqld_exporter process would no longer be running.
- We can also add and modify instances with PMM API. It eases deployments of large fleets of servers through scripting against the Administrative API.
Along with this, you can also customize your PMM and extend the list of available metrics. Check out below Percona blogs by Daniel Guzmán Burgos, Carlos Salguero, and Vadim Yalovets on these cool customizations!
PMM is a best-of-breed open source database monitoring and management solution which helps you focus on optimizing database performance with better observability in a secure way.
by Rituja Borse via Percona Database Performance Blog