Automatic Database Management System Tuning Through，Large-scale Machine Learning.pdf

hardware

284

16页

7次

2021-07-27

免费下载

Automatic Database Management System Tuning Through

Large-scale Machine Learning

Dana Van Aken Andrew Pavlo Geoffrey J. Gordon Bohan Zhang

Carnegie Mellon University Carnegie Mellon University Carnegie Mellon University Peking University

dvanaken@cs.cmu.edu pavlo@cs.cmu.edu ggordon@cs.cmu.edu bohan@pku.edu.cn

ABSTRACT

Database management system (DBMS) conﬁguration tuning is an

essential aspect of any data-intensive application effort. But this

is historically a difﬁcult task because DBMSs have hundreds of

conﬁguration “knobs” that control everything in the system, such

as the amount of memory to use for caches and how often data

is written to storage. The problem with these knobs is that they

are not standardized (i.e., two DBMSs use a different name for the

same knob), not independent (i.e., changing one knob can impact

others), and not universal (i.e., what works for one application may

be sub-optimal for another). Worse, information about the effects

of the knobs typically comes only from (expensive) experience.

To overcome these challenges, we present an automated approach

that leverages past experience and collects new information to tune

DBMS conﬁgurations: we use a combination of supervised and un-

supervised machine learning methods to (1) select the most impact-

ful knobs, (2) map unseen database workloads to previous work-

loads from which we can transfer experience, and (3) recommend

knob settings. We implemented our techniques in a new tool called

OtterTune and tested it on three DBMSs. Our evaluation shows that

OtterTune recommends conﬁgurations that are as good as or better

than ones generated by existing tools or a human expert.

1. INTRODUCTION

The ability to collect, process, and analyze large amounts of data

is paramount for being able to extrapolate new knowledge in busi-

ness and scientiﬁc domains [35, 25]. DBMSs are the critical com-

ponent of data-intensive (“Big Data”) applications [46]. The per-

formance of these systems is often measured in metrics such as

throughput (e.g., how fast it can collect new data) and latency (e.g.,

how fast it can respond to a request).

Achieving good performance in DBMSs is non-trivial as they are

complex systems with many tunable options that control nearly all

aspects of their runtime operation [24]. Such conﬁguration knobs

allow the database administrator (DBA) to control various aspects

of the DBMS’s runtime behavior. For example, they can set how

much memory the system allocates for data caching versus the

transaction log buffer. Modern DBMSs are notorious for having

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 proﬁt or commercial advantage and that copies bear this notice and the full citation

on the ﬁrst page. Copyrights for components of this work owned by others than the

author(s) must be honored. Abstracting with credit is permitted. To copy otherwise, or

republish, to post on servers or to redistribute to lists, requires prior speciﬁc permission

and/or a fee. Request permissions from permissions@acm.org.

SIGMOD’17, May 14 - 19, 2017, Chicago, IL, USA

ISBN 978-1-4503-4197-4/17/05. . . $15.00

DOI: http://dx.doi.org/10.1145/3035918.3064029

many conﬁguration knobs [22, 47, 36]. Part of what makes DBMSs

so enigmatic is that their performance and scalability are highly de-

pendent on their conﬁgurations. Further exacerbating this problem

is that the default conﬁgurations of these knobs are notoriously bad.

As an example, the default MySQL conﬁguration in 2016 assumes

that it is deployed on a machine that only has 160 MB of RAM [1].

Given this, many organizations resort to hiring expensive experts

to conﬁgure the system’s knobs for the expected workload. But as

databases and applications grow in both size and complexity, opti-

mizing a DBMS to meet the needs of an application has surpassed

the abilities of humans [11]. This is because the correct conﬁgura-

tion of a DBMS is highly dependent on a number of factors that are

beyond what humans can reason about.

Previous attempts at automatic DBMS conﬁguration tools have

certain deﬁciencies that make them inadequate for general purpose

database applications. Many of these tuning tools were created

by vendors, and thus they only support that particular company’s

DBMS [22, 33, 37]. The small number of tuning tools that do sup-

port multiple DBMSs still require manual steps, such as having the

DBA (1) deploy a second copy of the database [24], (2) map depen-

dencies between knobs [49], or (3) guide the training process [58].

All of these tools also examine each DBMS deployment indepen-

dently and thus are unable to apply knowledge gained from previ-

ous tuning efforts. This is inefﬁcient because each tuning effort can

take a long time and use a lot of resources.

In this paper, we present a technique to reuse training data gath-

ered from previous sessions to tune new DBMS deployments. The

crux of our approach is to train machine learning (ML) models

from measurements collected from these previous tunings, and use

the models to (1) select the most important knobs, (2) map previ-

ously unseen database workloads to known workloads, so that we

can transfer previous experience, and (3) recommend knob settings

that improve a target objective (e.g., latency, throughput). Reusing

past experience reduces the amount of time and resources it takes

to tune a DBMS for a new application. To evaluate our work, we

implemented our techniques using Google TensorFlow [50] and

Python’s scikit-learn [39] in a tuning tool, called OtterTune,

and performed experiments for two OLTP DBMSs (MySQL, Post-

gres) and one OLAP DBMS (Vector). Our results show that Ot-

terTune produces a DBMS conﬁguration for these workloads that

achieves 58–94% lower latency compared to their default settings

or conﬁgurations generated by other tuning advisors. We also show

that OtterTune generates conﬁgurations in under 60 min that are

within 94% of ones created by expert DBAs.

The remainder of this paper is organized as follows. Sect. 2 be-

gins with a discussion of the challenges in database tuning. We then

provide an overview of our approach in Sect. 3, followed by a de-

scription of our techniques for collecting DBMS metrics in Sect. 4,

1009

Buffer pool size (MB)

500

1000

1500

2000

2500

Log ﬁle size (MB)

200

400

600

800

99th %-tile (sec)

0.0

0.5

1.0

1.5

2.0

(a) Dependencies

500 1000 1500 2000 2500 3000

Buffer pool size (MB)

0.0

1.0

2.0

3.0

99th %-tile (sec)

(b) Continuous Settings

Conﬁg #1 Conﬁg #2 Conﬁg #3

0.0

2.0

4.0

6.0

99th %-tile (sec)

Workload #1

Workload #2

Workload #3

2000 2004 2008 2012 2016

Release date

200

400

600

Number of knobs

MySQL

Postgres

(d) Tuning Complexity

Figure 1: Motivating Examples – Figs. 1a to 1c show performance measurements for the YCSB workload running on MySQL (v5.6) using different

conﬁguration settings. Fig. 1d shows the number of tunable knobs provided in MySQL and Postgres releases over time.

identifying the knobs that have the most impact in Sect. 5, and rec-

ommending settings in Sect. 6. In Sect. 7, we present our experi-

mental evaluation. Lastly, we conclude with related work in Sect. 8.

2. MOTIVATION

There are general rules or “best practice” guidelines available

for tuning DBMSs, but these do not always provide good results

for a range of applications and hardware conﬁgurations. Although

one can rely on certain precepts to achieve good performance on a

particular DBMS, they are not universal for all applications. Thus,

many organizations resort to hiring expensive experts to tune their

system. For example, a 2013 survey found that 40% of engagement

requests for a large Postgres service company were for DBMS tun-

ing and knob conﬁguration issues [36].

One common approach to tuning a DBMS is for the DBA to copy

the database to another machine and manually measure the perfor-

mance of a sample workload from the real application. Based on

the outcome of this test, they will then tweak the DBMS’s conﬁgu-

ration according to some combination of tuning guidelines and in-

tuition based on past experiences. The DBA then repeats the exper-

iment to see whether the performance improves [47]. Such a “trial-

and-error” approach to DBMS tuning is tedious, expensive, and

inefﬁcient because (1) many of the knobs are not independent [24],

(2) the values for some knobs are continuous, (3) one often cannot

reuse the same conﬁguration from one application to the next, and

(4) DBMSs are always adding new knobs.

We now discuss these issues in further detail. To highlight their

implications, we ran a series of experiments using MySQL (v5.6)

that execute variations of the YCSB workload with different knob

settings. We present the details of our operating environment for

these experiments in Sect. 7.

Dependencies: DBMS tuning guides strongly suggest that a

DBA only change one knob at a time. This is wise but woefully

slow given the large number of knobs. It is also not entirely helpful

because changing one knob may affect the beneﬁts of another. But

it is difﬁcult enough for humans to understand the impact of one

knob let alone the interactions between multiple ones. The differ-

ent combinations of knob settings means that ﬁnding the optimal

conﬁguration is NP -hard [49]. To demonstrate this point, we mea-

sured the performance of MySQL for different conﬁgurations that

vary the size of its buffer pool

and the size of its log ﬁle.

The

results in Fig. 1a show that the DBMS achieves better performance

when both the buffer pool and log ﬁle sizes are large. But in gen-

eral, the latency is low when the buffer pool size and log ﬁle size

are “balanced.” If the buffer pool is large and the log ﬁle size is

small, then the DBMS maintains a smaller number of dirty pages

and thus has to perform more ﬂushes to disk.

Continuous Settings: Another difﬁcult aspect of DBMS tuning

is that there are many possible settings for knobs, and the differ-

MySQL Knob: innodb_buffer_pool_size

MySQL Knob: innodb_log_file_size

ences in performance from one setting to the next could be irreg-

ular. For example, the size of the DBMS’s buffer pool can be an

arbitrary value from zero to the amount of DRAM on the system.

In some ranges, a 0.1 GB increase in this knob could be incon-

sequential, while in other ranges, a 0.1 GB increase could cause

performance to drop precipitously as the DBMS runs out of phys-

ical memory. To illustrate this point, we ran another experiment

where we increase MySQL’s buffer pool size from 10 MB to 3 GB.

The results in Fig. 1b show that the latency improves continuously

up until 1.5 GB, after which the performance degrades because the

DBMS runs out of physical memory.

Non-Reusable Conﬁgurations: The effort that a DBA spends

on tuning one DBMS does not make tuning the next one any eas-

ier. This is because the best conﬁguration for one application may

not be the best for another. In this experiment, we execute three

YCSB workloads using three MySQL knob conﬁgurations. Each

conﬁguration is designed to provide the best latency for one of the

workloads (i.e., conﬁg #1 is the best for workload #1, same for #2

and #3). Fig. 1c shows that the best conﬁguration for each workload

is the worst for another. For example, switching from conﬁg #1 to

conﬁg #3 improves MySQL’s latency for workload #3 by 90%, but

degrades the latency of workload #1 by 3500%. Conﬁg #2 provides

the best average performance overall. But both workloads #1 and

#3 improve by over 2× using their optimized conﬁgurations.

Tuning Complexity: Lastly, the number of DBMS knobs is al-

ways increasing as new versions and features are released. It is

difﬁcult for DBAs to keep up to date with these changes and under-

stand how that will affect their system. The graph in Fig. 1d shows

the number of knobs for different versions of MySQL and Postgres

dating back to 2001. This shows that over 15 years the number of

knobs increased by 3× for Postgres and by nearly 6× for MySQL.

The above examples show how tricky it is to conﬁgure a DBMS.

This complexity is a major contributing factor to the high total cost

of ownership for database systems. Personnel is estimated to be

almost 50% of the total ownership cost of a large-scale DBMS [43],

and many DBAs spend nearly 25% of their time on tuning [21].

A better approach than examining each database application sep-

arately is to use an automated tool that leverages the knowledge

gained from one application to assist in the tuning of others.

3. SYSTEM OVERVIEW

We now present our automatic database tuning tool that over-

comes the problems that we described above. OtterTune is a tuning

service that works with any DBMS. It maintains a repository of

data collected from previous tuning sessions, and uses this data to

build models of how the DBMS responds to different knob conﬁgu-

rations. For a new application, it uses these models to guide experi-

mentation and recommend optimal settings. Each recommendation

provides OtterTune with more information in a feedback loop that

allows it to reﬁne its models and improve their accuracy.

1010

of 16

免费下载

postgresql

关注

评论