5.1 Auto Shape Prediction

Activate the Auto Shape Prediction feature in MySQL Autopilot for MySQL HeatWave on AWS to analyze the workload and assess the suitability of the current shape.

When you enable Auto Shape Prediction, the feature begins to collect MySQL statistics reflecting your current workload. Statistics are collected at varying intervals, and Auto Shape Prediction creates a prediction every five minutes while it is active. If there is insufficient or no activity in a five minute interval, or if buffer pool usage is growing, Auto Shape Prediction cannot make a prediction for that interval. Allow the prediction to become stable before disabling Auto Shape Prediction again.

Auto Shape Prediction looks at buffer pool usage, workload activity, and access patterns, and bases its recommendation on those factors. Choose a shape that will accommodate your recommended buffer pool size. If the buffer pool is too small, although MySQL will generally be able to run the workload with stability, the performance of the DB System will suffer through excessive disk I/O.

Tip

If Auto Shape Prediction suggests a possible downsize, consider the DB System CPU usage and memory usage before downsizing. For heavy CPU utilization, downsizing to a shape with fewer CPUs is not recommended.

While Auto Shape Prediction is running, predictions are recorded in the shape_predictions table in the mysql_autopilot schema. This is a system schema that is always present even if you do not activate Auto Shape Prediction. The schema also contains tables for the statistics used to calculate the predictions. If you leave Auto Shape Prediction running, the shape_predictions table keeps a maximum of a little over seven days of history. The internal statistics tables are cleared when you disable Auto Shape Prediction, but the predictions are kept. When your DB System is upgraded, the mysql_autopilot schema is dropped and re-installed, removing the predictions from the previous release.

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 ARE NOT STABLE       |
| 2022-10-28 09:56:48 | 5               | 0.8665   | 4.97778          | FEATURE DATA ARE 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.

  • 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. 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 you can start checking the results by issuing the following statement from your SQL client:

    mysql> SELECT * FROM mysql_autopilot.shape_predictions;

    A new prediction is attempted every five minutes. Re-issue the statement every so often while your typical workload is still running, until you see that the prediction has stabilized.

  4. When the prediction has stabilized and you have made a note of the recommended buffer pool size (prediction_value), disable Auto Shape Prediction by issuing the following statement:

    mysql> CALL mysql_autopilot.shape_prediction(JSON_OBJECT(“enable”, FALSE));
    Note

    You can leave Auto Shape Prediction running, although be aware that this incurs a monitoring overhead from periodic statistics collection and prediction events. If you do leave Auto Shape Prediction running, the shape_predictions table keeps a rotating history for a bit over seven days. When your DB System is upgraded, Auto Shape Prediction is disabled, and you will need to enable it again.

  5. If an upsize or downsize was recommended, check your recommended buffer pool size against this listing of shapes to see what shape is appropriate for your current workload. If there is no HeatWave Cluster attached to the DB System, a larger buffer pool size can be set.

    If Auto Shape Prediction suggests a possible downsize, consider the DB System CPU usage and memory usage before downsizing. For heavy CPU utilization, downsizing to a shape with fewer CPUs is not recommended.

    Shape Specifications Buffer pool size upper limit with HeatWave Cluster Buffer pool size upper limit without HeatWave Cluster
    MySQL.2.16GB 1 CPU, 16 GB memory 5 GB 10 GB
    MySQL.4.32GB 2 CPUs, 32 GB memory 15 GB 20 GB
    MySQL.8.64GB 4 CPUs, 64 GB memory 43 GB 57 GB
    MySQL.32.256GB 16 CPUs, 256 GB memory 20 GB 230 GB
  6. To move to a different DB system shape, follow the steps in Creating a DB System.