In addition to indexes and materialized views, horizontal and vertical partitioning are important aspects of physical design in a relational database system that significantly impact performance. Horizontal partitioning also provides manageability; database administrators often require indexes and their underlying tables partitioned identically so as to make common operations such as backup/restore easier. While partitioning is important, incorporating partitioning makes the problem of automating physical design much harder since: (a) The choices of partitioning can strongly interact with choices of indexes and materialized views. (b) A large new space of physical design alternatives must be considered. (c) Manageability requirements impose a new constraint on the problem. In this paper, we present novel techniques for designing a scalable solution to this integrated physical design problem that takes both performance and manageability into account. We have implemented our techniques and evaluated it on Microsoft SQL Server. Our experiments highlight: (a) the importance of taking an integrated approach to automated physical design and (b) the scalability of our techniques.

/pdf/integrating-vertical-and-horizontal-partitioning-into-k70utovdpk.pdf

Integrating vertical and horizontal partitioning into automated physical database design

We consider the problem of horizontally partitioning a dynamic relation across a large number of disks/nodes by the use of range partitioning. Such partitioning is often desirable in large-scale parallel databases, as well as in peer-to-peer (P2P) systems. As tuples are inserted and deleted, the partitions may need to be adjusted, and data moved, in order to achieve storage balance across the participant disks/nodes. We propose efficient, asymptotically optimal algorithms that ensure storage balance at all times, even against an adversarial insertion and deletion of tuples. We combine the above algorithms with distributed routing structures to architect a P2P system that supports efficient range queries, while simultaneously guaranteeing storage balance.

/pdf/online-balancing-of-range-partitioned-data-with-applications-1k4wh6a792.pdf

Online balancing of range-partitioned data with applications to peer-to-peer systems

Physical database design is important for query performance in a shared-nothing parallel database system, in which data is horizontally partitioned among multiple independent nodes. We seek to automate the process of data partitioning. Given a workload of SQL statements, we seek to determine automatically how to partition the base data across multiple nodes to achieve overall optimal (or close to optimal) performance for that workload. Previous attempts use heuristic rules to make those decisions. These approaches fail to consider all of the interdependent aspects of query performance typically modeled by today's sophisticated query optimizers.We present a comprehensive solution to the problem that has been tightly integrated with the optimizer of a commercial shared-nothing parallel database system. Our approach uses the query optimizer itself both to recommend candidate partitions for each table that will benefit each query in the workload, and to evaluate various combinations of these candidates. We compare a rank-based enumeration method with a random-based one. Our experimental results show that the former is more effective.

/pdf/automating-physical-database-design-in-a-parallel-database-mywg847ygx.pdf

Automating physical database design in a parallel database

Disclosed is a method, system, and program for query processing. Metadata for a facts metadata object and one or more dimension metadata objects that are associated with the facts metadata object is stored. A view with columns for one or more measures in the facts metadata object and one or more attributes in the one or more dimension metadata objects is constructed. Additional metadata that describes roles of columns in the fact and dimension metadata objects is generated. Also disclosed is a computer-readable medium for storing data for access by a program. A data structure stored in the computer-readable medium includes data for use by the program. The data includes a cube model metadata object that includes a facts metadata object, one or more dimension metadata objects, and one or more join metadata objects that describe how one or more tables in the facts metadata object and one or more tables in the one or more dimension metadata objects are joined. The data also includes a cube metadata object that represents a subset of the cube model metadata object and comprises a view with columns for one or more measures of one of the facts metadata objects and one or more attributes of one or more of the dimension metadata objects and a document that describes roles of columns in the facts metadata object and the one or more dimension metadata objects.

Method, system, and program for use of metadata to create multidimensional cubes in a relational database

In most enterprises, databases are deployed on dedicated database servers. Often, these servers are underutilized much of the time. For example, in traces from almost 200 production servers from different organizations, we see an average CPU utilization of less than 4%. This unused capacity can be potentially harnessed to consolidate multiple databases on fewer machines, reducing hardware and operational costs. Virtual machine (VM) technology is one popular way to approach this problem. However, as we demonstrate in this paper, VMs fail to adequately support database consolidation, because databases place a unique and challenging set of demands on hardware resources, which are not well-suited to the assumptions made by VM-based consolidation.Instead, our system for database consolidation, named Kairos, uses novel techniques to measure the hardware requirements of database workloads, as well as models to predict the combined resource utilization of those workloads. We formalize the consolidation problem as a non-linear optimization program, aiming to minimize the number of servers and balance load, while achieving near-zero performance degradation. We compare Kairos against virtual machines, showing up to a factor of 12× higher throughput on a TPC-C-like benchmark. We also tested the effectiveness of our approach on real-world data collected from production servers at Wikia.com, Wikipedia, Second Life, and MIT CSAIL, showing absolute consolidation ratios ranging between 5.5:1 and 17:1.

/pdf/workload-aware-database-monitoring-and-consolidation-2kf628vmvl.pdf

Workload-aware database monitoring and consolidation

Data allocation is a key performance factor for parallel database systems (PDBS). This holds especially for data warehousing environments where huge amounts of data and complex analytical queries have to be dealt with. While there are several studies on data allocation for relational PDBS, the specific requirements of data warehouses have not yet been sufficiently addressed. In this study, we consider the allocation of relational data warehouses based on a star schema and utilizing bitmap index structures. We investigate how a multi-dimensional hierarchical data fragmentation of the fact table supports queries referencing different subsets of the schema dimensions. Our analysis is based on realistic parameters derived from a decision support benchmark. The performance implications of different allocation choices are evaluated by means of a detailed simulation model.

/pdf/multi-dimensional-database-allocation-for-parallel-data-1d56uffpfg.pdf

Multi-Dimensional Database Allocation for Parallel Data Warehouses

Due to the increasing complexity of data warehouses , a centralized and declarative management of metadata is essential for data warehouse administration, maintenance and usage. Metadata are usually divided into technical and semantic metadata. Typically, current approaches only support subsets of these metadata types, such as data movement meta-data or multidimensional metadata for OLAP. In particular, the interdependencies between technical and semantic metadata have not yet been investigated sufficiently. The representation of these interdependencies form an important prerequisite for the translation of queries formulated at the business concept level to executable queries on physical data. Therefore, we suggest a uniform and integrative model for data warehouse metadata. This model uses a uniform representation approach based on the Uniform Modeling Language (UML) to integrate technical and semantic metadata and their interdependencies.

/pdf/an-integrative-and-uniform-model-for-metadata-management-in-334f8j7xjh.pdf

An Integrative and Uniform Model for Metadata Management in Data Warehousing Environments.

We present the WARLOCK tool to automatically determine a parallel data warehouse’s allocation to disk. This GUIequipped tool is implemented in Java and utilizes an internal cost model and heuristics to determine a disk allocation minimizing both I/O work and query response times. WARLOCK recommends a ranked list of fragmentation candidates, a detailed query performance analysis and a tailored physical allocation scheme for relational star schemas and bitmap indexes. It supports multi-dimensional fragmentations and can deal with data skew for parallel data warehouses based on a Shared Everything or Shared Disk architecture.

/pdf/warlock-a-data-allocation-tool-for-parallel-warehouses-wdqbrxyei1.pdf

WARLOCK: A Data Allocation Tool for Parallel Warehouses

Data warehouse queries pose challenging performance problems that often necessitate the use of parallel database systems (PDBS). Although dynamic load balancing is of key importance in PDBS, to our knowledge it has not yet been investigated thoroughly for parallel data warehouses. In this study, we propose a scheduling strategy that simultaneously considers both processors and disks while utilizing the load balancing potential of a Shared Disk architecture. We compare the performance of this new method to several other approaches in a comprehensive simulation study, incorporating skew aspects and typical data warehouse features such as star schemas.

/pdf/dynamic-query-scheduling-in-parallel-data-warehouses-50cfdu1fzg.pdf

Dynamic Query Scheduling in Parallel Data Warehouses

Parallel processing is a key to high performance in very large data warehouse applications that execute complex analytical queries on huge amounts of data. Although parallel database systems (PDBSs) have been studied extensively in the past decades, the specifics of load balancing in parallel data warehouses have not been addressed in detail.

In this study, we investigate how the load balancing potential of a Shared Disk (SD) architecture can be utilized for data warehouse applications. We propose an integrated scheduling strategy that simultaneously considers both processors and disks, regarding not only the total workload on each resource but also the distribution of load over time. We evaluate the performance of the new method in a comprehensive simulation study and compare it to several other approaches. The analysis incorporates skew aspects and considers typical data warehouse features such as star schemas with large fact tables and bitmap indices. Copyright © 2003 John Wiley & Sons, Ltd.

/pdf/dynamic-query-scheduling-in-parallel-data-warehouses-45imc9u38o.pdf

Thomas Stöhr

Papers

Multi-Dimensional Database Allocation for Parallel Data Warehouses

An Integrative and Uniform Model for Metadata Management in Data Warehousing Environments.

WARLOCK: A Data Allocation Tool for Parallel Warehouses

Dynamic Query Scheduling in Parallel Data Warehouses

Dynamic query scheduling in parallel data warehouses