Interactive Database Query Scheduling with SLA Enforcement

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Current distributed SQL query systems lack service level assurances (SLAs) for query latency and do not allow users to connect to their own custom metastores, leading to high costs and unreliable query performance.

Innovation Solution

A system that schedules interactive database queries using a workflow manager, resource estimator cluster, and execution clusters, enabling proactive autoscaling and resource isolation, while allowing queries to run on custom metastores, ensuring SLAs are met by estimating resources and scaling clusters accordingly.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If enterprise customers run large presto clusters to enable data analysts and data scientists to issue queries at large scale, then query processing capability is improved, but total cost of ownership increases considerably

Engineering Contradiction:

Improvequery processing capabilityVSAvoidtotal cost of ownership

Core Design Contradiction:

ProductivityVSQuantity of substance

Solution Approach 1:

The system dynamically scales compute resources based on workload demand. Execution clusters are automatically provisioned or de-provisioned depending on the number of pending queries and resource availability, allowing the system to maintain high query processing capability when needed while reducing costs during low-utilization periods.

Inventive Principle:

Principle #15Dynamics

Solution Approach 2:

The system changes the parameter of resource allocation from static (fixed cluster size) to dynamic (variable cluster size based on demand). By monitoring queue depth and adjusting the number of execution clusters accordingly, the system optimizes the balance between query processing capability and total cost of ownership.

Inventive Principle:

Principle #35Parameter changes

2Quantity of substance

If serverless interactive query systems are used to reduce total cost of ownership, then cost is reduced, but service level assurances for query latency are not provided

Engineering Contradiction:

Improvetotal cost of ownershipVSAvoidservice level assurances

Core Design Contradiction:

Quantity of substanceVSReliability

Solution Approach 1:

The workflow manager continuously monitors the state of execution clusters and query queues, using this feedback to make real-time decisions about resource allocation. When query latency SLAs are at risk of being violated, the system proactively provisions additional resources. When SLAs are comfortably met, resources are released to reduce costs.

Inventive Principle:

Principle #23Feedback

Solution Approach 2:

The system performs preliminary resource estimation for incoming queries and proactively provisions execution clusters before queries are submitted. This advance preparation ensures that resources are available when needed to meet SLAs, while avoiding over-provisioning by only allocating resources based on actual demand patterns.

Inventive Principle:

Principle #10Preliminary action

3Reliability

If resources are over-provisioned to ensure service level agreements are met, then query reliability is improved, but resource utilization efficiency decreases

Engineering Contradiction:

Improvequery reliabilityVSAvoidresource utilization efficiency

Core Design Contradiction:

ReliabilityVSLoss of energy

Solution Approach 1:

The system dynamically adjusts resource provisioning based on real-time workload conditions. Execution clusters are provisioned or de-provisioned according to the depth of the query queue and current resource utilization, ensuring that resources are available to meet reliability targets only when actually needed, rather than maintaining static over-provisioning at all times.

Inventive Principle:

Principle #15Dynamics

4Loss of energy

If multiple tenants share the same execution clusters, then resource utilization is improved, but query reliability and isolation decrease

Engineering Contradiction:

Improveresource utilizationVSAvoidquery reliability

Core Design Contradiction:

Loss of energyVSReliability

Solution Approach 1:

The system segments the multi-tenant execution environment into isolated workgroups, where each workgroup has dedicated resources and isolation guarantees. This segmentation allows multiple tenants to share the overall system resources efficiently while maintaining reliability and isolation within each workgroup boundary through resource quotas and scheduling policies.

Inventive Principle:

Principle #1Segmentation

Data Source

PatentUS11144360B2System and method for scheduling and running interactive database queries with service level agreements in a multi-tenant processing system

Publication Date: 2021.10.12 QUBOLE INC

AI summary

The invention is directed to systems and methods for scheduling interactive database queries from multiple tenants onto distributed query processing clusters with service level agreements (SLAs). SLAs may be provided through a combination of estimation of resources per query followed by scheduling of that query onto a cluster if enough resources are available or triggering proactive autoscaling to spawn new clusters if they are not. In some embodiments systems may include a workflow manager; a resource estimator cluster; one or more execution clusters; and one or more metastores. A workflow manager may include an active node and a passive node configured to send a query to the resource estimator cluster and receive a resource estimate. A resource estimator cluster may be in communication with the workflow manager. One or more execution clusters may be scaled by the workflow manager as part of a schedule or autoscale based on workload.