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. Remember to use the correct naming convention to name your service level. If you decide to use Reserved Keywords, enclose them in either single or double quotes (for example 'primary').

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:

• service_level_name - Specifies the name of the service level you created. See Create a Service Level.

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

Procedure

Run the following:

DROP SERVICE_LEVEL IF EXISTS OLTP;


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.