Netezza — Workload Management

Workload Management, one of the areas that are often overlooked nowadays by the elastic properties of cloud based solutions. But with the costs associated with it you might find better to run your workloads in a different manner.

I don’t want to get into the very deep details of WLM, but rather give you a simple way to tune your workload performance to suit your needs. You might already know that in Netezza, generally speaking, query performance is always the best you can get from the system and that no WLM means basically that a single query will get 100%/#Queries.

• GRA (Guaranteed Resources Allocation)

To define a different approach we need to make use of GRA (Guaranteed Resources Allocation) groups. This means that you need to define a resource minimum for it, which is not a % but rather the the number of shares to determine a proportion when multiple queries from different resource groups are running.

With that in mind, let’s define a Resource Group for our Batch ETL processes and our online Reporting workloads:

CREATE GROUP ETL    WITH RESOURCE MINIMUM 60;
CREATE GROUP ONLINE WITH RESOURCE MINIMUM 20;

Now that was really simple.. this essentially means that whenever we have queries from both RSG active at the same time it will be split in a 3:1 ratio, with our ETL using~75% and Online workload using~25%.

But let’s say I wanted to invert the proportions during the regular work hours which are 9 to 5 PM? Good news, it is a simple as issuing an ALTER statement:

ALTER GROUP ETL    WITH RESOURCE MINIMUM 20;
ALTER GROUP ONLINE WITH RESOURCE MINIMUM 60;

You can also play with priorities, which enable you to speed up queries inside the same RSG as well (a high priority query will have 2x more resources than a normal priority one), but I think that this might be enough for starters.

So with that in mind, let’s jump into the next interesting section to map our workloads into the relevant RSG.

• Scheduler Rules

So now that we have defined our RSGs we need a way to make sure that our workload flows through it. One simple way would be to map a particular set of users into it:

ALTER USER ETL_USER IN RESOURCEGROUP ETL;
ALTER USER RPT_USER IN RESOURCEGROUP ONLINE;

This is perfectly fine to do, in fact is one of the most common techniques and works out pretty nicely. However, there is another way to map the queries into the RSGs and here is where Scheduler Rules come into play.

Scheduler Rules can do a lot of things, but lets focus first on how they can be used to identify our workload. There are many conditions that can be specified, as an example let’s take that we want to apply it based on the DATABASE in which the query will run.

So in this example we are going to classify the ETL queries that go into the ETL_DB database and the ONLINE queries that go into the ONLINE_DB database:

CREATE SCHEDULER RULE ETL_DB_RULE AS IF DATABASE IS ETL_DB THEN EXECUTE AS RESOURCEGROUP ETL;
CREATE SCHEDULER RULE ONLINE_DB_RULE AS IF DATABASE IS ONLINE_DB THEN EXECUTE AS RESOURCEGROUP ONLINE;

As simple as that.. we can even do this on a per table level or based on the type of plan (Load, Unload, Genstats, Groom and UDX). And there is more to it, we can even use this to avoid jobs on a particular object during maintentance.

CREATE SCHEDULER RULE ETL_DB_RULE AS IF RESOURCEGROUP IS NOT DBA_GROUP THEN ABORT 'System is under maintenance';

And that is everything… hopefully it was interesting enough for you!

Please refer to the documentation for a comprehensive tour around our Workload management capabilities.

https://www.ibm.com/docs/en/psfa/7.2.1?topic=management-guaranteed-resource-allocation-gra

https://www.ibm.com/docs/en/psfa/7.2.1?topic=management-scheduler-rules