Adaptive Segmentation for Concurrent Query Execution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Business intelligence and enterprise data warehouse systems face inefficiencies due to the need to manage varying query complexities and resource allocation across multiple CPUs, where queries of different sizes require different degrees of parallelism to optimize resource use, balance workload, and minimize resource contention.
Innovation Solution
The system dynamically adjusts the degree of parallelism for each query based on estimated resource requirements and uses adaptive segmentation to balance workload across CPUs, employing an executive server process distribution scheme that selects CPU subsets based on affinity values generated by a workload management service, which considers runtime feedback to optimize CPU utilization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If all queries run at full degree of parallelism on all CPUs, then large queries can process billions of rows effectively, but small and medium queries incur unnecessary overhead and resource waste
Solution Approach 1:
The patent segments the CPU pool into multiple subsets and assigns different degrees of parallelism to different queries based on their size and complexity. Small queries are executed on fewer CPUs while large queries utilize all available CPUs, thereby avoiding the overhead of full parallelism for small tasks while maintaining high throughput for large queries.
2Ease of operation
If queries are distributed evenly across all CPUs, then system resource utilization appears balanced, but resource contention and context switching increase
Solution Approach 1:
The patent applies local quality by assigning queries to specific CPU subsets based on their individual resource requirements. Instead of uniform distribution, each query receives a tailored allocation of CPU resources, optimizing the balance between workload distribution and minimizing context switching for that specific query's needs.
3Productivity
If the system dynamically adjusts degree of parallelism per query, then resource utilization efficiency improves, but system complexity increases
Solution Approach 1:
The patent implements dynamic adjustment of parallelism degree through an affinity value mechanism. The system dynamically determines appropriate CPU subset sizes and compositions based on query characteristics and current system state, allowing flexible resource allocation without requiring complex manual configuration or management.
4Loss of energy
If CPU subsets are assigned based on query size, then small queries avoid unnecessary overhead, but ensuring fair workload balance across all CPUs becomes more difficult
Solution Approach 1:
The patent introduces affinity values as an additional dimension for CPU subset selection. This allows the system to optimize for both query size (reducing overhead) and workload balance (improving fairness) simultaneously by selecting CPU subsets based on multiple criteria rather than a single factor.
Data Source
AI summary
An enterprise data warehouse or business intelligence system having a plurality of processors and memory resources. The system includes at least a database server, a workload management system, a query compiler, and a query executor. The database server configured to receive a database query from an application at a database client system. The query compiler configured to prepare the execution plan for the query and compute the number of executive server processes (ESPs) in each ESP layer of the query. The workload management system is configured to generate an affinity value, and the query executor is configured to execute the query. As disclosed herein, placement of the executive server process layers of the query onto processors of the computing system is determined using the affinity value. Other embodiments, aspects and features are also disclosed.