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
  • Write idle session timeout rules for each resource group to set the amount of time a session can be idle before it is killed

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

Query assignment rules is a Greenplum Command Center resource group enhancement that you can use to more flexibly assign transactions to resource groups. With idle session timeout rules you can set the amount of time a session managed by a resource group is idle before it is terminated. The following sections provide more information about these Command Center features.

About Query Assignment 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.

When a transaction begins, Greenplum Database calls the workload management 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 the workload management database extension 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.

About Timeout Rules

Command Center administrators can create session timeout rules for resource groups that specify the maximum time that a session can remain idle before it is terminated. When the session process on the Greenplum Database master becomes idle and the specified time has elapsed, the session terminates itself. Sessions that become idle while inside of a transaction are not subject to timeout rules.

Session timeout rules are per resource group, allowing you to determine how long to wait for different classes of transactions. You can include a list of Greenplum Database roles to exempt from the rule and a custom message to add to the session termination error output.

Creating timeout rules for the admin_group resource group is not recommended. Avoid creating rules that kill sessions started by Greenplum Database client applications that are designed to have a long-lasting or persistent session. For example, the gpmmon process creates a session as the gpmon role to update the gpperfmon database. An idle session timeout rule that kills idle gpmon sessions too quickly could cause Greenplum Database to log many session termination messages and unnecessarily create new sessions. See Workload Management for details about creating timeout rules.