Improvements to Query Analytics (QAN) Component of Percona Monitoring and Management

We’ve been improving the Query Analytics component of Percona Monitoring and Management (PMM). Here’s a summary of what we’ve done, beginning with a reminder of what PMM is and what it’s for.

Database Troubleshooting and PMM

Troubleshooting performance issues can be tricky, especially when all you’ve been told is that “the database seems slow”.

In such cases, your first thought should be to use PMM, the free, open-source database troubleshooting and performance optimization platform.

PMM lets you actively monitor open-source databases, providing detailed, time-based analysis of MySQL, PostgreSQL, MongoDB, or MariaDB databases. PMM helps you analyze your data to make sure that everything is working as efficiently as possible. And it helps you quickly identify problematic queries.

PMM works on-premise or in the cloud with major cloud providers, and it can scale up to manage large environments.

Query Analytics (QAN)

QAN is the application for the analysis of specific requests. Where PMM lets you home in on the problem in general, QAN helps you to focus on the core problem and understand exactly where your database is performing poorly.

Up until now, QAN was a little overcomplicated. So, we reworked a lot of functionality. Along the way, we added some interesting features, fixed a lot of old bugs, and made some general technical improvements. (I’ll explain some of them in this post.)

Also, in PMM version 2.9, we’ve completed a fairly long piece of work in migrating PMM2 from angular1 and angular8 to React. The migration of QAN completes this transition.

So, what’s new?

Functionality Improvements

MongoDB Explains

We’ve added support for MongoDB ‘explain’.

More Graphs

There are two new graphs:

• Percentage – Shows the percentage ratio between the value of the metric and its value in total.
• Time distribution – Shows which parts of the request spend the most time on execution:

Improved Metrics Selection

We’ve added the ability to swap any metric with main, without additional intermediate actions. A convenient indication has been added showing which types of databases the metric belongs to; you no longer need to guess which metrics are available.

Overview Table Simplification

One of the problems with QAN is that most of its users are experts, who tend to want to see an interface full of information and data. However, even experts were finding it hard to see what was happening in the main table, which was heavily overloaded with information and quite difficult to understand.

So, we’ve made the interface easier to understand without depriving experts of the details they need.

Cleaner Metrics

In each column, you can now see only the main numerical value and a graph showing it as a percentage of the total value:

More information is revealed by mouse-over tooltips:

Standardized Column Widths

The additional information in tooltips lets us equalize and reduce the widths of all columns. This means we can show significantly more information in the same screen area without scrolling.

Consistent Formatting

Now, all SQL queries in the output are formatted, not only those in the example sections.

Removed Duplicated Filters Search and Selection functionality

The filter search string and filter selection blocks have been combined into one section.

Improved UI Styling and Consistency

We’ve reduced the number of different fonts, colors, and styles used in the UI and removed a lot of unnecessary clutter from the interface.

Future Plans

We’re not done yet! Here are some PMM improvements we’ll be working on in the coming weeks and months.

1. Simplify the onboarding process for users;
2. Improve user documentation;
3. Improve the search and filter performance so that it performs better with large amounts of data;
4. Add more information content without compromising the simplicity of the interface, to make the system even more appealing to experts.

With our recent changes, we don’t need to worry about maintaining compatibility between many weakly-compatible elements of the system. Instead, we can focus all of our attention on the front-end, further improving the functionality and polishing the interface.

Why Did We Start a Migration?

A few years ago, QAN was created as a small, separate application. PMM1 development began with Angular1, because Grafana was written with Angular1. Then, when Angular2 was released, we noticed in Grafana’s source code hints of a possible future migration to Angular2.

Originally, QAN was a proof of concept. As we needed to add functionality and rethink the architecture, and given the hints we’d seen in Grafana development, we decided to rewrite QAN in Angular2.

However, the Grafana team later announced their decision to migrate to React. We were left with a bunch of panels in which we’d invested a lot of time and which needed to be supported. There was no hope of adding native support from the Grafana side. We were being faced with these difficulties:

1. Grafana is implemented with both Angular1 and React;
2. We integrate tightly with Grafana, and our developers need to work with its code; our developers now need to be experts in three different frameworks;
3. When Grafana chose React, our differences meant that it was just a matter of time before another rewrite would be necessary.

How It Began

As we considered these difficulties, we concluded that rewriting should start as soon as possible. Fortunately, Grafana’s approach to organizing panels as separate applications meant we could do this with minimal pain.

The goals we had when starting the migration were:

• To simplify and unify the technical stack. This would also allow us to hire developers specializing in React.
• To synchronize with Grafana’s development strategy.
• To switch to a simpler and more comfortable framework, one that allows us to speed up the adding of new functionality, and streamline development as a whole.

As an added advantage, we eliminated many legacy bugs, improved project documentation, and also simplified user interaction. And yes, we ejected a lot of redundant code that had been accumulating for a long time.

Technical Changes

This section is for those who may be interested in participating in the development.

What does migration mean for us, as front-end developers?

1. Problems with tooling are now gone, and we don’t need to assemble different parts of the interface in different ways. All assembly, testing, and checks happen as they should for Grafana plugins.
2. Almost all the code related to the frontend has moved to one repository. (This has already greatly simplified our work with assembly pipelines.)
3. We now have a homogeneous code base. Our requirements for hiring only specify an in-depth knowledge of React (in addition to the usual stack development skills).
4. There is improved integration with e2e tests that now form an integral part of the solution. Previously, developers and QA lived their own lives.

Your Opinion Matters!

We are constantly working to improve Percona Monitoring and Management –your feedback is invaluable. Please tell us about your user experiences by completing this one-minute survey.

You’ll be helping to decide how PMM develops and continues to grow in the future. Don’t miss this chance to have your voice heard!

by Roman Misyurin via Percona Database Performance Blog