Isolate interactive and batch workloads

Use a workload tree when interactive queries and batch processing should share a server without sharing the same scheduling policy.

Before you begin

• Set RAWTREE_API_KEY to an administrator API key.
• Use analytics. If it does not exist, create it with the POST /v1/databases endpoint.
• Pass ?database=analytics on each query. If you omit it, the Query API uses the API key's stored default database.
• Apply workload statements to the default cluster.

1. Create the CPU resource

Create a CPU resource.

CREATE RESOURCE cpu ON CLUSTER 'default' (MASTER THREAD, WORKER THREAD)

2. Create the root workload

Create the root workload.

CREATE WORKLOAD all ON CLUSTER 'default' SETTINGS max_concurrent_threads = 4

3. Add a default child workload

Create the default child workload.

CREATE WORKLOAD default ON CLUSTER 'default' IN all

4. Add an interactive workload

Give interactive queries more weight and two threads.

CREATE WORKLOAD interactive ON CLUSTER 'default' IN all SETTINGS weight = 3, priority = 1, max_concurrent_threads = 2 FOR cpu

5. Add a batch workload

Give batch queries less weight and one thread.

CREATE WORKLOAD batch ON CLUSTER 'default' IN all SETTINGS weight = 1, priority = 5, max_concurrent_threads = 1 FOR cpu

6. Select a workload

Select the interactive workload for one query.

SELECT count() FROM system.one SETTINGS workload = 'interactive'

7. Inspect workload definitions

List the workload definitions.

SELECT name FROM system.workloads

8. Inspect scheduler resources

List the scheduler resources.

SELECT name FROM system.resources

9. Inspect scheduler nodes

Inspect the request counts for the interactive workload.

SELECT resource, path, dequeued_requests, inflight_requests
FROM system.scheduler
WHERE path LIKE '%interactive%'

The dequeued_requests count increases for the interactive path after a query uses that workload. inflight_requests shows requests that run now. is_active is 1 only when requests wait in the queue.

Check the result

Run a query with SETTINGS workload = 'interactive'. After it finishes, inspect dequeued_requests in system.scheduler. While it runs, inspect inflight_requests.

Cleanup

Drop interactive before you drop its parent workload.

DROP WORKLOAD interactive ON CLUSTER 'default'

Drop batch before you drop its parent workload.

DROP WORKLOAD batch ON CLUSTER 'default'

Drop default before you drop the root workload.

DROP WORKLOAD default ON CLUSTER 'default'

Drop the root workload.

DROP WORKLOAD all ON CLUSTER 'default'

Drop the CPU resource after you drop its workloads.

DROP RESOURCE cpu ON CLUSTER 'default'

Next steps

• Workload scheduling concepts
• Lock a role to a workload
• Workload statements