2024 IEEE 40th International Conference on Data Engineering (ICDE), 83-95._Functionality-Aware Database Tuning via Multi-Task Learning_OceanBase.pdf

迹部景吾

13页

1次

2024-12-31

免费下载

Functionality-Aware Database Tuning via

Multi-Task Learning

Zhongwei Yue

†

, Shujian Peng

†

, Peng Cai

†∗

, Xuan Zhou

†

, Huiqi Hu

†

, Rong Zhang

†

, Quanqing Xu

, Chuanhui Yang

†

East China Normal University,

OceanBase, Ant Group

{52195100010, 51205903033}@stu.ecnu.edu.cn, {pcai, xzhou, hqhu, rzhang}@dase.ecnu.edu.cn,

{xuquanqing.xqq, rizhao.ych}@oceanbase.com

Abstract—Functionalities of a database system are co-designed

and jointly maintain the database performance. Each function-

ality usually has its own metrics to evaluate its state. Previous

knobs tuning methods regard the database system as a black

box and aim to automatically ﬁnd the optimal conﬁgurations

by collecting and observing the overall performance data (e.g.,

transaction throughput per second) under various conﬁguration

knobs. However, if a functionality is not running in the tuning

phase, its knobs irrelevant to performance changes can also be

tuned by existing tools and potential risks would be introduced.

To resolve this problem, we design a database knob tuning

framework to support functionality-aware knobs tuning. It uses

multi-task learning to take the database overall performance as

the objective of main learning task, and each function module

as a separate learning task. This framework enhances the tuning

results through learning the relationships between different tasks,

and avoids adjusting irrelevant knobs by perceiving the status of

functionalities. We validate its generalizability on OceanBase and

PostgreSQL. Experimental results show that better performances

were achieved on the overall performance and the metrics of

various functionalities.

Index Terms—knob tuning, database system, multi-task learn-

ing, Gaussian process

I. INTRODUCTION

To achieve the best running performance under various

workloads and deployment environments, modern database

systems allow the users to tune knobs (or parameters) of

their functionalities. Finding a set of optimal knob settings

requires the DBA to have a deep understanding of the deployed

database systems. However, manually tuning these knobs

is unscalable, especially for cloud service providers which

support a huge number of database instances [1]. Therefore,

how to automatically tune database parameters has received

much attention [2]–[8].

The workﬂow of most automatic database tuning systems

can be summarized as: First, replay the workload to obtain

overall performance (e.g., transaction throughput and latency)

or resource occupancy (e.g., CPU and I/O utilization) of the

database and judge the quality of the conﬁguration knobs

according to the collected performance data. Second, calculate

the next set of promising knobs and re-run the workload

by applying these knobs to the database. The two steps

∗

Corresponding Author

are repeated until some stop criteria are satisﬁed (e.g., the

allocated tuning time has expired).

In this work, we argue that the change in the overall

performance of the tuned database system may mislead the

conﬁguration tuning. Modern distributed DBMSes have mul-

tiple functionalities such as SQL optimization, load balancing,

data compaction, etc. Each functionality has its own knobs and

it can be triggered under various conditions. Even under the

same knob settings and workloads, the transaction throughput

and mean latency may ﬂuctuate as different functionalities

are triggered [9]. It will happen that the knobs of a speciﬁc

functionality are tuned even if this functionality does not work

during the phase of automatic parameter tuning. Actually,

the adjustment on these knobs of not-running functionality

is unrelated to the changes in the overall performance. This

may introduce an unknown effect if this functionality starts

to work using these unveriﬁed knobs values. In the following,

we present a concrete example for this problem occurred in

real-world scenarios.

OceanBase [10], [11] adopted the well-known LSM-based

storage engine. Its parameter major

compact trigger

of the

major compaction functionality is to control how many minor

compactions are required to trigger a major compaction. This

parameter is to make a trade-off between read ampliﬁcation

and write ampliﬁcation. Previous tuning tools such as Ot-

terTune [3] could adjust the major

compact trigger’s value

to 53000 although the major compaction functionality is not

triggered in the tuning process. In production scenarios, the

large value of this knob can lead to slow read as large minor

compaction ﬁles need to be checked for read queries. However,

this unreasonable setting may not affect the performance

of the tuned DBMS because only few minor compaction

operations are conducted during the short iteration. We design

and conduct systematic experiments to illustrate the problem

of incorrect knobs tuning.

We ﬁrst deﬁne two kinds of knobs to facilitate a better

understanding of the experiment:

• fake knob: To evaluate whether the parameter auto-

tuning tools randomly tune the knobs irrelevant to the

major compact trigger speciﬁes how many minor compactions are re-

quired to trigger a major compaction. When the value is 0, minor compactions

are disabled.

2024 IEEE 40th International Conference on Data Engineering (ICDE)

DOI 10.1109/ICDE60146.2024.00014

Authorized licensed use limited to: Zhejiang Tmall Technology Co.Ltd.. Downloaded on August 01,2024 at 03:39:58 UTC from IEEE Xplore. Restrictions apply.

BBAAD9C20180234D78A0072836F0B380F2B9B2091CE87BA0AFD98A34B1BC2BE43B4DB4389156AB0422B920089846DFEB92E921BAC1D0BB511BBFC261763E3FD6241121AD5E26F9F76406289767B74B475DC7EC897D66C41F589A219C63D0ACC8D7E62A964E3

0.5 0.6 0.7 0.8 0.9 1.0

0.5

0.6

0.7

0.8

0.9

1.0

Percentage of Real Knobs

Percentage of Tuned Real Knobs

Equal Probability Tuning

Optimal Tuning

OtterTune Real Tuning

(a) OtterTune

0.5 0.6 0.7 0.8 0.9 1.0

0.5

0.6

0.7

0.8

0.9

1.0

Percentage of Real Knobs

Percentage of Tuned Real Knobs

Equal Probability Tuning

Optimal Tuning

HUNTER Real Tuning

(b) HUNTER

Fig. 1: Fake Knobs Experiments on OtterTune [3] and

HUNTER [7]

overall performance changes, we introduce extra conﬁg-

uration knobs to the list of tuned knobs, referred to as

fake knobs. The tuning of these fake knobs is just to

modify their values which have no effect on the database

performance. There is no correlation between database

performance and the tuning of these fake knobs. We

use such a setup to simulate those parameters that are

currently not relevant to the database performance.

• real knob: Real knobs are conﬁguration knobs that

really exist in the database. The modiﬁcation of real

knobs can change the performance of tuned database

systems.

Experiments were conducted on OceanBase Community

Edition 3.1.1. For all experiments, we ﬁxed the same 20 real

knobs to tune. They are bound to affect on the overall perfor-

mance. The number of fake knobs in each experiment varied.

After each tuning experiment was ﬁnished, we calculated how

many fake knobs were tuned.

As shown in Figure 1, we use the percentage of real knobs in

all knobs (i.e., fake and 20 real knobs in the tuning list) as the

horizontal coordinate. The vertical coordinate is the percentage

of tuned real knobs in all tuned knobs. Experimental results are

plotted as a line passing ﬁlled rectangles. The optimal bound

is plotted as a line passing ﬁlled triangles, labeled as Optimal

Tuning. The optimal means only real knobs were tuned in

each tuning task. The line passing ﬁlled circles (labeled as

Equal Probability Tuning) demonstrates the case that the real

and fake knobs have an equal probability to be tuned. This

means fake and real knobs can not be discriminated according

to whether they have impact on the DBMS performance. For

example, the tuning list includes 30 knobs (i.e., 20 real knobs

and 10 fake knobs). After the tuning task is ﬁnished, 9 knobs

are tuned, where 6 real knobs and 3 fake knobs are tuned. The

area above Equal Probability Tuning speciﬁes more choice

of the real knob being tuned , while the area below Equal

Probability Tuning speciﬁes more choice of the fake knob

being tuned.

Figure 1 (a) presents the results on OtterTune [3]. The curve

of OtterTune is far from the Optimal Tuning and close to

Equal Probability Tuning most of the time. This indicates that

OtterTune has tuned the fake knobs and it can not ﬁnd that

the changes in fake knobs have nothing to do with the overall

performance. Figure 1 (b) shows the results on HUNTER [7].

It can be seen that HUNTER basically overlaps with Equal

Probability Tuning. It tunes all the knobs in the tuning list, no

matter whether they are real or fake knobs.

Although the user would not inject the fake knobs into

the tuning list in real applications, it may happen that some

important knobs are tuned to invalid or meaningless values.

These badly tuned knobs belong to function components that

are not in the running state during the tuning task. Meanwhile,

these badly tuned knobs have no chance to be ﬁxed as their

function components still do not work in the later tuning

iterations. The reason can be attributed to short iteration (e.g.,

3∼5 minutes) or the functionality not being triggered because

the triggering condition is not satisﬁed.

To resolve the above problems, we introduce OBTune,

a functionality-aware database tuning system designed for

OceanBase [10], which operates as an ofﬂine tuning system.

Our intuition is that database functionalities are usually co-

designed and jointly maintain their overall performance (see

the deﬁnition in Section III). Each functionality has its speciﬁc

knobs and related metrics to evaluate its health state. Multiple

functionalities can not work well together by only using the

overall performance (e.g., transaction throughput and latency)

to tune their knobs. Thus, OBTune adopts the concept of multi-

task learning [12] to break down the tuning task of a database

instance into functionality tuning and overall performance

tuning. The knob tuning of each function component is used

as the auxiliary task to help the main task of tuning the overall

database performance.

To the best of our knowledge, this is the ﬁrst paper to co-

tune the overall performance and various database functional-

ities. Our contributions are summarized as follows.

• We demonstrate the problem that black box based auto-

tuning methods tune knobs unrelated to the changes in

the overall database performance. This would introduce

potential risks of applying unreasonable parameters to the

online database instances.

• We propose a multi-task learning based auto-tuning

framework. It allows the DBA to tune the speciﬁc

database functionality during tuning the overall perfor-

mance. The knob auto-tuning of a functionality is re-

garded as an auxiliary learning task, and the main task is

to tune the overall performance.

• We implement the functionality-aware auto-tuning

method (i.e., OBTune) and evaluate it on the distributed

database system OceanBase and the centralized database

system PostgreSQL. Experimental results indicate that

OBTune can tune the knobs of triggered function com-

ponents effectively and accurately.

Section VII concludes the paper.

II.

RELATED WORK

The challenge of knob tuning in databases has prompted a

multitude of solutions. These solutions can be classiﬁed into

four categories based on the tuning methods employed: rule-

based tuning, search-based tuning, Gaussian process-based

tuning, and reinforcement learning-based tuning.

Authorized licensed use limited to: Zhejiang Tmall Technology Co.Ltd.. Downloaded on August 01,2024 at 03:39:58 UTC from IEEE Xplore. Restrictions apply.

Rule-based Tuning. MySQLTuner

and PGTune

harness

database commands to gather pertinent information for tuning,

then adjust corresponding knobs according to predeﬁned rules.

This approach, however, often restricts tuning to speciﬁc

subsets of knobs. For example, MySQLTuner and PGTune are

limited to tuning only 12 and 14 out of hundreds of MySQL

and PostgreSQL knobs, respectively.

Search-based Tuning. BestConﬁg [2] is a heuristic tuning

method designed to tune database knobs. It is executed in three

key steps. First, the Latin Hypercube Sampling (LHS) method

[13] is employed to sample within the knob space. Next, the

surrounding region of the best-performing sample is identiﬁed

as the new sampling space, where the LHS is conducted again.

Finally, if a sample within this new space shows improved

performance, the process returns to the second step; if not, it

reverts to the ﬁrst step. What sets BestConﬁg apart is its highly

randomized sampling process, which may result in signiﬁcant

variations in the ﬁnal outcomes for the same scenario.

Gaussian Process-based Tuning. iTuned [4] pioneered Gaus-

sian process-based modeling the relationship between knobs

and database performance. By leveraging the sampling data

gathered through the LHS method, it constructs a preliminary

tuning model and employs the expected improvement method

to balance exploration and exploitation. Conversely, OtterTune

[3] builds an initial Gaussian model using historical tuning

data, and applies Lasso to identify key knobs. It then utilizes an

incremental approach [14] to dynamically increase the number

of tuning knobs throughout the process. ResTune [8] also

employs a Gaussian process, but aims to minimize system

resource utilization while maintaining DBMS performance.

Taking into account the inﬂuence of various factors such

as operating system and Java virtual machine on database

performance, CGPTuner [15] employs a Contextual Gaussian

Process Bandit Optimization to tune knob of the entire IT stack

of the database for performance maximization. In contrast to

these ofﬂine tuning strategies, ONLINETUNE [1] offers an

online approach, using contextual Bayesian optimization for

adaptive database tuning in ever-changing cloud environments.

The widely utilization of Gaussian processes [16] in database

knob tuning is attributed to their theoretical capability to

balance exploration and exploitation.

Reinforcement Learning-based Tuning. Unlike OtterTune,

which segments the tuning process into various phases and re-

lays the optimal solution from one stage to the next, CDBTune

[5] introduces a more uniﬁed, end-to-end solution. Utilizing

reinforcement learning for database knob tuning, CDBTune

employs DDPG [17] as an agent, with the knob value as the

action, the database as the environment, the internal state of

the database as the state, and changes in database performance

as the reward. On the other hand, QTune [6] also incorporates

reinforcement learning but with a unique perspective, consid-

ering query statements during model training. It allows for

multi-level tuning, including query-level, workload-level, and

https://github.com/major/MySQLTuner-perl

https://pgtune.leopard.in.ua/

cluster-level adjustments. Typically, trained models struggle

to adapt to new tuning scenarios, necessitating a cold start.

Addressing this cold start problem, HUNTER [7] proposes a

solution that integrates genetic algorithms with reinforcement

learning. This method minimizes model training time by

independently performing various conﬁgurations on multiple

replicated database instances.

At present, there are some other studies related to database

knob tuning. LlamaTune [18] focuses on enhancing the sam-

pling efﬁciency of existing optimizers. Studies such as [19]

and [20] propose methods for extracting tuning rules from

text data. ReIM [21] adopts an empirically-driven white-box

method to tune the memory resource allocation in Spark [22]–

[24]. [25] utilizes the Plackett-Burman experimental design

approach [26] to rank database knobs by importance. [27]

concludes, based on relevant experiments, that database knob

tuning often requires adjusting only a few knobs. However,

it does not provide guidance on how to rapidly identify

these crucial knobs in a speciﬁc scenario. [28] performs a

detailed experimental comparison of relevant tuning tools

(e.g., OtterTune and CDBTune) in a real scenario. In a study

outlined in [29], the three key aspects of database knob tuning

(knob selection, conﬁguration optimization, and knowledge

transferring) are experimentally compared, with SHAP [30],

SMAC [31], and RGPE [32] identiﬁed as the best algorithms

for these aspects, respectively.

III. O

VERVIEW OF OBTUNE

Domain Knowledge. Shallow domain knowledge such as the

knob’s value range and how to split the value range for

efﬁcient sampling has been used in auto knob tuning [18],

[33]. In this work, OBTune is designed to integrate deep

domain knowledge. Our motivation is to help DBAs optionally

contribute domain knowledge regarding database functionali-

ties. While OBTune can achieve commendable tuning results

without this input, the inclusion of DBA insights enables even

better outcomes. Importantly, OBTune uses domain knowledge

to selectively tune knobs related to active functionalities,

enhancing tuning effectiveness and reducing potential risks.

This methodology caters to varying DBA expertise levels,

maximizing the use of available knowledge.

Knob Classiﬁcation. Knobs in OBTune are primarily classi-

ﬁed into two categories: functionality knobs and main knobs.

Speciﬁcally, if the adjustment of knob x directly affects the

performance of triggered functionality A, and the change

of knob x has no impact on the database performance if

functionality A is not triggered. Then x is classiﬁed to the

knobs of functionality A. For knobs that simultaneously affect

the performance of multiple functionalities, OBTune’s strategy

is to classify such knobs as main knobs. Furthermore, if a

tuning knob does not belong to any speciﬁc functionalities, it

also is classiﬁed to the main knob.

Deﬁnition 1. Direct metrics, that are used to directly reﬂect

the performance of a database functionality. For instance, the

direct metric for load balancing functionality in OceanBase

Authorized licensed use limited to: Zhejiang Tmall Technology Co.Ltd.. Downloaded on August 01,2024 at 03:39:58 UTC from IEEE Xplore. Restrictions apply.

of 13

免费下载

paper oceanbase

相关文档

评论