Exasol Database Priorities and Priority Groups for Resource Allocation

Exasol data warehouse developers can use priority groups to distribute Exasol cluster resources including CPU, RAM, disk IO and network resources among active sessions executing queries and tasks on Exasol database. When a single query is active and executing a SQL query on the database and all other sessions are idle on the database, all of the resources are consumed by the active session query. On the other hand, if multiple sessions are active, then according to the priority groups, weight of priority groups, and whether the sessions is set to NICE or not; a calculation is taken for distributing the clustee resources on Exasol database. If a change occurs in priority group settings, or priority group assignments, or some of the active sessions go to IDLE state, the resource allocations are immediately updated.

Display Priority Groups List on Exasol Database

Exasol database SQL developers can query the system view exa_priority_groups under sys schema to get and display the current list of priority groups created on the Exasol database.
Here is the SQL SELECT query which can be used to list existing priority group definitions.


select * from SYS.EXA_PRIORITY_GROUPS;

Code

By default, there are 3 built-in priority groups on an Exasol Data Warehouse: High, Medium and Low as seen in below screenhot.

list of priority groups on Exasol database

These priority groups share the resources (CPU, RAM, Disk IO, network, etc) according to their relative weight values.
Default weight values for built-in priority groups on Exasol database are as follows:
Priority group Low with Weight 100,
Priority group Medium with Weight 300,
Priority group High with Weight 900

Exasol database default priority groups and weights

Of course, data warehouse administrator can later change the weight values of each priority group easily. We will see how to alter priority group weights later in this tutorial.

Just as a note, by default all users created on Exasol database are assigned to medium priority group.

Create New Priority Group on Exasol Databases

In order to create a new priority group following CREATE PRIORITY GROUP SQL command can be used.


create priority group PG_IMPORT with weight = 50;

Code

In above example, I have created a priority group for data loaders and keep its weight low because I don't want these developers to affect running SQL queries on the database in a negative manner. So although they will still be able to execute their IMPORT commands but will consume less cluster resources compared with other users.

I can also create another priority group with maximum possible weight value 1000 for top priority group for database users of management dashboard reports, etc.


create priority group PG_TOP with weight = 1000;

Code

Please note the maximum value for a priority group can be 1000.
If you try weight values more than 1000, you will get an error indicating weight must be an integer between 1 and 1000

Display Sessions and Consumed Exasol Resources using SQL

If Exasol database administrator or developer execute following SQL query on SYS.EXA_DBA_SESSIONS system view, the all sessions idle or active will be listed in the execution output. The resource percentage consumed by the session activity will be also displayed under Resources column.

Please note the IDLE sessions do not consume any resources since they are not active at that moment.


SELECT Session_Id, User_Name, Status, Priority, Nice, Resources FROM SYS.EXA_DBA_SESSIONS;

Code

So at the moment when we query system view EXA_DBA_SESSIONS there were two sessions active with both form medium priority group so each sharing 50% of the Exasol cluster resources.

Assign Exasol Database User to a Priority Group

By default, database users on Exasol database are assigned to medium priority group whigh has the weight 300. Assume that SQL admin wants to assign database user to a higher priority group, than following SQL command can be used:


Grant Priority PG_TOP to ERALPER;

Code

This time if 1 medium with weight 300 and 1 pg_top with weight 1000 sessions are active, immediately the distribution of the Exasol cluster resources are modified as follows:

Exasol cluster resource distribution among active sessions

Resource calculation formula:
PG_TOP session takes 1000 of 1300 (1000+300) which is 77% of database resources
MEDIUM session takes 300 of 1300 (1000+300) which is 23% of Exasol database resources

Revoke Priority Group from Exasol Database User

Just like the data warehouse developers grant a priorty to a database user or a database user role, it is possible to revoke the priority from the user or role which sets the user's priority group to default medium priority group.

To take back a priority group assignment from a user, following REVOKE SQL command is used


revoke priority from ERALPER;

Code

Executing Multiple Sessions with High Priority Groups

Although using a high priority group consumes more resources than a medium or low priority group, executing too many SQL queries with high priorities can result less resource when compared with a single medium or low priority group query.

Here is a case where I execute 5 PG_TOP priority group (weight equals to max value 1000) and only one medium priority group SQL query.

multiple active sessions with high priority groups cause less Exasol cluster resource

It is interesting that, 77% of cluster resources are distributed equally between 5 sessions. So each top priority group SQL query consumes only 15% of the cluster resources.
On the other hand, the only medium priority group query now consumes 23% of the Exasol resources. This is the total percentage of the resources assigned to medium priority group.

Resources column is showing allocated resources in percent and because its data type is integer, the rounding caused 15% display instead of exact 15.4 % value.

So resources are distributed among active priority groups first.
In this case, there are two active priority groups; medium and pg_top

As a second step, the priority group resources are distributed among their active sessions.

Executing excessive number of sessions with high priority group can result consuming less Exasol cluster resource while executing a single session with low priority can consume more cluster resource.

Assign Priority to User Role

Instead of directly granting a priority to a user, it is possible to assign priority group to a role on Exasol database. In order to see the list of user roles created on your Exasol database, administrators and developers can use following SQL to query system view SYS.EXA_DBA_ROLES


SELECT * FROM EXA_DBA_ROLES;

Code

DBA and PUBLIC are default roles shipped built-in with a brand new Exasol database. Others are created by database administrator after the installation. DBA role cannot be dropped.

Exasol database user roles

For example, if you want to create a new priority group and assign to REPORT_USERS group, following SQL command can be used


create priority group PG_Reporting with weight = 500;

grant priority PG_Reporting to REPORT_USERS;

grant REPORT_USERS to eralper;

Code

In the last step, an existing user is assigned to the user group Report_Users.
In this case, we expect that the sessions of the mentioned user will be executed with new priority group weight and consume cluster resources accordingly.

On the other hand, if a user has more roles and specific priority group assignments, the highest priority group will be affective.

assign priority group to roles on Exasol database

The calculation is as followa:
We have two different or distinct priority groups with active (non-idle) sessions: PG_REPORTING and MEDIUM priority groups.
PG_REPORTING priority group has weight 500 and MEDIUM has weight 300. So total is 800.
Active database sessions of PG_REPORTING priority group will consume 500/800 of the Exasol cluster resources which are the RAM, CPU, Disk IO and network resources.
The active sessions of MEDIUM priority group will consume 300/800 of the data warehouse resources.

Since there are only 1 active session of each, we will divide the priority group resources to 1 for each session.

Session shown in first row will consume: (500/800)*100 = 62.5% rounded to 63% of the available resources.
Session listed in the 6th row will consume: (300/800)*100 = 37.5% rounded to 38% of the resources.

Change Priority Group Weight

It is possible to change the weight of an Exasol priority group using SQL ALTER PRIORITY GROUP command as follows:


alter priority group PG_Reporting set weight = 600;

Code

This change will be immediately reflected to the active sessions resource allocations and will be visible on system view EXA_DBA_SESSIONS

Delete Priority Group Definition using SQL DROP Command

Exasol data warehouse administrator can delete priority group definition using DROP PRIORTY GROUP command using SQL if the priority setting is no more useful.


drop priority group pg_top;

Code

When the priority group definition is deleted or dropped, the users and roles granted this priority group are reassigned to default medium priority group automatically.

I hope it is now a bit more clear how Exasol Analytics Database manages resource allocation between active sessions for database developer and data warehouse administrators after reading this Exasol tutorial about priorities and priority groups.