Automated partitioning design in parallel database systems

Abstract

In recent years, Massively Parallel Processors (MPPs) have gained ground enabling vast amounts of data processing. In such environments, data is partitioned across multiple compute nodes, which results in dramatic performance improvements during parallel query execution. To evaluate certain relational operators in a query correctly, data sometimes needs to be re-partitioned (i.e., moved) across compute nodes. Since data movement operations are much more expensive than relational operations, it is crucial to design a suitable data partitioning strategy that minimizes the cost of such expensive data transfers. A good partitioning strategy strongly depends on how the parallel system would be used. In this paper we present a partitioning advisor that recommends the best partitioning design for an expected workload. Our tool recommends which tables should be replicated (i.e., copied into every compute node) and which ones should be distributed according to specific column(s) so that the cost of evaluating similar workloads is minimized. In contrast to previous work, our techniques are deeply integrated with the underlying parallel query optimizer, which results in more accurate recommendations in a shorter amount of time. Our experimental evaluation using a real MPP system, Microsoft SQL Server 2008 Parallel Data Warehouse, with both real and synthetic workloads shows the effectiveness of the proposed techniques and the importance of deep integration of the partitioning advisor with the underlying query optimizer.

Skew-aware automatic database partitioning in shared-nothing, parallel OLTP systems

“…5, the search tree's levels are split into two sections corresponding to the two search phases. In the first phase, Horticulture explores the tables' candidate attributes using a branch-and-bound search [49,32]. Once all of the relaxed tables are assigned an attribute in D relax , Horticulture then performs a brute-force search in the second phase to select the stored procedures' routing parameters.…”

“…Our cost model must also calculate estimates for designs where not all of the tables and procedures have been assigned an attribute yet [32]. This allows Horticulture to determine whether an incomplete design has a greater cost than the current best design, and thus allows it to skip exploring the remainder of the search tree below its current location.…”

“…Another approach is to calculate a lower bound using a theoretical design [32] and then halt the LNS algorithm when it finds a design with a cost that is within a certain distance to that bound [21]. We compute this bound by estimating the cost of the workload using a design where all transactions execute as single-partitioned and with no skew in the cluster (i.e., round-robin assignment).…”

“…Much of this work is not applicable to OLTP systems because the multi-node coordination required to achieve transaction consistency dominates the performance gains obtained by this type of parallelism; previous work [17,24] has shown that, even after ignoring the affects of lock-contention, this overhead can be up to 50% of the total execution time of a transaction when compared to single-node execution. Although other work has focused on parallel OLTP database design [49,17,32], these approaches lack three features that are crucial for enterprise OLTP databases: (1) support for stored procedures to increase execution locality, (2) the use of replicated secondary indexes to reduce distributed transactions, and (3) handling of time-varying skew in data accesses to increase cluster load balance. These three salient aspects of enterprise databases hinder the applicability and effectiveness of the previous work.…”

Dynamic Workload-Based Partitioning Algorithms for Continuously Growing Databases

“…Automatic database partitioning for distributed databases has recently received further attention. In [14], data is partitioned automatically to optimize the execution of MPP systems. As a possible alternative they only consider hash-based partitioning over a single column.…”