11.3 Autopilot Shape Advisor

11.3.1 Autopilot Shape Advisor with MySQL HeatWave Console

11.3.2 Autopilot Shape Advisor with MySQL

As of MySQL 8.0.32, use the Auto Shape Prediction feature in MySQL Autopilot for MySQL HeatWave on AWS to analyze the workload and assess the suitability of the current MySQL shape. The Auto Shape Prediction feature is available prior to MySQL 8.0.32, but not enabled by default.

The Auto Shape Prediction feature begins to collect MySQL statistics that reflect the current workload. It collects statistics 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.

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

When Auto Shape Prediction is running it keeps a rotating history for just over seven days. While there is a monitoring overhead from periodic statistics collection and prediction events, for most workloads and shape combinations the overhead is negligible. If necessary, it is possible to disable Auto Shape Prediction.

When Auto Shape Prediction is disabled it clears the internal statistics tables, but keeps the predictions.

An upgrade to the DB System drops and re-installs the mysql_autopilot schema, which removes the predictions.

As of MySQL 8.0.32, there are two ways to access the Autopilot Shape Advisor, with the MySQL HeatWave Console or with MySQL statements. Prior to MySQL 8.0.32, use MySQL statements.

Note

The SQL output can only provide hints. The MySQL HeatWave Console can provide much more information, and can recommend an improved shape. See: Section 11.3.1, “Autopilot Shape Advisor with MySQL HeatWave Console”.