09 May

A New Era of Greenplum Monitoring & Workload Management – Greenplum Command Center v4

We’re excited to announce the Pivotal Greenplum Command Center v4 release. It is available for download from Pivotal Network for Enterprise Greenplum 5.7 or later.

Quickly identify and troubleshoot problematic queries with ease with new query monitoring capabilities. New workload management features improves mixed workloads handling, system resource management, and SLAs support.

 

Monitor Queries in Real-time

For Command Center v4, the query monitoring happens in real time. Now, queries will immediately appear on the Query Monitor when submitted to GPDB. There is no longer a minimum required runtime for queries to show up on the Query Monitor.

Long and short running queries mix on the query monitor.

 

Visualize Locking & Blocking

Do you have queries running for a long time because of complicated chains of locking and blocking? The Command Center now exposes the locking and blocking behavior of queries.

The Query Monitor indicates queries that are blocked from execution and the offending blockers. You can drill down the list of blocking queries to build a clear chain of locking and blocking – expose the key blocker preventing other queries from execution.

A single query here is blocking the others from execution. you can see the query ID of the blocker and drill into its Query Detail page.

 

A complete list of locking and blocking information for a single query is found on the Query Details page.

The Blocking (#) section shows a list of all queries that are currently blocked by this query.

The Blocked By (#) section lists queries that block the current query from execution.

Completing or cancelling the queries in the Blocked By section will allow other queries  to resume execution.

 

Understand Query Performance

You can drill down into the Query Details page for a single query to see more information. A variety of query performance details are available to diagnose the problem.

Drill into query details for any query to see more information about performance and diagnose any problems.

 

CPU % is the average of all processes on each segment for a query.

Memory is the total memory consumed by all segment processes executing a query.

 

Unexpectedly high utilization for CPU or memory is generally indicative of problems with query performance.

For additional information, you can look to the CPU Skew metric show in the performance details.

CPU skew occurs when one segment uses up more CPU than other segments executing this query. Often, CPU skew points to deeper problems within the database such as bad data distribution and hardware malfunction. It is calculated as 1 − (average segment CPU / maximum segment CPU).

Note that since the CPU skew is sampled periodically during the course of query execution, a single number for high CPU skew may not be problematic. If CPU skew remains high throughout the course of query execution, further investigation may be required.

 

View Spill Files

A good way to identify problematic queries is seeing if any queries are spilling to disk. On the Query Monitor, you can see the total amount of spill files on the cluster for each query. Spill files can indicate memory management issues. Additionally, queries generating large amounts of spill can fill up the disk quickly and crash the database.

As this query continues to generate a large amount of spill files, you can investigate by drilling down into the details page.

 

Manage Greenplum Workloads

Workload Management in Command Center is built on top of Resource Groups in GPDB 5. Resource groups replaces the use of resource queues and allows the user to compartmentalize cluster resources such as CPU and memory. Once enabled, user queries are routed to a resource group based on role assignments specified by the administrator.

GPCC workload management allows you to apply additional rules on top of resource groups to provide more robust query routing and resource management.

For example, you can augment the GPDB role-based query assignments with query tag assignment filters. When query tags filters are matched, they supersede GPDB roles to route a query to the corresponding resource group. Query tags allow you to fine tune resource allocation based on any criteria such as user group, business unit, or incoming application.

How a query is routed to a resource group with GPCC query tags.

 

Additionally, you can now set an idle session timeout duration for a resource group. After setting a session timeout duration, GPCC will terminate the connection automatically based on your specified time.

Please refer to GPCC Workload Management documentation to view the currently available features for workload management.

Through GPCC workload management, it becomes easy to manage, understand, and tune the usage of your resource groups.

 

Greenplum Command Center strives to be the “Captain’s Chair” of your Greenplum clusters — an intelligent monitoring and management interface that frees you to make the important decisions. Please stay tuned for more exciting features coming to GPCC in the next few releases.

 

For more information:

Learn more about Pivotal Greenplum

Download Pivotal Greenplum and Greenplum Command Center

Read Pivotal Greenplum Command Center Documentation

Leave a Reply