docs/features/workload-prioritization.rst
In a typical database there are numerous workloads running at the same time. Each workload type dictates a different acceptable level of latency and throughput. For example, consider the following two workloads:
OLTP ( Online Transaction Processing) - backend database for your application
OLAP (Online Analytical Processing ) - performs data analytics in the background
Using Service Level CQL commands, database administrators (working on ScyllaDB) can set different workload prioritization levels (levels of service) for each workload without sacrificing latency or throughput. By assigning each service level to the different roles within your organization, DBAs ensure that each role_ receives the level of service the role requires.
.. _role : /operating-scylla/security/rbac_usecase/
Initially ScyllaDB has two service levels:
'default' - Receives all load unassigned to any other service level.'driver' - Isolates metadata operations (for example, driver schema and topology fetches) and handles new connections before the user is authenticated and authorized. This reduces the impact of connection handling on regular query traffic. In most deployments, you do not need to tune the 'driver' service level.To create a new service level and assign it to a role, you need:
authenticated </operating-scylla/security/runtime-authentication> and :doc:authorized </operating-scylla/security/enable-authorization> userrole created <create-role-statement>.To follow the examples in this document, create the roles spark and web. You can assign permissions to these roles later, if needed.
Procedure
Run the following:
.. code-block:: cql
CREATE ROLE Spark; CREATE ROLE Web;
Create a Service Level_Assign a Service Level to a Role_.. _workload-priorization-service-level-management:
These commands set, list, and edit the level of service.
When you create a service level, you allocate a percentage of resources to the service level. Remember to use the correct naming convention to name your service level. If you decide to use :ref:reserved keywords <appendix-A>, enclose them in either single or double quotes (for example 'primary').
Syntax
.. code-block:: none
CREATE SERVICE_LEVEL [IF NOT EXISTS] <service_level_name> [WITH SHARES = <shares_number>];
Where:
service_level_name - Specifies the name of the service you're creating. This can be any string without spaces. The name should be meaningful, such as class names (silver, gold, diamond, platinum), or categories (OLAP or OLTP), etc.shares_number - The number of shares of the resources you're granting to the service level name. You can use any number within the range from 1 to 1000. Default : 1000Example .......
There are 4 service levels (OLAP, OLTP, driver, default) where: (the percentage of resources = (Assigned Shares / Total Shares) x 100). Total Shares in this case is the total of all allocated shares + the Default SLA (1000). The percentage of resources would be:
.. list-table:: :widths: 30 30 30 :header-rows: 1
Procedure
.. code-block:: cql
CREATE SERVICE_LEVEL IF NOT EXISTS OLAP WITH SHARES = 100; CREATE SERVICE_LEVEL IF NOT EXISTS OLTP WITH SHARES = 1000;
.. code-block:: cql
LIST ALL SERVICE_LEVELS;
service_level | shares --------------+------- driver | 200 --------------+------- olap | 100 --------------+------- oltp | 1000 (3 rows)
You can change resource allocation for a given service level. If you don't specify the number the shares, the default setting (1000) is used.
Syntax
.. code-block:: none
ALTER SERVICE_LEVEL <service_level_name> WITH SHARES = <shares_number>;
Where:
service_level_name - Specifies the name of the service level you created. See Create a Service Level_.shares_number - The number of shares in the CPU that you're granting to the service level name. You can use any number within the range from 1 to 1000. Default : 1000.. warning::
Altering the SERVICE LEVEL does not affect active sessions (see #12923 <https://github.com/scylladb/scylladb/issues/12923>_).
To apply a new timeout to existing clients, execute a :doc:rolling restart </operating-scylla/procedures/config-change/rolling-restart> after the ALTER command.
Example ........
Analysts are complaining that they don't have enough resources. To increase the resources, you change the service level attributes for the OLAP service level.
Procedure
.. code-block:: cql
ALTER SERVICE_LEVEL OLAP WITH SHARES = 500;
.. code-block:: cql
LIST SERVICE_LEVEL OLAP;
service_level | shares --------------+------- olap | 500 (1 rows)
.. code-block:: cql
ALTER SERVICE_LEVEL OLAP WITH SHARES = 100;
Lists the specified service level with its class parameters. If the service level is attached to a role it does not appear in this list.
Syntax
.. code-block:: none
LIST SERVICE_LEVEL <service_level_name>;
Where:
service_level_name - Specifies the name of the service level you created. See Create a Service Level_.Example .......
In this example you list the service level parameters for OLTP.
Procedure
Run the following:
.. code-block:: cql
LIST SERVICE_LEVEL OLTP;
service_level | shares --------------+------- oltp | 1000 (1 rows)
Lists all service levels with their class parameters. This list contains all service levels including those which are assigned to roles.
Syntax
.. code-block:: none
LIST ALL SERVICE_LEVELS;
Example .......
In this example, you list all service levels and their parameters.
Procedure
Run the following:
.. code-block:: cql
LIST ALL SERVICE_LEVELS;
service_level | shares ---------------+-------- driver | 200 olap | 100 oltp | 1000 (3 rows)
Permanently removes the service level. Any role attached to this service level is automatically assigned to the Default SLA if there is no other service level attached to the role.
Syntax
.. code-block:: none
DROP SERVICE_LEVEL IF EXISTS <service_level_name>;
Where:
service_level_name - Specifies the name of the service level you created. See Create a Service Level_.IF EXISTS - If the service level does not exist and IF EXISTS is not used an error is returned.Example .......
In this example you drop the OLTP service level.
Procedure
Run the following:
.. code-block:: cql
DROP SERVICE_LEVEL IF EXISTS OLTP;
Once you have created roles and service levels you can attach and remove the service levels from the roles and list which roles are attached to which service levels.
If you have created a role and a service level, you can attach the service level to the role.
.. note:: A role can only be assigned one service level. However, the same service level can be attached to many roles. If a role inherits a service level from another role, the highest level of service from all the roles wins.
Syntax
.. code-block:: none
ATTACH SERVICE_LEVEL <service_level_name> TO <role_name>;
Where:
service_level_name - Specifies the name of the service level you created. See Create a Service Level_.role_name - Specifies the role that you want to use the service level on. This is the role you created with :ref:create role <create-role-statement>... note:: Any role which does not have an SLA attached to it, receives the default SLA.
Example .......
Continuing from the example in Create a Service Level_, you can attach the service levels that you created to different roles in your organization as follows:
.. list-table:: :widths: 50 50 :header-rows: 1
Procedure
To assign these service levels to the roles, run the following CQL commands:
.. code-block:: cql
ATTACH SERVICE_LEVEL OLAP TO Spark; ATTACH SERVICE_LEVEL OLTP TO Web;
Lists all directly attached service levels for all roles. This does not include any service level which the role inherits from other roles.
Syntax
.. code-block:: none
LIST ALL ATTACHED SERVICE_LEVELS;
Example .......
In this example you list all service levels attached to any role.
Procedure
Run the following:
.. code-block:: cql
LIST ALL ATTACHED SERVICE_LEVELS;
role | service_level
-------+---------------
spark | olap
-------+---------------
web | oltp
(2 rows)
Lists all roles directly attached to a service level. This does not include any service level which the role inherits from other roles.
Syntax
.. code-block:: none
LIST ATTACHED SERVICE_LEVEL OF <role_name>;
Where:
role_name - Specifies the role that you want to use the service level on. This is the role you created with :ref:create role <create-role-statement>.Example .......
In this example, you list all of Roles which are assigned to the OLAP Service Level.
Procedure
Run the following:
.. code-block:: cql
LIST ATTACHED SERVICE_LEVEL OF Spark;
role | service_level -------+--------------- spark | olap
(1 rows)
Removes a service level from a specified role. Once the service level is removed from a role, if there are other service levels attached to roles which that role inherits, the service level in the hierarchy with the most amount of shares wins.
Syntax
.. code-block:: none
DETACH SERVICE_LEVEL FROM <role_name>;
Where:
role_name - Specifies the role that you want to use the service level on. This is the role you created with :ref:create role <create-role-statement>.Example .......
In this example, you re-assign the Spark to a different level of service by detaching it from one level of service and attaching it to another.
Procedure
Run the following:
.. code-block:: cql
DETACH SERVICE_LEVEL FROM Spark;
At this point, the Spark role receives the Default SLA, until it is assigned another service level. You assign a new service level to this role using Assign a Service Level to a Role_.
In order for workload prioritization to take effect, application users need to be assigned to a relevant role. In addition, each role you create needs to be assigned to a specific Service Level. Any user that signs into the application without a role is automatically assigned the Default service level. This is always be the case with users who sign in anonymously.
ScyllaDB is limited to 10 service levels, including the default and driver levels; this means you can create up to 8 service levels.
OLAP or OLTP? Why Not Both? <https://www.youtube.com/watch?v=GhmgwN6ZraI>_ Session by Glauber Costa from Scylla Summit 2018
Scylla University: Workload Prioritization lesson <https://university.scylladb.com/courses/scylla-operations/lessons/workload-prioritization/>_ - The lesson covers: