You can use priorities to systematically distribute the Exasol resources across the users and roles.
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 priorities, Exasol treats all queries equally except short queries running less than 5 seconds, which get a higher weighting for minimizing the system's latency.
If you want to influence the execution of many parallel queries, you can use the priorities which can be assigned through the GRANT statement. This intervention should only be necessary in case of a highly parallel usage of your system, combined with a certain user group which needs maximum performance. Priorities can be useful if you connect a web application which has low latency requirements, and your system frequently has to process 10 or more parallel queries. On the other side, long running ETL processes or queries from less important applications could get a lower priority.
Information about the usage of your database is available in the statistical system table. For more information, see System Tables.
Priorities in Exasol
In the default case, the sessions of all users have the same priority. But if you identified certain users or roles which should be prioritized higher or lower, then you can chose between the priority groups LOW, MEDIUM, and HIGH with weights 100, 300, and 900. These three groups define the resource distribution by allocating at least 8%, 23%, and 69% according to their weights.
Users within one group share these resources (CPU and RAM) equally. If a priority group doesn't exist, the resources are distributed across the other groups by their weights. If, for example, only LOW and MEDIUM sessions exist, then these obtain 25% and 75% of the resources (corresponding to their weights 100:300).
- Users without an explicit priority obtain the default priority group which is defined through the system parameter DEFAULT_PRIORITY_GROUP which is by default MEDIUM.
- A user inherits the priority group with the highest weight of his roles, but a directly granted priority overwrites that.
- Multiple sessions of a user obtain the same priority.
- All sessions of a priority group share its resources equally. As a result, if there are many parallel HIGH sessions, each of them could get fewer resources than a single LOW session. On the other hand, this way of system allocation has an advantage that certain user groups can be assured to get a certain amount of resources (for example, at least 69% for the HIGH users).
- The execution times of queries don't follow the resource distribution. Therefore, an identical query with twice as much resources than another one will be executed significantly faster, but not exactly by factor 1:2.
- A user can set the attribute NICE through the statement ALTER SESSION (ALTER SESSION SET NICE='ON'). This indicates the system that the sessions of other users shall be affected as less as possible by this user's session. The resource manager then divides the session's weight by the number of currently active queries. Hereby such sessions can be processed without affecting sessions from the same or other priority groups.
- The priority and concrete 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).
In the following example a few roles with different priorities are defined. In the picture below, the resulting resource distribution for a list of active sessions is displayed.
GRANT PRIORITY GROUP LOW TO reporting, etl;
GRANT PRIORITY GROUP MEDIUM TO marketing;
GRANT PRIORITY GROUP HIGH TO web, dba;
Defining Priority Groups
If the default priority 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 priority groups or completely replace them. The general concept of how the database allocates the actual resources for a session is similar to the default case described above.
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 priority groups in the first step.
Within a priority 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.
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 priority group:
- Query the existing priority groups through the system table EXA_PRIORITY_GROUPS.
- Change the database-wide default priority group using ALTER SYSTEM.
- Create new priority groups with weights ranging from 1 to 1000 using CREATE PRIORITY GROUP.
- Alter the weight of existing priority groups using ALTER PRIORITY GROUP.
- Drop priority groups using DROP PRIORITY GROUP.
- Rename priority groups using RENAME PRIORITY GROUP.
- Comment priority groups using COMMENT ON PRIORITY GROUP.
- Grant the system privilege MANAGE PRIORITY GROUPS to users/roles to allow them to create/modify/drop priority groups.
SELECT * FROM EXA_PRIORITY_GROUPS;
ALTER SYSTEM SET DEFAULT_PRIORITY_GROUP = LOW;
CREATE PRIORITY GROUP example WITH WEIGHT = 1;
ALTER PRIORITY GROUP example SET WEIGHT = 1000;
DROP PRIORITY GROUP example;
RENAME PRIORITY GROUP example TO new_name;
COMMENT ON PRIORITY GROUP example IS 'new comment';
GRANT MANAGE PRIORITY GROUPS TO user;
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 ALTER SCHEMA command . The corresponding raw data sizes for objects is 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.