DB BM

Download as pdf or txt
Download as pdf or txt
You are on page 1of 4

A Generic Database Benchmarking Service

Martin Kaufmann #∗1 , Peter M. Fischer†2 , Donald Kossmann #3 , Norman May ∗4


#
Systems Group, ETH Zürich, Switzerland
1 3
[email protected], [email protected]

Albert-Ludwigs-Universität Freiburg, Germany
2
[email protected]

SAP AG, Walldorf, Germany
4
[email protected]

Abstract—Benchmarks are widely applied for the development generic benchmarking service, which is demonstrated here for
and optimization of database systems. Standard benchmarks the first time. Deploying a long-running service instead of an
such as TPC-C and TPC-H provide a way of comparing the ad-hoc script collection or program has numerous advantages:
performance of different systems. In addition, micro benchmarks
can be exploited to test a specific behavior of a system. • Collection of benchmark artifacts (such as workload
Yet, despite all the benefits that can be derived from bench- generators and queries), benchmark definitions and results
mark results, the effort of implementing and executing bench- • Analysis of collected data for comparison with reference
marks remains prohibitive: Database systems need to be set up, results or performance regression tests
a large number of artifacts such as data generators and queries
• Automation of common tasks such as running experi-
need to be managed and complex, time-consuming operations
have to be orchestrated. In this demo, we introduce a generic ments, regression analysis or detecting performance bugs
benchmarking service that combines a rich meta model, low • Small incremental costs to define individual benchmark
marginal cost and ease of use, which drastically reduces the time artifacts and to add a new database server or users
and cost to define, adapt and run a benchmark.
Our demo also provides a number of technical contributions:
I. I NTRODUCTION • A rich meta model to express all aspects of benchmarking
• A complete end-to-end solution with default implemen-
The benchmarks defined by the TPC consortium [1] and
tations for most of the common functionality
their derivatives [2], [3] provide the de-facto standard in eval-
• An extensible architecture to include custom components
uating and comparing the performance of database systems.
• An easy-to-use web-based UI, fully supporting bench-
Yet, running these benchmarks incurs a significant effort:
mark definition and result analysis
A range of tools need to be co-ordinated to run the actual
The service can easily be deployed in the cloud. Due to
workloads, to modify the workloads according to specific dis-
the sensitive nature of benchmarking (limitations on result
tributions, and to visualize the results. A considerable amount
publishing, license fees, data privacy, etc.), however, we expect
of state (e.g., input data) needs to be generated and managed.
it to be used in rather restricted settings and private clouds.
Judging from our own experience in the database development
In this demonstration, users will be able to interact with
team at SAP, typically a large number of scripts written in
the system, register database servers, define new benchmarks,
different languages are applied to implement benchmarks.
adjust data generators or SQL statements and understand the
The problem of defining and running benchmarks has been
effects using various graphical representations.
recognized by both the research community and commercial
vendors, and has lead to a wide range of tools. Most of II. C ONCEPTS
these, however, are frameworks that focus solely on the ad- The goals we have defined for the benchmarking service
hoc execution of a particular kind of benchmark. Examples of require several aspects of conceptual underpinning. First we
such frameworks include SIMS [4] (local resources), OLTP- describe how a benchmark is modeled in our service. Next, we
Benchmark [5] (OLTP), YCSB [6] (cloud) and PolePosition sketch the architecture of the system. We continue with a de-
[7] (OR mapping). Quest Benchmark Factory [8] is a com- scription of the web-based user interface of the benchmarking
mercial product which supports the definition of benchmarks service and explain how a benchmark can be executed.
by means of a rich-client program. Yet, its scripting ap-
proach leads to a rather limited reusability and extendibility A. Modeling a Benchmark
beyond predefined components. Lately, there have been two Since our benchmarking service aims to combine flexibility
approaches to offer benchmarking as a service: XQBench [9], with rich data operations and user guidance, a comprehensive
based on XCheck [10] and Liquid Benchmarking [11]. Both and expressive model is required. The key benefit of this meta
approaches, however, aim at non-relational data (XML, RDF) model is that artifacts (i.e., components of a benchmark) can
and provide limited meta models and execution flexibility. be parameterized, stored and reused. The intuitive definition
In order to consolidate and unify various benchmarks and to of these artifacts is achieved by a web-based UI, which also
simplify the development of new benchmarks, we developed a supports archiving and comparing results.
Parameter
Sercice Controller
Benchmark Definition Binding Data Generator. A data generator can be applied before
Execution Order
c the execution of an SQL statement in order to populate the
d
Execution Node database instance with an experimental data set. Different
e types of data generators are supported and may be combined:
Measurements
f
Database Instance
a b 1) Predefined generators for common benchmarks (e.g., all
System Architecture
Schema DDL Generator DB Server Query
the TPC benchmarks), supporting the parameters given
Instance Instance Instance Instance Instance
in the benchmark specification.
Schema
Type
DDL
Type
Generator
Type
DB Server
Type
Query
Type
2) Generic user-defined generators: a built-in generator
using information from data definition and database
Parameter Definition server information, covering common aspects such as
Versioned Results
size, value distribution and correlation between the ta-
Service Controller bles. Furthermore, referential integrity constraints and
Fig. 1. Data Model
arbitrary join paths with a chosen selectivity can be
defined. All these aspects are exposed as parameters.
As visualized by the abstract data model (Figure 1), a 3) Custom generators: Specific requirements can be ex-
benchmark definition is a combination of several artifact types: pressed in the service as custom classes or by calling an
Schema Definition, DDL Tuning, Data Generators, Database external tool (such as [12]). Parameters of these tools
Servers, Query Set, Execution Order. A simplified example need to be specified for the integration into the service.
of such a benchmark definition is (“TPC-H schema”, “Index
on L SHIPDATE”, “TPC-H dbgen: Scale 100”, “SAP HANA,
Database Servers. Since our benchmarking service aims
PostgreSQL”, “Q1,Q5”, “uniform mix”). In addition to general
at supporting a multitude of different database servers, the
artifact selection, most of these artifacts can be parameterized.
Generally speaking, a benchmark can be seen as a subset meta model needs to cover three aspects: 1) Capabilities
of the cross-product of all the artifact types and parameters. of the database systems involved such as data types, DML
Given the possibly large design space, we introduced two expressions, etc. This information can be utilized to tailor
means of structuring: 1) Templates define the type of a bench- DDL and DML statements. 2) Operational information on how
mark. Examples of such templates include “a parameterized to perform operations on the actual server instances using
query on a server (one curve per parameter)” or “several standard call-level interfaces like JDBC, e.g., establishing a
grouped generator runs (one curve per server and query)”. connection, executing a query, interpreting the results, all of
2) Measurements are a grouping of artifacts along particular which will be relevant when running a benchmark. 3) Tunable
aspects (yielding, for instance, a line in a graph for a query, parameters that are not reachable via normal DDL statements,
scaled over the database size). The known set of artifacts, such as the “merge interval” of SAP HANA or memory/disk
possible parameters and templates provide information to the settings of Oracle. Besides custom call-level statements, this
GUI to let the user intuitively design and run benchmarks. may involve a collection of scripts at OS-level access.

The artifacts of a benchmark are described as follows: Query Set. The set of queries to be executed in an experiment
can consist of arbitrary DML statements in their textual form.
Schema Definition. The aim of the schema meta model is to This includes standard SQL statements like queries, insert, up-
provide abstract information on the data model of individual date and delete operations, but also stored procedures or scripts
benchmarks (such as TPC-H), in particular on tables, columns, in languages such as PL/SQL or T-SQL. Each statement has a
data types and constraints. This information can be exploited possibly empty set of parameters (including type information)
in various ways, among them: 1) Generating DDL statements for input and output values, allowing for parameterized queries
for creating tables (with metadata specific for a database server and reusing the output of one query as input for another.
type) 2) Generating consistent data-preserving constraints and Depending on the specification, these parameters may be
relationships. In terms of parameterization, we allow the user applied by text replacement or as invocation-time arguments.
to choose which columns are being used for each experiment.
Execution Order. Many benchmarks do not consider in-
DDL Tuning. Beside the schema, there are many aspects in dividual queries in isolation; instead, queries are combined
DDLs which can affect performance. We separate them from at varying levels of complexity. The meta model of the
the schema, so as to provide more flexibility in benchmark benchmarking service provides two means to express such
design and execution. Typical “tuning” DDL aspects include interactions: 1) For workloads that consider state changes
index creation, materialized views and partitioning. Given the explicitly, an ordering of the query set may be given. 2) For
abstract modeling of the schema and the tuning, the system workloads which combine multiple queries with different cost
can create both combined and incremental DDL statements at or characteristics, a query mix can be specified. Once more, a
different states within a running experiment. built-in model and driver provide the means to define common
aspects like the distribution of query types or their timing.
and the queries with their parameters. Once an experiment has
finished, its results can be compared to similar experiments.
Meta Model
DB Instance DB Instance The web front-end provides a comprehensive access to
Coordinator features, models and results of the experiments. Yet, the
Benchmark Description
Job Queue Execution
system supports including custom code and classes for special
Nodes problems such as specific parameter distributions or complex
Versioned Results Web Front-End
and state-dependent conditional execution orders.
Service Controller
Fig. 2. System Architecture of the Benchmarking Service D. Running Benchmarks
Custom query mix drivers may be included to manage those Benchmarking is inherently expensive, as it involves
requirements which are not expressible by standard settings. computation- and data-intensive tasks like running input data
The entire meta model (artifacts and benchmark specifica- generators and loading the generated data into databases.
tions) as well as results are stored in a versioned database. Furthermore, our definition of a benchmark as a cross-product
With this versioning we can track how interactions among of its contributing artifacts and their parameters, provides great
artifacts have developed. Furthermore, artifacts can have vari- flexibility, but can possibly entail high cost. The benchmarking
ants, e.g., custom queries for specific DBMSs if automatic service contains several strategies to cope with these costs:
tailoring from meta model data is not sufficient. Users can specify directly or implicitly (using a template)
which execution flow to follow. We apply a number of
B. System Architecture optimizations: The sequence of steps can be modified to
A distributed architecture, as shown in Figure 2, was chosen reuse previous, costly stages (like dataset creation or DB
for the service: A central Service Controller keeps track of loading). In addition, the data generator performs caching and
the meta model instances, which includes both the actual pipelining (depending on the setting) to reduce memory and/or
artifacts and the results. The process of running an experiment CPU costs. Whenever possible, the controller distributes and
is controlled by a Coordinator Node, which contains a queue parallelizes steps as to take advantage of available nodes. A
of benchmarks that are about to be executed, distributes jobs typical flow of such an execution flow is shown in Figure 4.
and detects node failures. The benchmarks are run on several Correctness of the results and precision of measurements
Execution Nodes in parallel to simulate a multi-user workload can be ensured. Within an experiment, measurements are
or speed up measurements. Each execution node in turn may performed on a “hot” database and repeated several times
distribute the measurements over several database servers. to achieve stable results. Users may specify reference results
Database servers can be accessed at different levels, mainly against which the output values of queries are to be compared.
using call-level interfaces such as JDBC with queries and
statements derived from workloads and DBMS metadata. And, III. D EMONSTRATION
when necessary, at the OS level using scripts to start/stop Our demonstration will consist of three parts: 1) introduc-
databases and perform external tuning. Clearly, more access tion, 2) benchmark definition 3) running the experiment.
rights provide more precise control of the execution. A few words on the benchmark setup: While the bench-
The usage model assumes a benchmark cluster or a “pri- marking service is inherently designed for distribution, we
vate” cloud setting. Using it in a public cloud is possible, will make our demonstration more tangible by using two local
but problematic due to the legal and financial constraints machines – one running the GUI and service controller, the
of benchmarking (commercial) DBMS: Benchmarking results
must not be published without explicit permissions by the ven-
dors. Running DBMS instances in the cloud incurs additional
licensing fees, while running DBMSs on customer premises
and accessing them from the cloud is often prohibited for
security reasons.

C. Web-Based User Interface


The service controller provides a web front-end (Figure 3)
for the definition of artifacts and visualization of results. This
GUI leverages the powerful meta model introduced in Section
II-A by exposing the various kinds of artifact types.
For the definition of a benchmark, the web front-end allows
the user to combine artifacts and specify parameters. For
instance, in the TPC-H data definition, the configuration of
a benchmark is done in multiple steps. It includes the known
DDL tuning options, the parameters for generating input data Fig. 3. Screenshot of the Web Front-End
other hosting execution nodes and DB Server installations. If Start

possible, we will also include nodes from our lab.


Create
new DB no Measurement
Initialization?
A. Part 1: Introduction and Motivation
delete
Create
We will begin the demonstration by illustrating some scripts Tables
Delete
Generator?

Tables yes no
that were deployed at SAP to run standard benchmarks such DDL Tuning
Run Data
as TPC-C and TPC-H before our benchmarking service was Generator
Pre-Populate? More
introduced. By trying to tune some benchmark parameters in Execute
Measurements
?
yes no no
the script, we will show how inconvenient and error-prone Query
yes

defining a benchmark was in the past. Based on a standard Pre-Populate


Data
Save
Repeat?
no
Measurement
benchmark, we will demonstrate how easily the configuration Initialization Measurement

can be adapted using the new system. End

B. Part 2: Defining a Benchmark and Adding DBMSs Fig. 4. Steps for Running an Experiment
In the second part of the demonstration we will show how in a traditional scripting language. All reoccurring tasks, such
the web-interface can be used to create a new benchmark from as plot generation, storing, archiving and comparing results can
scratch in only a few minutes and run on different DBMSs. be configured and are handled by the application automatically.
The example use-case will be the evaluation of join queries.
IV. C ONCLUSION
First, the audience will see how several different database
servers can be registered with the system. We will continue This demonstration presents a generic benchmarking service
by creating a new database schema related to the synthetic and its benefits over previous benchmarking frameworks: An
data used to micro-benchmark the join queries. This schema, expressive meta model supports defining and reusing artifacts
in this instance, will include two tables with attributes of and benchmark definitions by capturing the relevant properties
different types and a foreign-key constraint. In the following and “powering” a user-friendly, yet effective web GUI. Fur-
step we will create a user defined data generator for this thermore, the service incurs only a small cost when adding
schema together with the audience. We will agree on different new resources and provides automation of common tasks. Rich
types of distributions for each field of the tables (e.g., uniform visualization and analysis capabilities complete the picture.
distribution, Zipf distribution and sequences) to assess how the A previous version of this service is used extensively by the
joins are to be processed on skewed data. The generator will database engineering group at SAP. We noticed developers
populate the database with values fulfilling the constraints and now run significantly more experiments than before since
distribution. The demonstration will continue with the creation benchmarks are much easier to define, leading to the discovery
of SQL join queries with parameters influencing aspects and correction of regressions and correctness bugs. The tool
such as the selectivity of the join predicate. Next, servers, has also been applied to non-relational workloads, in particular
a schema, a generator and queries will be combined and a XML and XQuery. In terms of scalability, we have so far
new benchmark will be defined. We will add a measurement reached a scaling factor of 100 for TPC-H. We plan to release
to the benchmark for each configuration of the join queries, an open-source version [13] of the benchmarking service soon.
each DBMS, and we will choose a suitable visualization. R EFERENCES
C. Part 3: Running the Experiment and Results [1] “TPC transaction processing performance council,” Website, 2012,
http://www.tpc.org/.
In the final part of the demonstration the benchmark will [2] F. Funke, A. Kemper, and T. Neumann, “Benchmarking Hybrid
be executed. The progress of the running experiment will be OLTP&OLAP Database Systems,” in BTW, 2011.
[3] P. O’Neil, E. O’Neil, and X. Chen, “The star schema benchmark,”
monitored using the web-interface. When the experiment is University of Massachusetts, Boston, Tech. Rep., 2007.
finished, an e-mail with the link to the result page will be sent. [4] H. J. Jeong and S. H. Lee, “An integrated benchmark suite for database
Next, together with the audience, we will examine the plot and systems,” in ISDB, 2002, pp. 74–79.
[5] “OLTP-Benchmark,” Website, 2012, http://www.oltpbenchmark.com/.
interpret the result. We will find out that it is necessary to adapt [6] B. F. Cooper et al., “Benchmarking cloud serving systems with YCSB,”
the data type and the selectivity of the join attributes, and we in SoCC, 2010.
will rerun the experiment. In addition, one of the database [7] “PolePosition,” Website, 2012, http://www.polepos.org/.
[8] “Quest Benchmark Factory,” Website, 2012,
servers will need to be tuned by adding an index. The effect http://www.quest.com/benchmark-factory/.
of this modification can be visualized by comparing the new [9] P. M. Fischer, “XQBench - A XQuery Benchmarking Service,” in XML
result with the previous one in a single diagram. Prague, 2011, pp. 341–355.
[10] L. Afanasiev et al., “XCheck: A Platform for Benchmarking XQuery
A most relevant aspect of the demonstration is that we will Engines,” in VLDB, 2006.
create a new benchmark from scratch, taking into account the [11] S. Sakr and F. Casati, “Liquid Benchmarks: Benchmarking-as-a-
feedback and suggestions from the audience. It will become Service,” in JCDL, 2011.
[12] J. Gray et al., “Quickly generating billion-record synthetic databases,”
evident that these measurements can be defined within the in SIGMOD Conference, 1994.
benchmarking service in only a few minutes as opposed to [13] “A Generic Database Benchmarking Service,” Project Website, 2013,
several hours for the manual implementation of the benchmark http://www.benchmarking-service.org/.

You might also like