Database Workload Reduction for Faster Index Tuning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Index tuning in large databases is resource-intensive and can cause bottlenecks, particularly when scaling to handle complex workloads, leading to high operational costs and disruptions in database operations.
Innovation Solution
A workload reduction system that uses table and query re-writing functions to generate simplified queries by removing lower-value tables and columns, reducing the complexity of workloads before index tuning, thereby accelerating the index-tuning process.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional index tuning processes are used on large databases, then comprehensive index optimization can be achieved, but the process becomes extremely resource-intensive and causes operational bottlenecks
Solution Approach 1:
The patent segments the workload into multiple partitions and processes each partition independently with a distributed index tuning system. This allows the overall index tuning task to be divided into smaller, parallelizable units that can be processed simultaneously across multiple computing nodes, maintaining optimization quality while significantly improving throughput and reducing bottlenecks.
Solution Approach 2:
The patent performs preliminary actions by collecting and analyzing workload information before executing the full index tuning process. The system pre-processes workload data, identifies query patterns, and prepares optimization strategies in advance, which reduces the computational burden during the actual index tuning execution and accelerates the overall process.
2Measurement precision
If comprehensive workload analysis is performed for index tuning, then optimal index configuration can be achieved, but operational disruptions increase
Solution Approach 1:
The patent performs preliminary workload analysis and index tuning recommendations during off-peak periods or in advance of actual execution. By pre-computing index tuning strategies based on collected workload data, the system minimizes the need for intensive real-time analysis that would cause operational disruptions, while still achieving accurate index configurations.
3Measurement precision
If full workload processing is used in index tuning, then complete optimization coverage is achieved, but computational costs become impractical
Solution Approach 1:
The patent segments the workload into partitions and processes only relevant portions for index tuning. By dividing the workload and selectively processing partitions that require optimization, the system achieves comprehensive coverage across the entire database while reducing the computational cost of processing any single partition, making the overall process scalable to large databases.
Solution Approach 2:
The patent applies partial action by focusing index tuning efforts on specific workload partitions that benefit most from optimization, rather than uniformly processing the entire workload. This selective approach maintains effective optimization coverage while reducing overall computational costs by avoiding redundant processing of already-optimized or low-priority data segments.
Data Source
AI summary
This disclosure describes a workload reduction system that reduces the complexity of workloads sent to a database system. For instance, the workload reduction system pre-processes workloads sent to a database system by generating reduced workloads that include less complex queries that reference fewer tables or columns than the original workloads. In various implementations, the workload reduction system uses table reduction functions and query re-writing functions to generate the reduced workloads. As a result, the workload reduction system improves computational efficiency by rewriting complex queries from workloads into simpler ones that speed up index tuning and decrease individual what-if call times.