MPP Database Partition Routing for IUD Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Massively Parallel Processing (MPP) Database Management Systems (DBMS) face performance issues with Insert/Update/Delete operations due to increased network traffic and skewed data distribution, which negatively impact both IUD and query operations.
Innovation Solution
Enabling IUD operations by identifying optimized partitions and generating new partition maps for insert operations, allowing direct routing of IUD requests without regenerating hash values, thus reducing network bottlenecks and maintaining query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If the MPP DBMS control node evaluates partition routing for IUD operations, then IUD operations can be executed, but network traffic increases and performance deteriorates
Solution Approach 1:
The patent segments the partition routing function by creating dedicated IUD partitions that handle insert, update, and delete operations separately from query partitions. This segmentation allows IUD operations to be routed directly to specialized partitions without requiring control node evaluation, thereby improving network performance while maintaining IUD operation capability.
Solution Approach 2:
The patent introduces partition routing tables as an intermediary mechanism that pre-computes and stores the mapping between distribution keys and partition identifiers. This intermediary eliminates the need for real-time control node evaluation during IUD operations, reducing network traffic and improving performance.
2Device complexity
If the MPP DBMS uses a single partition map for both query and IUD operations, then system complexity is reduced, but query performance deteriorates due to network bottlenecks
Solution Approach 1:
The patent divides the partition management system into separate query partitions and IUD partitions, each with its own partition map. This segmentation allows query operations to use a partition map optimized for read performance while IUD operations use a partition map optimized for write performance, eliminating the network bottlenecks that would occur with a single shared partition map.
Solution Approach 2:
The patent applies local quality by creating partition maps with different characteristics for different operation types. The query partition map is optimized for efficient data location during reads, while the IUD partition map is optimized for efficient data routing during writes, allowing each partition map to have the local quality needed for its specific purpose.
3Manufacturing precision
If the MPP DBMS regenerates hash values for each IUD operation, then data distribution accuracy is improved, but processing time increases
Solution Approach 1:
The patent applies preliminary action by pre-computing the hash values and storing them in the partition routing tables during partition creation or data loading. This preliminary computation eliminates the need to regenerate hash values for each IUD operation, maintaining data distribution accuracy while significantly reducing processing time for insert, update, and delete operations.
Data Source
AI summary
A method, computer program product, and computer system are provided. Based on an insert/update/delete (IUD) feature being enabled, and based on receiving an IUD request at a massively parallel processing (MPP) database management system (DBMS) engine, identifying a partition optimized for the IUD request. A partition map is loaded corresponding to a partition optimized for only IUD requests. Based on the IUD request being an insert row operation, a new distribution key is calculated by hash. A new partition map is generated, the insert row operation is executed using the new partition map.