Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-244

Priority Groups

    XMLWordPrintable

    Details

    • Type: Explanation
    • Status: Published
    • Affects Version/s: EXASolution 4.2.0, EXASolution 5.0
    • Fix Version/s: None
    • Component/s: EXASolution
    • Labels:
      None
    • Solution:
      Hide

      General information

      By the use of priorities, EXASolution resources can be systematically distributet across users and roles.

      If you want to influence the execution of many parallel queries, you can use the priorities which can be assigned via 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 maximal performance. Priorities could be for instance 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.

      Priorities in EXASolution

      In 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 choose between the priority groups LOW, MEDIUM and HIGH with weights 1, 3 and 9. These three groups define the resource distribution by allocating at least about 8%, 23% and 69% in accordance to their weights. Users within one group share these resources (CPU and RAM) equally. If a priority group is not represented at all, then the resources are distributed across the other groups by their weights. If e.g. only LOW and MEDIUM sessions exist, then these obtain 25% and 75% of the resources (corresponding to their weights 1:3).

      Important notes

      • Users without an explicit priority obtain the default priority group MEDIUM.
      • A user inherits the highest priority of his roles, but a directly granted priority overwrites that.
      • Multiple sessions of a certain user obtain the same priority.
      • Please note that all sessions of a priority group share its resources equally. As consequence, e.g. each of many parallel HIGH sessions can get less resources than a single LOW session. On the other hand, this way of system allocation has the great advantage that certain user groups can be assured to get a certain amount of resources (e.g. at least 69% for the HIGH users).
      • The execution times of queries doesn't scale perfectly linearly with the resource distribution. That means an identical query executed with twice as much resources will be executed significantly faster, but not exactly by factor 2.
      • A user can set the NICE attribute via 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 the resulting resource allocation of users, roles and their sessions can be displayed via several
        system tables (e.g.EXA_ALL_USERS, EXA_ALL_ROLES, EXA_ALL_SESSIONS).

      Example

      In the following example some roles with different priorities are defined. In the picture below, the resulting resource distribution for a list of active sessions is displayed.

      GRANT PRIORITY LOW TO reporting, etl;
      GRANT PRIORITY HIGH TO web, dba;
      

      Show
      General information By the use of priorities, EXASolution resources can be systematically distributet across users and roles. If you want to influence the execution of many parallel queries, you can use the priorities which can be assigned via 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 maximal performance. Priorities could be for instance 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. Priorities in EXASolution In 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 choose between the priority groups LOW, MEDIUM and HIGH with weights 1, 3 and 9. These three groups define the resource distribution by allocating at least about 8%, 23% and 69% in accordance to their weights. Users within one group share these resources (CPU and RAM) equally. If a priority group is not represented at all, then the resources are distributed across the other groups by their weights. If e.g. only LOW and MEDIUM sessions exist, then these obtain 25% and 75% of the resources (corresponding to their weights 1:3). Important notes Users without an explicit priority obtain the default priority group MEDIUM. A user inherits the highest priority of his roles, but a directly granted priority overwrites that. Multiple sessions of a certain user obtain the same priority. Please note that all sessions of a priority group share its resources equally. As consequence, e.g. each of many parallel HIGH sessions can get less resources than a single LOW session. On the other hand, this way of system allocation has the great advantage that certain user groups can be assured to get a certain amount of resources (e.g. at least 69% for the HIGH users). The execution times of queries doesn't scale perfectly linearly with the resource distribution. That means an identical query executed with twice as much resources will be executed significantly faster, but not exactly by factor 2. A user can set the NICE attribute via 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 the resulting resource allocation of users, roles and their sessions can be displayed via several system tables (e.g.EXA_ALL_USERS, EXA_ALL_ROLES, EXA_ALL_SESSIONS). Example In the following example some roles with different priorities are defined. In the picture below, the resulting resource distribution for a list of active sessions is displayed. GRANT PRIORITY LOW TO reporting, etl; GRANT PRIORITY HIGH TO web, dba;
    • Category 1:
      Database Administration - Priorities & Resource Management
    • Category 2:
      Database Administration - Performance

      Attachments

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: