Coloring away communication in parallel query optimization
Waqar Hasan1995, Proc. 21st Int. Conf. Very Large Data Bases, Zurich
Sign up for access to the world's latest research
checkGet notified about relevant papers
checkSave papers to use in your research
checkJoin the discussion with peers
checkTrack your impact
Abstract
We address the problem of finding parallel plans for SQL queries using the two-phase approach of join ordering and query rewrite (JOQR) followed by parallelization. We focus on the JOQR phase and develop optimization algorithms that account for communication as well as ...
Related papers
Optimizing Multi-Join Queries in Parallel Relational Databases
2000
Query optimization for parallel machines needs to consider machine architecture, processor and memory resources available, and di erent types of parallelism, making the search space much larger than the sequential case. In this paper our aim is to determine a plan that makes the execution of an individual query very fast, making minimizing parallel execution time the right objective. This creates the following circular dependence: a plan tree is needed for e ective resource assignment, which is needed to estimate the parallel execution time, and this is needed for the cost-based search for a good plan tree. In this paper we propose a new search heuristic that breaks the cycle by constructing the plan tree layer by layer in a bottom-up manner. To select nodes at the next level, the lower and upper bounds on the execution time for plans consistent with the decisions made so far are estimated and are used to guide the search. A query plan representation for intra-and inter-operator parallelism, pipelining, and processor and memory assignment is proposed. Also proposed is a new approach to estimating the parallel execution time of a plan that considers sum and max of operators working sequentially and in parallel, respectively. The results obtained from a prototype optimizer are presented. 3 1 4 J J J J
A Tree-Decomposition Approach to Parallel Query Optimization
1993
In this paper we present an approach for transforming a relational join tree into a detailed execution plan with resource allocation information, for execution on a parallel machine. Our approach starts by transforming a query tree, such as might be generated by a sequential optimizer, into an operator tree which is then partitioned into a forest of linear chains of pipelined operators. We present an algorithm for scheduling these chains in conformance to their precedence ordering on a parallel machine. The aim of the scheduling is to achieve some objective like minimum response time or maximum speedup, etc. We provide a set of experiments to demonstrate the quality of the allocation plans generated by the method. A major benefit of the method is that it uses inter-operator, intra-operator and pipelining methods of parallelizing relational query operators simultaneously. Our experiments show that the heuristic scheduling method creates schedules that are close to optimal while being very easy to compute. We also propose a new way to calculate speedup that takes into account the amount of work that is done.
Optimizing multi-joint queries in parallel relational databases
[1993] Proceedings of the Second International Conference on Parallel and Distributed Information Systems, 1993
Query optimization for parallel machines needs to consider machine architecture, processor and memory resources available, and di erent types of parallelism, making the search space much larger than the sequential case. In this paper our aim is to determine a plan that makes the execution of an individual query very fast, making minimizing parallel execution time the right objective. This creates the following circular dependence: a plan tree is needed for e ective resource assignment, which is needed to estimate the parallel execution time, and this is needed for the cost-based search for a good plan tree. In this paper we propose a new search heuristic that breaks the cycle by constructing the plan tree layer by layer in a bottom-up manner. To select nodes at the next level, the lower and upper bounds on the execution time for plans consistent with the decisions made so far are estimated and are used to guide the search. A query plan representation for intra-and inter-operator parallelism, pipelining, and processor and memory assignment is proposed. Also proposed is a new approach to estimating the parallel execution time of a plan that considers sum and max of operators working sequentially and in parallel, respectively. The results obtained from a prototype optimizer are presented. 3 1 4 J J J J
Dependency-aware reordering for parallelizing query optimization in multi-core CPUs
2009
The state of the art commercial query optimizers employ cost-based optimization and exploit dynamic programming (DP) to find the optimal query execution plan (QEP) without evaluating redundant sub-plans. The number of alternative QEPs enumerated by the DP query optimizer can increase exponentially, as the number of joins in the query increases. Recently, by exploiting the coming wave of multi-core processor architectures, a state of the art parallel optimization algorithm , referred to as PDPsva, has been proposed to parallelize the "time-consuming" DP query optimization process itself. While PDPsva significantly extends the practical use of DP to queries having up to 20-25 tables, it has several limitations: 1) supporting only the size-driven DP enumerator, 2) statically allocating search space, and 3) not fully exploiting parallelism. In this paper, we propose the first generic solution for parallelizing any type of bottom-up optimizer, including the graph-traversal driven type, and for supporting dynamic search allocation and full parallelism. This is a challenging problem, since recently developed, state of art DP optimizers such as DPcpp [21] and DP hyp are very difficult to parallelize due to tangled dependencies in the join pairs they generate. Unless the solution is very carefully devised, a lot of synchronization conflicts are bound to occur. By viewing a serial bottom-up optimizer as one which generates a totally ordered sequence of join pairs in a streaming fashion, we propose a novel concept of dependency-aware reordering, which minimizes waiting time caused by dependencies of join pairs. To maximize parallelism, we also introduce a series of novel performance optimization techniques: 1) pipelining of join pair generation and plan generation; 2) the synchronization-free global MEMO; and 3) threading across dependencies. Through extensive experiments with various query topologies, we show that our solution supports any type of bottom up optimization, achieving linear speedup for each type. Despite the fact that our solution is generic, due to sophisticated optimization techniques, our generic parallel optimizer outperforms PDPsva tailored to size-driven enumeration. Experimental results also show that our solution is much more robust than PDPsva with respect to search space allocation.
Parallelizing query optimization
Proceedings of The Vldb Endowment, 2008
Many commercial RDBMSs employ cost-based query optimization exploiting dynamic programming (DP) to efficiently generate the optimal query execution plan. However, optimization time increases rapidly for queries joining more than 10 tables. Randomized or heuristic search algorithms reduce query optimization time for large join queries by considering fewer plans, sacrificing plan optimality. Though commercial systems executing query plans in parallel have existed for over a decade, the optimization of such plans still occurs serially. While modern microprocessors employ multiple cores to accelerate computations, parallelizing query optimization to exploit multi-core parallelism is not as straightforward as it may seem. The DP used in join enumeration belongs to the challenging nonserial polyadic DP class because of its non-uniform data dependencies. In this paper, we propose a comprehensive and practical solution for parallelizing query optimization in the multi-core processor architecture, including a parallel join enumeration algorithm and several alternative ways to allocate work to threads to balance their load. We also introduce a novel data structure called skip vector array to significantly reduce the generation of join partitions that are infeasible. This solution has been prototyped in PostgreSQL. Extensive experiments using various query graph topologies confirm that our algorithms allocate the work evenly, thereby achieving almost linear speed-up. Our parallel join enumeration algorithm enhanced with our skip vector array outperforms the conventional generate-and-filter DP algorithm by up to two orders of magnitude for star queries-linear speedup due to parallelism and an order of magnitude performance improvement due to the skip vector array.
Tradeoffs in Parallel Query Processing and its Implications for Query Optimization
2007
Selecting the best plan for executing a given query is the problem of query optimization. The focus of query optimization for sequential machines has been on nding query plans which involve the least amount of work, since response time is equivalent to work done in a uniprocessor environment. With the advent of parallel computers and their application to data management, this is no longer true. It may not be the case that the best sequential plan will result in the best parallel plan, since sequential dependencies in certain plans make them inherently less parallelizable. It is thus possible to reduce the response time of a query by selecting a plan which may do more work but is also more parallelizable. I/O has traditionally been a bottleneck for query processing, and the di erence in I/O and CPU speeds is increasing with modern technology. Since join processing is very CPU intensive, maximizing overlap between I/O, and CPU resource utlization is desirable. Researchers have looked at Asynchronous I/O as a tool to achieve this e ect. In this paper we study the problem of parallel join execution on a shared nothing architecture with support for asynchronous I/O, and asynchronous message passing. We examine the tradeo s among di erent query execution models and propose a viable alternative. An analytical cost model for the proposed query execution model is developed. Tradeo s among di erent query plan structures are explored, and query domains where one structure does better than the other are categorized. Based on this we propose a heuristic approach which tries to achieve the best of all worlds, and gives a stable, and good average performance across most domains.
Industrial-strength parallel query optimization: Issues and lessons
Information Systems, 1994
In the industrial context of the EDS project, we have designed and implemented a query optimizer which we have integrated within a parallel database system. The optimizer takes as input a query expressed in ESQL, an extension of SQL with objects and rules, and produces a minimum cost parallel execution plan. Our research agenda has focused on several di cult problems: support of ESQL's advanced features such as path expressions and recursion, modelling of parallel execution spaces and extensibility of the search strategy. In this paper, we give a retrospective on the optimizer project with emphasis on our design goals, research contributions and implementation decisions. We also describe the current optimizer prototype and report on experiments performed with a pilot application. Finally, we present the lessons learned.
Open issues in parallel query optimization
ACM SIGMOD Record, 1996
Parallel database systems combine data management and parallel processing techniques to provide high-performance, high-availability and scalability for data-intensive applications [10, 35]. By exploiting parallel computers, they provide performance at a cheaper price ...
Optimization Algorithms for Exploiting the Parallelism-Communication Tradeoff in Pipelined Parallelism
1994
We address the problem of finding parallel plans for SQL queries using the two-phase approach of join ordering followed by parallelization. We focus on the parallelization phase and develop algorithms for exploiting pipelined parallelism. We formulate parallelization as scheduling a weighted operator tree to minimize response time. Our model of response time captures the fundamental tradeoff between parallel execution and its communication overhead. We assess the quality of an optimization algorithm by its performance ratio which is the ratio of the response time of the generated schedule to that of the optimal. We develop fast algorithms that produce near-optimal schedules -- the performance ratio is extremely close to 1 on the average and has a worst case bound of about 2 for many cases.
Invited Project Review: Industrial-strength parallel query optimization: issues and lessons
1994
In the industrial context of the EDS project, we have designed and implemented a query optimizer which we have integrated within a parallel database system. The optimizer takes as input a query expressed in ESQL, an extension of SQL with objects and rules, and produces a minimum cost parallel execution plan. Our research agenda has focused on several difficult problems: support of ESQL's advanced features such as path expressions and recursion, modelling of parallel execution spaces and extensibility of the search strategy. In this paper, we give a retrospective on the optimizer project with emphasis on our design goals, research contributions and implementation decisions. We also describe the current optimizer prototype and report on experiments performed with a pilot application. Finally, we present the lessons learned.
Worst-Case Optimal Algorithms for Parallel Query Processing
2016
In this paper, we study the communication complexity for the problem of computing a conjunctive query on a large database in a parallel setting with $p$ servers. In contrast to previous work, where upper and lower bounds on the communication were specified for particular structures of data (either data without skew, or data with specific types of skew), in this work we focus on worst-case analysis of the communication cost. The goal is to find worst-case optimal parallel algorithms, similar to the work of [18] for sequential algorithms. We first show that for a single round we can obtain an optimal worst-case algorithm. The optimal load for a conjunctive query $q$ when all relations have size equal to $M$ is $O(M/p^{1/\psi^*})$, where $\psi^*$ is a new query-related quantity called the edge quasi-packing number, which is different from both the edge packing number and edge cover number of the query hypergraph. For multiple rounds, we present algorithms that are optimal for several c...
Optimization Algorithms for Pipelined Parallelism
Intl. Conf. VLDB, 1994
We address the problem of parallel query optimization, which is to find optimal parallel plans for executing SQL queries. Following Hong and Stonebraker [HS91], we break the optimization problem into two phases: join ordering followed by parallelization. We focus ...
Optimal parallel scheduling of M-way join queries
Information Systems, 1991
The problem of computing multirelation (M-way) join queries on uniprocessor architectures has been considered by many researchers in the past. This paper lays the necessary foundation for work involving optimization of M-way joins in parallel architectures. We explain the inadequacies of previous uniprocessor strategies and describe a more suitable formulation based on the concept of matching in graph theory to approach the problem in a parallel environment. It has been shown that the problem of optimizing M-way joins is an NP-hard problem and hence we would expect that in a parallel processing environment the search space of possible solutions (join schedules) would be enormous, especially when a variable number of processors are considered. Our strategy seeks to reduce the region to search by partitioning the search space according to the number of available processors. Based on this a significant portion of the search space, which will produce non-optimal join schedules, may be ignored.
Query Optimization for Distributed Database Systems
I would like to thank my supervisor Dr Dan Olteanu for his incredible level of enthusiasm and encouragement throughout the project. I am also very grateful for the continuous level of feedback and organisation as well as the amount of time he has devoted to answering my queries. I feel that I now approach complex and unknown problems with enthusiasm instead of apprehension as I used to. I couldn't have had a better supervisor.
Using parallelism and pipeline for the optimisation of join queries
Lecture Notes in Computer Science, 1992
In this study we present a technique for the parallel optimisation of join queries, that uses the offered coarse-grain parallelism of the underlying architecture in order to reduce the CPU-bound optimisation overhead. The optimisation technique performs an almost exhaustive search of the solution space for small join queries and gradually, as the number of joins increases, it diverges towards iterative improvement. This technique has been developed on a low-parallelism transputer-based architecture, where its behaviour is studied for the optimisation of queries with many tenths of joins.
From the Modeling of Parallel Relational Query Processing to Query Optimization and Simulation
Parallel Processing Letters, 1998
This paper presents a novel theoretical model for representing parallel relational query processing. It is based on a hierarchical approach. First, a scheme graph, called DPL graph, describes all possible execution dependencies between operators, including communication and run-time control mechanisms. Second, a high-level Petri net is used for modeling the data-and control ow. Our model provides the framework for building and studying parallel database tools and applications. Thus, we implemented a parallel query optimizer based on DPL graphs, which is able to access sub-search spaces not yet considered. Furthermore, based on this model, a simulation environment has been designed and implemented for testing run-time control strategies as well as query optimization methods.
A PERF solution for distributed query optimization
Query optimization techniques aim to minimize the cost of transferring data across networks. Many techniques and algorithms have been proposed to optimize queries. One of the algorithms is the W algorithm using semi-joins. Nowadays, a new technique called PERF seems to bring some improvement over semi-joins . PERF joins are two-way semijoins using a bit vector as their backward phase. Our research encompasses applying PERF joins to the W algorithm. Programs were designed to implement both the original and the enhanced algorithms. Several experiments were conducted and the results showed a very considerable enhancement obtained by applying the PERF concept.
Optimal distributed execution of join queries
Computers & mathematics with applications, 1994
It is proposed that the execution of a chain query in a distributed system can be usefully and appropriately modeled as an integer linear program. In response to a user request, information in the form of relational tables scattered across the network is to be combined and made available to the user. The formulation initially attained by considering the behavior of the distributed system in processing such a query is then reduced by removing redundant linear constraints, to produce a model of minimal transmission cost execution. In view of varying properties displayed by the possibly many optima of this problem, further attention is devoted to discriminating between them. By perturbing the objective function, those solutions requiring fewer network transmissions can be favored at the expense of equal-cost, but more complicated, strategies. This includes those strategies that may specify the transmission of a relation around a cycle; when the costs of transmission between sites forming the cycle are zero, such a solution might otherwise be optimal. Many different ways have been devised to solve programs having some number of variables restricted to taking only integer values in some interval, and virtually any of these might be used to solve the join query model. One possible method, using a tree-search approach, is discussed here.
Parallel optimization of large join queries with set operators and aggregates in a parallel environment supporting pipeline
IEEE Transactions on Knowledge and Data Engineering, 1996
We propose a parallel optimizer for queries containing a large number of joins, as well as set operators and aggregate functions. The platform of execution is a shared-disk multiprocessor machine supporting bushy parallelism and pipeline. Our model partitions the query into almost independent subtrees that can be optimized simultaneously and applies an enhanced variation of the iterative improvement technique on those of the subtrees, which contain a large number of joins. This technique is parallelized, too. In order to estimate the cost of the states constructed during optimization of join subtrees, cost formulae are developed that estimate the cost of relational algebra operators when executed across coalescing pipes.
Related topics
Loading Preview
Sorry, preview is currently unavailable. You can download the paper by clicking the button above.