About Greenplum Command Center Workload Management

Greenplum Database segment hosts have a set amount of memory, CPU, I/O, and network resources. If these resources start to run short due to heavy database load, queries can fail or the entire database system can become unacceptably slow. For this reason, it is important to manage database requests to prevent resource depletion.

Greenplum Database has two resource management systems: resource queues and resource groups. Command Center workload management is based on resource groups, the resource management system introduced in Greenplum Database version 5. Resource groups require enabling Linux control groups (cgroups), so Greenplum Database initially is set to perform resource management using resource queues.

When Command Center starts, it checks the resource management system enabled in your Greenplum Database system. If you have not yet enabled resource groups in your Greenplum Database system, the Command Center workload management view displays a message encouraging you to enable resource groups, including a link to documentation with the procedure for completing the task. When you start Command Center after enabling resource groups, click the ENABLE WORKLOADS button. Command Center presents a view to help you set the intial resource group configuration by importing your existing resource queues to resource groups.

See Using Resource Groups in the Greenplum Database Administrator Guide for a full description of resource management features available with resource groups and instructions to enable resource groups in Greenplum Database.

On the Command Center Workload> Resource Groups view, you can perform the following tasks:

  • Create new resource groups in Greenplum Database
  • Delete existing resource groups
  • Change the number of concurrent transactions each resource group allows
  • Change the percentages of available system CPU and memory each resource group manages
  • Change the default resource group assignment for Greenplum Database roles

See Resource Groups for details about creating resource groups and query timeout rules.

For more information about Linux cgroups and Greenplum Database resource groups see Using Resource Groups in the Greenplum Database Administrator Guide.

On the Command Center Workload> Workload Mgmt view, you can perform the following tasks:

  • Create workload management rules to cancel or move queries resource groups, based on conditions such as the amount of CPU time used, planner cost, query running time, and disk I/O utilitization.
  • Monitor log details about when workload management rules perform actions against matching queries.

Query management rules is a Greenplum Command Center resource group enhancement that you can use to more flexibly assign transactions to resource groups or cancel queries. The following sections provide more information about these Command Center features.

About Query Assignment Rules and Workload Rules

Greenplum Database defers to the workload management database extension to assign transactions to resource groups. Command Center users with Admin permission level can create assignment rules in Command Center to assign transactions to resource groups before they run.

When a transaction begins, Greenplum Database calls the metrics collector extension to determine the resource group. The extension evaluates the assignment rules and, if a matching rule is found, returns that rule’s resource group. If no assignment rule matches, Greenplum Database falls back to the default behavior, assigning the transaction to the resource group specified for the current user in the pg_authid system table.

Assignment rules can redirect a transaction to a resource group based on any combination of query tag and/or the Greenplum Database role that executes the transaction.

A query tag is a user-defined name=value pair that you can set in a Greenplum Database session when resource group-based workload management is enabled. Query tags are defined by setting the gpcc.query_tags parameter on the connect string when requesting a database connection or in the session with SET gpcc.query_tags TO '<query-tags>'. Multiple query tags can be set by separating them with a semicolon. Query tags are set before a transaction begins and cannot be changed inside of a transaction.

If multiple assignment rules would match the same query, Command Center matches only the first assignment rule (the rule with the lowest ID number) to the query.

See Workload Management for details about creating assignment rules, and for examples that use query tags.

Workload Rules

Beginning with Command Center version 6.3, you can configure workload rules as well as query assignment rules. A workload rule defines additional conditions after which a running query may be canceled or (for Greenplum versions 6.8 or later) moved into a different resource group.

Similar to query assignment rules, workload rules are first matched to a transaction based on any combination of the database role, query tags, and/or the initial resource group where the transaction begins to execute. After the transaction execution begins, additional conditions are evaluated to determine if configured CPU time, I/O, slice, and/or planner cost limits are exceeded.

If all configured conditions for a workload rule are exceeded, the rule action action is performed against the transaction. Available actions are either cancelling the transaction or, for Greenplum versions 6.8 or later, moving the transaction to a different resource group.

Command Center always ensures that a given workload rule is applied only once to a matched transaction. For example, two workload rules could be created with one rule that moves queries from resource group A to group B based on planner cost, and another rule that moves queries from any resource group to group A based on the query running time. The first of these rules would only be applied to a given query once, even if the second rule again placed the query back into resource group A.

See Workload Management for details about creating workload rules.