Resource Manager

This article explains how to use the resource manager in Exasol.

What is the resource manager in Exasol?

The resource manager in Exasol systematically distributes resources across the users and roles in the database to improve the reliability and efficiency of resource usage. You can also use the resource manager to:

• Prioritize important users by allocating a higher share of resources to them
• Guarantee the availability of resources to users
• Prevent small groups of users from exhausting the resources

You can view the usage of your database in the statistical system tables. For more information, see System Tables.

Consumer groups

The following consumer groups are created when a new Exasol database is initialized:

When a user or role has not been assigned to a specific consumer group, all their sessions are assigned to the default consumer group. The scheduler assigns equal priority to all queries from all sessions and allocates approximately the same CPU processing time for each query. You can use the system parameter DEFAULT_CONSUMER_GROUP to specify the default consumer group. The default is MEDIUM.

SYS_CONSUMER_GROUP is a special consumer group that is set for the SYS user. You cannot set this group for other users or roles, and you cannot set another consumer group for the SYS user.

The CPU_WEIGHT and PRECEDENCE values are consumer group attributes that govern the distribution of resources between the consumer groups. For more details, see Consumer group attributes.

Consumer group attributes

The following attributes can be set on each consumer group. All attributes except CPU_WEIGHT are optional.

Usage notes

• Multiple sessions of the same user are always assigned to the same consumer group.

• All sessions within a consumer group contend for all resources granted to that consumer group. This means that if there are many sessions in the HIGH group and a single session in the LOW group, the session in the LOW group may get more resources than an individual session in the HIGH group.

• A user can set the attribute NICE='ON' through ALTER SESSION. This tells the system that sessions of other users should be affected as little as possible by the current user’s session. The resource manager then divides the session’s weight by the number of currently active queries. Sessions can thus be processed without affecting sessions from the same or other consumer groups.

• The consumer groups and the resource allocation of users, roles, and their sessions can be viewed in system tables such as EXA_ALL_USERS, EXA_ALL_ROLES, and EXA_ALL_SESSIONS.

Example

In the following example, a number of roles with different consumer groups are defined.

CREATE CONSUMER GROUP CEO WITH
 PRECEDENCE = '1000',
 CPU_WEIGHT = '900';
 
CREATE CONSUMER GROUP BI_TEAM WITH
 PRECEDENCE = '900',
 CPU_WEIGHT = '500',
 GROUP_TEMP_DB_RAM_LIMIT = '200G',
 USER_TEMP_DB_RAM_LIMIT = '100G';
 
CREATE CONSUMER GROUP TEST_TEAM WITH
 PRECEDENCE = '800',
 CPU_WEIGHT = '100',
 GROUP_TEMP_DB_RAM_LIMIT = '10G',
 SESSION_TEMP_DB_RAM_LIMIT = '5G',
 QUERY_TIMEOUT = 60,
 IDLE_TIMEOUT=3600;

Create and alter consumer groups

If the default consumer groups LOW, MEDIUM, and HIGH are not adequate for your needs, you can create additional consumer groups as required. Database administrators can alter and extend the existing consumer groups or completely replace them, and have more options to prioritize between different user groups.

To learn more about how to manage consumer groups, see CONSUMER GROUP.

The list of existing consumer groups is found in the system table EXA_CONSUMER_GROUPS.

Resource distribution

Exasol always tries to use all available system resources, which means that a single LOW query will receive 100% of the available resources. If there are multiple queries running, the resources are first distributed on the active consumer groups.

Within a consumer group, all sessions that are running a query share the group resources:

Sessions that have the NICE attribute set share their resources with all other groups. For example, compare the following two graphics:

This approach of setting the NICE attribute may cause the sessions in group HIGH to achieve less resources than sessions in group MEDIUM.

You can also perform the following actions on a consumer group: 

• Query the existing consumer groups through the system table EXA_CONSUMER_GROUPS

• Change the database-wide default consumer group using ALTER SYSTEM

• Create new consumer groups with weights ranging from 1 to 1000 using CREATE CONSUMER GROUP

• Alter the weight of existing consumer groups using ALTER CONSUMER GROUP

• Drop consumer groups using DROP CONSUMER GROUP

• Rename consumer groups using RENAME CONSUMER GROUP

• Comment consumer groups using COMMENT ON CONSUMER GROUP

• Grant the system privilege SET ANY CONSUMER GROUP to users/roles to allow them to set consumer groups for user or roles

Example

SELECT * FROM EXA_CONSUMER_GROUPS;
ALTER SYSTEM SET DEFAULT_CONSUMER_GROUP = LOW;

CREATE CONSUMER GROUP example WITH CPU_WEIGHT = 1;
ALTER CONSUMER GROUP example SET CPU_WEIGHT = 1000;

DROP CONSUMER GROUP example;
RENAME CONSUMER GROUP example TO new_name;

COMMENT ON CONSUMER GROUP example IS 'new comment';
GRANT SET ANY CONSUMER GROUP TO user; 

Setting consumer groups

Users with the system privilege SET ANY CONSUMER GROUP can set consumer groups for users or roles.

ALTER USER <username> SET CONSUMER_GROUP = <consumerGroupName>;
ALTER ROLE <rolename> SET CONSUMER_GROUP = <consumerGroupName>;
 
ALTER USER <username> SET CONSUMER_GROUP = NULL;
ALTER ROLE <rolename> SET CONSUMER_GROUP = NULL;

When you set a consumer group for a user, all sessions for the user are assigned to that consumer group. If no consumer group is assigned directly to the user, the sessions of the user will receive one of the consumer groups assigned to its roles.

If no consumer group is set to a user or one of its roles, the sessions of the user are assigned to the default consumer group specified by the system parameter DEFAULT_CONSUMER_GROUP.

You can use the attribute PRECEDENCE to tiebreak among multiple consumer groups available to a user through multiple roles. When the PRECEDENCE attribute is the same for the consumer groups, the tiebreaking is done in alphanumerical order.

Manage consumer groups

Users with the system privilege MANAGE CONSUMER GROUPS can create, alter, and delete consumer groups.

You can allocate resources to a consumer group by setting the consumer group attributes. The CPU_WEIGHT attribute must be specified when creating a new consumer group. All other attributes are optional.

For information about the attributes and their default values, see Consumer group attributes.

CREATE CONSUMER GROUP <identifier> WITH
 CPU_WEIGHT = <string_literal> [, <ATTRIBUTE> = <string_literal>]*;
  
ALTER CONSUMER GROUP <identifier> SET
 <ATTRIBUTE> = <string_literal> [, <ATTRIBUTE> = <string_literal>]*;
  
DROP CONSUMER GROUP <identifier>;

Schema quotas