如何預測SQL語句查詢時間?

語言: CN / TW / HK

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工具生成通訊雙方認證所需的證書,保證通訊安全。

  1. 搭建證書生成環境,證書檔案儲存路徑為$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
    

    –至此證書生成環境準備完成

  2. 生成證書及金鑰

    cd $GAUSSHOME
    sh ssl.sh
    

    –根據提示設定密碼,假如為[email protected]

    –密碼要求至少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)內容如下:

環境準備

  1. 將工具程式碼資料夾拷貝至目標環境

    –假設安裝路徑為$INSTALL_FOLDER

    –假設目標環境路徑為/home/ai_user :

    scp -r $INSTALL_FOLDER/bin/dbmind/predictor  [email protected]:path_to_Predictor
    
  2. 拷貝CA證書資料夾至aiEngine環境中某路徑下:

    cp -r $GAUSSHOME/CA  [email protected]:path_to_CA
    
  3. 安裝predictor/install/requirements(-gpu).txt工具(該功能比較特殊,與其他AI功能不同,需要獨立安裝依賴):

    有GPU:pip install -r requirements-gpu.txt
    無GPU:pip install -r requirements.txt
    

拉起AiEngine

  1. 切換至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資料夾路徑)
    
  2. 執行aiEngine啟動指令碼:

    python path_to_Predictor/python/run.py
    

    此時,aiEngine即在相應埠保持拉起狀態,等待核心側時間預測功能的請求指令。

    至此,aiEngine工具部署完成。從核心中發起執行時間預測功能指令步驟,請參考使用指導

3.使用指導

資料收集

  1. 開啟資料收集。

    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的作業被全量收集。

  2. 關閉資料收集。

    1. 設定ActiveSQL operator資訊相關引數:

      enable_resource_track=off 或
      resource_track_level=none 或
      resource_track_level=query
      
    2. 執行業務查詢語句。

      等待三分鐘之後檢視當前節點上的資料:

      select * from gs_wlm_plan_operator_info;
      

      預期:所查表和檢視無新增資料。

  3. 資料持久化儲存。

    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的作業被全量收集。

模型管理(系統管理員使用者)

 說明: 模型管理操作需要在資料庫正常的狀態下進行。

  1. 新增模型:

    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約束。 - 其他引數設定見產品文件最佳實踐部分。

  2. 修改模型引數:

    UPDATE gs_opt_model SET <attribute> = <value> WHERE model_name = <target_model_name>;
    
  3. 刪除模型:

    DELETE FROM gs_opt_model WHERE model_name = <target_model_name>;
    
  4. 查詢現有模型及其狀態:

    SELECT * FROM gs_opt_model;
    

模型訓練(系統管理員使用者)

  1. 配置/新增模型訓練引數:參考模型管理(系統管理員使用者)進行模型新增、模型引數修改,來指定訓練引數。

    例:

    模型新增:

    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>;
    
  2. 前提條件為資料庫狀態正常且歷史資料正常收集:

    刪除原有encoding資料:

    DELETE FROM gs_wlm_plan_encoding_table;
    

    進行資料編碼,需要指定資料庫名:

    SELECT gather_encoding_info('postgres');
    

    開始訓練:

    SELECT model_train_opt('rlstm', 'default');
    
  3. 獲取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');

  1. 呼叫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”列為標籤預測值。
    

其他功能

  1. 檢查AiEngine是否可連線:

    openGauss=# select check_engine_status('aiEngine-ip-address',running-port);
    
  2. 檢視模型對應日誌在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

aiEngine-ip地址,如‘127.0.0.1’。

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”對應的模型;使用預測功能報錯時,檢查模型是否已被訓練;