Workload Management Walkthrough

workload management (wlm) in the {{ocienthyperscaledatawarehouse}} allows a database administrator to set restrictions and priorities for groups of users the main use case for this is to effectively control multiple workflows across the system, prioritizing or limiting resources to groups when needed query restrictions are set and assigned using a service class that can be used for multiple user groups for a full list of restrictions and their definitions, see the documentation this example shows how to set up three service classes with differing priorities, explaining the tradeoffs and design decisions one should make when setting up these restrictions create service classes before creating service classes, the system administrator or database administrator first determines the logical grouping of users on their system and their relative priority in addition, the response time and concurrency requirements of different user groups are understood using these inputs, the administrator can design the service classes that allow their users to receive the proper resource allocations that deliver query requirements in this example, define the priority at the service level by creating three service classes to service high, medium, and low priority users different settings are configured to provide an illustration of the types of configuration that you can use to control features such as concurrency, result sizing, or scheduling priority prerequisites an {{ocient}} system has been set up with an operational sql node a user with the system administrator role has access to the system step 1 login as a system administrator user in order to create service classes, the user signed in must be assigned the system administrator role for a full list of user permissions and their definitions, see the docid\ asr8r6xqiyofgaz5qnbiw first, connect to the system database with the command connect to jdbc\ ocient //my sql ip address 4050/system;user=admin\@system;password=securepassword; step 2 set up the high priority user class user groups that need fast performance and need access to the most system resources should fall under this service class their queries should see minimal impact from outside workloads when defining a service class, any service class field that is omitted will be set to the maximum/least restrictive value example service class create service class high priority sc scheduling priority = 100 0, max concurrent queries = 10, max elapsed time = 3600; example system catalog view select from sys service classes; id database id name max temp disk usage max elapsed time max concurrent queries max rows returned scheduling priority cache max bytes cache max time max elapsed time for caching max columns in result set priority adjustment factor priority adjustment time min priority max priority \ 42649e8e ae7f 4de5 bb2b 846d158fcb91 23291ad0 ce94 4e1a bdc9 178463ef6ec4 default 100 1 1 1 1 0 1 1 1 1 0 0 0 0 0 1 0 06b92c4c b0b1 4a14 880c 7d257af3b2b5 23291ad0 ce94 4e1a bdc9 178463ef6ec4 high priority sc 100 3600 10 1 100 0 1 1 1 1 0 0 0 0 0 1 0 scheduling priority — a numeric value that defines the query’s priority in the database a default query being run with no service class has a priority set to 1 0 this priority number is used as an ordered list for scheduling with higher values receiving priority it is not a weighted value, so only the ordering, not the relative sizes of the priority levels matter when setting the priority for a high priority service class, set this value to be greater than 1 0 max concurrent queries — the number of concurrent queries that can be run at a given time for the entire service class if the max concurrency is hit, queries will enter a queue a value of 1 indicates unlimited queries, however it is a best practice to set this value so no individual workloads can monopolize the system max elapsed time — the maximum amount of time, in seconds, a query can run this restriction helps reduce the length of queries holding concurrency slots and allows concurrent workloads using the same service class to all make progress it is recommended to set max elapsed time if max concurrent queries is set to prevent extremely long running queries from blocking other concurrent queries if a service class needs to run sets of queries with very different expected elapsed times, it might be an indication that a separate service class is warranted step 3 set up the medium priority user class user groups that do not necessarily need the fastest performance fall under the medium priority service class higher priority queries impact the performance of queries in this service class, but these queries receive priority over lower priority ad hoc workloads example service class create service class medium priority sc scheduling priority = 10 0, max concurrent queries = 2, max temp disk usage = 50, max elapsed time = 600; example system catalog view select from sys service classes; id database id name max temp disk usage max elapsed time max concurrent queries max rows returned scheduling priority cache max bytes cache max time max elapsed time for caching max columns in result set priority adjustment factor priority adjustment time min priority max priority \ 42649e8e ae7f 4de5 bb2b 846d158fcb91 23291ad0 ce94 4e1a bdc9 178463ef6ec4 default 100 1 1 1 1 0 1 1 1 1 0 0 0 0 0 1 0 06b92c4c b0b1 4a14 880c 7d257af3b2b5 23291ad0 ce94 4e1a bdc9 178463ef6ec4 high priority sc 100 3600 10 1 100 0 1 1 1 1 0 0 0 0 0 1 0 bf3a939d d773 4c7c 9c30 4bc14e7b9422 23291ad0 ce94 4e1a bdc9 178463ef6ec4 medium priority sc 50 600 2 1 10 0 1 1 1 1 0 0 0 0 0 1 0 scheduling priority — for a "medium" priority, this value should be set to a number in between the highest priority service class and the default service class max temp disk usage — from 0 100, this field represents the maximum percentage of unused disk space that a query can see before failing when setting up the cluster, it is important to keep some disk space unused on a foundation node so queries can spill to disk note that this percentage is tracked on a system wide basis per node, not within a service class as one service class uses its allocation of temporary disk space to fulfill a query, it also reduces the total temporary disk available to all other service classes even if those service classes have a max temp disk usage set, if the total available temp disk space is below the amount needed to run a query, it can fail for example, if a higher priority service class is using over 50% of the unused disk space during a medium priority query that needs to spill to disk, the medium priority query fails during its first attempt to spill without using any disk space spill to disk scheduling uses the same priority used for normal execution step 4 set up the low priority service class user groups that are less performance sensitive fall under the low priority service class the design goal is for these queries to have minimal effect on other workloads examples of users in this service class are analysts who want to run ad hoc queries or processes that run time insensitive background reports example service class create service class low priority sc scheduling priority = 1 0, max concurrent queries = 1, max elapsed time = 60, max rows returned = 100, max temp disk usage = 0, cache max bytes = 0, cache max time = 0, max elapsed time for caching = 0; example system catalog view select from sys service classes; id database id name max temp disk usage max elapsed time max concurrent queries max rows returned scheduling priority cache max bytes cache max time max elapsed time for caching max columns in result set priority adjustment factor priority adjustment time min priority max priority \ 42649e8e ae7f 4de5 bb2b 846d158fcb91 23291ad0 ce94 4e1a bdc9 178463ef6ec4 default 100 1 1 1 1 0 1 1 1 1 0 0 0 0 0 1 0 06b92c4c b0b1 4a14 880c 7d257af3b2b5 23291ad0 ce94 4e1a bdc9 178463ef6ec4 high priority sc 100 3600 10 1 100 0 1 1 1 1 0 0 0 0 0 1 0 bf3a939d d773 4c7c 9c30 4bc14e7b9422 23291ad0 ce94 4e1a bdc9 178463ef6ec4 medium priority sc 50 600 2 1 10 0 1 1 1 1 0 0 0 0 0 1 0 dd1f9f03 edff 4c7e acd7 32997b6fceaa 23291ad0 ce94 4e1a bdc9 178463ef6ec4 low priority sc 0 60 1 100 1 0 0 0 0 1 0 0 0 0 0 1 0 scheduling priority — for a low priority, this value should be set to a low number like 1 0 if a service class wants to be lower priority than default queries, it can schedule a value lower than 1 0 max temp disk usage — setting this limitation to 0 means that the system does not allow any queries in the low priority service class to spill to disk in this example, the goal of the designer is to prevent the low priority service class from impacting more important workloads that might need the entire temp disk space in other cases, it could be suitable to allow low priority service class queries to use temp disk cache max bytes , cache max time , max elapsed time for caching — result set caching is a feature that attempts to cache result sets for commonly used queries this result set cache is shared across service classes for a lower priority user, it makes sense to limit the time allowed in the cache or cap the maximum number of bytes used in the cache so a higher priority service class can take advantage of it in this example, the result set cache is explicitly disabled for low priority users by setting these values to zero for details, see the docid\ psmzptrqhyaatbbbo4gum step 5 assign service classes to user groups after setting up the service classes, you need to assign them to user groups when a service class is assigned to user groups, users in each user group can run queries under the restrictions set for the given service class this process can be repeated for all service classes and users examples / create a user group / create group low priority group; / assign the service class to the user group / alter group low priority group set service class = low priority sc; / add a user to a user group / alter group low priority group add user analyst1; dynamic workload management priority adjustment when a query executes, you can set the priority of a query at the service class level, database session level, or the query level the system chooses the maximum value from these three priority levels while the engine executes a query, the engine can dynamically adjust the priority using a specified adjustment interval you can change the percentage amount to dynamically adjust query priority using the priority adjustment factor (or adjustfactor ) and the time period that indicates the frequency for the adjustment of the query priority during query execution using the priority adjustment time (or adjusttime ) you can override this limit at the query level or the session level define the priority at the service level by creating service classes you can override the priority at the service level by using the priority at the session or query level for details, see docid\ hcnnsmzcvpai1kqlhtzud define the priority at the session level using the command line this code instructs the engine to dynamically adjust the priority every 15 seconds and decrease the query priority by 25% set priority "5 0"; set adjusttime 15; set adjustfactor "0 75"; to reset the priority adjustment factor, enter this sql statement set adjustfactor reset; or, define the priority at the query level in the sql statement, such as this statement select from table1 using scheduling priority = 10 0, priority adjustment factor = 1 75, priority adjustment time = 120; alternatively, you can also specify a service class to use at the query level this example creates the service class lower priority and assigns it to the query create service class lower priority scheduling priority = 10 0, priority adjustment factor = 0 75, priority adjustment time = 120; select from table1 using service class lower priority; if you do not identify a specific service class for a query with the using service class clause and the query does not match any service classes that use the statement text parameter, the query uses a composite of least restrictive parameters allowed by the service classes eligible for the query using a service class at the query level requires a user to be assigned to a user group that is included in that specific service class low latency settings low latency service class settings are intended to reduce runtime overhead for classes of subsecond queries with minimal processing work classes of queries that are good candidates utilize indexes on their filters, have low cardinality, and perform minimal work such as simple aggregations or scalar functions low latency service class settings might hinder query performance when queries have high cardinality or perform computationally intense work workload management is key to isolating these simple, low latency queries to specific service classes where you can apply these optimizations in isolation queries for latency sensitive applications, such as populating live dashboards and key value retrieval might benefit from service class level settings that help the ocient system to make runtime choices to reduce latency these choices often help queries with low cardinality and minimal computational work, but they can be detrimental to queries that violate these assumptions database administrators should use service classes to isolate low latency components of a system from computationally intensive components a system administrator can turn on one simple or two advanced settings setting description value low latency this optional service class setting enables you to control the half parallelism and load balance shuffle settings when you set this setting to true , it is equivalent to setting half parallelism = true and load balance shuffle = false when you do not set this setting or set it to false , the ocient system clears the half parallelism and load balance shuffle settings optionally set to true or false the default value is unset half parallelism this optional service class setting controls execution parallelism overrides when you do not set this setting, the system uses cost based heuristics to determine runtime parallelism when you set this setting to true , the system always uses half parallelism for queries that match this service class when you set this setting to false , the system always uses full parallelism for queries that match this service class half parallelism can help latency by reducing the amount of administrative work the system needs to do to run a query, but it reduces the computational power allocated to the query optionally set to true or false the default value is unset load balance shuffle this optional service class setting controls load balancing shuffles above i/o operators when you do not set this setting, the ocient system uses statistics based heuristics to determine whether to use load balance shuffles when you set this setting to true , the system always performs a load balance shuffle for queries that match this service class when you set this setting to false , the system never performs a load balance shuffle for queries that match this service class load balance shuffles are useful for distributing uneven data distributions on disk, which can occur during live loading of tables or small cardinality tables with compute heavy computations however, this load balancing incurs some system coordination overhead, which hampers low latency queries optionally set to true or false the default value is unset there is further convenience for organizing these parameters in a service class by enclosing half parallelism or load balance shuffle settings in a low latency key value setting map create service class ll service class low latency = {half parallelism = true, load balance shuffle = true}; select from table1 using service class ll service class; other service class considerations service classes are a powerful tool to properly allocate resources in the database to the users that need them most when deciding on the restrictions for your individual use case, consider the total number of service classes that the system creates some settings like max concurrent queries and result set caching values like cache max bytes and cache max time can have a cumulative effect on resource availability as the number of service classes increases for details about result set caching, see docid\ psmzptrqhyaatbbbo4gum for example, concurrency limitations are set on an individual service class level while the intention might be to limit low priority service classes to only a couple of concurrent queries, the total effect of many service classes with a couple of concurrent queries each can start to affect high priority workloads due to the number of concurrent queries running across all lower priority service classes related links docid\ sibw5ky21wk028mfgeqzs docid 2wcd8nmbi5m9fctufiqtm docid\ twelobi0rarxjfiot1a70