Workload Prediction Framework for Dynamic Database Layout Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing automated physical database design approaches fail to address workload drifts, leading to suboptimal performance and increased memory consumption due to their reliance on static workloads.
Innovation Solution
A workload prediction framework that generates Statement Arrival Rate (SAR) and host variable assignment predictions based on observed workload information, enabling the automatic creation of workload predictions and the generation of recommended physical database layouts.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If automated physical database design approaches use static workload analysis, then the design process is simplified and can be performed efficiently, but the physical layout becomes suboptimal when workload changes occur, leading to degraded performance and increased memory consumption
Solution Approach 1:
The patent transforms the static workload analysis into a dynamic system by implementing workload prediction mechanisms that continuously monitor observed workload patterns and generate future workload estimates. This allows the physical database design to adapt to changing workload conditions, resolving the contradiction between design efficiency and performance stability by enabling automated reoptimization when workload drift is detected
Solution Approach 2:
The patent applies preliminary action by predicting future workload characteristics before they fully manifest, allowing the system to proactively adjust physical layouts in advance. The workload prediction framework analyzes historical patterns and generates forecasts that trigger design optimizations before performance degradation occurs, maintaining reliability while preserving design efficiency through automated preliminary adjustments
2Speed
If the physical layout is optimized for current workload, then performance is maximized for the observed workload, but memory consumption increases and the layout becomes inappropriate when workload drifts occur
Solution Approach 1:
The patent implements parameter changes by dynamically adjusting physical layout parameters based on predicted workload characteristics. The system monitors workload patterns and modifies layout parameters such as table partitioning, indexing strategies, and data organization to match anticipated workload demands, thereby maintaining optimal performance while reducing unnecessary memory consumption that would result from static optimization
3Manufacturing precision
If manual physical database design is performed by database experts, then optimal layouts can be found for specific workloads, but the process is time-consuming and cannot keep pace with changing workloads
Solution Approach 1:
The patent applies self-service by implementing an automated system that performs physical database design without requiring database experts. The workload prediction framework combined with the physical database design advisor creates a self-optimizing system that automatically monitors workload, predicts future patterns, and generates optimized layouts, eliminating the time cost of manual expert intervention while maintaining high optimization quality through algorithmic precision
Solution Approach 2:
The patent implements feedback mechanisms where the system continuously monitors actual workload execution, compares it with predicted workload, and uses this feedback to refine future predictions and adjustments. This closed-loop feedback system enables automated iterative optimization that matches or exceeds expert manual design quality while operating continuously without time loss
Data Source
AI summary
According to some embodiments, methods and systems may be associated with a cloud computing environment. A workload prediction framework may receive observed workload information associated with a database in the cloud computing environment (e.g., a DataBase as a Service (“DBaaS”)). Based on the observed workload information, a Statement Arrival Rate (“SAR”) prediction may be generated. In addition, a host variable assignment prediction may be generated based on the observed workload information. The workload prediction framework may then use the SAR prediction and the host variable assignment prediction to automatically create a workload prediction for the database. A physical database design advisor (e.g., a table partitioning advisor) may receive the workload prediction and, responsive to the workload prediction, automatically generate a recommended physical layout for the database (e.g., using a cost model, the current physical layout, and an objective function).