Distributed Join Optimization via Dual Query Engine Architecture
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Distributed database systems face inefficiencies in query processing due to data being distributed across multiple hosts, requiring additional time and computing resources for data shuffling during queries, especially for operations like distributed joins.
Innovation Solution
Implementing a dual query processing engine system where a master node generates an execution plan and offloads data partitioning and join operations to a second query processing engine that can preserve data locality, allowing for efficient execution of queries without extensive data shuffling by performing operations like distributed joins directly on the same host instance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If data is distributed across multiple hosts in a distributed database system, then data storage capacity and system scalability are improved, but query processing time and computing resources increase due to required data shuffling
Solution Approach 1:
The system performs preliminary data partitioning using a partition function before query execution. Data from multiple tables is pre-grouped and loaded onto the same host instances based on partition keys, so that when a distributed join query is executed, the required data is already co-located and no shuffling is needed during query processing.
Solution Approach 2:
The patent introduces a dual query processing engine architecture where a first query processing engine handles initial data partitioning and loading, while a second query processing engine executes the actual join operations. This intermediary structure allows data to be pre-positioned on appropriate hosts, eliminating the need for data shuffling during the join operation.
2Adaptability or versatility
If data is distributed across multiple hosts, then system scalability is improved, but memory requirements and computing resources for distributed join operations increase
Solution Approach 1:
Data is pre-partitioned and loaded onto host instances before query execution. By using a partition function to group related data from multiple tables onto the same hosts in advance, the system eliminates the need to load entire distributed tables into memory during join operations, significantly reducing peak memory requirements.
Solution Approach 2:
The patent segments the query processing workload between two distinct query processing engines. The first engine handles data partitioning and loading tasks, while the second engine specializes in executing join operations. This segmentation allows each engine to be optimized for its specific function and reduces the memory burden on any single component.
3Stability of the object's composition
If traditional distributed join operations are performed across multiple hosts, then data distribution is maintained, but data shuffling increases processing complexity and time
Solution Approach 1:
The system performs the complex data shuffling operation in advance during a preliminary partitioning phase, rather than during query execution. By using a partition function to pre-group data by join keys and load it onto appropriate hosts before queries are submitted, the system transforms a complex runtime operation into a simpler one-time setup task.
Solution Approach 2:
The patent creates a logical copy of the data distribution pattern through the partition function. Instead of physically moving data during each join operation, the partition function creates a virtual mapping that directs queries to the appropriate host instances that already contain the required data partitions, eliminating the need for actual data shuffling during query processing.
Data Source
AI summary
In one respect, there is provided a distributed database system that includes hosts configured to store and/or manage data, a first query processing engine comprising a master node and an executor node, and a second query processing engine. The master node can be implemented on programmable processors and configured to perform operations. The operations can include: offloading, to the second query processing engine, a creation of a first and a second table such that the first and the second table are partitioned by the second query processing engine; generating an execution plan for a query on data in the distributed database system, the execution plan including a distributed join operation that combines data from the first and second table; determining to push down, to the second query processing engine, the distributed join operation; and dispatching, to the executor node, a portion of the execution plan including the distributed join operation.