Optimizing multi-joint queries in parallel relational databases

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.

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.

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 ...

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.

Sigmod, 1994

The decreasing cost of computing makes

2005

A "plan diagram" is a pictorial enumeration of the execution plan choices of a database query optimizer over the relational selectivity space. In this paper, we present and analyze representative plan diagrams on a suite of popular commercial query optimizers for queries based on the TPC-H benchmark. These diagrams, which often appear similar to cubist paintings, provide a variety of interesting insights, including that current optimizers make extremely fine-grained plan choices, which may often be supplanted by less efficient options without substantively affecting the quality; that the plan optimality regions may have highly intricate patterns and irregular boundaries, indicating strongly non-linear cost models; that nonmonotonic cost behavior exists where increasing result cardinalities decrease the estimated cost; and, that the basic assumptions underlying the research literature on parametric query optimization often do not hold in practice.

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.

In this paper, we discuss the most powerful techniques of tuning parallel/distributed databases. As in engineering, database tuning becomes an inescapable part of big projects since the conception phase of research projects. The needs of companies including big data have increased to databases optimization. Systems that not take into account the optimization rules become heavy after five years of their production; these reasons were of a paramount of importance to prepare this paper. Indexing is the most suitable way to optimize database systems, further one of the top ways of optimizing index is the application of parallelization. In this paper, we will discuss parallelization and we will practice it with different complex queries and sub-queries using different types of indexes; then we will compare the results gotten from each index. To top it all, the most suitable interference between the major types of index: B*Tree index, Bitmap index, composite parallel index, local parallel index and global parallel index.

Proc. 21st Int. Conf. Very Large Data Bases, Zurich, 1995

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 ...

Proceedings of the 19th Int. Conf. on Very Large Data Bases (VLDB), 1993

The cost of query optimization is affected by both the search space and the search strategy of the optimizer. In a parallel execution environment, the search space tends to be much larger than in the centralized case. This is due to the high number of execution alternatives which implies a significant increase in the optimization cost. In this paper, we investigate the trade-off between optimization cost and parallel execution cost using the DBS3 parallel query optimizer. We describe its cost model which captures all essential aspects of parallel executions. We show how the cost metrics imply a significant increase in the search space and optimization cost. However, instead of restricting the search space, which may lead to loosing better plans, we reduce the optimization cost bycontrolling the search strategy. We extend randomized strategies to adapt well to parallel query optimization. In particular, we propose Toured Simulation Annealing which provides a better trade-off between optimization cost and quality of the parallel execution plan.

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...

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.

1996

In some key database applications, a sequence of interdependent queries may be posed simultaneously to the DBMS. The optimization of such sequences is called multi-query optimization, and it attempts to exploit these dependencies in the derivation of a query evaluation plan (qep).

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.

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.