如何预测SQL语句查询时间？

Gauss松鼠会 2022-05-13

280

1.概述

Predictor是基于机器学习且具有在线学习能力的查询时间预测工具。通过不断学习数据库内收集的历史执行信息，实现计划的执行时间预测功能。

本特性需要拉起python进程AIEngine，用于模型的训练和推理。

2.环境部署

前提条件

需要保证openGauss数据库处于正常状态，用户通过身份验证成功登录openGauss；用户执行的SQL语法正确无报错，且不会导致数据库异常等；历史性能数据窗口内openGauss并发量稳定，表结构、表数量不变，数据量无突变，涉及查询性能的guc参数不变；进行预测时，需要保证模型已训练并收敛；AiEngine运行环境稳定。

请求样例

AiEngine进程与内核进程使用https发送请求进行通信，请求样例如下：

curl -X POST -d '{"modelName":"modelname"}' -H 'Content-Type: application/json' 'https://IP-address:port/request-API'

表 1 ： AI-Engine对外接口

Request-API	功能
/check	检查模型是否被正常拉起
/configure	设置模型参数
/train	模型训练
/track_process	查看模型训练日志
/setup	加载历史模型
/predict	模型预测

证书生成

使用此功能前需使用openssl工具生成通信双方认证所需的证书，保证通信安全。

搭建证书生成环境，证书文件保存路径为$GAUSSHOME/CA。
–拷贝证书生成脚本及相关文件
```
cp path_to_predictor/install/ssl.sh $GAUSSHOME/
cp path_to_predictor/install/ca_ext.txt $GAUSSHOME/
```
–copy 配置文件openssl.cnf到$GAUSSHOME路径下
```
 cp $GAUSSHOME/share/om/openssl.cnf  $GAUSSHOME/
```
–修改openssl.conf配置参数
```
dir  = $GAUSSHOME/CA/demoCA
default_md      = sha256
```
–至此证书生成环境准备完成

生成证书及密钥

cd $GAUSSHOME
sh ssl.sh

–根据提示设置密码，假如为Test@123：

–密码要求至少3种不同类型字符，长度至少为8位

Please enter your password:

–根据提示输入选项：

Certificate Details:
        Serial Number: 1 (0x1)
        Validity
            Not Before: May 15 08:32:44 2020 GMT
            Not After : May 15 08:32:44 2021 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = SZ
            organizationName          = HW
            organizationalUnitName    = GS
            commonName                = CA
        X509v3 extensions:
            X509v3 Basic Constraints:
                CA:TRUE
Certificate is to be certified until May 15 08:32:44 2021 GMT (365 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y

–输入拉起AIEngine的IP地址，如IP为127.0.0.1：

Please enter your aiEngine IP: 127.0.0.1

–根据提示输入选项：

Certificate Details:
        Serial Number: 2 (0x2)
        Validity
            Not Before: May 15 08:38:07 2020 GMT
            Not After : May 13 08:38:07 2030 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = SZ
            organizationName          = HW
            organizationalUnitName    = GS
            commonName                = 127.0.0.1
        X509v3 extensions:
            X509v3 Basic Constraints:
                CA:FALSE
Certificate is to be certified until May 13 08:38:07 2030 GMT (3650 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y

–输入启动openGauss IP地址，如IP为127.0.0.1：

Please enter your gaussdb IP: 127.0.0.1

–根据提示输入选项：

Certificate Details:
        Serial Number: 3 (0x3)
        Validity
            Not Before: May 15 08:41:46 2020 GMT
            Not After : May 13 08:41:46 2030 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = SZ
            organizationName          = HW
            organizationalUnitName    = GS
            commonName                = 127.0.0.1
        X509v3 extensions:
            X509v3 Basic Constraints:
                CA:FALSE
Certificate is to be certified until May 13 08:41:46 2030 GMT (3650 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y

–至此，相关证书及密钥已生成，（$GAUSSHOME/CA）内容如下：

环境准备

将工具代码文件夹拷贝至目标环境
–假设安装路径为$INSTALL_FOLDER
–假设目标环境路径为/home/ai_user :
```
scp -r $INSTALL_FOLDER/bin/dbmind/predictor  ai_user@127.0.0.1:path_to_Predictor
```
拷贝CA证书文件夹至aiEngine环境中某路径下：
```
cp -r $GAUSSHOME/CA  ai_user@127.0.0.1:path_to_CA
```
安装predictor/install/requirements(-gpu).txt工具（该功能比较特殊，与其他AI功能不同，需要独立安装依赖）：
```
有GPU：pip install -r requirements-gpu.txt
无GPU：pip install -r requirements.txt
```

拉起AiEngine

切换至aiEngine环境（即拷贝predictor的目标环境 ai_user）：

设置predictor/python/settings.py 中的相关参数：

DEFAULT_FLASK_SERVER_HOST = '127.0.0.1' （aiEngine运行IP地址）
DEFAULT_FLASK_SERVER_PORT = '5000' （aiEngine运行端口号）
PATH_SSL = "path_to_CA" (CA文件夹路径)

运行aiEngine启动脚本：
```
python path_to_Predictor/python/run.py
```
此时，aiEngine即在相应端口保持拉起状态，等待内核侧时间预测功能的请求指令。
至此，aiEngine工具部署完成。

3.使用指导

数据收集

打开数据收集。
1. 设置ActiveSQL operator信息相关参数:
```
enable_resource_track=on
resource_track_level=operator
enable_resource_record=on
resource_track_cost=10（默认值为100000）
```
  说明： - resource_track_cost需设置为小于需要收集的查询总代价，满足条件的信息才能被收集。 - Cgroups功能正常加载。
2. 信息收集：
  执行业务查询语句。
  查看实时收集数据：
```
select * from gs_wlm_plan_operator_history;
```
  预期：满足resource_track_duration和resource_track_cost的作业被全量收集。
关闭数据收集。
1. 设置ActiveSQL operator信息相关参数：
```
enable_resource_track=off 或
resource_track_level=none 或
resource_track_level=query
```
2. 执行业务查询语句。
  等待三分钟之后查看当前节点上的数据：
```
select * from gs_wlm_plan_operator_info;
```
  预期：所查表和视图无新增数据。
数据持久化保存。
1. 设置ActiveSQL operator信息相关参数：
```
enable_resource_track=on
resource_track_level=operator
enable_resource_record=on
resource_track_duration=0（默认值为60s）
resource_track_cost=10（默认值为100000）
```
  说明： - resource_track_cost需设置为小于需要收集的查询总代价，满足条件的信息才能被收集。 - Cgroups功能正常加载。
2. 执行业务查询语句。
  等待三分钟之后查看当前节点上的数据：
```
select * from gs_wlm_plan_operator_info;
```
  预期：满足resource_track_duration和resource_track_cost的作业被全量收集。

模型管理（系统管理员用户）

说明： 模型管理操作需要在数据库正常的状态下进行。

新增模型：
INSERT INTO gs_opt_model values('……')；
示例：
```
INSERT INTO gs_opt_model values('rlstm', 'model_name', 'datname', '127.0.0.1', 5000, 2000, 1, -1, 64, 512, 0 , false, false, '{S, T}', '{0,0}', '{0,0}', 'Text');
```
说明： - 具体模型参数设置请参考GS_OPT_MODEL。 - 目前 “template_name” 列只支持 “rlstm”; - “datname” 列请和用于模型使用和训练的数据库保持一致，否则无法使用。 - “model_name” 一列需要满足unique约束。 - 其他参数设置见产品文档最佳实践部分。

修改模型参数：

UPDATE gs_opt_model SET <attribute> = <value> WHERE model_name = <target_model_name>;

删除模型：

DELETE FROM gs_opt_model WHERE model_name = <target_model_name>;

查询现有模型及其状态：
```
SELECT * FROM gs_opt_model;
```

模型训练（系统管理员用户）

配置/添加模型训练参数：参考模型管理（系统管理员用户）进行模型添加、模型参数修改，来指定训练参数。

例：

模型添加：

INSERT INTO gs_opt_model values('rlstm', 'default', 'postgres', '127.0.0.1', 5000, 2000, 1, -1, 64, 512, 0 , false, false, '{S, T}', '{0,0}', '{0,0}', 'Text');

训练参数更新：

UPDATE gs_opt_model SET <attribute> = <value> WHERE model_name = <target_model_name>;

前提条件为数据库状态正常且历史数据正常收集：
删除原有encoding数据：
```
DELETE FROM gs_wlm_plan_encoding_table;
```
进行数据编码，需要指定数据库名：
```
SELECT gather_encoding_info('postgres');
```
开始训练：
```
SELECT model_train_opt('rlstm', 'default');
```

获取AI Engine侧模型训练日志相对路径：

SELECT * FROM track_model_train_opt('rlstm', 'default');

模型预测

说明： - 模型预测功能需在数据库状态正常、指定模型已被训练且收敛的条件下进行。 - 目前，模型训练参数的标签设置中需要包含“S”标签，explain中才可显示“p-time”预测值。例：INSERT INTO gs_opt_model values('rlstm', 'default', 'postgres', '127.0.0.1', 5000, 1000, 1, -1, 50, 500, 0 , false, false, '{S, T}', '{0,0}', '{0,0}', 'Text');

调用explain接口：

explain (analyze on, predictor <model_name>)
SELECT ...

预期结果：

例：Row Adapter  (cost=110481.35..110481.35 rows=100 p-time=99..182 width=100) (actual time=375.158..375.160 rows=2 loops=1)
其中，“p-time”列为标签预测值。

其他功能

检查AiEngine是否可连接:

openGauss=# select check_engine_status('aiEngine-ip-address',running-port);

查看模型对应日志在AiEngine侧的保存路径：

openGauss=# select track_model_train_opt('template_name', 'model_name');

4.最佳实践

相关参数解释参考表GS_OPT_MODEL。

表 1

模型参数	参数建议
template_name	‘rlstm’
model_name	自定义，如‘open_ai’，需满足unique约束。
datname	所服务database名称，如‘postgres’。
ip	自定义，如‘open_ai’，需满足unique约束。
port	aiEngine侦听端口，如‘5000’。
max_epoch	迭代次数，推荐较大数值，保证收敛效果，如‘2000’。
learning_rate	(0, 1]浮点数，推荐较大的学习率，助于加快收敛速度。
dim_red	特征值降维系数： ‘-1’：不采用PCA降维，全量特征； ‘（0，1] ’区间浮点数：越小，训练维度越小，收敛速度越快，但影响训练准确率。
hidden_units	特征值维度较高时，建议适度增大此参数，提高模型复杂度，如 ‘64，128……’
batch_size	根据编码数据量，较大数据量推荐适度增大此参数，加快模型收敛，如‘256，512……’
其他参数	参考表GS_OPT_MODEL

推荐参数配置：

INSERT INTO gs_opt_model values('rlstm', 'open_ai', 'postgres', '127.0.0.1', 5000, 2000，1, -1, 64, 512, 0 , false, false, '{S, T}', '{0,0}', '{0,0}', 'Text');

5.常见问题处理

AI Engine配置问题

AiEngine启动失败：请检查ip地址，端口是否可用；CA证书路径是否存在。
发起请求AiEngine无响应：请检查通信双方CA证书是否一致。
训练，测试场景失败：请检查模型文件保存路径是否存在；训练预测文件是否在正确下载。
更换AiEngine-IP地址：按照证书生成步骤重新生成证书，在生成证书及密钥中替换成相应的IP地址即可。

数据库内部报错问题

问题：AiEngine链接失败。

ERROR:  AI engine connection failed.
CONTEXT:  referenced column: model_train_opt

处理方法：检查AIEngine是否正常拉起或重启AIEngine；检查通信双方CA证书是否一致；检查模型配置信息中的ip和端口是否匹配；

问题：模型不存在。

ERROR:  OPT_Model not found for model name XXX
CONTEXT:  referenced column: track_model_train_opt

处理方法：检查GS_OPT_MODEL表中是否存在执行语句中“model_name”对应的模型；使用预测功能报错时，检查模型是否已被训练；

opengauss

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

openGauss 经验总结（共11篇）

总结openGauss开发问题&案例，给大家提供参考。

如何预测SQL语句查询时间？

1.概述

2.环境部署

前提条件

请求样例

证书生成

环境准备

拉起AiEngine

3.使用指导

数据收集

模型管理（系统管理员用户）

模型训练（系统管理员用户）

模型预测

其他功能

4.最佳实践

5.常见问题处理

AI Engine配置问题

数据库内部报错问题

文章被以下合辑收录

评论