Workload Prioritization

In a typical database you have many 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
    • High volume of requests
    • Fast processing
    • In essence - Latency sensitive
  • OLAP (Online Analytical Processing ) - performs data analytics in the background
    • High volume of data
    • Slow queries
    • In essence - Latency agnostic

Using Service Level CQL commands, database administrators working on Scylla Enterprise can now set different workload prioritization (level of service) for each of these workloads without sacrificing latency or throughput. In addition, each service level can be attached to the different roles within your organization ensuring that each role is granted the level of service they require.

Prerequisites

In order to create a level of service and assign it to a role, you need:

Work by Example

To follow the examples in this document, the roles spark and web need to be created. You can assign permissions to these roles later, if needed.

Procedure

Run the following:

CREATE ROLE Spark;
CREATE ROLE Web;

Service Level Management

These commands set, list, and edit the level of service.

Create a Service Level

When you create a service level, you allocate a percentage of resources to the service level.

Syntax

CREATE SERVICE_LEVEL [IF NOT EXISTS] <service_level_name> [WITH SHARES = <shares_number>];

Where:

  • service_level_name - Specifies the name of the service you are 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 are granting to the service level name. You can use any number within the range from 1 to 1000. Default : 1000

Example

You have 3 service levels (OLAP, OLTP, 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:

Service Level Name Shares Percentage of Resources
OLAP 100 4%
OLTP 1000 48%
Default 1000 48%
Total 2100 100%

Procedure

  1. To create these service levels, run the following CQL commands:
CREATE SERVICE_LEVEL IF NOT EXISTS OLAP WITH SHARES = 100;
CREATE SERVICE_LEVEL IF NOT EXISTS OLTP WITH SHARES = 1000;
  1. Confirm the change was made by listing the service level allocations:
LIST ALL SERVICE_LEVELS;

service_level | shares
--------------+-------
         olap |    100
--------------+-------
         oltp |   1000
(2 rows)

Change Resource Allocation for a Service Level

You can change resource allocation for a given service level. If you do not list the shares, the default (1000) is used.

Syntax

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 you are granting to the service level name. You can use any number within the range from 1 to 1000. Default : 1000

Example

Analysts are complaining that they do not have enough resources. To increase the resources, you will change the service level attributes for the OLAP service level.

Procedure

  1. Run the following:
ALTER SERVICE_LEVEL OLAP WITH SHARES = 500;
  1. Confirm the change was made by listing the service level allocation:
LIST SERVICE_LEVEL OLAP;

service_level | shares
--------------+-------
         olap |    500
(1 rows)
  1. To change it back to the original setting (or to remain consistent for the examples that follow) change the shares amount back to the original.
ALTER SERVICE_LEVEL OLAP WITH SHARES = 100;

Display Specified Service Level Parameters

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

LIST SERVICE_LEVEL <service_level_name>;

Where:

Example

In this example you will list the service level parameters for OLTP.

Procedure

Run the following:

LIST SERVICE_LEVEL OLTP;

service_level | shares
--------------+-------
         oltp |   1000
(1 rows)

Display All Service Levels and Parameters

Lists all service levels with their class parameters. This list contains all service levels including those which are assigned to roles.

Syntax

LIST ALL SERVICE_LEVELS;

Example

In this example, you will list all service levels and their parameters.

Procedure

Run the following:

LIST ALL SERVICE_LEVELS;

service_level  | shares
---------------+--------
          olap |     100
          oltp |    1000
(2 rows)

Delete a Service Level

Permanently removes the service level. Any role attached to this service level will automatically be assigned to the Default SLA if there is no other service level attached to the role.

Syntax

DROP SERVICE_LEVEL <service_level_name> IF EXISTS;

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 will drop the OLTP service level.

Procedure

Run the following:

DROP SERVICE_LEVEL OLTP IF EXISTS;

Manage Roles with Service Levels

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.

Assign a Service Level to a Role

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

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 you want to use the service level. This is the role you created with create role.

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:

Service Level Name Role Name
OLAP Spark
OLTP Web

Procedure

To assign these service levels to the roles, run the following CQL commands:

ATTACH SERVICE_LEVEL OLAP TO Spark;
ATTACH SERVICE_LEVEL OLTP TO Web;

List All Attached Service Levels for All Roles

Lists all directly attached service levels for all roles. This does not include any service level which the role inherits from other roles.

Syntax

LIST ALL ATTACHED SERVICE_LEVELS;

Example

In this example you will list all service levels attached to any role.

Procedure

Run the following:

LIST ALL ATTACHED SERVICE_LEVELS;

role   | service_level
-------+---------------
spark  |          olap
-------+---------------
  web  |          oltp

(2 rows)

List the Roles Assigned to a Specific Service Level

Lists all roles directly attached to a service level. This does not include any service level which the role inherits from other roles.

Syntax

LIST ATTACHED SERVICE_LEVEL OF <role_name>;

Where:

  • role_name - Specifies the role you want to use the service level. This is the role you created with create role.

Example

In this example, you will list all of Roles which are assigned to the OLAP Service Level.

Procedure

Run the following:

LIST ATTACHED SERVICE_LEVEL OF Spark;

role   | service_level
-------+---------------
spark  |  olap

(1 rows)

Remove a Service Level from a Role

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

DETACH SERVICE_LEVEL FROM <role_name>;

Where:

  • role_name - Specifies the role you want to use the service level. This is the role you created with create role.

Example

In this example, you will reassign 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:

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.

Using Workload Prioritization with your Application

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 will always be the case with users who sign in anonymously.

Additional References

OLAP or OLTP? Why Not Both? Session by Glauber Costa from Scylla Summit 2018