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, when you open the Command Center workload management view, you will see a message encouraging you to enable resource groups, including a link to documentation with the procedure for completing the task. Then, when you start Command Center after enabling resource groups, you can click the Enable Workloads button to get help converting the 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 kill 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 queue enhancement that you can use to more flexibly assign transactions to resource groups. With idle session kill rules you can set the number of seconds a session managed by a resource group is idle before it is killed. The following sections provide more information about these Command Center features.
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 queue 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.
Command Center administrators can create idle session kill rules for resource groups that specify the maximum number of seconds that a session can remain idle before it is killed. When the session process on the Greenplum Database master becomes idle and the number of seconds has elapsed, the session kills itself. Sessions that become idle while inside of a transaction are not subject to session kill rules.
Idle session kill 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 idle session kill 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 kill 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](ui/workload-management.html) for details about creating idle session kill rules.