17.3.2 Autopilot Shape Advisor with a MySQL Client

Auto Shape Prediction records predictions in the shape_predictions table in the mysql_autopilot schema on the MySQL server. 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 some hints. The MySQL HeatWave Console provides 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 45 minutes: 

mysql> SELECT * FROM mysql_autopilot.shape_predictions LIMIT 10;
+----------------------------+------------+-------------+----------------+-----------------+----------------------+--------------------------+-----------------------------------+
| prediction_time            | bp_size_gb | bp_hit_rate | cpu_core_count | cpu_utilization | predicted_bp_size_gb | predicted_cpu_core_count | prediction_outcome                |
+----------------------------+------------+-------------+----------------+-----------------+----------------------+--------------------------+-----------------------------------+
| 2023-12-11 14:34:43.566972 |         43 |        NULL |              8 |       0.0188317 |                 NULL |                     NULL | EMPTY FEATURE DATA                |
| 2023-12-11 14:39:43.630568 |         43 |        NULL |              8 |        0.809462 |                 NULL |                     NULL | NOT ENOUGH FEATURE DATA SNAPSHOTS |
| 2023-12-11 14:44:43.682870 |         43 |           1 |              8 |        0.917938 |                 NULL |                     NULL | FEATURE DATA IS NOT STABLE        |
| 2023-12-11 14:49:43.691465 |         43 |           1 |              8 |        0.517613 |                 NULL |                     NULL | FEATURE DATA IS NOT STABLE        |
| 2023-12-11 14:54:43.707952 |         43 |       0.875 |              8 |       0.0378884 |                 NULL |                     NULL | FEATURE DATA IS NOT STABLE        |
| 2023-12-11 14:59:43.724345 |         43 |        NULL |              8 |      0.00403875 |                 NULL |                     NULL | LOW ACTIVITY                      |
| 2023-12-11 15:04:43.734345 |         43 |           1 |              8 |        0.120723 |                 12.3 |                     4    | DOWNSIZE                          |
| 2023-12-11 15:09:43.744345 |         43 |           1 |              8 |        0.110723 |                 12.5 |                     4    | DOWNSIZE                          |
| 2023-12-11 15:14:43.754345 |         43 |           1 |              8 |        0.120723 |                 12.4 |                     4    | DOWNSIZE                          |
| 2023-12-11 15:19:43.764345 |         43 |           1 |              8 |        0.130723 |                 12.2 |                     4    | DOWNSIZE                          |
+----------------------------+------------+-------------+----------------+-----------------+----------------------+--------------------------+-----------------------------------+

The shape_predictions table has these columns:

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

  • bp_size_gb: The current buffer pool size in GB.

  • bp_hit_rate: The current buffer pool hit rate.

  • cpu_core_count: The number of current available CPU cores.

  • cpu_utilization: The CPU utilization (on the scale of 0 to 1, with 1 meaning full utilization).

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

  • predicted_cpu_core_count: The predicted CPU core count.

  • prediction_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 with a MySQL client, follow these steps:

  1. Connect to the DB System for the MySQL HeatWave Cluster using the MySQL client (mysql) or MySQL Shell in SQL mode. For instructions to connect to MySQL HeatWave on AWS using 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 (the feature is enabled by default for MySQL 8.0.32 and later): 

    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 a statement like the following on 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 predictions have stabilized.

  4. When the predictions have stabilized, make a note of the maximum values of predicted_bp_size_gb and predicted_cpu_core_count, and the associated actions suggested in prediction_outcome.

  5. 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.

Parent topic: Autopilot Shape Advisor