11.3.2 Autopilot Shape Advisor with MySQL

Auto Shape Prediction records predictions in the shape_predictions table in the mysql_autopilot schema. This is a system schema that is always present even if Auto Shape Prediction is not active. The schema also contains tables for the statistics used to calculate the predictions.

Note

The SQL output can only provide hints. The MySQL HeatWave Console can provide much more information, and can recommend an improved shape. See: Section 11.3.1, “Autopilot Shape Advisor with MySQL HeatWave Console”.

This example shows output from the shape_predictions table with predictions made over the course of an hour:

mysql> SELECT * FROM mysql_autopilot.shape_predictions;
+---------------------+-----------------+-----------------------------+-----------------------------------+
| prediction_time     | current_bp_size | hit_rate | prediction_value | outcome                           |
+---------------------+-----------------+-----------------------------+-----------------------------------+
| 2022-10-28 09:46:48 | 5               | NULL     | 0                | NOT ENOUGH FEATURE DATA SNAPSHOTS |
| 2022-10-28 09:51:48 | 5               | 0.866    | 4.97363          | FEATURE DATA IS NOT STABLE        |
| 2022-10-28 09:56:48 | 5               | 0.8665   | 4.97778          | FEATURE DATA IS NOT STABLE        |
| 2022-10-28 10:01:48 | 5               | 0.866    | 25.08            | UPSIZE                            |
| 2022-10-28 10:06:48 | 5               | 0.86425  | 25.63            | UPSIZE                            |
| 2022-10-28 10:11:48 | 5               | 0.8675   | 26.1             | UPSIZE                            |
| 2022-10-28 10:16:48 | 5               | 0.8675   | 26.52            | UPSIZE                            |
| 2022-10-28 10:21:48 | 5               | 0.86625  | 27.24            | UPSIZE                            |
| 2022-10-28 10:26:48 | 5               | 0.86525  | 27.42            | UPSIZE                            |
| 2022-10-28 10:31:48 | 5               | 0.866    | 27.83            | UPSIZE                            |
| 2022-10-28 10:36:48 | 5               | 0.866    | 28.18            | UPSIZE                            |
+---------------------+-----------------+----------+------------------+-----------------------------------+

The shape_predictions table has these columns:

• prediction_time: The timestamp for this prediction. A prediction is attempted every five minutes.

• current_bp_size: The current buffer pool size in GB.

• hit_rate: The current buffer pool hit rate.

• prediction_value: The predicted buffer pool size for optimal performance with this workload in GB.

• outcome: A recommendation to upsize, downsize, or stay with your current shape, or information on why a prediction cannot be made for that interval.

To use Auto Shape Prediction, follow these steps:

1. Connect to the DB System for the HeatWave Cluster, using the MySQL client (mysql) or MySQL Shell in SQL mode. For instructions to connect to MySQL HeatWave on AWS as a client, see Section 5.2, “Connecting from a Client”.

2. Prior to MySQL 8.0.32, while a typical workload is running, enable Auto Shape Prediction by issuing the following statement using the client:

   mysql> CALL mysql_autopilot.shape_prediction(JSON_OBJECT("enable", TRUE));

3. Wait at least five minutes for the first prediction to be attempted, then start to check the results with this statement from the SQL client:

   mysql> SELECT * FROM mysql_autopilot.shape_predictions 
             ORDER BY prediction_time DESC LIMIT 20;

   Auto Shape Prediction attempts a new prediction every five minutes. Re-issue the statement every so often while a typical workload is running, until the prediction has stabilized.

4. When the prediction has stabilized, make a note of the maximum prediction_value and outcome.

5. Choose whether to leave Auto Shape Prediction running to monitor other workloads or disable Auto Shape Prediction with this statement:

   mysql> CALL mysql_autopilot.shape_prediction(JSON_OBJECT("enable", FALSE));

6. To move to a different DB System shape, follow the steps in Section 12.1, “Creating a Backup” and Section 12.3, “Restoring a Backup to a New DB System”.

   The Section 11.3.1.1, “Auto Shape Prediction Data” includes Recommended Action, which recommends a shape, and provides instructions.