Compression has historically been used to reduce the cost of storage, I/Os from that storage, and buffer pool utilization, at the expense of the CPU required to decompress data every time it is queried. However, significant additional CPU efficiencies can be achieved by deferring decompression as late in query processing as possible and performing query processing operations directly on the still-compressed data. In this paper, we investigate the benefits and challenges of performing joins on compressed (or encoded) data. We demonstrate the benefit of independently optimizing the compression scheme of each join column, even though join predicates relating values from multiple columns may require translation of the encoding of one join column into the encoding of the other. We also show the benefit of compressing "payload" data other than the join columns "on the fly," to minimize the size of hash tables used in the join. By partitioning the domain of each column and defining separate dictionaries for each partition, we can achieve even better overall compression as well as increased flexibility in dealing with new values introduced by updates. Instead of decompressing both join columns participating in a join to resolve their different compression schemes, our system performs a light-weight mapping of only qualifying rows from one of the join columns to the encoding space of the other at run time. Consequently, join predicates can be applied directly on the compressed data. We call this procedure encoding translation. Two alternatives of encoding translation are developed and compared in the paper. We provide a comprehensive evaluation of these alternatives using product implementations of each on the TPC-H data set, and demonstrate that performing joins on encoded and partitioned data achieves both superior performance and excellent compression.

Storage architecture includes more and more processing power for increasing requirement of reliability, managibility and scalability. For example, an IBM storage server is equipped with 4 or 8 state-of-the-art processors and gigabytes of memories. This trend enables analyzing data locally inside a storage server. Processing data locally is appealing under the following circumstances: (1) huge reduction of data flowing to the host, (2) reduction of CPU consumption on host. Accordingly, the benefits are (1) less data traffic through IO channel to the host, (2) better utilization of host bufferpool, and (3) enabling more workload on the host. One crucial task is to understand how DBMS can benefit from such hardware. That is to identify which database operations are beneficial to be offloaded given a query workload in a particular setting. For certain operations, we establish value proposition via various approaches and show the analytical and experimental results. In particular, starjoin queries are commonly used in business warehouses. We propose to offload a portion of a starjoin query from host to the POWER5 P processors on a storage server, which dramatically reduces the amount of channel IO and host CPU consumption. Moreover, the query elapsed time is improved via the exploitation of the state-of-the-art P processors on a storage server.

Query performance in current systems depends significantly on tuning: how well the query matches the available indexes, materialized views etc. Even in a well tuned system, there are always some queries that take much longer than others. This frustrates users who increasingly want consistent response times to ad hoc queries. We argue that query processors should instead aim for constant response times for all queries, with no assumption about tuning. We present Blink, our first attempt at this goal, that runs every query as a table scan over a fully denormalized database, with hash group-by done along the way. To make this scan efficient, Blink uses a novel compression scheme that horizontally partitions tuples by frequency, thereby compressing skewed data almost down to entropy, even while producing long runs of fixed-length, easily-parseable values. We also present a scheme for evaluating a conjunction of range and equality predicates in SIMD fashion over compressed tuples, and different schemes for efficient hash-based aggregation within the L2 cache. A experimental study with a suite of arbitrary single block SQL queries over a TPCH-like schema suggests that constant-time queries can be efficient.

Computer architectures are increasingly based on multi-core CPUs and large memories. Memory bandwidth, which has riot kept pace with the increasing number of cores, has become the primary processing bottleneck, replacing disk I/O as the limiting factor. To address this challenge, we provide novel algorithms for increasing the throughput of Business Intelligence (BI) queries, as well as for ensuring fairness and avoiding starvation among a concurrent set of such queries. To maximize throughput, we propose a novel FullSharing scheme that allows all concurrent queries, when performing base-table I/O, to share the cache belonging to a given core. We then generalize this approach to a BatchSharing scheme that avoids thrashing on "agg-tables" ---hash tables that are used for aggregation processing---caused by execution of too many queries on a core. This scheme partitions queries into batches such that the working-set of agg-table entries for each batch can fit into a cache; an efficient sampling technique is used to estimate selectivities and working-set sizes for purposes of query partitioning. Finally, we use lottery-scheduling techniques to ensure fairness and impose a hard upper bound on staging time to avoid starvation. On our 8-core testbed, we were able to completely remove the memory I/O bottleneck, increasing throughput by a factor of 2 to 2.5, while also maintaining fairness and avoiding starvation.

As database systems have grown in terms of scale and complexity, administration tasks have become increasingly difficult and time consuming. A scarcity of skilled database professionals has meant that human costs have begun to dominate the total cost of ownership (TCO) of a database system. Database vendors are under immense pressure to provide solutions that make their products easy to administer in areas such as problem diagnostics, monitoring, query tuning, access control and system configuration.

RID-List (row id list) intersection is a common strategy in query processing, used in star joins, column stores, and even search engines. To apply a conjunction of predicates on a table, a query process ordoes index lookups to form sorted RID-lists (or bitmap) of the rows matching each predicate, then intersects the RID-lists via an AND-tree, and finally fetches the corresponding rows to apply any residual predicates and aggregates. This process can be expensive when the RID-lists are large. Furthermore, the performance is sensitive to the order in which RID lists are intersected together, and to treating the right predicates as residuals. If the optimizer chooses a wrong order or a wrong residual, due to a poor cardinality estimate, the resulting plan can run orders of magnitude slower than expected. We present a new algorithm for RID-list intersection that is both more efficient and more robust than this standard algorithm. First, we avoid forming the RID-lists up front, and instead form this lazily as part of the intersection. This reduces the associated IO and sort cost significantly, especially when the data distribution is skewed. It also ameliorates the problem of wrong residual table selection. Second, we do not intersect the RID-lists via an AND-tree, because this is vulnerable to cardinality mis-estimations. Instead, we use an adaptive set intersection algorithm that performs well even when the cardinality estimates are wrong. We present detailed experiments of this algorithm on data with varying distributions to validate its efficiency and predictability.

Automated storage management is critical for most dataintensive applications running on DBMSs. In large-scale storage subsystems, the workload is expected to vary with time. In order to ensure both QoS and efficient usage of storage resources, variation in the actual physical disks is allowed to support a single virtual disk. Such data migration generates extra IOs and consumes storage resources. Not only does data migration need to be scheduled ahead but it must also be scheduled in such a way that QoS violations do not occur because of the extra migration IOs. In this paper, we present a novel analytic framework, PULSTORE, for autonomically managing the storage to provide performance guarantee during migration.

The demands on storage systems are increasingly requiring expressiveness, fault-tolerance, security, distribution, etc. Such functionalities have been traditionally provided by DBMS. We propose a storage management system, SVL that leverages DBMS technology. The primary problem in block storage management is block virtualization, which is essentially an abstraction layer that separates the user view of storage from the implementation of storage. Storage virtualization standardizes storage management in a heterogeneous storage and/or host environment, and plays a crucial role in enhancing storage functionality and utilization. Currently specialized hardware or microcode-based solutions are popular for implementing block storage management systems, commonly referred to as disk controllers. We demonstrate how to take a general purpose commercial RDBMS, rather than a specialized solution, to support block storage management. We exploit the simple semantics of storage management systems to streamline database performance and thus attain acceptability from a storage point of view. This work promises to pave the way for diverse and innovative industrial applications of database management systems.

This paper proposes STORAGEDB; a paradigm for implementing storage virtualization using databases. It describes details for storing the logical-to-physical mapping information as tables within the database; handling the incoming I/O requests of the application as database queries; bookkeeping of the I/O operations as database transactions. In addition, STORAGEDB uses built-in DBMS features to support storage virtualization functionalities; as an example we describe how online table space migration can be used to support reallocation of logical disks. Finally, we describe our modifications to a traditional RDBMS implementation, in order to make it light-weight. Improving the performance of a traditional DBMS is critical for the acceptance of STORAGEDB since the performance overheads are considered a primary challenge in replacing existing storage virtualization engines. Our current lightweight RDBMS has a 19 times shorter invocation path length than the original. In comparison to the open-source virtualization software, namely LVM, the extra cost of STORAGEDB is within 20% of LVM in trace-driven tests, (unlike STORAGEDB, LVM did not have logging overhead). We consider these initial results as the "stepping stone" in the paradigm of applying databases for storage virtualization.

Although traditional databases and data warehouses have been exploited widely to manage persistent data, a large number of applications from sensor network need functional supports for transient data in the continuous data stream. One of the crucial functions is to summarize the data items within a sliding window. A sliding window contains a fixed width span of data elements. The data items are implicitly deleted from the sliding window, when it moves out of the window scope. Several one-dimensional histograms have been proposed to store the succinct time information in a sliding window. Such histograms, however, only handle the data items with attribute values in unary domains. In this paper, we explore the problem of extending the value to a multi-valued domain. A two-dimensional histogram, the hybrid histogram, is proposed to support sliding window queries on a practical multi-valued domain. The basic building block of the hybrid histogram is the exponential histogram. The hybrid histogram is maintained to capture the changes of data distribution. To further compress the exponential histograms, we propose a condensed exponential histogram without losing the error bound. Results of an extensive experimental study are included to evaluate the benefits of the proposed technique.

Maintaining approximate aggregates and summaries over data streams is crucial to handle the OLAP query workload that arises in applications, such as network monitoring and telecommunications. Furthermore, since the entire data is not available at all times the maintenance task must be done incrementally. We show that R(elaxed)Hist(ogram) is an appropriate summarization under data stream scenario. In order to reduce query estimation errors, we propose adaptive approaches which not only capture the data distribution, but also integrate independent query patterns. We introduce a workload decay model to efficiently capture global workload information and ensure that the query patterns from the recent past are weighted more than queries that are further in the past. We verify experimentally that our approach successfully adapts to continuously changing workload as well as data streams.