netezza workload management

Netezza Workload Management

1 © sieac llc

Keeping work load management of the appliance in-line with change in work load is a key administrative

function so that users are serviced efficiently. This section will detail the workload management options

available in the Netezza appliance using some example scenarios.

Maximum number of Concurrent Jobs The maximum number of concurrent jobs which can be run on a Netezza system is restricted by the

system registry setting “gkMaxConcurrent”. The default value is 48 and that means if more than 48 jobs

are requested to be executed, additional jobs are queued for execution. As and when jobs executing in

the system gets completed the jobs on queue will be scheduled to run. If all the jobs were executed by

users who are members of the default public group and no other workload management related features

are configured, all the 48 jobs gets executed with equal priority and every one of the jobs will get 1/48th

of the available resources. The number of maximum concurrent jobs can be changed by changing the

“host. gkMaxConcurrent” parameter value using the “nzsystem set -arg” command. This will require a system

pause and restart. If the maximum number of concurrent jobs allowed to run in the system is kept

smaller, it may help with the system performance since there will be more resources available for each

job.

Resource Sharing Groups Similar to setting up of groups to manage user access restrictions, Netezza allows resource group

definitions to control the resource usage of users in the system. “Resource Sharing Groups (RSG)” helps

allocate resources disproportionately to various jobs by attaching the users running the jobs to particular

resource group. Note that each user can be attached to only one “resource group” compared to access

groups where users can be part of many. Resource Sharing groups provide the options to control the

Rowsetlimit –max number of rows any query executed by the users in the group can return

Sessiontimeout –max time sessions of users in the group can be idle before it gets cancelled

automatically

Querytimeout – max time queries executed by users in the group can run before it gets cancelled

Defpriority – default priority if queries executed by users in the group if none explicitly

mentioned. It can have the values critical, high, normal, low which is in reverse order of priority

Maxpriority – max priority the users in the group can have and the acceptable values are same as

the Defpriority.

Resource minimum – minimum percentage of system resources guaranteed to the queries run by

the users in the group when the system is fully (100%) utilized

Resource maximum – the maximum percentage of system resources guaranteed to the queries

run by the users in the group when the system is not fully utilized

Job maximum – the maximum number of concurrent jobs which can be executed by the users in

the group and currently it has a limit of 48 max which is hardcoded


2 © sieac llc

Netezza Schedulers There are multiple queues in Netezza to help configure and schedule queries and jobs based on the usage

pattern. Understanding them will help in coming up with a configuration which can provide optimal user

response to their queries.

Gate Keeper: Without any additional configuration, gate keeper acts as a queue to control the total

number of concurrent jobs/queries executed in the system. As detailed earlier the maximum number of

concurrent jobs/queries by default is 48 and it can be lowered by modifying the “host. gkMaxConcurrent”

registry setting. When more than the maximum number of allowed queries arrives, they get queued in

Gate Keeper and will get scheduled when one or more of the jobs currently running in the system

completes.

GRA Scheduler: GRA (Guaranteed Resource Access) scheduler manages the system resource allocation

for the jobs based on the resource group definition of the user executing the job belongs to. Once the

“Gate Keeper” identifies that the job can be scheduled since the number of jobs running in the system is

less than the maximum allowed, the job gets passed to the GRA scheduler that allocates the resources

for the job before scheduling it to run. If no resource groups are defined and if “n” (where n < 48)

number of jobs arrives for execution, then each of the jobs will be allocated 1/nth of the total resources

available in the system.

Priority Query Execution (PQE) combined with GRA prioritizes the execution of queries based on the

query priority. When PQE is enabled which is the case by default, when queries with different priorities

from users of the same resource group arrives for execution, the queries with high priority will be

scheduled in advance to the ones with low priority. Also when PQE is enabled, if the jobs are assigned

different priorities, the GRA uses the priority to allocate the system resources disproportionately based

on priority i.e critical jobs will receive more resources compared to low priority jobs.

Snippet Scheduler: The snippet scheduler is ultimately responsible for the scheduling of the snippets

belonging to a query/job on the SPU in appropriate sequence.

The following diagram depicts the various Netezza scheduling components.

Host SPUs

CPU

Memory

Disk

Gate KeeperGRA+PQE

Scheduler

Snippet

Scheduler

User

Query

SQB: Even though Netezza is an appliance for data ware housing scenarios dealing with processing of

large volume of data, there can be user queries which can be satisfied in a very short duration (in

seconds). In order to satisfy the short duration queries and not delayed due to other long running queries


3 © sieac llc

overwhelming the system, Netezza has a feature called Short Query Bias “SQB”. SQB allows the

appliance users to define what a short duration query means in terms of execution time and how many

slots in the GRA and Snippet Scheduler queues along with the SPU memory need to be reserved for the

short queries. Once defined any query which Netezza estimates to complete with in the defined short

duration, will be queued using the reserved slots so that they get scheduled appropriately without waiting

in the normal queue where all the other queries are queued for execution preventing delays in servicing

the short queries.

The following is the updated diagram with SQB defined and the allocations are highlighted in red.

Host SPUs

CPU

Memory

Disk

Gate KeeperGRA+PQE

Scheduler

Snippet

Scheduler

User

Query

The system registry settings which can be used to manage the SQB allocations are detailed below

Parameter Description

host.schedSQBEnabled To enable or disable SQB and by default it is “true”.

host.schedSQBNominalSecs Time below which the query is considered short. Default value is 2 secs.

host.schedSQBReservedGraSlots Number of GRA scheduler slots reserved for short queries. Default value is 10.

host.schedSQBReservedSnSlots Number of snippet scheduler slots reserved for short queries.. Default value is 6.

host.schedSQBReservedSnMb Amount of SPU memory reserved for short queries. Default value is 50 MB.

host.schedSQBReservedHostMb Amount of host memory reserved for short queries. Default value is 64 MB.

Putting things together Having gone through the basics of the work load management components available in Netezza we can

use it to come-up with a workload management configuration for the following scenario so that it will be

clear on how these components work together. This will also help in understanding the details one need

to look at to configure the work load management.

The scenario we will be dealing with is an environment where there are two data warehouses (DW)

hosted on a Netezza server on separate databases. As with any DW environment data is ETLed or

ELTed into the tables in batch mostly nightly and during the day users runs queries against the data to


4 © sieac llc

perform analytics through BI tools. Data for one of the ware houses, DW1 need to get loaded during the

day when data is made available at certain intervals and the volume of user interaction during the day is

also high against DW1. Data for the second DW, DW2 needs to get loaded before users start querying

the data at 8:30 AM in the morning daily. The normal work day for users using both the DW is from

8:30 AM to 5:30 PM and both the DWs are of equal priority for business.

The business users can be categorized as normal users who bring up standard pre-built reports which

allow then to perform drill-down to aggregated data which in turn runs relatively short queries. The

second set of users is power users who does detailed analytics and data discovery which involves long

running queries. Other than business users, DW production support team members and administrators

are the other users of the Netezza system. Having gathered the basic information about the system usage

we can define the resource groups required to manage the resource allocation so that the user experience

and the data load requirements can be met. One way to do is to define the resource groups

RSGBAT1 & 2 – The resource sharing groups to which users running the batch jobs for the two DWs

can be attached to.

RSGPOW1 & 2 – The resource sharing groups to which power users of the two DWs can be attached

to.

RSGNOR1 & 2 – The resource sharing groups to which normal users of the two DWs can be attached

to.

RSGADM1 & 2 – The resource sharing groups to which production support and admin users of the two

DWs can be attached to.

By default the user group “PUBLIC” gets created which can be altered to have resource allocation

defined to the group.

Given that there are business users and support users access the system, the system resource can be

allocated in the ratio 90:5:5 between business, public and support users. So the resulting resource

allocation looks as follows

Business Users Public Support Users

90% 5% 5%

Since both the DWs are important to business, the resource allocation among the business user group

can be divided equally for the two DWs. After the split the resource allocation will be as follows

Business Users Public Support Users

DW1 DW2 N/A N/A

45% 45% 5% 5%

From the usage perspective we understand that there can be batch processes, queries from power users

and normal users hitting the server. Also based on whether it is during day time or night the usage varies.

For simplicity we assume that there can be no user activity during night i.e. from 5:30 PM to 8:30 AM all

the business user related resources can be allocated to the resource sharing groups related to batch. The


5 © sieac llc

following is the min – max percentage of resource allocation to all the resource sharing groups in the

system.

Business Users Support Users

DW DW1 DW2 DW1 & 2

RSG RSGBAT1 RSGPOW1 RSGNOR1 RSGBAT2 RSGPOW2 RSGNOR2 Public ADM

Night 44% - 90% 1%-1% 1%-1% 44%-90% 1%-1% 1%-1% 1%-9% 9%-90%

Day 10% - 35% 25% - 45% 10% - 45% 15% - 45% 25% - 45% 5% - 45% 5%-10% 5%-10%

During work hours, the resource sharing groups to which business users are attached to get allocated

higher percentage of system resources and the RSG used by the batch user ids get a higher percentage of

the system resources during the night. Once RSGs are defined, the resource allocation limits are altered

between work hours and night using the ALTER GROUP statement.

Even though we are looking at the system resource allocations to control the resource usage of various

user groups, the resource usage can also be restricted using the rowset limit, query timeout limit and

session timeout limit parameters. Setting these limits will help minimizing issues due to runaway queries

and bad queries entered by users which can run for ever holding on to the resources.

Once the resource sharing groups are allocated and all the users are attached to the correct groups, let’s

see how the resource allocation will happen when queries start arriving for execution during work hours.

Assuming that all the queries executed by the users are of the same priority and 2 queries one from

power user and one from normal user of DW1 and similarly 2 queries from DW2 users are entered into

the system the following will be the resource allocation for the 4 queries

Minimum resource (under 100% system usage) which will be allocated to the queries from the

different RSGs are

o QUERY1 - RSGPOW1 – 25%

o QUERY2 - RSGNOR1 – 10%



Given that 4 queries take up only 65% of the system resources, the queries will be allocated

remaining resources proportionally up to the resource maximum defined for the RSGs to which

the users running the queries are attached to. The following is the additional resource from the

remaining 35% (100%-65%) resource which will be allocated to each query

o QUERY1 - RSGPOW1 – (25*35)/65 = 14%

o QUERY2 - RSGNOR1 – (10*35)/65 = 4%

o QUERY3 - RSGPOW2 – (25*35)/65 = 14%

o QUERY4 - RSGNOR2 – (5*35)/65 = 3%

So the total resource allocation for each query will be

o QUERY1 - RSGPOW1 – 25%+14% = 39%

o QUERY2 - RSGNOR1 – 10%+4% = 14%

o QUERY3 - RSGPOW2 – 25%+14% = 39%

o QUERY4 - RSGNOR2 – 5%+3% = 8%


6 © sieac llc

In the next scenario, let’s assume that there are 2 queries with same priority from users in the RSG -

RSGPOW2 and 1 query from all the other RSGs are executed in the system. In this case the following

will be the resource allocation percentages



o QUERY3 – RSGBAT1 – 10%

o QUERY4 - RSGPOW2 – 12.5%

o QUERY41-RSGPOW2 – 12.5%


o QUERY6 - RSGBAT3 – 15%

o QUERY7 - PUBLIC – 5%

o QUERY8 - ADM – 5%

Note that the system is 100% utilized and the queries are allocated the resource minimum defined in the

RSG definition of the users executing the queries. Since there are two queries of equal priority executed

by the users in the same RSG (RSGPOW2), the resource allocated to the group 25% is equally split and

allocated to the two queries which is 12.5%.

In the third scenario, let’s assume that it is same as the second scenario except that the two queries from

the users in group are executed with different priorities critical and low. Netezza assigns a weight of

1,2,4,8 to the priorities low, normal, high and critical and uses it to allocate the resource proportionately.

As a result, the following will be the resource allocation percentages.



o QUERY3 – RSGBAT1 – 10%

o QUERY4 - RSGPOW2 – (8/(8+1))*25 = 22%

o QUERY41-RSGPOW2 – (1/(8+1))*25*=3%


o QUERY6 - RSGBAT3 – 15%

o QUERY7 - PUBLIC – 5%

o QUERY8 - ADM – 5%

The key points to remember is that

When the system is not fully utilized, queries will get more resources allocated to them

proportionally based on the RSG resource minimum parameter.

When the system is fully utilized all the queries will be guaranteed the resource minimum defined

to the RSG to which it is belongs to.

If more than one query from a RSG executed on a fully utilized system with equal priority, the

resource minimum defined for the RSG will be divided equally among the individual queries.


7 © sieac llc

If more than one query from a RSG executed on a fully utilized system with different priorities,

the resource minimum defined for the RSG will be divided proportionally among the individual

queries.

Note that the default “Admin” user gets allocated 50% of the resources and it can’t be changed. What

that means is to minimize the “Admin” usage. To compensate for that, create a new resource sharing

group with all the required administrative privileges and add users who perform administration tasks to

this group.

Configuring Gate Keeper With no changes to the default gate keeper configuration, all the queries from users are queued in a

single queue and scheduled if the total number of concurrent jobs running in the system is less than 48.

The Gate Keeper can be enabled to queue the queries into different queues and scheduled based on the

priority of the query instead of all queries being considered equal. To enable Gate Keeper along with the

GRA, the system registry host.schedAllowGKandGRA value need to be set to “yes” and it is by default

set to “no”. The following is a sample scheduling scenario in NZ, without gate keeper being enabled.

Host

Low

Low

High

Critical

SPUs

CPU

Memory

Disk

Gate KeeperGRA+PQE

Scheduler

Snippet

Scheduler

User

Query

Note that all the user queries irrespective of their priorities flow through a single gate keeper queue

which means there can be situations where high priority queries may end up waiting for low priority

queries to complete if the number of concurrent jobs running on the system are higher than the limit.

Once gate keeper is enabled along with GRA, the gate keeper by default will use queues of depth 36, 4, 2

and 2 for jobs with critical, high, normal and low priority jobs respectively for scheduling. The following

is a sample scenario with GK enabled.

Host

C

C

C

C

SPUs

CPU

Memory

Disk

Gate Keeper QueuesGRA+PQE

Scheduler

Snippet

Scheduler

User

Query

H

H

H

H

N

N

L

L

C


8 © sieac llc

On a side note, if PQE is disabled, then all the queries will be considered normal and all the queries will

be queued in the “normal” queue even though gate keeper is enabled along with GRA. The reason is that

the PQE is the component which is required for NZ to identify the priority of jobs set by the users.

With multiple queues with varying depth to control jobs of different priorities, the higher priority jobs

will not get queued due to lower priority jobs. But at the same time since the default queue depth values

are pre-defined, there can be situations low priority jobs can get queued even though there are no higher

priority jobs are running in the system and the concurrent jobs running in the system didn’t reach the

limit. For e.g. if only two low priority jobs are running on the system and a third job arrived, NZ will

queue it until one of the jobs which is running in the system completes even if there are resources

available. It would be good to understand the current work load pattern in the system to see whether it

fits the default settings before enabling GK.

Configuring Gate Keeper Manually

If the default job categorization and queue depth of GK doesn’t fit the work load pattern observed, Gate

Keeper queues and queue depth can be configured manually. For e.g., if it is found that there are more

very short running queries of high priority than long running queries of high priority the GK queues can

be defined using the observed grouping of query execution timings and the volume in each group. The

following are the steps

Disable PQE. What that means is that the priorities set by users for job executions will have no

effect and all the jobs executed will be considered normal. This will also eliminate the resource

allocation variations based on job priority by GRA which we saw earlier i.e. all the jobs from a

RSG will get allocated equal share of the resources available to the RSG.

Define the job groups identified based on the execution times observed using the NZ registry

setting host.gkQueueThreshold. For e.g. setting host.gkQueueThreshold=m,n,o,-1 identifies

four groups of jobs which take less than m secs, less than n secs, less than o secs and > o secs to

execute where m<n<o. This setting will also create the four GK queues and the maximum

number of GK queues which can be defined is four.

Set the queue depths for the job groups identified by using the NZ registry setting

host.gkMaxPerQueue. Following the example in the previous step, setting

host.gkMaxPerQueue=a,b,c,d will set the queue depth of “a” for job group identified to run < m

secs, queue depth of “b”,”c” and “d” for the remaining 3 job groups.

When manually configuring GK queues and depths, it is good to revisit whether SQB which deals with

short duration queries still need to be enabled. The following is a sample scenario after GK queues and

depths configured manually.


9 © sieac llc

Host SPUs

CPU

Memory

Disk

Gate Keeper QueuesGRA

Scheduler

Snippet

Scheduler

User

Query

< m < n < o > o

Summary Work load management is key to efficiently utilize the NZ appliance and it need to be kept current with

changes in the work load patterns. There are many options available to configure WLM to maximum

resource utilization but it all starts with clearly understanding the usage pattern. Default options may be

good to start with but will not be the best.

Document provided for information purpose only. For questions or comments reach out to “contact at sieac dot com”.

netezza workload management

Technology