【阿里云】Eraser- Eliminating Performance Regression on Learned Query Optimizer.pdf

胖酒精灯

106

13页

0次

2025-04-17

免费下载

Eraser: Eliminating Performance Regression

on Learned ery Optimizer

Lianggui Weng

Alibaba Group

Hangzhou, China

lianggui.wlg@alibaba-inc.com

Rong Zhu

#,∗

Alibaba Group

Hangzhou, China

red.zr@alibaba-inc.com

Di Wu

Alibaba Group, HUST

Hangzhou, China

woodybryant.wd@alibaba-inc.com

Bolin Ding

∗

Alibaba Group

Hangzhou, China

bolin.ding@alibaba-inc.com

Bolong Zheng

∗

HUST

Wuhan, China

bolongzheng@hust.edu.cn

Jingren Zhou

∗

Alibaba Group

Hangzhou, China

jingren.zhou@alibaba-inc.com

ABSTRACT

Ecient query optimization is crucial for database management

systems. Recently, machine learning models have been applied in

query optimizers to generate better plans, but the unpredictable

performance regressions prevent them from being truly applicable.

To be more specic, while a learned query optimizer commonly

outperforms the traditional query optimizer on average for a work-

load of queries, its performance regression seems inevitable for

some queries due to model under-tting and diculty in general-

ization. In this paper, we propose a system called

Eraser

to resolve

this problem.

Eraser

aims at eliminating performance regressions

while still attaining considerable overall performance improvement.

To this end,

Eraser

applies a two-stage strategy to estimate the

model accuracy for each candidate plan, and helps the learned

query optimizer select more reliable plans. The rst stage serves

as a coarse-grained lter that removes all highly risky plans with

feature values that are seen for the rst time. The second stage

clusters plans in a more ne-grained manner and evaluates each

cluster according to the prediction quality of learned query optimiz-

ers for selecting the nal execution plan.

Eraser

can be deployed

as a plugin on top of any learned query optimizer. We implement

Eraser

and demonstrate its superiority on PostgreSQL and Spark.

In our experiments,

Eraser

eliminates most of the regressions while

bringing very little negative impact on the overall performance of

learned query optimizers, no matter whether they perform better

or worse than the traditional query optimizer. Meanwhile, it is

adaptive to dynamic settings and generally applicable to dierent

database systems.

PVLDB Reference Format:

Lianggui Weng, Rong Zhu, Di Wu, Bolin Ding, Bolong Zheng, Jingren

Zhou. Eraser: Eliminating Performance Regression on Learned Query

Optimizer. PVLDB, 17(5): 926 - 938, 2024.

doi:10.14778/3641204.3641205

# Equal Contribution. * Corresponding authors.

This work is licensed under the Creative Commons BY-NC-ND 4.0 International

License. Visit https://creativecommons.org/licenses/by-nc-nd/4.0/ to view a copy of

this license. For any use beyond those covered by this license, obtain permission by

emailing info@vldb.org. Copyright is held by the owner/author(s). Publication rights

licensed to the VLDB Endowment.

Proceedings of the VLDB Endowment, Vol. 17, No. 5 ISSN 2150-8097.

doi:10.14778/3641204.3641205

PVLDB Artifact Availability:

The source code, data, and/or other artifacts have been made available at

https://github.com/duoyw/Eraser.

1 INTRODUCTION

Query optimization plays a crucial role in database management

systems. The goal of query optimizer is to nd an optimal execution

plan that minimizes a user-specied cost metric, e.g., the query exe-

cution time or resource usage. Traditional query optimizers rely on

a cost-based model that estimates the cost of execution plans based

on simple statistics and experience-driven rules [

]. However, the

estimated costs are often shown to have large errors [

due to unrealistic independence assumptions or over-simplied

models, which heavily degrade the generated plan quality.

Recently, there has been an active line of works using learned

optimizers to improve query optimization [

These optimizers apply machine learning (ML) models to learn from

data and/or queries to generate better execution plans. The pipeline

of a learned query optimizer often includes two main steps. First,

it generates a number of candidate plans

𝑄

by some exploration

strategy. Second, all candidate plans

𝑃 ∈ P

𝑄

are fed into an ML

model to predict its cost

𝐶 (𝑃 )

. The plan

𝑃

𝑟

∈ P

𝑄

that minimizes

the predicted cost is selected to execute.

Challenges of Learned Query Optimizer.

Despite the promis-

ing results of learned query optimizers have been shown in the liter-

ature [

], they still suer inevitable drawbacks that prevent

them from being truly applicable. Specically, the executed plans

selected by the learned query optimizer may be worse, sometimes

even seriously worse, than the traditional native query optimizer.

This phenomenon is called performance regression, and has been

observed in all learned query optimizers [5, 24, 25, 27, 41, 42, 45].

The learned models can not accurately predict the exact cost of

some data due to numerous reasons, such as the inherent diculty

of the learning problem [

], the low generalization ability of

the prediction model on new data, the under-tting on training data

due to insucient training data, loss of features, noisy labels and

inappropriate model training methods. Due to such intrinsic draw-

backs, the performance regressions of learned query optimizers

seem inevitable, no matter how we improve the models in learned

query optimizers. This is very harmful, or even unacceptable, to

database systems which require high stability.

926

In the literature, there exists very little work on eliminating the

performance regression, especially on learned query optimizer [

[

] and [

] propose methods to enhance the robustness in dy-

namic settings by updating models in the proper time. They are

post-processing methods but do not detect and eliminate regression

before query execution. [

] tries to reduce the regression using

the ensemble methods [

], but it is time-consuming

and often falsely lters some truly good plans. As far as we know,

until now, this problem has not been well solved.

Our Contributions.

In this paper, we try to tackle this problem in

a novel way. Notably, the benets and risks of the learned models

always come together. Our goal is not to eliminate any possible

regression (which degenerates to the traditional query optimizer),

but to eliminate it to a low level while still attaining considerable

performance improvement. To this end, we design a system called

Eraser

, which can be deployed as an external plugin on top of any

existing learned query optimizer.

Eraser

can be tuned to eliminate

its performance regression while bringing minimal impact on its

performance benet.

The key to eliminate the performance regression is to identify

whether the predicted cost is accurate for each candidate plan. Based

on this, we can lter out all highly risky candidate plans but reserve

those with high prediction accuracy for plan selection. However,

learning the exact prediction accuracy is very challenging, which

is as dicult as learning the accurate cost of each plan [

26, 35]. In Eraser, we try to simplify the learning tasks while still

preserving enough knowledge for plan identication.

Specically,

Eraser

adopts a two-stage strategy for plan identi-

cation. The rst stage serves as a coarse-grained lter that qualita-

tively removes all highly risky plans. We observe that the prediction

models are very likely to perform worse on plans with feature values

not occurring in the training data, due to their low generalization

ability. These plans are called unexpected plans. To detect how the

model behaves w.r.t. each feature value, we design an unexpecte d

plan explorer to divide the unexpected plan space into a number of

subspaces, each with one or more unseen feature values. Then, we

generate plans in a small number of representative subspaces. Based

on the model evaluation results on these plans, we can classify all

subspaces into precise and imprecise. All candidate plans fall into

the imprecise subspace would be ltered.

In the second stage, we learn a segment model to process the

remaining plans in a more ne-grained manner. We observe that

the performance of the prediction model is highly skewed, since it is

under-tting for some plans. To this end, the segment model groups

plans into a number of clusters and associates each cluster with a

reliability interval reecting the quality of the estimation results.

Based on the reliability interval, we design a plan selection method

to balance the risk of regressions and the loss of benets. Both the

unexpected plan explorer and the segment model are lightweight.

Through comprehensive evaluations, we nd that when the

learned query optimizer performs worse, i.e., even 1

to 2

than the traditional query optimizer,

Eraser

can help to improve

its performance to be comparable with the traditional query op-

timizer. When the learned query optimizer performs better than

the traditional query optimizer,

Eraser

makes little inuence on

its performance.

Eraser

is adaptive to balance regression risks

and improvement impacts to attain the best overall performance

in both static and dynamic settings. Meanwhile,

Eraser

exhibits

good generality to dierent underlying learned query optimizers

in [

] and dierent DBMSes, i.e., PostgreSQL and Spark [

Our main contributions are summarized as follows:

1) We propose a general framework subsuming existing learned

query optimizers. Based on this, we rigorously dene the perfor-

mance regression elimination problem on the learned query opti-

mizer.

2) We design

Eraser

, a system that can be deployed on top of any

learned query optimizer to eliminate its performance regression

while preserving its performance benet.

3) We conduct extensive experiments to evaluate the perfor-

mance of Eraser in dierent settings.

2 PRELIMINARIES

In the traditional query optimizer, such as the one in PostgreSQL,

for any input SQL query

𝑄

, all candidate plans are often enumerated

using dynamic programming. Then, a basic cost model is applied for

plan selection. It relies on estimated cardinality, which is often gen-

erated by simple statistical methods such as histogram or sampling,

and experience-driven rules to predict the cost of each candidate

plan. Let

𝐶 (𝑃 )

and

𝐶 (𝑃 )

denote the exact and estimated cost of

query plan

𝑃

, respectively. The cost

𝐶 (𝑃 )

is a user-specied metric,

e.g., execution time or I/O throughput, regarding the eciency of

executing

𝑃

. Finally, the plan

𝑃

𝑏

with the minimum estimated cost

is returned for execution.

Recently, a number of learned query optimizers [

] are proposed to provide instance-level query optimization.

Their procedures can be generalized into a unied framework with

two main steps. For the input query

𝑄

, a learned query optimizer

rst generates a set of candidate plans

𝑄

= {𝑃

, 𝑃

, . . . , 𝑃

𝑘

}

using

some plan exploration strategies. Then, a learned risk model

𝑀

𝑟

i.e., a complex ML-based model, is applied for plan selection.

𝑀

𝑟

can predict the goodness of each plan in

𝑄

in terms of

𝐶 (𝑃 )

. The

best plan

𝑃

𝑟

∈ P

𝑄

minimizing

𝐶 (𝑃 )

is selected for execution. We

note that dierent learned query optimizers, including Neo [

Balsa [

], Bao [

], HyperQO [

], Lero [

], PerfGuard [

] and

some other works [

], apply dierent plan ex-

ploration strategies and risk models, but they can all be subsumed

under this framework. Due to space limits, we defer the details to

Appendix A in the full version [40].

Problem Statement.

The plan

𝑃

𝑟

selected by the above learned

query optimizer is often shown to have a better performance than

𝑃

𝑏

. However, it may suer a heavy performance regression on some

queries due to numerous reasons: 1) the candidate set

𝑄

does not

contain plans better than

𝑃

𝑏

; 2) the risk model can not generalize

well on new data/workload, especially in dynamic settings; and

3) the risk model is under-tting on the training data owing to

loss of features, noisy labels, insucient training data, bad hyper-

parameters or inappropriate training optimizers.

Let

𝑄

be the distribution of all SQL queries occurring for a data-

base. Let

be a workload where each query

𝑄 ∈ Q

occurs with the

probability

𝑄

. Formally, a learned query optimizer learned

opt in

our framework generates an execution plan 𝑃

𝑟

for a query 𝑄 ∈ Q

𝑃

𝑟

← learned_opt(P

𝑄

, 𝑀

𝑟

)

927

of 13

免费下载

vldb 学习型索引阿里云

关注

评论