Usage Guide

Data Collection

1. Enable data collection.

   a. Set parameters related to the ActiveSQL operator.

   enable_resource_track=on
   resource_track_level=operator
   enable_resource_record=on
   resource_track_cost=10 (The default value is 100000.)

   NOTE:

   • The value of resource_track_cost must be smaller than the total query cost of the information to be collected. Only the information that meets the requirements can be collected.
   • Cgroup functions are available.

   b. Collect information.

   Execute the service query statement.

   View data collected in real time.

   select * from gs_wlm_plan_operator_history;

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

2. Disable data collection.

   a. Set any of the following parameters related to the ActiveSQL operator.

   enable_resource_track=off
   resource_track_level=none
   resource_track_level=query

   b. Execute the service query statement.

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

   select * from gs_wlm_plan_operator_info;

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

3. Persist data.

   a. Set parameters related to the ActiveSQL operator.

   enable_resource_track=on
   resource_track_level=operator
   enable_resource_record=on
   resource_track_duration=0 (The default value is 60s.)
   resource_track_cost=10 (The default value is 100000.)

   NOTE:

   • The value of resource_track_cost must be smaller than the total query cost of the information to be collected. Only the information that meets the requirements can be collected.
   • Cgroup functions are available.

   b. Execute the service query statement.

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

   select * from gs_wlm_plan_operator_info;

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

Model Management (System Administrators)

NOTE: Model management operations can be performed only when the database is normal.

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

   NOTE:

   • For details about model parameter settings, see GS_OPT_MODEL.
   • Currently, only rlstm is supported in the template_name column.
   • The values in the datname column must be the same as those in the database used for model usage and training. Otherwise, the model cannot be used.
   • The values in the model_name column must meet the unique constraint.
   • For details about other parameter settings, see Best Practices.

2. Modify model parameters.

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

3. Delete a model.

   DELETE FROM gs_opt_model WHERE model_name = <target_model_name>;

4. Query the existing model and its status.

   SELECT * FROM gs_opt_model;

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.

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

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.

   DELETE FROM gs_wlm_plan_encoding_table;

   To encode data, specify the database name.

   SELECT gather_encoding_info('postgres');

   Start training.

   SELECT model_train_opt('rlstm', 'default');

3. View the model training status.

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

   The URL used by TensorBoard is returned.

   

   Access the URL to view the model training status.

   

Model Prediction

NOTE:

• Model prediction can be performed only when the database status is normal and the specified model has been trained and converged.
• Currently, the labels of model training parameters must contain the S label so that the p-time value can be displayed in EXPLAIN. Example: 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');

Call EXPLAIN.

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

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 AI Engine can be connected.

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

2. Check the path for storing model logs on the AI Engine.

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