May 14, 2010

The Case for a Combined OLTP and OLAP Benchmark

In our research area "In-Memory Data Management for Enterprise Applications" we propose to reunite two systems supporting major enterprise operations: Online Transactional Processing (OLTP) and Online Analytical Processing (OLAP) systems. The two systems basically work on the same set of business data, using differently optimized data structures for their specific use cases. Latest developments in in-memory database technology and column-oriented data organization explored, for example, in the HYRISE project or the "In-Memory Data Management" bachelor's project encourage the idea of integrating the two workloads, or at least major parts of them, into one system.
In this blog post I will make the case for a new benchmark that combines transactional and analytical processing in order to analyze the aforementioned combined systems and explain what differences are introduced when compared to existing benchmarks.
Benchmarking as referred to in this post relates to evaluating the performance of software systems for managing business data, such as data management systems for transaction processing and reporting. Separate benchmarks exist in this specific area of application for evaluating systems built for transaction processing and analytics. However, only limited statements can be made concerning the ability of existing data management systems to handle a combination of the different workloads since OLTP and OLAP have so far been treated separately.
In any case, it is obvious that existing data management systems are not able to cope with a mixed workload of OLTP- and OLAP-style queries particularly well. Otherwise the mentioned separation would not have occurred. However, new systems that tackle mixed workload scenarios are faced with a greater challenge to keep up with the performance of either of the specialized systems concerning a pure workload. This challenge is well grounded in the aspect of being able to manage a wider range of query types.

Existing Benchmarks

The benchmarks of the Transaction Processing Performance Council (TPC) have become the standard benchmarks for OLTP and OLAP. TPC's currently active benchmarks are TPC-C and its successor TPC-E for OLTP and TPC-H for OLAP. TPC-DS, a new benchmark for decision support systems, is now available as a draft version. The star schema benchmark (SSB) takes the decision support benchmark TPC-H a step further by deriving a pure star schema from the schema layout of TPC-H in order to evaluate the performance of data management systems built for pure data warehouse environments.
Existing benchmarks could be applied to a combined architecture for OLTP and operational reporting, simply running the benchmarks in parallel, but this would lead to a partial picture of the actual performance of such a system measuring only the effects of hardware resource contention. The reason for this is that the underlying data schemes of the different benchmarks differ to a great extent and are hard to be integrated to create a sufficiently relevant and still realistic data schema for both workloads. However, conflicts arising from data access are one characteristic of particular interest for a combined workload. Consequently, new means are needed in order to evaluate and compare new solutions with each other and also with existing ones, integrating the aspect of the mixed workload, while covering desired business scenarios. These, we will investigate in our Combined Benchmark for Transactions and Reporting (CBTR).

Real Data

CBTR is not just defined closely resembling the behavior of original systems of an existing enterprise, but actually operates with real data instead of generated data. Most data generators consider statistical distributions of data in real enterprises. These, however, still lead to idealistic data as real data usually deviates in one way or the other from statistical distributions. Therefore, generated data sets never achieve the same behavior as really operating with original data. The best way of getting the real picture is still testing within the enterprise, which however is not possible, due to high availability requirements and a costly implementation of the benchmark activity. Consequently, CBTR, taking a snapshot of real systems, is as close to a real scenario as possible.

More Benchmarking Dimensions

Benchmarking Dimensions
CBTR utilizes three scales to focus on different parameters of the database system under test. The first is the commonly used one for varying the data set size, which will not be further discussed here.
The second scale is the variation of the logical database schema including physical structures for optimization, such as indexes, views etc., from being optimized for transaction processing to being optimized for reporting. The two ends of this scale are clear: the OLTP-optimized schema can directly be taken from established OLTP systems and the OLAP-end of this scale resulting in a star or snowflake schema optimized for reporting queries is also conceivable. However, the schema alterations between these two end-points are the most interesting, building one base of operation as neither of the two aforementioned schemes is ideal for the other workload.
The third scale is a variation of the workloads starting from pure OLTP, going over a mixed workload and ending in a pure analytical workload. Applying the pure OLTP workload on the OLTP schema and doing the same for the OLAP workload, acts as a baseline to compare the mixed workload. As a result, we will quantify performance benefits and penalties of the underlying database schema and the physical optimizations and create a base for comparisons with existing data management systems that are optimized for pure workloads. Applying a mixed workload leads to insights regarding the effects the two distinct workloads have on each other when released upon one and the same set of data.

Author: Anja Bog