Priority Gradient Multiprogramming Database Query Scheduling
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for managing Multi-Programming Level (MPL) in database systems, such as feed-forward, feed-back, and static approaches, face challenges in accurately estimating memory requirements and reacting to rapidly changing workloads, leading to potential thrashing and performance deterioration, especially in Business Intelligence workloads with fluctuating query mixes.
Innovation Solution
The implementation of Priority Gradient Multiprogramming (PGM) which schedules queries on a database system using a priority gradient, restricting the number of queries to consume less than total system memory and dynamically adjusting priorities to prevent overload while maintaining high throughput.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the MPL is increased to achieve higher throughput, then the number of queries processed per unit time increases, but the system may become overloaded and experience severe memory contention and CPU thrashing
Solution Approach 1:
The patent implements dynamic MPL adjustment by continuously monitoring system metrics (CPU utilization, memory usage, I/O activity) and adapting the multiprogramming level in real-time. This allows the system to transition between under-loaded and optimal regions, preventing overload and thrashing while maintaining high throughput. The dynamic nature enables the system to respond to changing workload conditions rather than operating at a fixed MPL.
2Speed
If the sampling interval is made small to quickly detect workload changes, then the system can react faster to prevent overload, but the system may oscillate and become unstable
Solution Approach 1:
The patent implements a feedback control mechanism that samples system metrics at controlled intervals and adjusts the MPL accordingly. The feedback loop monitors CPU utilization, memory usage, and I/O activity, then modulates the admission of new queries to maintain optimal system performance. This feedback approach allows the system to respond to workload changes while maintaining stability through controlled adjustment rates.
3Reliability
If memory allocation is restricted to prevent thrashing, then the system avoids overload, but the optimal MPL cannot be determined without accurate memory requirement estimates which are difficult to obtain for complex BI queries
Solution Approach 1:
The patent enables queries to self-regulate their memory usage through dynamic memory management techniques. Rather than requiring external estimation of memory requirements, the system allows queries to allocate and release memory dynamically based on actual needs and system availability. This self-service approach eliminates the need for complex upfront memory estimation while preventing thrashing through adaptive resource management.
Data Source
AI summary
A workload management system and operating method are configured for query stream execution using priority gradient programming. The workload management system comprises a database system that executes queries at a priority gradient wherein no more than a predetermined number of queries execute at a particular priority, and a scheduler that schedules queries for execution on the database system and restricts the queries to a number that consumes less than total system memory.