Worst-Case Optimal Algorithms for Parallel Query Processing
Dan Suciu2016
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
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...
Related papers
A Worst-Case Optimal Multi-Round Algorithm for Parallel Computation of Conjunctive Queries
Proceedings of the 36th ACM SIGMOD-SIGACT-SIGAI Symposium on Principles of Database Systems - PODS '17, 2017
We study the optimal communication cost for computing a full conjunctive query Q over p distributed servers. Two prior results were known. First, for one-round algorithms over skew-free data the optimal communication cost per server is m/p 1/τ * (Q) , where m is the size of the largest input relation, and τ * is the fractional vertex covering number of the query hypergraph. Second, for multi-round algorithms and unrestricted database instances, it was shown that any algorithm requires at least m/p 1/ρ * (Q) communication cost per server, where ρ * (Q) is the fractional edge covering number of the query hypergraph; but no matching algorithms were known for this case (except for two restricted queries: chains and cycles). In this paper we describe a multi-round algorithm that computes any query with load m/p 1/ρ * (Q) per server, in the case when all input relations are binary. Thus, we prove this to be the optimal load for all queries over binary input relations. Our algorithm represents a non-trivial extension of previous algorithms for chains and cycles, and exploits some unique properties of graphs, which no longer hold for hyper-graphs.
Skew in parallel query processing
Proceedings of the 33rd ACM SIGMOD-SIGACT-SIGART symposium on Principles of database systems - PODS '14, 2014
We study the problem of computing a conjunctive query q in parallel, using p of servers, on a large database. We consider algorithms with one round of communication, and study the complexity of the communication. We are especially interested in the case where the data is skewed, which is a major challenge for scalable parallel query processing. We establish a tight connection between the fractional edge packings of the query and the amount of communication, in two cases. First, in the case when the only statistics on the database are the cardinalities of the input relations, and the data is skew-free, we provide matching upper and lower bounds (up to a poly log p factor) expressed in terms of fractional edge packings of the query q. Second, in the case when the relations are skewed and the heavy hitters and their frequencies are known, we provide upper and lower bounds (up to a poly log p factor) expressed in terms of packings of residual queries obtained by specializing the query to a heavy hitter. All our lower bounds are expressed in the strongest form, as number of bits needed to be communicated between processors with unlimited computational power. Our results generalizes some prior results on uniform databases (where each relation is a matching) , and other lower bounds for the MapReduce model .
Communication steps for parallel query processing
Proceedings of the 32nd symposium on Principles of database systems - PODS '13, 2013
We consider the problem of computing a relational query q on a large input database of size n, using a large number p of servers. The computation is performed in rounds, and each server can receive only O(n/p 1−ε ) bits of data, where ε ∈ [0, 1] is a parameter that controls replication. We examine how many global communication steps are needed to compute q. We establish both lower and upper bounds, in two settings. For a single round of communication, we give lower bounds in the strongest possible model, where arbitrary bits may be exchanged; we show that any algorithm requires ε ≥ 1 − 1/τ * , where τ * is the fractional vertex cover of the hypergraph of q. We also give an algorithm that matches the lower bound for a specific class of databases. For multiple rounds of communication, we present lower bounds in a model where routing decisions for a tuple are tuple-based. We show that for the class of tree-like queries there exists a tradeoff between the number of rounds and the space exponent ε. The lower bounds for multiple rounds are the first of their kind. Our results also imply that transitive closure cannot be computed in O(1) rounds of communication.
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 ...
The complexity of acyclic conjunctive queries
Journal of the ACM, 2001
This paper deals with the evaluation of acyclic Boolean conjunctive queries in relational databases. By well-known results of Yannakakis [1981], this problem is solvable in polynomial time; its precise complexity, however, has not been pinpointed so far. We show that the problem of evaluating acyclic Boolean conjunctive queries is complete for LOGCFL, the class of decision problems that are logspace-reducible to a context-free language. Since LOGCFL is contained in AC 1 and NC 2 , the evaluation problem of acyclic Boolean conjunctive queries is highly parallelizable. We present a parallel database algorithm solving this problem with a logarithmic number of parallel join operations. The algorithm is generalized to computing the output of relevant classes of non-Boolean queries. We also show that the acyclic versions of the following well-known database and AI problems are all LOGCFL-complete: The Query Output Tuple problem for conjunctive queries, Conjunctive Query Containment, Clause Subsumption, and Constraint Satisfaction. The LOGCFL-completeness result is extended to the class of queries of bounded treewidth and to other relevant query classes which are more general than the acyclic queries.
Editors: Wim Martens and Thomas Zeume
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 [17] 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/ψ *), where ψ * 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 classes of queries....
Deterministic Load Balancing for Parallel Joins (Extended Abstract)
2016
We study the problem of distributing the tuples of a relation to a number of processors organized in an r-dimensional hypercube, which is an important task for parallel join processing. In contrast to previous work, which proposed randomized algorithms for the task, we ask here the question of how to construct efficient deterministic distribution strategies that can optimally load balance the input relation. We first present some general lower bounds on the load for any dimension; these bounds depend not only on the size of the relation, but also on the maximum frequency of each value in the relation. We then construct an algorithm for the case of 1 dimension that is optimal within a constant factor, and an algorithm for the case of 2 dimensions that is optimal within a polylogarithmic factor. Our 2-dimensional algorithm is based on an interesting connection with the vector load balancing problem, a well-studied problem that generalizes classic load balancing.
Improving the robustness and performance of parallel joins over distributed systems
Journal of Parallel and Distributed Computing, 2017
High-performance data processing systems typically utilize numerous servers with large amounts of memory. An essential operation in such environment is the parallel join, the performance of which is critical for data intensive operations. In many real-world workloads, data skew is omnipresent. Techniques that do not cater for the possibility of data skew often suffer from performance failures and memory problems. State-of-the-art methods designed to handle data skew propose new ways to distribute computation that avoid hotspots. However, this comes at the expense of global collection of statistics, redundant computation, duplication of data or increased network communication. In this light, performance could be further improved by removing the dependency on global skew knowledge and broadcasting. In this paper, we propose a new method called PRPQ (partial redistribution & partial query), with targets for efficient and robust joins with large datasets over high performance clusters. We present the detailed implementation of our approach and compare its performance with current implementations. The experimental results demonstrate that the proposed algorithm is scalable and robust and can also outperform the state-of-the-art approach with less network communication, figures that confirm our theoretical analysis.
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.
Distributed processing of conjunctive queries
2005
We distinguish that Web query processing is composed of two phases: (a) retrieving information on documents related to the queries and ranking them, and (b) generating snippets, title, and URL information for the answer page. Using real data and a small cluster of index servers, we study four basic and key issues related to this first phase of query processing: load balance, broker behavior, performance by individual index servers, and overall throughput. Our study reveals interesting tradeoffs: (1) that load unbalance at low query arrival rates can be controlled with a simple measure of randomizing the distribution of documents among the index servers, (2) that the broker is not a bottleneck, (3) that disk and CPU utilization at individual servers depends on the relationship between memory size and the distribution of frequencies for the query terms, and (4) that load unbalance at high loads prevents higher throughput.
Related topics
Related papers
Coloring away communication in parallel query optimization
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 ...
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.
Skew-insensitive parallel algorithms for relational join
Proceedings. Fifth International Conference on High Performance Computing (Cat. No. 98EX238), 1998
Join is the most important and expensive operation in relational databases. The parallel join operation is very sensitive to the presence of the data skew. In this paper, we present two new parallel join algorithms for coarse grained machines which work optimally in presence of arbitrary amount of data skew. The rst algorithm is sort-based and the second is hash-based. Both of these algorithms employ a preprocessing phase (prior to the redistribution phase) to equally partition the work among the processors. The proposed algorithms have been designed for memory resident-data. However, they can be extended to disk resident-data. These algorithms are shown to be theoretically as well as practically scalable. Experimental results are provided on the IBM SP-2.
A Near-Optimal Parallel Algorithm for Joining Binary Relations
2020
We present a constant-round algorithm in the massively parallel computation (MPC) model for evaluating a natural join where every input relation has two attributes. Our algorithm achieves a load of $\tilde{O}(m/p^{1/\rho})$ where $m$ is the total size of the input relations, $p$ is the number of machines, $\rho$ is the join's fractional edge covering number, and $\tilde{O}(.)$ hides a polylogarithmic factor. The load matches a known lower bound up to a polylogarithmic factor. At the core of the proposed algorithm is a new theorem (which we name {\em the isolated cartesian product theorem}) that provides fresh insight into the problem's mathematical structure. Our result implies that the {\em subgraph enumeration problem}, where the goal is to report all the occurrences of a constant-sized subgraph pattern, can be settled optimally (up to a polylogarithmic factor) in the MPC model.
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
Fast parallel algorithms on a class of graph structures with applications in relational databases and computer networks
The quest for efficient parallel algorithms for graph related problems necessitates not only fast computational schemes but also requires insights into their inherent structures that lend themselves to elegant problem solving methods. Towards this objective efficient parallel algorithms on a class of hypergraphs called acyclic hyper graphs and directed hypergraphs are developed in this thesis. Acyclic hypergraphs are precisely chordal graphs and its subclasses, and they have applications in rela tional databases and computer networks. In this thesis, firstly, we present efficient parallel algorithms for the following problems on graphs. determining whether a graph is strongly chordal, ptolemaic, or a block graph. If the graph is strongly chordal, determine the strongly perfect vertex elimination ordering. determining the minimal set of edges needed to make an arbitrary graph strongly chordal, ptolemaic, or a block graph. determining the minimum cardinality dominating set, connected dominating set, total dominating set, and the domatic number of a strongly chordal graph. Secondly, we show that the query implication problem (Q 1-> <2 2) on two queries, which is to determine whether the data retrieved by query Q x is always a sub set of the data retrieved by query Q 2, is not even in NP and in fact complete in U2P. We present several 'fine-grain' analysis of the query implication problem and show that the query implication can be solved in polynomial time given chordal queries. Thirdly, we develop efficient parallel algorithms for manipulating directed hypergraphs H such as finding a directed path in H , closure of H , and minimum equivalent hypergraph of H. We show that finding a directed path in a directed hypergraph is inherently sequential. For directed hypergraphs with fixed degree and diameter we present NC algorithms for manipulations. Directed hypergraphs are representation schemes for functional dependencies in relational databases. Finally, we also present an efficient parallel algorithm for multi-dimensional range search. We show that a set of points in a rectangular parallelepiped can be obtained in O (logn) time with only 2.1og2«-10.logn + 14 processors on a EREW-PRAM. A non-trivial implementation technique on the hypercube parallel architec ture is also presented. Our method can be easily generalized to the case of ddimensional range search.
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.
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-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
Time Complexity and Parallel Speedup of Relational Queries to Solve Graph Problems
Lecture Notes in Computer Science, 2018
Nowadays parallel DBMSs compete with graph Hadoop Big Data systems to analyze large graphs. In this paper, we study the processing and optimization of relational queries to solve fundamental graph problems, giving a theoretical foundation on time complexity and parallel processing. Specifically, we consider reachability, shortest paths from a single vertex, weakly connected components, PageRank, transitive closure and all pairs shortest paths. We explain how graphs can be stored on a relational database and then we show relational queries can be used to efficiently analyze such graphs. We identify two complementary families of algorithms: iteration of matrix-vector multiplication and iteration of matrix-matrix multiplication. We show all problems can be solved with a unified algorithm with an iteration of matrix multiplications. We present intuitive theory results on cardinality estimation and time complexity considering graph size, shape and density. Finally, we characterize parallel computational complexity and speedup per iteration, focusing on joins and aggregations.
Loading Preview
Sorry, preview is currently unavailable. You can download the paper by clicking the button above.