Eraser: Eliminating Performance Regression on Learned Query Optimizer

This repository contains the source code for the paper titled "Eraser: Eliminating Performance Regression on Learned Query Optimizer." Eraser is a plugin deployed on top of learned query optimizers like Lero, HyperQo, and PerfGuard. Its purpose is to assist these algorithms in eliminating performance regression and achieving comparable performance to PostgreSQL when these algorithms perform worse, while having minimal impact when these algorithms perform well.

Here, we provide a comprehensive and detailed description of two different approaches to reproduce the Eraser. The first approach allows for a quick reproduction of Eraser. We provide all the trained models and raw data used in our experiments. This allows anyone to reproduce the most important experiment results using a few simple commands. The second approach is a comprehensive guide that outlines the steps to reproduce Eraser from scratch. This includes creating the database, importing data, training Lero, HyperQo, and PerfGuard models, and collecting data for Eraser.

Reproducing the Experiment Result Quickly

We have provided the necessary resources to quickly reproduce Eraser, including all trained models of learned query optimizers and the raw data used in our experiments. By using a few simple commands, anyone can reproduce the experimental results.

When reproducing the results, we will build an Eraser model from scratch based on the prediction accuracy of ML models of learned query optimizers. We will then test the performance of Eraser using the collected candidate plans from the plan exploration algorithms of these learned query optimizers.

All experiments are finished on Linux, so you should also run codes on a Linux system.

Download Data

First, you need to download the source code of Eraser by executing the following command:

git clone git@github.com:duoyw/Eraser.git

Then, you need to download all data including trained models, candidate plans, SQLs, etc. Due to the space limitation of Github, we split all data into four compressed sub-files. You can download these files using the following commands after switching to the project root

# move to the root of the project
cd ./Eraser
# download all compressed files from the GitHub release
wget https://github.com/duoyw/Eraser/releases/download/v1.1/Eraser_Data_aa
wget https://github.com/duoyw/Eraser/releases/download/v1.2/Eraser_Data_ab
wget https://github.com/duoyw/Eraser/releases/download/v1.3/Eraser_Data_ac
wget https://github.com/duoyw/Eraser/releases/download/v1.4/Eraser_Data_ad

After downloading all compressed data, you need to merge all sub-files into a complete file using the following commands.

# on the root of the project
rm -rf Data/
cat Eraser_Data* > Data.tar
# The command may need to be executed twice
tar -xvf Data.tar
tar -xvf Data.tar 

The complete project is structured as follows:

- Eraser: the root of our Eraser project
    - Data: storing all trained models and raw training and test data.
    - Hyperqo: the source code of HyperQO baseline
    - PerfGuard: the source code of PerfGuard baseline
    - RegressionFramework: the source code of our Eraser
    - Spark: the source code of Lero on Spark
    - test_script: the source code of Lero baseline baseline
    - hyperqo_test: it provides a few commands to reproduce the experiment results of HyperQO quickly.
    - lero_test: it provides a few commands to reproduce the experiment results of Lero quickly.
    - perfguard_test: it provides a few commands to reproduce the experiment results of PerfGuard quickly.
    - Others: some auxiliary files or folders, such as README

Configurate Environment

To run Eraser, you need to set up the corresponding Python environment.

First, download Python 3.8 from https://www.python.org/downloads/ or download Conda from https://docs.conda.io/en/latest/ to create an independent Python 3.8 environment.

Next, switch to the corresponding 'pip' and install the required libraries used by Eraser using the following command:

# switching to the root of project
pip install -r requirements.txt

Once you have completed these steps, you can quickly reproduce the experimental results using the provided data.

Reproduce PostgreSQL Results

To minimize the reproduction overhead, all codes are executed on CPUs. Each experiment's results should be collected within a few minutes, which is still considered fast.

To reproduce the experiment results shown in Figure 5 of the paper, you can use the following commands.

# cd to the root of the project

# lero 
python -m unittest lero_test.LeroTest.test_static_all_job
python -m unittest lero_test.LeroTest.test_static_all_stats
python -m unittest lero_test.LeroTest.test_static_all_tpch

# PerfGuard 
python -m unittest perfguard_test.PerfguardTest.test_static_all_job
python -m unittest perfguard_test.PerfguardTest.test_static_all_stats
python -m unittest perfguard_test.PerfguardTest.test_static_all_tpch

# HyperQO, it is needed to set config manually, please see the description below.
python -m unittest hyperqo_test.HyperqoTest.test_static_all_job
python -m unittest hyperqo_test.HyperqoTest.test_static_all_stats
python -m unittest hyperqo_test.HyperqoTest.test_static_all_tpch

The above code utilizes the provided ML models from different learned query optimizers, as well as the training data, to build an Eraser system from scratch. Then, we test the performance of the Eraser on each test query. Each algorithm will be tested in each benchmark four times using models trained with 25%, 50%, 75%, and 100% of the training data.

All results will be saved in the "RegressionFramework/fig" directory. You should obtain consistent results with the paper for the Lero and PerfGuard algorithms. However, due to the high randomness of the HyperQO algorithm, the results may differ from those in the paper. Nevertheless, Eraser typically eliminates most of the performance regression.

For example, the figure "RegressionFramework/fig/lero_job_test2" shows the performance of the Lero algorithm trained with 50% of the data on the IMDB (JOB) benchmark.

The Figure shows the performance of Lero without Eraser, Lero with Eraser, and PostgreSQL.

The suffixes "1", "2", "3", and "4" of these files indicate that the model was trained on 25%, 50%, 75%, and 100% of the data, respectively. Similarly, "lero_stats_test3" indicates the performance of the Lero algorithm trained with 75% of the data on the STATS benchmark.

For HyperQO, due to limitations in the source code provided by the authors, we were unable to show all the results using a few simple commands. You need to change the variable "self.database=imdb/stats/tpch" in the "Hyperqo/ImportantConfig.py" file based on the selected benchmark. For example, when you prepare to execute the "test_static_all_job" command, you must set "self.database" to "imdb".

Reproducing the experiment results shown in Figure 7 of the paper is easy. When executing the code to collect the result of Figure 5, Eraser will save the corresponding result of Figure 7 in the "RegressionFramework/fig/" directory. For example, the figure "lero_job_regression_per_query_2" represents the experiment result of the Lero algorithm trained with 50% of the data in the IMDB (job) benchmark. Please note that due to slight randomness, the experiment result may slightly differ from the paper, but Eraser consistently eliminates most of the performance regression.

To reproduce the experiment results shown in Figure 8 of the paper, you can use the following commands. Please note that this may consume a significant amount of time and memory due to the for-loop process without releasing memory. However, this can be optimized by clearing models that are no longer needed.

# lero 
python -m unittest lero_test.LeroTest.test_dynamic_job
python -m unittest lero_test.LeroTest.test_dynamic_tpch

# PerfGuard 
python -m unittest perfguard_test.PerfguardTest.test_dynamic_job
python -m unittest perfguard_test.PerfguardTest.test_dynamic_tpch

# HyperQO, it is needed to set config manually, please see the description below.
python -m unittest hyperqo_test.HyperqoTest.test_dynamic_job
python -m unittest hyperqo_test.HyperqoTest.test_dynamic_tpch

The above code will build Eraser from scratch based on the provided models and data. All results are also saved in the " RegressionFramework/fig" directory. For example, the "dynamic_tpch_lero" file shows the dynamic performance of the Lero algorithm in the TPC-H benchmark. Similarly, you should change the variable of HyperQO algorithms if you want to execute it.

Reproducing the Spark Results

To reproduce the experiment results of the paper for Spark, we also provide trained Lero models and candidate plans for a quick reproduction. It is important to note that we retrained the Lero models due to the loss of raw models. As a result, there may be slight differences from the results presented in the paper. However, the Eraser is still effective in eliminating most of the performance regression.

To reproduce Figure 15, you can use the following commands. First, navigate to the Spark root path. Assuming your path is at the root of the project, execute the command:

cd ./Spark

Then, run the following commands to reproduce Figure 15(a):

python -m unittest spark_lero_test.SparkLeroTest.test_static_all_tpcds

Similarly, run the following commands to reproduce Figure 15(b):

# for online mode
python -m unittest spark_lero_test.SparkLeroTest.test_dynamic_tpcds
`