Monitoring queries in Greenplum

Hello everyone. My name is Dmitry and I am a system architect at Arenadata. I am designing and developing ADB Control — a query monitoring system for Arenadata DB.

When you work with different databases, it is often necessary to monitor the execution of current queries. This is mainly related to administration or analytics tasks. Monitoring tools that allow you to manage and monitor the execution of queries greatly assist in this. I’ll talk about the challenges we faced when designing and implementing a query monitoring system for Arenadata DB.

Arenadata DB is an open-source analytical distributed MPP DBMS built on the Greenplum DBMS. In this regard, monitoring queries becomes very complicated because queries are executed on many segments. In this case, the load on each segment depends on the uniformity of data distribution and the types of operations performed on it. A direct analogue of ADB Control is the VMware Tanzu Greenplum Command Center system, which is a part of the paid version of the platform.

I’ll talk about the second version of ADB Control. The functional requirements in the new version, for the most part, remained the same, but the non-functional requirements were significantly increased. These requirements can be briefly expressed by the well-known triad: performance, fault tolerance, extensibility. This led to profound changes in the architecture — the system was essentially rewritten from scratch.

The result is a solution for detailed monitoring of a distributed data processing system.

First, we analyzed the system architecture of the ADB Control’s first version. After studying the working of the system and the code base, we compiled a system architecture diagram.

Where:

The process of processing metrics is shown in the image below.

Simplified operation of the system can be described as follows:

The main issues of this architecture:

There are also other problems in this scheme, but solving them does not greatly affect the original goal.

I mentioned the Extensions component, but did not specify how it works and what reasons lie behind the features of its operation.

Extensions are two Arenadata DB components: gpperfmon and gpadcc (Arenadata DB extension). They collect internal Arenadata DB telemetry, which includes:

Metrics are collected using two approaches: push and pull models.

Next, we began to study the composition and amounts of data taken from Arenadata DB for monitoring tools. Metrics can be transmitted via two types of sockets: UDS and UDP (the latter is used if the former is not available). Due to a single packet size limit for UDP (just under 64 KB for Linux), a proprietary protocol is used to split messages into parts and then reassemble them.

To collect the payload, we disabled data transfer via UDS, ran regression tests, and used the tcpdump utility to collect dumps of files with messages on the master and one segment. The resulting files could then be used as data sources via udpreplay.

Analysis of the dumps showed the following:

 
We have unpleasant consequences:

Before you start designing a system, it is important to make a list of requirements that it must meet. Otherwise, you can develop something that will simply stay at the table. But requirements do not always mean restrictions — some of them can be regarded as relaxations, which, in turn, give more freedom in design and implementation. Let’s look at an example of one of these properties.

In our case, the monitoring system should be:

Let me look at the last three points in more detail.

The complexity of a system means many things — it includes the total number of components (both internal and third-party), the interaction between them, the labor intensity of support, and almost inevitable changes in the infrastructure requirements for the system.

Updating the system should also be thought out in terms of data migration, downtime, the ability to return to a previous version, etc.

Since ADB Control is a monitoring system, it is also imposed time requirements. In our case, it can be soft. A delay of 1 second in displaying metrics on the client is not critical for a user, since there is no point in tracking very fast queries online (they can be viewed in the query history) and such delay is insignificant for long queries. This relaxation gives much more freedom in implementation.

As a result, we drew up a concept for a new ADB Control architecture.

Components of the new architecture:

In general, the sequence of receiving and processing metrics has not changed, but if we go into detail, there are quite a lot of distinctive features. Below we will look at some of them:

We have implemented a system that not only solves the performance problems of the previous version, but is also ready to process much larger amounts of data. The first version could not handle the load if you enable the GUC gp_enable_gpperfmon to collect information about the progress of a query execution periodically (without this option, the query state comes only at the time it begins to execute and at its completion). In the new version, even with minimal system configuration, enabling gp_enable_gpperfmon does not lead to problems — this makes possible to monitor the execution status of query nodes in near-online mode using a graphical representation.

Monitoring queries in a distributed system poses many challenges and difficult technical solutions. As an additional complexity, such systems are associated with processing large amounts of data received from an entire cluster (and sometimes multiple clusters). But in general, such complex tasks are very interesting and allow us to improve the monitoring system, providing users with various levels of detail and understanding of the cluster operation. We have a large number of ideas that we are gradually implementing, improving the quality of ADB Control and taking into account our expertise in understanding the work of Greenplum.