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.
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
- Set the amount of time a session can be idle in a resource group before it is killed
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 a query or move it to a different resource group, based on conditions such as the amount of CPU time used, planner cost, query running time, and disk I/O utilitization.
- Create workload management rules to terminate an idle session based on which resource group it belongs to and how long it has been idle.
- Monitor workload management rules when they perform actions against matching queries or idle sessions.
Workload management rules allow you to flexibly assign transactions to resource groups or cancel queries or idle sessions. 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 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 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
A workload rule defines:
-
For workload rules for queries, additional conditions beyond query assignment rule conditions under which a running query may be canceled or (for Greenplum versions 6.8 or later) moved into a different resource group
Workload rules for queries are first matched to a transaction based on any combination of the database role and query tags. 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. -
For workload rules for idle sessions, the conditions under which an an idle session may be terminated
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.
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.
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.
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.
