Autonomous Database Table Partitioning for Workload-Aware Scheme Selection
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Manual partitioning of database tables is prone to inefficiencies and requires expert users, leading to potential mistakes and wasted computing resources, making it difficult for ordinary users to implement optimal partitioning strategies.
Innovation Solution
An automatic partitioner generates and evaluates multiple candidate partition schemes using synthesized statistics, scores them based on workload performance, and implements the best scheme with real data to validate its effectiveness, allowing for autonomous partitioning without user expertise.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If manual partitioning is performed by expert users, then partitioning can be implemented, but it requires expert knowledge and is prone to mistakes
Solution Approach 1:
The system performs self-service by automatically generating partitioning schemes without requiring user expertise. The automatic partitioner analyzes table data and workloads to create optimal partitioning configurations autonomously, eliminating the need for expert user intervention while maintaining high reliability.
Solution Approach 2:
An automatic partitioner acts as an intermediary between the database system and users. It receives user requests for partitioning, performs complex analysis and generation of partitioning schemes, and presents results to users, thereby shielding users from technical complexity while ensuring reliable partitioning outcomes.
2Productivity
If incorrect partitioning schemes are implemented by users, then partitioning can be applied, but computing resources are wasted and performance deteriorates
Solution Approach 1:
The system performs preliminary actions by evaluating multiple candidate partitioning schemes before implementation. The automatic partitioner generates several potential schemes, evaluates their expected performance impact, and selects the optimal one, preventing resource waste from incorrect partitioning decisions before they occur.
Solution Approach 2:
The system uses feedback mechanisms to evaluate partitioning schemes by analyzing workload characteristics and predicting performance outcomes. This feedback loop ensures that only high-quality partitioning schemes that are likely to improve performance are implemented, avoiding resource waste on suboptimal configurations.
3Reliability
If multiple candidate partition schemes are generated and evaluated, then optimal partitioning can be achieved, but system complexity increases
Solution Approach 1:
The automatic partitioner is segmented into distinct functional modules: candidate generation, evaluation, and selection. This segmentation manages complexity by organizing the sophisticated partitioning logic into manageable, independent components that can be developed and maintained separately while working together to achieve optimal partitioning.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
What is disclosed is an improved approach to perform automatic partitioning, without requiring any expertise on the part of the user. A three stage processing pipeline is provided to generate candidate partition schemes, to evaluate the candidate using real table structures that are empty, and to then implement a selected scheme with production data for evaluation. In addition, an improved approach is described to perform automatic interval partitioning, where the inventive concept implements interval partitioning that does not impose these implicit constraints on the partition key column.