Intelligence Explain: SQL Statement Query Time Prediction
Tags: opengauss
Published: 2023-04-17 08:18:05

**Overview**
Predictor is a query time prediction tool that leverages machine learning and has online learning capabilities. By continuously learning the historical execution information collected in the database, Predictor can predict the execution time of a plan.

To use this tool, you must start the Python process AI Engine for model training and inference.

This function is stored in the $GAUSSHOME/**bin/dbmind/components/predictor** directory. Some functions of this module involve complex setup. Therefore, you need to switch to the directory to find the corresponding files and deploy the functions according to the description in this section.

****Environment Deployment**
Prerequisites**
openGauss is in the normal state. The user has logged in to openGauss with an authenticated identity. The executed SQL syntax is correct and no error is reported. In the historical performance data window, the number of openGauss concurrent tasks is stable, the structure and number of tables remain unchanged, the data volume changes smoothly, and the GUC parameters related to query performance remain unchanged. During prediction, the model has been trained and converged. The running environment of the AIEngine is stable.

**Request Example**
The AIEngine process communicates with the kernel process using HTTPS. An example request is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vtni1hwoshk62xlyo5by.png)

Table 1 AIEngine external APIs

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tnt94u77xfosdqiedt1e.png)

**Generating Certificates**
Before using the prediction function, you need to use OpenSSL to generate certificates required for authentication between the communication parties, ensuring communication security.

1. Set up a certificate generation environment. The certificate file is stored in **$GAUSSHOME/CA**.

– Copy the certificate generation script and related files.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x0wexpx8qvk9hhrtn8mn.png)

– Copy the configuration file **openssl.cnf** to **$GAUSSHOME**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/raq47wkdadz86gjthpof.png)

– Modify the configuration parameters in **openssl.conf**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sejp3ju17skz1qfp1t82.png)

– The certificate generation environment is ready.

2. Generate a certificate and private key.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8jihike85z6s19gq1m5c.png)

– Set the password as prompted, for example, **Test@123**.

– The password must contain at least eight characters of at least three different types.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e9fuzy3blm3lejsvd36h.png)

– Set the options as prompted.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2k6pqmvjrncmqr0mzifk.png)

– Enter the IP address for starting the AIEngine, for example, **127.0.0.1**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/djhx29aua3dy88vfx7lw.png)

– Set the options as prompted.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5g68fna2ys8hfi5zozpn.png)

– Enter the IP address for starting openGauss, for example, **127.0.0.1**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wfd01tpil66ldtfi49a4.png)

– Set the options as prompted.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9ph62fzbedpg15qyfngo.png)

– The related certificate and key have been generated. The content in **$GAUSSHOME/CA** is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7urqiebszzq1gbln9kw6.png)

**Setting Up the Environment**
1. Copy the tool code folder to the target environment.

– Assume that the installation directory is **$INSTALL_FOLDER**.

– Assume that the destination directory is **/home/ai_user**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bdcv3g3xaandyf2dihul.png)

2. Copy the CA certificate folder to a directory in the AIEngine environment.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rendm0bi800f5t9ftdgp.png)

3. Install the **predictor/install/requirements(-gpu).txt** tool.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/71xc7n18mdjc7nquumdz.png)

**Starting the AIEngine**
1. Switch to the AIEngine environment (that is, copy the target environment **ai_user** of the predictor).

Set parameters in **predictor/python/settings.py**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0p86n87x00tn5l18gxcv.png)

2. Run the startup script of the AIEngine.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1ov628pnfa84jgavlfaj.png)

In this case, the AIEngine keeps enabled on the corresponding port and waits for the request of the time prediction function from the kernel.

For details about how to initiate a time prediction command from the kernel, see User Guide.

**User Guide**
**Data Collection**
1. Enable data collection.

1.1 Set parameters related to the ActiveSQL operator.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8nxaf4avisfdpnb5ejr4.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ijf8rx74p8xh3ueil1qq.png)

1.2 Collect information.

Execute the service query statement.

View data collected in real time.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q0bn7dqgqaysm8c2cj2c.png)

Expected result: All jobs that meet **resource_track_duration** and **resource_track_cost** are collected.

2 Disable data collection.

2.1 Set parameters related to the ActiveSQL operator.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r3j4quwkmtl3wchg2lyi.png)

2.2 Execute the service query statement.

Wait for 3 minutes and check the data on the current node.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/09nvzk31a75fpb0w4zc2.png)

Expected result: No new data is added to the tables and views.

3. Persist data.

3.1 Set parameters related to the ActiveSQL operator.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wluw0o4z61fgava9igj1.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qnh6egrhif8upstspw1l.png)

3.2 Execute the service query statement.

Wait for 3 minutes and check the data on the current node.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i9yoavvuyfhx956n64dw.png)

Expected result: All jobs that meet **resource_track_duration** and **resource_track_cost** are collected.

**Model Management (System Administrators)**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dzf7misov5e2f3601p5g.png)

1. Add a new model.

INSERT INTO gs_opt_model values('……');

Example:

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');

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9inh36qxepdv5pe153sn.png)

2. Modify model parameters.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e5uypwh0jl5jdxe6cjon.png)

3. Delete a model.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hk10njsejj44swy549ag.png)

4. Query the existing model and its status.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qvubs6y5cne9mrprukyl.png)

**Model Training (System Administrators)**
1. Add models and modify model parameters by following the steps in Model Management (System Administrators).

Example:

Add a mode.

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');

Modify training parameters.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7lkrqr5suk10fbuy2zou.png)

2. Check that the database status is normal and historical data is collected properly before you perform the following operations:

Delete the original encoding data.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/61x1gm1vtezy3v90xslz.png)

To encode data, specify the database name.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zyqftm1kp4dih70peyhj.png)

Start training.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ywowdp0uknn6kpwyyvco.png)

3. Run the following command to obtain the relative path of the model training log on the AIEngine side:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pgmob8hsyxq83dp8o7ie.png)

**Model Prediction**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hu393k2wz22qfofpgh8h.png)

1. Call EXPLAIN.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gyzx7vixgpob8b8gstm9.png)

Expected result:

Example: 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)
The p-time column indicates the predicted value of the label.

**Other Functions**
1. Check whether the AIEngine can be connected.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nggmvvqhtpji8r9kngi4.png)

2. Check the path for storing model logs on the AIEngine.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/12lv8fjr1cu3lwcxgpy4.png)

**Best Practices**
For details about the parameters, see GS_OPT_MODEL.

Table 1

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jsctvc7zj7hprwneq7kd.png)

Recommended parameter settings:

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');

**FAQs**

**AI Engine Configuration**
**·** AI Engine failed to be started: Check whether the IP address and port are available and whether the CA certificate path exists.
**·** AI Engine does not respond: Check whether the CA certificates of the two communication parties are consistent.
**·** Training and test failure: Check whether the path for saving the model files exists and whether the training prediction file is correctly downloaded.
**·** Changing the AI Engine IP address: Regenerate the certificate by following the steps in Generating Certificates. Enter the new IP address in Generate a certificate and private key.

**Database Internal Errors**
Problem: AI Engine connection failed.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xsnwxml7ogq71ngy1fi8.png)

Solution: Check whether the AI Engine is started or restarted properly. Check whether the CA certificates of the communication parties are consistent. Check whether the IP address and port number in the model configuration match.

Problem: The model does not exist.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5htv10c95g7vwspjpbhu.png)

Solution: Check whether GS_OPT_MODEL contains the model specified in the **model_name** column in the statement. If the error is reported when the prediction function is used, check whether the model has been trained.