MySQL :: HeatWave User Guide :: 6.7.6.4 Train a Model in an Interim Isolated Environment

HeatWave User Guide / ... / Train a Model in an Interim Isolated Environment

6.7.6.4 Train a Model in an Interim Isolated Environment

The ML_TRAIN routine is a compute-intensive operation that may an take extended period of time to complete. To minimize training latency, MySQL HeatWave runs ML_TRAIN in parallel across the MySQL HeatWave cluster, consuming significant amount of ECPU and memory resources on each node. During training, these resources are exclusively allocated for AutoML and GenAI operations, and also some background activities. SQL queries offloaded to the RAPID engine are queued until ML_TRAIN completes. MySQL HeatWave still allows limited resource sharing with GenAI workloads and other AutoML queries, with a maximum of eight concurrent operations.

In contrast, the ML_PREDICT_TABLE and ML_PREDICT_ROW routines perform inference by applying a pre-trained machine learning model to input data and generate predictions. These computations are lightweight in terms of ECPU and memory consumption, and process fairly quickly. They can therefore be interleaved with RAPID-offloaded online queries.

ML_TRAIN is an infrequent operation, but each time it runs causes significant latency for the queued RAPID offloaded-queries. For this reason, you should not run ML_TRAIN on a production MySQL HeatWave DB System that is also used for analytics queries. The recommended architecture is to run ML_TRAIN on a dedicated interim MySQL HeatWave DB System provisioned exclusively for model training and brought online only when training or re-training is required. When training completes, you export and deploy the trained model to the production DB System for inference. You can then stop the interim DB System to optimize infrastructure costs.

Before You Begin

Review how to Create a Machine Learning Model.

Workflow

The following sub-tasks show how to train a machine learning model in an interim DB System and then transfer it to a production DB System for inference.

Export the training data to an Object Storage bucket. See Export Training Data to Object Storage.
Start the interim MySQL HeatWave DB System. See Start Interim Database.
Send the training data from the Object Storage bucket to the interim DB System. See Import Training Data.
Train the model in the interim DB System. See Train Model.
Send the trained model to an Object Storage bucket. See Export Trained Model to Object Storage.
Stop the interim DB System to optimize resource usage and costs. See Stop Interim DB System.
Import the trained model to the production DB System. See Import Model to Production DB System.
Use the trained model in the production DB System. See Generate Predictions with Trained Model.

To learn more about standardizing this process, see Process Orchestration.

Note

This use case covers the requirement for a primary key or a pseudo primary key when running a model and inference on a DB System configured with MySQL HeatWave High Availability (HA). Group Replication requires that all tables have a primary key, so to successfully run the ML_TRAIN and ML_PREDICT_* routines you must exclude the pseudo primary key.

The sample data is based on the Iris Data Set Quickstart and the MySQL HeatWave Machine Learning Workshop.

Architecture Overview

This use case uses two DB Systems: one that acts as a production instance, and an interim one used exclusively for training.

The production DB System (instance1) is a MySQL HeatWave HA instance with sql_generate_invisible_primary_key enabled. This DB System requires that every table have a primary key, or a pseudo primary key with auto-increment.

The interim DB System (instance2) does not have HA. It is used for training the model. We recommend configuring the interim DB System with the following:

A 512GB shape.
Between 1-8 nodes, depending on the size of the training data set.

The following DDL statements show the structure of the training (iris_train) and testing (iris_test) tables:

CREATE TABLE `iris_train` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `sepal length` float DEFAULT NULL,
  `sepal width` float DEFAULT NULL,
  `petal length` float DEFAULT NULL,
  `petal width` float DEFAULT NULL,
  `class` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=121 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


CREATE TABLE `iris_test` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `sepal length` float DEFAULT NULL,
  `sepal width` float DEFAULT NULL,
  `petal length` float DEFAULT NULL,
  `petal width` float DEFAULT NULL,
  `class` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The my_row_id column is the required column with a pseudo primary key. You must exclude this column when generating predictions and other operations.

Export Training Data to Object Storage

The first sub-task is to use one-liner MySQL Shell to export the training table (iris_train) from the production DB System (instance1) to an Object Storage bucket. In this example, the bucket is demo.

[opc@diagnostics ~]$ mysqlsh admin@instance1 --js -e "util.dumpSchemas(['ml_data'],'ml_data',{'osBucketName': 'demo', 'osNamespace': 'mysqlpm','ocimds': 'true', 
'includeTables':['ml_data.iris_train'], 'compatibility': ['strip_definers', 'strip_restricted_grants']})"
NOTE: The 'targetVersion' option is set to 9.5.0. This version supports the SET_ANY_DEFINER privilege, using the 'strip_definers' compatibility option is unnecessary.
Acquiring global read lock
Global read lock acquired
Initializing - done
1 schemas will be dumped and within them 1 out of 3 tables, 0 views.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
NOTE: When migrating to MySQL HeatWave Service, please always use the latest available version of MySQL Shell.
Checking for compatibility with MySQL HeatWave Service 9.5.0
Checking for potential upgrade issues.
NOTE: The value of 'targetVersion' option (9.5.0) is not greater than current version of the server (9.5.0), skipping upgrade compatibility checks
Validating MySQL HeatWave Service compatibility - done
Compatibility checks finished.
Writing global DDL files
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
100% (120 rows / ~120 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:05
Total duration: 00:00:05
Schemas dumped: 1
Tables dumped: 1
Uncompressed data size: 3.92 KB
Compressed data size: 1.02 KB
Compression ratio: 3.8
Rows written: 120
Bytes written: 1.02 KB
Average uncompressed throughput: 3.92 KB/s
Average compressed throughput: 1.02 KB/s
[opc@diagnostics ~]$

Start Interim Database

If needed, create and start the interim DB System (instance2).

To learn more, see the following:

Import Training Data

Use one-liner MySQL Shell to import the training table (iris_train) to the interim DB System (instance2) by using loadDump.

[opc@diagnostics ~]$ mysqlsh admin@instance2 --js -- util loadDump 'ml_data' --osBucketName=demo --osNamespace=mysqlpm
Loading DDL and Data from OCI ObjectStorage bucket=demo, prefix='ml_data' using 4 threads.
Opening dump - done
Target is MySQL 9.5.0-u3-cloud (MySQL HeatWave Service). Dump was produced from MySQL 9.5.0-u3-cloud
Fetching dump data from remote location...
Listing files - done
Scanning metadata - done
Checking for pre-existing objects - done
Executing common preamble SQL - done
Executing schema DDL - done
Executing table DDL - done
Executing view DDL - done
Starting data load
100% (3.92 KB / 3.92 KB), 0.00 B/s (0.00 rows/s), 1 / 1 tables done
Building indexes - done
Executing common postamble SQL - done
1 chunks (120 rows, 3.92 KB) for 1 tables in 1 schemas were loaded in 0 sec (avg throughput 3.92 KB/s, 120.00 rows/s)
2 DDL files were executed in 0 sec.
Data load duration: 0 sec
Total duration: 1 sec
0 warnings were reported during the load.
[opc@diagnostics ~]$

Train Model

Train the model in the interim DB System (instance2).

In instance2, set the model handle for the trained model to iris_model_handle.
```
mysql> SET @model_handle='iris_model_handle';
Query OK, 0 rows affected (0.0013 sec)
```

Run ML_TRAIN and exclude the column with the pseudo primary key (my_row_id) by using the exclude_column_list option.

mysql> CALL sys.ML_TRAIN('ml_data.iris.train','class',JSON_OBJECT('task','classification','exclude_column_list', JSON_ARRAY('my_row_id')), @model_handle);
Query OK, 0 rows affected (3 min 37.6367 sec)

Export Trained Model to Object Storage

When the training completes in instance2, export the model metadata table by using the ML_MODEL_EXPORT routine. Then, export this table to an Object Storage bucket.

In instance2, confirm the training table is in the current schema.

mysql> SHOW tables;
+-----------------------------+
| Tables_in_ml_data           |
+-----------------------------+
| iris_train                  |
+-----------------------------+
1 row in set (0.0019 sec)

Run ML_MODEL_EXPORT. Include the model handle for the trained table and define the table to export the table to (iris_model_handle_export).
```
mysql> CALL sys.ML_MODEL_EXPORT(@model_handle,'ml_data.iris_model_handle_export');
Query OK, 0 rows affected (0.0262 sec)
```

Confirm the table is successfully created.

mysql> SHOW tables;
+-----------------------------+
| Tables_in_ml_data           |
+-----------------------------+
| iris_model_handle_export    |
| iris_train                  |
+-----------------------------+
2 rows in set (0.0027 sec)

Disconnect from the DB System.
```
mysql> \q
Bye!
```

Use one-liner MySQL Shell to export this table to an Object Storage bucket. This example uses the demo bucket.

[opc@diagnostics ~]$ mysqlsh admin@instance2 --js -- util exportTable 'ml_data.iris_model_handle_export' 'export_model/iris_model_handle_export.csv' 
--dialect=csv --osBucketName=demo --osNamespace=mysqlpm
Initializing - done
Gathering information - done
Running data dump using 1 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Starting data dump
100% (1 rows / ~1 rows), 0.00 rows/s, 0.00 B/s
Dump duration: 00:00:00s
Total duration: 00:00:00s
Rows written: 1
Bytes written: 423.63 KB
Data size: 423.63 KB
Average throughput: 423.63 KB/s
[opc@diagnostics ~]$

Stop Interim DB System

After exporting the table to Object Storage, you can stop the interim DB System (instance2) to optimize resource usage and costs.

See Starting, Stopping, or Restarting a DB System.

Import Model to Production DB System

Truncate the existing iris_model_handle_export table in the production DB System (instance1). Then, import the trained model from the Object Storage bucket into the production DB System in the iris_model_handle_export table.

In instance1, truncate the iris_model_handle_export table that already exists in instance1.
```
mysql> TRUNCATE ml_data.iris_model_handle_export;
Query OK, 0 rows affected (0.0186 sec)
```

Enable js mode.

mysql> \js
Switching to JavaScript mode...

Import the table from the Object Storage bucket.

mysql-js> util.importTable('export_model/iris_model_handle_export.csv',{'schema':'ml_data','table':'iris_model_handle_export','dialect':'csv','osBucketName':'demo','osNamespace':'mysqlpm'})
Importing from file 'export_model/iris_model_handle_export.csv' to table 'ml_data'.'iris_model_handle_export' in MySQL Server at instance1:3306 using 1 thread
 *.iris_model_handle_export.csv: Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
  100% (423.63 KB / 423.63 KB), 0.00 B/s
NOTE: 'export_model/iris_model_handle_export.csv' (423.63 KB) was imported in 0.6603 sec at 423.63 KB/s
Total rows affected in ml_data.iris_model_handle_export: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

Switch back to sql mode.

mysql-js> \sql
Switching to SQL mode... Commands end with ;

Set the model handle for the imported table.

mysql> SET @model_handle='iris_model_handle2';
Query OK, 0 rows affected (0.0046 sec)

Run ML_MODEL_IMPORT to load the data from the imported table into the iris_model_handle_export table.

mysql> CALL sys.ML_MODEL_IMPORT(NULL, JSON_OBJECT('schema','ml_data','table','iris_model_handle_export'),@model_handle);
Query OK, 0 rows affected (0.1041 sec)

Generate Predictions with Trained Model

At this stage, the trained model is available. You can use it repeatedly on the production instance to generate predictions and other operations. This example uses ML_PREDICT_ROW to perform inference on the iris_test data.

If the test table includes explicit primary key columns, you can use ML_PREDICT_TABLE directly. However, if the table contains invisible or pseudo primary key columns (as in this example), ML_PREDICT_ROW should be used instead, allowing you to omit the pseudo primary key column during inference.

In instance1, load the model handle for the trained model.

mysql> CALL sys.ML_MODEL_LOAD(@model_handle,NULL);
Query OK, 0 rows affected (34.3230 sec)

Run ML_PREDICT_ROW to generate predictions on specified rows.

mysql> SELECT 'sepal length', 'sepal width', 'petal length', 'petal width', 
sys.ML_PREDICT_ROW(JSON_OBJECT('sepal width', "sepal width", 
'petal length', "petal length", 
'petal width', "petal width"),
@model_handle,NULL) prediction from iris.test
limit 3;
+--------------+------------+-------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sepal length | sepal width | petal length | petal width | prediction                                                                                                                                                                                                                                                                                                                                                                                           |
+--------------+------------+-------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 5.9          | 3          | 4.2         | 1.5         | {"Prediction": "Iris-setosa", "ml_results": {"predictions": {"class": "Iris-setosa"}, "probabilities": {"Iris-setosa": 0.98, "Iris-virginica": 0.02, "Iris-versicolor": 0.0}}, "petal width": 1.5, "sepal width": 3.0, "petal length": 4.199999809265137, "sepal length": 5.900000095367432}                                                                                                           |
| 6.9          | 3.1        | 5.4         | 2.1         | {"Prediction": "Iris-virginica", "ml_results": {"predictions": {"class": "Iris-virginica"}, "probabilities": {"Iris-setosa": 0.01, "Iris-virginica": 0.99, "Iris-versicolor": 0.0}}, "sepal width": 3.0999999046325684, "sepal width": 3.0999999046325684, "sepal width": 3.0999999046325684, "petal width": 2.0999999046325684, "petal length": 5.400000095367432, "sepal length": 6.900000095367432} |
| 5.1          | 3.7        | 1.7         | 0.5         | {"Prediction": "Iris-versicolor", "ml_results": {"predictions": {"class": "Iris-versicolor"}, "probabilities": {"Iris-setosa": 0.04, "Iris-virginica": 0.0, "Iris-versicolor": 0.96}}, "petal width": 0.5, "sepal width": 3.299999952316284, "petal length": 1.7000000476837158, "sepal length": 5.099999904632568}                                                                                    |
+--------------+------------+-------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (21.9953 sec)

Process Orchestration

Process orchestration may become essential to standardize this mechanism. This is especially important for handling start/stop events in interim DB Systems, and coordinating data movement between production DB Systems and the interim DB Systems used to run ML_TRAIN.

In OCI, workflow and process orchestration combines services such as Resource Manager, DevOps Pipelines, Events, Functions and many more. Starting and stopping DB Systems can be handled programmatically by running the command line, such as OCI Python SDK or OCI CLI.

To learn more, see the following:

What's Next

Review other Machine Learning Use Cases.

PREV HOME UP NEXT