HeatWave User Guide  /  ...  /  ML_MODEL_ACTIVE

10.2.12 ML_MODEL_ACTIVE

MySQL 9.0.0 introduces the ML_MODEL_ACTIVE routine. Use this routine to check which models are loaded and active for which users. All active users and models share the amount of memory defined by the shape, and it might be necessary to schedule users.

ML_MODEL_ACTIVE Syntax

mysql> CALL sys.ML_MODEL_ACTIVE (user, model_info);

ML_MODEL_ACTIVE parameters:

  • user: The user to provide information for. Set to current or all or NULL. NULL is equivalent to current.

  • model_info: The name of the JSON array session variable that contains the active user and model information. There are two JSON object literals.

    If user is set to current or NULL, the following information displays.

    • A JSON object literal that displays:

      • Key: The total model size (bytes).

      • Value: The sum of model sizes for the current user.

    • A second JSON object literal that displays:

      • Key: The model handle for a loaded and active model owned by the current user.

      • Value: The model_metadata for the model.

    If user is set to all, the following information displays.

    • A JSON object literal that displays:

      • Key: The total model size (bytes).

      • Value: The sum of model sizes for all users.

    • A second JSON object literal that displays:

      • Key: The name of a user who has loaded and active models.

      • Value: A list of JSON object literals of the model handle and brief model metadata for each loaded and active model.

Syntax Examples

  • user1 checks their own models:

    mysql> CALL sys.ML_MODEL_ACTIVE('current', @model_info);
    Query OK, 0 rows affected (0.10 sec)
    
    mysql> SELECT JSON_PRETTY(@model_info);
    +-----------------------------------------------------------+
    | JSON_PRETTY(@model_info)                                  |
    +-----------------------------------------------------------+
    | [                                                         |
    {                                                         |
    "total model size(bytes)": 348954                       |
    },                                                        |
    {                                                         |
    "iris_export_user1": {                                  |
      "task": "classification",                             |
      "notes": "",                                          |
      "chunks": 1,                                          |
      "format": "HWMLv2.0",                                 |
      "n_rows": 120,                                        |
      "status": "Ready",                                    |
      "options": {                                          |
        "model_explainer": "permutation_importance, shap",  |
        "prediction_explainer": "shap"                      |
      },                                                    |
      "n_columns": 4,                                       |
      "pos_class": null,                                    |
      "column_names": [                                     |
        "sepal length",                                     |
        "sepal width",                                      |
        "petal length",                                     |
        "petal width"                                       |
      ],                                                    |
      "contamination": null,                                |
      "model_quality": "high",                              |
      "training_time": 18.363686,                           |
      "algorithm_name": "ExtraTreesClassifier",             |
      "training_score": -0.10970368035588404,               |
      "build_timestamp": 1697524180,                        |
      "n_selected_rows": 96,                                |
      "training_params": {                                  |
        "sp_arr": null,                                     |
        "timezone": null,                                   |
        "recommend": "ratings",                             |
        "force_use_X": false,                               |
        "recommend_k": 3,                                   |
        "remove_seen": true,                                |
        "contamination": null,                              |
        "feedback_threshold": 1                             |
      },                                                    |
      "train_table_name": "mlcorpus.iris_train",            |
      "model_explanation": {                                |
        "shap": {                                           |
          "petal width": 0.3139,                            |
          "sepal width": 0.0296,                            |
          "petal length": 0.2787,                           |
          "sepal length": 0.0462                            |
        },                                                  |
        "permutation_importance": {                         |
          "petal width": 0.2301,                            |
          "sepal width": 0.0056,                            |
          "petal length": 0.2192,                           |
          "sepal length": 0.0056                            |
        }                                                   |
      },                                                    |
      "model_object_size": 348954,                          |
      "n_selected_columns": 4,                              |
      "target_column_name": "class",                        |
      "optimization_metric": "neg_log_loss",                |
      "selected_column_names": [                            |
        "petal length",                                     |
        "petal width",                                      |
        "sepal length",                                     |
        "sepal width"                                       |
      ]                                                     |
    }                                                       |
    }                                                         |
    ] |                                                         |
    +-----------------------------------------------------------+
    1 row in set (0.00 sec)
  • user1 checks their own models, and extracts specific information:

    mysql> CALL sys.ML_MODEL_ACTIVE('current', @model_info);
    Query OK, 0 rows affected (0.12 sec)
    
    mysql> SELECT JSON_KEYS(JSON_EXTRACT(@model_info, '$[1]'));
    +------------------------------------------------------------+
    | JSON_KEYS(JSON_EXTRACT(@model_info, '$[1]'))               |
    +------------------------------------------------------------+
    | ["iris_export", "mlcorpus.iris_train_user1_1697524152037"] |
    +------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT JSON_EXTRACT(@model_info, '$[0]');
    +--------------------------------------+
    | JSON_EXTRACT(@model_info, '$[0]')    |
    +--------------------------------------+
    | {"total model size(bytes)":  697908} |
    +--------------------------------------+
    1 row in set (0.01 sec)
  • user1 checks the models for all users:

    mysql> CALL sys.ML_MODEL_ACTIVE('all', @model_info);
    Query OK, 0 rows affected (0.11 sec)
    
    mysql> SELECT JSON_PRETTY(@model_info);
    +-----------------------------------------------------+
    | JSON_PRETTY(@model_info)                            |
    +-----------------------------------------------------+
    | [                                                   |
    {                                                   |
    "total model size(bytes)": 1046862                |
    },                                                  |
    {                                                   |
    "user2": [                                        |
      {                                               |
        "iris_export_user2": {                        |
          "format": "HWMLv2.0",                       |
          "model_size(byte)": 348954                  |
        }                                             |
      }                                               |
    ],                                                |
    "user1": [                                        |
      {                                               |
        "mlcorpus.iris_train_user1_1697524152037": {  |
          "format": "HWMLv2.0",                       |
          "model_size(byte)": 348954                  |
        }                                             |
      },                                              |
      {                                               |
        "iris_export": {                              |
          "format": "HWMLv2.0",                       |
          "model_size(byte)": 348954                  |
        }                                             |
      }                                               |
    ]                                                 |
    }                                                   |
    ] |                                                   |
    +-----------------------------------------------------+
    1 row in set (0.00 sec)