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 Admin> Workload Mgmt 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 Greenplum Database roles’ default resource groups
  • Write query assignment rules to override a user’s default resource group when a transaction executes

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

About Query Assignment Rules

Greenplum Database defers to the metrics collector 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.

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 query tags or a combination of query tags and the Greenplum Database role executing 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.

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