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

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 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:

Press CTRL+C to copy
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):

    Press CTRL+C to copy
    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:

    Press CTRL+C to copy
    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:

    Press CTRL+C to copy
    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.