数据库优化-算法实现-Automatic Database Management System Tuning Through Large-scale Machine Learning

基本信息

论文链接
代码地址
Projects OtterTune
ottertune安装实验
python3 manage.py runserver 0.0.0.0:8000 run server
http://127.0.0.1:8000 visit website
以下问题出现的原因大多是兼容性问题，要确保与代码中requerments.txt中包版本一致。
In this document ,I should install java environment, but I failed with the problem Oracle Java downloads now require logging in to an Oracle account to download Java updates, like the latest Oracle Java 8u211 / Java SE 8u212.
I can’t install the oracle JDK, so I decide to install open jdk, seeing the install document
after I installed mysql , I don’t know how to login in it . Then I find a document teaching us Ubuntu18.04安装mysql并登录
and I set my password as 123456.but I still can’t login . Then I find another document ubuntu18.04中解决MySQL的access denied for user root @localhost的方法 I solved my problem by function 2.
sudo gedit /etc/mysql/mysql.conf.d/mysqld.cnf

5j2foNssuRVrcU54
CREATE USER ‘ottertune’@‘localhost’ IDENTIFIED BY ‘ottertune’;
The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
MySQL数据库–命令行操作
MySQL 教程
Ubuntu下命令行操作Postgresql
I change the configuration of website/settings/credentials.py, I should change the secret-key ,but I don’t know how .So I didn’t do it at now!

when I ran the commend python3 manage.py makemigrations website, I got the error of ModuleNotFoundError: No module named 'django' cause I didn’t install django;
I can run the commandpip install -r requirements.txt to install all dependencies.
but before that, I should activate my virtualenv(my command is source /home/lilan/env/bin/activate).

after that , I run python3 manage.py makemigrations website,I meet the from django.utils import six ImportError: cannot import name 'six'.the reason is that the django version is not property. I changeed my django version to pip install django==2.2.6.Then I solve the problem.
but I meet a problem of django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module. Did you install mysqlclient?
I can run the command pip install mysqlclientto solve this.
TypeError: init() missing 1 required positional argument: ‘on_delete’
~/venv/lib/python3.6/site-packages/django/utils

~/code/ottertune/server/website

【机器学习化DBMS】——ottertune客户端部署安装
pg_ctl restart failure:pg_ctl: command not found

客户端主要作用是收集DBMS中的参数信息，然后上传到服务端用于机器学习。
run oltpbench
do not just use the command,or you will meet a lot of problems

ant
./oltpbenchmark -b tpcc -c config/tpcc_config_postgres.xml --create=true --load=true

run client using command fab run_loops:max_iter=10 in direction /ottertune/client/driver

论文解读

项目架构

处理流程

1.使用度量来匹配模式 Workload Mapping

The first step in the tuning system is to discover a model that best
represents the distinguishing aspects of the target workload so that
it can identify which previously seen workloads in the repository
are similar to it. This enables OtterTune to leverage the information
that it has collected from previous tuning sessions to help guide the
search for a good knob configuration for the new application.
通过比较相似性从之前的仓库中找到一个与当前模式最匹配的

计算目标工作负荷测量向量与矩阵中每个工作负荷对应向量之间的欧几里德距离.
该算法选择得分最低的工作负荷作为与该观察期目标工作量最相似的工作负荷。

use the DBMS’s internal runtime metrics to characterize how a workload behaves.
使用DBMS的运行指标来做匹配

做匹配的方法：

1. 选择出所有的指标，并保存成key-value
2. 移除多余的指标（多余的原因可能是两个指标高度相关）
   移除指标的方法：首先降维、然后用k-means聚合

选择最具影响力的knobs

（这是什么？不太理解）

OtterTune next identififies which knobs have the strongest impact on the DBA’s target objective function.

选择knobs方法：

1. 使用Lasso（线性回归）方法/ordinary least squares
2. 使用动态增加knobs的方法 Incremental Knob Selection

2.Configuration Recommendation

1. OtterTune uses Gaussian Process (GP) regression to recommend confifigurations
2. It does this by either (1) searching an unknown region in its GP (i.e., workloads for which it has little to no data for), or (2) selecting a configuration that is near the best configuration in its GP.

实现过程

1. git下载代码
2. 安装Pytorch
   Python笔记–Torch环境的搭建

run the project

make config of server and run server
get the page of ottertune by visiting http://127.0.0.1:8000

at first ,I should sign up, but I can’t go to the sign-up page from login page. to solve this,
I go to the page by visiting http://127.0.0.1:8000/signup/

then I create project and session , with the session’s creation ,upload_code will be created.it will be used in client/driver/driver_config upload_code

after finished that ,I still meet a question which postgres 9.6 has been duplicated, so I use postgres 13.0 , but there isn’t postgres’ website_knobcatalog, so I change table
website_dbmscatalog use command update website_dbmscatalog set version=13.0 where id=1,use postgres 9.6’s config to replace postgres 13.0;

but their config has a little different ,so I meet the problem of Knob global.password_encryption boolean value not valid! Boolean values should be one of: valid true values: on true yes 1 ; valid false values: off false no 0 , but the actual value is: md5 so i change like the picture
after that , I finished the project.