MySQL HeatWave on AWS  /  ...  /  Autopilot Shape Advisor with MySQL

12.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: 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 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 Creating a Backup and Restoring a Backup to a New DB System.

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