前沿科技探究之AI功能：慢SQL发现

Gauss松鼠会

已于 2022-07-19 15:22:08 修改

阅读量1w

点赞数 23

分类专栏： openGauss经验总结文章标签：数据库 database sql

于 2022-04-01 09:46:39 首次发布

本文链接：https://blog.csdn.net/GaussDB/article/details/123889483

版权

openGauss经验总结专栏收录该内容

140 篇文章 93 订阅

订阅专栏

SQLdiag：慢SQL发现

SQLdiag是openGauss中SQL语句执行时长预测工具。现有的预测技术主要基于执行计划的预测方法，但这些预测方案仅适用于OLAP场景且可以获取执行计划的任务，对于OLTP或者HTAP这样的快速、简单查询是没有太多使用价值的。与上述方案不同，SQLdiag着眼于数据库的历史SQL语句，通过对历史SQL语句的执行表现进行总结归纳，将之再用于推断新的未知业务上。由于短时间内数据库SQL语句执行时长不会有太大的差距，SQLdiag可以从历史数据中检测出与已执行SQL语句相似的语句结果集，并基于SQL向量化技术和模板化方法预测SQL语句执行时长。本工具有如下优点：

不需要SQL语句的执行计划，对数据库性能不会有任何的影响。
使用场景广泛，目前业内的很多算法局限性比较高，比如只适用于OLTP或者OLAP，而SQLdiag使用场景广泛。
该框架容易理解，只需要简单的操作，就可以训练出自己的预测模型。
本工具的典型应用场景是对一批即将上线的SQL语句进行透视，提前识别风险。

概述

SQLdiag是一个SQL语句执行时间预测工具，通过模板化方法或者深度学习方法，实现在不获取SQL语句执行计划的前提下，依据语句逻辑相似度与历史执行记录，预测SQL语句的执行时间并以此发现异常SQL。

使用指导

前提条件

需要保证用户提供训练数据。
如果用户通过提供的工具收集训练数据，则需要启用WDR功能，涉及到的参数为track_stmt_stat_level和log_min_duration_statement，具体情况见下面小结。
为保证预测准确率，用户提供的历史语句日志应尽可能全面并具有代表性。
按照要求配置python 3.6+环境及其依赖。

环境配置

本功能运行环境要求Python 3.6版本及以上，需要的第三方依赖包记录在requirements.txt文件中，可以通过pip install命令安装依赖，如：

pip install requirements.txt

SQL流水采集方法

本工具需要用户提前准备数据，训练数据格式如下，每个样本通过换行符分隔：

SQL,EXECUTION_TIME

预测数据格式如下：

SQL

其中SQL表示SQL语句的文本，EXECUTION_TIME表示SQL语句的执行时间，样例数据见sample_data中的train.csv和predict.csv。

用户可以按照要求格式自己收集训练数据，工具也提供了脚本自动采集（load_sql_from_rd），该脚本基于WDR报告获取SQL信息，涉及到的参数有log_min_duration_statement和track_stmt_stat_level：

其中log_min_duration_statement表示慢SQL阈值，如果为0则全量收集，时间单位为毫秒；
track_stmt_stat_level表示信息捕获的级别，建议设置为track_stmt_stat_level=‘L0,L0’
参数开启后，可能占用一定的系统资源，但一般不大。持续的高并发场景可能产生5%以内的损耗，数据库并发较低的场景，性能损耗可忽略。

使用脚本获取训练集方式：
load_sql_from_wdr.py [-h] --port PORT --start_time START_TIME
                            --finish_time FINISH_TIME [--save_path SAVE_PATH]
例如：
    python load_sql_from_wdr.py --start_time "2021-04-25 00:00:00" --finish_time "2021-04-26 14:00:00" --port 5432  --save_path ./data.csv

操作步骤

提供历史日志以供模型训练
进行训练与预测操作：

基于模板法的训练与预测：
    python main.py [train, predict] -f FILE --model template --model-path template_model_path 
基于DNN的训练与预测：
    python main.py [train, predict] -f FILE --model dnn --model-path dnn_model_path

使用方法示例

在本工具的根目录中，执行下列语句可以实现对应功能。

使用提供的测试数据进行模板化训练：

python main.py train -f ./sample_data/train.csv --model template --model-path ./template

使用提供的测试数据进行模板化预测：

python main.py predict -f ./sample_data/predict.csv --model template --model-path ./template --predicted-file ./result/t_result

使用提供的测试数据进行模板化模型更新：

python main.py finetune -f ./sample_data/train.csv --model template --model-path ./template

使用提供的测试数据进行DNN训练：

python main.py train -f ./sample_data/train.csv --model dnn --model-path ./dnn_model

使用提供的测试数据进行DNN预测：

python main.py predict -f ./sample_data/predict.csv --model dnn --model-path ./dnn_model --predicted-file

使用提供的测试数据进行DNN模型更新：

python main.py finetune -f ./sample_data/train.csv --model dnn --model-path ./dnn_model

获取帮助

使用SQLdiag工具前，您可以通过以下指令获取帮助。

python main.py --help

显示如下帮助信息：

usage: main.py [-h] [-f CSV_FILE] [--predicted-file PREDICTED_FILE]
               [--model {template,dnn}] --model-path MODEL_PATH
               [--config-file CONFIG_FILE]
               {train,predict,finetune}

SQLdiag integrated by openGauss.

positional arguments:
  {train,predict,finetune}
                        The training mode is to perform feature extraction and
                        model training based on historical SQL statements. The
                        prediction mode is to predict the execution time of a
                        new SQL statement through the trained model.

optional arguments:
  -h, --help            show this help message and exit
  -f CSV_FILE, --csv-file CSV_FILE
                        The data set for training or prediction. The file
                        format is CSV. If it is two columns, the format is
                        (SQL statement, duration time). If it is three
                        columns, the format is (timestamp of SQL statement
                        execution time, SQL statement, duration time).
  --predicted-file PREDICTED_FILE
                        The file path to save the predicted result.
  --model {template,dnn}
                        Choose the model model to use.
  --model-path MODEL_PATH
                        The storage path of the model file, used to read or
                        save the model file.
  --config-file CONFIG_FILE

命令参考

表 1 命令行参数说明

参数	参数说明	取值范围
-f	训练或预测文件位置
–predicted-file	预测结果存储位置
–model	模型选择	template，dnn
–model-path	训练模型存储位置

常见问题处理

数据库实例连接失败：请检查数据库实例的情况，是否数据库实例出现了问题或安全权限配置（pg_hba.conf文件中的配置项）不正确。
重启失败：请检查数据库实例健康情况，确保数据库实例工作正常。
依赖安装失败：建议先升级pip包管理工具，通过命令python -m pip install –upgrade pip实现。
跑TPC-C作业时发现性能越来越慢：TPC-C等高并发场景下的压力测试，往往伴随着大量的数据修改。由于每一次测试并非是幂等的（TPC-C数据库数据量的增加、没有进行vacuum full清理掉失效元组、数据库没有触发checkpoint、没有进行drop cache等），因此一般建议TPC-C等伴随着较多数据写入的benchmark应该每隔一段时间（视具体并发量、执行时长的不同而异）重新导入一次数据，比较简单的方法是备份$PGDATA目录。
TPC-C 跑作业时，TPC-C驱动脚本报异常 “TypeError: float() argument must be a string or a number, not ‘NoneType’”（不能将None转换为float类型）：这是因为没有获取到TPC-C的压测返回结果，造成该问题的原因比较多，请首先手动检测是否能够跑通TPC-C并能够获取返回结果。若无上述问题，则建议将 TPC-C 驱动脚本中的命令列表中的 “sleep” 命令延迟时间设得更大一些。