TEMP DBRAM Limits / Consumer Groups

Detail name	Value
Changelog Number	7045
Type	New Feature
Status	Resolved
Fix Versions	Exasol 7.0.0
Resolution Date	2020-09-11

Background

On multi tenant systems it is critical to manage database resources. So far administrators could use PRIORITY GROUPS to prioritize concurrently running queries. This proves useful, but lacks the ability to specify limits on other resources such as TEMP DBRAM usage.

Feature Description

You can now define hard limits on TEMP DBRAM usage on different levels. If a limit is violated, the query will be terminated.

A straightforward approach to enforce these limits are SESSION/SYSTEM parameters. On a SYSTEM level you can set the TEMP DBRAM limit:

• globally
• for individual users
• for individual sessions

On a SESSION level you can set the TEMP DBRAM limit for your own session.

A more flexible way to manage resources are CONSUMER GROUPS. CONSUMER GROUPS are an evolution of the PRIORITY GROUPS we offered before. With CONSUMER GROUPS you can set:

• CPU_WEIGHT: like the WEIGHT in PRIORITY GROUPS
• SESSION_TEMP_DB_RAM_LIMIT: a limit per individual session
• USER_TEMP_DB_RAM_LIMIT: a limit per individual user
• GROUP_TEMP_DB_RAM_LIMIT: a limit for the whole consumer group (all users and roles that this CONSUMER GROUP applies to)
• PRECEDENCE: a tiebreaker to decide which CONSUMER GROUP to apply if multiple are granted to a role

Example Usage

ALTER SYSTEM SET TEMP_DB_RAM_LIMIT = '70G';
CREATE CONSUMER GROUP BI_CONSUMER_GROUP WITH PRECEDENCE = '900', 
                                             CPU_WEIGHT = '900', 
                                             USER_TEMP_DB_RAM_LIMIT = '10G', 
                                             GROUP_TEMP_DB_RAM_LIMIT = '50G';
ALTER ROLE BI_TEAM SET CONSUMER_GROUP = BI_CONSUMER_GROUP;

Benefits

Consumer Groups are a new extensible interface to manage database resources. In addition to managing CPU resources, we provide a very flexible way to manage TEMP DBRAM limitations. The new interface is extendable to include other resource limits in future without breaking backward compatibility.

Changed Behavior

Existing Priority Groups are converted into equivalent Consumer Groups during upgrade to Exasol 7.0 from previous versions. Sessions of SYS user are allotted a special SYS_CONSUMER_GROUP. The SYS_CONSUMER_GROUP has highest CPU_WEIGHT by default viz. 1000. This may lead to an impact on query prioritization of other users when the SYS user is running queries. No TEMP DBRAM limits are enforced by default. 

Syntax changes:

Old PRIORITY GROUPS syntax	New CONSUMER GROUPS syntax
CREATE PRIORITY GROUP example WITH WEIGHT = 1;	CREATE CONSUMER GROUP example WITH PRECEDENCE = 1000, CPU_WEIGHT = 1000;
ALTER PRIORITY GROUP example SET WEIGHT = 1000;	ALTER CONSUMER GROUP example SET CPU_WEIGHT = 500;
DROP PRIORITY GROUP example;	DROP CONSUMER GROUP example;
RENAME PRIORITY GROUP example TO new_name;	RENAME CONSUMER GROUP example TO new_name;
COMMENT ON PRIORITY GROUP example IS 'new comment';	COMMENT ON CONSUMER GROUP example IS 'new comment';
GRANT MANAGE PRIORITY GROUPS TO user1;	GRANT MANAGE CONSUMER GROUPS TO user1;
GRANT PRIORITY GROUP example TO user1;	ALTER USER user1 SET CONSUMER_GROUP = example;
GRANT PRIORITY GROUP example TO role1;	ALTER ROLE role1 SET CONSUMER_GROUP = example;
ALTER SYSTEM SET DEFAULT_PRIORITY_GROUP = LOW;	ALTER SYSTEM SET DEFAULT_CONSUMER_GROUP = LOW;

Restrictions

Only CPU weight and TEMP DBRAM can be managed currently.

Changed behavior