Managing the operator ordering problem in parallel 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.

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.

1994

This dissertation addressed the performance of database operations on parallel systems, emphasizing factors which limit scalability of such applications. Even though algorithms were proposed and discussed in the context of the relational framework, the work developed here is relevant for the performance of systems supporting any other data model, such as object-oriented databases. The join operation is representative of the family of binary matching operators, which include set operators that must be supported by any system regardless of the underlying data model [42]. The techniques developed in this dissertation are not restricted to the join operation and can be applied for other binary matching operators as well. This is also true of sorting, which is used in many other nonnumerical applications besides database processing. Both architectural aspects and the design of algorithms were considered in this work. These two topics cannot be divorced in any work addressing parallel performance, since only with good knowledge of the capabilities of a parallel system can an algorithm be optimally designed to achieve the best capabilities of the system.

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.

1999

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.

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.

Sigmod, 1994

The decreasing cost of computing makes

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

International Journal of Geomate

The widespread growth of data has created many problems for businesses, such as delay requests; in this paper, we propose several methods of partitioning an index B*Tree in multi-processor machines in parallel/distributed database systems and collaboration between processors when executing multi-queries. When optimizing, indexing automatically comes to mind; we distinguish two types of indexing: B*Tree and Bitmap. Since the advent of multicore computers (multi processors) parallelism becomes an indispensable part of optimization. Our work will focus on partitioning each table on three parts following indexing key partitioning; each processor will host a partition of the index, and the first processor that will finish will immediately take another partition of the index pending according to the priority. The parallelism will reduce the CPU cost then reduces execution time; collaboration between processors will further reduce these costs.