OpAdviserPlus

Setup Dev Container

Setup dev container using .devcontainer/devcontainer.json

Fix workspaceMount attribute and mounts attribute in .devcontainer/devcontainer.json to mount directories to SSDs (performances may degrade if code and /var/lib/mysql is in slow disk)

Prepare workload

rm -rf /var/lib/mysql/*
apt update
apt install -y mysql-server-5.7 \
    git  \
    default-jdk \
    ant \
    build-essential \
    openssh-client \
    cgroup-tools \
    libaio1 \
    libaio-dev \
    python3.8  \
    python3.8-dev  \
    python3.8-venv  \
    python3-pip  \
    python3-setuptools \
    autoconf \
    pkg-config \
    libtool \
    libmysqlclient-dev \
    automake \
    sudo

echo '[mysqld]
port=3308' | sudo tee -a /etc/mysql/my.cnf
service mysql start 
mysql -e"ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';"
mysql -ppassword -e"CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'password';"
mysql -ppassword -e"CREATE USER 'root'@'::1' IDENTIFIED BY 'password';"
mysql -ppassword -e"GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1';"
mysql -ppassword -e"GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1';"
mysql -ppassword -e"FLUSH PRIVILEGES;"
mysql -ppassword -e"set global max_connections=100000;"
mkdir /var/log/mysql/base
touch /var/log/mysql/base/mysql-slow.log

Setup Python Environment

update-alternatives --install /usr/bin/python python /usr/bin/python3.8 1
update-alternatives --install /usr/bin/python3 python3 /usr/bin/python3.8 1
python -m pip install --upgrade pip
pip install --user --upgrade setuptools
pip install --upgrade wheel
python -m pip install -r requirements.txt

Run Experiments

End-to-end Comparison (OpAdviser-w/o-Optimizer (Orange Line) in Figure 7 in the OpAdviser paper)

python scripts/optimize.py --config=scripts/sysbench_rw.ini
python scripts/optimize.py --config=scripts/sysbench_wo.ini
python scripts/optimize.py --config=scripts/sysbench_ro.ini
python scripts/optimize.py --config=scripts/twitter.ini

Find ground truth

cd /
rm -rf sysbench
git clone https://github.com/akopytov/sysbench.git && \
    cd sysbench && \
    git checkout ead2689ac6f61c5e7ba7c6e19198b86bd3a51d3c && \
    ./autogen.sh && \
    ./configure && \
    make && make install
mysql -ppassword -e"drop database sbrw;"
mysql -ppassword -e"create database sbrw;"
sysbench  \
    --db-driver=mysql  \
    --mysql-host=localhost  \
    --mysql-port=3308  \
    --mysql-user=root  \
    --mysql-password=password  \
    --table_size=800000  \
    --tables=300  \
    --events=0  \
    --threads=80  \
    --mysql-db=sbrw  \
    oltp_read_write  \
    prepare
cd ~/OpAdviserPrivate
export PYTHONPATH="."
python scripts/optimize.py --config=scripts/sysbench_rw.ini
python scripts/optimize.py --config=scripts/sysbench_rw_ground_truth.ini

cd /
rm -rf sysbench
git clone https://github.com/akopytov/sysbench.git && \
    cd sysbench && \
    git checkout ead2689ac6f61c5e7ba7c6e19198b86bd3a51d3c && \
    ./autogen.sh && \
    ./configure && \
    make && make install
mysql -ppassword -e"drop database sbwrite;"
mysql -ppassword -e"create database sbwrite;"
sysbench  \
    --db-driver=mysql  \
    --mysql-host=localhost  \
    --mysql-port=3308  \
    --mysql-user=root  \
    --mysql-password=password  \
    --table_size=800000  \
    --tables=300  \
    --events=0  \
    --threads=80  \
    --mysql-db=sbwrite  \
    oltp_write_only  \
    prepare
cd ~/OpAdviserPrivate
export PYTHONPATH="."
python scripts/optimize.py --config=scripts/sysbench_wo.ini
python scripts/optimize.py --config=scripts/sysbench_wo_ground_truth.ini

cd /
rm -rf sysbench
git clone https://github.com/akopytov/sysbench.git && \
    cd sysbench && \
    git checkout ead2689ac6f61c5e7ba7c6e19198b86bd3a51d3c && \
    ./autogen.sh && \
    ./configure && \
    make && make install
mysql -ppassword -e"drop database sbread;"
mysql -ppassword -e"create database sbread;"
sysbench  \
    --db-driver=mysql  \
    --mysql-host=localhost  \
    --mysql-port=3308  \
    --mysql-user=root  \
    --mysql-password=password  \
    --table_size=800000  \
    --tables=300  \
    --events=0  \
    --threads=80  \
    --mysql-db=sbread  \
    oltp_read_only  \
    prepare
cd ~/OpAdviserPrivate
export PYTHONPATH="."
python scripts/optimize.py --config=scripts/sysbench_ro.ini
python scripts/optimize.py --config=scripts/sysbench_ro_ground_truth.ini

cd /
rm -rf oltpbench && \
  git clone https://github.com/seokjeongeum/oltpbench.git

cd /oltpbench && \
    ant bootstrap && \
    ant resolve && \
    ant build && \
    chmod 777 /oltpbench/*
cd ~/OpAdviserPrivate
mysql -ppassword -e"drop database twitter;"
mysql -ppassword -e"create database twitter;"
/oltpbench/oltpbenchmark -b twitter -c /oltpbench/config/sample_twitter_config.xml  --create=true --load=true
export PYTHONPATH="."
python scripts/optimize.py --config=scripts/twitter.ini
python scripts/optimize.py --config=scripts/twitter_ground_truth.ini

cd /
rm -rf oltpbench && \
  git clone https://github.com/seokjeongeum/oltpbench.git

cd /oltpbench && \
    ant bootstrap && \
    ant resolve && \
    ant build && \
    chmod 777 /oltpbench/*
cd ~/OpAdviserPrivate
/oltpbench/oltpbenchmark -b tpcc -c /oltpbench/config/sample_tpcc_config.xml  --create=true --load=true
export PYTHONPATH="."
python scripts/optimize.py --config=scripts/tpcc.ini
python scripts/optimize.py --config=scripts/tpcc_ground_truth.ini

cd /
rm -rf oltpbench && \
  git clone https://github.com/seokjeongeum/oltpbench.git

cd /oltpbench && \
    ant bootstrap && \
    ant resolve && \
    ant build && \
    chmod 777 /oltpbench/*
cd ~/OpAdviserPrivate
mysql -ppassword -e"drop database ycsb;"
mysql -ppassword -e"create database ycsb;"
/oltpbench/oltpbenchmark -b ycsb -c /oltpbench/config/sample_ycsb_config.xml  --create=true --load=true
export PYTHONPATH="."
python scripts/optimize.py --config=scripts/ycsb.ini
python scripts/optimize.py --config=scripts/ycsb_ground_truth.ini

cd /
rm -rf oltpbench && \
  git clone https://github.com/seokjeongeum/oltpbench.git

cd /oltpbench && \
    ant bootstrap && \
    ant resolve && \
    ant build && \
    chmod 777 /oltpbench/*
cd ~/OpAdviserPrivate
mysql -ppassword -e"drop database wikipedia;"
mysql -ppassword -e"create database wikipedia;"
/oltpbench/oltpbenchmark -b wikipedia -c /oltpbench/config/sample_wikipedia_config.xml  --create=true --load=true
export PYTHONPATH="."
python scripts/optimize.py --config=scripts/wikipedia.ini
python scripts/optimize.py --config=scripts/wikipedia_ground_truth.ini

cd /
rm -rf oltpbench && \
  git clone https://github.com/seokjeongeum/oltpbench.git

cd /oltpbench && \
    ant bootstrap && \
    ant resolve && \
    ant build && \
    chmod 777 /oltpbench/*
cd ~/OpAdviserPrivate
mysql -ppassword -e"drop database tatp;"
mysql -ppassword -e"create database tatp;"
/oltpbench/oltpbenchmark -b tatp -c /oltpbench/config/sample_tatp_config.xml  --create=true --load=true
export PYTHONPATH="."
python scripts/optimize.py --config=scripts/tatp.ini
python scripts/optimize.py --config=scripts/tatp_ground_truth.ini

cd /
rm -rf oltpbench && \
  git clone https://github.com/seokjeongeum/oltpbench.git

cd /oltpbench && \
    ant bootstrap && \
    ant resolve && \
    ant build && \
    chmod 777 /oltpbench/*
cd ~/OpAdviserPrivate
mysql -ppassword -e"drop database voter;"
mysql -ppassword -e"create database voter;"
/oltpbench/oltpbenchmark -b voter -c /oltpbench/config/sample_voter_config.xml  --create=true --load=true
export PYTHONPATH="."
python scripts/optimize.py --config=scripts/voter.ini
python scripts/optimize.py --config=scripts/voter_ground_truth.ini

cd ~/OpAdviserPrivate
rm -rf queries-tpch-dbgen-mysql 
git clone https://github.com/seokjeongeum/queries-tpch-dbgen-mysql.git
cd queries-tpch-dbgen-mysql 
apt install unzip
unzip TPC-H\ V3.0.1.zip
cd dbgen 
make
./dbgen -s 10
mysql -ppassword -e"DROP DATABASE tpch;"
mysql -ppassword -e"CREATE DATABASE tpch;"
mysql -ppassword -e"
USE tpch;

CREATE TABLE NATION  ( N_NATIONKEY  INTEGER NOT NULL,
                            N_NAME       CHAR(25) NOT NULL,
                            N_REGIONKEY  INTEGER NOT NULL,
                            N_COMMENT    VARCHAR(152));

CREATE TABLE REGION  ( R_REGIONKEY  INTEGER NOT NULL,
                            R_NAME       CHAR(25) NOT NULL,
                            R_COMMENT    VARCHAR(152));

CREATE TABLE PART  ( P_PARTKEY     INTEGER NOT NULL,
                          P_NAME        VARCHAR(55) NOT NULL,
                          P_MFGR        CHAR(25) NOT NULL,
                          P_BRAND       CHAR(10) NOT NULL,
                          P_TYPE        VARCHAR(25) NOT NULL,
                          P_SIZE        INTEGER NOT NULL,
                          P_CONTAINER   CHAR(10) NOT NULL,
                          P_RETAILPRICE DECIMAL(15,2) NOT NULL,
                          P_COMMENT     VARCHAR(23) NOT NULL );

CREATE TABLE SUPPLIER ( S_SUPPKEY     INTEGER NOT NULL,
                             S_NAME        CHAR(25) NOT NULL,
                             S_ADDRESS     VARCHAR(40) NOT NULL,
                             S_NATIONKEY   INTEGER NOT NULL,
                             S_PHONE       CHAR(15) NOT NULL,
                             S_ACCTBAL     DECIMAL(15,2) NOT NULL,
                             S_COMMENT     VARCHAR(101) NOT NULL);

CREATE TABLE PARTSUPP ( PS_PARTKEY     INTEGER NOT NULL,
                             PS_SUPPKEY     INTEGER NOT NULL,
                             PS_AVAILQTY    INTEGER NOT NULL,
                             PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
                             PS_COMMENT     VARCHAR(199) NOT NULL );

CREATE TABLE CUSTOMER ( C_CUSTKEY     INTEGER NOT NULL,
                             C_NAME        VARCHAR(25) NOT NULL,
                             C_ADDRESS     VARCHAR(40) NOT NULL,
                             C_NATIONKEY   INTEGER NOT NULL,
                             C_PHONE       CHAR(15) NOT NULL,
                             C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
                             C_MKTSEGMENT  CHAR(10) NOT NULL,
                             C_COMMENT     VARCHAR(117) NOT NULL);

CREATE TABLE ORDERS  ( O_ORDERKEY       INTEGER NOT NULL,
                           O_CUSTKEY        INTEGER NOT NULL,
                           O_ORDERSTATUS    CHAR(1) NOT NULL,
                           O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
                           O_ORDERDATE      DATE NOT NULL,
                           O_ORDERPRIORITY  CHAR(15) NOT NULL,
                           O_CLERK          CHAR(15) NOT NULL,
                           O_SHIPPRIORITY   INTEGER NOT NULL,
                           O_COMMENT        VARCHAR(79) NOT NULL);

CREATE TABLE LINEITEM ( L_ORDERKEY    INTEGER NOT NULL,
                             L_PARTKEY     INTEGER NOT NULL,
                             L_SUPPKEY     INTEGER NOT NULL,
                             L_LINENUMBER  INTEGER NOT NULL,
                             L_QUANTITY    DECIMAL(15,2) NOT NULL,
                             L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
                             L_DISCOUNT    DECIMAL(15,2) NOT NULL,
                             L_TAX         DECIMAL(15,2) NOT NULL,
                             L_RETURNFLAG  CHAR(1) NOT NULL,
                             L_LINESTATUS  CHAR(1) NOT NULL,
                             L_SHIPDATE    DATE NOT NULL,
                             L_COMMITDATE  DATE NOT NULL,
                             L_RECEIPTDATE DATE NOT NULL,
                             L_SHIPINSTRUCT CHAR(25) NOT NULL,
                             L_SHIPMODE     CHAR(10) NOT NULL,
                             L_COMMENT      VARCHAR(44) NOT NULL);

LOAD DATA LOCAL INFILE 'customer.tbl' INTO TABLE CUSTOMER FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE 'orders.tbl' INTO TABLE ORDERS FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE 'lineitem.tbl' INTO TABLE LINEITEM FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE 'nation.tbl' INTO TABLE NATION FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE 'partsupp.tbl' INTO TABLE PARTSUPP FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE 'part.tbl' INTO TABLE PART FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE 'region.tbl' INTO TABLE REGION FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE 'supplier.tbl' INTO TABLE SUPPLIER FIELDS TERMINATED BY '|';

ALTER TABLE REGION
ADD PRIMARY KEY (R_REGIONKEY);
ALTER TABLE NATION
ADD PRIMARY KEY (N_NATIONKEY);
ALTER TABLE NATION
ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references REGION(R_REGIONKEY);
ALTER TABLE PART
ADD PRIMARY KEY (P_PARTKEY);
ALTER TABLE SUPPLIER
ADD PRIMARY KEY (S_SUPPKEY);
ALTER TABLE SUPPLIER
ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references NATION(N_NATIONKEY);
ALTER TABLE PARTSUPP
ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);
ALTER TABLE CUSTOMER
ADD PRIMARY KEY (C_CUSTKEY);
ALTER TABLE CUSTOMER
ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references NATION(N_NATIONKEY);
ALTER TABLE LINEITEM
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);
ALTER TABLE PARTSUPP
ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references SUPPLIER(S_SUPPKEY);
ALTER TABLE PARTSUPP
ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references PART(P_PARTKEY);
ALTER TABLE ORDERS
ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references CUSTOMER(C_CUSTKEY);
ALTER TABLE LINEITEM
ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY)  references ORDERS(O_ORDERKEY);
ALTER TABLE LINEITEM
ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) references PARTSUPP(PS_PARTKEY, PS_SUPPKEY);
"
cd ~/OpAdviserPrivate
export PYTHONPATH="."
python scripts/optimize.py --config=scripts/tpch.ini
python scripts/optimize.py --config=scripts/tpch_ground_truth.ini

chmod +x ./job.sh
./job.sh
export PYTHONPATH="."
python scripts/optimize.py --config=scripts/job.ini
python scripts/optimize.py --config=scripts/job_ground_truth.ini

Name		Name	Last commit message	Last commit date
Latest commit History 841 Commits
.devcontainer		.devcontainer
OpAdviserPlus		OpAdviserPlus
autotune		autotune
documents		documents
join-order-benchmark		join-order-benchmark
queries-tpch-dbgen-mysql		queries-tpch-dbgen-mysql
scripts		scripts
sysbench		sysbench
test		test
tuning_benchmark/data		tuning_benchmark/data
.env		.env
.gitignore		.gitignore
.gitmodules		.gitmodules
102.sh		102.sh
108.sh		108.sh
109.sh		109.sh
110.sh		110.sh
111.sh		111.sh
114.sh		114.sh
115.sh		115.sh
116.sh		116.sh
117.sh		117.sh
121.sh		121.sh
123.sh		123.sh
124.sh		124.sh
125.sh		125.sh
126.sh		126.sh
127.sh		127.sh
128 copy 2.sh		128 copy 2.sh
128 copy.sh		128 copy.sh
128.sh		128.sh
129.sh		129.sh
130 copy 2.sh		130 copy 2.sh
130 copy 3.sh		130 copy 3.sh
130 copy 4.sh		130 copy 4.sh
130 copy.sh		130 copy.sh
130.sh		130.sh
133.sh		133.sh
136.sh		136.sh
137.sh		137.sh
139.sh		139.sh
140.sh		140.sh
LICENSE		LICENSE
MANIFEST.in		MANIFEST.in
README.md		README.md
_job.sh		_job.sh
_oltpbench.sh		_oltpbench.sh
_sysbench.sh		_sysbench.sh
_tpch.sh		_tpch.sh
all.record		all.record
cluster.sh		cluster.sh
docker.sh		docker.sh
job.sh		job.sh
mysql.cnf		mysql.cnf
oltpbench_tatp_smac		oltpbench_tatp_smac
oltpbench_tpcc_smac		oltpbench_tpcc_smac
oltpbench_ycsb_smac		oltpbench_ycsb_smac
requirements.txt		requirements.txt
sbread_smac		sbread_smac
sbrw.csv		sbrw.csv
sbrw_smac		sbrw_smac
sbwrite_smac		sbwrite_smac
setup.py		setup.py
space.record		space.record
technical_report.pdf		technical_report.pdf
test.txt		test.txt
tpch.sh		tpch.sh
twitter_smac		twitter_smac

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

OpAdviserPlus

Setup Dev Container

Prepare workload

Setup Python Environment

Run Experiments

Find ground truth

About

Releases

Packages

Languages

License

LeeJYg/OpAdviserPrivate

Folders and files

Latest commit

History

Repository files navigation

OpAdviserPlus

Setup Dev Container

Prepare workload

Setup Python Environment

Run Experiments

Find ground truth

About

Resources

License

Stars

Watchers

Forks

Releases

Packages 0

Languages

Packages