Database Query Step Resource Allocation for SLG Satisfaction
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face challenges in managing mixed workload queries, particularly in consistently meeting service level goals (SLGs) without requiring user input for resource allocations, and in dynamically adjusting resources to ensure queries meet their SLGs without impacting other queries.
Innovation Solution
The system implements an automatic resource allocation technique that decomposes queries into steps and assigns them to different levels of priority for resource allocation (LPRAs) based on projected execution times and progress, dynamically adjusting resource allocation to ensure queries meet their SLGs with minimal impact on other queries.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If resource allocation is based on worst-case queries, then SLGs can be met for all WDs, but queries requiring fewer resources receive more than they need and complete much earlier than their SLGs without reward
Solution Approach 1:
The system dynamically adjusts resource allocation from static WD-level allocations to query-level allocations that change during execution. The query dispatcher monitors query progress and dynamically reassigns steps to different LPRAs based on actual execution status, allowing queries to receive more resources when behind schedule and fewer resources when ahead, eliminating premature completion without SLG reward.
Solution Approach 2:
The patent segments queries into multiple executable steps and segments resource allocation into multiple LPRAs (Levels of Priority for Resource Allocation). Each step can be independently assigned to different LPRAs, enabling granular control over resource distribution throughout query execution rather than allocating all resources at once based on worst-case estimates.
2Reliability
If resource allocations are adjusted at the WD level to speed up a specific query, then the query can meet its SLG, but other queries in the same WD are also speeded up unnecessarily
Solution Approach 1:
The system applies local quality by assigning different LPRAs to different steps of the same query based on their individual execution status. Instead of uniformly increasing resources for all queries in a WD, the system selectively applies higher priority LPRAs only to specific steps that are behind schedule, while other steps continue at normal priority levels.
3Ease of operation
If user input is required for resource allocation determination, then appropriate allocations can be specified, but the process becomes laborious trial-and-error and becomes more difficult when multiple WDs exist
Solution Approach 1:
The system performs self-service by automatically determining resource allocations without user input. The query dispatcher autonomously analyzes query progress, compares it against SLGs, and selects appropriate LPRAs based on predefined policies, eliminating the need for users to perform laborious trial-and-error allocation configuration.
4Reliability
If sufficient resources are allocated to meet SLGs for all WDs, then all queries can satisfy their SLGs, but there may not be sufficient resources available when allocations are based on worst cases
Solution Approach 1:
The system dynamically adjusts resource consumption based on actual query progress rather than reserving resources for worst-case scenarios. Queries receive resources proportional to their actual needs during execution, allowing the system to meet SLGs for all WDs within available resource constraints by avoiding over-allocation to queries that complete early.
Data Source
AI summary
A database system receives a query to be processed. The database system has resources. A user assigns the query to a tier of resource allocation priorities in a hierarchy of tiers. The tier has been designated as being automatically managed by the database system. The tier has a plurality of levels of priority for resource allocation (LPRAs). The database system decomposes the query into a first step and a set of subsequent steps. The first step has a beginning and each of the set of subsequent steps has a respective beginning. The database system assigns the first step to a first LPRA, wherein executing the query at the first LPRA is projected by the database system to satisfy a service level goal (SLG) within a on_schedule_range of the SLG. The database system determines during execution of the set of subsequent steps that the query is no longer projected to satisfy the SLG within the on_schedule_range of the SLG and, as a result, assigns one of the set of subsequent steps to a second LPRA different from the first LPRA, wherein executing the query at the second LPRA is projected by the database system to return execution of the query to within the on_schedule_range of the SLG.