Related Documentation Download this Manual
PDF (US Ltr) - 1.7Mb
PDF (A4) - 1.7Mb


HeatWave User Guide  /  HeatWave AutoML  /  Progress tracking

3.14 Progress tracking

MySQL 8.0.32 introduces progress tracking for ML_TRAIN. Use a second MySQL Client window to track progress with calls to the performance schema.

MySQL 8.2.0 adds progress tracking for all HeatWave AutoML routines:

For each of these routines, progress tracking tracks each individual operation, stages within routines, and includes a completed percentage value.

MySQL 8.2.0 adds batch processing for ML_PREDICT_TABLE and ML_EXPLAIN_TABLE.

Syntax Examples

  • As of MySQL 8.2.0, run ML_TRAIN from the first MySQL Client window:

    mysql>  CALL sys.ML_TRAIN('mlcorpus_v5.`titanic_train`', 'survived', NULL, @model);
    Query OK, 0 rows affected (1 min 28.7369 sec)

    From the second MySQL Client window, run the following successive queries:

    1. At the start of the ML_TRAIN operation.

      mysql> SELECT * FROM performance_schema.rpd_query_stats;
      +----------+--------------+---------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      | QUERY_ID | STATEMENT_ID | CONNECTION_ID | QUERY_TEXT    | QEXEC_TEXT                                                                                                                                                                                                                       | QKRN_TEXT | QEP_TEXT | OFFLOAD_FAIL_TEXT |
      +----------+--------------+---------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      |        1 |   4294967295 |    4294967295 | ML_LOAD_TABLE | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.66 Seconds", "startTime": "06/15/2023, 18:55:46:833357 UTC", "progressItems": [], "details": {}}                                   | {}        | {}       |                   |
      |        2 |   4294967295 |    4294967295 | ML_TRAIN      | {"options": null, "operation": "training", "user_name": "root", "table_name": "titanic_train", "schema_name": "mlcorpus_v5", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810", "target_column_name": "survived"} | {}        | {}       |                   |
      +----------+--------------+---------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      2 rows in set (0.0005 sec)
    2. During the ML_TRAIN operation.

      mysql> SELECT * FROM performance_schema.rpd_query_stats;
      +----------+--------------+---------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      | QUERY_ID | STATEMENT_ID | CONNECTION_ID | QUERY_TEXT    | QEXEC_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | QKRN_TEXT | QEP_TEXT | OFFLOAD_FAIL_TEXT |
      +----------+--------------+---------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      |        1 |   4294967295 |    4294967295 | ML_LOAD_TABLE | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.66 Seconds", "startTime": "06/15/2023, 18:55:46:833357 UTC", "progressItems": [], "details": {}}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | {}        | {}       |                   |
      |        2 |   4294967295 |    4294967295 | ML_TRAIN      | {"status": "In Progress", "completedSteps": ["Initialization", "Preprocessing"], "completionPercentage": 11, "totalRunTime": null, "startTime": "06/15/2023, 18:55:47:732187 UTC", "progressItems": [{"type": "Initialization", "status": "Completed", "lastUpdated": "06/15/2023, 18:55:54:093603 UTC", "completionPercentage": 100}, {"type": "Preprocessing", "status": "Completed", "lastUpdated": "06/15/2023, 18:55:54:137747 UTC", "completionPercentage": 100}, {"type": "Algorithm Selection", "status": "In Progress", "lastUpdated": "06/15/2023, 18:55:56:202947 UTC", "completionPercentage": 47}, {"type": "Row Sampling", "status": "Not Started", "lastUpdated": "06/15/2023, 18:55:54:088933 UTC", "completionPercentage": 0}, {"type": "Column Selection", "status": "Not Started", "lastUpdated": "06/15/2023, 18:55:54:089422 UTC", "completionPercentage": 0}, {"type": "Hyperparameter Optimization", "status": "Not Started", "lastUpdated": "06/15/2023, 18:55:54:089931 UTC", "completionPercentage": 0}, {"type": "Model Generation", "status": "Not Started", "lastUpdated": "06/15/2023, 18:55:54:090460 UTC", "completionPercentage": 0}, {"type": "Prediction Explainer", "status": "Not Started", "lastUpdated": "06/15/2023, 18:55:54:090920 UTC", "completionPercentage": 0}, {"type": "Model Explainer", "status": "Not Started", "lastUpdated": "06/15/2023, 18:55:54:091456 UTC", "completionPercentage": 0}], "details": {"trainingScore": null, "optimizationMetric": null, "options": null, "operation": "training", "user_name": "root", "table_name": "titanic_train", "schema_name": "mlcorpus_v5", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810", "target_column_name": "survived"}} | {}        | {}       |                   |
      +----------+--------------+---------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      2 rows in set (0.0005 sec)
    3. When the ML_TRAIN operation is complete.

      mysql> SELECT * FROM performance_schema.rpd_query_stats;
      +----------+--------------+---------------+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      | QUERY_ID | STATEMENT_ID | CONNECTION_ID | QUERY_TEXT        | QEXEC_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | QKRN_TEXT | QEP_TEXT | OFFLOAD_FAIL_TEXT |
      +----------+--------------+---------------+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      |        1 |   4294967295 |    4294967295 | ML_LOAD_TABLE     | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.66 Seconds", "startTime": "06/15/2023, 18:55:46:833357 UTC", "progressItems": [], "details": {}}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | {}        | {}       |                   |
      |        2 |   4294967295 |    4294967295 | ML_TRAIN          | {"status": "Completed", "completedSteps": ["Initialization", "Preprocessing", "Algorithm Selection", "Row Sampling", "Column Selection", "Hyperparameter Optimization", "Model Generation", "Prediction Explainer", "Model Explainer"], "completionPercentage": 100, "totalRunTime": "84.9 Seconds", "startTime": "06/15/2023, 18:55:47:732187 UTC", "progressItems": [{"type": "Initialization", "status": "Completed", "lastUpdated": "06/15/2023, 18:55:54:093603 UTC", "completionPercentage": 100}, {"type": "Preprocessing", "status": "Completed", "lastUpdated": "06/15/2023, 18:55:54:137747 UTC", "completionPercentage": 100}, {"type": "Algorithm Selection", "status": "Completed", "lastUpdated": "06/15/2023, 18:55:57:159736 UTC", "completionPercentage": 100}, {"type": "Row Sampling", "status": "Completed", "lastUpdated": "06/15/2023, 18:55:58:210754 UTC", "completionPercentage": 100}, {"type": "Column Selection", "status": "Completed", "lastUpdated": "06/15/2023, 18:56:11:007284 UTC", "completionPercentage": 100}, {"type": "Hyperparameter Optimization", "status": "Completed", "lastUpdated": "06/15/2023, 18:56:55:725475 UTC", "completionPercentage": 100}, {"type": "Model Generation", "status": "Completed", "lastUpdated": "06/15/2023, 18:57:11:779587 UTC", "completionPercentage": 100}, {"type": "Prediction Explainer", "status": "Completed", "lastUpdated": "06/15/2023, 18:57:11:951237 UTC", "completionPercentage": 100}, {"type": "Model Explainer", "status": "Completed", "lastUpdated": "06/15/2023, 18:57:12:633848 UTC", "completionPercentage": 100}], "details": {"trainingScore": -0.0774, "optimizationMetric": "neg_log_loss", "options": null, "operation": "training", "user_name": "root", "table_name": "titanic_train", "schema_name": "mlcorpus_v5", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810", "target_column_name": "survived"}} | {}        | {}       |                   |
      |        3 |   4294967295 |    4294967295 | ML_MODEL_OBJECT   | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.14 Seconds", "startTime": "06/15/2023, 18:57:14:500838 UTC", "progressItems": [], "details": {"operation": "model_object", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | {}        | {}       |                   |
      |        4 |   4294967295 |    4294967295 | ML_MODEL_METADATA | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.09 Seconds", "startTime": "06/15/2023, 18:57:15:004316 UTC", "progressItems": [], "details": {"operation": "model_metadata", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | {}        | {}       |                   |
      |        5 |   4294967295 |    4294967295 | ML_UNLOAD_MODEL   | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.03 Seconds", "startTime": "06/15/2023, 18:57:15:491299 UTC", "progressItems": [], "details": {"operation": "unload_model", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | {}        | {}       |                   |
      +----------+--------------+---------------+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      5 rows in set (0.0005 sec)
  • As of MySQL 8.2.0, as an example for other operations, run ML_EXPLAIN from the first MySQL Client window:

    mysql> CALL sys.ML_MODEL_LOAD(@model, NULL);
    Query OK, 0 rows affected (0.5951 sec)
    
    mysql> CALL sys.ML_EXPLAIN('mlcorpus_v5.`titanic_train`', 'survived', @model, 
                         JSON_OBJECT('model_explainer', 'fast_shap', 'prediction_explainer', 'shap'));
    Query OK, 0 rows affected (8 min 35.2447 sec)

    From the second MySQL Client window, run the following successive queries:

    1. At the start of the ML_EXPLAIN operation. The first five rows relate to the progress of ML_TRAIN.

      mysql> SELECT * FROM performance_schema.rpd_query_stats;
      +----------+--------------+---------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      | QUERY_ID | STATEMENT_ID | CONNECTION_ID | QUERY_TEXT        | QEXEC_TEXT                                                                                                                                                                                                                                                                                  | QKRN_TEXT | QEP_TEXT | OFFLOAD_FAIL_TEXT |
      +----------+--------------+---------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      |        6 |   4294967295 |    4294967295 | ML_LOAD_TABLE     | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.54 Seconds", "startTime": "06/15/2023, 19:00:37:119521 UTC", "progressItems": [], "details": {}}                                                                                              | {}        | {}       |                   |
      |        7 |   4294967295 |    4294967295 | ML_LOAD_TABLE     | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.61 Seconds", "startTime": "06/15/2023, 19:01:18:392749 UTC", "progressItems": [], "details": {}}                                                                                              | {}        | {}       |                   |
      |        8 |   4294967295 |    4294967295 | ML_EXPLAIN        | {"options": {"model_explainer": "fast_shap", "prediction_explainer": "shap"}, "operation": "explain", "user_name": "root", "table_name": "titanic_train", "schema_name": "mlcorpus_v5", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810", "target_column_name": "survived"} | {}        | {}       |                   |
      +----------+--------------+---------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      8 rows in set (0.0005 sec)
    2. During the ML_EXPLAIN operation.

      mysql> SELECT * FROM performance_schema.rpd_query_stats;
      +----------+--------------+---------------+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      | QUERY_ID | STATEMENT_ID | CONNECTION_ID | QUERY_TEXT        | QEXEC_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | QKRN_TEXT | QEP_TEXT | OFFLOAD_FAIL_TEXT |
      +----------+--------------+---------------+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      |        6 |   4294967295 |    4294967295 | ML_LOAD_TABLE     | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.54 Seconds", "startTime": "06/15/2023, 19:00:37:119521 UTC", "progressItems": [], "details": {}}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | {}        | {}       |                   |
      |        7 |   4294967295 |    4294967295 | ML_LOAD_TABLE     | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.61 Seconds", "startTime": "06/15/2023, 19:01:18:392749 UTC", "progressItems": [], "details": {}}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | {}        | {}       |                   |
      |        8 |   4294967295 |    4294967295 | ML_EXPLAIN        | {"status": "In Progress", "completedSteps": ["Prediction Explainer"], "completionPercentage": 50, "totalRunTime": null, "startTime": "06/15/2023, 19:01:19:237573 UTC", "progressItems": [{"type": "Prediction Explainer", "status": "Completed", "lastUpdated": "06/15/2023, 19:01:25:559690 UTC", "completionPercentage": 100}, {"type": "Model Explainer", "status": "In Progress", "lastUpdated": "06/15/2023, 19:01:25:589740 UTC", "completionPercentage": 0}], "details": {"options": {"model_explainer": "fast_shap", "prediction_explainer": "shap"}, "operation": "explain", "user_name": "root", "table_name": "titanic_train", "schema_name": "mlcorpus_v5", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810", "target_column_name": "survived"}} | {}        | {}       |                   |
      +----------+--------------+---------------+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      8 rows in set (0.0005 sec)
    3. When the ML_EXPLAIN operation is complete.

      mysql> SELECT * FROM performance_schema.rpd_query_stats;
      +----------+--------------+---------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      | QUERY_ID | STATEMENT_ID | CONNECTION_ID | QUERY_TEXT        | QEXEC_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | QKRN_TEXT | QEP_TEXT | OFFLOAD_FAIL_TEXT |
      +----------+--------------+---------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      |        6 |   4294967295 |    4294967295 | ML_LOAD_TABLE     | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.54 Seconds", "startTime": "06/15/2023, 19:00:37:119521 UTC", "progressItems": [], "details": {}}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | {}        | {}       |                   |
      |        7 |   4294967295 |    4294967295 | ML_LOAD_TABLE     | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.61 Seconds", "startTime": "06/15/2023, 19:01:18:392749 UTC", "progressItems": [], "details": {}}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | {}        | {}       |                   |
      |        8 |   4294967295 |    4294967295 | ML_EXPLAIN        | {"status": "Completed", "completedSteps": ["Prediction Explainer", "Model Explainer"], "completionPercentage": 100, "totalRunTime": "511.79 Seconds", "startTime": "06/15/2023, 19:01:19:237573 UTC", "progressItems": [{"type": "Prediction Explainer", "status": "Completed", "lastUpdated": "06/15/2023, 19:01:25:559690 UTC", "completionPercentage": 100}, {"type": "Model Explainer", "status": "Completed", "lastUpdated": "06/15/2023, 19:09:51:031054 UTC", "completionPercentage": 100}], "details": {"options": {"model_explainer": "fast_shap", "prediction_explainer": "shap"}, "operation": "explain", "user_name": "root", "table_name": "titanic_train", "schema_name": "mlcorpus_v5", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810", "target_column_name": "survived"}}| {}        | {}       |                   |
      |        9 |   4294967295 |    4294967295 | ML_MODEL_OBJECT   | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.08 Seconds", "startTime": "06/15/2023, 19:09:53:234425 UTC", "progressItems": [], "details": {"operation": "model_object", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | {}        | {}       |                   |
      |       10 |   4294967295 |    4294967295 | ML_MODEL_METADATA | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.06 Seconds", "startTime": "06/15/2023, 19:09:53:553660 UTC", "progressItems": [], "details": {"operation": "model_metadata", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | {}        | {}       |                   |
      +----------+--------------+---------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
      10 rows in set (0.0005 sec)
  • As of MySQL 8.2.0, run ML_PREDICT_ROW from the first MySQL Client window:

    mysql> SELECT sys.ML_PREDICT_ROW(
              JSON_OBJECT('pclass',`titanic_test`.`pclass`,
                          'name',`titanic_test`.`name`,
                          'sex',`titanic_test`.`sex`,
                          'age',`titanic_test`.`age`,
                          'sibsp',`titanic_test`.`sibsp`,
                          'parch',`titanic_test`.`parch`,
                          'ticket',`titanic_test`.`ticket`,
                          'fare',`titanic_test`.`fare`,
                          'cabin',`titanic_test`.`cabin`,
                          'embarked',`titanic_test`.`embarked`,
                          'boat',`titanic_test`.`boat`,
                          'body',`titanic_test`.`body`,
                          'home.dest',`titanic_test`.`home.dest` ), 
              @model, NULL) FROM `titanic_test` LIMIT 4;
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | sys.ML_PREDICT_ROW(JSON_OBJECT('pclass',`titanic_test`.`pclass`,'name',`titanic_test`.`name`,'sex',`titanic_test`.`sex`,'age',`titanic_test`.`age`,'sibsp',`titanic_test`.`sibsp`,'parch',`titanic_test`.`parch`,'ticket',`titanic_test`.`ticket`,'fare',`titan                                                                                                                               |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | {"age": 20.0, "sex": "male", "boat": null, "body": 89.0, "fare": 9.2250003815, "name": "Olsvigen, Mr. Thor Anderson", "cabin": null, "parch": 0, "sibsp": 0, "pclass": 3, "ticket": "6563", "embarked": "S", "home.dest": "Oslo, Norway Cameron, WI", "Prediction": 0, "ml_results": {"predictions": {"survived": 0}, "probabilities": {"0": 0.9900000095367432, "1": 0.009999999776482582}}} |
    | {"age": 4.0, "sex": "female", "boat": "2", "body": null, "fare": 22.0249996185, "name": "Kink-Heilmann, Miss. Luise Gretchen", "cabin": null, "parch": 2, "sibsp": 0, "pclass": 3, "ticket": "315153", "embarked": "S", "home.dest": null, "Prediction": 1, "ml_results": {"predictions": {"survived": 1}, "probabilities": {"0": 0.0, "1": 1.0}}}                                            |
    | {"age": 42.0, "sex": "male", "boat": null, "body": 120.0, "fare": 7.6500000954, "name": "Humblen, Mr. Adolf Mathias Nicolai Olsen", "cabin": "F G63", "parch": 0, "sibsp": 0, "pclass": 3, "ticket": "348121", "embarked": "S", "home.dest": null, "Prediction": 0, "ml_results": {"predictions": {"survived": 0}, "probabilities": {"0": 0.9900000095367432, "1": 0.009999999776482582}}}    |
    | {"age": 45.0, "sex": "male", "boat": "7", "body": null, "fare": 29.7000007629, "name": "Chevre, Mr. Paul Romaine", "cabin": "A9", "parch": 0, "sibsp": 0, "pclass": 1, "ticket": "PC 17594", "embarked": "C", "home.dest": "Paris, France", "Prediction": 1, "ml_results": {"predictions": {"survived": 1}, "probabilities": {"0": 0.009999999776482582, "1": 0.9900000095367432}}}           |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    4 rows in set (1.1977 sec)

    From the second MySQL Client window, run the following query:

    mysql> SELECT * FROM performance_schema.rpd_query_stats;
    +----------+--------------+---------------+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
    | QUERY_ID | STATEMENT_ID | CONNECTION_ID | QUERY_TEXT        | QEXEC_TEXT                                                                                                                                                                                                                                                                                                                          | QKRN_TEXT | QEP_TEXT | OFFLOAD_FAIL_TEXT |
    +----------+--------------+---------------+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
    |       23 |   4294967295 |    4294967295 | ML_PREDICT_ROW    | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.11 Seconds", "startTime": "06/15/2023, 19:44:10:248906 UTC", "progressItems": [], "details": {"options": null, "operation": "prediction_row", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}} | {}        | {}       |                   |
    |       24 |   4294967295 |    4294967295 | ML_PREDICT_ROW    | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.08 Seconds", "startTime": "06/15/2023, 19:44:10:726698 UTC", "progressItems": [], "details": {"options": null, "operation": "prediction_row", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}} | {}        | {}       |                   |
    |       25 |   4294967295 |    4294967295 | ML_PREDICT_ROW    | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.09 Seconds", "startTime": "06/15/2023, 19:44:11:171724 UTC", "progressItems": [], "details": {"options": null, "operation": "prediction_row", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}} | {}        | {}       |                   |
    |       26 |   4294967295 |    4294967295 | ML_PREDICT_ROW    | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.07 Seconds", "startTime": "06/15/2023, 19:44:11:502916 UTC", "progressItems": [], "details": {"options": null, "operation": "prediction_row", "user_name": "root", "model_handle": "mlcorpus_v5.`titanic_train`_root_1686855346810"}} | {}        | {}       |                   |
    +----------+--------------+---------------+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+-------------------+
    26 rows in set (0.0005 sec)
  • As of MySQL 8.2.0, call ML_PREDICT_TABLE with a batch_size of 2 on a table with 4 rows:

    mysql> CALL sys.ML_MODEL_LOAD(@model, NULL);
    Query OK, 0 rows affected (0.5957 sec)
    
    mysql> CREATE TABLE `titanic_test_temp` AS SELECT * FROM `titanic_test` LIMIT 4;
    Query OK, 4 rows affected (0.0200 sec)
    
    mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus_v5.`titanic_test_temp`', @model, 
              'mlcorpus_v5.`titanic_predictions`', JSON_OBJECT('batch_size',2));
    Query OK, 0 rows affected (3.6509 sec)
    
    mysql> SELECT * FROM titanic_predictions;
    +-----+--------+------------------------------------------+--------+-----+-------+-------+----------+--------+-------+----------+------+------+--------------------------+----------+------------+---------------------------------------------------------------------------+
    | _id | pclass | name                                     | sex    | age | sibsp | parch | ticket   | fare   | cabin | embarked | boat | body | home.dest                | survived | Prediction | ml_results                                                                |
    +-----+--------+------------------------------------------+--------+-----+-------+-------+----------+--------+-------+----------+------+------+--------------------------+----------+------------+---------------------------------------------------------------------------+
    |   1 |      3 | Olsvigen, Mr. Thor Anderson              | male   |  20 |     0 |     0 | 6563     |  9.225 | NULL  | S        | NULL |   89 | Oslo, Norway Cameron, WI |        0 |          0 | {"predictions": {"survived": 0}, "probabilities": {"0": 0.99, "1": 0.01}} |
    |   2 |      3 | Kink-Heilmann, Miss. Luise Gretchen      | female |   4 |     0 |     2 | 315153   | 22.025 | NULL  | S        | 2    | NULL | NULL                     |        1 |          1 | {"predictions": {"survived": 1}, "probabilities": {"0": 0.0, "1": 1.0}}   |
    |   3 |      3 | Humblen, Mr. Adolf Mathias Nicolai Olsen | male   |  42 |     0 |     0 | 348121   |   7.65 | F G63 | S        | NULL |  120 | NULL                     |        0 |          0 | {"predictions": {"survived": 0}, "probabilities": {"0": 0.99, "1": 0.01}} |
    |   4 |      1 | Chevre, Mr. Paul Romaine                 | male   |  45 |     0 |     0 | PC 17594 |   29.7 | A9    | C        | 7    | NULL | Paris, France            |        1 |          1 | {"predictions": {"survived": 1}, "probabilities": {"0": 0.01, "1": 0.99}} |
    +-----+--------+------------------------------------------+--------+-----+-------+-------+----------+--------+-------+----------+------+------+--------------------------+----------+------------+---------------------------------------------------------------------------+
    4 rows in set (0.0006 sec)

    From the second MySQL Client window, run the following query:

    mysql> SELECT * FROM performance_schema.rpd_query_stats;
    +----------+--------------+---------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+----------------------------+----------------------------+
    | QUERY_ID | STATEMENT_ID | CONNECTION_ID | QUERY_TEXT        | QEXEC_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                         | QKRN_TEXT | QEP_TEXT | INTERNAL_OFFLOAD_FAIL_TEXT | EXTERNAL_OFFLOAD_FAIL_TEXT |
    +----------+--------------+---------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+----------------------------+----------------------------+
    |        8 |       397099 |            10 | ML_LOAD_TABLE     | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.54 Seconds", "startTime": "12/13/2023, 00:55:00:549849 UTC", "progressItems": [], "details": {}}                                                                                                                                                                                                                                     | {}        | {}       |                            |                            |
    |        9 |       397224 |            10 | ML_LOAD_TABLE     | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.48 Seconds", "startTime": "12/13/2023, 00:55:04:780535 UTC", "progressItems": [], "details": {}}                                                                                                                                                                                                                                     | {}        | {}       |                            |                            |
    |       10 |       595247 |            10 | ML_PREDICT_TABLE  | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.06 Seconds", "startTime": "12/13/2023, 00:55:05:577699 UTC", "progressItems": [], "details": {"options": {"batch_size": 2}, "operation": "prediction_table", "user_name": "root", "table_name": "0266fbec8a7fdf3b12ac766650391345", "schema_name": "mlcorpus_v5", "model_handle": "mlcorpus_v5.`titanic_train`_root_1702428631617"}} | {}        | {}       |                            |                            |
    |       11 |       595318 |            10 | ML_LOAD_TABLE     | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.45 Seconds", "startTime": "12/13/2023, 00:55:06:679923 UTC", "progressItems": [], "details": {}}                                                                                                                                                                                                                                     | {}        | {}       |                            |                            |
    |       12 |       793401 |            10 | ML_PREDICT_TABLE  | {"status": "Completed", "completedSteps": [], "completionPercentage": 100, "totalRunTime": "0.06 Seconds", "startTime": "12/13/2023, 00:55:07:434696 UTC", "progressItems": [], "details": {"options": {"batch_size": 2}, "operation": "prediction_table", "user_name": "root", "table_name": "0266fbec8a7fdf3b12ac766650391345", "schema_name": "mlcorpus_v5", "model_handle": "mlcorpus_v5.`titanic_train`_root_1702428631617"}} | {}        | {}       |                            |                            |
    +----------+--------------+---------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+----------+----------------------------+----------------------------+
    12 rows in set (0.0011 sec)
  • As of MySQL 8.2.0, to extract the completed percentage for the ML_TRAIN operation:

    mysql> SELECT QEXEC_TEXT INTO @progress_json 
                         FROM performance_schema.rpd_query_stats 
                         WHERE QUERY_TEXT='ML_TRAIN' 
                         ORDER BY QUERY_ID 
                         DESC limit 1;
    Query OK, 1 row affected (0.0009 sec)
    
    mysql> SELECT JSON_EXTRACT(@progress_json, '$.completionPercentage');
    Query OK, 1 row affected (0.0009 sec)
    +--------------------------------------------------------+
    | JSON_EXTRACT(@progress_json, '$.completionPercentage') |
    +--------------------------------------------------------+
    | 63                                                     |
    +--------------------------------------------------------+
    1 row in set (0.0005 sec)
  • As of MySQL 8.2.0, to extract the status for the ML_TRAIN operation:

    mysql> SELECT QEXEC_TEXT INTO @progress_json 
              FROM performance_schema.rpd_query_stats 
              WHERE QUERY_TEXT='ML_TRAIN' 
              ORDER BY QUERY_ID 
              DESC limit 1;
    Query OK, 1 row affected (0.0009 sec)
    
    mysql> SELECT JSON_EXTRACT(@progress_json, '$.status');
    Query OK, 1 row affected (0.0009 sec)
    +------------------------------------------+
    | JSON_EXTRACT(@progress_json, '$.status') |
    +------------------------------------------+
    | "In Progress"                            |
    +------------------------------------------+
    1 row in set (0.0006 sec)
  • As of MySQL 8.0.32, and before MySQL 8.2.0, run ML_TRAIN from the first MySQL Client window:

    mysql> CALL sys.ML_TRAIN('heatwaveml_bench.census_train', 'revenue', 
              JSON_OBJECT('task', 'classification'), @census_model);

    From the second MySQL Client window, run the following successive queries:

    1. Before ML_TRAIN has started. It might take several seconds before ML_TRAIN starts with a large dataset.

      mysql> SELECT QEXEC_TEXT FROM performance_schema.rpd_query_stats 
                 WHERE QUERY_TEXT='ML_TRAIN' 
                 ORDER BY QUERY_ID  
                 DESC limit 1;
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | QEXEC_TEXT                                                                                                                                                                                                                            |
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | {"options": null, "operation": "training", "user_name": "user1", "table_name": "`census_train`", "schema_name": heatwaveml_bench", "model_handle": "heatwaveml_bench.census_train_user1_1636729526", "target_column_name": "revenue"} |
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    2. During the ML_TRAIN operation.

      mysql> SELECT QEXEC_TEXT FROM performance_schema.rpd_query_stats 
                 WHERE QUERY_TEXT='ML_TRAIN' 
                 ORDER BY QUERY_ID  
                 DESC limit 1;
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | QEXEC_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | {"status": "In Progress", "completedSteps": ["Initialization", "Preprocessing", "Algorithm Selection"], "elapsedRunTime": 9.716094, "startTime": "11/28/2022, 09:02:36:910865", "progressItems": [{"type": "Initialization", "status": "Completed", "timestamp": "11/28/2022, 09:02:44:662426"}, {"type": "Preprocessing", "status": "Completed", "timestamp": "11/28/2022, 09:02:44:696405"}, {"type": "Algorithm Selection", "status": "Completed", "timestamp": "11/28/2022, 09:02:46:623882"}, {"type": "Row Sampling", "status": "In Progress", "timestamp": "11/28/2022, 09:02:46:626959"}, {"type": "Column Selection", "status": "Pending", "timestamp": "11/28/2022, 09:02:44:655721"}, {"type": "Hyperparameter Optimization", "status": "Pending", "timestamp": "11/28/2022, 09:02:44:656245"}, {"type": "Model Generation", "status": "Pending", "timestamp": "11/28/2022, 09:02:44:656736"}], "options": null, "operation": "training", "user_name": "user1", "table_name": "`census_train`", "schema_name": heatwaveml_bench", "model_handle": "heatwaveml_bench.census_train_user1_1636729526", "target_column_name": "revenue"} |
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    3. When the ML_TRAIN operation is complete.

      mysql> SELECT QEXEC_TEXT FROM performance_schema.rpd_query_stats 
                 WHERE QUERY_TEXT='ML_TRAIN' 
                 ORDER BY QUERY_ID  
                 DESC limit 1;
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | QEXEC_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | {"status": "Completed", "completedSteps": ["Initialization", "Preprocessing", "Algorithm Selection", "Row Sampling", "Column Selection", "Hyperparameter Optimization", "Model Generation"], "elapsedRunTime": 69.678911, "startTime": "11/28/2022, 09:02:36:910865", "progressItems": [{"type": "Initialization", "status": "Completed", "timestamp": "11/28/2022, 09:02:44:662426"}, {"type": "Preprocessing", "status": "Completed", "timestamp": "11/28/2022, 09:02:44:696405"}, {"type": "Algorithm Selection", "status": "Completed", "timestamp": "11/28/2022, 09:02:46:623882"}, {"type": "Row Sampling", "status": "Completed", "timestamp": "11/28/2022, 09:02:47:252748"}, {"type": "Column Selection", "status": "Completed", "timestamp": "11/28/2022, 09:03:46:055310"}, {"type": "Hyperparameter Optimization", "status": "Completed", "timestamp": "11/28/2022, 09:03:46:055928"}, {"type": "Model Generation", "status": "Completed", "timestamp": "11/28/2022, 09:03:46:589776"}], "options": null, "operation": "training", "user_name": "user1", "table_name": "`census_train`", "schema_name": heatwaveml_bench", "model_handle": "heatwaveml_bench.census_train_user1_1636729526", "target_column_name": "revenue"} |
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+