16.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 HeatWave Console provides much more information and can recommend an improved shape. See: Autopilot Shape Advisor with 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 HeatWave Cluster using the MySQL client (mysql) or MySQL Shell in SQL mode. For instructions to connect to 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