Resource Manager

You can use the resource manager to distribute Exasol resources across the users and roles systematically. In addition to providing ways to improve the reliability and efficiency of resources used in Exasol, you can use the resource manager to

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

You can create consumer groups and allocate resources to them. You can set consumer groups to users or roles. When a user connects to Exasol, a session is assigned to the consumer groups set directly to the user or one of the user roles. The resource manager then allocates resources to the consumer groups and enforces resource limits by killing the violating queries or sessions when the limits are exceeded.

During the execution of a query, Exasol attempts to use as many resources (CPU, RAM, network, I/O) as possible by internal parallelization (multi-threading). However, since all execution steps can not be parallelized, the utilization of all hardware resources will be achieved only with multiple parallel queries. If more parallel queries are executed than the number of cores per server, Exasol's resource manager schedules the parallel queries to ensure a constant system performance.

Using a time slot model, the resource manager distributes the resources evenly across the parallel queries while limiting the overall resource consumption by regularly pausing and restarting certain queries. Without such scheduling the overall usage could exhaust the system resources, leading to a decreased throughput.

Without explicitly setting consumer groups to users/roles, all 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.

If you want to influence the execution of many parallel queries, you can set consumer groups for different users or roles depending on the priority of the jobs. The CPU_WEIGHT attribute of the consumer group will then govern the amount of CPU processing time allocated to sessions within each consumer group.

Information about the usage of your database is available in the statistical system table. For more information, see System Tables.

Default Consumer Groups

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

The SYS_CONSUMER_GROUP is a special consumer group that is set to the SYS user by default. You cannot set the SYS_CONSUMER_GROUP to other users or roles, and you cannot set another consumer group to the SYS user.

You can use the system parameter DEFAULT_CONSUMER_GROUP to specify the default consumer group to use when no consumer group is assigned to a user or one of its roles. By default, the DEFAULT_CONSUMER_GROUP is set to MEDIUM.

Consumer Group Attributes

The following table provides you with the list of consumer group attributes: 

Considerations

• Multiple sessions of a user are assigned to the same consumer group.
• All sessions within a consumer group contend for all resources granted to the consumer group. As a result, if there are many sessions in the HIGH consumer group and a single session in the LOW group, then 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 through the statement ALTER SESSION (ALTER SESSION SET NICE='ON'). Setting this attribute indicates the system that the sessions of other users will be affected as less as possible by the current user's session. The resource manager then divides the session's weight by the number of currently active queries. As a result of this, sessions can be processed without affecting sessions from the same or other consumer groups.
• The consumer groups and resource allocation of users, roles, and their sessions can be displayed through system tables (such as EXA_ALL_USERS, EXA_ALL_ROLES, or EXA_ALL_SESSIONS).

Example

In the following example a few 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;

Defining Consumer Groups

If the default consumer groups LOW, MEDIUM, and HIGH are not enough for your requirements, Exasol provides the possibility to adjust them appropriately by creating, altering, dropping them, or by adjusting their weights (between 1 and 1000). Database administrators have more options to prioritize between different user groups. They can alter and extend the existing consumer groups or completely replace them.

For information about how to manage the system's consumer groups, see CONSUMER GROUP. The list of existing consumer groups is displayed in system table EXA_CONSUMER_GROUPS.

Resource Distribution

Exasol always tries to use all system resources, so a single LOW query receives 100% of the available resources. If there are multiple queries running, the resources are distributed on the active consumer groups in the first step.

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. Compare the following two graphics for an example.

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

Additionally, you can perform the following actions on the 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 Usage

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 SET ANY CONSUMER GROUP system privilege 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 the set consumer group. When no consumer group is assigned/set for a user, then the sessions of the user will receive one of the consumer groups assigned to its roles. You can use the optional attribute PRECEDENCE to tie-break among multiple consumer groups available to a user through multiple roles. When the PRECEDENCE attribute is the same for the consumer groups, the tie-breaking is done in alpha-numeric order. When no consumer group is assigned to a user or one of its roles, then the sessions of the user are assigned to the default consumer group specified by the system parameter DEFAULT_CONSUMER_GROUP.

Managing Consumer Groups

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

You can create consumer groups and allocate resources to them by setting the attributes of the consumer group. You must specify the CPU_WEIGHT attribute 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

To limit the data volume in your system, it's possible to use the concept of schema quotas. You can specify the maximum raw data size for a schema (the raw size of all contained data objects). By default, the raw data sizes for schemas are unlimited. You can define the limit through the ALTER SCHEMA command. The corresponding raw data sizes for objects are available in the system table EXA_ALL_OBJECT_SIZES. The value NULL means unlimited. If the limit of a schema is exceeded, users will get an error message during the commit when trying to insert additional data.