Database Data Partitioning for Mixed Transaction and Analytical Workloads
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems struggle to efficiently manage mixed loads comprising both transaction and analytical loads, as prior data partitioning methods either compromise transaction load efficiency or fail to optimize performance for mixed loads, leading to reduced working performance.
Innovation Solution
A data partitioning method that determines tuple relationship information and split cost between tuples, using an image segmentation algorithm to generate multiple partitioning schemes and select an optimum scheme with a minimum total cost value, ensuring efficient access for both transaction and analytical loads by optimizing data storage across partitions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If data partitioning is performed using traditional hash function methods optimized for analytical loads, then analytical load access efficiency is improved, but transaction load processing efficiency deteriorates
Solution Approach 1:
The patent segments the database system into multiple partitioning schemes: a first partitioning scheme optimized for analytical loads (using hash functions) and a second partitioning scheme optimized for transaction loads (using range-based or other methods). The system dynamically selects or combines these schemes based on the specific load type, allowing each to operate at optimal efficiency without compromising the other.
Solution Approach 2:
The patent implements dynamic partitioning scheme selection that adapts to the current workload characteristics. The system determines whether incoming queries are analytical or transactional in nature and applies the appropriate partitioning scheme accordingly, making the system flexible and responsive to changing workload demands rather than being static.
2Device complexity
If a single partitioning scheme is used for both transaction and analytical loads, then system complexity is reduced, but overall working performance deteriorates
Solution Approach 1:
The patent creates a universal partitioning framework that incorporates multiple partitioning schemes within a single system architecture. The framework can handle both analytical and transactional workloads by selecting the appropriate scheme, making the system multi-functional without requiring separate database systems. This resolves the contradiction by providing both simplicity (single system) and performance (multiple optimized schemes).
3Reliability
If data is stored in centralized control for transaction loads, then transaction processing reliability is improved, but analytical query performance deteriorates
Solution Approach 1:
The patent adds a temporal and contextual dimension to data storage by maintaining multiple partitioning schemes that can be activated based on the query type. Instead of a single spatial arrangement, the system can transform the data access pattern by switching between different partitioning dimensions (hash-based for analytical, range-based for transactions), allowing both reliability and performance to be optimized simultaneously.
Data Source
Figure 1
Figure 2A~2B
Figure 3
AI summary
Embodiments of the present invention provide a data partitioning method and apparatus. The method includes: determining tuple relationship information according to received mixed loads and structure information of a database; determining tuple split cost information according to a feature about whether the mixed loads are executable in parallel and the tuple relationship information; obtaining multiple partitioning schemes according to the tuple split cost information, and determining, from the partitioning schemes, a partitioning scheme with a minimum total cost value as an optimum partitioning scheme to perform partitioning processing on data stored in the database. In the data partitioning method and apparatus, according to a feature about whether mixed loads are executable in parallel, optimum partitioning is performed on data associated with the mixed loads in a database, after partitioning, data has features of a transaction load and an analytical load in the mixed loads, so that when different loads access data in a database system, the loads can obtain higher access efficiency, thereby improving working performance of the database system oriented to the mixed loads.