Introduction

User groups are database items that represent a collection of user accounts. They are designed to make it easier to allocate security permissions to multiple user accounts. Typically, you create a user group to represent a group of users. By granting security permission to it, you can reduce the need to configure security permission for individual user accounts. It is good practice to create user groups with just a single user because, if needed, it can be scaled easily by adding more users while having permissions already set.

While user groups are something that can be found across nearly every database, IBM’s Netezza, besides user groups, has something specific called resource groups. Resource groups are, just like user groups, database items that represent a collection of user accounts, but they have server management resource values assigned. Besides resource minimum and maximum, there are some additional important settings related to resource groups that we can set up:

• QUERYTIMEOUT.
  o The number of minutes that a query can run before the system notifies the administrator.
• SESSIONTIMEOUT.
  o The number of minutes that a session can be idle before the system terminates it
• CONCURRENT SESSIONS.
  o The maximum number of concurrent sessions a member of this group can have.
• JOB MAXIMUM.
  o The maximum number of concurrent jobs that can be run by the resource group.

Usage of resource groups

To explain usage of resource groups, we can imagine a system that is used by three types of users:

• Data analysts
• Report users
• All other users.

The groups are created using simple SQL commands. Besides creating resource groups, we must also assign users to them.

Following are command examples we can use:

# create resource group - minimum > 0

CREATE GROUP RSG_ANALYSTS WITH RESOURCE MINIMUM 50 RESOURCE MAXIMUM 100;

# add user to resource group

ALTER USER test_user IN RESOURCEGROUP RSG_ANALYSTS;

# to remove user from resource group, we have to move it back to default group - PUBLIC

ALTER USER test_user IN RESOURCEGROUP PUBLIC;

By assigning minimum values to resource groups, the system ensures that the members of the group will get at least minimum value of the net system resources. For example, if we have three groups:

Table 1. Example of resource groups

when a user from the RSG_ANALYSTS group executes a query, it is guaranteed at least 50% of the net system resource. Allocating minimum and maximum values to resource groups is also called guaranteed resource allocation or GRA.

When some resource groups are idle, an active resource group receives additional resources. If only a few of groups are busy, the system has more resources to give to the active resource groups. The system then scales the minimum resources percentages based on the set values. For example, if the only active groups are RSG_ANALYSTS and RSG_REPORTING, the actual given resources would be:

Table 2. Actual minimum values.

The system frequently adjusts the percentage of resources allocated to each group based on the currently active resource groups and their plans. Within the resource group, the number of concurrently active plans affects the resources that are applied to each plan. Meaning, the resources allocated for a resource group are shared among the active plans for that group. For example, if RSG_ANALYSTS (minimum resources 50%) group has ten active plans that all have the same priority and all groups are busy, each plan will get 1/10 of resources allocated to that group - 5%. Because of this, we must take into consideration the number of active concurrent plans (JOBMAX) for resource group while setting up minimum value. It is also important to keep in mind that ADMIN user is treated as a resource group with resource minimum of 50%. In general, we should avoid using ADMIN user to run queries on the system.

However, there is a case where concurrent plans have different priorities. In this case, the system allocates the resources within the group by using priority factors. Default priority set to resource group is Normal and default maximum priority is Critical. Maximum priority is the highest possible priority that can be set for any job within resource group.

Table 3. Priorities and weights.

For example, group RSG_ANALYSTS (minimum resources 50%) have two concurrent plans where first has normal priority and second one critical. The actual given resourced would be:

Table 4. Net system resources with priority applied.

Besides minimum, every resource group can have maximum set as well. The maximum resource percentage is the largest percentage of available system resources that the resource group should receive, regardless of whether other resource groups are using the system. It is no surprise that sometimes a resource group can receive more than a minimum percentage allocated (see Table 2 as an example), but a resource group cannot receive more than its configured maximum percentage.

In general, setting maximum value to anything less than 100% is not recommended. We can easily imagine a simple query being the only active query on the system, but still receiving only percentage set as maximum while more is available. The only case for setting maximum below 100% is to create a resource group where we would have users causing long running queries or using too many resources for no reason.

Dynamic GRA allocation.

In many cases, the workload on the system is divided throughout a day and it is no surprise to have system running queries 24/7. However, in most cases, we can see jobs being scheduled based on their purpose. For example, we can have ETL jobs scheduled to be running during the night and reporting jobs during the day. If we know for a fact that reporting will have just a few or no jobs running during the night, can we take resources from that group and increase our ETL group?

By combining bash scripting, Crontab and our knowledge of the Netezza system we are working on, we can easily move minimum percentages from one group to another based on their job schedule.

Crontab example

# increase reporting

0 7 * * * ~/adjust_resources.sh 60 40 &>> ~/increase_reporting.out

# increase etl

0 1 * * * ~/adjust_resources.sh 10 90 &>> ~/increase_etl.out

Script example

### ~/adjust_resources.sh

#!/bin/bash

REPORTING_MIN=$1

ETL_MIN=$2

# print date to the logfile

date

echo "Setting up RSG_REPORTING MINIMUM to $REPORTING_MIN"

nzsql -Atc "ALTER GROUP RSG_REPORTING WITH RESOURCE MINIMUM $REPORTING_MIN;"

echo "Setting up RSG_ANALYSTS MINIMUM to $ETL_MIN"

nzsql -Atc "ALTER GROUP RSG_ANALYSTS WITH RESOURCE MINIMUM $ETL_MIN;"

echo "Script ended!"

echo "------------------------"

Conclusion

Setting up your resource groups on Netezza is a very important task and is something that will be crucial to achieve the best performance possible. Unlike some other tasks, there is no universal way of setting them up. Of course, there are some guidelines, but the actual percentages and additional scripts to modify them must be done for every system differently. Besides minimum and maximum values, we must have in mind that Job Max value will be very important since the actual net system resources will be divided by every running job within each resource group taking priority into consideration. Setting actual values is a process and something that should be revised every once in a while, because we have changes in data, ETL, and reporting on a daily basis. We hope this article helps you streamline your usual processes and if you have any questions, feel free to reach out to us.