Workload Management
Defining Resource Groups and Resource Attributes
Command Center allows you to view resource groups that have been created in Greenplum Database, to add or delete resource groups, and to edit the resource group attributes Concurrency, CPU %, and Memory %.
To change values of the MEMORY_AUDITOR
, CPUSET
, MEMORY_SHARED_QUOTA
, or MEMORY_SPILL_RATIO
resource group attributes, use the ALTER RESOURCE GROUP
SQL command.
Click EDIT to open the Resource Group editor.
To delete a resource group, select the resource group, and click the minus sign that appears at the right.
You cannot delete the
default_group
oradmin_group
resource groups. You cannot delete a resource group that is assigned to any Greenplum Database role.To add a resource group, click ADD RESOURCE GROUP and enter a name for the resource group in the Name column. Resource group names must be unique and are case-sensitive.
Adjust the values of the Concurrency, CPU %, and Memory % resource group attributes.
Concurrency
- The maximum number of concurrent transactions, including active and idle transactions, that are permitted in the resource group. Concurrency sets the
CPU %CONCURRENCY
attribute of the resource group. The total of the Concurrency columns cannot exceed the value of the Greenplum Databasemax_connections
master server configuration parameter.
- The percentage of CPU resources available to this resource group. The percentage is the portion of the total CPU percentage allocated for all resource groups (reserved CPUs excluded), which is set with the
Memory %gp_resource_group_cpu_limit
server configuration parameter. CPU % sets theCPU_RATE_LIMIT
attribute of the resource group.
- The percentage of memory resources available to this resource group. The percentage is the portion of the total memory allocated for all resource groups, which is set with the
Min memory per querygp_resource_group_memory_limit
Greenplum Database configuration parameter. Changing the Memory % value sets theMEMORY_LIMIT
attribute of the resource group.- The minimum amount of memory allocated to a query. This column is recalculated as you adjust Concurrency and Memory % settings. The value is the resource group’s total share of system memory, less the resource group’s shared memory pool (20% by default), divided by the value in the Concurrency column. The percentage of memory allocated to the shared memory pool can be changed by setting the
MEMORY_SHARED_QUOTA
attribute of the resource group using the ALTER RESOURCE GROUP SQL command. Each query managed by the resource queue is allocated this amount of memory. If a query needs more memory, it is allocated from the resource group shared memory pool and the global shared memory pool, if available.
The totals of the CPU % and Memory % columns must not exceed 100%. You should not allow the total of the CPU % column to exceed 90%, because this could cause resource-intensive queries to consume nearly all CPU, starving other Greenplum Database processes. If the total of the Memory % column is less than 100%, the unreserved memory is part of the resource group shared global memory pool. See “Global Shared Memory” in Using Resource Groups in the Greenplum Database Administrator Guide for information about the global resource group shared memory pool.- The maximum number of concurrent transactions, including active and idle transactions, that are permitted in the resource group. Concurrency sets the
Click Apply to save your changes or click Cancel to abandon your changes.
Assigning Roles to Resource Groups
Every Greenplum Database role is assigned to a single resource group in the pg_roles
system table. Transactions executed by a role are managed by its assigned resource group, unless you create an assignment rule to override the default.
You can view the current resource group assignments for all roles and change a role’s resource group by adding it to a different resource group.
To move a role to a different resource group:
Enter all or part of the role name in the add role field beneath the new resource group.
Choose the role from the list that is displayed and press Enter.
The change is immediately applied to the Greenplum Database pg_roles
system table.
Defining Workload Management Rules
Workload management rules include query assignment rules and timeout rules.
Query assignment rules allow you assign transactions to a resource group based on user-defined query tags and, optionally, the current role in the database session. When no rule matches, the transaction is assigned to the role’s default resource group. See About Assignment Rules for more information about assignment rules.
Timeout rules set the amount of time a session can be idle before it is terminated. You create a timeout rule for each resource group. See About Timeout Rules for more information about timeout rules.
See Accessing the Workload Configuration Programmatically for information about retrieving and setting rules programmatically with database functions.
Click EDIT to open the Workload Management Rules editor.
To delete a rule, select the rule and click the minus sign that appears at the right.
To add an assignment rule, click ADD ASSIGNMENT RULE and fill in the fields.
Query Tags- The first field is a list of query tags to match against the
Rolegpcc.query_tags
parameter in the Greenplum Database session. A query tag is a user-defined <name>=<value> pair. Separate multiple query tags with semicolons. See Defining and Setting Query Tags for more information about query tags.
- (Optional) If you enter a role name in this field, the rule matches only if both the query tags and role match the tags and current role in the database session.
Resource Group- Choose a resource group from the list.
- Change the order of the assignment rules by dragging a rule’s handle (at the left) up or down. Assignment rules are evaluated from top to bottom. Greenplum Database applies the first rule that matches.
- Use the Active/Inactive toggle to make a rule active or inactive.
- The first field is a list of query tags to match against the
To add a timeout rule, click ADD TIMEOUT RULE and fill in the fields.
Resource Group- Choose a resource group from the list.
Time before idle connections killed- The amount of time before an idle session is terminated. Enter an integer value for the number of seconds or minutes and choose minutes or seconds from the list. A value of 600 seconds, for example, terminates the session after it has been idle for 10 minutes. See About Timeout Rules for warnings about setting timeouts for the
Exempted rolesadmin_group
resource group and resource groups with roles that require persistent sessions.- The rule is not applied to roles added to this field. Open the list to choose roles to exempt from the rule. Only roles for the selected resource group are included in the list. To remove a role from the field, click the x next to the role name.
Message- Enter the message to log when a session is killed by this rule.
Click APPLY to save your changes.
Defining and Setting Query Tags
A query tag is a user-defined <name>=<value> pair, set in the Greenplum Database gpcc.query_tags
parameter in the Greenplum Database session. The gpcc.query_tags
parameter is defined when the gp_wlm
database extension is enabled in the postgres database. If you try to set query tags when the gp_wlm
extension is not enabled, you get an unrecognized configuration parameter error. To see if the extension is enabled, run the following command.
$ psql postgres -c "\dx"
List of installed extensions
Name | Version | Schema | Description
--------+---------+--------+--------------------------------------
gp_wlm | 0.1 | gpcc | Greenplum Workload Manager Extension
(1 row)
When you submit a transaction and the gp_wlm
extension is enabled, Greenplum Database calls the gp_wlm
extension to determine the resource group for the transaction. The extension evaluates the current role and query tags set in the session against the rules you have defined in Command Center. If there is a match, the extension returns the rule’s resource group. If there is no match, Greenplum Database assigns the transaction to the role’s default resource group.
The following command, executed in the Greenplum Database session, sets the appName
and appUser
query tags to “tableau” and “bi_sales”, respectively.
=# SET gpcc.query_tags TO 'appName=tableau;appUser=bi_sales';
To match a rule, all tags in the rule’s query tag field must be present in the gpcc.query_tags
parameter in the database session. The order of the tags is not significant, and the gpcc.query_tags
parameter can have a superset of the tags defined in the queryTags
value.
If you set the gpcc.query_tags
parameter inside of a transaction, you must commit the transaction before the new query tags are used to evaluate assignment rules.
You can set the value of the gpcc.query_tags
parameter using the SET
command, as in the example above, or as a connection parameter with database clients that support it, such as psql
. Following are two examples that show how to specify query tags on the psql
command line.
$ PGOPTIONS="-c gpcc.query_tags='appName=tableau;appUser=bi_sales'" psql
$ psql postgresql://mdw:5432/postgres?options="-c gpcc.query_tags%3D'appName%3Dtableau;appUser%3Dbi_sales'"
In the second example, it is necessary to code the equals signs as %3D
to prevent psql
from interpreting the query tags as command-line arguments.