Some interesting Workload Manager "Hacks"

Aster
Enthusiast

Some interesting Workload Manager "Hacks"

(Disclaimer: There's no actual "hacking" involved here.  Just using WLM in a slightly less-than-obvious way)

Hack #1 - Say you have a class of users who can be trusted to know what priority their queries should run at.  You want to give them control, but how can you do that?  Here's one solution that uses a combination of roles and WLM policies:

In ACT, logged in as an account with db_admin role,

  1. Create a database role called "query_captain" [CREATE ROLE query_captains;]
  2. Add users to the role [GRANT query_captain TO user1, user2, user3, ... userN;]
  3. Create a schema "query_control" and grant role query_captain USAGE privilege on it.  [CREATE SCHEMA query_control; GRANT USAGE ON SCHEMA query_control TO query_captain;]
  4. Create three tables "query_low_priority," "query_medium_priority" and "query_high_priority" in the query_control schema The tables only need one column and should not have any rows.  [CREATE TABLE query_control.query_low_priority DISTRIBUTE BY REPLICATION; GRANT SELECT on query_control.query_low_priority TO query_captain.

In AMC, logged in as an account with db_admin role,

  1. Create three new Service Classes "priority_control_low," "priority_control_medium," and "priority_control_high" setting their priority, weight, and memory soft limits to desired values.
  2. Create three new Workload Policies "prority_control_low," "priority_control_medium, and "priority_control_high" setting the service class names to the corresponding class created in the previous step.  Then, set their predicate to:

    For low priority:  'query_captain'=ANY(roles) AND 'query_control.query_low_priority'=ANY(tableNames)  AND username NOT IN ('db_superuser','beehive')

    For medium priority:  'query_captain'=ANY(roles) AND 'query_control.query_medium_priority'=ANY(tableNames) AND username NOT IN ('db_superuser','beehive')

    For high priority: 'query_captain'=ANY(roles) AND 'query_control.query_high_priority'=ANY(tableNames) AND username NOT IN ('db_superuser','beehive')
  3. Drag the new workload policies to the desired place in the list (remembering that they are evaluated top-to-bottom

Inform users that are in the (hopefully small) group of accounts granted the "query_captain" role that they can now control the priority of their queries by appending "UNION ALL SELECT null, null, null, ... FROM query_control.query_[low|medium|high]_priority" to the end.  For example:

SELECT store_name, sum(sales_amt)
FROM sales_schema.sales_fact
GROUP BY store_name
UNION ALL
SELECT null, null
FROM query_control.query_high_priority --This is an urgent request!

The key elements of this "hack" are:

1. The creation and assignment of the "query_captain" (or whatever you prefer to call it) role.  This allows you to control which users are allowed to explicitly set their queries' priority

2. The "query_control" schema associated tables.  These are needed to trigger the Workload Policy via it's "tableNames" predicate variable.

3. The "query_control" workload policies and service classes.  These are used by the Aster Workload Manager to set the priority of the statements.

Although the solution shown above has three priorities, you could easily extend it (or limit it) by varying the number of tables, services classes, and workload policies you create.  And obviously if you don't like the names I chose for any of the above you can pick names that you prefer.

Hack #2 - Say you want to disable an account but don't want to drop it altogether because it might need to be reactivated at some point in the future, is the owner of a lot of tables that you don't want to go hunting down in the database.  Aster doesn't have an "ALTER USER username DISABLE;" command (feature request, maybe?)  How can you prevent the user from executing queries in the system?  Here's a solution that again uses a combination of roles and WLM policies:

In ACT, logged in as an account with db_admin role,

  1. Create a database role called "inactive_user" [CREATE ROLE inactive_user;]
  2. When needed, add users to the role [GRANT inactive_user TO user1, user2, user3, ... userN;]

In AMC, logged in as an account with db_admin role,

  1. Create a new Service Classes "inactive_user" and set its priority to 0 (Deny).  The other settings are ignored, so just enter some default value.
  2. Create a new Workload Policies "inactive_user" and set its service class name to the class created in the previous step.  Then, set its predicate to:

    'inactive_user'=ANY(roles)
  3. Drag the new workload policy to the TOP of the list.  It absolutely must be at the top of the list to work.

As before, they key elements of this "hack" are the creation and assignment of the roles and workload manager items.  The other nice thing about this one is that it also allows you to monitor and detect inactive users attempting to connect and execute queries.

1 REPLY
Enthusiast

Re: Some interesting Workload Manager "Hacks"

A quick note about the first of the ideas I presented in the article above:  The current version of Aster Database (5.10) has a bug in the workload manager that prevents the ANY(tableNames) predicate expression from working when trying to match a schema-qualified table name.  I wrote the article under the assumption that the bug was fixed (or that I was typing things incorrectly).

So if you're interested in trying out the idea, don't create the query_control schema.  Instead, create the tables in the public schema and remove the schema name from the workload policy predicate, e.g. 'query_high_priority'=ANY(tableNames)