Sigmod 2013

Performance and Resource Modeling in Highly-Concurrent

OLTP Workloads

Barzan Mozafari∗ Carlo Curino† Alekh Jindal∗ Samuel Madden∗

∗CSAIL MIT †Microsoft

{barzan,alekh,madden}@csail.mit.edu ccurino@microsoft.com

ABSTRACT

Database administrators of Online Transaction Processing (OLTP)

systems constantly face difficult questions. For example, “What is

the maximum throughput I can sustain with my current hardware?”,

“How much disk I/O will my system perform if the requests per second

double?”, or “What will happen if the ratio of transactions in my

system changes?”. Resource prediction and performance analysis are

both vital and difficult in this setting. Here the challenge is due to

high degrees of concurrency, competition for resources, and complex

interactions between transactions, all of which non-linearly impact

performance.

Although difficult, such analysis is a key component in enabling

database administrators to understand which queries are eating up

the resources, and how their system would scale under load. In

this paper, we introduce our framework, called DBSeer, that addresses

this problem by employing statistical models that provide

resource and performance analysis and prediction for highly concurrent

OLTP workloads. Our models are built on a small amount

of training data from standard log information collected during

normal system operation. These models are capable of accurately

measuring several performance metrics, including resource consumption

on a per-transaction-type basis, resource bottlenecks, and

throughput at different load levels. We have validated these models

on MySQL/Linux with numerous experiments on standard benchmarks

(TPC-C) and real workloads (Wikipedia), observing high accuracy

(within a few percent error) when predicting all of the above

metrics.

Categories and Subject Descriptors

H.2.4 [Systems]: Relational databases

Keywords

OLTP, Performance Predictions, Multi-tenancy

1. INTRODUCTION

Operating a large database management system (DBMS) or

a multi-tenant “database-as-a-service” [16] is a challenging and

stressful task for database administrators (DBA), especially as the

Permission to make digital or hard copies of all or part of this work for

personal or classroom use is granted without fee provided that copies are

not made or distributed for profit or commercial advantage and that copies

bear this notice and the full citation on the first page. To copy otherwise, to

republish, to post on servers or to redistribute to lists, requires prior specific

permission and/or a fee.

SIGMOD’13, June 22–27, 2013, New York, New York, USA.

Copyright 2013 ACM 978-1-4503-2037-5/13/06 ...$15.00.

DBMS starts experiencing heavy concurrent load. Although some

databases provide tools for measuring the run-time of an individual

query, many performance problems are a result of interactions

between concurrent queries, which existing systems are not capable

of modeling. Many transactions that run fine in isolation become

much slower when run together, as they interact in complex

ways and contend for shared resources. Load that is added over

time may cause resources that were previously abundant to become

constrained, and query performance to plummet. Applications may

generate unpredictable, time-varying load that puts strain on different

resources (e.g., RAM, disk, or CPU) at different times. To handle

all these scenarios, we need a way to attribute system load, on a perresource

basis to different queries, transactions, or applications. This

attribution enables a number of useful applications, including:

● Diagnosis / Performance Inspection: Why is a given query

running slow (in the presence of concurrency)? Which transaction

groups are causing the spike of lock wait times in the DBMS?

● Run-time Performance Isolation: In a database supporting

multiple applications, which application/transaction is using more

than its allocated share of resources? At what rate should transactions

of a given application be admitted/ dropped in order to avoid

any SLA (service-level agreement) violations?

● Billing: What is the actual contribution of each workload to

the overall resource consumption?

A second challenge for DBAs is to understand how database resource

consumption

...