Enterprise Data Warehouse Workload Manager for Thrashing Prevention
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Enterprise data warehouses face challenges in managing complex workloads to meet performance objectives, particularly in avoiding underload and overload regions that lead to thrashing and performance deterioration due to fluctuating query mixes and resource intensive queries.
Innovation Solution
A Business Intelligence (BI) workload manager is introduced, comprising an admission controller, scheduler, and execution manager that uses memory as a manipulated variable to stabilize throughput by dividing queries into sub-batches and employing priority gradient multiprogramming to allocate resources effectively, preventing thrashing and optimizing CPU utilization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the number of concurrent queries is increased to improve throughput, then productivity increases, but the system enters overload region causing thrashing and performance deterioration
Solution Approach 1:
The workload manager continuously monitors system state (memory pressure, CPU utilization) and dynamically adjusts the number of concurrent queries admitted to the system. When memory pressure exceeds a threshold, the manager reduces admission of new queries to prevent thrashing, and when pressure is low, it increases admission to maximize throughput. This closed-loop feedback control maintains system stability while optimizing productivity.
Solution Approach 2:
The system transitions from static query admission to dynamic query admission based on real-time memory pressure conditions. The workload manager adjusts the manipulated variable (number of concurrent queries) dynamically according to system state, allowing the system to adapt between underload and overload regions to maintain optimal performance and stability.
2Productivity
If memory- intensive queries are admitted to improve resource utilization, then productivity increases, but memory pressure increases causing thrashing
Solution Approach 1:
The workload manager monitors memory pressure as a key system state metric and uses it to control query admission decisions. When memory pressure is high, the manager restricts admission of memory-intensive queries even if they would improve resource utilization. When memory pressure is low, the manager allows greater admission to maximize resource utilization. This feedback mechanism balances productivity and memory consumption.
Solution Approach 2:
The system changes the operating point by adjusting the number of concurrent queries based on memory pressure levels. Rather than maintaining a fixed query admission rate, the manager varies this parameter dynamically to keep memory pressure within acceptable bounds while maximizing overall resource utilization and throughput.
3Reliability
If the system operates in underload region to maintain stability, then reliability is maintained, but throughput is suboptimal
Solution Approach 1:
The workload manager enables the system to dynamically operate across different load regions rather than being constrained to the underload region. By monitoring memory pressure and adjusting query admission accordingly, the manager allows the system to safely enter and operate in the optimal load region where throughput is maximized, while preventing entry into the overload thrashing region. This dynamic operation achieves both high throughput and system stability.
Data Source
AI summary
Described herein is a workload manager for managing a workload in a database that includes: an admission controller operating to divide the workload into a plurality of batches, with each batch having at least one workload process to be performed in the database, and each batch having a memory requirement based on the available memory for processing workloads in the database; a scheduler operating to assign a unique priority to each of the at least one workload process in each of the plurality of batches, the unique priority provides an order in which each workload process is executed in the database; and an execution manager operating to execute the at least one workload process in each of the plurality of batches in accordance with the unique priority assigned to each workload process.