User Guide

Data Collection

1. Enable data collection.

   1. 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.

   2. 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.

   1. Set parameters related to the ActiveSQL operator.

      enable_resource_track=off
      resource_track_level=none
      resource_track_level=query

   2. 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.

   1. 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.

   2. 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. Run the following command to obtain the relative path of the model training log on the AIEngine side:

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

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

1. 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 AIEngine can be connected.

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

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

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